[MySQL] 인덱스 다이브(Index Dive) 현상과 방지하기

💡 July님의 200억건의 데이터를 MySQL로 마이그레이션 할 때 고려했던 개념과 튜닝 방법 강의를 듣고 정리한 내용입니다.

 

목차

Index Dive

잘못된 인덱스 설정이나 비효율적으로 작성된 쿼리로 인해 MySQL 옵티마이저(Optimizer)가 쿼리 수행 전략을 수립할 때 많은 시간이 소요되는 현상을 말한다. 일종의 잘못된 로직으로 인해 발생하는 버그이며, Index Dive가 발생하면 쿼리 속도가 현저히 느려진다.

Optimizer와 실행 계획

쿼리가 MySQL 서버로 전달되면 옵티마이저(Optimizer)가 동작하여 쿼리의 실행 계획을 수립한다. DBMS에 따라 PostgreSQL은 캐시성으로 실행 계획 수립 단계를 스킵하기도 한다. MySQL도 하나의 Connection 내에서는 실행 계획 수립을 재활용하기도 하지만, 새로운 Connection에서는 항상 새로운 SQL문에 대해서 Optimizer가 분석하고 실행 계획을 수립한다. 이처럼 쿼리가 들어올때마다 최적의 선택지를 탐색하기 위해 분석하면서 오버헤드가 발생한다. (Trade-off)

 

이때 실행 계획을 수립하는데 많은 오버헤드가 드는 현상을 인덱스 다이브(Index Dive)라고 한다.

발생 예시

보통 다음과 같이 인덱스를 구성하는 컬럼 간 중복이 많을때 발생한다. 먼저 다음과 같이 test 테이블에 name과 second_name 컬럼을 중복 사용하는 인덱스가 많이 설정되어 있다고 가정하자.

CREATE TABLE test (
	name VARCHAR(255),
	second_name VARCHAR(255),
	one INT,
	two INT,
	INDEX name_name(name, second_name),
	INDEX name_name_one(name, second_name, one),
	INDEX name_name_two(name, second_name, two)
);

 

이때 두 컬럼에 대한 쿼리를 호출하는 경우 MySQL은 적절한 실행 계획을 수립하기 위해 설정된 인덱스를 검토한다. 위와 같이 생성한 test 테이블에 다음과 같은 쿼리를 호출한다고 가정하자.

 

먼저 name 컬럼에 대한 IN절과 second_name 컬럼에 대한 IN 절에 각각 150개의 파라미터를 전달하고 있으므로 총 150 * 150번의 연산이 발생하게 된다.

SELECT * 
FROM test 
WHERE name IN ("1", "2", ... "150") AND second_name IN ("1", "2", ... "150")

 

또한 name과 second_name 필드로 구성된 인덱스가 총 3개 존재한다. MySQL은 최적의 실행 계획을 수립하기 분석하기 위해 부합되는 모든 인덱스 설정값에 대해서 한 번씩 계획을 수립한다. 따라서 150 * 150번의 계획이 3번 발생하므로 총 150 * 150 * 3번의 연산이 발생한다.

 

이처럼 인덱스를 구성하는 값들의 중복이 많아질수록 오버헤드가 발생하여 Index Dive가 발생하게 된다.

Index Dive 방지 - 특정 인덱스 지정하기

이를 방지하는 방안으로 FORCE 혹은 USE 옵션을 주어 특정 인덱스를 지정하는 방법이 있다.

FORCE

단일 테이블에 대한 쿼리만 가능하다. 또한 ORDER BY, GROUP BY, DISTINCT 등 함수가 SQL에 포함되어 있는 경우 사용할 수 없다.

SELECT * 
FROM test 
WHERE name IN ("1", "2", ... "150") AND second_name IN ("1", "2", ... "150")
FORCE INDEX (name, name)

 

USE

FORCE 옵션과 달리 제약이 없다.

SELECT * 
FROM test 
WHERE name IN ("1", "2", ... "150") AND second_name IN ("1", "2", ... "150")
USE INDEX (name, name)

 

또한 Oracle을 사용하는 경우 힌트로 인덱스를 고정하는 방안도 있다.