인덱스 - 왜 빠르고, 왜 만들어도 안 타는가

느린 쿼리를 발견했다. WHERE email = ?에 인덱스가 없길래 만들었다. 그런데 여전히 느리다. 실행 계획을 보니 인덱스를 안 탄다.

이런 일이 왜 생기는지 알려면 인덱스가 애초에 어떻게 빠른지부터 봐야 한다. 그게 뒤집히는 조건들이 곧 “안 타는 이유”이기 때문이다.

앞의 락 글에서 인덱스가 동시성에도 영향을 준다고 했다. 그 얘기의 뿌리도 여기 있다.

인덱스가 없으면

100만 건짜리 테이블에서 이메일 하나를 찾는다. 인덱스가 없으면 방법은 하나뿐이다. 처음부터 끝까지 다 본다. 운 좋으면 첫 줄에서 찾고, 없는 이메일이면 100만 줄을 다 읽고 나서 “없다”고 답한다.

이걸 풀스캔이라고 부른다. 데이터가 10배 늘면 시간도 10배 늘어난다.

왜 이진트리가 아닌가

찾는 걸 빠르게 하려면 정렬해두고 반씩 잘라 들어가면 된다. 학교에서 배운 이진 탐색이다. 100만 건이면 20번쯤 비교하면 끝난다. 20번이면 충분히 빨라 보인다.

그런데 데이터베이스는 이진트리를 안 쓴다. 이유는 디스크 때문이다.

메모리에서라면 20번 비교는 눈 깜짝할 사이다. 하지만 인덱스가 디스크에 있으면 얘기가 다르다. 앞 글에서 본 그것 때문이다. 디스크는 블록 단위로만 읽는다. 값 하나를 달라고 해도 8KB가 통째로 온다.

이진트리는 노드 하나에 값이 하나다. 값 하나 보려고 8KB를 읽고, 다음 노드로 가려고 또 8KB를 읽는다. 20번 내려가면 디스크를 20번 두드린다. 8KB를 읽어놓고 그 안의 값 하나만 쓰고 버리는 셈이다.

그래서 발상을 뒤집는다. 어차피 블록 하나를 통째로 읽을 거면, 그 블록을 값으로 꽉 채우자.

노드 하나에 값을 수백 개 담는다. 그러면 한 번 내려갈 때마다 갈래가 수백 개로 갈린다. 이게 B-tree다.

효과가 극적이다. 갈래가 200개라면 깊이 3짜리 트리가 200 × 200 × 200 = 800만 건을 담는다. 즉 디스크를 세 번만 두드리면 800만 건 중 하나를 찾는다. 이진트리로 23번 두드릴 걸 3번으로 줄인 것이다.

그리고 이 깊이는 잘 안 자란다. 데이터가 10배로 늘어도 깊이는 1 늘까 말까다. 테이블이 커져도 조회 시간이 거의 그대로인 이유가 이거다.

대가

인덱스는 공짜가 아니다. 두 가지를 낸다.

쓰기가 느려진다. INSERT 한 번에 테이블에 쓰고, 인덱스에도 쓴다. 인덱스가 5개면 6군데에 쓴다. 게다가 정렬을 유지해야 하니 중간에 끼워 넣고, 블록이 꽉 차면 쪼갠다.

공간을 먹는다. 인덱스도 결국 데이터다.

여기에 락 글에서 본 동시성 얘기가 붙는다. 인덱스가 없으면 UPDATE가 테이블을 전부 훑어야 하고, 그동안 다른 트랜잭션과 부딪힐 창이 넓어진다. MySQL InnoDB라면 훑으며 지나간 행까지 잠그니 3건을 고치자고 만 건을 잠근 뒤가 된다. 인덱스는 조회를 빠르게 하면서 동시에 잠그는 범위도 좁힌다.

만들었는데 왜 안 타나

이제 본론이다. 인덱스가 빠른 건 정렬되어 있기 때문이다. 정렬을 못 쓰게 되는 순간 인덱스는 무용지물이 된다.

컬럼을 가공하면 못 쓴다

-- 인덱스: created_at

