[CS]MySQL 아키텍처2_InnoDB 스토리지 엔진

이전 글

[CS]MySQL 아키텍처1_MySQL 엔진

---

InnoDB

구조

Primary Key에 의한 클러스터링

클러스터링이란? 

데이터 레코드가 특정 키의 순서에 따라 디스크에 물리적으로 정렬되어 저장되는 것

클러스터링이 적용되는 키를 클러스터링 인덱스라고 부른다.

 

세컨더리 인덱스란?

Primary Key를 제외한 테이블의 다른 모든 인덱스를 통칭

보조 인덱스라고도 한다.

 

InnoDB

• Primary Key가 클러스터링 인덱스
• 세컨더리 인덱스는 Primary Key의 값을 논리적인 주소로 사용

MyISAM

• 클러스터링 키 지원 X
• Primary Key와 세컨더리 인덱스는 구조적으로 차이가 없다.
• Primary Key를 포함한 모든 인덱스는 물리적인 레코드의 주소 값을 가진다.

 

외래 키 지원

InnoDB 스토리지 엔진 레벨에서 지원 (MyISAM X)

 

부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하다.

foreign_key_checks 시스템 변수를 OFF로 설정하면 외래 키 관계에 대한 체크 작업을 일시적으로 멈출 수 있다.

하지만 부모와 자식 테이블 간의 관계가 깨진 상태로 그대로 유지해도 된다는 것은 아니다. 일관성을 맞춰준 후 외래 키 체크 기능을 다시 활성화해야 한다. 또한 비활성화되면 외래 키 관계의 부모 테이블에 대한 작업도 무시하게 된다. 

 

MVCC(Multi Version Concurrency Control)

목적: 잠금을 사용하지 않는 일관된 읽기 제공

Multi Version: 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미

 

InnoDB는 Undo log를 이용해 해당 기능을 구현한다. 

 

Undo log란?

단일 읽기-쓰기 트랜잭션과 관련된 실행 취소(Undo) 로그 레코드의 모음

 

예를 들어, INSERT문으로 데이터를 넣은 뒤 이를 UPDATE하는 과정이 있다고 하자. 이때 UPDATE문이 실행되면 커밋 실행 여부와 관계없이 InnoDB의 버퍼 풀은 새로운 값으로 업데이트 된다. 디스크의 데이터 파일에는 체크포인트나 InnoDB의 쓰기 스레드에 의해 새로운 값으로 업데이트 되었을 수도 있고 아닐 수도 있다.(일반적으로는 버퍼 풀과 동일한 상태라고 가정해도 무방하다)

 

이때, SELECT로 데이터를 조회하면 어떤 데이터를 조회할까? 이는 격리 수준에 따라 달라진다. 

 

격리 수준 4가지 (Gemini)

격리 수준 (낮음 → 높음)	Dirty Read 방지	Non-Repeatable Read 방지	Phantom Read 방지
READ UNCOMMITTED	X	X	X
READ COMMITTED	O	X	X
REPEATABLE READ	O	O	X (InnoDB O)
SERIALIZABLE	O	O	O

현상 정의

Dirty Read (오손 읽기)	다른 트랜잭션이 아직 커밋하지 않은(Uncommitted) 데이터를 읽는 현상. (가장 위험함)
Non-Repeatable Read (반복 불가능한 읽기)	한 트랜잭션 내에서 같은 행을 두 번 읽을 때, 그 사이에 다른 트랜잭션이 해당 행을 수정하고 커밋하여 값이 바뀌는 현상.
Phantom Read (유령 읽기)	한 트랜잭션 내에서 같은 범위의 행을 두 번 읽을 때, 그 사이에 다른 트랜잭션이 새로운 행을 삽입하고 커밋하여 이전에는 없던 행이 나타나는 현상.

 

격리 수준이 READ UNCOMMITTED인 경우: InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환한다.

다른 격리 수준을 가지고 있는 경우: 아직 커밋되지 않았기 때문에 변경되기 이전의 값을 보관하고 있는 Undo 영역의 데이터를 반환한다.

 

