SQL Server 인덱스 튜닝 입문 가이드

이 글은 “인덱스 튜닝이 무엇인지”를 감으로 이해하는 수준이 아니라, 실제 실행 계획 / STATISTICS IO·TIME를 보면서
Scan → Seek, Sort 제거, Key Lookup 제거(커버링 인덱스) 까지 단계적으로 체험하는 입문 실습 가이드입니다.

---

1. 실습 준비 – 데이터베이스 & 테이블 만들기

1-1. 실습용 데이터베이스 생성

-- 1) 실습용 DB 생성
CREATE DATABASE DemoIndexTuning;
GO

-- 2) 해당 DB 사용
USE DemoIndexTuning;
GO

---

1-2. Orders 테이블 생성 (기본 키만 있음)

테이블 구조는 다음과 같습니다.

• 주문 번호: OrderId (PK, 기본적으로 클러스터형 인덱스가 만들어지는 경우가 많음)
• 고객 번호: CustomerId
• 주문 일자: OrderDate
• 금액: Amount
• 상태: Status (O: 정상, X: 취소 등)

CREATE TABLE dbo.Orders
(
    OrderId     INT IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_Orders PRIMARY KEY, -- 기본적으로 클러스터형(환경에 따라 명시 권장)
    CustomerId  INT           NOT NULL,
    OrderDate   DATETIME      NOT NULL,
    Amount      DECIMAL(18,2) NOT NULL,
    Status      CHAR(1)       NOT NULL
);
GO

여기까지 생성하면, 현재 테이블에는 클러스터형 인덱스(PK_Orders, OrderId 기준)만 있고
비클러스터형 인덱스는 하나도 없는 상태입니다.

---

1-3. 테스트용 데이터 대량 삽입

아래 스크립트는 대략 수만 건의 데이터를 만듭니다.
너무 오래 걸리면 @Max 값을 줄여도 됩니다(예: 20,000).

SET NOCOUNT ON;

DECLARE @i   INT = 1;
DECLARE @Max INT = 50000;   -- 필요하면 10000~20000 정도로 줄여도 OK

WHILE (@i <= @Max)
BEGIN
    INSERT dbo.Orders (CustomerId, OrderDate, Amount, Status)
    VALUES
    (
        ABS(CHECKSUM(NEWID())) % 1000 + 1,                               -- CustomerId: 1 ~ 1000
        DATEADD(DAY, - (ABS(CHECKSUM(NEWID())) % 365), GETDATE()),        -- 최근 1년 날짜
        CAST(ABS(CHECKSUM(NEWID())) % 100000 / 100.0 AS DECIMAL(18,2)),   -- 0 ~ 1000.00
        CASE WHEN @i % 10 = 0 THEN 'X' ELSE 'O' END                       -- 10개 중 1개 정도 X(취소)
    );

    SET @i += 1;
END
GO

(선택) 데이터 생성 확인:

SELECT COUNT(*) AS row_count FROM dbo.Orders;
GO

---

2. 실행 계획 & STATISTICS 켜기

실습할 때 아래 옵션을 켜두면 변화가 훨씬 명확해집니다.

-- IO/시간 정보 출력
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

그리고 SSMS에서는:

• 실제 실행 계획 포함 체크 (Ctrl+M)
• 쿼리를 실행(F5)하면 결과 / 메시지 / 실행 계획 탭을 함께 보게 됩니다.

---

3. 인덱스가 거의 없을 때(=원시 상태)

3-1. 문제 쿼리

“특정 고객의 최근 주문 내역”을 보는 간단한 쿼리입니다.

USE DemoIndexTuning;
GO

-- 특정 고객의 최근 주문 100건 조회
SELECT TOP 100
       OrderId, CustomerId, OrderDate, Amount, Status
FROM dbo.Orders
WHERE CustomerId = 500
ORDER BY OrderDate DESC;
GO

---

3-2. 여기서 확인할 포인트(튜닝 전 기준선)

