인덱스는 “탔다”라는 한 단어로 성능을 설명하는 기능이 아니라, 정렬된 키에서 후보 범위를 찾고 필요한 값을 읽는 접근 경로입니다. 질의의 동등 조건과 범위 조건, 요구 정렬, 반환 컬럼을 한꺼번에 대입해 인덱스 페이지·원본 행·정렬 작업이 실제로 얼마나 발생하는지 판단해야 합니다.
인덱스 탐색·스캔·행 접근의 실제 비용
B-tree 계열 인덱스는 정렬된 키를 따라 시작 범위를 찾고 연속 구간을 읽게 합니다. 인덱스에서 행 위치를 찾은 뒤 반환 컬럼·가시성을 위해 테이블을 다시 읽을 수도 있습니다. 실제 비용은 탐색·스캔·추가 행 접근·정렬·반환 바이트의 합입니다.
복합 키의 순서
(customer_id, created_at, id)는 고객별로 묶고, 같은 고객 안에서 시각, 같은 시각 안에서 ID순으로 정렬합니다.
SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = :customer
ORDER BY created_at DESC, id DESC
LIMIT 20;
특정 고객의 구간으로 들어가 필요한 끝부분부터 읽을 수 있는 후보입니다. 하지만 전체 고객의 최신 주문을 같은 인덱스로 조회하면 앞선 고객 ID가 고정되지 않아 시각의 전역 순서가 아닙니다. 인덱스 방향·역방향 스캔·정렬 제거는 실제 엔진 계획으로 확인합니다.
다섯 키의 저장 순서와 실제 읽기 구간
설명을 위해 시각을 작은 정수로 줄여 보겠습니다. 복합 인덱스가 오름차순으로 다음 키를 저장하고 있다고 합시다. 괄호 안 순서는 (customer_id, created_at, id)입니다.
| 저장 순서 | 인덱스 키 | 고객 7의 최신 주문 조회에서 |
|---|---|---|
| 1 | (2, 90, 201) |
고객이 달라 제외 |
| 2 | (7, 10, 701) |
고객 7 구간의 앞부분 |
| 3 | (7, 20, 702) |
같은 시각 안에서 ID가 작음 |
| 4 | (7, 20, 703) |
고객 7 구간의 마지막, 먼저 반환 |
| 5 | (9, 15, 901) |
다음 고객 구간이므로 제외 |
고객 7의 최신 주문 두 개가 필요하면 적절한 끝 경계를 찾고 역방향으로 (7,20,703), (7,20,702)를 읽으면 됩니다. 반면 전체 고객 중 최신 주문은 시각 90인 고객 2의 주문입니다. 인덱스의 마지막 키가 전체 최신 주문이라고 볼 수 없는 이유가 이 작은 예에서도 드러납니다.
그림을 글로 읽기
- 고객 7 · 최신 두 건: ORDER BY 시각 DESC, ID DESC
- 복합 인덱스 구간: (7,20,703) → (7,20,702) · 고객 범위 안에서 역방향
- 필요한 원본 행: total_amount 조회 · 엔진별 가시성 확인
- 최종 두 행 반환
- 고객 7 · 최신 두 건 → 복합 인덱스 구간: 시작 경계 탐색
- 복합 인덱스 구간 → 필요한 원본 행: 행 위치로 접근
- 필요한 원본 행 → 최종 두 행 반환: 결과 구성
이 그림의 두 번째 단계만 보고 “인덱스를 탔으니 빠르다”고 끝내면 안 됩니다. 인덱스에서 몇 키를 읽었는지, 원본 행을 몇 번 읽었는지, 추가 정렬이 있었는지를 따로 봐야 합니다. 반환값은 두 개여도 필터 때문에 수많은 후보를 읽었다면 작업량은 두 행 조회가 아닙니다.
앞 컬럼 범위 조건과 뒤 컬럼의 전역 정렬 순서
(a,b) 인덱스에 (1,10), (1,100), (2,1), (2,20)이 순서대로 있다고 합시다. a=1이면 b는 10,100 순서지만 a BETWEEN 1 AND 2이면 b는 10,100,1,20입니다. 따라서 ORDER BY b를 생략할 수 있다는 결론이 나오지 않습니다. ORDER BY a,b와 ORDER BY b는 다른 요구입니다. 여러 구간 병합이나 별도 정렬이 필요한지는 엔진 계획으로 확인합니다.
a BETWEEN 1 AND 2처럼 앞 컬럼이 넓은 범위일 때도 뒤의 b 조건이 전혀 쓸모없는 것은 아닙니다. 엔진에 따라 b를 index condition·잔여 필터로 반영하거나 skip scan을 사용할 수 있지만, (1,10),(1,100),(2,1),(2,20)처럼 a 구간별 후보가 이어지면 뒤 조건을 활용하는 것과 ORDER BY b의 전역 순서를 얻는 것은 별개입니다.
따라서 선택도가 높은 열을 무조건 앞에 놓지 말고, 동등 조건으로 고정되는 prefix와 요구 정렬을 실제 plan에 대입해 후보 범위·최종 반환량·sort 여부를 비교합니다.
키셋 페이지의 조건
같은 고객의 다음 페이지는 마지막 (created_at,id)보다 작은 키를 찾습니다. 같은 시각의 주문을 구분하는 ID가 있어야 누락·중복 경계를 정할 수 있습니다.
created_at < last_time
OR (created_at == last_time AND id < last_id)
정렬 방향이 섞이면 비교 방향도 맞춰야 합니다. 키셋은 큰 OFFSET 비용을 줄이는 접근 방식이지, 여러 HTTP 요청의 데이터를 고정 snapshot으로 만드는 기능은 아닙니다. 정렬 키 수정·삭제·복제 지연의 관찰 계약을 별도로 정합니다.
선택도와 lookup
customer_id로 최신 주문 20개를 반환하는 질의도, 질의·인덱스 조합에 따라 인덱스가 최종 20개보다 많은 후보를 훑고 각 후보의 원본 페이지를 다시 읽으면 테이블을 순차 scan하는 것보다 비쌀 수 있습니다. 따라서 실행 계획에서 seek나 scan이라는 이름만 보지 말고, 인덱스 후보·원본 행 접근·최종 반환 행을 나란히 세어 실제 읽은 범위와 비용을 비교합니다.
covering 인덱스는 SELECT에 필요한 값을 인덱스에서 모두 읽을 수 있어 원본 행 lookup을 줄이는 형태입니다. 그래도 PostgreSQL처럼 MVCC 가시성을 확인하려고 heap 접근이 남을 수 있으므로, 실행 계획에서 실제 원본 접근이 남는지 따로 봅니다. INCLUDE·클러스터링 키·heap locator가 저장되는 방식은 제품별로 다르므로 해당 엔진의 인덱스 정의와 plan을 함께 비교합니다.
특수 인덱스의 조건
- 부분 인덱스:
status='pending'같은 작은 집합만 저장합니다. 질의가 그 집합 안이라는 것을 엔진이 판단할 수 있어야 합니다. - 함수·표현식 인덱스:
lower(email)같은 반복 변환을 저장합니다. 정규화·collation·함수 제약과 질의 표현을 맞춥니다. - 해시 인덱스: 동등 비교에 맞지만 대소 순서를 보존하지 않아 일반적인 범위·정렬을 대신하지 않습니다.
해시 버킷 후보와 실제 일치 행 수
해시 인덱스는 키를 버킷 후보로 바꾸지만 대소 순서를 보존하지 않습니다. 충돌한 후보는 원래 키의 동등성을 다시 검사해야 합니다. 같은 실제 키가 아주 많은 경우와 서로 다른 키가 같은 해시 버킷에 몰리는 경우는 다른 편중이며, 둘 다 많은 후보·행 접근을 만들 수 있습니다.
가상의 해시 함수 h(k)=k mod 4에서 키 1·5·9·13은 같은 버킷에 모입니다. 키 9 하나를 찾더라도 버킷 내부 후보를 확인해야 하고, 범위 5~10은 해시값의 연속 구간으로 바뀌지 않습니다. 실제 DB의 해시 저장·overflow page·버킷 확장 규칙은 제품별로 다르므로 이 작은 함수를 실제 엔진 구현이라고 보지 않습니다.
평균 O(1)은 자료구조 탐색의 제한된 모형이지 디스크·가시성·반환 바이트·동시 경합을 포함한 질의의 상수 지연 보장이 아닙니다. 고빈도 키·충돌 후보·버킷 재구성 시점과 보통 키의 논리 읽기·행 lookup·쓰기 p99를 나누어 B-tree와 비교합니다. 테스트 데이터는 관리하는 격리 환경에서만 사용합니다.
실행 계획 읽는 순서
- 입력·바인드·격리·데이터 크기를 고정합니다.
- 추정 행과 실제 행의 차이를 봅니다.
- 읽은 행과 최종 반환 행, lookup·sort·spill을 확인합니다.
- CPU·I/O·잠금·연결 대기를 구분합니다.
- 작은 고객·큰 고객·cold/warm cache·동시 쓰기로 재검증합니다.
컬럼 통계가 최신이어도 도시와 우편번호처럼 상관된 조건은 독립 선택도 가정 때문에 틀릴 수 있습니다. 통계·쿼리 구조·인덱스 중 무엇이 원인인지 구분합니다.
후보 실행 계획을 비교할 때 동일한 바인드 값으로 고객 한 명의 최신 20건과 전체 고객의 최신 20건을 각각 실행하고, index entry 수·원본 행 접근 수·sort·최종 행 수를 나란히 기록하십시오. 예상 결과는 첫 질의에서는 고객 prefix가 좁은 구간을 만들 수 있지만 두 번째 질의에서는 같은 인덱스가 전역 created_at 순서를 자동으로 제공하지 않는다는 것입니다.
인덱스 쓰기 비용과 읽기량 비교
인덱스마다 삽입·삭제·갱신 시 페이지·로그·메모리를 사용합니다. 반환 컬럼을 무조건 전부 넣으면 읽기 lookup은 줄어도 쓰기와 cache가 악화될 수 있습니다. 온라인 생성도 시작·종료 잠금·디스크·로그 비용을 없애지 않습니다.
작은 가상 주문 테이블에 고객별 편중을 만들고 인덱스 유무·순서를 바꾸어 결과와 실제 읽기량을 비교해 보세요. 반환 결과가 같은지 먼저 확인한 뒤 성능을 판단합니다.