이처럼 하나의 레코드에 대해 2개의 버전이 유지되고, 필요에 따라 어느 데이터가 보여지는지 여러 상황에 따라 달라지는 구조를 MVCC라고 표현한다. 

 

잠금 없는 일관된 읽기(Non-Locking Consistent Read)

특정 사용자가 레코드를 변경하고 아직 커밋을 수행하지 않았더라도 다른 사용자의 SELECT 작업을 방해하지 않는다. InnoDB에서는 변경되기 전의 데이터를 읽기 위해  Undo 로그를 사용한다.

즉, 위에서 설명한 MVCC 기술을 통해 잠금을 걸지 않고 읽기 작업을 수행한다.

 

자동 데드락 감지

InnoDB는 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(Wait for List) 형태로 관리한다. 데드락 감지 스레드가 주기적으로 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그중 하나를 강제 종료한다. 이때 판단 기준은 Undo 로그의 양인다. 이를 적게 가진 트랜잭션이 롤백의 대상이 된다.

 

자동화된 자동 복구

InnoDB에는 손실이나 장애로부터 데이터를 보호하기 위한 여러 메커니즘이 있으며, MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된(Partial write) 데이터 페이지 등에 대한 일련의 복구 작업이 자동으로 진행된다.

 

InnoDB 데이터 파일은 기본적으로 MySQL 서버가 시작될 때 항상 자동 복구를 수행한다. MySQL 서버 설정 파일에 innodb_force_recovery 시스템 변수를 설정해서 MySQL 서버를 시작해야 한다. 해당 값으로 InnoDB 스토리지 엔진이 데이터 파일이나 로그 파일의 손상 여부 검사 과정을 진행할 수 있다. 해당 값은 1~6까지 설정할 수 있고, 값이 커질수록 그만큼 심각한 상황이어서 데이터 손실 가능성이 커지고 복구 가능성은 적어진다. 참고로 0은 복구 모드가 아닐 때이다. 복구 모드에서는 쿼리를 수행할 수 없다.

 

MySQL 서버가 기동되고 InnoDB 테이블이 인식된다면 mysqldump를 이용해 데이터를 가능한 만큼 백업하고 그 데이터로 MySQL 서버의 DB와 테이블을 다시 생성하는 것이 좋다.

 

InnoDB 버퍼 풀

디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다. 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 역할도 여기서 이루어진다.
쓰기 지연은 버퍼 풀이 데이터 변경을 메모리에 보유하여 디스크 쓰기를 미루는 것이며, Redo 로그는 그 미뤄진 변경 내용이 시스템 장애 시에도 복구될 수 있도록 보장하는 역할을 한다. ([CS]MySQL 아키텍처1의 MySQL 스레딩 구조 파트와 연결된다)
변경된 데이터를 모아서 처리하면 랜덤한 디스크 작업의 횟수를 줄일 수 있다.

 

버퍼 풀의 크기 설정

InnoDB 버퍼 풀 크기는 운영체제와 각 클라이언트 스레드가 사용할 메모리도 충분히 고려해서 설정해야 한다.

InnoDB 버퍼 풀 크기를 적절히 작은 값으로 설정해서 조금씩 상황에 따라 증가시키는 방법이 좋다.

• 운영 체제의 전체 메모리 공간이 8GB 미만이라면 50% 할당
• 그 이상(8~50GB)이라면 50%에서 시작하여 조금씩 올려가며 최적점 찾기
• 50GB 이상이라면 15GB에서 30GB정도를 남겨둔 후 나머지 공간을 InnoDB 버퍼 풀로 할당

innodb_buffer_pool_size 시스템 변수로 크기를 설정한다.

참고로, 버퍼 풀의 크기를 줄이는 작업은 서비스 영향도가 매우 크기 때문에 이는 하지 않도록 주의해야 한다.

 