• 실행 계획에서 어떤 연산자가 나오는지 확인합니다.
• Clustered Index Scan이 나올 가능성이 큽니다.
• 이유: CustomerId에 인덱스가 없으니, 서버 입장에서는 전체를 훑으며 조건을 검사해야 합니다.
• 메시지 탭에서 logical reads가 어느 정도인지 확인해 둡니다.
• 이 값이 “튜닝 전 기준선(Baseline)”이 됩니다.
• ORDER BY OrderDate DESC 때문에 Sort 연산자가 등장할 수 있습니다.

---

4. 단일 컬럼 인덱스로 Index Seek 만들기

이제 CustomerId에 인덱스를 하나 만들어 보겠습니다.

4-1. 인덱스 생성

-- CustomerId에 비클러스터형 인덱스 생성
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON dbo.Orders (CustomerId);
GO

---

4-2. 같은 쿼리 다시 실행

SELECT TOP 100
       OrderId, CustomerId, OrderDate, Amount, Status
FROM dbo.Orders
WHERE CustomerId = 500
ORDER BY OrderDate DESC;
GO

---

4-3. 변화 관찰

• 실행 계획에서 Index Seek(IX_Orders_CustomerId) 를 볼 수 있을 가능성이 커집니다.
• 전체 logical reads가 줄어드는지 확인합니다.
• 다만 다음이 남아 있을 수 있습니다.
• ORDER BY OrderDate DESC 때문에 Sort가 남음(인덱스가 OrderDate 순으로 정렬되어 있지 않기 때문)
• SELECT 컬럼(Amount, Status 등)을 가져오기 위해 Key Lookup이 발생할 수 있음(아직 커버링이 아님)

핵심 포인트:

• WHERE 조건에 자주 쓰는 CustomerId에 인덱스 하나만 추가해도
  Scan → Seek로 전환되면서 성능이 크게 좋아질 수 있습니다.

---

5. 복합 인덱스로 범위 조건 + 정렬 튜닝하기

조건을 조금 더 현실적으로 바꿔보겠습니다.

• 특정 고객(CustomerId)의
• 올해 주문(OrderDate >= 올해 1월 1일)만
• 최신 순으로 조회

5-1. 범위 조건이 있는 쿼리

DECLARE @StartDate DATE = DATEFROMPARTS(YEAR(GETDATE()), 1, 1);

SELECT
    OrderId, CustomerId, OrderDate, Amount, Status
FROM dbo.Orders
WHERE CustomerId = 500
  AND OrderDate >= @StartDate
ORDER BY OrderDate DESC;
GO

현재 인덱스는 (CustomerId) 하나뿐입니다. 실행 계획은 보통 다음 패턴이 됩니다.

• CustomerId로 Seek 한 뒤
• OrderDate 조건은 Filter로 처리되거나
• 정렬 때문에 Sort가 남을 수 있음

---

5-2. 복합 인덱스 만들기(키 순서: CustomerId → OrderDate)

CustomerId + OrderDate 조합으로 인덱스를 새로 만들어 보겠습니다.

-- 기존 단일 인덱스 제거
DROP INDEX IX_Orders_CustomerId ON dbo.Orders;
GO

-- 복합 인덱스 생성 (CustomerId, OrderDate)
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate
ON dbo.Orders (CustomerId, OrderDate);
GO

---

5-3. 같은 쿼리 재실행

DECLARE @StartDate DATE = DATEFROMPARTS(YEAR(GETDATE()), 1, 1);

SELECT
    OrderId, CustomerId, OrderDate, Amount, Status
FROM dbo.Orders
WHERE CustomerId = 500
  AND OrderDate >= @StartDate
ORDER BY OrderDate DESC;
GO

---

5-4. 관찰 포인트

