DB 인덱스, 제대로 알고 쓰자 - 실행 계획 편
DB 인덱스, 제대로 알고 쓰자 - 실행 계획 편
들어가며
지난 글에서 인덱스의 기본 개념을 다뤘어요. B+Tree 구조, 클러스터드/논클러스터드 차이, 커버링 인덱스까지. 근데 이론만 알아서는 반쪽짜리입니다.
“인덱스 잘 타고 있는 거 맞아?”
이 질문에 답하려면 실행 계획을 읽을 줄 알아야 합니다. 저도 처음엔 실행 계획 창을 열어놓고 뭐가 뭔지 몰라서 그냥 총 실행시간만 보고 닫고 그랬던 적이 있어요. 근데 이거 한번 제대로 익혀두면 쿼리 튜닝할 때 정말 큰 무기가 됩니다.
오늘은 MSSQL 기준으로 실행 계획 읽는 법을 정리해볼게요.
실행 계획이 뭔가요?
쿼리를 던지면 DB가 알아서 데이터를 가져오잖아요. 근데 “알아서”가 어떻게 하는 건지 궁금하지 않으세요?
실행 계획은 DB가 쿼리를 어떻게 처리할 건지 보여주는 로드맵입니다.
- 어떤 테이블을 먼저 읽을지
- 인덱스를 쓸지 말지, 쓴다면 어떤 인덱스를
- 테이블을 어떻게 조인할지
- 정렬은 어디서 할지
이걸 알면 “왜 느리지?”에 대한 답을 찾을 수 있습니다.
MSSQL에서 실행 계획 보는 방법
1. 예상 실행 계획 (Estimated)
쿼리를 실행하지 않고 “이렇게 할 거야”라는 계획만 보여줍니다.
1
2
3
4
5
-- 방법 1: 단축키
-- 쿼리 선택 후 Ctrl + L
-- 방법 2: 버튼
-- SSMS 상단 메뉴에서 "예상 실행 계획 표시" 클릭
실제 실행 안 하니까 부담 없이 볼 수 있어요. 대신 예상치라서 실제와 다를 수 있고요.
2. 실제 실행 계획 (Actual)
쿼리를 실제로 실행하고 “이렇게 했어”를 보여줍니다.
1
2
3
4
5
-- 방법 1: 단축키
-- Ctrl + M 누른 후 쿼리 실행 (F5)
-- 방법 2: 버튼
-- "실제 실행 계획 포함" 클릭 후 쿼리 실행
실제 행 수, 실제 실행 횟수 등 진짜 데이터가 나와서 더 정확합니다. 튜닝할 땐 이걸 보는 게 좋아요.
3. SET STATISTICS로 수치 확인
그래픽 실행 계획과 함께 보면 좋은 옵션들입니다.
1
2
3
4
5
6
7
8
9
10
11
12
-- I/O 통계: 테이블별로 읽은 페이지 수
SET STATISTICS IO ON;
-- 시간 통계: CPU 시간, 경과 시간
SET STATISTICS TIME ON;
-- 쿼리 실행
SELECT * FROM Orders WHERE customer_id = 1234;
-- 끄기
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
결과 예시:
1
2
3
4
Table 'Orders'. Scan count 1, logical reads 3, physical reads 0
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
logical reads가 핵심입니다. 이 숫자가 작을수록 좋아요. 튜닝 전후로 비교하면 개선됐는지 바로 알 수 있습니다.
실행 계획 읽는 순서
처음 보면 뭐가 뭔지 모르겠는데, 규칙이 있어요.
1
2
3
4
5
6
7
8
9
10
11
12
┌─────────────────────────────────────────────────────────────┐
│ 실행 계획 읽는 방향 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 데이터 흐름: 오른쪽 → 왼쪽 │
│ 실행 순서: 오른쪽 → 왼쪽, 위 → 아래 │
│ │
│ [SELECT] ← [Sort] ← [Nested Loop] ← [Index Seek] │
│ ↑ ↑ ↑ │
│ 최종 중간 연산 시작점 │
│ │
└─────────────────────────────────────────────────────────────┘
오른쪽에서 왼쪽으로 읽으면 됩니다. 가장 오른쪽이 데이터를 가져오는 시작점이고, 왼쪽으로 가면서 가공되어서 최종 결과가 나와요.
화살표 굵기도 의미가 있습니다. 굵을수록 데이터가 많이 흐르는 거예요. 갑자기 굵어지는 구간이 있으면 그 부분을 의심해봐야 합니다.
핵심 연산자 알아보기
Scan vs Seek: 가장 중요한 차이
이것만 알아도 절반은 먹고 들어갑니다.
Table Scan / Clustered Index Scan
1
2
3
4
5
6
7
8
┌─────────────────────────────────────────┐
│ Table Scan 아이콘 │
│ (테이블 전체를 훑는 모양) │
└─────────────────────────────────────────┘
의미: 테이블 전체를 처음부터 끝까지 다 읽음
언제: 인덱스가 없거나, 있어도 안 타는 경우
성능: 데이터 많으면 느림 🐢
1
2
3
-- Scan이 발생하는 예시
SELECT * FROM Orders WHERE YEAR(order_date) = 2024;
-- order_date에 인덱스 있어도 함수 썼으니까 Scan
Index Seek
1
2
3
4
5
6
7
8
┌─────────────────────────────────────────┐
│ Index Seek 아이콘 │
│ (인덱스 트리 타고 내려가는 모양) │
└─────────────────────────────────────────┘
의미: 인덱스를 타고 필요한 데이터만 정확히 찾아감
언제: 인덱스가 제대로 걸려있고, 조건이 맞을 때
성능: 빠름 🚀
1
2
3
-- Seek가 발생하는 예시
SELECT * FROM Orders WHERE order_date >= '2024-01-01';
-- order_date 인덱스를 타고 해당 범위만 읽음
비교 정리
| 구분 | Scan | Seek |
|---|---|---|
| 동작 | 전체 데이터 훑음 | 필요한 것만 찾아감 |
| 비유 | 책 전체 읽기 | 색인 보고 해당 페이지로 |
| 성능 | 데이터 많으면 느림 | 빠름 |
| 목표 | Seek로 바꾸고 싶음 | 유지하고 싶음 |
Scan이 무조건 나쁜 건 아닙니다. 전체 데이터의 대부분을 읽어야 하는 경우엔 Scan이 오히려 효율적일 수 있어요. 하지만 소수의 행만 필요한데 Scan이 뜨면 문제입니다.
Key Lookup (키 룩업)
1편에서 잠깐 언급했던 녀석입니다. 실행 계획에서 자주 보이는데, 이게 많으면 성능이 안 좋아져요.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
┌─────────────────────────────────────────────────────────────┐
│ Key Lookup 발생 상황 │
├─────────────────────────────────────────────────────────────┤
│ │
│ SELECT name, email, phone │
│ FROM Users │
│ WHERE name = '김철수'; │
│ │
│ name 인덱스: name만 있음 │
│ 필요한 컬럼: name, email, phone │
│ │
│ [SELECT] ← [Nested Loop] ← [Index Seek (name)] │
│ ↑ │
│ [Key Lookup] ← email, phone 가져오려고 │
│ 클러스터드 인덱스 다시 접근! │
│ │
└─────────────────────────────────────────────────────────────┘
왜 문제인가요?
name 인덱스에서 ‘김철수’를 찾았어요. 근데 email이랑 phone은 이 인덱스에 없습니다. 그래서 클러스터드 인덱스(실제 테이블)에 다시 가서 가져와야 해요. 결과가 100건이면? 100번 왔다 갔다 합니다.
해결법: 커버링 인덱스
1
2
3
4
-- email, phone을 인덱스에 포함시킴
CREATE INDEX IX_Users_Name
ON Users (name)
INCLUDE (email, phone);
이제 인덱스만으로 다 해결됩니다. Key Lookup이 사라져요.
조인 연산자 3가지
테이블 조인할 때 DB가 선택하는 방식입니다.
1. Nested Loop Join
1
2
3
4
5
6
7
8
9
10
11
12
13
14
┌─────────────────────────────────────────┐
│ Nested Loop Join │
├─────────────────────────────────────────┤
│ │
│ for (외부 테이블의 각 행) { │
│ for (내부 테이블에서 매칭 찾기) { │
│ 결과에 추가 │
│ } │
│ } │
│ │
│ 적합: 외부 테이블 작고, 내부 테이블에 │
│ 인덱스 있을 때 │
│ │
└─────────────────────────────────────────┘
소량의 데이터 조인할 때 좋습니다. 우리 이커머스 시스템에서 주문 1건의 상세 정보 조회할 때 이런 방식이 효율적이에요.
2. Hash Match Join
1
2
3
4
5
6
7
8
9
10
11
┌─────────────────────────────────────────┐
│ Hash Match Join │
├─────────────────────────────────────────┤
│ │
│ 1. 작은 테이블로 해시 테이블 생성 │
│ 2. 큰 테이블 스캔하면서 해시로 매칭 │
│ │
│ 적합: 대용량 + 인덱스 없을 때 │
│ 주의: 메모리 많이 씀 │
│ │
└─────────────────────────────────────────┘
대용량 데이터 조인할 때 씁니다. 인덱스가 없어도 쓸 수 있어서 배치 작업에서 자주 봐요.
3. Merge Join
1
2
3
4
5
6
7
8
9
10
11
┌─────────────────────────────────────────┐
│ Merge Join │
├─────────────────────────────────────────┤
│ │
│ 1. 양쪽 테이블이 조인 키로 정렬됨 │
│ 2. 지퍼 올리듯이 순서대로 매칭 │
│ │
│ 적합: 양쪽 다 정렬되어 있거나 │
│ 정렬 비용 감수할 만큼 데이터 많을 때 │
│ │
└─────────────────────────────────────────┘
양쪽이 이미 정렬되어 있으면 아주 빠릅니다.
조인 방식 비교
| 방식 | 적합한 상황 | 인덱스 필요 |
|---|---|---|
| Nested Loop | 소량 데이터, OLTP | 내부 테이블에 필요 |
| Hash Match | 대용량, 배치 | 없어도 됨 |
| Merge Join | 정렬된 대용량 | 있으면 좋음 |
실행 계획에서 예상과 다른 조인 방식이 나오면, 통계가 오래됐거나 인덱스가 없을 수 있습니다.
Sort (정렬)
1
2
3
4
5
6
7
8
9
10
11
┌─────────────────────────────────────────┐
│ Sort 연산자 │
├─────────────────────────────────────────┤
│ │
│ ORDER BY, GROUP BY, DISTINCT, │
│ Merge Join 전처리 등에서 발생 │
│ │
│ 비용: 높음 (특히 대용량일 때) │
│ 메모리 부족하면: tempdb 사용 → 더 느림 │
│ │
└─────────────────────────────────────────┘
Sort가 실행 계획에 나타나면 비용이 높을 수 있습니다.
1
2
3
4
5
6
-- Sort 발생
SELECT * FROM Orders ORDER BY order_date DESC;
-- 인덱스로 Sort 제거
CREATE INDEX IX_Orders_OrderDate ON Orders (order_date DESC);
-- 이제 인덱스 순서대로 읽으면 되니까 Sort 불필요
정렬이 필요한 쿼리가 자주 쓰인다면, 해당 순서로 인덱스를 만들어두면 Sort 연산을 없앨 수 있습니다.
실전: 느린 쿼리 튜닝해보기
실제 상황을 가정해볼게요.
상황
이커머스 시스템에서 특정 고객의 최근 주문 조회가 느립니다.
1
2
3
4
5
6
7
SELECT o.order_id, o.order_date, o.total_amount,
p.product_name, od.quantity
FROM Orders o
JOIN OrderDetails od ON o.order_id = od.order_id
JOIN Products p ON od.product_id = p.product_id
WHERE o.customer_id = 12345
ORDER BY o.order_date DESC;
Step 1: 실행 계획 확인
Ctrl + M 누르고 실행해서 실제 실행 계획을 봅니다.
1
2
3
4
5
[SELECT] ← [Sort] ← [Hash Match] ← [Clustered Index Scan (Orders)]
↑
[Table Scan (OrderDetails)]
↑
[Table Scan (Products)]
음… Scan이 여러 개 보이네요.
Step 2: 문제점 파악
- Orders 테이블: Clustered Index Scan
- customer_id로 검색하는데 인덱스가 없음
- OrderDetails 테이블: Table Scan
- order_id로 조인하는데 인덱스가 없음
- Sort 연산
- order_date DESC 정렬을 위해 별도 정렬 발생
Step 3: 인덱스 생성
1
2
3
4
5
6
7
8
9
-- 1. Orders: customer_id로 검색 + order_date로 정렬
CREATE INDEX IX_Orders_CustomerId_OrderDate
ON Orders (customer_id, order_date DESC)
INCLUDE (total_amount);
-- 2. OrderDetails: order_id로 조인
CREATE INDEX IX_OrderDetails_OrderId
ON OrderDetails (order_id)
INCLUDE (product_id, quantity);
Step 4: 다시 확인
1
2
3
4
5
[SELECT] ← [Nested Loop] ← [Index Seek (Orders)]
↑
[Index Seek (OrderDetails)]
↑
[Index Seek (Products)]
- Scan → Seek로 바뀜
- Sort 사라짐 (인덱스가 이미 정렬되어 있으니까)
- Hash Match → Nested Loop (소량 데이터에 적합)
Step 5: 수치로 확인
1
2
3
4
5
SET STATISTICS IO ON;
-- 쿼리 실행
-- Before: logical reads 1547
-- After: logical reads 12
I/O가 100배 이상 줄었습니다. 이게 튜닝이에요.
실행 계획 볼 때 체크리스트
쿼리 튜닝할 때 이 순서로 확인해보세요.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
┌─────────────────────────────────────────────────────────────┐
│ 실행 계획 체크리스트 │
├─────────────────────────────────────────────────────────────┤
│ │
│ □ 1. Scan이 있는가? │
│ → 해당 컬럼에 인덱스가 있는지 확인 │
│ → 인덱스가 있는데 Scan이면 왜 안 타는지 확인 │
│ │
│ □ 2. Key Lookup이 있는가? │
│ → SELECT 컬럼을 인덱스에 INCLUDE 고려 │
│ │
│ □ 3. Sort가 있는가? │
│ → 정렬 순서대로 인덱스 생성 고려 │
│ │
│ □ 4. 예상 행 수 vs 실제 행 수 차이가 큰가? │
│ → 통계 업데이트 필요 (UPDATE STATISTICS) │
│ │
│ □ 5. 굵은 화살표가 갑자기 나타나는가? │
│ → 데이터가 폭발하는 구간, 조인 조건 확인 │
│ │
│ □ 6. 경고 표시(느낌표)가 있는가? │
│ → 암시적 형변환, 메모리 부족 등 확인 │
│ │
└─────────────────────────────────────────────────────────────┘
자주 보이는 경고들
실행 계획에 노란 느낌표가 뜨면 뭔가 문제가 있다는 신호입니다.
1. 암시적 형변환
1
2
3
4
-- phone_number가 VARCHAR인데
WHERE phone_number = 01012345678 -- 숫자로 비교
-- 경고: Type conversion in expression may affect cardinality
타입 맞춰주세요.
2. 통계 부정확
1
2
예상 행 수: 10
실제 행 수: 50,000
차이가 크면 통계가 오래된 겁니다.
1
2
3
UPDATE STATISTICS Orders;
-- 또는 전체
EXEC sp_updatestats;
3. 메모리 부족 (Sort나 Hash)
1
-- 경고: Operator used tempdb to spill data
메모리가 부족해서 디스크(tempdb)를 썼다는 뜻입니다. 쿼리 자체를 개선하거나, 서버 메모리 설정을 확인해봐야 해요.
정리
오늘 배운 내용 정리할게요.
- 실행 계획은 DB가 쿼리를 어떻게 처리하는지 보여주는 로드맵
- 오른쪽에서 왼쪽으로 읽으면 됨
- Scan vs Seek: Seek가 대체로 좋음, Scan은 상황에 따라
- Key Lookup: 커버링 인덱스로 제거 가능
- 조인 방식: Nested Loop, Hash Match, Merge Join 각각 적합한 상황이 있음
- Sort: 인덱스로 제거할 수 있음
- SET STATISTICS IO: logical reads 수치로 개선 확인
- 경고 표시 나오면 꼭 확인하기
처음엔 복잡해 보여도, 몇 번 보다 보면 패턴이 보입니다. 느린 쿼리 만나면 실행 계획부터 열어보는 습관을 들이면 좋아요.
다음 편에서는 실제 운영 환경에서 인덱스 모니터링하고 관리하는 방법을 다뤄볼게요. 안 쓰는 인덱스 찾아서 정리하고, 조각화 관리하는 것들입니다.
그럼 다음에 또 봐요! 🚀