기존에는 버퍼 풀 전체를 관리하는 잠금(세마포어)으로 인해 내부 잠금 경합을 유발해왔다. 이를 줄이기 위해 버퍼 풀을 쪼개서 관리할 수 있도록 개선됐다. 각 버퍼 풀을 버퍼 풀 인스턴스라고 한다.

 

버퍼 풀의 구조

버퍼 풀의 공간을 페이지 크기의 조각으로 쪼개어 데이터 페이지를 읽어서 각 조각에 저장한다.

LRU(Least Recently Used)리스트, 플러시(Flush) 리스트, 프리(Free) 리스트라는 3개의 자료 구조를 관리한다.

 

프리 리스트

InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록

사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용

 

LRU 리스트

LRU + MRU(Most Recently Used)

한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지해서 디스크 읽기 최소화

 

데이터를 찾는 과정

1. 필요한 레코드가 저정된 데이터 페이지가 버퍼 풀에 있는지 검사
2. 디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가
3. 버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동
4. 버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리가 얼마나 접근했었는지에 따라 나이(Age)가 부여되며, 버퍼 풀에 상주하는 동안 쿼리에서 오랫동안 사용되지 않으면 해당 페이지는 버퍼 풀에서 제거된다. 버퍼 풀의 데이터 페이지가 쿼리에 의해 사용되면 나이가 초기화되어 다시 젊어지고 MRU의 헤더 부분으로 옮겨진다.
5. 필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가

 

플러시 리스트

디스크로 동기화되지 않는 데이터를 가진 데이터 페이지의 변경 시점 기준의 페이지 목록을 관리

데이터 변경이 가해진 데이터 페이지는 플러시 리시트에 관리되고 특정 시점이 되면 디스크로 기록돼야 한다.

 

데이터가 변경되면 InnoDB는 변경 내용을 Redo 로그에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영한다. 

하지만 Redo 로그가 디스크로 기록됐다고 해서 데이터 페이지가 디스크로 기록됐다는 것을 보장하지 않으며, 그 반대의 경우도 발생할 수 있다. InnoDB 스토리지 엔진은 체크포인트를 발생시켜 디스크의 Redo 로그와 데이터 페이지의 상태를 동기화하게 된다. 

 

버퍼 풀과 Redo 로그

버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정하면 할수록 쿼리의 성능이 빨라진다. 이는 데이터 베이스 서버의 성능 향상을 위해 데이터 캐시 기능을 향상시키는 것이다.

버퍼 풀은 데이터베이스 서버의 성능 향상을 위해 쓰기 버퍼링 기능까지 향상시킬 수 있다. 이를 위해서는 InnoDB 버퍼 풀과 Redo 로그와의 관계를 먼저 이해해야 한다.

InnoDB 버퍼 풀은 클린 페이지(Clean Page)와 더티 페이지(Dirty Page)를 함께 가지고 있다.

더티 페이지는 디스크와 메모리(버퍼 풀)의 데이터 상태가 다르기 때문에 디스크로 기록되어야 한다.

InnoDB 스토리지 엔진에서 Redo 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용한다. 데이터 변경이 계속 발생하면 Redo 로그 파일에 기록됐던 로그 엔트리는 어느 순간 새로운 로그 엔트리로 덮어 쓰인다. 

InnoDB 스토리지 엔진은 전체 Redo 로그 파일에서 재사용 가능한 공간과 당장 재사용 불가능한 공간을 구분해서 관리해야 한다. 이때 재사용 불가능한 공간을 활성 Redo 로그(Active Redo Log)라고 한다.

 

InnoDB 스토리지 엔진은 주기적으로 체크 포인트 이벤트를 발생시켜 Redo 로그와 버퍼 풀의 더티 페이지를 디스크로 동기화한다. 로그가 다 차면 체크 포인트가 발생한다. 발생한 체크 포인트 중 가장 최근 지점의 LSN이 활성 Redo 공간의 시작점이 된다.

LSN(Log Sequence Number): Redo 로그 파일의 공간이 재사용되며 기록될 때 가지는 로그 포지션으로, 계속 증가한다.

 

