[MSSQL] 복합 인덱스 선행 컬럼 누락이 Index Scan을 만드는 이유와 키 순서 설계

복합 인덱스 키 순서: 실무적 정의와 운영상 중요성

MSSQL의 복합 인덱스(Composite Index)는 둘 이상의 컬럼을 하나의 키로 구성한 B-Tree 인덱스이다. INDEX (CustomerID, OrderDate)는 CustomerID를 먼저 정렬하고, CustomerID가 같은 행 내부에서 OrderDate를 정렬한다.

따라서 전체 인덱스가 OrderDate 순서로 정렬된 것은 아니다. 첫 번째 키인 CustomerID가 조건절에서 빠지면 SQL Server는 특정 OrderDate가 존재할 연속 범위의 시작점과 종료점을 계산할 수 없다. 이 경우 옵티마이저는 후행 키 조건을 만족하는 행을 찾기 위해 인덱스 리프 페이지를 넓게 읽는 Index Scan을 선택할 수 있다.

핵심은 WHERE 절에 조건을 작성한 순서가 아니다. 인덱스 정의의 키 순서와 실제 검색 조건이 일치하는지가 중요하다. 선행 컬럼이 누락되면 후행 컬럼 조건은 탐색 경계가 아니라 스캔 도중 행을 거르는 잔여 조건(Residual Predicate)으로 처리될 가능성이 높다.

Part 1. 복합 인덱스 기본 문법 및 사용법 (Basic Syntax)

다음 예제는 주문 테이블에서 고객과 주문일을 검색하는 구조이다. 검증용 테이블은 운영 데이터를 복제한 별도 환경에서 준비해야 한다. 데이터 분포와 통계 상태가 실행 계획을 바꾸므로 운영 서버에서 인덱스를 즉시 추가하거나 강제해서는 안 된다.

CREATE INDEX IX_Orders_CustomerID_OrderDate
ON dbo.Orders (CustomerID, OrderDate)
INCLUDE (OrderStatus, TotalAmount);

SELECT OrderID, OrderStatus, TotalAmount
FROM dbo.Orders
WHERE CustomerID = @CustomerID
  AND OrderDate >= @FromDate
  AND OrderDate <  @ToDate;

위 쿼리는 첫 번째 키 CustomerID에 등가 조건을 적용하고 두 번째 키 OrderDate에 범위 조건을 적용한다. SQL Server는 CustomerID = @CustomerID와 날짜 범위를 결합하여 읽어야 할 키 구간을 계산할 수 있다. 실행 계획에서는 일반적으로 Index Seek 연산자와 Seek Predicates가 확인된다.

날짜의 종료 조건은 < @ToDate 형태의 반개방 구간을 사용하는 것이 안전하다. datetime이나 datetime2 컬럼에서 하루의 마지막 시각을 임의로 생성하면 정밀도 차이로 경계 행이 누락될 수 있다.

SELECT OrderID, OrderStatus, TotalAmount
FROM dbo.Orders
WHERE OrderDate >= @FromDate
  AND OrderDate <  @ToDate;

두 번째 쿼리는 동일한 인덱스를 사용하면서 선행 키 CustomerID를 생략한다. OrderDate 값은 서로 다른 CustomerID 구간마다 반복되어 존재한다. SQL Server는 하나의 연속된 날짜 구간으로 바로 이동할 수 없으므로 인덱스 전체 또는 상당 부분을 읽고 각 행의 OrderDate를 평가한다.

검색 조건	인덱스 키	탐색 경계 계산	예상 접근 방식
CustomerID 등가 + OrderDate 범위	(CustomerID, OrderDate)	가능	Index Seek 가능
OrderDate 범위만 사용	(CustomerID, OrderDate)	첫 키가 없어 직접 계산 불가	Index Scan 가능성 증가
OrderDate 범위만 사용	(OrderDate, CustomerID)	가능	Index Seek 가능
CustomerID 범위 + OrderDate 등가	(CustomerID, OrderDate)	첫 키 범위까지만 효율적	후행 키가 잔여 조건이 될 수 있음

