Database Index Strategy

인덱스는 DB 성능 최적화의 가장 기본적인 도구이다. 하지만 “일단 인덱스 걸면 빨라지겠지”라는 접근은 오히려 성능을 악화시킬 수 있다.

인덱스가 해결하는 문제

인덱스가 없으면 DB는 원하는 데이터를 찾기 위해 테이블 전체를 순차 탐색(Full Table Scan) 해야 한다. 행이 100만 개면 100만 개를 다 읽는다. 인덱스가 있으면 B-Tree 구조를 통해 O(log N) 으로 찾는다.

인덱스는 결국 “책의 목차” 이다. 목차가 없으면 원하는 내용을 찾기 위해 책 전체를 넘겨야 하지만, 목차가 있으면 해당 페이지로 바로 갈 수 있다.

인덱스가 필요한 상황

WHERE 절의 조건 컬럼

가장 기본적인 경우다. 자주 조회되는 조건 컬럼에 인덱스를 건다.

-- user_id로 자주 조회한다면 인덱스가 필요
SELECT * FROM orders WHERE user_id = 123;

JOIN의 결합 컬럼

JOIN에서 매칭에 사용되는 컬럼에 인덱스가 없으면, 매 행마다 상대 테이블을 Full Scan한다.

-- orders.user_id에 인덱스가 없으면 users의 각 행마다 orders를 Full Scan
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;

ORDER BY / GROUP BY 컬럼

정렬이나 그룹핑에 사용되는 컬럼에 인덱스가 있으면, DB가 이미 정렬된 인덱스를 활용하여 별도의 정렬 작업(filesort)을 건너뛸 수 있다.

-- created_at에 인덱스가 있으면 정렬 비용이 사라진다
SELECT * FROM notifications WHERE user_id = 123 ORDER BY created_at DESC;

다만 이 쿼리는 user_id 필터링 후 created_at 정렬이므로, 단일 인덱스로는 둘 다 해결할 수 없다. 이 경우 복합 인덱스가 필요하다.

고유성 보장

UNIQUE 제약 조건은 내부적으로 인덱스를 생성한다. 멱등성 키, 이메일 중복 방지 등에 사용된다.

ALTER TABLE transfers ADD CONSTRAINT uk_idempotency_key UNIQUE (idempotency_key);

인덱스가 불필요하거나 오히려 해로운 상황

인덱스는 공짜가 아니다. INSERT/UPDATE/DELETE마다 인덱스도 함께 갱신해야 하므로 쓰기 성능이 저하된다. 인덱스 자체도 디스크 공간을 차지한다.

행이 적은 테이블: 수백~수천 행이면 Full Scan이 인덱스 조회보다 빠를 수 있다. 인덱스를 타는 것 자체가 B-Tree 탐색 + 데이터 페이지 접근이라 오버헤드가 있다
카디널리티가 낮은 컬럼: gender, is_active 같은 값의 종류가 2~3개인 컬럼은 인덱스 효과가 적다. 인덱스를 타도 결국 전체 행의 절반을 읽어야 한다
쓰기가 압도적으로 많은 테이블: 로그 테이블처럼 INSERT가 초당 수만 건이고 읽기는 드문 경우, 인덱스가 쓰기 병목이 된다

단일 인덱스 vs 복합 인덱스

단일 인덱스

하나의 컬럼에 대한 인덱스이다.

CREATE INDEX idx_user_id ON orders (user_id);

적합한 경우:

해당 컬럼 단독으로 WHERE 조건에 자주 사용될 때
다른 컬럼과 함께 조회되는 패턴이 없을 때
JOIN의 결합 키

복합 인덱스 (Composite Index)

여러 컬럼을 하나의 인덱스로 묶는다.

CREATE INDEX idx_user_created ON notifications (user_id, created_at DESC);

적합한 경우:

WHERE + ORDER BY 조합이 자주 사용될 때
여러 컬럼이 함께 WHERE 조건에 사용될 때
Covering Index로 활용하고 싶을 때 (아래에서 설명)

선택 기준

핵심은 실제 쿼리 패턴이다.

-- 쿼리 1: user_id로만 조회
SELECT * FROM orders WHERE user_id = 123;
→ 단일 인덱스 (user_id)

-- 쿼리 2: user_id + status로 조회
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed';
→ 복합 인덱스 (user_id, status)

-- 쿼리 3: user_id로 필터 + created_at으로 정렬
SELECT * FROM notifications WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20;
→ 복합 인덱스 (user_id, created_at DESC)

쿼리 2에서 user_id와 status에 각각 단일 인덱스를 거는 것은 비효율적이다. MySQL은 하나의 쿼리에 하나의 인덱스만 선택하는 것이 기본 동작이다 (Index Merge가 가능하지만 옵티마이저가 항상 선택하지는 않는다). 복합 인덱스를 쓰면 한 번의 B-Tree 탐색으로 두 조건을 모두 만족하는 행을 찾는다.

복합 인덱스의 컬럼 순서

복합 인덱스에서 컬럼 순서가 매우 중요하다. B-Tree는 첫 번째 컬럼 기준으로 정렬되고, 첫 번째 컬럼 값이 같은 범위 내에서 두 번째 컬럼 기준으로 정렬된다.

