9장 : 인덱스
인덱스의 개념과 필요성
쿼리가 느리다는 문제의 80%는 인덱스로 해결됩니다. 하지만 인덱스를 아무 데나 걸면 오히려 느려집니다 — 원리를 알아야 올바른 결정을 할 수 있습니다.
인덱스는 데이터베이스 성능의 핵심 요소입니다. 잘 설계된 인덱스는 수십 초 걸리던 쿼리를 밀리초 단위로 단축시키고, 잘못 설계된 인덱스는 쓰기 성능을 저하시키고 디스크 공간을 낭비합니다.
인덱스란
인덱스(Index)는 데이터베이스 테이블의 검색 속도를 높여주는 자료구조입니다. 책의 색인(찾아보기)과 같은 원리입니다.
책에서 "트랜잭션" 단어를 찾으려면?
방법 1: 1페이지부터 끝까지 넘기면서 찾기 → Full Table Scan
방법 2: 색인(Index)에서 "트랜잭션 ... p.245" 확인 → Index Scan
데이터가 1,000만 건이면?
Full Scan: 수십 초
Index Scan: 수 밀리초인덱스는 검색 대상 컬럼의 값과 해당 행의 위치(ROWID)를 쌍으로 저장합니다. 이때 인덱스의 값은 정렬된 상태로 유지됩니다. 정렬되어 있기 때문에 이진 탐색이나 트리 탐색 같은 효율적인 알고리즘을 사용할 수 있습니다.
┌────────────────────────────────────────────────────────┐
│ 테이블 (users) │
│ ┌────┬──────────┬────────┬─────┐ │
│ │ ID │ name │ email │ age │ │
│ ├────┼──────────┼────────┼─────┤ │
│ │ 1 │ 김철수 │ a@t.co │ 25 │ ← ROWID: 1 │
│ │ 2 │ 이영희 │ b@t.co │ 30 │ ← ROWID: 2 │
│ │ 3 │ 박민수 │ c@t.co │ 22 │ ← ROWID: 3 │
│ │ 4 │ 최지은 │ d@t.co │ 28 │ ← ROWID: 4 │
│ └────┴──────────┴────────┴─────┘ │
│ │
│ 인덱스 (idx_users_email) │
│ ┌──────────┬────────┐ │
│ │ email │ ROWID │ ← 정렬됨! │
│ ├──────────┼────────┤ │
│ │ a@t.co │ 1 │ │
│ │ b@t.co │ 2 │ │
│ │ c@t.co │ 3 │ │
│ │ d@t.co │ 4 │ │
│ └──────────┴────────┘ │
└────────────────────────────────────────────────────────┘Full Table Scan vs Index Scan
Full Table Scan
테이블의 모든 행을 처음부터 끝까지 순차적으로 읽는 방식입니다. 인덱스가 없거나 인덱스를 사용할 수 없을 때 이 방식으로 검색합니다.
SELECT * FROM users WHERE email = 'test@example.com';
인덱스 없음
┌───┬──────────────┐ 검색 방식:
│ 1 │ a@test.com │ 1번 행 → 아님
│ 2 │ b@test.com │ 2번 행 → 아님
│ 3 │ c@test.com │ 3번 행 → 아님
│ . │ ... │ ...
│ N │ test@example │ N번 행 → 찾음!
└───┴──────────────┘
→ 최악의 경우 N번 비교 (O(N))1,000만 행 테이블에서 1건을 찾으려면 최악의 경우 1,000만 번 비교해야 합니다. 디스크 I/O가 수반되므로 실제로는 수십 초 이상 걸릴 수 있습니다.
Index Scan
인덱스를 사용하여 필요한 행의 위치를 빠르게 찾는 방식입니다. B+Tree 인덱스의 경우 로그 시간에 검색이 완료됩니다.
email 컬럼에 인덱스 생성 후
인덱스 (B+Tree)
[m]
/ \
[d] [t]
/ \ / \
[a,b,c] [n,p] [t,u,z]
│
└→ test@example → ROWID: 238
→ 3~4번 비교로 찾음 (O(log N))1,000만 행이라도 B+Tree에서는 약 23번(log₂(10,000,000) ≈ 23)의 비교만으로 원하는 행을 찾을 수 있습니다.
검색 방식 성능 비교
| 건수 | Full Table Scan | Index Scan (B+Tree) |
| ----------- | --------------- | ------------------- |
| 1,000 | 1,000 | ~10 |
| 100,000 | 100,000 | ~17 |
| 1,000,000 | 1,000,000 | ~20 |
| 10,000,000 | 10,000,000 | ~23 |
| 100,000,000 | 100,000,000 | ~27 |데이터가 많을수록 인덱스의 효과는 극적으로 커집니다.
인덱스의 종류
주요 인덱스 분류
┌─────────────────────────────────────────────────────────┐
│ 구조에 따른 분류 │
│ * B-Tree / B+Tree: 가장 일반적, 범위 검색 지원 │
│ * Hash: 등가 비교만 가능, O(1) 검색 │
│ * Bitmap: 카디널리티가 낮은 컬럼 (성별 등) │
│ * GiST/GIN: PostgreSQL, 전문 검색/공간 데이터 │
├─────────────────────────────────────────────────────────┤
│ 고유성에 따른 분류 │
│ * Unique Index: 중복 값 불허 (PK에 자동 생성) │
│ * Non-Unique Index: 중복 값 허용 │
├─────────────────────────────────────────────────────────┤
│ 컬럼 수에 따른 분류 │
│ * 단일 컬럼 인덱스: 하나의 컬럼 │
│ * 복합 인덱스: 여러 컬럼을 조합 (컬럼 순서 중요!) │
├─────────────────────────────────────────────────────────┤
│ 특수 인덱스 │
│ * Clustered Index: 데이터의 물리적 순서를 결정 │
│ * Covering Index: 쿼리에 필요한 모든 컬럼 포함 │
│ * Partial Index: 조건에 맞는 행만 인덱싱 │
│ * Function-Based Index: 함수 결과에 대한 인덱스 │
└─────────────────────────────────────────────────────────┘Clustered vs Non-Clustered Index
Clustered Index (클러스터형)
* 테이블당 1개만 가능
* 데이터가 인덱스 순서로 물리적으로 정렬됨
* PK에 자동 생성 (SQL Server, InnoDB)
* 범위 검색에 매우 유리 (연속된 페이지 읽기)
Non-Clustered Index (비클러스터형)
* 테이블당 여러 개 가능
* 인덱스와 데이터가 별도로 저장됨
* 인덱스에서 ROWID/포인터로 데이터 행을 참조
* 랜덤 I/O 발생 (다수 행 접근 시 느릴 수 있음)Clustered = 사전 (단어가 알파벳 순으로 정렬되어 있음)
Non-Clustered = 책의 색인 (색인은 정렬, 본문은 별도)인덱스 생성과 관리
-- 기본 인덱스 생성
CREATE INDEX idx_users_email ON users (email);
-- 유니크 인덱스
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);
-- 복합 인덱스 (컬럼 순서 중요!)
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date DESC);
-- 부분 인덱스 (PostgreSQL)
CREATE INDEX idx_active_users ON users (email) WHERE status = 'active';
-- 함수 기반 인덱스
CREATE INDEX idx_users_lower_email ON users (LOWER(email));-- 인덱스 삭제
DROP INDEX idx_users_email;
-- 인덱스 목록 조회 (PostgreSQL)
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';
-- 인덱스 목록 조회 (MySQL)
SHOW INDEX FROM users;
-- 인덱스 재구성 (PostgreSQL)
REINDEX INDEX idx_users_email;
-- 인덱스 재구성 (SQL Server)
ALTER INDEX idx_users_email ON users REBUILD;인덱스의 비용
인덱스는 공짜가 아닙니다. INSERT, UPDATE, DELETE가 발생할 때마다 인덱스도 갱신해야 합니다.
| 비용 항목 | 설명 | 영향 |
|---|---|---|
| 쓰기 오버헤드 | 데이터 변경 시 인덱스도 재정렬 | INSERT/UPDATE/DELETE 느려짐 |
| 저장 공간 | 인덱스 자체가 디스크를 차지 | 테이블 크기의 10~30% 추가 |
| 유지 비용 | 인덱스 단편화 시 리빌드 필요 | 주기적 관리 필요 |
| 옵티마이저 부담 | 인덱스가 많으면 최적 계획 탐색 시간 증가 | 쿼리 파싱 느려짐 |
┌──────────────────────────────────────────────────────┐
│ 인덱스 0개: INSERT 10ms, SELECT 5000ms │
│ 인덱스 1개: INSERT 12ms, SELECT 3ms ← 최적 │
│ 인덱스 3개: INSERT 18ms, SELECT 3ms │
│ 인덱스 10개: INSERT 50ms, SELECT 3ms ← 쓰기 악화 │
└──────────────────────────────────────────────────────┘
읽기 위주: 인덱스 많아도 괜찮음
쓰기 위주: 인덱스 최소한으로 유지인덱스를 걸어야 하는 경우 vs 걸면 안 되는 경우
| 인덱스 추천 | 인덱스 비추천 |
|---|---|
| WHERE 조건에 자주 사용되는 컬럼 | 데이터가 적은 테이블 (수백 건) |
| JOIN 조건 컬럼 (FK) | 카디널리티가 낮은 컬럼 (성별 등) |
| ORDER BY, GROUP BY 대상 | INSERT가 매우 빈번한 테이블 |
| UNIQUE 제약이 필요한 컬럼 | 거의 조회하지 않는 컬럼 |
| 선택도(Selectivity)가 높은 컬럼 | 테이블 대부분을 읽어야 하는 경우 |
카디널리티와 선택도
카디널리티(Cardinality)란 컬럼 값의 고유한 수입니다. 주민번호는 카디널리티가 높고(모두 다름), 성별은 낮습니다(M/F). 카디널리티가 높을수록 인덱스 효과가 좋습니다.
선택도(Selectivity)는 전체 행 중 조건으로 선택되는 행의 비율입니다. 선택도가 낮을수록(적은 행을 선택할수록) 인덱스가 효과적입니다.
테이블: users (1,000,000행)
| 컬럼 | 고유 값 수 | 카디널리티 | 선택도 | 인덱스 효과 |
| ------------ | ---------- | ---------- | ------- | ----------- |
| user_id (PK) | 1,000,000 | 최고 | 0.0001% | 최고 |
| email | 1,000,000 | 최고 | 0.0001% | 최고 |
| phone | 990,000 | 높음 | 0.0001% | 높음 |
| city | 200 | 중간 | 0.5% | 중간 |
| status | 3 | 낮음 | 33% | 낮음 |
| gender | 2 | 최저 | 50% | 최저 |일반적으로 선택도 5~10% 이하일 때 인덱스가 효과적이며, 그 이상이면 Full Table Scan이 오히려 빠를 수 있습니다.
복합 인덱스
두 개 이상의 컬럼으로 만든 인덱스입니다. 컬럼 순서가 매우 중요합니다.
CREATE INDEX idx_orders ON orders (user_id, order_date);인덱스: (user_id, order_date)
user_id | order_date | ROWID
--------|------------|------
1 | 2024-01-01 | 101
1 | 2024-02-15 | 102
1 | 2024-03-20 | 103
2 | 2024-01-05 | 201
2 | 2024-02-10 | 202
3 | 2024-01-15 | 301
→ user_id로 먼저 정렬, 같은 user_id 내에서 order_date로 정렬선행 컬럼 규칙
복합 인덱스는 왼쪽부터 순서대로 사용됩니다. 중간 컬럼을 건너뛸 수 없습니다.
-- 인덱스: (user_id, order_date, status)
-- ✅ 사용 가능
WHERE user_id = 1 -- a만
WHERE user_id = 1 AND order_date = '2024-01-01' -- a, b
WHERE user_id = 1 AND order_date = '2024-01-01' AND status = 'completed' -- a, b, c
-- ⚠️ 부분 사용 (user_id만 인덱스 사용)
WHERE user_id = 1 AND status = 'completed' -- a, c (b 건너뜀!)
-- ❌ 사용 불가
WHERE order_date = '2024-01-01' -- b만 (a 없음!)
WHERE status = 'completed' -- c만
WHERE order_date = '2024-01-01' AND status = 'completed' -- b, c (a 없음!)복합 인덱스 설계 원칙
┌────────────────────────────────────────────────────────┐
│ 1. 등가 조건 (=) 컬럼을 먼저 │
│ WHERE status = 'active' AND created_at > '2024-01' │
│ → (status, created_at) │
├────────────────────────────────────────────────────────┤
│ 2. 카디널리티가 높은 컬럼을 먼저 (일반적으로) │
│ (user_id, status) > (status, user_id) │
├────────────────────────────────────────────────────────┤
│ 3. 자주 사용되는 WHERE 조건 조합을 고려 │
│ 쿼리 패턴을 분석하여 가장 많이 사용되는 조합 │
├────────────────────────────────────────────────────────┤
│ 4. ORDER BY / GROUP BY 컬럼도 고려 │
│ 인덱스 순서와 정렬 순서가 일치하면 정렬 비용 제거 │
└────────────────────────────────────────────────────────┘인덱스를 사용하지 못하는 경우
인덱스가 있어도 사용되지 않는 경우가 많습니다. 이를 이해하는 것이 매우 중요합니다.
-- 1. 함수를 적용하면 인덱스 사용 불가
SELECT * FROM users WHERE UPPER(email) = 'TEST@EXAMPLE.COM';
-- 해결: 함수 기반 인덱스 또는 컬럼 원본 값으로 비교
-- 2. 연산을 적용하면 인덱스 사용 불가
SELECT * FROM orders WHERE YEAR(order_date) = 2024;
-- 해결: 범위 조건으로 변환
SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
-- 3. 암시적 타입 변환
SELECT * FROM users WHERE phone = 01012345678; -- phone이 VARCHAR
-- 해결: 올바른 타입으로 비교
SELECT * FROM users WHERE phone = '01012345678';
-- 4. LIKE 앞부분 와일드카드
SELECT * FROM users WHERE name LIKE '%철수'; -- 인덱스 사용 불가
SELECT * FROM users WHERE name LIKE '김%'; -- 인덱스 사용 가능
-- 5. OR 조건 (경우에 따라)
SELECT * FROM users WHERE email = 'a@t.co' OR name = '김철수';
-- 각 컬럼에 개별 인덱스 필요 (Index Merge)
-- 6. NOT, != 조건
SELECT * FROM users WHERE status != 'deleted';
-- 대부분의 행을 선택하므로 Full Scan이 더 효율적실행 계획으로 인덱스 확인
-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
-- Index Scan using idx_users_email on users
-- Index Cond: (email = 'test@example.com')
-- Execution Time: 0.05 ms
-- MySQL
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- type: ref → 인덱스 사용
-- type: ALL → Full Table Scan (인덱스 미사용)
-- 주요 접근 방식 (MySQL)
-- const: PK/유니크 인덱스로 1건 조회
-- ref: 인덱스로 여러 건 조회
-- range: 인덱스 범위 스캔
-- index: 인덱스 전체 스캔
-- ALL: Full Table Scan (최악)┌──────────────────────────────────────────────────────┐
│ 좋은 실행 계획 │
│ * type: const, ref, range │
│ * key: 인덱스 이름이 표시됨 │
│ * rows: 적은 수 │
│ * Extra: Using index (Covering Index!) │
├──────────────────────────────────────────────────────┤
│ 나쁜 실행 계획 │
│ * type: ALL (Full Table Scan) │
│ * key: NULL (인덱스 미사용) │
│ * rows: 테이블 전체 행 수 │
│ * Extra: Using filesort, Using temporary │
└──────────────────────────────────────────────────────┘Covering Index
쿼리에 필요한 모든 컬럼이 인덱스에 포함되어 있으면, 테이블 데이터를 읽지 않고 인덱스만으로 결과를 반환할 수 있습니다. 이를 Covering Index라 합니다.
-- 인덱스: (user_id, order_date)
-- 이 쿼리는 인덱스만으로 응답 가능 (테이블 접근 불필요)
SELECT user_id, order_date FROM orders WHERE user_id = 1;
-- EXPLAIN에서 "Using index" 표시 → Covering Index 적용INCLUDE 절 (PostgreSQL, SQL Server)
인덱스 검색에는 사용하지 않지만 Covering Index를 위해 추가 컬럼을 포함할 수 있습니다.
-- 검색은 user_id로, 결과에 name도 인덱스에서 바로 반환
CREATE INDEX idx_orders_covering
ON orders (user_id) INCLUDE (order_date, total_amount);인덱스 설계 실전 예시
테이블: orders (주문)
주요 쿼리 패턴
1. 특정 고객의 주문 조회: WHERE user_id = ?
2. 기간별 주문 조회: WHERE order_date BETWEEN ? AND ?
3. 고객의 최근 주문: WHERE user_id = ? ORDER BY order_date DESC LIMIT 5
4. 상태별 주문 수: WHERE status = ? GROUP BY user_id
인덱스 전략
* idx_orders_user_date: (user_id, order_date DESC)
→ 쿼리 1, 3 커버
* idx_orders_date: (order_date)
→ 쿼리 2 커버
* idx_orders_status: (status, user_id)
→ 쿼리 4 커버
* PK: order_id (자동 인덱스)* 쿼리 패턴 분석이 최우선 (어떤 쿼리가 자주 실행되는지)
* 인덱스는 읽기 최적화, 쓰기 비용 발생 → 균형 필요
* 복합 인덱스로 여러 쿼리를 하나의 인덱스로 커버
* 인덱스 수는 테이블당 3~5개 권장 (상황에 따라 다름)
* 주기적으로 사용되지 않는 인덱스 제거인덱스 모니터링
운영 환경에서는 인덱스의 사용 현황을 주기적으로 점검해야 합니다. 사용되지 않는 인덱스는 쓰기 성능만 저하시키므로 제거하는 것이 좋습니다.
-- 인덱스별 스캔 횟수 확인
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
-- idx_scan = 0인 인덱스는 사용되지 않는 인덱스 → 삭제 후보SELECT indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;핵심 정리
┌────────────────────────────────────────────────────────┐
│ 인덱스 = 검색 속도를 높이는 정렬된 자료구조 │
├────────────────────────────────────────────────────────┤
│ 검색 성능 │
│ Full Table Scan: O(N) — 전체 탐색 │
│ Index Scan: O(log N) — 트리 탐색 │
│ 1,000만 건: 10,000,000번 vs 23번 │
├────────────────────────────────────────────────────────┤
│ 인덱스 종류 │
│ B+Tree: 범위 검색 가능 (가장 일반적) │
│ Hash: 등가 비교만, O(1) │
│ Clustered: 테이블당 1개, 물리적 정렬 │
│ Covering: 인덱스만으로 쿼리 응답 │
├────────────────────────────────────────────────────────┤
│ 복합 인덱스 규칙 │
│ 선행 컬럼부터 순서대로 사용 │
│ 등가 조건 먼저, 범위 조건 나중에 │
│ 카디널리티 높은 컬럼 먼저 배치 │
├────────────────────────────────────────────────────────┤
│ 인덱스 무시되는 경우 │
│ 함수 적용, 타입 변환, LIKE '%...', NOT/!= │
├────────────────────────────────────────────────────────┤
│ 비용: 쓰기 오버헤드 + 디스크 공간 + 유지 비용 │
│ 읽기 위주 → 인덱스 적극 활용 │
│ 쓰기 위주 → 인덱스 최소화 │
└────────────────────────────────────────────────────────┘다음 절에서는 인덱스의 핵심 자료구조인 B-Tree와 B+Tree를 다루겠습니다.