Part 2. 내부 동작 메커니즘과 안티 패턴

복합 인덱스의 키는 개별 컬럼 목록이 아니라 정렬된 튜플로 취급된다. (CustomerID, OrderDate)의 정렬 순서는 전화번호부에서 성을 먼저 정렬하고 같은 성 내부에서 이름을 정렬하는 방식과 유사하지만, 운영 관점에서는 탐색 가능한 연속 구간의 존재 여부로 이해해야 한다.

예를 들어 키가 (10, 2026-01-01), (10, 2026-02-01), (20, 2026-01-01) 순서로 저장되었다면 1월 데이터는 인덱스 전체에서 하나의 연속 구간을 형성하지 않는다. CustomerID 값마다 날짜 범위가 다시 시작된다. 이로 인해 OrderDate만 지정한 쿼리는 단일 루트 탐색으로 대상 리프 구간을 확정할 수 없다.

실행 계획에서는 연산자 이름만 확인해서는 안 된다. Index Seek가 표시되어도 넓은 범위를 읽은 뒤 Predicate로 필터링할 수 있으며, Index Scan도 작은 인덱스를 순차적으로 읽는 편이 비용상 유리해 선택될 수 있다. DBA는 실제 실행 계획의 Seek Predicates, Predicate, Actual Number of Rows, Estimated Number of Rows와 읽은 행 수를 함께 확인해야 한다.

실행 계획 속성	의미	운영상 판단
Seek Predicates	B-Tree 탐색의 시작점과 종료점을 결정하는 조건	조건이 여기에 포함되면 읽을 키 범위를 줄일 수 있음
Predicate	읽은 행에 추가로 적용하는 잔여 필터	읽기 이후 제거되는 행이 많으면 CPU와 논리 I/O 증가
Actual Rows Read	연산자가 실제로 검사한 행 수	반환 행 수보다 지나치게 크면 접근 범위가 비효율적임
Actual Rows	상위 연산자로 반환한 행 수	읽은 행 수와 비교하여 필터 효율 판단
Estimated Rows	통계 기반 예상 행 수	실제값과 차이가 크면 통계와 데이터 편향 점검 필요

가장 흔한 안티 패턴은 모든 검색 컬럼을 하나의 복합 인덱스에 넣으면 각 컬럼을 독립적으로 Seek할 수 있다고 판단하는 것이다. (A, B, C) 인덱스는 일반적으로 A, A+B, A+B+C 조건에 유리하지만 B 또는 C 단독 검색을 위한 범용 인덱스가 아니다.

또 다른 안티 패턴은 WHERE 절의 조건 순서를 인덱스 순서대로 재배치하면 해결된다고 판단하는 것이다. WHERE OrderDate = @Date AND CustomerID = @ID와 조건 순서를 바꾼 쿼리는 논리적으로 동일하다. 옵티마이저가 조건을 재배치하므로 SQL 텍스트의 순서가 아니라 인덱스 키 정의와 연산자 종류가 접근 경로를 결정한다.

선행 컬럼에 범위 조건을 적용한 뒤 후행 컬럼에 등가 조건을 적용하는 경우도 주의가 필요하다. CustomerID > @ID AND OrderDate = @Date는 첫 키의 넓은 범위를 탐색한 뒤 각 구간에서 OrderDate를 검사할 수 있다. 복합 인덱스는 일반적으로 첫 번째 범위 조건 이후 키를 탐색 범위 축소에 충분히 활용하기 어렵다.

Part 3. 성능 최적화 및 튜닝 전략

키 순서가 원인인지 확인하려면 같은 컬럼으로 순서만 다른 인덱스 2종을 별도 검증 환경에서 비교해야 한다. 한 번에 두 인덱스를 모두 활성화하면 옵티마이저가 예상과 다른 인덱스를 선택할 수 있으므로 실제 사용 인덱스 이름을 실행 계획에서 확인해야 한다.

CREATE INDEX IX_Orders_CustomerID_OrderDate
ON dbo.Orders (CustomerID, OrderDate)
INCLUDE (OrderStatus, TotalAmount);

