클러스터 vs 논클러스터 인덱스
들어가며
인덱스는 SQL Server 성능 튜닝의 가장 핵심적인 요소 이다. 인덱스를 잘못 설계하면 아무리 좋은 하드웨어를 써도 쿼리가 느리고, 잘 설계하면 적은 리소스로도 빠른 응답을 얻을 수 있다.
이 글에서는 인덱스의 기본 개념부터 내부 구조(B-Tree), 실행 계획 읽는 법, 복합 인덱스 설계, 커버링 인덱스 등을 간단히 다룬다.
1. 인덱스란 무엇인가?
책의 색인과 같은 원리
인덱스는 책의 색인(Index) 과 같다. 책에서 “트랜잭션”이라는 단어를 찾고 싶을 때:
- 색인 없이 : 1페이지부터 끝까지 모든 페이지를 넘기며 찾음
- 색인 있으면 : 색인에서 “트랜잭션 → 234페이지” 확인 후 바로 이동
데이터베이스에서도 마찬가지다:
- 인덱스 없이 : 테이블의 모든 행을 처음부터 끝까지 스캔
- 인덱스 있으면 : 인덱스를 통해 원하는 데이터 위치로 바로 이동
인덱스의 구성 요소
1
2
3
[인덱스]
├── 인덱스 키 (검색 기준이 되는 컬럼)
└── 포인터 (실제 데이터 위치를 가리킴)
예를 들어 userId 컬럼에 인덱스가 있다면:
1
2
3
4
5
인덱스 키 → 데이터 위치
userId: 1 → Page 10, Row 3
userId: 2 → Page 15, Row 7
userId: 3 → Page 10, Row 5
...
2. B-Tree 구조 (인덱스의 내부)
B-Tree란?
SQL Server의 인덱스는 B-Tree(Balanced Tree) 구조로 저장된다. 정확히는 B+Tree 를 사용한다.
1
2
3
4
5
6
7
8
9
[Root Node]
/ | \
/ | \
[Intermediate] [Intermediate] [Intermediate]
/ \ / \ / \
/ \ / \ / \
[Leaf] [Leaf] [Leaf] [Leaf] [Leaf] [Leaf]
↕ ↕ ↕ ↕ ↕ ↕
(Data) (Data) (Data) (Data) (Data) (Data)
B-Tree의 3가지 레벨
| 레벨 | 이름 | 역할 |
|---|---|---|
| Root Level | 루트 노드 | 검색의 시작점, 단 1개 |
| Intermediate Level | 중간 노드 | 하위 노드로 가는 길 안내 |
| Leaf Level | 리프 노드 | 실제 데이터 또는 데이터 위치 포함 |
검색 과정 예시
userId = 57 을 찾는 과정:
1
2
3
4
5
6
7
8
1. Root Node 시작
- "57은 50보다 크고 100보다 작다" → 중간 노드 B로 이동
2. Intermediate Node B
- "57은 55보다 크고 60보다 작다" → Leaf Node X로 이동
3. Leaf Node X
- userId = 57 데이터 발견
이 과정은 O(log n) 의 시간 복잡도를 가진다. 100만 건의 데이터도 약 3~4번의 탐색 으로 찾을 수 있다.
왜 B-Tree가 빠른가?
로그 스케일의 마법:
| 데이터 건수 | 트리 깊이 (대략) | 필요한 페이지 읽기 |
|---|---|---|
| 1,000 | 2 | 2~3회 |
| 100,000 | 3 | 3~4회 |
| 10,000,000 | 4 | 4~5회 |
| 1,000,000,000 | 5 | 5~6회 |
10억 건의 데이터도 5~6번의 페이지 읽기로 찾을 수 있다.
SQL Server의 각 노드는 8KB 페이지 이다. 한 페이지에 수백 개의 키를 저장할 수 있어서 트리가 매우 넓고 얕다.
3. Clustered vs Non-Clustered Index
Clustered Index (클러스터 인덱스)
물리적으로 데이터를 정렬해서 저장 한다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
[Clustered Index on userId]
Leaf Level = 실제 데이터 페이지
┌────────────────────────────────────┐
│ userId: 1 | name: "Kim" | ... │
│ userId: 2 | name: "Lee" | ... │
│ userId: 3 | name: "Park" | ... │
└────────────────────────────────────┘
↕ (Doubly Linked List)
┌────────────────────────────────────┐
│ userId: 4 | name: "Choi" | ... │
│ userId: 5 | name: "Jung" | ... │
│ ... │
└────────────────────────────────────┘
특징:
- 테이블당 1개만 가능
- 리프 노드 = 실제 데이터
- PK에 자동 생성 (기본값)
- 데이터 자체가 인덱스 순서대로 디스크에 저장됨
Non-Clustered Index (논클러스터 인덱스)
별도의 인덱스 구조 를 만들고, 실제 데이터 위치를 가리킨다.
1
2
3
4
5
6
7
8
9
10
[Non-Clustered Index on email]
Leaf Level = 인덱스 키 + Row Locator
┌─────────────────────────────────────────┐
│ email: "a@test.com" → Row Locator 1 │
│ email: "b@test.com" → Row Locator 2 │
│ email: "c@test.com" → Row Locator 3 │
└─────────────────────────────────────────┘
↓
실제 데이터로 이동
Row Locator의 종류:
- Clustered 테이블: Clustered Index Key (예: userId)
- Heap 테이블: RID (파일ID + 페이지ID + 슬롯번호)
특징:
- 테이블당 여러 개 가능 (최대 999개)
- 리프 노드 = 인덱스 키 + 데이터 위치
- 추가적인 Key Lookup 이 필요할 수 있음
비교 정리
| 구분 | Clustered Index | Non-Clustered Index |
|---|---|---|
| 개수 | 테이블당 1개 | 테이블당 여러 개 |
| 리프 노드 | 실제 데이터 | 인덱스 키 + Row Locator |
| 물리적 정렬 | O (데이터 자체 정렬) | X (별도 구조) |
| 추가 조회 | 불필요 | Key Lookup 필요할 수 있음 |
| 주 용도 | PK, 범위 검색, JOIN 키 | 자주 검색하는 컬럼 |
4. 페이지 분할 (Page Split)
페이지 분할이란?
데이터를 INSERT 할 때 해당 페이지가 가득 차 있으면 SQL Server는 페이지 분할 을 수행한다.
1
2
3
4
5
6
7
8
[Before - 페이지가 꽉 참]
Page 10: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
[Insert userId = 5.5]
[After - 페이지 분할 발생]
Page 10: [1, 2, 3, 4, 5]
Page 15: [5.5, 6, 7, 8, 9, 10] ← 새 페이지 할당
페이지 분할의 비용
- 새 페이지 할당
- 기존 페이지에서 약 50% 데이터 이동
- B-Tree 포인터 업데이트
- 트랜잭션 로그 기록
이 모든 작업이 INSERT 성능을 저하 시킨다.
GUID를 Clustered Key로 쓰면 안 되는 이유
1
2
3
4
5
-- 나쁜 예: GUID를 PK/Clustered로 사용
CREATE TABLE BadTable (
Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
...
)
GUID(NEWID())는 랜덤한 값 이다. 새 데이터가 테이블 중간 어딘가에 삽입되어 페이지 분할이 빈번하게 발생 한다.
1
2
3
4
5
-- 좋은 예: IDENTITY(자동증가)를 PK/Clustered로 사용
CREATE TABLE GoodTable (
Id INT IDENTITY(1,1) PRIMARY KEY,
...
)
IDENTITY는 항상 끝에 추가 되므로 페이지 분할이 거의 없다.
GUID를 써야 한다면
NEWSEQUENTIALID()를 사용하자. 순차적인 GUID를 생성해준다.
5. 실행 계획 읽기
Index Seek vs Index Scan
Index Seek:
- B-Tree를 타고 원하는 데이터로 바로 이동
- 필요한 행만 읽음
- 일반적으로 빠름
Index Scan:
- 인덱스 전체를 처음부터 끝까지 읽음
- 모든 행을 읽고 조건에 맞는지 확인
- 일반적으로 느림 (대용량일수록)
1
2
3
4
5
[Index Seek]
Root → Intermediate → Leaf (특정 위치) → 필요한 데이터만
[Index Scan]
Leaf 첫 페이지 → 다음 페이지 → ... → 마지막 페이지 (전체 스캔)
실제 벤치마크 (100만 건)
| 테스트 케이스 | 작업 유형 | Logical Reads | CPU Time | Rows |
|---|---|---|---|---|
| PK 조회 | Clustered Index Seek | 3 | 0 ms | 1 |
| 날짜 범위 조회 (커버링) | Index Seek | 470 | 0 ms | 98,562 |
| 함수 사용 (YEAR) | Clustered Index Scan | 4,800 | 142 ms | 99,812 |
Seek이 Scan보다 Logical Reads가 10배 이상 적다.
Table Scan
Clustered Index가 없는 테이블(Heap) 에서 발생:
1
2
3
-- Heap 테이블 (PK 없음)
SELECT * FROM HeapTable WHERE SomeColumn = 'value'
-- 결과: Table Scan (전체 테이블 읽기)
Seek이 항상 좋고 Scan이 항상 나쁜 것은 아니다. 전체 데이터의 대부분을 가져올 때는 Scan이 더 효율적 일 수 있다.
Key Lookup 문제
Non-Clustered Index로 검색했는데, SELECT 절에 인덱스에 없는 컬럼이 있으면 Key Lookup이 발생한다.
1
2
3
-- 인덱스: (email)
-- 쿼리:
SELECT email, name, phone FROM Users WHERE email = 'test@test.com'
실행 과정:
- Non-Clustered Index에서 email로 검색 (Index Seek)
- Row Locator로 실제 데이터 페이지 접근 (Key Lookup)
- name, phone 가져옴
1
2
3
[실행 계획]
Index Seek (email) → Key Lookup (name, phone 가져오기)
5% 95%
Key Lookup이 대부분의 비용을 차지한다.
행이 많아지면:
- 10건 매칭 → 10번 Key Lookup → 괜찮음
- 10,000건 매칭 → 10,000번 Key Lookup → 심각한 성능 저하
6. 커버링 인덱스 (Covering Index)
Key Lookup 해결하기
커버링 인덱스 는 쿼리에 필요한 모든 컬럼을 인덱스에 포함 시켜 Key Lookup을 제거한다.
1
2
3
4
5
6
-- 기존 인덱스 (Key Lookup 발생)
CREATE INDEX IX_Users_Email ON Users (email)
-- 커버링 인덱스 (Key Lookup 제거)
CREATE INDEX IX_Users_Email_Covering ON Users (email)
INCLUDE (name, phone)
INCLUDE 절의 역할
인덱스 키 vs INCLUDE 컬럼 차이:
| 구분 | 인덱스 키 | INCLUDE 컬럼 |
|---|---|---|
| 정렬 | O (B-Tree 정렬에 사용) | X |
| 검색 조건 | WHERE, JOIN, ORDER BY | SELECT 절에만 사용 |
| 중간 노드에 저장 | O | X |
| 리프 노드에 저장 | O | O |
1
2
CREATE INDEX IX_Example ON Table (KeyCol1, KeyCol2)
INCLUDE (IncludeCol1, IncludeCol2)
KeyCol1, KeyCol2: 검색/정렬에 사용IncludeCol1, IncludeCol2: 데이터만 저장 (Key Lookup 방지)
실제 성능 차이
Before (Key Lookup 있음):
1
2
Logical Reads: 317
Elapsed Time: 느림
After (커버링 인덱스):
1
2
Logical Reads: 5
Elapsed Time: 빠름
Logical Reads가 60배 이상 감소
커버링 인덱스는 강력하지만, 인덱스 크기가 커진다. INSERT/UPDATE/DELETE 성능에 영향을 줄 수 있으므로 자주 사용하는 쿼리에만 적용하자.
7. 복합 인덱스 (Composite Index)
복합 인덱스란?
여러 컬럼을 하나의 인덱스로 묶은 것이다.
1
CREATE INDEX IX_Composite ON Users (lastName, firstName)
컬럼 순서가 중요한 이유
복합 인덱스는 “전화번호부”와 같다:
1
2
3
4
전화번호부 정렬: 성(lastName) → 이름(firstName)
김철수 → O (성으로 검색 가능)
철수 → X (성 없이 이름만으로 검색 불가)
예시:
1
2
3
4
5
6
7
8
9
10
-- 인덱스: (lastName, firstName)
-- 1. 두 컬럼 모두 사용 → Index Seek ✅
WHERE lastName = 'Kim' AND firstName = 'Chulsoo'
-- 2. 첫 번째 컬럼만 사용 → Index Seek ✅
WHERE lastName = 'Kim'
-- 3. 두 번째 컬럼만 사용 → Index Scan ❌
WHERE firstName = 'Chulsoo'
세 번째 케이스가 Scan인 이유:
- 인덱스는
lastName기준으로 먼저 정렬됨 firstName만으로는 시작점을 찾을 수 없음- 결국 전체 인덱스를 스캔해야 함
컬럼 순서 결정 원칙
권장 순서:
1
2
3
4
1. 등호(=) 조건 컬럼 (선택도 높은 것부터)
2. 범위 조건 컬럼 (>, <, BETWEEN)
3. ORDER BY 컬럼
4. SELECT 절 컬럼 (INCLUDE로)
예시 쿼리:
1
2
3
4
5
6
SELECT orderId, orderDate, total
FROM Orders
WHERE customerId = @customerId -- 등호 조건
AND status = 'Pending' -- 등호 조건
AND orderDate > @startDate -- 범위 조건
ORDER BY orderDate DESC
최적의 인덱스:
1
2
3
CREATE INDEX IX_Orders_Optimal
ON Orders (customerId, status, orderDate DESC)
INCLUDE (total)
이유:
customerId,status: 등호 조건 → 앞에orderDate: 범위 조건 + ORDER BY → 그 다음total: SELECT에서만 사용 → INCLUDE
“선택도 높은 컬럼 먼저”의 진실
흔히 “선택도(Selectivity) 높은 컬럼을 앞에” 라고 하지만, 이건 등호 조건일 때만 해당된다.
| 컬럼 | 고유 값 개수 | 선택도 |
|---|---|---|
| customerId | 10,000 | 높음 |
| status | 5 | 낮음 |
| gender | 2 | 매우 낮음 |
등호 조건이라면 customerId를 앞에 두는 것이 좋다.
하지만 범위 조건이라면 이야기가 다르다:
1
2
-- customerId가 범위 조건이면?
WHERE customerId > 100 AND status = 'Active'
이 경우 (status, customerId) 순서가 더 나을 수 있다.
컬럼 순서는 쿼리 패턴 에 따라 결정해야 한다. 단순히 선택도만 보지 말고, 실제 쿼리의 조건 유형을 분석하자.
8. 인덱스 설계 실수
인덱스 과다 생성
1
2
3
4
5
-- 나쁜 예: 컬럼마다 인덱스
CREATE INDEX IX_Col1 ON Table (Col1)
CREATE INDEX IX_Col2 ON Table (Col2)
CREATE INDEX IX_Col3 ON Table (Col3)
...
문제점:
- INSERT/UPDATE/DELETE 시 모든 인덱스 업데이트
- 디스크 공간 낭비
- 유지보수 비용 증가
선택도 낮은 단일 컬럼 인덱스
1
2
-- 나쁜 예: 성별에 단독 인덱스
CREATE INDEX IX_Gender ON Users (gender) -- 값: M, F 뿐
성별은 값이 2개뿐이라 인덱스를 타도 전체의 50%를 읽어야 한다. 이런 경우 SQL Server는 Table Scan을 선택할 수 있다.
함수 사용으로 인덱스 무효화
1
2
3
4
5
-- 나쁜 예: 함수 사용
WHERE YEAR(orderDate) = 2024 -- Index Scan 발생
-- 좋은 예: 범위 조건으로 변경
WHERE orderDate >= '2024-01-01' AND orderDate < '2025-01-01' -- Index Seek
컬럼에 함수를 적용하면 인덱스를 사용할 수 없다 (SARGable 하지 않음).
SELECT * 사용
1
2
3
4
5
-- 나쁜 예
SELECT * FROM Orders WHERE customerId = 123
-- 좋은 예
SELECT orderId, orderDate, total FROM Orders WHERE customerId = 123
SELECT *는 커버링 인덱스 활용을 막고 Key Lookup을 유발한다.
마치며
SQL Server 인덱스의 핵심을 정리하면
1. 기본 구조
- Clustered Index는 테이블당 1개, 데이터를 물리적으로 정렬
- Non-Clustered Index는 여러 개 가능, 별도의 B-Tree 구조
2. B-Tree
- 10억 건도 5~6번의 페이지 읽기로 검색 가능
- 페이지 분할을 피하려면 순차적인 키(IDENTITY) 사용
3. 실행 계획
- Seek은 필요한 데이터만, Scan은 전체 읽기
- Key Lookup이 성능 병목 → 커버링 인덱스로 해결
4. 복합 인덱스
- 컬럼 순서가 매우 중요
- 등호 조건 → 범위 조건 → ORDER BY 순서로 설계
5. 균형
- 인덱스가 많으면 SELECT 빠르지만 INSERT/UPDATE 느림
- 쿼리 패턴을 분석하고 꼭 필요한 인덱스만 생성
인덱스는 “만능 해결책”이 아니다. 쿼리 패턴을 이해하고, 실행 계획을 확인하며, 지속적으로 모니터링 하는 것이 진정한 성능 튜닝이다.
References
- SQL Server Index Structure and Concepts - SQLShack
- The B-Tree: How SQL Server Indexes are Stored - sqlity.net
- Use The Index, Luke - B-Tree Anatomy
- Index Seek vs Index Scan Benchmark - Medium
- Covering Indexes - Red Gate Simple Talk
- Index Column Order Matters - Brent Ozar
- Composite Index Column Order - SQLShack