DB 인덱스 완전정복 — 왜 안 만들고, 언제 만들고, 어떻게 만드나

종종 서비스가 느린걸 찾아보면 DB의 테이블에 인덱스가없어서 느린경우가 꽤나 있다.(다중 조인이 문제인경우가 더 많긴하지만)

안그런경우도 있는데 DB의 느린이유가 대부분 인덱스 설정을 안한경우가 있어서 그에 대해 포스팅해보려고한다. 

 

 

개발자들이 인덱스를 안 만드는 이유

실무에서 테이블을 만들고 인덱스를 빠뜨리는 일은 생각보다 흔하다. 악의가 있어서가 아니라 몇 가지 구조적인 이유가 있다.

1. 기능이 먼저, 최적화는 나중

"일단 동작하게 만들고 느려지면 고치자"는 사고방식이 팽배하다. 인덱스는 당장 기능에 영향이 없으니 자연스럽게 뒤로 밀린다. 문제는 "나중"이 잘 오지 않는다는 것.

2. 데이터가 적을 때는 체감이 없다

10만 건 이하에서는 풀스캔이나 인덱스 스캔이나 응답 속도 차이가 거의 없다. 문제를 겪어본 적이 없으니 필요성을 모른다. 데이터가 수백만 건이 됐을 때 비로소 후회한다.

3. ORM이 대신 쿼리를 짜준다

Prisma, SQLAlchemy, JPA 같은 ORM을 쓰면 SQL을 직접 작성하지 않는다. DB가 어떻게 돌아가는지 추상화 뒤에 가려지고, 인덱스는 ORM이 알아서 해줄 것 같은 착각이 생긴다. ORM은 기본 PK 인덱스 외에는 자동으로 만들어주지 않는다.

4. 실행 계획을 안 본다

EXPLAIN, Execution Plan 같은 도구로 쿼리가 풀스캔인지 인덱스 스캔인지 확인하는 습관이 없다. 문제가 보이지 않으면 없는 것처럼 느껴진다.

5. 인덱스의 트레이드오프를 모른다

인덱스가 SELECT를 빠르게 한다는 건 알아도, INSERT/UPDATE/DELETE 시 인덱스도 함께 갱신되어 쓰기 비용이 증가한다는 사실을 모르는 경우가 많다. 반대로 읽기 비중이 높은 테이블에 인덱스가 없는 것도 문제다. 트레이드오프를 이해해야 올바른 판단을 할 수 있다.

6. DB 스키마가 코드 리뷰에서 빠진다

애플리케이션 코드는 PR 리뷰를 거치지만, CREATE TABLE 스크립트는 "그냥 실행하면 되는 것"으로 취급되는 경우가 많다. 리뷰 프로세스가 없으니 인덱스 누락이 걸러지지 않는다.

---

인덱스란 무엇인가

책의 목차와 같다. 책 전체를 뒤지지 않고 목차에서 페이지를 찾아가듯, DB도 인덱스를 통해 데이터를 빠르게 찾는다.

내부적으로는 대부분 B-Tree 구조로 구현되어 있어, 범위 검색과 정렬에 효율적이다.

인덱스 없음: 테이블 전체 스캔 (Full Table Scan) → O(n)
인덱스 있음: B-Tree 탐색 → O(log n)

1,000만 건 테이블에서 단 한 건을 찾을 때 차이가 극명하다.

---

인덱스의 종류

클러스터드 인덱스 (Clustered Index)

• 테이블의 실제 데이터가 인덱스 순서대로 물리적으로 정렬됨
• 테이블당 1개만 존재 가능
• PRIMARY KEY 선언 시 자동 생성됨 (MSSQL 기준)
• 범위 검색에 유리

-- PK 선언 = 클러스터드 인덱스 자동 생성
CREATE TABLE orders (
    order_no NVARCHAR(50) NOT NULL PRIMARY KEY,  -- 클러스터드 인덱스
    ...
);

논클러스터드 인덱스 (Non-Clustered Index)

• 실제 데이터와 별도로 인덱스 구조를 따로 유지
• 테이블당 여러 개 생성 가능
• 특정 컬럼으로 자주 조회할 때 추가

CREATE INDEX IX_orders_created_at ON orders (created_at);

복합 인덱스 (Composite Index)

• 두 개 이상의 컬럼을 묶어 하나의 인덱스로 만듦
• 컬럼 순서가 중요 — 첫 번째 컬럼 기준으로 먼저 정렬됨

-- worker 로 필터링 후 completed_at 으로 정렬하는 쿼리에 유리
CREATE INDEX IX_favor_worker_date ON favor_completion (worker, completed_at);

유니크 인덱스 (Unique Index)

