같은 query 가 어떤 column 엔 0.5ms, 다른 column 엔 5,000ms. 차이는 index. 그러나 "index 추가" 가 만능 X — 어떤 index 가 어떤 query 에 효과? 왜 LIKE '%foo' 는 index 를 못 쓰나? composite index 의 column 순서가 왜 중요? 이 가이드는 B-tree index 의 내부, query planner 가 index 를 선택하는 방식, 흔한 index 안티패턴을 정리한다.
왜 index 가 필요한가
-- users table, 100 만 row
SELECT * FROM users WHERE email = 'alice@example.com';
WITHOUT index:
- full table scan (100 만 row 읽기) ≈ 1-5 초
WITH index on email:
- B-tree lookup → row pointer → fetch row ≈ < 1msIndex 는 책의 색인과 같음. 책 전체를 처음부터 읽지 않고 색인의 page 번호로 직접 찾기.
B-tree — 가장 흔한 index 구조
Balanced tree. PostgreSQL / MySQL / SQLite / Oracle 의 default index type:
[30, 70]
/ | \
[10, 20] [40, 50, 60] [80, 90, 100]
↓ ↓ ↓
leaf node leaf node leaf node
(실제 데이터 pointer)
search "55":
1. root [30, 70] → 30 < 55 < 70 → middle child
2. [40, 50, 60] → 50 < 55 < 60 → 60 다음 pointer
3. leaf → 55 의 row pointer 반환100 만 row 의 B-tree depth ≈ log_100 (per node) (1,000,000) ≈ 3. 3 page read 로 lookup. 매우 빠름.
B-tree 의 강점 — range query 도 효율적. leaf 가 정렬된 linked list 라 WHERE age BETWEEN 20 AND 30 는 첫 매치 후 순차 read.
Hash Index — equality only
- equality (
WHERE id = 42) 매우 빠름 — O(1) - range / sorting / partial match 불가 — hash 가 정렬 X
- PostgreSQL hash index 는 9.6+ 부터 WAL 지원, 그 전엔 권장 X
- 대부분 시스템 — B-tree 가 default. hash 는 niche
왜 LIKE '%foo' 는 index 못 쓰나
-- index on name
SELECT * FROM users WHERE name LIKE 'foo%'; ✓ index 사용
SELECT * FROM users WHERE name LIKE '%foo'; ✗ full scan
이유:
B-tree 는 prefix 기반 탐색. 'foo%' 는 'foo' 로 시작하는 값 = 알파벳
순으로 인접 → range query. 'foo'-'foo가나다' 까지 순차 read.
'%foo' 는 끝이 'foo' → name 의 모든 값 검사 필요. 정렬 무관.해결 — full-text search index (PostgreSQL tsvector + GIN, MySQL FULLTEXT) 또는 reverse index (이름 거꾸로 저장 + LIKE 'oof%').
Composite Index — column 순서가 결정적
CREATE INDEX idx ON users (last_name, first_name, age);
이게 동작하는 쿼리:
✓ WHERE last_name = 'Kim' (prefix)
✓ WHERE last_name = 'Kim' AND first_name = 'Sumi' (prefix)
✓ WHERE last_name = 'Kim' AND first_name = 'Sumi' AND age = 30 (full)
✓ WHERE last_name = 'Kim' AND age = 30 (range scan + filter)
이게 못 쓰는 쿼리:
✗ WHERE first_name = 'Sumi' (prefix 안 맞음)
✗ WHERE age = 30 (prefix 안 맞음)
✗ WHERE first_name = 'Sumi' AND age = 30 (prefix 안 맞음)Composite index 는 "왼쪽에서 오른쪽" rule. 첫 column 안 쓰면 index 무효. 가장 selective (distinct 값 많은) column 을 앞에.
실용 예:
# 자주 쓰는 query
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';
# 좋은 index:
CREATE INDEX idx ON orders (user_id, status);
# 나쁜 index (user_id 가 더 selective):
CREATE INDEX idx ON orders (status, user_id);Covering Index — index 만으로 응답
-- 일반 index
CREATE INDEX idx_email ON users (email);
SELECT name FROM users WHERE email = 'a@b.c';
↓
1. idx_email 에서 email 매치 → row pointer
2. table 의 row 페이지 fetch → name 추출
2 단계 ─ 2 번 I/O
-- Covering index
CREATE INDEX idx_email_with_name ON users (email) INCLUDE (name);
-- (PostgreSQL INCLUDE 또는 (email, name) composite)
SELECT name FROM users WHERE email = 'a@b.c';
↓
1. index 에서 email 매치 → name 같이 박혀있음 → 즉시 응답
1 단계 ─ table 접근 0매우 빠름. 단, index 가 커짐. 자주 호출되는 query 에만 적용.
Index 의 비용
Index 가 마법 X. 비용:
- 저장 공간 — 100 만 row + index 4 개 = 추가 수 GB
- write 부담 — INSERT / UPDATE / DELETE 시 모든 관련 index 도 업데이트. 1 row write = N+1 disk write.
- VACUUM / OPTIMIZE — fragment 정리 시간
- planner overhead — 너무 많은 index → planner 의 선택 시간 ↑
Trade-off — write-heavy table 은 index 줄임. read-heavy table 은 index ↑.
Query Planner — 어떤 index 쓸지 결정
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42 AND created_at > NOW() - INTERVAL '7 days';
QUERY PLAN
─────────────────────────────────────────────────────────────────
Index Scan using idx_user_created on orders
(cost=0.43..8.45 rows=1 width=72)
(actual time=0.025..0.027 rows=3 loops=1)
Index Cond: (user_id = 42 AND created_at > now() - '7 days'::interval)
cost = planner 의 추정 비용
actual time = 실제 시간Planner 가 statistics (row 수 / distinct 값 / column 분포) 기반 가장 싼 plan 선택:
- Seq Scan — full table read. 작은 table 또는 많은 row 매치 시.
- Index Scan — index 로 row pointer → row fetch. 중간 양의 row 매치 시.
- Index Only Scan — covering index 활용. row fetch X.
- Bitmap Index Scan — 여러 index 결합.
ANALYZE table_name 으로 statistics 업데이트. 큰 INSERT 후 또는 정기 (PostgreSQL 의 autovacuum).
Index 가 무용한 패턴
1. Function on indexed column
# Bad — function 적용 → index 못 씀
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
# Good — query 측에서 lowercase
SELECT * FROM users WHERE email = LOWER('Alice@Example.com');
# 또는 functional index
CREATE INDEX idx_email_lower ON users (LOWER(email));2. Implicit type conversion
# id column 이 VARCHAR
SELECT * FROM users WHERE id = 42; ← 42 가 number → VARCHAR 변환
→ index 못 쓸 수 있음
SELECT * FROM users WHERE id = '42'; ← string 명시, index 사용3. OR 의 다른 column
SELECT * FROM users WHERE email = '...' OR phone = '...';
만약 email 과 phone 에 각각 index 있어도:
- 옛 planner: full scan (OR 처리 어려움)
- 모던 planner: Bitmap Index Scan 으로 두 index union
기왕이면 UNION 으로 분리:
SELECT * FROM users WHERE email = '...'
UNION
SELECT * FROM users WHERE phone = '...';4. NOT IN / !=
Negation 은 index 효율 ↓. 대부분 row 매치 → full scan 차라리. 가능하면 positive filter 로 reformulate.
5. 작은 table
100 row 의 lookup 은 index vs scan 차이 미미. planner 가 scan 선택해도 정상. 모든 column 에 index 박지 말 것.
Index 종류 — B-tree 외
- GIN (Generalized Inverted Index, PostgreSQL) — full-text search, JSONB 안 key, array
- GiST (Generalized Search Tree, PostgreSQL) — geometric (PostGIS), tsvector
- BRIN (Block Range Index, PostgreSQL) — append- only 대용량 (log table). 매우 작음, 정확도 ↓
- Bitmap (Oracle / data warehouse) — 적은 distinct 값 (gender, status)
- Spatial / R-tree — 지리 정보
Unique Index 와 Constraint
CREATE UNIQUE INDEX idx_email ON users (email);
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);
거의 동일 — constraint 가 unique index 자동 생성. constraint 는 schema
의 명시적 의도, index 는 기능.
Primary key = unique index + NOT NULL.Partial Index — 조건부 index
-- 활성 user 만 자주 query
CREATE INDEX idx_active_email ON users (email) WHERE status = 'active';
-- 효과:
-- 1. index 크기 ↓ (활성 user 만)
-- 2. 같은 query 더 빠름 (smaller B-tree)
-- 3. WHERE status = 'active' 조건이 query 에 있으면 사용Index 모니터링
# PostgreSQL — 사용 안 되는 index 찾기
SELECT schemaname, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;
# MySQL
SELECT * FROM sys.schema_unused_indexes;
# 결과 — 자주 사용 안 되는 index 는 DROP. write 부담만.흔한 함정
1. 모든 column 에 index
"혹시 모르니" 모든 column 에 index → write 부담만. EXPLAIN 으로 실제 사용되는 index 확인 후 결정.
2. composite index 순서 헷갈림
가장 selective + 자주 filter 되는 column 을 앞에. equality 가 range 보다 앞에:
# 권장 — equality 가 앞, range 가 뒤
INDEX (user_id, created_at)
WHERE user_id = 42 AND created_at > '...'
# 비권장 — range 가 앞
INDEX (created_at, user_id)
WHERE user_id = 42 AND created_at > '...'
↑ created_at 의 range scan 후 user_id 필터 — 비효율3. stale statistics
대량 INSERT 후 ANALYZE 안 함 → planner 가 잘못된 plan. 정기 autovacuum 확인.
4. UUID primary key 의 fragmentation
UUID v4 는 random → INSERT 마다 B-tree 의 다른 위치에 page split. UUID v7 (timestamp prefix) 또는 ULID 가 sequential → fragmentation 해소.
5. NULL 의 index 동작
PostgreSQL — NULL 도 index. MySQL InnoDB — NULL 도 index. Oracle — NULL 은 B-tree index 에 안 들어감. 시스템별 다름. WHERE col IS NULL 검증.
참고 자료
- Use The Index, Luke — Markus Winand 의 SQL index tutorial — use-the-index-luke.com
- PostgreSQL — Indexes — postgresql.org
- MySQL 8 — Optimization and Indexes — mysql.com
- B-tree visualization — USF CS
요약
- Index = 책의 색인. full scan 대신 직접 lookup. B-tree 가 가장 흔함.
- B-tree 는 equality + range 둘 다 효율적. Hash 는 equality 만.
- LIKE 'foo%' = index 사용. LIKE '%foo' = full scan. full-text 또는 reverse index 로 우회.
- Composite index 는 "왼쪽 prefix" rule. selective + equality column 을 앞에.
- Covering index 가 table fetch 없이 응답. 자주 쓰는 query 만.
- Index 비용 — 저장 + write + VACUUM + planner. 모든 column 에 박지 말 것.
- EXPLAIN 으로 planner 의 실제 선택 확인. ANALYZE 로 statistics 업데이트.
- 특수 index — GIN (full-text), GiST (geometric), BRIN (대용량 log), partial (조건부). 상황별 선택.