복합 인덱스 컬럼 순서: 등호 조건 먼저, 범위 조건 나중인 이유
주문 관리 화면 맨 위에 ‘결제 대기’ 숫자를 띄우는 작은 상자가 있었다. 최근 30일 동안 들어온 주문 가운데 아직 결제가 끝나지 않은 건수를 세어 보여 주는 칸이다. 처음 만들 때 (created_at, status) 순서로 복합 인덱스를 걸었다. 값의 종류가 많은 컬럼을 앞에 두라는 말을 어디선가 들었고, 날짜로 크게 자른 뒤 상태로 한 번 더 거르면 되겠다고 생각했다.
주문이 수백만 건으로 늘자 그 숫자 하나가 화면에서 가장 늦게 떴다. 실행 계획에는 인덱스를 쓴다고 분명히 찍혀 있었다. 인덱스를 타는데 왜 느린지 한동안 감을 잡지 못했다. 반쯤 홧김에 컬럼 순서를 뒤집어 (status, created_at)으로 바꾸자 문제가 사라졌다. 고치긴 했는데 이유는 설명하지 못했다.
아래는 그 이유를 나중에 직접 재 본 기록이다. 실험은 PostgreSQL로 했고 MySQL 이야기는 중간에 덧붙인다.
실험 준비: 주문 300만 건과 인덱스 두 개
주문 300만 건을 지난 1년에 고르게 흩뿌렸다. 상태는 네 가지이고 결제 대기(pending)는 2%만 되게 했다. 실제 서비스에서도 대기 상태는 금방 다른 상태로 넘어가서 비중이 작았다.
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
status text NOT NULL,
created_at timestamptz NOT NULL,
amount int NOT NULL
);
INSERT INTO orders (status, created_at, amount)
SELECT CASE WHEN r < 0.90 THEN 'done'
WHEN r < 0.95 THEN 'shipping'
WHEN r < 0.98 THEN 'cancelled'
ELSE 'pending' END,
now() - interval '365 days' + i * interval '365 days' / 3000000,
(random() * 100000)::int
FROM (SELECT i, random() AS r FROM generate_series(1, 3000000) AS i) s;
CREATE INDEX idx_status_created ON orders (status, created_at);
CREATE INDEX idx_created_status ON orders (created_at, status);
VACUUM ANALYZE orders;두 인덱스를 함께 두면 플래너가 나은 쪽 하나만 고르니 비교가 안 된다. 그래서 트랜잭션 안에서 한쪽 인덱스를 지우고 실행 계획을 본 다음 롤백했다. DROP INDEX는 트랜잭션 안에서도 쓸 수 있고, 롤백하면 인덱스가 되살아난다. 다만 롤백할 때까지 테이블에 가장 강한 잠금이 걸리므로 운영 DB에서는 하지 않는다.
BEGIN;
DROP INDEX idx_created_status; -- 반대쪽을 볼 때는 idx_status_created를 지운다
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM orders
WHERE status = 'pending'
AND created_at >= now() - interval '30 days';
ROLLBACK;쿼리가 count(*)만 하므로 두 경우 모두 Index Only Scan이 나와서 인덱스 페이지 수만 깔끔하게 비교할 수 있다. PostgreSQL 18부터는 EXPLAIN ANALYZE에 BUFFERS가 기본으로 붙고, 그 전 버전이라면 위처럼 직접 적어야 한다.
결과는 같은데 읽은 페이지는 40배 넘게 달랐다
| 인덱스 | 스캔 방식 | 읽은 버퍼 | 결과 |
|---|---|---|---|
(status, created_at) | Index Only Scan | 약 20 | 약 4,900 |
(created_at, status) | Index Only Scan | 약 950 | 약 4,900 |
숫자는 장비와 데이터에 따라 조금씩 다르겠지만 비율은 거의 그대로 나온다. 최근 30일 주문은 24만 건 남짓이고 그중 결제 대기는 2%다. (created_at, status) 인덱스는 24만 개 항목을 모두 넘기며 5천 개를 골라냈다. (status, created_at) 인덱스는 처음부터 그 5천 개 근처만 읽었다. 두 인덱스가 읽은 양의 차이가 대략 2%의 역수와 맞아떨어진다.
이 표를 처음 만들었을 때 좀 허탈했다. 몇 달 동안 그 작은 상자는 화면이 열릴 때마다 필요 없는 항목 23만여 개를 넘기고 있었다. 데이터가 적을 때는 그 낭비가 눈에 띄지 않았을 뿐이다.
B-tree 리프는 앞 컬럼부터 줄을 선다
B-tree 인덱스의 맨 아래층인 리프 페이지에는 항목이 정렬된 채 한 줄로 이어져 있다. 복합 인덱스라면 첫 컬럼으로 먼저 정렬하고, 첫 컬럼 값이 같은 항목끼리만 둘째 컬럼으로 다시 정렬한다. 성으로 먼저 줄을 세우고 같은 성 안에서만 이름순으로 세우는 전화번호부와 같다.
(status, created_at)에서는 pending 항목이 모두 한곳에 모여 있고, 그 안은 시간순이다. 루트에서 내려가 ‘pending이면서 30일 전’ 위치를 찾고, 거기서 오른쪽으로 읽다가 상태가 바뀌는 순간 멈추면 된다. 두 조건이 모두 시작점과 끝점을 정하는 데 쓰인다.
(created_at, status)에서는 사정이 다르다. 줄은 시간순으로 서 있고, 상태는 시각이 똑같은 항목끼리만 정렬된다. 그런데 주문 시각은 거의 겹치지 않으니 둘째 컬럼의 정렬은 사실상 아무 의미가 없다. pending 항목은 30일 치 줄 사이에 쉰 개에 하나꼴로 흩어져 있다. 시작점은 ‘30일 전’으로 찾을 수 있지만 끝점은 줄의 맨 끝이다. 그 사이를 하나씩 넘기며 상태를 확인하는 수밖에 없다.
범위 조건 뒤의 컬럼은 거르는 데만 쓰인다
PostgreSQL 문서는 이 규칙을 꽤 정확하게 적어 두었다. 앞쪽 컬럼의 등호 조건, 그리고 등호 조건이 없는 첫 컬럼에 걸린 부등호 조건까지가 훑을 범위를 줄인다. 그보다 오른쪽 컬럼의 조건은 인덱스 안에서 검사되니 테이블 방문은 아껴 주지만, 훑는 범위까지 줄여 준다는 보장은 없다.
Use The Index, Luke는 이 둘을 접근 조건(access predicate)과 필터 조건(filter predicate)으로 나눠 부른다. 접근 조건은 탐색의 시작과 끝을 정하고, 필터 조건은 그 범위 안에서 걸러 낼 뿐이다. 실험이 950페이지에서 끝난 것도 status가 인덱스 안에서 걸러진 덕분이다.
실행 계획만 봐서는 차이가 보이지 않는다
처음에 헤맨 이유는 실행 계획에 있었다. PostgreSQL은 두 경우 모두 Index Cond 줄에 created_at과 status 조건을 함께 적어 준다. 어느 조건이 범위를 정했고 어느 조건이 거르기만 했는지는 그 줄로 구분되지 않는다. 나는 그 줄만 보고 인덱스를 제대로 탄다고 믿었다. 둘을 가르는 건 Buffers 숫자다. 결과 행 수에 비해 읽은 페이지가 터무니없이 많으면 필터 조건이 일하고 있다고 의심한다.
MySQL을 쓰는 다른 서비스에서 같은 일을 한 번 더 겪었다. 이번엔 EXPLAIN의 key_len이 단서였다. (created_at, status) 인덱스인데 key_len이 DATETIME 컬럼 길이인 5에서 멈춰 있었다. 첫 컬럼까지만 범위 탐색에 쓰였다는 뜻이다. MySQL 문서에 따르면 비교 연산자가 =, <=>, IS NULL인 동안은 다음 키 부분으로 넘어간다. >, <, BETWEEN, LIKE 같은 연산자를 만나면 그 키 부분까지만 쓰고 멈춘다.
순서를 뒤집어 두는 게 맞을 때
등호 먼저, 범위 나중이 늘 답은 아니다. 인덱스 하나를 여러 쿼리가 나눠 쓰니 다른 쿼리가 무엇을 요구하는지도 봐야 한다.
- 날짜 범위만 거는 쿼리가 많다면
(status, created_at)은 그 쿼리를 잘 받아 주지 못한다. 첫 컬럼에 조건이 없으면 시작점을 찾을 수 없어서다. 이럴 때는created_at단일 인덱스를 따로 둔다. - 상태와 상관없이 최신 주문 20건을 뽑는
ORDER BY created_at DESC LIMIT 20같은 쿼리는created_at이 앞에 있어야 정렬 없이 끝난다. - PostgreSQL 18에는 B-tree 스킵 스캔이 들어왔다. 앞 컬럼에 조건이 없거나 부등호만 걸려 있어도 뒤 컬럼 조건을 쓸 수 있게 한 기능이다.
status처럼 값이 네 개뿐인 컬럼이 앞에 있으면 그 네 갈래를 건너뛰며 찾을 여지가 생긴다. 반대로created_at이 앞이면 값이 거의 다 달라서 건너뛸 덩어리가 없다. 스킵 스캔이 생겼다고 순서 고민이 사라지지는 않는다.
지금 인덱스를 만드는 순서
요즘은 인덱스를 만들기 전에 WHERE 절을 두 줄로 나눠 적는다. 한 줄에는 등호 조건을, 다른 줄에는 범위 조건을 적는다. 등호 컬럼을 앞에 세우고 범위 컬럼은 하나만 맨 뒤에 둔다. 범위 조건이 둘이면 그중 하나만 탐색에 쓰인다고 받아들이고, 범위를 더 좁히는 쪽을 고른다. 고객별 최신 주문을 뽑을 때 (user_id, created_at)을 거는 것도 같은 원리이고, 그룹별 최신 1건 뽑기에서 따로 다뤘다.
값의 종류가 많은 컬럼을 앞에 두라는 말은 이제 믿지 않는다. 등호로만 쓰이는 컬럼끼리라면 순서를 바꿔도 훑는 범위가 거의 같다. 범위 조건이 끼면 값의 종류 수보다 조건의 모양이 먼저다. 내가 created_at을 앞에 세운 이유가 바로 그 말이었으니 꽤 비싸게 배운 셈이다.
인덱스를 만든 뒤에는 꼭 BUFFERS를 본다. 결과가 5천 행인데 페이지를 천 장 가까이 읽었다면 어딘가 순서가 틀린 것이다. 그 상자를 고친 지 한참 지나서야 이유를 남에게 설명할 수 있게 됐다. 늦게 배운 만큼 지금은 숫자부터 확인한다.