데이터베이스 인덱싱(Indexing)의 원리와 최적화: 쿼리 속도의 비밀

2026-03-19Database

Database Indexing

백엔드 개발자 면접 단골 질문이자, 서비스 성능 튜닝의 첫 번째 단추인 데이터베이스 인덱싱(Indexing). 우리는 흔히 "느리면 인덱스 걸어"라고 말하지만, 인덱스가 내부적으로 어떻게 동작하는지, 왜 B-Tree를 쓰는지, 많이 걸면 어떤 부작용이 있는지 깊이 있게 이해하는 개발자는 많지 않습니다. 이 글에서는 인덱스의 자료구조부터 실행 계획(Explain) 분석, 커버링 인덱스까지 약 3,000자 분량으로 상세히 파헤칩니다.

1. 인덱스란 무엇인가? (책의 색인)

데이터베이스 테이블은 책의 본문과 같습니다. 인덱스는 책의 맨 뒤에 있는 **'색인'**입니다. 특정 단어를 찾고 싶을 때 책을 첫 페이지부터 한 장씩 넘기며 찾는 것(Full Table Scan)과, 색인에서 단어를 찾아 페이지 번호로 바로 이동하는 것(Index Seek)의 속도 차이는 데이터가 많을수록 어마어마해집니다.

2. 인덱스의 내부 구조: B-Tree (Balanced Tree)

대부분의 RDBMS(MySQL, PostgreSQL, Oracle)는 인덱스 관리를 위해 B-Tree 계열의 자료구조를 사용합니다. 왜 하필 B-Tree일까요? 해시 테이블(Hash Table)은 O(1)로 더 빠르지 않을까요?

2.1 범위 검색(Range Scan)의 효율성

해시 테이블은 = 연산(단건 조회)에는 매우 빠르지만, >, <, BETWEEN 같은 범위 검색이나 정렬(ORDER BY)에는 사용할 수 없습니다. 반면 B-Tree는 데이터가 항상 정렬된 상태로 유지되므로, 특정 범위를 스캔하는 데 매우 유리합니다.

2.2 균형(Balanced) 유지

B-Tree는 데이터가 삽입/삭제되어도 트리의 높이(Depth)를 일정하게 유지합니다. 이는 최악의 경우에도 검색 성능 O(log N)을 보장한다는 뜻입니다. 데이터가 100만 건이 있어도 트리 높이가 3~~4 정도라면, 디스크 I/O를 3~~4번만 수행하면 데이터를 찾을 수 있습니다.

3. Clustered Index vs Non-Clustered Index

이 두 가지 개념을 구분하는 것은 매우 중요합니다.

Clustered Index: 데이터 자체를 정렬해서 저장합니다. 테이블당 오직 하나만 존재할 수 있으며, 일반적으로 Primary Key가 이 역할을 합니다. 리프 노드에 실제 데이터 페이지가 들어있습니다. 검색 속도가 가장 빠릅니다.
Non-Clustered Index (Secondary Index): 데이터는 그대로 두고, 별도의 인덱스 페이지를 만듭니다. 테이블당 여러 개 만들 수 있습니다. 리프 노드에는 실제 데이터가 아니라, 데이터의 위치를 가리키는 주소값(혹은 PK)이 들어있습니다.

4. 인덱스의 양날의 검: 쓰기 성능 저하

"그럼 모든 컬럼에 인덱스를 걸면 빨라지겠네요?" 이는 초보자가 가장 많이 하는 실수입니다. 인덱스는 공짜가 아닙니다.

저장 공간: 인덱스도 테이블이므로 디스크 공간을 차지합니다.
쓰기 속도 저하: 데이터를 INSERT, UPDATE, DELETE 할 때마다, 인덱스 테이블도 같이 정렬하고 갱신해야 합니다. 인덱스가 많을수록 쓰기 작업은 기하급수적으로 느려집니다.
따라서 **"읽기(Read)와 쓰기(Write)의 비율"**을 고려하여 전략적으로 인덱스를 생성해야 합니다.