• 중복 값을 허용하지 않는 인덱스
• UNIQUE 제약과 함께 자동 생성되거나 명시적으로 선언

CREATE UNIQUE INDEX UX_users_email ON users (email);

---

인덱스 생성 문법

MSSQL

-- 기본 인덱스
CREATE INDEX IX_테이블_컬럼 ON 테이블명 (컬럼명);

-- 복합 인덱스
CREATE INDEX IX_테이블_복합 ON 테이블명 (컬럼1, 컬럼2);

-- 유니크 인덱스
CREATE UNIQUE INDEX UX_테이블_컬럼 ON 테이블명 (컬럼명);

-- 인덱스 삭제
DROP INDEX IX_테이블_컬럼 ON 테이블명;

-- 인덱스 목록 조회
SELECT i.name, i.type_desc, i.is_primary_key, i.is_unique,
       STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS columns
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID('테이블명')
GROUP BY i.name, i.type_desc, i.is_primary_key, i.is_unique;

PostgreSQL

-- 기본 인덱스
CREATE INDEX idx_테이블_컬럼 ON 테이블명 (컬럼명);

-- 복합 인덱스
CREATE INDEX idx_테이블_복합 ON 테이블명 (컬럼1, 컬럼2);

-- 유니크 인덱스
CREATE UNIQUE INDEX uidx_테이블_컬럼 ON 테이블명 (컬럼명);

-- 운영 중 락 없이 생성 (대용량 테이블)
CREATE INDEX CONCURRENTLY idx_테이블_컬럼 ON 테이블명 (컬럼명);

-- 인덱스 목록 조회
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = '테이블명';

MySQL

-- 기본 인덱스
CREATE INDEX idx_테이블_컬럼 ON 테이블명 (컬럼명);

-- 인덱스 목록 조회
SHOW INDEX FROM 테이블명;

---

언제 인덱스를 만들어야 하나

테이블 생성 시 바로 걸어야 하는 경우

 

상황	이유
WHERE 절에 자주 등장하는 컬럼	조회 시마다 풀스캔 방지
JOIN의 ON 조건에 쓰이는 컬럼	JOIN 성능 직결
ORDER BY / GROUP BY 에 쓰이는 컬럼	정렬 비용 감소
유니크해야 하는 컬럼 (이메일, 코드 등)	중복 방지 + 조회 성능
FK (외래키) 컬럼	JOIN 시 매번 풀스캔 방지

운영 중에 추가해야 하는 경우

• 슬로우 쿼리 로그에 특정 쿼리가 반복 등장할 때
• 실행 계획에서 Full Table Scan / Seq Scan이 나올 때
• 응답 시간이 눈에 띄게 늘어날 때

-- MSSQL: 실행 계획 확인
SET STATISTICS IO ON;
SELECT * FROM favor_completion WHERE worker = '홍길동';

-- PostgreSQL: 실행 계획 확인
EXPLAIN ANALYZE SELECT * FROM favor_completion WHERE worker = '홍길동';

인덱스를 피해야 하는 경우

• 데이터가 매우 적은 테이블 (수천 건 이하): 풀스캔이 오히려 빠를 수 있음
• 카디널리티가 낮은 컬럼: 성별(남/여), 상태(Y/N) 같이 값의 종류가 2~3개뿐인 컬럼은 인덱스 효과가 거의 없음
• 쓰기가 압도적으로 많은 테이블: 로그 테이블처럼 INSERT가 주인 경우 인덱스가 쓰기 성능을 떨어뜨림

---

인덱스 명명 규칙 (권장)

일관된 네이밍이 없으면 인덱스가 있는지조차 파악하기 어렵다.

 

타입	패턴	예시
일반 인덱스	IX_테이블_컬럼	IX_orders_created_at
유니크 인덱스	UX_테이블_컬럼	UX_users_email
복합 인덱스	IX_테이블_컬럼1_컬럼2	IX_favor_worker_date
PK (자동)	PK_테이블	PK_orders

---

정리

인덱스는 조회 성능을 높이는 대신 쓰기 비용과 저장 공간을 더 쓴다.

이 트레이드오프를 이해하고, 쿼리 패턴에 맞게 만드는 것이 핵심이다.

테이블 생성 시 체크리스트

• WHERE 조건으로 자주 쓰일 컬럼이 있는가?
• JOIN ON 조건에 쓰이는 컬럼인가?
• 유니크 제약이 필요한 컬럼인가?
• 데이터 규모가 충분히 커질 것인가?
• 읽기와 쓰기 비율이 어떻게 되는가?

이 다섯 가지를 스키마 설계 시점에 같이 고민하는 습관이 나중의 장애를 막는다.