MySQL 쿼리 최적화2

⭐ 인덱스 다이브

인덱스 설정이나 SQL문이 비효율적으로 작성되었을 경우, 옵티마이저의 쿼리 실행 계획 수립에 많은 시간이 소요되는 상황을 의미한다.

테이블 : users
컬럼 리스트 : id, name, phone, email
인덱스 : id + name, id + name + phone, id + name + email

위와 같은 구조를 가진 테이블이 있다고 가정했을 때, 아래와 같은 SQL문을 실행한다면, 옵티마이저는 IN절에서 각각 100, 31개의 파라미터를 전달하기에 우선적으로 100 * 31의 연산이 발생한다. 추가적으로 조건으로 사용하고 있는 id, name 컬럼에 부합하는 인덱스를 각각 대입하여 최적의 계획을 수립하고자 하기에, 추가로 부합하는 인덱스 수만큼의 연산이 발생하기에 총 100 * 31 * 3의 계획 수립 과정을 거친다.

SELECT * FROM users WHERE id IN (1,2,3,4...100) AND name IN ('a','b'...'z')

이처럼 비효율적인 SQL(대량의 IN조건)과 인덱스 설정(부합하는 다수의 인덱스)로 인하여 인덱스 다이브가 발생하게 된다.

🔎 FORCE & USE

인덱스 다이브를 해결하기 위해서 SQL문 최적화와 더불어 옵티마이저가 해당 SQL문에 가장 부합하는 인덱스를 빠르게 선택하기 위한 전략도 필요하다. 이러한 옵티마이저의 인덱스 선택을 제어하기 위한 키위드가 FORCE, USE이다.

1. FORCE INDEX
   MySQL의 옵티마이저가 특정 인덱스를 강제로 사용하도록 지시한다. FORCE를 사용하면 옵티마이저의 의사와 관계없이 지정된 인덱스를 반드시 사용해야만 한다.

// idx_name 인덱스를 강제로 사용
SELECT * FROM users FORCE INDEX (idx_name)
WHERE id IN (1,2,3) AND name IN ('a','b','c')

2. USE INDEX
   MySQL의 옵티마이저에게 특정 인덱스를 추천한다. FORCE와 다르게 인덱스를 강제하는 것이 아닌 추천하는 것이기에 옵티마이저가 판단하기에 더 나은 성능의 인덱스가 존재한다면 다른 인덱스를 사용할 수도 있다.

// idx_name 인덱스를 추천
SELECT * FROM users USE INDEX (idx_name)
WHERE id IN (1,2,3) AND name IN ('a','b','c')

⭐ Prefix Index

Prefix Index는 접두사(=컬럼의 일부)를 기준으로 한 인덱싱 방법을 의미한다.

// users 테이블 name 컬럼의 앞 5자리만을 인덱싱 한다.
CREATE INDEX idx_name_prefix ON users (name(5))

위의 SQL문처럼 작성 방법은 보통의 인덱스와 동일하나 지정 컬럼의 우측에 몇자리까지의 정보를 인덱스로 관리할 것인지를 추가하는 것이 Prefix Index 작성의 특징이다.

🔎 언제 사용?

일부 접두사만으로 해당 컬럼의 데이터 구분이 가능할 때, 혹은 컬럼의 사이즈가 매우 긴 경우라면 Prefix Index의 사용을 고려해 볼 필요가 있다.
아래와 같이 DISTINCT, LEFT 키워드를 사용한 검색을 통해서 컬럼의 Prefix Index의 적합도를 측정해 볼 수도 있다. 비율이 높을수록 Prefix Index 효율성이 올라간다.

// name 컬럼의 전체 대비 접두사 3자리의 중복비율을 체크
SELECT COUNT(DISTINCT LEFT(name, 3)) / COUNT(*) as unique_rate FROM users