5. 최적화 기법: 커버링 인덱스(Covering Index)

쿼리를 튜닝하는 가장 강력한 기법 중 하나입니다. 쿼리에 필요한 모든 컬럼이 인덱스에 포함되어 있어, 실제 데이터 테이블을 찌를(Random Access) 필요 없이 인덱스 스캔만으로 결과를 반환하는 경우를 말합니다.

-- (id, name, email)로 구성된 인덱스가 있다고 가정
SELECT id, name FROM users WHERE email = 'test@example.com';

위 쿼리는 테이블에 접근하지 않고 인덱스 페이지만 읽고 끝납니다. 이로 인해 불필요한 디스크 I/O가 줄어들고 비약적인 성능 향상이 일어납니다.

6. 실행 계획(EXPLAIN)으로 인덱스 효과 검증하기

인덱스를 설계했다면 반드시 EXPLAIN 명령어로 쿼리 실행 계획을 확인해야 합니다. MySQL의 경우 EXPLAIN SELECT 형태로 사용하며, PostgreSQL에서는 EXPLAIN ANALYZE SELECT로 실제 실행 시간까지 측정할 수 있습니다.

6.1 핵심 지표 읽기

실행 계획에서 가장 주의 깊게 봐야 할 컬럼은 type과 rows입니다.

ALL: 풀 테이블 스캔. 인덱스가 전혀 사용되지 않은 최악의 상황입니다.
index: 인덱스 풀 스캔. 테이블보다는 낫지만 여전히 비효율적입니다.
range: 인덱스를 사용한 범위 검색. 대부분의 경우 이 정도면 충분히 최적화된 것입니다.
ref / eq_ref: 인덱스를 사용한 단건 또는 소수 건 조회로 가장 이상적입니다.
const: Primary Key로 단 하나의 행만 읽는 최고의 성능입니다.

6.2 복합 인덱스(Composite Index)의 컬럼 순서 전략

복합 인덱스를 설계할 때 컬럼의 순서는 매우 중요합니다. (A, B, C) 순서로 인덱스를 생성하면, WHERE A = ?는 사용 가능하지만 WHERE B = ?만으로는 인덱스를 탈 수 없습니다(Leftmost Prefix Rule).

최적의 순서를 정하는 원칙은 다음과 같습니다.

동등 조건(=)으로 자주 검색되는 컬럼을 앞에 배치합니다.
**범위 조건(>, <, BETWEEN)**으로 사용되는 컬럼은 뒤에 배치합니다. 범위 조건 이후의 컬럼은 인덱스를 활용하지 못하기 때문입니다.
**카디널리티(Cardinality)**가 높은(고유 값이 많은) 컬럼을 선두에 두면 검색 범위를 더 빠르게 좁힐 수 있습니다.

6.3 인덱스 모니터링과 유지보수

운영 환경에서는 인덱스의 건강 상태를 주기적으로 점검해야 합니다. 사용되지 않는 인덱스(Unused Index)는 쓰기 성능만 저하시키므로 과감히 제거해야 하며, 인덱스 단편화(Fragmentation)가 심해지면 OPTIMIZE TABLE 또는 REINDEX 명령으로 재구축하는 것이 좋습니다.

6.4 파티션 인덱스(Partitioned Index)

대용량 테이블에서는 파티셔닝과 인덱스를 결합하여 성능을 더욱 높일 수 있습니다. 날짜 기반으로 파티션된 테이블에서 각 파티션마다 독립적인 인덱스를 유지하면, 쿼리 범위가 특정 파티션으로 한정되어 스캔 대상이 크게 줄어듭니다.

7. 결론

인덱스는 마법이 아닙니다. 데이터의 분포도(Cardinality)가 좋을수록(중복이 적을수록) 효과가 좋습니다. 예를 들어 '성별' 컬럼처럼 '남/여' 두 가지 값만 있는 컬럼에 인덱스를 거는 것은 효과가 거의 없습니다. 항상 EXPLAIN 명령어로 쿼리의 실행 계획을 확인하고, 병목 지점을 찾아 인덱스를 적재적소에 배치하는 습관을 들여야 합니다.