• 실행 계획에서 IX_Orders_CustomerId_OrderDate에 대한 Index Seek가 나오는지 확인합니다.
• 인덱스 키가 (CustomerId, OrderDate) 순으로 정렬되어 있기 때문에,
• CustomerId = 500으로 먼저 좁히고
• 그 안에서 OrderDate >= @StartDate 범위를 효율적으로 읽을 수 있습니다.
• ORDER BY OrderDate DESC에 대해
• Sort가 사라지거나(또는 비용이 크게 감소)할 수 있습니다.
  (SQL Server는 인덱스를 역방향 스캔(backward scan)해서 정렬을 만족시키기도 합니다.)
• logical reads가 케이스 4보다 더 줄었는지 비교합니다.

핵심 포인트:

• WHERE에서 CustomerId와 OrderDate를 함께 쓴다면
  인덱스 키도 보통 (CustomerId, OrderDate) 로 맞추는 것이 정석적인 패턴입니다.
• 인덱스 키 순서는 일반적으로
  첫 번째 필터링 기준 → 그다음 필터링 기준 순으로 잡습니다.

---

6. 커버링 인덱스와 INCLUDE로 Key Lookup 제거하기

6-1. Key Lookup(키 룩업)이란?

현재 인덱스 키는 (CustomerId, OrderDate)뿐입니다. 그런데 우리가 SELECT 하는 컬럼은:

• OrderId, CustomerId, OrderDate, Amount, Status

입니다. 인덱스에 없는 Amount, Status 등을 가져오려면 SQL Server는 보통 아래 방식으로 실행합니다.

1) 비클러스터형 인덱스로 조건에 맞는 행(키)을 찾고
2) 그 결과를 가지고 클러스터형 인덱스(PK_Orders) 로 다시 찾아가서(Key Lookup) 나머지 컬럼을 가져옴

이 “2번 단계”가 실행 계획의 Key Lookup 연산입니다.
(만약 테이블이 Heap이면 RID Lookup으로 표시됩니다.)

---

6-2. 현재 실행 계획에서 Key Lookup 확인

아래 쿼리를 실행하고 실행 계획에 Key Lookup이 있는지 확인해 보세요.

DECLARE @StartDate DATE = DATEFROMPARTS(YEAR(GETDATE()), 1, 1);

SELECT
    OrderId, CustomerId, OrderDate, Amount, Status
FROM dbo.Orders
WHERE CustomerId = 500
  AND OrderDate >= @StartDate
ORDER BY OrderDate DESC;
GO

행 수가 많고 자주 호출된다면 Key Lookup 때문에 비용이 꽤 커질 수 있습니다.

---

6-3. INCLUDE로 커버링 인덱스 만들기

Amount, Status를 인덱스에 포함 컬럼(INCLUDE) 로 추가해 보겠습니다.

-- 기존 복합 인덱스 제거
DROP INDEX IX_Orders_CustomerId_OrderDate ON dbo.Orders;
GO

-- 커버링 인덱스로 재생성: INCLUDE (Amount, Status)
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate
ON dbo.Orders (CustomerId, OrderDate)
INCLUDE (Amount, Status);
GO

---

6-4. 다시 같은 쿼리 실행

DECLARE @StartDate DATE = DATEFROMPARTS(YEAR(GETDATE()), 1, 1);

SELECT
    OrderId, CustomerId, OrderDate, Amount, Status
FROM dbo.Orders
WHERE CustomerId = 500
  AND OrderDate >= @StartDate
ORDER BY OrderDate DESC;
GO

---

6-5. 관찰 포인트

• 실행 계획에서 Key Lookup이 사라졌는지 확인합니다.
• 이제 필요한 컬럼이 인덱스에 모두 포함되어 있기 때문에
  클러스터형 인덱스(PK_Orders)로 다시 찾아갈 필요가 줄어듭니다.
• logical reads와 실행 시간이 어떻게 바뀌었는지 비교합니다.

핵심 포인트:

• SELECT되는 컬럼이 인덱스에 모두 포함되면 그 인덱스는 해당 쿼리에 대해 커버링 인덱스가 됩니다.
• 자주 호출되는 핵심 조회 쿼리에 커버링 인덱스를 적용하면 Key Lookup을 제거할 수 있고,
  이는 보통 I/O 감소 → 성능 개선으로 이어집니다.
