스키마 마이그레이션 표준 원칙 7은 슬로우 쿼리(정해 둔 시간보다 오래 걸린 쿼리)로 잡힌 검색 조건 컬럼의 조합에 인덱스를 추가하기로 정했다. 이때 만드는 인덱스는 컬럼 여러 개를 묶은 복합 인덱스라서 컬럼을 어떤 순서로 묶을지가 문제가 되는데, 원칙 7은 그 순서를 이렇게 적었다. 인용 안의 「원칙 5」는 같은 글의 원칙 5이고, 「페이징 방식 표준」은 페이징 방식 표준이다.
컬럼 순서는 원칙 5의 규칙을 그대로 쓴다 — 등호 조건 컬럼을 앞에, 범위 조건 컬럼을 뒤에,
정렬 키 컬럼을 맨 뒤에 둔다. 정렬 키가 뒤에 붙는 이유는 페이징 방식 표준이 커서를
(정렬키, id)로 이미 못박아서다.
위 인용에서 등호 조건은 컬럼 = 값처럼 정확히 하나의 값과 맞아떨어지는 조건이고, 범위 조건은 컬럼 >= 값 AND 컬럼 < 값처럼 값의 구간을 고르는 조건이다. 정렬 키는 ORDER BY에 적는, 결과를 그 순서로 줄 세울 기준 컬럼이다. 페이징은 결과가 많을 때 20개씩처럼 끊어서 나눠 보내는 방식이다. 「커서를 (정렬키, id)로 못박았다」는 것은, 목록을 다음 페이지로 넘길 때 페이지 번호 대신 지금까지 읽은 마지막 행(테이블에 저장된 데이터 한 건)의 정렬 키 값과 id 값(행마다 붙은 고유 번호)을 같이 기억해 뒀다가, 화면이 서버에 다음 페이지를 달라고 다시 물을 때 그 두 값을 함께 보낸다는 뜻이다.
정렬 키를 맨 뒤에 붙이는 목적은 정렬을 인덱스가 대신 해주길 바라는 것이다. 인덱스는 값이 미리 줄 서 있는 목록이라, 정렬 키 순서대로 줄 서 있는 부분을 앞에서부터 읽기만 하면 따로 정렬할 필요가 없다.
이 저장소는 표준 글의 주장마다 실험으로 확인할 후보를 뽑아 두는데(proofs/backlog.jsonl), 그 후보를 뽑을 때 여기에 의심이 붙었다. 의심의 내용은 이렇다. 복합 인덱스는 앞 컬럼 값이 같은 것끼리만 다음 컬럼으로 줄을 선다. 주문 테이블의 상태(status) · 사용자 번호(user_id) · 금액(amount) · 주문 시각(created_at) · 고유 번호(id) 컬럼으로 만든 (status, user_id, amount, created_at, id) 인덱스라면 created_at은 amount 값 하나 안에서만 줄을 서 있다. amount가 10000인 행들 안에서 created_at 순, 10001인 행들 안에서 다시 created_at 순이다. 그런데 범위 조건은 amount 값 여러 개를 한꺼번에 고른다. 고른 구간 전체를 앞에서부터 읽으면 created_at이 amount 값마다 처음부터 다시 시작하니, 구간 전체로 보면 created_at 순서가 아니다. 그러면 DB가 읽은 행을 따로 다시 정렬해야 하고, 그 작업이 filesort다. 그게 맞는지 쟀다. 이 규칙이 원칙 5에 실제로 있느냐는 별개 문제로 GitHub 이슈 #145에서 따졌다(지금은 닫혔다).
주장 — 등호·범위·정렬 키 순서면 정렬까지 인덱스가 받는다
등호 → 범위 → 정렬 키 순서의 복합 인덱스는
등호 조건 + 범위 조건 + 정렬 키 ORDER BY 쿼리를 filesort 없이 처리한다.
근거 종류는 1(기술 사실)이다.
반증 조건 — (가) 순서 인덱스를 쓰고도 filesort가 나면 반증이다
돌리기 전에 적었다. 아래에서 (가) 순서는 표준 규칙대로 「등호 → 범위 → 정렬 키」로 묶은 인덱스이고, (나) 순서는 의심이 대안으로 떠올린 「등호 → 정렬 키 → 범위」 인덱스다. 대조군은 보조 인덱스(PK 말고 따로 더 만든 인덱스)를 하나도 두지 않은 상태다. filesort가 났는지는 EXPLAIN 결과의 Extra 칸에 Using filesort라는 글자가 찍혔는지로 본다(설계에서 다시 설명한다). 인덱스가 없으면 정렬을 대신해 줄 것이 없으니 ORDER BY가 있는 쿼리에는 반드시 filesort가 나야 한다. 그래서 대조군에서 filesort가 안 잡히면 쿼리가 아니라 filesort를 알아보는 방법이 고장 난 것으로 본다. 「여섯 번」은 쿼리 두 개(아래 설계의 Q1 · Q2)에 파라미터(쿼리의 ? 자리에 넣는 값) 세 벌씩을 곱한 수다. 또 옵티마이저는 인덱스가 있어도 테이블 전체를 처음부터 읽는 편이 빠르다고 계산하면 인덱스를 안 쓸 수 있다(MySQL 문서 기준이고, MariaDB 문서에서 따로 확인하지는 않았다). 그러면 (가) 인덱스를 잰 것이 아니게 되므로 판정을 내리지 않는다. 위에서부터 차례로 본다.
- inconclusive — 실행이 실패하거나, 대조군(인덱스 없음) 측정 여섯 번 중 하나라도
Using filesort가 안 나오거나(filesort를 알아보는 방법이 고장 난 것이다), (가) 측정 여섯 번 중 하나라도 옵티마이저가 (가) 인덱스를 안 골랐다 - refuted — (가) 측정 여섯 번 중 하나라도
Using filesort가 나온다 - supported — (가) 측정 여섯 번이 모두 (가) 인덱스를 쓰고
Using filesort가 없다
(나) 순서 인덱스 결과와, 실제로 읽은 행 수 · DB가 세는 내부 카운터(둘 다 아래 설계에서 설명한다)는 기록만 하고 판정에 쓰지 않았다. 주장이 다루는 것은 표준이 적은 (가) 순서다.
설계 — 범위 컬럼이 정렬 키와 같을 때와 다를 때를 갈랐다
의심을 그대로 재려면 범위 컬럼과 정렬 키가 같은 경우와 다른 경우를 나눠야 했다. 목록 조회의 흔한 모양은 created_at 기간 검색에 created_at 정렬이라 둘이 겹친다. 겹치면 (가) 「등호 → 범위(created_at) → 정렬 키(created_at, id)」와 (나) 「등호 → 정렬 키(created_at, id) → 범위(created_at)」가 둘 다 (status, user_id, created_at, id)라는 같은 인덱스가 되어 아무것도 가려내지 못한다. 한 인덱스에 같은 컬럼을 두 번 둘 수 없어서, 겹친 created_at은 한 번만 남기 때문이다.
테이블은 orders 하나이고 컬럼은 다섯 개다. id는 행마다 붙은 고유 번호이자 PK, status는 주문 상태(0~4 다섯 가지 값), user_id는 주문한 사용자 번호(200명), amount는 금액(0~99999), created_at은 주문 시각(2025년 한 해)이다. 값의 종류와 범위는 result.md(실험 스크립트가 실행 결과를 사람 손을 거치지 않고 그대로 적는 파일)에 찍힌 데이터 요약 그대로다.
아래 두 쿼리에서 -- 로 시작하는 줄은 SQL 주석이라 실행되지 않는다. 쿼리 이름표다.
-- 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;
한 줄씩 따라가면, SELECT *는 「모든 컬럼을 가져와라」, FROM orders는 「orders 테이블에서」라는 뜻이다. WHERE status = ? AND user_id = ? AND created_at >= ? AND created_at < ?는 그 뒤에 조건을 적은 것으로, ?는 실제 쿼리를 실행할 때 들어갈 값의 자리(파라미터)를 뜻하고 AND는 앞뒤 조건을 모두 만족해야 한다는 뜻이다. status = ?·user_id = ?는 등호 조건(정확히 하나의 값과 맞아떨어지는 조건)이고, created_at >= ? AND created_at < ?는 범위 조건(구간을 고르는 조건)이다. Q2의 amount BETWEEN ? AND ?도 「두 값 사이」를 뜻하는 범위 조건이다. ORDER BY created_at, id는 결과를 created_at 순으로, 같은 값이면 id 순으로 줄 세우라는 뜻이고, LIMIT 20은 그중 앞의 20개만 가져오라는 뜻이다. 즉 Q1·Q2 둘 다 “조건에 맞는 행을 찾아 정렬한 뒤 20개만 보여주는” 쿼리이고, 차이는 범위 조건으로 쓴 컬럼이 Q1은 정렬 키와 같은 created_at이고 Q2는 다른 amount라는 것이다.
인덱스는 테이블에 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)이다. 실행마다 전용 컨테이너를 띄우고 끝나면 지웠다. 컨테이너는 MariaDB를 다른 프로그램과 섞이지 않는 작은 방에 넣어 돌리는 것이라, 앞 실행이 남긴 데이터나 설정이 다음 실행에 끼어들지 않는다. 데이터는 orders 50만 행이다. 행 번호 1~500000에 MariaDB의 CRC32 함수(문자열을 넣으면 32비트 정수 하나를 돌려주는 함수)를 써서 각 컬럼 값을 만들었다. 예를 들어 status는 CRC32(CONCAT('s', 행 번호)) % 5다. CONCAT은 문자열을 이어 붙이는 함수라 행 번호 7이면 's7'이 되고, 그 CRC32 값을 5로 나눈 나머지(0~4)가 status가 된다. 난수가 아니라 행 번호로 계산한 값이라 몇 번을 돌려도 같은 데이터가 나온다. 인덱스를 바꾸면 옵티마이저가 참고하는 테이블 통계(행 수, 값의 분포)가 낡으므로, ANALYZE TABLE ... PERSISTENT FOR ALL로 통계를 다시 뽑았다. PERSISTENT FOR ALL은 모든 컬럼과 모든 인덱스의 통계를 다 갱신하라는 뜻이다. 이건 쿼리가 아니라 “이 테이블 다시 살펴봐”라고 DB에 시키는 명령이다. 쿼리마다 status·user_id·구간을 바꾼 파라미터 세 벌로 쟀다. filesort 판정은 EXPLAIN의 Extra를 봤다 — Extra는 EXPLAIN 결과에서 옵티마이저가 그 쿼리를 처리하며 덧붙인 메모를 적는 칸으로, 여기에 Using filesort가 뜨면 정렬을 인덱스가 아니라 따로 처리했다는 뜻이고 Using index가 뜨면 테이블 본문을 다시 안 읽고 인덱스만으로 끝났다는 뜻이다. 인덱스는 테이블 본문(행 데이터 전체)과 따로 저장되는 별도의 목록이라, 인덱스에 없는 컬럼이 필요하면 본문을 한 번 더 찾아가야 한다. EXPLAIN은 쿼리를 실행하지 않고 계획만 보여 주므로, 거기 나오는 행 수(rows 칸)는 옵티마이저의 예측이다. ANALYZE FORMAT=JSON은 쿼리를 실제로 실행하면서 더 자세한 숫자를 JSON(이름과 값을 짝지어 글자로 적는 형식)으로 보여 주는 명령이고, 실제로 읽은 행 수(r_rows)가 여기서 나온다. 같은 ANALYZE라는 단어지만 앞의 ANALYZE TABLE(통계 다시 뽑기)과는 다른 명령이다. Sort_%·Handler_read_%는 이름이 Sort_와 Handler_read_로 시작하는 카운터들을 줄여 적은 것이다(%는 「뒤에 무엇이 붙든」이라는 뜻이다). MariaDB가 DB 연결 세션(DB에 맺은 연결 하나)마다 세고 있는 내부 카운터로, 그 연결에서 쿼리를 실행하며 정렬을 몇 번 했는지, 행을 몇 번 읽었는지 같은 숫자다. 쿼리 직전에 0으로 되돌리고(FLUSH STATUS) 쿼리 한 번 뒤의 값을 읽었다. ANALYZE FORMAT=JSON의 숫자와 이 세션 카운터, 둘은 EXPLAIN과 서로 다른 각도에서 같은 사실을 확인하는 교차 확인용으로 남겼다.
결과 — 범위가 다른 컬럼이면 (가) 순서에서 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 |
표를 읽는 법은 이렇다. 인덱스는 그 줄에서 쓴 보조 인덱스, 사용은 세 번 측정 중 옵티마이저가 그 인덱스를 실제로 고른 횟수, filesort는 세 번 중 Using filesort가 뜬 횟수, 실제로 읽은 행은 ANALYZE FORMAT=JSON이 보여 준 “실제로 읽은 행 수”(r_rows)다. EXPLAIN의 예측(rows)이 아니다. 인덱스가 없는 줄은 인덱스 대신 테이블을 처음부터 끝까지 읽은 행 수다. 예를 들어 Q1에 인덱스가 없으면 orders 50만 행을 처음부터 끝까지 다 읽어야(풀스캔) 조건에 맞는 20개를 찾지만, (가) 인덱스를 쓰면 20행만 읽고 끝난다 — 조건에 정확히 맞는 20개를 인덱스 순서 그대로 바로 찾아낸 것이다.
판정은 refuted다. 대조군은 여섯 번 모두 filesort였고 (가) 인덱스는 여섯 번 모두 선택됐으니 판정을 막는 조건은 없었다. 그 위에서 Q2의 (가) 인덱스가 세 번 모두 Using filesort였다.
세션 카운터도 같은 말을 한다. Handler_read_next는 인덱스를 순서대로 한 칸씩 읽을 때마다 늘어나는 카운터인데, Q2-(가)는 이 값이 조건에 맞는 행 수(124~137)와 같다. Sort_range는 범위 조건으로 좁힌 결과를 정렬한 횟수를, Sort_priority_queue_sorts는 그 정렬을 LIMIT 개수만 담는 우선순위 큐로 했다는 것을 세는 카운터인데 둘 다 1이다. 우선순위 큐로 정렬했다는 것은 읽은 행 전부를 줄 세우지 않고, 지금까지 본 것 중 created_at이 가장 이른 20개만 담아 두며 나머지는 버리는 식으로 앞 20개를 골랐다는 뜻이다. 종합하면 범위 구간에 맞는 행을 전부 인덱스로 읽은 다음, 그걸 따로 정렬해서 20개를 골랐다는 뜻이다. ANALYZE FORMAT=JSON이 보여 준 used_key_parts(인덱스 컬럼 중 실제로 찾기에 쓰인 것)는 status, user_id, amount에서 끝났다. created_at, id는 인덱스 안에 있었지만 정렬에는 쓰이지 않았다. 도입에 적은 의심 — created_at은 amount 값 하나 안에서만 줄 서 있어서 amount 구간 전체로는 순서가 맞지 않는다 — 과 맞는 모습이다.
예상과 달랐던 점 — 첫 실행의 inconclusive는 스크립트 버그였다
첫 실행이 inconclusive로 나왔다. run.sh는 이 실험을 처음부터 다시 돌리는 셸 스크립트이고, 측정 한 번마다 결과를 MEASURE로 시작하는 한 줄(측정 줄)로 찍는다. 측정 줄은 전부 멀쩡했는데, run.sh가 측정 횟수를 21번으로 잘못 세어 「실행 실패」로 처리한 것이었다. 실제 설계는 대조군 6 + (가) 6 + (나) 3 = 15번이다. 개수 검사만 15로 고치고 다시 돌렸다. claim.md의 반증 조건과 설계는 건드리지 않았고, 두 실행의 측정 수치는 같았다.
(가)-Q2가 커버링 인덱스로 돌았다. Extra에 Using index가 붙었다. 테이블 컬럼 다섯 개(id, status, user_id, amount, created_at)가 전부 인덱스에 들어 있어서다. 실제 테이블은 컬럼이 더 많아 이렇게 안 되지만, filesort 여부는 테이블을 다시 읽느냐와 상관없어 판정에는 영향이 없다고 봤다. filesort는 행을 인덱스 순서대로 꺼낼 수 있느냐의 문제이고, 커버링은 꺼낸 행의 나머지 컬럼을 테이블 본문에서 다시 읽느냐의 문제라 둘이 따로라고 판단했다. 넓은 테이블에서 이 판단이 맞는지는 재지 않았다.
(나)가 행을 덜 읽었다. EXPLAIN의 예측 행 수(rows)는 475~506으로 (가)-Q2의 예측 124~137보다 컸는데 실제로 읽은 행(r_rows)은 63~81로 더 적었다. 정렬 순서대로 읽다가 조건에 맞는 20개를 채우면 멈춰서다. 대신 (나)의 used_key_parts는 status, user_id에서 끝났다. 인덱스로 찾은 것은 등호 조건까지이고, amount 범위는 찾기에 쓰지 못하고 읽어 가며 한 행씩 걸렀다는 뜻이다. 다만 이건 amount 구간이 전체의 약 25%라서 나온 숫자다. 세 벌의 파라미터는 amount BETWEEN 10000 AND 35000, 40000 AND 65000, 70000 AND 95000이었고, amount가 0~99999 사이에 퍼져 있으니 폭 25000짜리 구간은 약 25%다. 구간이 좁으면 (나)는 20개를 채우려고 등호에 맞는 행 거의 전부를 읽어야 한다. (나)가 늘 낫다는 뜻이 아니다. 그 비교는 재지 않았다.
이 결과가 표준에 남기는 것 — 규칙은 범위와 정렬 키가 같은 컬럼일 때만 성립한다
규칙이 성립하는 것은 범위 조건이 정렬 키의 첫 컬럼에 걸릴 때다. 이때는 「범위 뒤에 정렬 키」가 사실상 한 컬럼으로 합쳐지고 filesort가 없다. 커서 페이징의 (created_at, id) > (?, ?)(먼저 created_at을 비교하고, created_at이 같을 때만 id를 비교해 「앞 페이지 마지막 행보다 뒤」를 고르는 조건)도 정렬 키 자체에 거는 범위라 이 경우에 가까워 보이지만, 그 쿼리 모양은 재지 않았다.
범위가 다른 컬럼이면 한 인덱스로 범위 접근과 정렬을 둘 다 받을 수 없다. 순서를 정하는 것은 둘 중 무엇을 인덱스에 맡기느냐는 선택이다.
- (가) 순서 — 범위로 좁혀 읽고 filesort를 감수한다. 범위가 좁을 때 유리해 보인다
- (나) 순서 — 정렬 순서대로 읽으며 범위를 거르고 LIMIT에서 멈춘다. 범위가 넓을 때 유리해 보인다
제안은 이렇다. 결정하지 않는다. 규칙 문장을 「등호 조건 컬럼을 앞에 둔다. 범위 조건이 정렬 키의 첫 컬럼이면 그 뒤에 정렬 키를 둔다. 범위 조건이 다른 컬럼이면 한 인덱스로 둘 다 받을 수 없으니, 슬로우 쿼리로 관측한 쿼리에서 (가)와 (나)를 EXPLAIN으로 비교해 고른다」로 고치는 안이다. 이 저장소의 증명 절차는 결과가 refuted면 지우지 않고 두 가지를 하게 한다. 표준 글의 그 원칙 밑에 > 반증:으로 시작하는 줄을 달아 반증된 결과를 드러내고, GitHub 이슈를 「규칙과 실험이 서로 어긋난다」는 뜻의 kind: 모순으로 열어 실험 결함인지 규칙 결함인지 따진다. 이슈는 #145와 합칠지까지 포함해 따진다.
정리
- 범위 = 정렬 키(Q1)면 (가) 순서에서 filesort가 없었다. 20행만 읽고 끝났다
- 범위 ≠ 정렬 키(Q2)면 (가) 순서에서 세 번 모두 filesort였다. 범위 뒤에 둔
created_at, id는 정렬에 쓰이지 않았다 - (나) 순서는 filesort가 없었지만 범위를 인덱스 접근에 쓰지 못했다. 둘 중 무엇이 빠른지는 범위 선택도(등호에 맞는 행 중 범위 조건에도 맞는 행의 비율, 이 실험에서는 약 25%)에 달렸을 것이고 재지 않았다
- 재지 않은 것 — 실행 시간, 범위 선택도를 바꿨을 때 (가)·(나)의 비용, 커버링이 아닌 넓은 테이블, 커서 조건
(created_at, id) > (?, ?), 범위 조건이 둘 이상인 경우, MariaDB 11.8 외 버전
자신만의 철학을 만들어가는 중입니다.
댓글남기기