인덱스 (user_id, status, created_at)의 내부 구조:

user_id=100, status='completed', created_at=03-01
user_id=100, status='completed', created_at=03-05
user_id=100, status='pending',   created_at=03-02
user_id=101, status='completed', created_at=03-03
user_id=101, status='pending',   created_at=03-01

Leftmost Prefix 규칙

복합 인덱스는 왼쪽부터 순서대로 사용해야 효과가 있다.

인덱스가 (A, B, C)일 때:

쿼리 조건	인덱스 사용	이유
`WHERE A = 1`	사용	첫 번째 컬럼
`WHERE A = 1 AND B = 2`	사용	왼쪽 두 컬럼
`WHERE A = 1 AND B = 2 AND C = 3`	전체 사용	전체 컬럼
`WHERE B = 2`	미사용	첫 번째 컬럼 누락
`WHERE A = 1 AND C = 3`	A만 사용	B를 건너뛸 수 없음
`WHERE B = 2 AND C = 3`	미사용	첫 번째 컬럼 누락

컬럼 순서 결정 기준

등호(=) 조건 컬럼을 앞에: 등호 조건은 정확한 위치를 잡아주므로 B-Tree 탐색 범위를 좁힌다
범위 조건이나 정렬 컬럼을 뒤에: 범위 조건(>, <, BETWEEN) 이후의 컬럼은 인덱스를 활용하지 못한다

-- 좋은 예: 등호 → 정렬
CREATE INDEX idx ON notifications (user_id, created_at DESC);
SELECT * FROM notifications WHERE user_id = 123 ORDER BY created_at DESC;
-- user_id=123으로 범위를 좁히고, created_at DESC로 이미 정렬된 상태에서 읽음

-- 나쁜 예: 범위 → 등호
CREATE INDEX idx ON orders (created_at, status);
SELECT * FROM orders WHERE created_at > '2026-03-01' AND status = 'completed';
-- created_at 범위를 탄 후 status는 인덱스를 활용하지 못함

Covering Index

쿼리가 필요로 하는 모든 컬럼이 인덱스에 포함되어 있으면, DB는 테이블 데이터 페이지에 접근하지 않고 인덱스만으로 결과를 리턴할 수 있다. 이를 Covering Index라 한다.

CREATE INDEX idx_covering ON notifications (user_id, created_at DESC, title, is_read);

-- 이 쿼리는 인덱스만으로 응답 가능 (테이블 접근 없음)
SELECT title, is_read, created_at
FROM notifications
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN 결과에서 Extra: Using index가 표시되면 Covering Index가 동작하고 있다는 뜻이다.

다만 컬럼을 많이 포함할수록 인덱스 크기가 커지고 쓰기 비용이 증가하므로, 자주 실행되는 핵심 쿼리에 대해서만 고려해야 한다.

인덱스 설계 실수 패턴

1. 모든 컬럼에 단일 인덱스를 거는 것

-- 안 좋은 예: 컬럼마다 인덱스
CREATE INDEX idx_user ON orders (user_id);
CREATE INDEX idx_status ON orders (status);
CREATE INDEX idx_created ON orders (created_at);

-- 이 쿼리에서 세 인덱스 중 하나만 사용됨
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed' ORDER BY created_at;

→ 복합 인덱스 (user_id, status, created_at) 하나가 세 단일 인덱스보다 효과적이다.

2. 카디널리티를 고려하지 않는 것

-- is_deleted는 값이 TRUE/FALSE 두 개뿐
CREATE INDEX idx_deleted ON users (is_deleted);

-- 전체 행의 절반을 읽게 되므로 옵티마이저가 Full Scan을 선택할 수 있다
SELECT * FROM users WHERE is_deleted = FALSE;

→ 카디널리티가 낮은 컬럼은 단독 인덱스의 효과가 적다. 다른 컬럼과 복합 인덱스로 묶는 것이 낫다.

3. 인덱스를 타지 못하는 쿼리 작성

-- 함수를 씌우면 인덱스를 타지 못한다
SELECT * FROM users WHERE YEAR(created_at) = 2026;
→ SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- 암묵적 타입 변환
SELECT * FROM users WHERE phone = 01012345678;  -- phone이 VARCHAR인데 숫자로 비교
→ SELECT * FROM users WHERE phone = '01012345678';

-- LIKE의 앞부분 와일드카드
SELECT * FROM users WHERE name LIKE '%민규';  -- 인덱스 사용 불가
SELECT * FROM users WHERE name LIKE '민규%';  -- 인덱스 사용 가능

4. 사용되지 않는 인덱스를 방치하는 것

시간이 지나면서 쿼리 패턴이 바뀌어도 인덱스는 그대로 남아있는 경우가 많다. 사용되지 않는 인덱스는 쓰기 성능만 저하시킨다.

MySQL에서는 sys.schema_unused_indexes 뷰로, PostgreSQL에서는 pg_stat_user_indexes의 idx_scan 값으로 사용되지 않는 인덱스를 식별할 수 있다.