InnoDB 버퍼 풀의 더티 페이지는 특정 Redo 로그 엔트리와 관계를 가지고, 체크 포인트가 발생하면 체크 포인트 LSN보다 작은 Redo 로그 엔트리와 관련된 더티 페이지는 모두 디스크로 동기화된다.

 

예제1. InnoDB 버퍼 풀은 100GB이며 Redo 로그 파일의 전체 크기는 100MB인 경우

평균 Redo 로그 엔트리가 4KB라면 25600개 정도의 더티 페이지만 버퍼 풀에 보관할 수 있다. 데이터 페이지가 16KB라고 가정하면 허용 가능한 전체 더티 페이지의 크기는 400MB 수준이다.

→ 버퍼 풀의 크기는 매우 크지만, 쓰기 버퍼링 X

 

예제2. InnoDB 버퍼 풀은 100MB이며 Redo 로그 파일의 전체 크기는 100GB인 경우

예제1과 동일하게 대략 400GB 정도의 더티 페이지를 가질 수 있다. 하지만 버퍼 풀의 크기가 100MB이기 때문에 최대 허용 가능한 더티 페이지는 100MB 크기가 된다.

 

즉, 1번의 상황은 Redo 로그가 작아 자주 체크 포인트가 발생해 대용량 버퍼 풀의 효과가 없다. 낭비가 된다. 반면에 2번의 상황은 버퍼 풀이 작아 캐싱 자체가 적어 Redo 로그가 큰 게 의미가 없다.

그렇기 때문에 버퍼 풀과 Redo 로그 크기 사이의 균형이 중요하다.

 

버퍼 풀 플러시(Buffer Pool Flush)

InnoDB 스토리지 엔진은 버퍼 풀에서 아직 디스크로 기록되지 않은 더티 페이지들을 성능상의 악영향 없이 디스크에 동기화하기 위해 2개의 플러시 기능을 백그라운드로 실행한다.

 

플러시 리스트(Flush_List)

오래 전에 변경된 데이터 페이지 순서대로 디스크에 동기화하는 작업을 수행한다. 언제부터 얼마나 많은 더티 페이지를 한 번에 디스크로 기록하느냐에 따라 사용자의 쿼리 처리가 악영향을 받지 않으면서 처리된다.

 

Adaptive flush 기능

기본 값: 활성화

활성화되면 InnoDB 스토리지 엔진은 단순히 버퍼 풀의 더티 페이지 비율이나 디스크가 처리할 수 있는 환경 변수 설정 값에 의존하지 않고 새로운 알고리즘을 사용한다.

 

LRU 리스트(LRU_List)

사용 빈도가 낮은 데이터 페이지들을 제거해서 새로운 페이지들을 읽어올 공간을 만들어야 한다. 

LRU 리스트의 끝부분부터 시작해서 시스템 변수에 설정된 개수만큼의 페이지들을 스캔한다. 이때 더티 페이지는 디스크에 동기화하게 되며, 클린 페이지는 즉시 프리(Free) 리스트로 페이지를 옮긴다. 

 

버퍼 풀 상태 백업 및 복구

워밍업(Warming Up): 디스크의 데이터가 버퍼 풀에 적재돼 있는 상태

버퍼 풀이 잘 워밍업된 상태에서는 그렇지 않은 경우보다 몇십 배의 쿼리 처리 속도를 보이는 것이 일반적이다.

 

MySQL 5.6 버전부터는 버퍼 풀 덤프 및 적재 기능이 도입됐다. 현재 InnoDB 버퍼 풀의 상태를 백업할 수 있다. 서버를 다시 시작하면 시스템 변수를 이용해 백업된 버퍼 풀의 상태를 다시 복구할 수 있다.

InnoDB 스토리지 엔진이 버퍼 풀의 LRU 리스트에서 적재된 데이터 페이지의 메타 정보만 가져와서 저장하기 때문에 크기가 작고 백업이 매우 빠르게 완료된다. 하지만 백업된 내용을 다시 복구하는 과정은 InnoDB 버퍼 풀의 크기에 따라 시간이 걸릴 수도 있다.

 

