[DB] MySQL 어댑티브 해시 인덱스

🔍 어댑티브 해시 인덱스 

   일반적으로 인덱스를 생각하면 B-Tree 인덱스를 생각할 것이다. 하지만 여기서는 사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이다. 사용자는 innodb_adaptive_hash_index 변수를 이용해서 어댑티브 해시 인덱스 기능을 활성화하거나 비활성화할 수 있다. 

 

✏️ B-Tree의 한계

B-Tree는 실제 데이터가 리프 노드에 존재하기 때문에 특정 데이터를 찾기 위해 루트 노드 -> 브랜치 노드 -> 리프 노드의 경로를 거친다. 만약 검색 작업을 몇천 개의 스레드로 실행하면 CPU 사용량이 늘어나고 자연히 쿼리의 성능은 떨어진다.

 

✏️ 어댑티브 해시 엔덱스의 등장

어댑티브 해시 인덱스는 이런 B-Tree의 검색 시간을 줄여주기 위해 도입된 기능이다. InnoDB 스토리지 엔진은 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 어댑티브 해시 인덱스를 검색해서 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있다. 

 

해시 인덱스는 '인덱스 키 값'과 '데이터 페이지 주소'의 쌍으로 관리된다. 해시 인덱스는 하나만 존재하며, 모든 B-Tree 인덱스에 대한 인덱스가 저장되어야 하기 때문에 특정 키 값이 어느 인덱스에 속한 것인지 구분해야 한다. 따라서 인덱스 키 값은 B-Tree 인덱스의 고유번호와 B-Tree 인덱스의 실제 키 값 조합으로 생성된다. 

 

✏️ 어댑티브 해시 인덱스의 사용

어댑티브 해시 인덱스는 다음과 같은 경우에 성능 향상에 도움이 된다

• 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우 (디스크 읽기가 많지 않은 경우)
• 동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
• 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우

 

어댑티브 해시 인덱스는 데이터 페이지를 메모리내에서 접근하는 것을 더 빠르게 만드는 기능으로 데이터 페이지를 디스크에서 읽어오는 경우가 빈번한 데이터베이스 서버에서는 아무런 도움이 되지 않는다.

• 디스크 읽기가 많은 경우
• 특정 패턴의 쿼리가 많은 경우(조인이나 LIKE 패턴 검색)
• 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우

마지막으로 어댑티브 해시 인덱스의 도움을 많이 받을수록 테이블 삭제 또는 변경 작업은 더 치명적인 작업이 된다. 만약 테이블을 변경한다고 하면 InnoDB 스토리지 엔진은 이 테이블이 가진 모든 데이터 페이지의 내용을 어댑티브 해시 인덱스에서 제거해야 한다.  

 

✏️ MySQL 서버 상태 값

어댑티브 해시 인덱스를 도입하는 것에 대해 고민이 될 때는 MySQL 서버의 상태 값들을 살펴보는 것이 좋다. 

mysql > SHOW ENGINE INNODB STATUS\G

위의 결과는 초당 3.67(1.06 + 2.61)번의 검색이 실행되었고, 그중 2.61번은 어댑티브 해시 인덱스를 사용했다는 것을 의미한다.  여기서 'searches'는 쿼리가 처리되기 위해 내부적으로 키 값의 검색이 몇 번 실행되었는지 가리킨다. 

어댑티브 해시 인덱스의 효율은 검색 횟수가 아니라 두 값의 비율과 어댑티브 해시 인덱스가 사용 중인 메모리 공간, 그리고 서버의 CPU 사용량을 종합해서 판단해야 한다.