-- ✗ 못 탄다
WHERE EXTRACT(YEAR FROM created_at) = 2026

-- ✓ 탄다
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

인덱스는 created_at 값으로 정렬돼 있지 EXTRACT(...) 결과로 정렬돼 있지 않다. 함수를 씌우는 순간 정렬 순서가 무의미해지고, DB는 모든 행에 함수를 적용해봐야 답을 안다. 그게 풀스캔이다.

WHERE price * 2 > 10000도, WHERE substr(code, 1, 3) = 'ABC'도 같은 이유로 못 탄다. 컬럼은 왼쪽에 벌거벗은 채로 둬야 한다.

복합 인덱스의 앞 컬럼을 안 쓰면 못 쓴다

(성, 이름) 순서로 만든 인덱스는 전화번호부와 같다.

전화번호부에서 “박씨”는 금방 찾는다. 성으로 정렬돼 있으니까. 하지만 “서연이라는 이름을 가진 사람” 을 찾으라면? 처음부터 끝까지 넘겨봐야 한다. 서연이는 김씨에도 있고 박씨에도 있고 흩어져 있다.

-- 인덱스: (성, 이름)
WHERE 성 = '박'                    -- ✓
WHERE 성 = '박' AND 이름 = '서연'   -- ✓
WHERE 이름 = '서연'                -- ✗ 선행 컬럼이 없다

그래서 복합 인덱스는 순서가 전부다. 컬럼을 어떤 순서로 넣느냐가 그 인덱스로 뭘 할 수 있는지를 정한다.

순서를 정하는 기준은 대체로 이렇다. 등호(=)로 쓰는 컬럼을 앞에, 범위(>, BETWEEN)로 쓰는 컬럼을 뒤에 둔다. 범위 조건이 걸리는 순간 그 뒤 컬럼들의 정렬은 흐트러지기 때문이다.

많이 읽을 거면 일부러 안 탄다

이게 제일 뜻밖이다. 인덱스가 있는데도 옵티마이저가 안 쓰기로 결정하는 경우가 있다.

인덱스로 찾으면 두 단계다. 인덱스에서 위치를 찾고, 그 위치로 가서 실제 행을 읽는다. 이 두 번째 단계가 여기저기 흩어진 곳을 하나씩 찾아가는 일이다.

조건에 맞는 게 10건이면 10번 찾아가면 된다. 싸다. 그런데 조건에 맞는 게 90만 건이면? 90만 번을 흩어진 채로 찾아다니느니 그냥 처음부터 순서대로 다 읽는 게 빠르다.

-- status가 'active'인 행이 전체의 90%라면
WHERE status = 'active'   -- 인덱스가 있어도 풀스캔이 낫다

그래서 값이 골고루 흩어지는 컬럼일수록 인덱스가 잘 듣는다. 이메일이나 주문번호는 좋고, 성별이나 활성 여부 같은 건 별로다. 옵티마이저는 통계를 보고 이걸 판단한다. 통계가 낡으면 판단도 틀린다.

컬럼 쪽에 형변환이 걸리면 못 쓴다

-- code 컬럼이 문자열인데
WHERE code = 12345   -- 숫자로 비교

DB가 알아서 형변환을 해주는데, 그 과정에서 컬럼 쪽에 변환이 걸리면 앞서 본 “컬럼을 가공한 것”과 같은 상황이 된다. 조용히 풀스캔으로 떨어지고, 쿼리는 멀쩡해 보인다. 찾기 어려운 축에 든다.

방향이 반대면 멀쩡하다. 숫자 컬럼에 문자열을 비교하면 변환이 리터럴 쪽에 걸리니 인덱스를 그대로 탄다. 그래서 규칙은 “타입이 다르면 안 된다”가 아니라 **“컬럼이 변환당하면 안 된다”**이다.

앞이 열린 LIKE는 못 쓴다

WHERE name LIKE '김%'    -- ✓ '김'으로 시작하는 구간으로 점프
WHERE name LIKE '%준'    -- ✗ 어디서 시작할지 알 수 없다