Double Write Buffer

Partial-page / Torn-page

InnoDB의 스토리지 엔진에서 더티 페이지를 디스크 파일로 플러시할 때 일부만 기록되는 현상

 

해당 문제를 막기 위해 Double-Write 기법을 이용한다. 

DoubleWrite 버퍼의 내용은 실제 데이터 파일의 쓰기가 중간에 실패할 때만 원래의 목적으로 사용된다.

'C'페이지가 기록되는 도중에 운영체제가 비정상적으로 종료됐다고 가정. InnoDB 스토리지 엔진은 재시작될 때 항상 DoubleWrite 버퍼의 내용과 데이터 파일의 페이지들을 모두 비교해서 다른 내용을 담고 있는 페이지가 있으면 DoubleWrite 버퍼의 내용을 데이터 파일의 페이지로 복사한다.

 

Undo 로그

트랜잭션과 격리 수준을 보장하기 위해 DML로 변경되기 이전 버전의 백업된 데이터

 

트랜잭션 보장: 트랜잭션이 롤백되면 트랜잭션 도중 변경된 데이터를 변경 전 데이터로 복구해야 하는데, 이때 Undo 로그에 백업해둔 이전 버전의 데이터를 이용해 복구한다.

격리 수준 보장: 특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 변경 중인 레코드를 읽지 않고 Undo 로그에 백업해둔 데이터를 읽어서 반환하기도 한다.

 

Undo 로그 레코드 모니터링

Undo 로그의 데이터는 두 가지 용도로 사용된다.

첫 번째 용도가 트랜잭션의 롤백 대비용이다. 트랜잭션을 커밋하지 않아도 실제 데이터 파일(데이터/인덱스 버퍼) 내용은 업데이트된다. 그리고 변경되지 전의 값은 Undo 영역에 백업되는 것이다. 이 상태에서 사용자가 커밋하면 현재 상태가 그대로 유지되고, 롤백하면 Undo 영역의 백업된 데이터를 다시 데이터 파일로 복구한다.

 

두 번째 용도는 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공하는 데 있다. 

*트랜잭션의 격리 수준: 동시에 여러 트랜잭션이 데이터를 변경하거나 조회할 때 한 트랜잭션의 작업 내용이 다른 트랜잭션에 어떻게 보일지를 결정하는 기준

 

Undo 테이블스페이스 관리

하나의 Undo 테이블스페이스 > 1개 이상 128개 이하의 롤백 세그먼트 > 1개 이상의 Undo Slot

하나의 롤백 세그먼트는 InnoDB 페이지 크기를 16바이트로 나눈 개수만큼의 Undo Slot을 가진다.

 

하나의 트랜잭션이 필요로 하는 Undo Slot의 개수는 트랜잭션이 실행하는 INSERT, UPDATE, DELETE 문장의 특성에 따라 최대 4개까지 사용

일반적으로는 2개 정도의 Undo Slot을 필요로 한다고 가정

 

체인지 버퍼

임시 메모리 공간

RDBMS에서 레코드가 INSERT되거나 UPDATE될 때 테이블에 포함된 인덱스를 업데이트하는 과정도 필요하다. InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만, 디스크로부터 읽어와야 하는 경우에는 즉시 수행하지 않고 임시 공간인 체인지 버퍼에 저장해 두고 사용자에게 결과를 반환하는 형태로 성능을 향상시킨다. 

 

Redo 로그 및 로그 버퍼

서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치

ACID 중 D 만족