X. 깊게 파헤치는 데이터베이스 클러스터링과 분산 인덱스 고도화 (Deep Dive)

인덱싱은 단순히 단일 RDBMS 한 대 위에서의 최적화를 넘어, 데이터베이스 클러스터 환경 체제로 들어가면 그 설계 복잡도가 급증하게 됩니다.

1. 분산 환경의 파티셔닝(Partitioning) 기능과 지역성

데이터가 테라바이트 급으로 넘어가면, 하나의 거대한 B-Tree 안에 모든 데이터를 저장하고 탐색하는 것은 디스크 I/O의 병목을 부릅니다. 이 경우 데이터를 기준키(예: 가입연월, 지역)에 따라 여러 개의 데이터베이스 노드로 조각화하는 샤딩(Sharding) 및 파티셔닝이 필수적입니다.
여기서 무서운 점은 글로벌 인덱스(Global Index)와 로컬 인덱스(Local Index) 간의 구조적 결합입니다. 파티션 로컬 인덱스는 분할된 조각 내부에서만 동작하므로 쓰기(Write) 작업에는 극도로 유리하지만 전역(Global) 범위 조회 쿼리에는 심각한 오버헤드를 발생시킵니다.
따라서 쿼리 라우팅 계층에서 올바른 파티션 키를 포함하도록 애플리케이션의 쿼리 컨텍스트를 설계해주는 것만이 이러한 병목을 사전에 차단하는 킬러 솔루션입니다.

2. 파생형 인덱스와 복합 커버링(Covering) 전략의 정수

MySQL 계열의 DB에서 가장 막강한 인덱싱 기법은 커버링 인덱스(Covering Index)입니다.
만약 SELECT user_name, age FROM users WHERE dept_code = 'A' 라는 쿼리가 극심한 빈도로 호출된다면 어떻게 튜닝해야 할까요?
단순히 dept_code 컬럼에 단일 인덱스를 거는 것으로 타협할 수 있습니다. 그러나 조회 시마다 B-Tree 리프(Leaf) 단에서 데이터 블록의 클러스터드 인덱스(Clustered Index) 주소를 찾아 다시 수십 밀리초(Disk Seek)를 소모하는 랜덤 엑세스가 필연적으로 발생합니다.
하지만 아예 조회 대상인 컬럼들을 모두 결합해 (dept_code, user_name, age) 복합 인덱스를 잡아버리면 어떨까요? 인덱스 자체에 원하는 모든 데이터가 포함되어 있으므로 디스크 본체의 테이블 데이터 구역(Data Rows)은 아예 방문조차 하지 않고 B-Tree 스캔만으로 0.1ms 이내로 즉각 메모리 반환을 수행합니다. 이것이 바로 "Index Only Scan"이며, 개발자가 DB 스토리지 논리 엔진의 허점을 파고들어 한계 스피드를 쥐어짜내는 최고의 예술적 경지입니다.

X. 깊게 파헤치는 데이터베이스 클러스터링과 분산 인덱스 고도화 (Deep Dive)

인덱싱은 단순히 단일 RDBMS 한 대 위에서의 최적화를 넘어, 데이터베이스 클러스터 환경 체제로 들어가면 그 설계 복잡도가 급증하게 됩니다.

1. 분산 환경의 파티셔닝(Partitioning) 기능과 지역성

2. 파생형 인덱스와 복합 커버링(Covering) 전략의 정수

X. 깊게 파헤치는 데이터베이스 클러스터링과 분산 인덱스 고도화 (Deep Dive)

인덱싱은 단순히 단일 RDBMS 한 대 위에서의 최적화를 넘어, 데이터베이스 클러스터 환경 체제로 들어가면 그 설계 복잡도가 급증하게 됩니다.