들어가며
인덱스는 SQL Server 성능 튜닝의 가장 핵심적인 요소 이다. 인덱스를 잘못 설계하면 아무리 좋은 하드웨어를 써도 쿼리가 느리고, 잘 설계하면 적은 리소스로도 빠른 응답을 얻을 수 있다.
이 글에서는 인덱스의 기본 개념부터 내부 구조(B-Tree), 실행 계획 읽는 법, 복합 인덱스 설계, 커버링 인덱스 등을 간단히 다룬다.
1. 인덱스란 무엇인가?
책의 색인과 같은 원리
인덱스는 책의 색인(Index) 과 같다. 책에서 "트랜잭션"이라는 단어를 찾고 싶을 때:
- 색인 없이 : 1페이지부터 끝까지 모든 페이지를 넘기며 찾음
- 색인 있으면 : 색인에서 "트랜잭션 → 234페이지" 확인 후 바로 이동
데이터베이스에서도 마찬가지다:
- 인덱스 없이 : 테이블의 모든 행을 처음부터 끝까지 스캔
- 인덱스 있으면 : 인덱스를 통해 원하는 데이터 위치로 바로 이동
인덱스의 구성 요소
[인덱스]
├── 인덱스 키 (검색 기준이 되는 컬럼)
└── 포인터 (실제 데이터 위치를 가리킴)예를 들어 userId 컬럼에 인덱스가 있다면:
인덱스 키 → 데이터 위치
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 를 사용한다.
[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. 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 (클러스터 인덱스)
물리적으로 데이터를 정렬해서 저장 한다.
[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 (논클러스터 인덱스)
별도의 인덱스 구조 를 만들고, 실제 데이터 위치를 가리킨다.
[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는 페이지 분할 을 수행한다.
[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로 쓰면 안 되는 이유
-- 나쁜 예: GUID를 PK/Clustered로 사용
CREATE TABLE BadTable (
Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
...
)GUID(NEWID())는 랜덤한 값 이다. 새 데이터가 테이블 중간 어딘가에 삽입되어 페이지 분할이 빈번하게 발생 한다.
-- 좋은 예: 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:
- 인덱스 전체를 처음부터 끝까지 읽음
- 모든 행을 읽고 조건에 맞는지 확인
- 일반적으로 느림 (대용량일수록)
[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) 에서 발생:
-- Heap 테이블 (PK 없음)
SELECT * FROM HeapTable WHERE SomeColumn = 'value'
-- 결과: Table Scan (전체 테이블 읽기)Seek이 항상 좋고 Scan이 항상 나쁜 것은 아니다. 전체 데이터의 대부분을 가져올 때는 Scan이 더 효율적 일 수 있다.
Key Lookup 문제
Non-Clustered Index로 검색했는데, SELECT 절에 인덱스에 없는 컬럼이 있으면 Key Lookup이 발생한다.
-- 인덱스: (email)
-- 쿼리:
SELECT email, name, phone FROM Users WHERE email = 'test@test.com'실행 과정:
1. Non-Clustered Index에서 email로 검색 (Index Seek)
2. Row Locator로 실제 데이터 페이지 접근 (Key Lookup)
3. name, phone 가져옴
[실행 계획]
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을 제거한다.
-- 기존 인덱스 (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 |
CREATE INDEX IX_Example ON Table (KeyCol1, KeyCol2)
INCLUDE (IncludeCol1, IncludeCol2)KeyCol1, KeyCol2: 검색/정렬에 사용IncludeCol1, IncludeCol2: 데이터만 저장 (Key Lookup 방지)
실제 성능 차이
Before (Key Lookup 있음):
Logical Reads: 317
Elapsed Time: 느림After (커버링 인덱스):
Logical Reads: 5
Elapsed Time: 빠름Logical Reads가 60배 이상 감소
커버링 인덱스는 강력하지만, 인덱스 크기가 커진다. INSERT/UPDATE/DELETE 성능에 영향을 줄 수 있으므로 자주 사용하는 쿼리에만 적용하자.
7. 복합 인덱스 (Composite Index)
복합 인덱스란?
여러 컬럼을 하나의 인덱스로 묶은 것이다.
CREATE INDEX IX_Composite ON Users (lastName, firstName)컬럼 순서가 중요한 이유
복합 인덱스는 "전화번호부"와 같다:
전화번호부 정렬: 성(lastName) → 이름(firstName)
김철수 → O (성으로 검색 가능)
철수 → X (성 없이 이름만으로 검색 불가)예시:
-- 인덱스: (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. 범위 조건 컬럼 (>, <, BETWEEN)
3. ORDER BY 컬럼
4. SELECT 절 컬럼 (INCLUDE로)예시 쿼리:
SELECT orderId, orderDate, total
FROM Orders
WHERE customerId = @customerId -- 등호 조건
AND status = 'Pending' -- 등호 조건
AND orderDate > @startDate -- 범위 조건
ORDER BY orderDate DESC최적의 인덱스:
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를 앞에 두는 것이 좋다.
하지만 범위 조건이라면 이야기가 다르다:
-- customerId가 범위 조건이면?
WHERE customerId > 100 AND status = 'Active'이 경우 (status, customerId) 순서가 더 나을 수 있다.
컬럼 순서는 쿼리 패턴 에 따라 결정해야 한다. 단순히 선택도만 보지 말고, 실제 쿼리의 조건 유형을 분석하자.
8. 인덱스 설계 실수
인덱스 과다 생성
-- 나쁜 예: 컬럼마다 인덱스
CREATE INDEX IX_Col1 ON Table (Col1)
CREATE INDEX IX_Col2 ON Table (Col2)
CREATE INDEX IX_Col3 ON Table (Col3)
...문제점:
- INSERT/UPDATE/DELETE 시 모든 인덱스 업데이트
- 디스크 공간 낭비
- 유지보수 비용 증가
선택도 낮은 단일 컬럼 인덱스
-- 나쁜 예: 성별에 단독 인덱스
CREATE INDEX IX_Gender ON Users (gender) -- 값: M, F 뿐성별은 값이 2개뿐이라 인덱스를 타도 전체의 50%를 읽어야 한다. 이런 경우 SQL Server는 Table Scan을 선택할 수 있다.
함수 사용으로 인덱스 무효화
-- 나쁜 예: 함수 사용
WHERE YEAR(orderDate) = 2024 -- Index Scan 발생
-- 좋은 예: 범위 조건으로 변경
WHERE orderDate >= '2024-01-01' AND orderDate < '2025-01-01' -- Index Seek컬럼에 함수를 적용하면 인덱스를 사용할 수 없다 (SARGable 하지 않음).
SELECT * 사용
-- 나쁜 예
SELECT * FROM Orders WHERE customerId = 123
-- 좋은 예
SELECT orderId, orderDate, total FROM Orders WHERE customerId = 123SELECT *는 커버링 인덱스 활용을 막고 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
