DB 인덱스: 동작 원리와 안 쓰이는 이유, 복합 인덱스 순서

들어가며

조회가 느려서 인덱스를 추가했는데 EXPLAIN 결과에 여전히 Seq Scan 이 나오는 경우가 있습니다. 그 컬럼에 인덱스가 있는데도 테이블 전체를 읽는다면 조건의 모양이나 복합 인덱스의 컬럼 순서를 먼저 확인해야 합니다.

이 글에서는 B-tree 인덱스가 실제로 어떤 구조로 값을 찾는지 정리합니다. 그 구조를 바탕으로 인덱스가 쓰이지 않는 조건과 복합 인덱스의 컬럼 순서를 정하는 기준을 설명합니다.

1. B-tree 인덱스의 구조

인덱스는 컬럼 값을 정렬해 별도 자료구조에 저장하고, 값마다 테이블 행의 위치를 함께 기록한 것입니다. PostgreSQL 과 MySQL(InnoDB)의 기본 인덱스는 B-tree 이며, 루트·브랜치·리프 페이지로 이루어진 균형 트리입니다.

루트와 브랜치 페이지에는 구간을 나누는 키와 하위 페이지 주소만 있습니다. 실제 값과 행 위치는 리프 페이지에 있습니다. PostgreSQL 은 리프에 행의 물리 위치(TID)를 저장하고, InnoDB 의 보조 인덱스는 기본 키 값을 저장합니다.

루트 키가 40, 80 이고 가운데 브랜치 키가 50, 65 라고 해 보겠습니다. user_id = 42 를 찾을 때는 루트에서 40 ≤ 42 < 80 이므로 가운데 브랜치로 내려갑니다. 브랜치에서 42 < 50 이므로 40~49 구간 리프로 가서 42 의 행 위치를 얻고, 그 위치로 테이블 행을 읽습니다.

한 페이지에 키가 수백 개씩 들어가므로 수백만 행 테이블에서도 트리 높이는 보통 3~4단계입니다. 리프 페이지끼리는 양방향으로 연결돼 있습니다. 그래서 BETWEEN 이나 > 같은 범위 조건은 시작 리프를 한 번 찾은 뒤 옆 리프로 이어서 읽으면 됩니다.

정렬돼 있다는 점이 빠른 탐색과 범위 조회를 모두 가능하게 합니다.

-- 인덱스 없음: 전체 행을 읽는다
EXPLAIN SELECT * FROM jobs WHERE user_id = 42;
--  Seq Scan on jobs
 
CREATE INDEX idx_jobs_user ON jobs (user_id);
--  Index Scan using idx_jobs_user on jobs

비용도 있습니다. 행을 넣거나 고칠 때마다 해당 테이블의 모든 인덱스에서 정렬 위치를 찾아 갱신해야 하므로 쓰기가 느려지고 저장 공간이 늘어납니다. 그래서 인덱스는 자주 조회되고 값이 잘 갈리는 컬럼에 만듭니다.

2. 인덱스가 쓰이지 않는 경우

B-tree 는 저장된 값 그대로, 앞부분부터 정렬돼 있습니다. 조건이 이 정렬 순서를 따라 탐색할 수 없는 모양이면 트리를 타고 내려갈 기준이 없어집니다.

컬럼을 함수로 가공한 조건

인덱스 리프에는 created_at 의 원래 값이 정렬돼 있습니다. date(created_at) 로 비교하면 인덱스에 없는 값으로 찾는 셈이어서 트리 탐색을 쓸 수 없습니다. 컬럼은 그대로 두고 범위로 비교하면 인덱스를 씁니다.

-- 인덱스를 못 쓴다
WHERE date(created_at) = '2026-10-28'
 
-- 컬럼은 그대로, 범위로 비교
WHERE created_at >= '2026-10-28'
  AND created_at <  '2026-10-29'

함수 조건이 꼭 필요하면 그 식으로 인덱스를 만듭니다(표현식 인덱스). CREATE INDEX ON users (lower(email)) 을 만들고, 쿼리도 lower(email) = ... 처럼 같은 식으로 작성합니다.

앞이 비어 있는 LIKE

리프에는 문자열이 첫 글자부터 사전순으로 정렬돼 있습니다. LIKE '뉴스%' 는 '뉴스' 로 시작하는 구간을 찾아 이어 읽으면 되지만, LIKE '%뉴스' 는 그런 구간이 없어서 전체를 확인해야 합니다.

LIKE '뉴스%' 에 맞는 값은 2~3번에 연속으로 모여 있고, LIKE '%뉴스' 에 맞는 값은 1, 4, 5번에 흩어져 있습니다. 흩어진 값은 시작점과 끝점을 정할 수 없어 트리 탐색으로 좁히지 못합니다.

