인덱스는 걸려 있는데 왜 느릴까 — 복합 인덱스 컬럼 순서

주문 검색이 유독 느렸습니다. 회원 ID와 상태로 걸러 최신순으로 뽑는 흔한 쿼리인데 3초씩 걸렸죠. 인덱스는 분명히 걸려 있었습니다. EXPLAIN을 찍어 보고 나서야 인덱스가 있어도 안 타는 이유를 알았습니다.

EXPLAIN SELECT * FROM orders
WHERE member_id = ? AND status = 'PAID'
ORDER BY created_at DESC LIMIT 20;
-- type: ALL, rows: 1,200,000, Using filesort

Table of Contents

인덱스는 왼쪽부터 순서대로 쓰인다

기존 인덱스는 (status, created_at) 순서였습니다. 그런데 쿼리는 member_id로 먼저 좁힙니다. 복합 인덱스는 맨 왼쪽 컬럼부터 순서대로 걸릴 때만 효율적으로 쓰이는데, 정작 선택도가 높은 member_id가 인덱스에 빠져 있었던 겁니다. 그래서 상태로만 대충 걸러 놓고 나머지는 풀스캔에 정렬까지 하고 있었습니다.

선택도 높은 컬럼을 앞으로

쿼리 패턴에 맞춰 (member_id, status, created_at) 순서로 인덱스를 다시 만들었습니다. 등호 조건인 member_id와 status를 앞에 두고, 정렬에 쓰는 created_at을 마지막에 붙였더니 정렬까지 인덱스로 해결됐습니다.

type: ref, rows: 24, (Using filesort 사라짐)

정리

인덱스는 있고 없고의 문제가 아니라 컬럼 순서의 문제였습니다. 등호로 좁히는 컬럼을 앞에, 범위나 정렬 컬럼을 뒤에. 이 원칙만 지켜도 EXPLAIN의 type이 ALL에서 ref로 바뀌는 걸 여러 번 봤습니다.