• 단, INCLUDE에 너무 많은 컬럼을 넣으면 인덱스가 커져서
  쓰기 성능 저하 / 저장 공간 증가 / 캐시 효율 저하 등 역효과가 날 수 있으니 “핵심 쿼리 위주로 적당히”가 중요합니다.

---

7. 잘못 만든 인덱스 예시(선택도 낮은 컬럼)

이번에는 “선택도(Selectivity)가 낮은 컬럼을 단독 인덱스로 만들면 왜 효과가 작을 수 있는가?”를 봅니다.

7-1. Status 컬럼 단독 인덱스

Status는 O 또는 X 정도로 값 종류가 많지 않은 컬럼입니다.
(예: 10개 중 9개는 O, 1개는 X)

-- Status 컬럼 단독 인덱스 생성
CREATE NONCLUSTERED INDEX IX_Orders_Status
ON dbo.Orders (Status);
GO

---

7-2. Status만 조건으로 쓰는 쿼리

SELECT
    OrderId, CustomerId, OrderDate, Amount, Status
FROM dbo.Orders
WHERE Status = 'O';
GO

---

7-3. 실행 계획에서 확인할 것

의외로 SQL Server가 IX_Orders_Status 인덱스를 사용하지 않고
클러스터형 인덱스를 Scan할 수도 있습니다.

이유:

• Status='O'인 행이 너무 많다면(예: 전체 80~90%)
  “인덱스를 타도 결국 거의 다 읽어야 한다”로 판단할 수 있고,
  그 경우 Scan이 더 유리하다고 판단하기도 합니다.

요약:

• 값 종류가 적고, 편향된(특정 값이 대부분) 컬럼은
  단독 인덱스를 만들었을 때 효과가 거의 없을 수 있습니다.

---

8. Missing Index DMV로 후보 인덱스 찾기

조금 더 나아가서, SQL Server가 “이런 인덱스가 있으면 좋겠다”라고 추천하는 DMV도 있습니다.

SELECT TOP 10
    migs.user_seeks,
    migs.user_scans,
    mid.statement,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
JOIN sys.dm_db_missing_index_groups      AS mig
    ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details     AS mid
    ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID('DemoIndexTuning')
ORDER BY migs.user_seeks DESC;
GO

여러 쿼리를 실행한 뒤 위 DMV를 조회하면,
SQL Server가 어떤 컬럼에 인덱스가 있으면 좋겠는지 힌트를 줍니다.

다만 주의할 점:

• DMVs는 서버 재시작/상태 변화에 따라 초기화될 수 있습니다.
• “추천 인덱스”는 실제 운영 패턴(쓰기 부하, 중복 인덱스, 키 순서, 필터 인덱스 등)을 고려하지 못합니다.
• 따라서 “그대로 다 만들기”가 아니라,
• 실제 쿼리/플랜을 보고
• 기존 인덱스와 통합/중복 제거
• 비용 대비 효과를 검증
  하는 방식이 중요합니다.

---

9. 인덱스 튜닝 체크리스트

실습을 다 따라왔다면, 아래 질문에 YES/NO를 직접 답해보세요.

• 이 인덱스는 어떤 쿼리를 빠르게 만들기 위한 것인지 명확한가?
• WHERE / JOIN / ORDER BY에 맞게 인덱스 키 순서를 잘 잡았는가?
• 선택도가 너무 낮은 컬럼을 단독 키로 쓰고 있지 않은가?
• 자주 호출되는 쿼리에 대해 커버링 인덱스(INCLUDE)를 고려해 봤는가?
• 인덱스를 만들고 나서 실행 계획 / STATISTICS IO / TIME로 실제 효과를 확인했는가?
• 비슷한 인덱스가 여러 개라면 통합해서 줄일 수는 없는가?

---