CREATE INDEX IX_Orders_OrderDate_CustomerID
ON dbo.Orders (OrderDate, CustomerID)
INCLUDE (OrderStatus, TotalAmount);

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT OrderID, OrderStatus, TotalAmount
FROM dbo.Orders
WHERE OrderDate >= @FromDate
  AND OrderDate <  @ToDate;

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

첫 번째 인덱스는 날짜 단독 검색에서 선행 키가 누락된다. 두 번째 인덱스는 OrderDate가 선행 키이므로 날짜 범위의 시작점과 종료점을 계산할 수 있다. 실제 비교에서는 각 실행 계획의 Seek Predicates와 Predicate를 캡처하고, 메시지 탭의 해당 테이블 논리 읽기 수를 기록해야 한다.

인덱스 힌트는 검증 환경에서 두 구조의 차이를 분리해서 관찰하는 용도로 제한해야 한다. 운영 쿼리에 WITH (INDEX(...))를 고정하면 데이터 분포와 통계가 변해도 다른 접근 경로를 선택하지 못한다. 결과적으로 장기적인 성능 저하를 유발할 수 있다.

검증 항목	(CustomerID, OrderDate)	(OrderDate, CustomerID)	판정 기준
물리 연산자	작성자 측정값 입력	작성자 측정값 입력	Seek 또는 Scan 이름만으로 결론 내리지 않음
Seek Predicates	작성자 캡처 내용 입력	작성자 캡처 내용 입력	OrderDate 범위가 탐색 경계에 포함되는지 확인
Predicate	작성자 캡처 내용 입력	작성자 캡처 내용 입력	OrderDate가 잔여 필터로 남는지 확인
Actual Rows Read / Actual Rows	작성자 측정값 입력	작성자 측정값 입력	읽고 버린 행의 비율 비교
논리 읽기 수	작성자 측정값 입력	작성자 측정값 입력	동일 파라미터와 동일 캐시 조건에서 비교

논리 읽기 수는 SET STATISTICS IO ON으로 수집한다. 비교할 때는 동일한 데이터, 파라미터, 통계, 인덱스 상태를 유지해야 한다. 한 쿼리만 반복 실행해 캐시가 예열된 상태와 최초 실행 결과를 섞으면 비교의 의미가 약해진다.

실제 실행 계획은 예상 실행 계획보다 많은 정보를 제공한다. 특히 Actual Rows Read가 Actual Rows보다 크게 나타나면 많은 행을 읽은 뒤 제거했다는 의미이다. 이 차이는 Index Scan뿐 아니라 잔여 조건을 가진 Index Seek에서도 발생하므로 반드시 확인해야 한다.

키 순서는 선택도가 가장 높은 컬럼을 무조건 앞으로 배치하는 방식으로 결정하지 않는다. 실제 쿼리의 등가 조건, 범위 조건, 정렬, 조인, 집계 패턴을 함께 평가해야 한다. 일반적으로 자주 사용되는 등가 조건을 앞에 두고 범위 조건을 뒤에 두면 연속된 탐색 구간을 만들기 유리하지만, 날짜 단독 조회가 핵심 업무라면 날짜를 선행 키로 둔 별도 인덱스가 필요할 수 있다.

업무 패턴	우선 검토할 키 순서	이유
특정 고객의 기간별 주문 조회	(CustomerID, OrderDate)	고객 등가 조건 이후 날짜 범위 탐색 가능
전체 고객의 일자별 주문 조회	(OrderDate, CustomerID)	날짜 단독 범위가 연속된 키 구간을 형성
두 패턴 모두 빈번함	서로 다른 인덱스 2종 검토	하나의 키 순서로 양방향 탐색을 동일하게 지원할 수 없음
조회 빈도는 낮고 쓰기가 많음	추가 인덱스 보류 가능	인덱스 유지 비용과 저장 공간 증가를 함께 평가해야 함

