데이터베이스 인덱스(클러스터드, 세컨더리, 커버링)

도서관에서 책을 찾는 두가지 방법

인덱스라는 것을 이해할 때 "인덱스는 책 뒤에 있는 색인 같은 거야"라고 이해를 했다. 하지만 실무에서 대용량 트래픽을 처리하는 환경에서는 인덱스는 색인 그 이상이었다. 

데이터베이스에서 데이터를 찾는 방법은 두가지가 있다. 

1. Full Table Scan: 책의 처음부터 끝까지 한 페이지씩 다 넘겨보며 찾기.
2. Index Seek: 색인을 통해 몇 페이지에 있는지 확인하고 바로 펼치기.

데이터가 수백만 건이라면 1번은 노답이다. 그래서 2번이 핵심 포인트인 클러스터드(Clustered), 세컨더리(Secondary), 그리고 튜닝의 끝판왕인 커버링(Covering) 인덱스에 대해 정리했다. 

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

"책 그 자체의 정렬 순서"

클러스터드 인덱스는 테이블의 데이터가 물리적으로 저장되는 순서를 정의한다. 

특징

• 테이블당 하나만 존재: 물리적인 순서는 하나밖에 없을 수 없다.
• Primary Key(PK): 일반적으로 PK를 설정하면 자동으로 클러스터드 인덱스가 생성된다. 
• 리프 노드(Leaf Node)가 곧 데이터 페이지: 인덱스의 끝에 실제 데이터(모든 컬럼)가 들어있다.
• 장점: 검색 속도가 가장 빠릅니다. 인덱스를 타고 내려가면 바로 데이터가 있으니까요.
• 단점: 데이터 입력/수정/삭제 시 정렬을 유지해야 하므로 오버헤드가 큽니다. (페이지 분할 등 발생)
• 비유: '영어 사전'을 예시로 Apple을 찾으면 그 단어 옆에  뜻이 적혀있다. 책 자체가 알파벳순으로 정렬되어 있다. 

2. 세컨더리 인덱스 (Secondary Index / Non-Clustered)

"책 뒤편에 붙어 있는 별도의 색인 페이지"

클러스터드 인덱스가 아닌 모든 인덱스를 말한다.

특징

• 테이블당 여러 개 생성 가능: 필요에 따라 이름, 이메일 등으로 인덱스를 만들 수 있다. 
• 별도의 저장 공간 필요: 실제 데이터와는 분리된 별도의 인덱스 페이지를 갖는다.
• 리프 노드에는 PK가 있다: (InnoDB 기준) 인덱스를 타면 실제 데이터의 주소 대신 PK 값을 가지고 있다.
• 작동 방식:
  1. 세컨더리 인덱스에서 검색 조건에 맞는 값을 찾는다.
  2. 거기서 얻은 PK 값을 가지고 클러스터드 인덱스를 다시 검색한다. (Lookup 발생)
  3. 최종 데이터를 가져온다.
• 비유: 전공 서적 맨 뒤의 '찾아보기'이다. "Index... 345p"라고 적혀 있으면, 우리는 345페이지(실제 데이터 위치)로 다시 이동해야 내용을 볼 수 있다.

3. 커버링 인덱스 (Covering Index)

"책을 펼칠 필요도 없이, 색인만 보고 정답을 알아내는 것"

성능 튜닝의 핵심이다. 쿼리가 요구하는 모든 컬럼이 인덱스에 포함되어 있어, 실제 데이터 테이블(클러스터드 인덱스)을 찾아가서 읽을(Lookup) 필요가 없는 상태를 말한다.

왜 중요한가?

세컨더리 인덱스의 단점인 '2번 과정(Lookup)'을 생략할 수 있기 때문이다. 그러면 랜덤 I/O가 확 줄어든다.

예시 (MySQL InnoDB 기준)

-- 상황: users 테이블에 (name) 컬럼으로 인덱스(idx_name)가 걸려있음.

-- Query A: 커버링 인덱스 X
SELECT name, email FROM users WHERE name = '홍길동';
-- 과정: idx_name에서 '홍길동' 찾음 -> email을 얻기 위해 PK로 원본 테이블 접근(Random I/O) -> 리턴

-- Query B: 커버링 인덱스 O
SELECT name, id FROM users WHERE name = '홍길동';
-- 과정: idx_name에서 '홍길동' 찾음 -> id(PK)는 세컨더리 인덱스에 이미 포함되어 있음 -> 원본 테이블 접근 없이 바로 리턴!

- 자주 조회하는 컬럼들을 묶어서 복합 인덱스(Composite Index)로 만들면 커버링 인덱스 효과를 높일 수 있다. 

4. 인덱스 설계 

1. 인덱스는 공짜가 아니다. (Write Penalty)

인덱스가 많으면 SELECT는 빨라지지만, INSERT, UPDATE, DELETE 성능은 떨어진다. 데이터가 바뀔 때마다 인덱스 정렬을 다시 해야 하기 때문이다. 불필요한 인덱스는 과감하게 삭제 해야한다. 

2. 카디널리티(Cardinality)가 높은 컬럼을 잡아라

성별처럼 종류가 2개(남/여)뿐인 컬럼은 인덱스 효율이 매우 낮다. 주민번호, 이메일, ID처럼 고유한 값이 많은(중복도가 낮은) 컬럼에 인덱스를 추가해야 한다. 

3. 복합 인덱스의 순서 (Left-Most Prefix)

(A, B, C) 순서로 인덱스를 걸었다면, WHERE A=... AND B=...는 인덱스를 타지만, WHERE B=...나 WHERE C=...는 인덱스를 타지 못합니다. (B는 A가 정렬된 후에 정렬되어 있기 때문이다.)

5. 인덱스는 전략이다

인덱스를 무지성으로 생성하면 안된다. 

1. 실행 계획(Explain)을 통해 쿼리가 어떻게 움직이는지 확인하고,
2. 가장 많이 조회되는 쿼리 패턴을 분석하여 클러스터드 인덱스와 세컨더리 인덱스를 적절히 배치하며,
3. 가능하다면 커버링 인덱스를 활용해 I/O 비용을 최소화한다.

데이터베이스 성능 문제는 결국 디스크 I/O를 얼마나 줄이느냐'의 싸움이다. 인덱스 설계를 잘 해서 서버 비용을 낮춰야한다.