DB 인덱스, 제대로 알고 쓰자 - 기본편
들어가며
“인덱스 걸었는데 왜 안 빨라지죠?”
주니어 때 한 번쯤은 해봤을 질문이에요. 저도 그랬고, 아마 대부분 그랬을 겁니다. 인덱스가 뭔지는 알겠는데, 정확히 어떻게 동작하는지, 왜 어떤 쿼리는 인덱스를 타고 어떤 쿼리는 안 타는지 명쾌하게 설명하기 어려웠어요.
오늘은 그 답답함을 좀 해소해보려고 합니다. 인덱스의 근본부터 차근차근 파볼게요.
인덱스가 뭔데?
가장 쉬운 비유는 책의 색인입니다.
500페이지짜리 기술서에서 “트랜잭션”이라는 단어를 찾고 싶다고 해볼게요. 두 가지 방법이 있습니다.
첫 번째, 1페이지부터 500페이지까지 한 장씩 넘기면서 찾는 방법. 운이 좋으면 앞쪽에서 찾겠지만, 최악의 경우 500페이지를 다 봐야 해요.
두 번째, 맨 뒤의 색인을 펼칩니다. “트랜잭션… p.127”이라고 적혀있어요. 바로 127페이지로 가면 됩니다. 끝이에요.
DB의 인덱스도 똑같은 원리입니다. 데이터를 빠르게 찾기 위한 별도의 자료구조를 만들어두는 거예요. 인덱스 없이 100만 건의 테이블에서 데이터를 찾으려면 100만 번 확인해야 할 수도 있습니다. 인덱스가 있으면? 수십 번의 비교만으로 끝나요.
B+Tree, 왜 이걸 쓸까?
면접에서 “인덱스가 어떤 자료구조로 되어있나요?”라고 물으면, “B+Tree요”라고 답하면 됩니다. 대부분의 RDBMS가 B+Tree를 사용해요.
근데 왜 하필 B+Tree일까요? 그냥 이진 트리나 해시 테이블 쓰면 안 되나?
B-Tree vs B+Tree
먼저 B-Tree(Balanced Tree)는 이렇게 생겼습니다.
1
2
3
4
5
[30, 70]
/ | \
[10, 20] [40, 50] [80, 90]
↓ ↓ ↓
Data Data Data
모든 노드에 키와 데이터가 함께 저장돼요. 중간 노드에서 원하는 키를 찾으면 바로 데이터를 반환할 수 있습니다.
B+Tree는 조금 다릅니다.
1
2
3
4
5
6
7
8
[30, 70] ← 내부 노드: 키만 저장
/ | \
[10, 20] [40, 50] [80, 90] ← 내부 노드: 키만 저장
| | |
↓ ↓ ↓
[10]→[20]→[30]→[40]→[50]→... ← 리프 노드: 실제 데이터
←── Linked List로 연결 ──→
두 가지 큰 차이점이 있어요.
- 데이터는 리프 노드에만 저장됩니다. 내부 노드는 키만 갖고 있어요.
- 리프 노드끼리 Linked List로 연결되어 있습니다.
이게 왜 DB에 더 좋을까?
핵심은 범위 검색에 있습니다.
WHERE price BETWEEN 10000 AND 50000 같은 쿼리를 생각해볼게요.
B-Tree라면 10000을 찾고, 다시 루트부터 10001을 찾고, 또 루트부터 10002를 찾고… 이걸 반복해야 합니다.
B+Tree는요? 10000을 찾은 다음, 리프 노드에서 옆으로 쭉 이동하면 돼요. 50000이 나올 때까지. 범위 검색이 압도적으로 빠릅니다.
또 하나, 내부 노드에 데이터가 없으니 한 노드에 더 많은 키를 담을 수 있어요. 디스크는 페이지(보통 8KB나 16KB) 단위로 읽는데, 한 번 읽을 때 키를 더 많이 가져오면 트리의 높이가 낮아집니다. 트리 높이가 낮아지면? 디스크 접근 횟수가 줄어들어요. 성능이 좋아지는 거죠.
클러스터드 인덱스 vs 논클러스터드 인덱스
MSSQL이나 MySQL(InnoDB)을 쓴다면 이 개념이 특히 중요합니다.
클러스터드 인덱스
클러스터드 인덱스는 좀 특별해요. 데이터 자체가 인덱스 순서대로 물리적으로 정렬되어 저장됩니다.
1
2
3
4
5
6
7
8
9
10
┌─────┬─────────┬──────────┬────────────┐
│ ID │ Name │ Email │ Phone │
├─────┼─────────┼──────────┼────────────┤
│ 1 │ 김철수 │ kim@... │ 010-1111 │
│ 2 │ 이영희 │ lee@... │ 010-2222 │
│ 3 │ 박민수 │ park@... │ 010-3333 │
│ 4 │ 최지영 │ choi@... │ 010-4444 │
└─────┴─────────┴──────────┴────────────┘
↑
ID 순서대로 물리적으로 정렬되어 있음
테이블당 딱 하나만 만들 수 있어요. 물리적 정렬 순서는 하나뿐이니까요. 보통 PK(Primary Key)에 자동으로 생성됩니다.
논클러스터드 인덱스
논클러스터드 인덱스는 별도의 공간에 인덱스 키와 데이터 위치(포인터)를 저장합니다.
1
2
3
4
5
6
7
8
9
10
11
Name 인덱스: 실제 테이블:
┌─────────┬─────────┐ ┌─────┬─────────┐
│ Name │ Pointer │ │ ID │ Name │
├─────────┼─────────┤ ├─────┼─────────┤
│ 김철수 │ ──────────────→ │ 1 │ 김철수 │
│ 박민수 │ ──────────────→ │ 3 │ 박민수 │
│ 이영희 │ ──────────────→ │ 2 │ 이영희 │
│ 최지영 │ ──────────────→ │ 4 │ 최지영 │
└─────────┴─────────┘ └─────┴─────────┘
↑
Name 가나다순 정렬 ID순 정렬 (클러스터드)
테이블당 여러 개 만들 수 있습니다. MSSQL은 최대 999개까지 가능하다고 해요. 물론 그렇게 많이 만들면 안 되지만요.
실제 동작 차이
1
2
3
4
5
6
7
8
9
10
11
12
-- ID가 클러스터드 인덱스, Name에 논클러스터드 인덱스가 있다고 가정
-- 쿼리 1: 클러스터드 인덱스 사용
SELECT * FROM Users WHERE ID = 3;
-- B+Tree에서 ID=3 찾으면 바로 그 위치에 모든 데이터가 있음
-- 1번의 탐색으로 끝
-- 쿼리 2: 논클러스터드 인덱스 사용
SELECT * FROM Users WHERE Name = '박민수';
-- Name 인덱스에서 '박민수' 찾음 → 포인터 획득
-- 포인터 따라가서 실제 데이터 행 접근 (추가 I/O!)
-- 이걸 "키 룩업" 또는 "북마크 룩업"이라고 함
논클러스터드 인덱스를 쓰면 추가적인 I/O가 발생해요. 이 점을 기억해두세요. 나중에 커버링 인덱스 얘기할 때 중요해집니다.
커버링 인덱스
쿼리 튜닝할 때 진짜 유용한 개념이에요.
커버링 인덱스란, 쿼리에 필요한 모든 컬럼이 인덱스에 포함되어 있어서 테이블에 접근하지 않고 인덱스만으로 결과를 반환하는 겁니다.
예시를 볼게요.
1
2
3
4
5
-- Orders 테이블에 customer_id 인덱스만 있는 상황
SELECT customer_id, order_date, total_amount
FROM Orders
WHERE customer_id = 1234;
이 쿼리가 실행되면:
- customer_id 인덱스에서 1234를 찾습니다
- 포인터로 실제 테이블에 가서 order_date, total_amount를 가져와요 (키 룩업!)
- 결과가 100건이면? 100번의 추가 I/O가 발생합니다
이걸 해결하려면요?
1
2
3
4
-- INCLUDE를 사용한 커버링 인덱스 (MSSQL)
CREATE INDEX IX_Orders_CustomerId
ON Orders (customer_id)
INCLUDE (order_date, total_amount);
이제 order_date와 total_amount도 인덱스 리프 노드에 저장돼요. 테이블에 갈 필요가 없어집니다. 키 룩업이 사라져요. 성능이 확 좋아집니다.
실행 계획에서 “Key Lookup”이 보이면 커버링 인덱스를 고려해볼 타이밍이에요.
복합 인덱스와 컬럼 순서
면접 단골 질문이기도 하고, 실무에서 실수하기 쉬운 부분입니다.
최좌선 접두사 원칙 (Leftmost Prefix)
복합 인덱스 (A, B, C) 순서로 만들었다면:
| 사용 가능 | 사용 불가능 |
|---|---|
| WHERE A = 1 | WHERE B = 1 |
| WHERE A = 1 AND B = 2 | WHERE C = 1 |
| WHERE A = 1 AND B = 2 AND C = 3 | WHERE B = 1 AND C = 2 |
왜 그럴까요? 인덱스가 실제로 어떻게 정렬되어 있는지 생각해보면 됩니다.
1
2
3
4
5
6
7
인덱스 (brand, category, price)의 정렬 상태:
Apple - Laptop - 1000
Apple - Laptop - 1500
Apple - Phone - 800
Samsung - Laptop - 900
Samsung - Phone - 700
brand가 같은 것들 안에서 category가 정렬되고, category가 같은 것들 안에서 price가 정렬돼요.
그래서 brand로 시작하면 범위를 빠르게 좁힐 수 있지만, category로만 검색하면 전체를 다 봐야 합니다. category끼리는 정렬되어 있지 않으니까요.
컬럼 순서 결정할 때
- 등호(=) 조건 컬럼을 앞에
- 카디널리티(고유값 개수) 높은 컬럼을 앞에
- 범위 조건은 뒤에 (범위 조건 이후 컬럼은 인덱스 효과가 떨어져요)
- 자주 사용되는 조건 컬럼을 앞에
이 정도만 기억해도 실무에서 크게 헤매지 않습니다.
인덱스가 안 타는 경우들
분명히 인덱스 만들었는데 왜 안 타는지 모르겠다면, 이 케이스들을 확인해보세요.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 1. 컬럼 가공 (함수, 연산)
WHERE YEAR(created_at) = 2024 -- 인덱스 안 탐
WHERE created_at >= '2024-01-01' -- 이렇게 바꾸세요
AND created_at < '2025-01-01'
-- 2. 암시적 형변환
WHERE phone_number = 01012345678 -- 문자열 컬럼에 숫자 넣으면
WHERE phone_number = '01012345678' -- 이렇게 해야 해요
-- 3. LIKE 앞쪽 와일드카드
WHERE name LIKE '%김%' -- Full Scan
WHERE name LIKE '김%' -- Index Range Scan
-- 4. 부정 조건
WHERE status != 'DELETED' -- 대부분 안 탐
WHERE status IN ('ACTIVE', 'PENDING') -- 긍정 조건으로 바꾸는 게 나아요
특히 1번, 컬럼 가공 실수는 정말 많이 합니다. YEAR(), MONTH(), SUBSTRING() 같은 함수를 WHERE절 컬럼에 걸면 인덱스를 못 타요. 조건을 변형해서 컬럼은 그대로 두고 값 쪽을 바꿔야 합니다.
인덱스는 공짜가 아니에요
마지막으로 중요한 얘기입니다. 인덱스를 무조건 많이 만든다고 좋은 게 아니에요.
장점은 명확합니다. SELECT가 빨라지고, ORDER BY와 JOIN도 최적화돼요.
하지만 단점도 있습니다. INSERT, UPDATE, DELETE가 느려져요. 데이터가 바뀔 때마다 인덱스도 같이 업데이트해야 하니까요. 그리고 인덱스도 저장 공간을 차지합니다.
읽기 위주 테이블이면 인덱스를 좀 넉넉하게 만들어도 괜찮아요. 하지만 쓰기가 많은 테이블이라면 신중해야 합니다.
MSSQL에서는 sys.dm_db_index_usage_stats를 조회하면 각 인덱스가 얼마나 사용되는지 확인할 수 있어요. user_seeks가 0인데 user_updates만 높은 인덱스가 있다면? 안 쓰면서 유지 비용만 나가는 인덱스입니다. 삭제를 고려해볼 만해요.
정리
오늘 다룬 내용을 정리하면 이렇습니다.
- 인덱스는 빠른 검색을 위한 별도 자료구조
- B+Tree를 쓰는 이유는 범위 검색 최적화와 디스크 I/O 효율
- 클러스터드는 테이블당 1개, 데이터 자체가 정렬됨
- 논클러스터드는 여러 개 가능, 키 룩업 비용 발생
- 커버링 인덱스로 키 룩업을 제거할 수 있음
- 복합 인덱스는 컬럼 순서가 중요 (최좌선 접두사 원칙)
- 인덱스가 안 타는 케이스들을 알아두세요
- 인덱스도 비용이에요, 무조건 많이 만들면 안 됨
다음 편에서는 실행 계획 읽는 법을 다뤄볼게요. 오늘 배운 인덱스가 실제로 잘 타고 있는지 확인하는 방법입니다.
그럼 다음에 또 봐요.