조회가 느려서 EXPLAIN 을 보니 idx_status 가 있는데도 type: ALL 로 전체를 읽고 있었다. 그래서 FORCE INDEX 로 강제로 쓰게 했더니 오히려 두 배 넘게 느려지는 결과가 나왔다.
Table of contents
Open Table of contents
원인 — 안 쓴 이유
그 조건에 실제로 매칭되는 건수를 COUNT(*) 로 세어 보니 전체의 20퍼센트 가까이였다. 인덱스로 그만큼을 찾으면 인덱스를 읽고 다시 행을 읽는 일이 매 건마다 일어난다.
그것보다 전체를 순서대로 읽는 편이 빨랐으므로 옵티마이저의 판단이 맞았다. 내가 인덱스를 쓰게 강제한 것이 나쁜 판단이었고 강제하기 전에 매칭 건수를 세었으면 알 수 있었다.
조치 — 강제 대신 만든 인덱스
두 가지 방법이 있었는데 FORCE INDEX 를 그대로 두는 것과 조건에 맞는 복합 인덱스 (status, created_at) 을 새로 만드는 것이다. 뒤쪽을 골랐다. 이유는 강제는 있는 인덱스 중에 고르는 것이고 새로 만드는 것은 조건에 맞는 접근 경로를 여는 것이라 성격이 달라서다. 대신 쓰기마다 갱신할 인덱스가 하나 늘고 ALTER TABLE 이 도는 동안의 부하를 감수했다. 두 컬럼을 함께 걸자 EXPLAIN 의 rows 가 88만에서 1만 대로 줄었다.
응답 시간도 초 단위에서 백분의 일 초 수준으로 떨어졌다.
주의 — 컬럼 순서
복합 인덱스를 만들면서 컬럼 순서도 함께 정해야 했다. 각 조건의 매칭 건수만 놓고 보면 created_at 쪽이 훨씬 더 좁히고 있었다.
그런데 날짜는 범위 조건이라 그 뒤에 오는 컬럼은 인덱스를 못 쓰게 된다. 같음 조건을 앞에 두고 범위 조건을 뒤에 두는 것이 원칙이므로 매칭 건수만 보고 순서를 정하면 안 됐다.
검증 — 통계가 낡은 경우
다른 쿼리에서는 실제로 통계가 낡아서 옵티마이저가 잘못 고르고 있었다. ANALYZE TABLE 로 통계를 갱신하고 다시 보니 type 이 ALL 에서 ref 로 바뀌었다.
대량으로 넣거나 지운 뒤에는 통계가 실제와 달라질 수 있다. 그래서 인덱스를 강제하기 전에 통계부터 갱신해 보는 단계를 넣었다.
배경 — 강제가 필요한 경우와 순서
그렇다고 모든 경우에 옵티마이저를 믿을 수 있는 것은 아니었다. 매칭이 열 몇 건뿐인데 ORDER BY id LIMIT 20 이 붙어 있으면 정렬을 피하려고 PRIMARY 를 따라가면서 수백만 행을 읽는 경우가 있었다.
그런 경우는 정렬에 쓰는 컬럼을 인덱스에 포함시켜서 풀었다. 최종 순서는 실행 계획을 보고 매칭 건수를 세고 통계를 갱신한 뒤 조건에 맞는 인덱스를 만들고 그래도 안 되면 강제를 검토하되 강제한 것과 안 한 것을 둘 다 재서 비교하는 것으로 정했다.
정리
- 옵티마이저가 인덱스를 안 쓰면 이유가 있을 수 있다
- 매칭 건수가 전체의 상당 부분이면 전체를 읽는 것이 빠르다
- 강제하기 전에 실제 매칭 건수를 센다
- 조건에 맞는 복합 인덱스가 강제보다 낫다
- 같음 조건을 앞에 두고 범위 조건을 뒤에 둔다
- 통계가 낡았을 수 있으므로 갱신해 본다
- 정렬과 제한이 붙으면 다른 인덱스를 타기도 한다
- 강제한 것과 안 한 것을 둘 다 재서 비교한다