*Durability(지속성): 성공적으로 수행된 트랜잭션은 영원히 반영되어야 함을 의미 (https://ko.wikipedia.org/wiki/ACID)

 

필요성

• DBMS에서 데이터 파일든 쓰기보다 읽기 성능을 고려한 자료 구조를 가진다.
  • 데이터 파일 쓰기는 디스크의 랜덤 액세스가 필요하다.
  • 큰 비용이 필요하다.
• 성능 저하를 막기 위해 쓰기 비용이 낮은 자료 구조를 가진 Redo 로그를 사용
• 비정상 종료가 발생하면 Redo 로그의 내용을 이용해 복구한다.

 

데이터베이스 서버는 Redo 로그를 버퍼링할 수 있는 InnoDB 버퍼 풀이나 로그 버퍼와 같은 자료 구조도 가진다.

 

InnoDB에서 데이터 변경이 발생했을 때

1. 데이터는 InnoDB 버퍼 풀 내의 페이지에서 변경된다. 이때 해당 페이지가 더티 페이지가 된다.
2. 데이터가 버퍼 풀에서 변경되면서, Redo 로그 레코드가 생성되어 로그 버퍼에 기록된다.
3. 트랜잭션이 commit 하면 로그 버퍼에서 Redo 로그 파일로 Flush되고 Sync 된다.
4. Redo 로그가 디스크에 기록된 후에는, 버퍼 풀에 남아있는 더티 페이지를 디스크의 데이터 파일에 기록한다.

4번의 과정을 백그라운드에서 비동기적으로 수행된다.

 

비정상 종료 시 InnoDB 스토리지 엔진의 데이터 파일은 커밋됐지만 데이터 파일에 기록되지 않은 데이터, 롤백됐지만 데이터 파일에 이미 기록된 데이터를 가질 수 있다.

전자의 경우에는 Redo 데이터를 데이터 파일에 다시 복사하기만 하면 된다.

후자는 변경되기 전 데이터를 가진 Undo 로그의 내용을 가져와 데이터 파일에 복사하면 된다. 이때 변경이 커밋됐는지, 롤백됐는지, 아니면 트랜잭션의 실행 중간 상태였는지를 확인하기 위해서 Redo가 필요하다.

 

데이터 변경이 너무 많으면 Redo 로그가 매우 빠르게 증가하고, 새로 추가되는 Redo 로그 내용을 복사하기도 전에 덮어쓰일 수도 있다. 이렇게 되면 백업 툴이 Redo 로그 엔트리를 복사할 수 없어서 백업은 실패한다. 이를 해결하기 위한 것이 Redo 로그 아카이빙 기능이다.

 

어댑티브 해시 인덱스

InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스

 

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

 

해시 인덱스는 '인덱스 키 값'과 '데이터 페이지 주소'의 쌍으로 관리된다. 여기서 인덱스 키 값은 'B-Tree 인덱스의 고유번호'와 'B-Tree 인덱스의 실제 키 값'의 조합으로 생성된다.

어댑티브 해시 인덱스는 버퍼 풀에 올려진 데이터 페이지에 대해서만 관리된다.

 

어댑티브 해시 인덱스는 상황에 따라 성능 향상에 도움이 될 수도, 안 될 수도 있다.

디스크 읽기가 많은 경우에는 도움이 되지 않을 것이며, 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우에는 디스크 읽기가 많지 않기 때문에 도움이 될 것이다. 

어댑티브 해시 인덱스는 저장 공간인 메모리를 사용하며, 데이터 페이지의 인덱스 키가 해시 인덱스로 만들어져야 하고 불필요하면 삭제되어야 하며 InnoDB 스토리지 엔진은 그 키 값이 해시 인덱스에 있든 없든 검색해봐야 한다.

테이블을 삭제하거나 변경하려고 하면 InnoDB 엔진은 해당 테이블이 가진 모든 데이터 페이지의 내용을 어댑티브 해시 인덱스에서 제거해야 한다. 테이블이 삭제되거나 스키마가 변경되는 동안 CPU 자원을 많이 사용하게 되고, 그만큼 데이터베이스 서버 처리의 성능이 느려진다.

 

어댑티브 해시 인덱스의 효율은 해시 인덱스 히트율과 어댑티브 해시 인덱스가 사용 중인 메모리 공간, 서버의 CPU 사용량을 종합해서 판단해야 한다.

 

 

 

 

출처

Real MySQL 8.0