[MySQL] INDEX 최적화 - 2 (feat. 커버링, 컨디션 푸시다운)

[MySQL] INDEX 최적화 - 1 글에 이어 두 번째 정리를 해야겠다.

커버링 인덱스

처음 글에 정리했던 것처럼 인덱스는 데이터를 효율적으로 탐색해서 디스크 I/O를 줄이는 게 하는 방법이라고 했다. 커버링 인덱스는 쿼리를 충족하는데 필요한 모든 데이터를 갖는 인덱스를 말한다. WHERE절에 대한 쿼리에 인덱스 컬럼을 걸어 사용하는 것 이외에 SELECT / GROUP BY / ORDER BY 등에 활용되는 모든 컬럼의 인덱스의 구성 요소를 뜻한다.

인덱스를 사용하여 처리하는 부분 중에 가장 큰 부하를 차치하는 부분은 인덱스 검색에서 일치하는 키 값의 레코드를 읽는 것이다.

위의 그림처럼 인덱스 탐색에서 일치하는 키 값의 데이터를 조회하기 위해서 추가적인 디스크 I/O가 발생하게 된다. N번의 인덱스가 검색됐을 때 N번의 디스크 I/O가 발생하게 된다. 쿼리 최적화는 디스크 I/O를 줄이는 것이라고 여러 번 강조했다.

create index idx_nickname_game on player (nickname, game); // 복합 인덱스 설정

 

EXPLAIN SELECT * FROM player WHERE nickname BETWEEN '가' AND '힣' AND game = 'LOL';

 

위의 쿼리의 실행 계획을 살펴보면 type은 ALL로 Full Table Scan이 발생되는데 옵티마이저는 전체 데이터의 25% 이상을 조회하는 경우 인덱스를 통해 조회하는 것보다 Full Table Scan으로 데이터 파일들을 읽어 들이는 것이 효율적이라 판단한 상황이다.

이것을 커버링 인덱스를 통해서 개선을 할 수 있는데 인덱스로 설정한 컬럼만 읽어서 쿼리는 모두 처리할 수 있는 인덱스로 불필요한 디스크 I/O를 줄여 조회 시간을 단축시킬 수 있다.

쿼리 조회 변경

EXPLAIN SELECT nickname, game FROM player WHERE nickname BETWEEN '가' AND '힣' AND game = 'LOL';

 

추가적인 데이터 파일을 읽지 않고 인덱스의 컬럼만 가져올 수 있다. 실행 계획을 살펴보면 type이 Index Range Scan이 발생된 걸 확인할 수 있고 Extra 컬럼에 Using Index가 표기되어 있다면 커버링 인덱스가 수행된다는 이야기다. (참고로 Extra 컬럼은 쿼리의 실행 계획에서 성능에 관련된 중요한 내용을 표기해준다)

 

여기서 잠깐!

EXPLAIN SELECT id, nickname, game FROM player WHERE nickname BETWEEN '가' AND '힣' AND game = 'LOL';

Player Table의 PK(Primary Key)도 같이 조회했을 때도 동일한 커버링 인덱스에서 조회될 수 있다. PK를 복합 인덱스로 설정하지 않았는데도 같은 결과가 나온 이유는 innoDB 세컨더리 인덱스의 특수 구조 때문에 리프 노드에는 실제 레코드 주소가 아닌 클러스터 인덱스가 걸린 PK를 주소로 가지게 되기 때문이다. 그래서 id도 동일하게 활용이 가능한 것이다.

인덱스 컨디션 푸시다운

create index idx_game on player (game);
EXPLAIN SELECT * FROM player WHERE nickname BETWEEN '가' AND '힣' AND game = 'LOL';

game 컬럼 기준으로 조회가 많이 일어난다고 가정하고 game 컬럼 인덱스를 새롭게 생성하고 'LOL' game 조건과 nickname BETWEEN 조건으로 조회했다.

 

실행 계획을 살펴보면 type이 idx_game을 통한 Ref로 인덱스를 탄 것을 확인할 수 있다. 인덱스가 잘 적용된 것처럼 보이지만 Extra 컬럼을 보게 되면 Using where만 표기되어 있다.

Using where는 InnoDB 스토리지 엔진을 통해 테이블에서 행을 가져온 뒤 MySQL 엔진에서 추가적인 체크 조건을 활용하여 행의 범위를 축소하는 방식이다. 아래 그림을 참고해보자

 

 

생성한 game index type을 통해서 InnoDB 스토리지 엔진이 조건을 활용해서 인덱스 필터링을 거치고 디스크 파일에서 10000개의 데이터를 MySQL 엔진으로 전달한다.

MySQL 엔진은 인덱스로 걸지 않는 nickname을 기반으로 체크 조건을 통해서 100개 데이터를 필터링을 해서 사용자에게 전달해준다.

결국 InnoDB 스토리지 엔진은 불필요하게 많은 데이터를 읽어버렸다.

 

create index idx_game_nickname on player (game, nickname);

 

game과 nickname을 복합 인덱스로 생성해주었고 실행 계획을 다시 살펴보면 생성한 복합 인덱스를 키로 생성해서 Index Range Scan이 동작하는 것을 확인했고 Extra 컬럼에 Using index condition이 표기되었다.

 Using index condition은 인덱스 컨디션 푸시다운으로 인한 표시이며 이 경우 MySQL WHERE 조건을 스토리지 엔진에 전달하여 스토리지 엔진에서 필터링된 데이터만 MySQL 엔진에 전달된다.

 

 

실행 계획을 살펴볼 때는 인덱스 키, Type 이외에 Extra 컬럼까지 반드시 확인하고 고려해야 한다.

 

아직도 인덱스 스캔 종류가 많이 있다. skip scan, merge scan, fast full scan 등등.. 다양하다.. 공부할게 많아서 좋은 건지;;;;

하나씩 천천히 정리해보는 시간을 가지도록 해보자. ㅠㅠ