정렬은 앞에서부터 되어 있다. 앞을 모르면 출발점을 못 잡는다.

인덱스만으로 끝내기

조회하는 컬럼이 전부 인덱스 안에 있으면, 실제 행을 읽으러 갈 필요가 없다. 인덱스만 보고 답한다.

-- 인덱스: (user_id, created_at)
SELECT created_at FROM orders WHERE user_id = 42;
-- user_id로 찾고, created_at도 인덱스에 있으니 여기서 끝

“흩어진 곳을 하나씩 찾아가는” 그 두 번째 단계가 통째로 사라진다. 이런 인덱스를 커버링 인덱스라고 부른다. 자주 쓰는 조회가 있다면 필요한 컬럼을 인덱스에 얹는 것만으로 크게 빨라질 수 있다.

물론 인덱스가 뚱뚱해지니 쓰기 비용과 공간을 더 낸다. 늘 그렇듯 교환이다.

정렬도 인덱스가 대신한다

지금까지 인덱스를 찾는 도구로만 봤다. 그런데 인덱스에는 성질이 하나 더 있다. 이미 정렬돼 있다는 것이다.

그래서 조건에 안 쓰이더라도 값을 한다.

-- 인덱스: (user_id, created_at)
SELECT * FROM orders
 WHERE user_id = 42
 ORDER BY created_at DESC
 LIMIT 20;

user_id = 42 구간을 찾아가면, 그 안은 이미 created_at 순으로 줄 서 있다. 정렬을 할 필요가 없다. 뒤에서부터 20개만 읽고 끝낸다.

인덱스가 없거나 순서가 안 맞으면 어떻게 되나. 조건에 맞는 행을 전부 모아서 정렬한 다음 위에서 20개를 잘라낸다. 조건에 맞는 게 10만 건이면 10만 건을 정렬하고 20개를 쓴다. 나머지 99,980건을 정렬한 일은 통째로 버려진다.

LIMIT이 붙었는데도 느리다면 대개 이 상황이다. 적게 가져오는 것과 적게 일하는 것은 다르다.

정렬을 인덱스가 대신하려면 조건이 있다.

• 정렬 기준이 인덱스 순서와 맞아야 한다. (user_id, created_at) 인덱스는 user_id가 하나로 정해진 상태에서만 created_at 순으로 줄 서 있다. user_id 범위를 넓게 잡고 created_at으로 정렬하려 들면 순서가 섞인다
• 방향이 섞이면 안 된다. ORDER BY a ASC, b DESC처럼 엇갈리면 인덱스 하나로는 못 맞춘다

같은 이야기가 그룹핑에도 적용된다. GROUP BY도 결국 같은 값끼리 모으는 일이라, 이미 정렬돼 있으면 훨씬 싸게 끝난다.

결국 실행 계획

EXPLAIN으로 계획을 뽑아보면 인덱스를 타는지 아닌지가 그대로 나온다. 안 타고 있으면 위의 다섯 가지 중 무엇에 걸렸는지 찾으면 된다. 계획을 제대로 읽는 법은 뒤에 실행 계획 편에서 따로 다룬다.

인덱스는 만들고 끝나는 게 아니다. 안 쓰이는 인덱스는 쓰기 비용만 내고 아무것도 안 돌려준다. 주기적으로 사용 통계를 보고 안 쓰는 건 걷어내는 편이 낫다.

정리

• 빠른 이유는 정렬이다. 디스크가 블록 단위라서 노드를 뚱뚱하게 만든 게 B-tree고, 그래서 800만 건을 세 번 만에 찾는다.
• 안 타는 이유는 전부 정렬을 못 쓰게 되기 때문이다. 컬럼을 가공했거나, 복합 인덱스의 앞을 안 썼거나, 앞이 열린 LIKE거나, 타입이 어긋났거나.
• 인덱스가 있어도 많이 읽을 거면 안 타는 게 맞다. 옵티마이저가 그렇게 판단한 거라면 대체로 옳다.
• 조회를 빠르게 하는 동시에 잠그는 범위도 좁힌다. 성능과 동시성 양쪽에 걸린 문제다.