스키마 마이그레이션 표준 원칙 7은 슬로우 쿼리로 잡힌 조합에 인덱스를 추가하기로 정하면서 컬럼 순서를 이렇게 적었다.
컬럼 순서는 원칙 5의 규칙을 그대로 쓴다 — 등호 조건 컬럼을 앞에, 범위 조건 컬럼을 뒤에,
정렬 키 컬럼을 맨 뒤에 둔다. 정렬 키가 뒤에 붙는 이유는 페이징 방식 표준이 커서를
(정렬키, id)로 이미 못박아서다.
정렬 키를 맨 뒤에 붙이는 목적은 정렬을 인덱스가 대신 해주길 바라는 것이다. 증명 후보를 뽑을 때 여기에 의심이 붙었다 — 범위 컬럼 뒤에 둔 정렬 키는 범위 구간 전체에서 정렬돼 있지 않으니 filesort가 날 것이다. 그게 맞는지 쟀다. 이 규칙이 원칙 5에 실제로 있느냐는 별개 문제로 #145에 열려 있다.
주장 — 등호·범위·정렬 키 순서면 정렬까지 인덱스가 받는다
등호 → 범위 → 정렬 키 순서의 복합 인덱스는
등호 조건 + 범위 조건 + 정렬 키 ORDER BY 쿼리를 filesort 없이 처리한다.
근거 종류는 1(기술 사실)이다.
반증 조건 — (가) 순서 인덱스를 쓰고도 filesort가 나면 반증이다
돌리기 전에 적었다. 위에서부터 차례로 본다.
- inconclusive — 실행이 실패하거나, 대조군(인덱스 없음) 측정 여섯 번 중 하나라도
Using filesort가 안 나오거나(filesort를 알아보는 방법이 고장 난 것이다), (가) 측정 여섯 번 중 하나라도 옵티마이저가 (가) 인덱스를 안 골랐다 - refuted — (가) 측정 여섯 번 중 하나라도
Using filesort가 나온다 - supported — (가) 측정 여섯 번이 모두 (가) 인덱스를 쓰고
Using filesort가 없다
(나) 순서 인덱스 결과와 조사 행 수·세션 카운터는 기록만 하고 판정에 쓰지 않았다. 주장이 다루는 것은 표준이 적은 (가) 순서다.
설계 — 범위 컬럼이 정렬 키와 같을 때와 다를 때를 갈랐다
의심을 그대로 재려면 범위 컬럼과 정렬 키가 같은 경우와 다른 경우를 나눠야 했다. 목록 조회의 흔한 모양은 created_at 기간 검색에 created_at 정렬이라 둘이 겹친다. 겹치면 규칙대로 만든 인덱스와 의심이 말하는 인덱스가 같은 것이 되어 아무것도 가려내지 못한다.
-- Q1 범위 = 정렬 키
SELECT * FROM orders WHERE status = ? AND user_id = ? AND created_at >= ? AND created_at < ?
ORDER BY created_at, id LIMIT 20;
-- Q2 범위 ≠ 정렬 키
SELECT * FROM orders WHERE status = ? AND user_id = ? AND amount BETWEEN ? AND ?
ORDER BY created_at, id LIMIT 20;
인덱스는 테이블에 PK와 보조 인덱스 하나만 두고 바꿔 가며 쟀다. 여럿을 같이 두면 옵티마이저가 무엇을 골랐는지에 결과가 끌려간다.
- (가)-Q1
(status, user_id, created_at, id)— 규칙대로면 범위created_at뒤에 정렬 키created_at, id가 오는데, 같은 컬럼을 두 번 둘 수 없어 이렇게 된다. Q1에서는 (나) 순서도 같은 인덱스라 따로 만들지 않았다 - (가)-Q2
(status, user_id, amount, created_at, id)— 규칙 그대로 - (나)-Q2
(status, user_id, created_at, id, amount)— 정렬 키를 범위 앞에 둔 순서 - 대조군 — 보조 인덱스 없음
DB는 표준이 고정한 MariaDB 11.8(11.8.9)이다. 실행마다 전용 컨테이너를 띄우고 끝나면 지웠다. 데이터는 orders 50만 행을 행 번호의 CRC32 식으로 만들어 몇 번을 돌려도 같다. 인덱스를 바꿀 때마다 ANALYZE TABLE ... PERSISTENT FOR ALL로 통계를 다시 뽑았고, 쿼리마다 status·user_id·구간을 바꾼 파라미터 세 벌로 쟀다. filesort 판정은 EXPLAIN의 Extra를 봤고, ANALYZE FORMAT=JSON과 Sort_%·Handler_read_% 세션 카운터는 교차 확인용으로 남겼다.
결과 — 범위가 다른 컬럼이면 (가) 순서에서 filesort가 났다
| 쿼리 | 인덱스 | 사용 | filesort | 인덱스로 읽은 행 |
|---|---|---|---|---|
| Q1 범위=created_at | 없음 | — | 3/3 | 50만(풀스캔) |
| Q1 범위=created_at | (가) status,user_id,created_at,id |
3/3 | 0/3 | 20 |
| Q2 범위=amount | 없음 | — | 3/3 | 50만(풀스캔) |
| Q2 범위=amount | (가) status,user_id,amount,created_at,id |
3/3 | 3/3 | 124~137(구간 전체) |
| Q2 범위=amount | (나) status,user_id,created_at,id,amount |
3/3 | 0/3 | 63~81 |
판정은 refuted다. 대조군은 여섯 번 모두 filesort였고 (가) 인덱스는 여섯 번 모두 선택됐으니 판정을 막는 조건은 없었다. 그 위에서 Q2의 (가) 인덱스가 세 번 모두 Using filesort였다.
세션 카운터도 같은 말을 한다. Q2-(가)는 Handler_read_next가 조건에 맞는 행 수(124~137)와 같고 Sort_range=1·Sort_priority_queue_sorts=1이다 — 범위 구간을 다 읽고 나서 20개를 골라 정렬했다. ANALYZE의 used_key_parts는 status, user_id, amount에서 끝났다. created_at, id는 인덱스에 있었지만 정렬에 쓰이지 않았다.
예상과 달랐던 점 — 첫 실행의 inconclusive는 스크립트 버그였다
첫 실행이 inconclusive로 나왔다. 측정 줄은 전부 멀쩡했는데, run.sh가 측정 횟수를 21번으로 잘못 세어 「실행 실패」로 처리한 것이었다. 실제 설계는 대조군 6 + (가) 6 + (나) 3 = 15번이다. 개수 검사만 15로 고치고 다시 돌렸다. claim.md의 반증 조건과 설계는 건드리지 않았고, 두 실행의 측정 수치는 같았다.
(가)-Q2가 커버링 인덱스로 돌았다. Extra에 Using index가 붙었다. 테이블 컬럼 다섯 개가 전부 인덱스에 들어 있어서다. 실제 테이블은 컬럼이 더 많아 이렇게 안 되지만, filesort 여부는 테이블을 다시 읽느냐와 상관없어 판정에는 영향이 없다고 봤다.
(나)가 행을 덜 읽었다. 예측 행 수(rows)는 475~506으로 (가)보다 컸는데 실제로 읽은 행은 63~81로 더 적었다. 정렬 순서대로 읽다가 조건에 맞는 20개를 채우면 멈춰서다. 다만 이건 amount 구간이 전체의 약 25%라서 나온 숫자다. 구간이 좁으면 (나)는 20개를 채우려고 등호에 맞는 행 거의 전부를 읽어야 한다. (나)가 늘 낫다는 뜻이 아니다. 그 비교는 재지 않았다.
이 결과가 표준에 남기는 것 — 규칙은 범위와 정렬 키가 같은 컬럼일 때만 성립한다
규칙이 성립하는 것은 범위 조건이 정렬 키의 첫 컬럼에 걸릴 때다. 이때는 「범위 뒤에 정렬 키」가 사실상 한 컬럼으로 합쳐지고 filesort가 없다. 커서 페이징의 (created_at, id) > (?, ?)도 정렬 키 자체에 거는 범위라 이 경우에 가까워 보이지만, 그 쿼리 모양은 재지 않았다.
범위가 다른 컬럼이면 한 인덱스로 범위 접근과 정렬을 둘 다 받을 수 없다. 순서를 정하는 것은 둘 중 무엇을 인덱스에 맡기느냐는 선택이다.
- (가) 순서 — 범위로 좁혀 읽고 filesort를 감수한다. 범위가 좁을 때 유리해 보인다
- (나) 순서 — 정렬 순서대로 읽으며 범위를 거르고 LIMIT에서 멈춘다. 범위가 넓을 때 유리해 보인다
제안은 이렇다. 결정하지 않는다. 규칙 문장을 「등호 조건 컬럼을 앞에 둔다. 범위 조건이 정렬 키의 첫 컬럼이면 그 뒤에 정렬 키를 둔다. 범위 조건이 다른 컬럼이면 한 인덱스로 둘 다 받을 수 없으니, 슬로우 쿼리로 관측한 쿼리에서 (가)와 (나)를 EXPLAIN으로 비교해 고른다」로 고치는 안이다. 절차대로 표준 글에 > 반증: 줄을 달고, kind: 모순 이슈(#145와 합칠지 포함)에서 실험 결함인지 규칙 결함인지 따진다.
정리
- 범위 = 정렬 키(Q1)면 (가) 순서에서 filesort가 없었다. 20행만 읽고 끝났다
- 범위 ≠ 정렬 키(Q2)면 (가) 순서에서 세 번 모두 filesort였다. 범위 뒤에 둔
created_at, id는 정렬에 쓰이지 않았다 - (나) 순서는 filesort가 없었지만 범위를 인덱스 접근에 쓰지 못했다. 둘 중 무엇이 빠른지는 범위 선택도에 달렸을 것이고 재지 않았다
- 재지 않은 것 — 실행 시간, 범위 선택도를 바꿨을 때 (가)·(나)의 비용, 커버링이 아닌 넓은 테이블, 커서 조건
(created_at, id) > (?, ?), 범위 조건이 둘 이상인 경우, MariaDB 11.8 외 버전
자신만의 철학을 만들어가는 중입니다.
댓글남기기