device_jobs 에서 status = 'PENDING' 인 행을 뽑는 배치 쿼리가 느렸다. 그 컬럼이 들어 있는 복합 인덱스 idx_site_status (site_id, status) 가 있었다. EXPLAIN 을 보니 type 이 ALL 이고 그 인덱스를 타지 않고 전체를 읽고 있었다.
Table of contents
Open Table of contents
원인 — 선두 컬럼이 조건에 없었다
복합 인덱스는 왼쪽부터 순서대로 쓰이므로 site_id 로 먼저 정렬되고 그 안에서 status 로 정렬된다. 내 조건에는 두 번째 컬럼만 있었다.
MySQL 5.7 매뉴얼은 조건의 컬럼이 인덱스의 leftmost prefix 를 이루지 않으면 그 인덱스로 조회할 수 없다고 적는다. 그러면 어느 선두 구간에 있는지를 모르므로 인덱스 전 구간을 훑어야 한다. 컬럼이 인덱스에 들어 있다는 것과 그 인덱스를 탈 수 있다는 것은 다르다. 옵티마이저는 그럴 바에 전체 스캔을 고른다.
해 본 것 — 조건 추가와 단독 인덱스와 강제
조건에 site_id IN (...) 을 추가하는 안은 현장 수가 적을 때 된다. 그러나 모든 값을 알아야 하고 새 현장이 생기면 놓친다. status 단독 인덱스를 만드는 안은 가장 직접적이다. 대신 인덱스가 늘고 쓰기가 조금 느려진다.
FORCE INDEX 로 강제하는 안은 대개 답이 아니었다. 옵티마이저가 안 쓰기로 한 데는 이유가 있고 선두 컬럼이 없으면 강제해도 인덱스 전체를 훑게 된다.
막힌 지점 — 조회 빈도가 정한 선택
어느 쪽으로 갈지 정하려고 이 조회가 얼마나 자주 도는지를 봤다. 화면에서 쓰는 것이 아니라 배치에서 하루 몇 번이었다.
그러면 인덱스를 하나 더 만드는 비용이 이득보다 클 수 있다. 조회 빈도가 대응을 가르므로 같은 증상이라도 화면에서 초당 도는 쿼리였으면 반대로 갔을 것이다.
조치 — 원래 있어야 했던 조건
조건을 추가하는 쪽을 골랐다. 이유는 하루 몇 번 도는 배치 때문에 쓰기마다 갱신되는 인덱스를 더 지는 것이 안 맞아서다. 대신 값 목록을 하드코딩하지 않을 방법을 찾아야 하는 부담을 감수했다.
의미 쪽을 보니 이 배치는 승인된 현장만 대상이다. sites.approved = 1 과 조인하는 조건이 원래 있어야 했다. 그 조건을 넣으니 site_id 가 조인 키로 들어와 인덱스를 타면서 결과도 더 정확해졌다. 성능 문제로 시작했는데 빠뜨린 조건 하나가 원인이었던 셈이다.
주의 — 복합 인덱스 설계 순서
이후로 복합 인덱스를 만들 때 등호 조건에 쓰는 컬럼을 앞에 두고 범위 조건을 뒤에 둔다. 값의 종류가 많아 잘 걸러 내는 컬럼을 앞에 두고 정렬에 쓰는 컬럼을 뒤에 둔다.
그리고 어떤 조회가 이 인덱스를 탈지를 목록으로 적어 둔다. EXPLAIN 의 rows 는 추정치이므로 실제 매칭 수를 COUNT(*) 로 따로 센다. 크게 어긋나면 ANALYZE TABLE 로 통계 갱신을 본다.
정리
- 복합 인덱스는 선두 컬럼이 조건에 있어야 탄다
- 컬럼이 인덱스에 들어 있는 것과 탈 수 있는 것은 다르다
- 선두가 없으면 옵티마이저가 전체 스캔을 고른다
- 대응은 조건 추가와 단독 인덱스와 강제 셋이다
- 강제는 선두가 없으면 인덱스 전체를 훑으므로 답이 아니다
- 조회 빈도가 어느 대응이 맞는지를 가른다
- 빠뜨린 조건이 원래 있어야 했던 것인 경우가 있다
- 실행 계획의 예상 행 수는 추정치이므로 실제로 세어 본다