PostgreSQL 에서는 앞이 고정된 LIKE 라도 인덱스가 C 정렬(collation)이거나 text_pattern_ops 로 만들어졌을 때만 B-tree 를 씁니다.

조건에 걸리는 행이 너무 많을 때

조건이 인덱스를 쓸 수 있는 모양이어도 플래너가 Seq Scan 을 고를 수 있습니다. 리프에서 행 위치를 얻은 뒤 행마다 테이블 페이지를 따로 읽어야 하는데(무작위 I/O), 결과가 많으면 이 비용이 순차로 전부 읽는 비용보다 커집니다.

플래너는 통계로 두 비용을 추정해 고릅니다. 통계가 오래됐다면 ANALYZE 로 갱신하고 EXPLAIN ANALYZE 로 실제 실행 시간을 확인합니다.

3. 복합 인덱스와 컬럼 순서

복합 인덱스는 여러 컬럼을 정한 순서대로 정렬합니다. 첫 컬럼으로 먼저 정렬하고, 첫 컬럼 값이 같은 행끼리 둘째 컬럼으로 정렬합니다.

(status, created_at) 인덱스의 리프에는 값이 다음 순서로 저장됩니다.

(status,  created_at)
(done,    2026-09-30)
(done,    2026-10-02)
(queued,  2026-09-28)
(queued,  2026-10-03)   ← status='queued' AND created_at > '2026-10-01' 시작점
(queued,  2026-10-05)   ← 여기까지 연속으로 읽고 끝
(running, 2026-09-29)
(running, 2026-10-04)

status = 'queued' 가 있으면 그 묶음 안에서 created_at 이 정렬돼 있어 시작점을 바로 찾습니다. created_at 조건만 있으면 해당 값이 done, queued, running 묶음마다 흩어져 있어 범위를 좁히기 어렵습니다. 이렇게 인덱스가 왼쪽 컬럼부터 차례로 쓰이는 것을 leftmost prefix 원칙이라고 합니다.

CREATE INDEX idx_jobs_st_ct ON jobs (status, created_at);
 
-- 잘 쓰임: 첫 컬럼은 =, 둘째는 범위
SELECT * FROM jobs
 WHERE status = 'queued'
   AND created_at > '2026-10-01';
 
-- 잘 안 쓰임: 첫 컬럼 조건이 없다
SELECT * FROM jobs
 WHERE created_at > '2026-10-01';

컬럼 순서는 조회 패턴으로 정합니다. 항상 = 로 걸리는 컬럼을 앞에, 범위 조건이나 ORDER BY 에 쓰는 컬럼을 뒤에 둡니다. 범위 조건 컬럼 뒤에 오는 컬럼은 정렬이 끊겨서 탐색 범위를 좁히는 데 쓰이지 못하기 때문입니다.

헷갈리기 쉬운 점

• 인덱스는 많을수록 좋다는 오해. 쓰기마다 모든 인덱스가 갱신돼 느려지고, 값 종류가 적은 컬럼은 플래너가 인덱스를 쓰지 않기도 합니다.
• Seq Scan 이 나오면 잘못됐다는 오해. 테이블이 작거나 결과가 전체의 상당 부분이면 전부 읽는 편이 실제로 빠릅니다.
• 자주 쓰는 컬럼이면 복합 인덱스 순서는 상관없다는 오해. = 조건 컬럼을 앞에, 범위 조건 컬럼을 뒤에 둬야 인덱스 하나로 범위를 끝까지 좁힐 수 있습니다.

면접에서 자주 받는 질문

정리

• B-tree 인덱스는 루트·브랜치·리프로 이루어진 정렬된 균형 트리입니다. 몇 단계만 내려가 행 위치를 찾고, 리프 연결을 따라 범위를 읽습니다. 대가로 쓰기 비용과 저장 공간이 늘어납니다.
• 컬럼을 가공하거나 앞이 빈 LIKE 를 쓰면 정렬 순서를 따라갈 수 없어 인덱스를 못 씁니다. 결과가 많으면 플래너가 일부러 Seq Scan 을 고릅니다.
• 복합 인덱스는 왼쪽 컬럼부터 쓰입니다. = 조건 컬럼을 앞에, 범위 컬럼을 뒤에 둡니다.

인덱스는 정렬된 트리이고, 조건이 그 정렬 순서를 따라갈 수 있을 때만 쓰입니다. 이 한 가지를 기준으로 보면 인덱스가 안 쓰이는 이유와 컬럼 순서를 함께 판단할 수 있습니다.