인덱스를 추가하면 INSERT, UPDATE, DELETE마다 키 정렬과 페이지 변경이 발생한다. 유사한 인덱스가 늘어나면 버퍼 풀 점유, 로그 기록, 저장 공간, 통계 관리 비용도 증가한다. DBA는 누락 인덱스 제안만 따르지 말고 기존 인덱스와의 중복, 쓰기 비율, 호출 빈도, 반환 행 수를 함께 검토해야 한다.

INCLUDE 컬럼은 결과 반환에 필요한 컬럼을 커버하여 Key Lookup을 줄이는 용도로 사용한다. INCLUDE 순서는 탐색 경계를 만들지 않으며 선행 키 누락 문제를 해결하지 않는다. 검색 조건에 사용되는 컬럼을 INCLUDE에만 배치하면 해당 조건은 일반적으로 잔여 필터로 처리된다.

파라미터에 따라 선택도가 크게 달라지는 쿼리는 키 순서를 조정한 뒤에도 계획 품질이 흔들릴 수 있다. 특정 고객은 주문이 매우 많고 대부분의 고객은 적다면 통계 히스토그램, 파라미터 스니핑, 실제값과 예상값의 차이를 추가로 확인해야 한다. 무조건적인 OPTION (RECOMPILE) 적용은 컴파일 CPU 비용을 증가시키므로 호출 빈도와 함께 판단한다.

실행 계획 검증 시 확인할 운영 기준

검증은 실제 업무에서 사용하는 대표 파라미터 여러 종류로 수행해야 한다. 좁은 날짜 범위, 넓은 날짜 범위, 데이터가 많은 고객, 데이터가 적은 고객을 분리하면 데이터 편향에 따른 계획 차이를 확인할 수 있다. 측정 결과에는 SQL Server 버전, 호환성 수준, 테이블 행 수, 통계 갱신 시점도 함께 기록하는 것이 필요하다.

인덱스 변경 전에는 대상 쿼리의 호출 빈도와 총 논리 읽기 기여도를 확인해야 한다. 단일 실행이 빨라져도 쓰기 부하가 큰 테이블에 중복 인덱스를 추가하면 시스템 전체 비용은 증가할 수 있다. Query Store를 사용할 수 있다면 변경 전후의 실행 계획, 평균 자원 사용량, 계획 회귀 여부를 동일 기간 기준으로 비교한다.

관련 실행 계획의 조인 연산자와 행 추정 문제는 [MSSQL] INNER JOIN 실행 계획 분석과 튜닝에서 함께 확인할 수 있다. 조인 입력에서 불필요한 Scan이 발생하면 Hash Match의 메모리 그랜트(Memory Grant)와 디스크 I/O 병목으로 이어질 수 있다.

컬럼 가공으로 탐색 조건이 무력화되는 구조는 DBMS가 달라도 동일한 SARGable 원칙으로 판단할 수 있다. 함수 적용에 따른 전체 테이블 스캔 사례는 [MySQL] DATEDIFF 성능 저하와 인덱스 무력화 해결 방법과 [MySQL] DATE_FORMAT이 유발하는 Full Table Scan과 쿼리 튜닝 가이드에서 확인할 수 있다.

복합 인덱스의 첫 번째 컬럼이 조건절에서 빠지면 Seek가 Scan으로 바뀌는 이유는 후행 키가 정렬되지 않았기 때문이 아니라, 선행 키별 구간 내부에서만 정렬되어 전체 인덱스에 대한 단일 탐색 범위를 만들 수 없기 때문이다. 성능 최적화의 핵심은 실행 빈도가 높은 쿼리의 등가 조건과 범위 조건을 기준으로 키 순서를 설계하고, Seek Predicates와 Predicate를 구분하여 확인하는 것이다.

운영 환경에서는 연산자 이름만으로 인덱스 효율을 판단해서는 안 된다. 실제 실행 계획의 읽은 행 수, 반환 행 수, 논리 읽기 수를 비교하고 추가 인덱스의 쓰기 비용까지 평가해야 한다. 선행 키 누락을 제거하고 탐색 가능한 범위를 구성하는 것이 Index Scan과 디스크 I/O 병목을 줄이는 기본 원칙이다.