인덱스 아키텍처 및 디자인 가이드

적용 대상:SQL Server Azure SQL Database Azure SQL Managed Instance Microsoft Fabric의 SQL 데이터베이스

효율적인 인덱스를 디자인하는 것은 좋은 데이터베이스 및 애플리케이션 성능을 달성하는 데 중요합니다. 인덱스 부족, 과잉 인덱싱 또는 잘못 디자인된 인덱스가 데이터베이스 성능 문제의 주요 원인입니다.

이 가이드에서는 인덱스 아키텍처 및 기본 사항을 설명하고 애플리케이션의 요구 사항에 맞게 효과적인 인덱스를 디자인하는 데 도움이 되는 모범 사례를 제공합니다.

사용 가능한 인덱스 형식에 대한 자세한 내용은 인덱스를 참조하세요.

이 가이드에서는 다음과 같은 형식의 인덱스에 대해 설명합니다.

기본 스토리지 형식	인덱스 유형
디스크 기반 rowstore
	Clustered
	Nonclustered
	Unique
	Filtered
Columnstore
	클러스터형 columnstore
	비클러스터형 columnstore
Memory-optimized
	Hash
	메모리 최적화 비클러스터형

XML 인덱스에 대한 자세한 내용은 XML 인덱스(SQL Server) 및 SXI(선택적 XML 인덱스)를 참조하세요.

공간 인덱스에 대한 자세한 내용은 공간 인덱스 개요를 참조하세요.

전체 텍스트 인덱스에 대한 내용은 전체 텍스트 인덱스 채우기를 참조하세요.

인덱스 기본 사항

일반적인 책을 생각해 보세요. 책의 끝에는 책에서 정보를 신속하게 찾는 데 도움이 되는 인덱스가 있습니다. 인덱스는 정렬된 키워드 목록이며 각 키워드 옆에는 각 키워드를 찾을 수 있는 페이지를 가리키는 여러 페이지 번호가 있습니다.

rowstore 인덱스는 값의 순서가 지정된 목록이며 각 값에 대해 이러한 값이 있는 데이터 페이지에 대한 포인터가 있습니다. 인덱스 자체는 인덱스 페이지라고도 하는 페이지에 저장됩니다. 일반 책에서 인덱스가 여러 페이지에 걸쳐 있고 예를 들어 단어가 SQL 포함된 모든 페이지에 대한 포인터를 찾아야 하는 경우 키워드 SQL가 포함된 인덱스 페이지를 찾을 때까지 인덱스 시작부터 리프를 거쳐야 합니다. 여기서 모든 책 페이지의 포인터를 따릅니다. 인덱스의 시작 부분에 각 문자를 찾을 수 있는 위치의 사전순 목록이 포함된 단일 페이지를 만들면 이 기능을 더욱 최적화할 수 있습니다. 예: "A에서 D까지 - 페이지 121", "E에서 G까지 - 페이지 122" 등. 이 추가 페이지는 시작 장소를 찾기 위해 인덱스로 리프하는 단계를 제거합니다. 일반적인 책에는 이러한 페이지가 없지만 rowstore 인덱스에는 있습니다. 이 단일 페이지를 인덱스의 루트 페이지라고 합니다. 루트 페이지는 인덱스에서 사용되는 트리 구조의 시작 페이지입니다. 트리 비유를 따라 실제 데이터의 포인터가 포함된 끝 페이지는 트리의 “리프 페이지”라고 합니다.

인덱스는 테이블이나 보기와 연결된 디스크상 또는 메모리 내 구조로, 테이블이나 보기의 행 검색 속도를 높입니다. rowstore 인덱스에는 테이블 또는 뷰의 하나 이상의 열에 있는 값으로 작성된 키가 포함됩니다. rowstore 인덱스의 경우 이러한 키는 데이터베이스 엔진이 키 값과 연결된 행을 빠르고 효율적으로 찾을 수 있는 트리 구조(B+ 트리)에 저장됩니다.

rowstore 인덱스는 행과 열이 있는 테이블로 논리적으로 구성된 데이터를 저장하고 rowstore¹이라는 행 단위 데이터 형식으로 물리적으로 저장됩니다. columnstore라고 하는 데이터 열 단위를 저장하는 다른 방법이 있습니다.

데이터베이스 및 해당 워크로드에 적합한 인덱스를 디자인하는 것은 쿼리 속도, 인덱스 업데이트 비용 및 스토리지 비용 간의 복잡한 분산 작업입니다. 좁은 디스크 기반 rowstore 인덱스 또는 인덱스 키에 열이 거의 없는 인덱스는 스토리지 공간을 줄이고 업데이트 오버헤드를 줄입니다. 반면에 와이드 인덱스는 더 많은 쿼리를 향상시킬 수 있습니다. 가장 효율적인 인덱스 집합을 찾기 전에 여러 가지 디자인을 실험해야 할 수 있습니다. 애플리케이션이 발전함에 따라 최적의 성능을 유지하기 위해 인덱스를 변경해야 할 수 있습니다. 데이터베이스 스키마 또는 애플리케이션 디자인에 영향을 주지 않고 인덱스를 추가, 수정 및 제거할 수 있습니다. 따라서 주저하지 말고 다양한 인덱스를 실험해 보아야 합니다.

데이터베이스 엔진의 쿼리 최적화 프로그램은 일반적으로 쿼리를 실행할 가장 효과적인 인덱스를 선택합니다. 쿼리 최적화 프로그램에서 특정 쿼리에 사용하는 인덱스를 확인하려면 SQL Server Management Studio의 쿼리 메뉴에서 예상 실행 계획 표시 또는 실제 실행 계획 포함을 선택합니다.

인덱스를 많이 사용한다고 해서 항상 성능이 좋은 것은 아니고, 좋은 성능이 항상 효율적인 인덱스 사용을 의미한다고 생각하지 마세요. 인덱스를 사용하는 것이 성능을 극대화하는 데 도움이 된다면 쿼리 최적화 프로그램의 작업은 간단할 것입니다. 실제로 잘못된 인덱스 선택으로 인해 최적 성능이 구현되지 못할 수 있습니다. 따라서 쿼리 최적화 프로그램의 작업은 성능이 향상되는 경우에만 인덱스 또는 인덱스 조합을 선택하고 성능이 저하될 때 인덱싱된 검색을 방지하는 것입니다.

일반적인 디자인 실수는 "최적화 프로그램 선택 사항 제공"을 위해 많은 인덱스를 추측적으로 만드는 것입니다. 결과적으로 과도하게 인덱싱하면 데이터 수정 속도가 느려지고 동시성 문제가 발생할 수 있습니다.

¹ Rowstore는 관계형 테이블 데이터를 저장하는 전통적인 방법이었습니다. Rowstore 는 기본 데이터 스토리지 형식이 힙, B+ 트리(클러스터형 인덱스) 또는 메모리 최적화 테이블인 테이블을 나타냅니다. 디스크 기반 rowstore는 메모리 최적화 테이블을 제외합니다.

인덱스 디자인 작업

다음 작업은 인덱스 디자인에 권장되는 전략을 구성합니다.

데이터베이스 및 애플리케이션의 특성을 이해합니다.

예를 들어 높은 처리량을 유지해야 하는 자주 데이터를 수정하는 OLTP(온라인 트랜잭션 처리) 데이터베이스에서 가장 중요한 쿼리를 대상으로 하는 몇 가지 좁은 rowstore 인덱스는 좋은 초기 인덱스 디자인입니다. 처리량이 매우 높은 경우 잠금 및 래치 없는 디자인을 제공하는 메모리 최적화 테이블 및 인덱스를 고려합니다. 자세한 내용은 이 가이드의 메모리 최적화 비클러스터형 인덱스 디자인 지침 및 해시 인덱스 디자인 지침을 참조하세요.

반대로 매우 큰 데이터 집합을 신속하게 처리해야 하는 분석 또는 OLAP(데이터 웨어하우징) 데이터베이스의 경우 클러스터형 columnstore 인덱스를 사용하는 것이 특히 적합합니다. 자세한 내용은 이 가이드의 Columnstore 인덱스( 개요 또는 Columnstore 인덱스 아키텍처 )를 참조하세요.
가장 자주 사용되는 쿼리의 특징을 이해합니다.

예를 들어 자주 사용되는 쿼리가 둘 이상의 테이블을 조인한다는 것을 알면 이러한 테이블에 대한 인덱스 집합을 결정하는 데 도움이 됩니다.
쿼리 조건자에서 사용되는 열의 데이터 분포를 이해합니다.

예를 들어 인덱스는 고유 데이터 값이 많은 열에 유용할 수 있지만 중복 값이 많은 열에는 유용하지 않을 수 있습니다. NULL이 많은 열 또는 데이터의 하위 집합이 잘 정의된 열의 경우 필터링된 인덱스를 사용할 수 있습니다. 자세한 내용은 이 가이드의 필터링된 인덱스 디자인 지침을 참조하세요.
성능을 향상시킬 수 있는 인덱스 옵션을 결정합니다.

예를 들어 기존 큰 테이블에 클러스터형 인덱스 만들기는 인덱스 옵션의 ONLINE 이점을 얻을 수 있습니다. ONLINE 옵션을 사용하면 인덱스가 생성되거나 재구축되는 동안 기본 데이터에 대한 동시 작업을 계속할 수 있습니다. 행 또는 페이지 데이터 압축을 사용하면 인덱스의 I/O 및 메모리 공간을 줄여 성능을 향상시킬 수 있습니다. 자세한 내용은 CREATE INDEX를 참조하세요.
테이블의 기존 인덱스를 검사하여 중복되거나 매우 유사한 인덱스를 만들지 않도록 합니다.

기존 인덱스를 수정하는 것이 새 인덱스를 대부분 중복되게 만드는 것보다는 종종 더 좋습니다. 예를 들어 이러한 열을 사용하여 새 인덱스를 만드는 대신 기존 인덱스에 하나 또는 두 개의 추가 포함 열을 추가하는 것이 좋습니다. 이는 누락된 인덱스 제안으로 비클러스터형 인덱스를 튜닝하거나 데이터베이스 엔진 튜닝 관리자를 사용하는 경우 특히 관련이 있습니다. 이 경우 동일한 테이블 및 열에 유사한 인덱스 변형이 제공될 수 있습니다.

일반 인덱스 디자인 지침

데이터베이스, 쿼리 및 테이블 열의 특성을 이해하면 애플리케이션이 발전함에 따라 처음에 최적의 인덱스를 디자인하고 디자인을 수정하는 데 도움이 될 수 있습니다.

데이터베이스 고려 사항

인덱스를 디자인할 때 다음 데이터베이스 지침을 고려하세요.

테이블에 많은 인덱스가 존재할 경우, INSERT, UPDATE, DELETE, 그리고 MERGE 문장의 성능에 영향을 미칠 수 있습니다. 이는 테이블의 데이터가 변경될 때 인덱스의 데이터도 변경되어야 하기 때문입니다. 예를 들어 열이 여러 인덱스에 사용되고 해당 열의 데이터를 수정하는 문을 실행하는 UPDATE 경우 해당 열이 포함된 각 인덱스도 업데이트해야 합니다.
- 업데이트가 많은 테이블을 무리하게 인덱싱하지 말고 인덱스를 좁게, 즉 가능한 열의 수가 적게 유지하세요.
- 데이터 수정이 거의 없지만 많은 양의 데이터가 있는 테이블에 더 많은 인덱스를 가질 수 있습니다. 이러한 테이블의 경우 인덱스 업데이트 오버헤드가 허용되는 동안 다양한 인덱스가 쿼리 성능에 도움이 될 수 있습니다. 그러나 인덱스를 추측적으로 만들지 마세요. 인덱스 사용량을 모니터링하고 시간이 지남에 따라 사용되지 않는 인덱스를 제거합니다.
데이터베이스 엔진이 기본 테이블 검색을 수행하는 것보다 데이터 검색을 트래버스하는 데 더 오래 걸릴 수 있으므로 작은 테이블을 인덱싱하는 것이 최적이 아닐 수 있습니다. 따라서 작은 테이블의 인덱스는 사용되지 않을 수 있지만 테이블의 데이터가 업데이트될 때 계속 업데이트해야 합니다.
뷰에 집계 및/또는 조인이 포함된 경우 뷰의 인덱스는 상당한 성능 향상을 제공할 수 있습니다. 자세한 정보는 인덱싱된 뷰 만들기를 참조하세요.
Azure SQL Database에서 주 복제본의 데이터베이스는 인덱스용 데이터베이스 관리자 성능 권장 사항을 자동으로 생성합니다. 필요에 따라 자동 인덱스 튜닝을 사용하도록 설정할 수 있습니다.
쿼리 저장소는 최적이 아니면 성능이 저하된 쿼리를 식별하는 데 도움이 되며 최적화 프로그램에서 선택한 인덱스를 볼 수 있는 쿼리 실행 계획의 기록을 제공합니다. 이 데이터를 사용하여 가장 빈번하고 리소스를 사용하는 쿼리에 집중하여 인덱스 튜닝 변경 내용을 가장 영향력 있게 만들 수 있습니다.

쿼리 고려 사항

인덱스를 디자인할 때 다음과 같은 쿼리 지침을 고려합니다.

조건 및 조인 표현식에서 자주 사용되는 열에 비클러스터 인덱스를 생성합니다. 이러한 열은 SARGable 열입니다. 그러나 인덱스에 불필요한 열을 추가하지 않아야 합니다. 인덱스 열을 너무 많이 추가하면 디스크 공간 및 인덱스 업데이트 성능에 부정적인 영향을 줄 수 있습니다.

관계형 데이터베이스에서 SARGable 이라는 용어는 인덱스를 사용하여 쿼리 실행 속도를 높일 수 있는 Search ARGumentable 조건자를 나타냅니다. 자세한 내용은 SQL Server 및 Azure SQL 인덱스 아키텍처 및 디자인 가이드를 참조하세요.

Tip

항상 만드는 인덱스가 쿼리 워크로드에서 실제로 사용되는지 확인합니다. 사용되지 않는 인덱스를 삭제합니다.

인덱스 사용 통계는 sys.dm_db_index_usage_stats 및 sys.dm_db_index_operational_stats 사용할 수 있습니다.
인덱스를 포함하면 인덱스 자체에 쿼리 요구 사항을 충족하는 데 필요한 모든 데이터가 포함되기 때문에 쿼리 성능이 향상될 수 있습니다. 즉, 요청된 데이터를 검색하려면 테이블 또는 클러스터형 인덱스의 데이터 페이지가 아닌 인덱스 페이지만 필요하기 때문에 전체 디스크 I/O가 줄어듭니다. 예를 들어 A, B, A 열에 대해 만든 복합 인덱스가 포함된 테이블에서 B와 C 열을 쿼리하면 지정된 데이터를 인덱스 자체에서만 검색할 수 있습니다.

Note

커버링 인덱스는 기본 테이블에 액세스하지 않고 쿼리의 모든 데이터 액세스를 직접 충족하는 비클러스터형 인덱스입니다.

이러한 인덱스에는 인덱스 키에 필요한 모든 SARGable 열과 포함된 열로 SARGable이 아닌 열이 있습니다. 즉, 쿼리에 필요한 모든 열이 WHERE, JOIN, GROUP BY 절 또는 SELECT 또는 UPDATE 절에 있든 관계없이 인덱스에 있습니다.

테이블 자체의 행 및 열과 비교할 때 인덱스가 충분히 좁으면 쿼리를 실행하는 I/O가 훨씬 적을 수 있습니다. 즉, 모든 열의 작은 하위 집합입니다.

큰 테이블의 작은 부분을 검색할 때와 해당 작은 부분이 고정 조건자에서 정의되는 경우 인덱스를 포함하는 것이 좋습니다.

열이 너무 많은 커버링 인덱스를 생성하지 마십시오. 이는 데이터베이스 스토리지, I/O, 메모리 사용량을 증가시켜 이점이 감소하기 때문입니다.
같은 행을 업데이트하기 위해 여러 쿼리를 사용하는 대신 단일 문에서 가능한 한 많은 행을 삽입하거나 수정하는 쿼리를 작성하세요. 이렇게 하면 인덱스 업데이트 오버헤드가 줄어듭니다.

열 고려 사항

인덱스를 디자인할 때 다음 열 지침을 고려하세요.

특히 클러스터형 인덱스의 경우 인덱스 키의 길이를 짧게 유지합니다.
ntext, text, image, varchar(max), nvarchar(max), varbinary(max), json 및 벡터 데이터 형식의 열은 인덱스 키 열로 지정할 수 없습니다. 그러나 이러한 데이터 형식의 열은 비클러스터형 인덱스에 키가 아닌(포함) 인덱스 열로 추가할 수 있습니다. 자세한 내용은 이 가이드의 비클러스터형 인덱스에 포함된 열 사용 섹션을 참조하세요 .
열 고유성을 검사하세요. 동일한 키 열에서 고유하지 않은 인덱스 대신 고유 인덱스는 인덱스를 더 유용하게 만드는 쿼리 최적화 프로그램의 추가 정보를 제공합니다. 자세한 내용은 이 가이드의 고유 인덱스 디자인 지침을 참조하세요.
열의 데이터 분산을 조사합니다. 행이 많지만 고유 값이 거의 없는 열에 인덱스를 만들면 쿼리 최적화 프로그램에서 인덱스를 사용하더라도 쿼리 성능이 향상되지 않을 수 있습니다. 비유적으로, 도시에 있는 모든 사람이 스미스나 존스라는 이름을 가진다면, 성으로 알파벳순으로 정렬된 물리적 전화 디렉터리는 사람을 찾는 데 도움이 되지 않습니다. 데이터 배포에 대한 자세한 내용은 통계를 참조하세요.
잘 정의된 하위 집합이 있는 열(예: NULL이 많은 열, 값 범주가 있는 열, 고유한 값 범위의 열)에서 필터링된 인덱스를 사용하는 것이 좋습니다. 잘 디자인된 필터링된 인덱스는 해당 하위 집합이 많은 쿼리와 관련된 경우 테이블에 있는 모든 행의 작은 하위 집합을 저장하여 쿼리 성능을 향상시키고 인덱스 업데이트 비용을 절감하며 스토리지 비용을 줄일 수 있습니다.
키에 여러 열이 포함된 경우 인덱스 키 열의 순서를 고려합니다. 쿼리에서 동등(=), 비동등(>,>=,<,<=) 또는 BETWEEN 표현식에 사용되거나 조인에 참여하는 열은 먼저 배치해야 합니다. 추가 열은 고유성 수준에 따라 정렬되어야 합니다. 즉, 가장 고유한 열부터 가장 고유하지 않은 열까지의 순서로 정렬해야 합니다.

예를 들어 인덱스가 LastName, FirstName로 정의된 경우, WHERE 절의 쿼리 조건이 WHERE LastName = 'Smith' 또는 WHERE LastName = Smith AND FirstName LIKE 'J%'일 때 인덱스가 유용합니다. 그러나 쿼리 최적화 프로그램에서는 검색된 쿼리에 WHERE FirstName = 'Jane'대해서만 인덱스가 사용되지 않거나 인덱스가 이러한 쿼리의 성능을 향상하지 않습니다.
계산 열이 쿼리 조건자에서 포함되는 경우 인덱싱하는 것이 좋습니다. 자세한 내용은 계산된 열의 인덱스를 참조하세요.

인덱스 특성

인덱스가 쿼리에 적합하다고 판단되면 상황에 가장 적합한 인덱스 형식을 선택할 수 있습니다. 인덱스 특성은 다음과 같습니다.

클러스터형 또는 비클러스터형
고유 또는 비유니크
단일 열 또는 여러 열
인덱스의 키 열에 대한 오름차순 또는 내림차순
비클러스터형 인덱스에 대해 모든 행 또는 필터링된 행
Columnstore(컬럼스토어) 또는 Rowstore(로우스토어)
메모리 최적화 테이블에 대한 해시 또는 비클러스터링

파일 그룹의 인덱스 순위 또는 파티션 구성표

인덱스 디자인 전략을 개발할 때 데이터베이스와 연결된 파일 그룹의 인덱스 배치를 고려해야 합니다.

기본적으로 인덱스는 인덱스가 만들어지는 기본 테이블(클러스터형 인덱스 또는 힙)과 동일한 파일 그룹에 저장됩니다. 다음을 비롯한 기타 구성이 가능합니다.

기본 테이블의 파일 그룹이 아닌 파일 그룹에 비클러스터형 인덱스를 만듭니다.
클러스터형 및 비클러스터형 인덱스를 여러 파일 그룹에 걸쳐 분할합니다.

분할되지 않은 테이블의 경우 가장 간단한 방법은 일반적으로 가장 좋습니다. 동일한 파일 그룹에 모든 테이블을 만들고, 사용 가능한 모든 실제 스토리지를 활용하기 위해 필요한 만큼의 데이터 파일을 파일 그룹에 추가합니다.

계층화된 스토리지를 사용할 수 있는 경우 고급 인덱스 배치 방법을 고려할 수 있습니다. 예를 들어 더 빠른 디스크에 파일이 있는 자주 액세스하는 테이블에 대한 파일 그룹과 느린 디스크의 보관 테이블을 위한 파일 그룹을 만들 수 있습니다.

클러스터형 인덱스가 있는 테이블을 한 파일 그룹에서 다른 파일 그룹으로 이동하려면, 클러스터형 인덱스를 삭제하고 MOVE TO 절의 DROP INDEX 문에서 새 파일 그룹 또는 파티션 구성표를 지정하거나, CREATE INDEX 절이 포함된 DROP_EXISTING 문을 사용할 수 있습니다.

분할된 인덱스

여러 파일 그룹에서 디스크 기반 힙, 클러스터형 및 비클러스터형 인덱스를 분할하는 것도 고려할 수 있습니다. 분할된 인덱스는 파티션 함수에 따라 행별로 수평으로 분할됩니다. 파티션 함수는 지정한 특정 열의 값(분할 열이라고 함)에 따라 각 행이 파티션에 매핑되는 방법을 정의합니다. 파티션 구성표는 파티션 집합을 파일 그룹에 매핑하도록 지정합니다.

인덱스 분할은 다음과 같은 이점을 제공할 수 있습니다.

대규모 데이터베이스를 보다 관리하기 쉽게 만듭니다. 예를 들어 OLAP 시스템은 대량으로 데이터 추가 및 제거를 크게 간소화하는 파티션 인식 ETL을 구현할 수 있습니다.
장기 실행 분석 쿼리와 같은 특정 유형의 쿼리를 더 빠르게 실행합니다. 쿼리가 분할된 인덱스를 사용하는 경우 데이터베이스 엔진은 동시에 여러 파티션을 처리하고 쿼리에 필요하지 않은 파티션을 건너뛰고 제거할 수 있습니다.

경고

분할하면 OLTP 시스템에서 쿼리 성능이 거의 향상되는 경우가 거의 없지만 트랜잭션 쿼리가 많은 파티션에 액세스해야 하는 경우 상당한 오버헤드가 발생할 수 있습니다.

자세한 내용은 분할된 테이블 및 인덱스를 참조하세요.

인덱스 정렬 순서 디자인 지침

인덱스를 정의할 때 각 인덱스 키 열을 오름차순 또는 내림차순으로 저장할지 여부를 고려합니다. 오름차순이 기본값입니다. CREATE INDEX, CREATE TABLE, ALTER TABLE 문의 구문은 인덱스 및 제약 조건의 개별 열에서 ASC(오름차순) 및 DESC(내림차순) 키워드를 지원합니다.

인덱스의 키 값 저장 순서를 지정하는 기능은 테이블을 참조하는 쿼리에 해당 인덱스에서 키 열의 다른 방향을 지정하는 ORDER BY 절이 포함된 경우에 유용합니다. 이러한 경우 인덱스는 쿼리 계획에서 Sort연산 자의 필요성을 제거할 수 있습니다.

예를 들어 Adventure Works Cycles 구매 부서의 구매자는 공급업체로부터 구매하는 제품의 품질을 평가해야 합니다. 구매자는 거부율이 높은 공급업체에서 보낸 제품을 찾는 데 가장 관심이 있습니다.

AdventureWorks 샘플 데이터베이스에 대한 다음 쿼리와 같이 이 조건에 맞는 데이터를 검색하려면 RejectedQty 테이블의 Purchasing.PurchaseOrderDetail 열을 내림차순(큰 데이터부터 작은 데이터 순서)으로 정렬하고 ProductID 열은 오름차순(작은 데이터부터 큰 데이터 순서)으로 정렬해야 합니다.

SELECT RejectedQty,
       ((RejectedQty / OrderQty) * 100) AS RejectionRate,
       ProductID,
       DueDate
FROM Purchasing.PurchaseOrderDetail
ORDER BY RejectedQty DESC, ProductID ASC;

이 쿼리에 대한 다음 실행 계획은 쿼리 최적화 프로그램에서 Sort 연산자를 사용하여 절에 지정된 순서로 결과 집합을 반환했음을 ORDER BY 보여 줍니다.

쿼리 최적화 프로그램이 SORT 연산자를 사용하여 ORDER BY 절에 지정된 순서대로 결과 집합을 반환했음을 보여주는 이 쿼리에 대한 실행 계획 다이어그램입니다.

쿼리의 절에 있는 열과 일치하는 ORDER BY 키 열을 사용하여 디스크 기반 rowstore 인덱스를 만들면 쿼리 계획의 Sort 연산자가 제거되어 쿼리 계획의 효율성이 높아집니다.

CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty
ON Purchasing.PurchaseOrderDetail
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);

쿼리가 다시 실행된 후 다음 실행 계획은 Sort 연산자가 더 이상 존재하지 않으며 새로 만든 비클러스터형 인덱스가 사용됨을 보여 줍니다.

SORT 연산자가 제거되고 새로 생성된 비클러스터 인덱스가 사용되는 것을 보여주는 실행 계획 다이어그램입니다.

데이터베이스 엔진은 어느 방향으로든 인덱스 검색할 수 있습니다. 정의된 인덱스 RejectedQty DESC, ProductID ASC는 ORDER BY 절에서 열의 정렬 방향이 반대로 바뀌는 경우에도 쿼리에서 사용할 수 있습니다. 예를 들어 절 ORDER BY 이 있는 쿼리는 ORDER BY RejectedQty ASC, ProductID DESC 동일한 인덱스로 사용할 수 있습니다.

정렬 순서는 인덱스의 키 열에 대해서만 지정할 수 있습니다. sys.index_columns 카탈로그 뷰는 인덱스 열이 오름차순 또는 내림차순으로 저장되는지 여부를 보고합니다.

클러스터형 인덱스 디자인 지침

클러스터형 인덱스는 테이블의 모든 행과 모든 열을 저장합니다. 행은 인덱스 키 값 순서로 정렬됩니다. 테이블당 하나의 클러스터형 인덱스만 있을 수 있습니다.

용어 기본 테이블은 클러스터형 인덱스 또는 힙을 참조할 수 있습니다. 힙은 테이블의 모든 행과 모든 열을 포함하는 디스크의 정렬되지 않은 데이터 구조입니다.

몇 가지 예외를 제외하고 모든 테이블에는 클러스터형 인덱스가 있어야 합니다. 클러스터형 인덱스의 바람직한 속성은 다음과 같습니다.

재산	Description
좁다	클러스터형 인덱스 키는 동일한 기본 테이블에 있는 비클러스터형 인덱스의 일부입니다. 좁은 키 또는 키 열의 총 길이가 작은 키는 테이블에 있는 모든 인덱스의 스토리지, I/O 및 메모리 오버헤드를 줄입니다. 키 길이를 계산하려면 키 열에서 사용하는 데이터 형식에 대한 스토리지 크기를 추가합니다. 자세한 내용은 데이터 형식 범주를 참조하세요.
독특한	클러스터형 인덱스가 고유하지 않은 경우 고유성을 보장하기 위해 4 바이트 내부 uniqueifier 열이 인덱스 키에 자동으로 추가됩니다. 클러스터형 인덱스 키에 기존 고유 열을 추가하면 테이블의 모든 인덱스에 있는 uniqueifier 열의 스토리지, I/O 및 메모리 오버헤드가 방지됩니다. 또한 쿼리 최적화 프로그램은 인덱스가 고유할 때 보다 효율적인 쿼리 계획을 생성할 수 있습니다.
계속 증가	계속 증가하는 인덱스의 데이터는 항상 인덱스의 마지막 페이지에 추가됩니다. 이렇게 하면 인덱스 중간에 페이지 분할이 방지되어 페이지 밀도가 감소하고 성능이 저하됩니다.
불변의	클러스터형 인덱스 키는 비클러스터형 인덱스의 일부입니다. 클러스터형 인덱스의 키 열을 수정하는 경우 CPU, 로깅, I/O 및 메모리 오버헤드를 추가하는 모든 비클러스터형 인덱스도 변경해야 합니다. 클러스터형 인덱스의 키 열을 변경할 수 없는 경우 오버헤드가 방지됩니다.
nullable 열만 포함하지 않음	행에 null 허용 열이 있는 경우 인덱스에 행당 3~4바이트의 스토리지를 추가하는 NULL 블록이라는 내부 구조를 포함해야 합니다. 클러스터형 인덱스의 모든 열을 null을 허용하지 않도록 하면 이러한 오버헤드를 방지할 수 있습니다.
고정 너비 열만 사용할 수 있습니다.	varchar 또는 nvarchar와 같은 가변 너비 데이터 형식을 사용하는 열은 고정 너비 데이터 형식에 비해 값당 2바이트를 추가로 사용합니다. int와 같은 고정 너비 데이터 형식을 사용하면 테이블의 모든 인덱스에 이 오버헤드가 발생하지 않습니다.

클러스터형 인덱스를 디자인할 때 이러한 속성을 최대한 많이 충족하면 클러스터형 인덱스뿐만 아니라 동일한 테이블의 모든 비클러스터형 인덱스도 더 효율적입니다. 스토리지, I/O 및 메모리 오버헤드를 방지하여 성능이 향상됩니다.

예를 들어 단일 int 또는 bigint가 nullable 열이 아닌 클러스터형 인덱스 키는 IDENTITY 사용하여 절 또는 기본 제약 조건으로 채워 지고 행이 삽입된 후에 업데이트되지 않는 경우 이러한 모든 속성을 갖습니다.

반대로 단일 uniqueidentifier 열이 있는 클러스터형 인덱스 키는 int 의 경우 4바이트 대신 16바이트의 스토리지를 사용하고 bigint의 경우 8바이트를 사용하므로 더 넓으며 값이 순차적으로 생성되지 않는 한 계속 증가하는 속성을 충족하지 않습니다.

Tip

제약 조건을 PRIMARY KEY 만들면 제약 조건을 지원하는 고유 인덱스가 자동으로 만들어집니다. 기본적으로 이 인덱스는 클러스터형입니다. 그러나 이 인덱스가 클러스터형 인덱스의 원하는 속성을 충족하지 않는 경우 제약 조건을 비클러스터형으로 만들고 다른 클러스터형 인덱스 대신 만들 수 있습니다.

클러스터형 인덱스는 만들지 않으면 테이블이 힙으로 저장되며 일반적으로 권장되지 않습니다.

클러스터형 인덱스 아키텍처

Rowstore 인덱스는 B+ 트리로 구성됩니다. 인덱스 B+ 트리의 각 페이지를 인덱스 노드라고 합니다. B+ 트리 맨 위 노드를 루트 노드라고 합니다. 인덱스의 맨 아래에 있는 노드를 리프 노드라고 합니다. 루트 노드와 리프 노드 사이의 모든 인덱스 수준을 총칭하여 중간 수준이라고 합니다. 클러스터형 인덱스에서 리프 노드에는 기본 테이블의 데이터 페이지가 포함되어 있습니다. 루트 노드와 중간 수준 노드에는 인덱스 행을 포함하는 인덱스 페이지가 있습니다. 각 인덱스 행에는 키 값과 함께 B+ 트리의 중간 수준 페이지에 대한 포인터나 인덱스 리프 수준의 데이터 행에 대한 포인터가 있습니다. 인덱스의 각 수준에 있는 페이지는 이중으로 연결된 목록에 연결됩니다.

클러스터형 인덱스에는 인덱스에 사용되는 각 파티션에 대해 sys.partitions 에 한 행이 있습니다 index_id = 1. 기본적으로 클러스터형 인덱스에는 단일 파티션이 있습니다. 클러스터형 인덱스에 여러 파티션이 있는 경우 각 파티션에는 해당 특정 파티션에 대한 데이터를 포함하는 별도의 B+ 트리 구조가 있습니다. 예를 들어 클러스터형 인덱스에 4개의 파티션이 있는 경우 각 파티션에 하나씩 4개의 B+ 트리 구조가 있습니다.

클러스터형 인덱스의 데이터 형식에 따라 각 클러스터형 인덱스 구조에는 특정 파티션에 대한 데이터를 저장하고 관리하는 할당 단위가 하나 이상 있습니다. 최소한 각 클러스터형 인덱스에는 파티션당 하나의 IN_ROW_DATA 할당 단위가 있습니다. 클러스터형 인덱스에는 LOB_DATA와 같은 LOB(큰 개체) 열이 포함된 경우 파티션당 하나의 할당 단위도 있습니다. 또한 8,060 바이트 행 크기 제한을 초과하는 가변 길이 열이 포함된 경우 파티션당 하나의 ROW_OVERFLOW_DATA 할당 단위가 있습니다.

B+ 트리 구조의 페이지는 클러스터형 인덱스 키의 값에 따라 정렬됩니다. 삽입된 행의 키 값이 기존 페이지 간의 순서 순서에 맞는 페이지에서 모든 삽입이 이루어집니다. 페이지 내에서 행이 반드시 물리적 순서로 저장되는 것은 아닙니다. 그러나 이 페이지는 슬롯 배열이라는 내부 구조를 사용하여 행의 논리적 순서를 유지합니다. 슬롯 배열의 항목은 인덱스 키 순서로 유지됩니다.

이 일러스트레이션에서는 단일 파티션에 있는 클러스터형 인덱스의 구조를 보여 줍니다.

단일 파티션의 클러스터형 인덱스 구조를 보여 주는 다이어그램입니다.

비클러스터형 인덱스 디자인 지침

클러스터형 인덱스와 비클러스터형 인덱스의 주요 차이점은 비클러스터형 인덱스에는 일반적으로 클러스터형 인덱스와 다르게 정렬된 테이블의 열 하위 집합이 포함되어 있다는 것입니다. 필요에 따라 비클러스터형 인덱스를 필터링할 수 있습니다. 즉, 테이블에 있는 모든 행의 하위 집합이 포함됩니다.

디스크 기반 rowstore 비클러스터형 인덱스에는 기본 테이블에 있는 행의 스토리지 위치를 가리키는 행 로케이터가 포함됩니다. 테이블 또는 인덱싱된 뷰에 비클러스터형 인덱스를 여러 개 만들 수 있습니다. 일반적으로 비클러스터형 인덱스는 기본 테이블을 검색해야 하는 자주 사용되는 쿼리의 성능을 향상하도록 설계되어야 합니다.

책에서 인덱스를 사용하는 방식과 유사하게, 쿼리 최적화 프로그램은 비클러스터형 인덱스를 검색하여 테이블에서 데이터 값의 위치를 찾은 다음 해당 위치에서 직접 데이터를 가져와 데이터 값을 검색합니다. 인덱스에는 쿼리에서 검색되는 데이터 값 테이블의 정확한 위치를 설명하는 항목이 포함되어 있기 때문에 비클러스터형 인덱스는 정확히 일치하는 쿼리에 대한 최적의 선택이 됩니다.

예를 들어 특정 관리자에게 보고하는 모든 직원의 테이블을 쿼리 HumanResources.Employee 하기 위해 쿼리 최적화 프로그램은 비클러스터형 인덱 IX_Employee_ManagerID스를 사용할 수 있습니다. 이 열은 ManagerID 첫 번째 키 열입니다. 값은 ManagerID 비클러스터형 인덱스로 정렬되므로 쿼리 최적화 프로그램은 지정된 ManagerID 값과 일치하는 인덱스 내의 모든 항목을 빠르게 찾을 수 있습니다. 각 인덱스 진입점은 다른 모든 열의 해당 데이터를 검색할 수 있는 기본 테이블의 정확한 페이지와 행을 가리킵니다. 쿼리 최적화 프로그램에서 인덱스의 모든 항목을 찾은 후에는 전체 기본 테이블을 검색하는 대신 정확한 페이지 및 행으로 직접 이동하여 데이터를 검색할 수 있습니다.

비클러스터형 인덱스 아키텍처

디스크 기반 rowstore 비클러스터형 인덱스는 다음과 같은 차이점을 제외하고 클러스터형 인덱스와 동일한 B+ 트리 구조를 갖습니다.

비클러스터형 인덱스에 테이블의 모든 열과 행이 반드시 포함된 것은 아닙니다.
비클러스터형 인덱스의 리프 수준은 데이터 페이지 대신 인덱스 페이지로 구성됩니다. 비클러스터형 인덱스의 리프 수준에 있는 인덱스 페이지에는 키 열이 포함되어 있습니다. 필요에 따라 기본 테이블에서 검색하지 않도록 테이블에 있는 다른 열의 하위 집합을 포함된 열로 포함할 수도 있습니다.

비클러스터형 인덱스 행의 행 로케이터는 행에 대한 포인터이거나 다음과 같이 설명된 행의 클러스터형 인덱스 키입니다.

테이블에 클러스터형 인덱스가 있거나 인덱스가 인덱싱된 뷰에 있는 경우 행 로케이터는 행의 클러스터형 인덱스 키입니다.
테이블이 힙이면 클러스터형 인덱스가 없으므로 행 로케이터는 행에 대한 포인터입니다. 포인터는 페이지의 파일 식별자(ID), 페이지 번호 및 행 번호에서 만들어집니다. 전체 포인터를 RID(행 ID)라고 합니다.

또한 행 로케이터는 비클러스터형 인덱스 행의 고유성을 보장합니다. 다음 표에서는 데이터베이스 엔진이 비클러스터형 인덱스에 행 로케이터를 추가하는 방법을 설명합니다.

기본 테이블 형식	비클러스터형 인덱스 형식	행 로케이터
Heap
	Nonunique	키 열에 추가된 RID
	Unique	포함된 열에 추가된 RID
고유 클러스터형 인덱스
	Nonunique	키 열에 추가된 클러스터형 인덱스 키
	Unique	포함된 열에 추가된 클러스터형 인덱스 키
고유하지 않은 클러스터형 인덱스
	Nonunique	키 열에 추가된 클러스터형 인덱스 키 및 uniqueifier(있는 경우)
	Unique	포함된 열에 추가된 클러스터형 인덱스 키 및 uniqueifier(있는 경우)

데이터베이스 엔진은 지정된 열을 비클러스터형 인덱스로 두 번 이상 저장하지 않습니다. 비클러스터형 인덱스를 만들 때 사용자가 지정한 인덱스 키 순서는 항상 적용됩니다. 비클러스터형 인덱스의 키에 추가해야 하는 모든 행 로케이터 열은 인덱스 정의에 지정된 열에 따라 키의 끝에 추가됩니다. 비클러스터형 인덱스의 클러스터형 인덱스 키 행 로케이터는 인덱스 정의에 명시적으로 지정되거나 암시적으로 추가되었는지 여부에 관계없이 쿼리 처리에 사용할 수 있습니다.

다음 예제에서는 비클러스터형 인덱스에서 행 로케이터가 구현되는 방식을 보여 줍니다.

클러스터형 인덱스	비클러스터형 인덱스 정의	행 로케이터를 사용하는 비클러스터형 인덱스 정의	Explanation
키 열이 있는 고유 클러스터형 인덱스(`A`, `B`, `C`)	키 열(`B`, `A`) 및 포함된 열(`E`, `G`)이 있는 고유하지 않은 비클러스터형 인덱스	키 열(`B`, `A`, `C`) 및 포함된 열(`E`, `G`)	비클러스터형 인덱스는 고유하지 않으므로 행 로케이터가 인덱스 키에 있어야 합니다. 행 로케이터의 `B`와 `A` 열이 이미 있으므로 `C` 열만 추가됩니다. `C` 열이 키 열 목록 끝에 추가됩니다.
키 열이 있는 고유 클러스터형 인덱스(`A`)	키 열(`B`, `C`) 및 포함된 열(`A`)이 있는 고유하지 않은 비클러스터형 인덱스	키 열(`B`, `C`, `A`)	비클러스터형 인덱스는 고유하지 않으므로 행 로케이터가 키에 추가됩니다. `A` 열은 아직 키 열로 지정되지 않았으므로 키 열 목록 끝에 추가됩니다. 이제 `A` 열이 키에 있으므로 포함된 열로 저장할 필요가 없습니다.
키 열이 있는 고유 클러스터형 인덱스(`A`, `B`)	키 열이 있는 고유 비클러스터형 인덱스(`C`)	키 열(`C`) 및 포함된 열(`A`, `B`)	비클러스터형 인덱스는 고유하므로 행 로케이터가 포함된 열에 추가됩니다.

비클러스터형 인덱스에는 인덱스가 사용하는 각 파티션에 대해 sys.partitions 에 한 행이 있습니다 index_id > 1. 기본적으로 비클러스터형 인덱스에는 단일 파티션이 있습니다. 비클러스터형 인덱스가 다중 파티션을 사용하는 경우 각 파티션은 해당 특정 파티션에 대한 인덱스 행을 포함하는 B+ 트리 구조를 갖습니다. 예를 들어 비클러스터형 인덱스에 4개의 파티션이 있는 경우 각 파티션에 하나씩 4개의 B+ 트리 구조가 있습니다.

비클러스터형 인덱스의 데이터 형식에 따라 각 비클러스터형 인덱스 구조에는 특정 파티션에 대한 데이터를 저장하고 관리하는 할당 단위가 하나 이상 있습니다. 최소한 각 비클러스터형 인덱스는 인덱스 B+ 트리 페이지를 저장하는 파티션당 하나의 IN_ROW_DATA 할당 단위를 포함합니다. 또한 비클러스터형 인덱스에는 LOB_DATA와 같은 LOB(큰 개체) 열이 포함된 경우 파티션당 하나의 할당 단위가 있습니다. 또한 8,060 바이트 행 크기 제한을 초과하는 가변 길이 열이 포함된 경우 파티션당 하나의 ROW_OVERFLOW_DATA 할당 단위가 있습니다.

다음 일러스트레이션에서는 단일 파티션에 있는 비클러스터형 인덱스의 구조를 보여 줍니다.

단일 파티션의 비클러스터형 인덱스 구조를 보여 주는 다이어그램입니다.

비클러스터형 인덱스에 포함된 열 사용

키 열 외에도 비클러스터형 인덱스에 리프 수준에 키가 아닌 열이 저장될 수도 있습니다. 이러한 키가 아닌 열은 포함된 열이라고 하며 문의 절 INCLUDE 에 CREATE INDEX 지정됩니다.

키가 아닌 열이 포함된 인덱스는 쿼리를 처리할 때 쿼리 성능을 크게 향상시킬 수 있습니다. 즉, 쿼리에 사용된 모든 열이 키 또는 키가 아닌 열로 인덱스에 있는 경우입니다. 데이터베이스 엔진이 인덱스 내의 모든 열 값을 찾을 수 있으므로 성능이 향상됩니다. 기본 테이블에 액세스하지 않으므로 디스크 I/O 작업이 줄어듭니다.

쿼리에서 열을 검색해야 하지만 쿼리 조건자, 집계 및 정렬에 사용되지 않는 경우 키 열이 아닌 포함된 열로 추가합니다. 다음과 같은 장점이 있습니다.

포함된 열은 인덱스 키 열로 허용되지 않는 데이터 형식을 사용할 수 있습니다.
포함된 열은 인덱스 키 열 또는 인덱스 키 크기의 수를 계산할 때 데이터베이스 엔진에서 고려하지 않습니다. 열(column)을 포함하면 900 바이트의 최대 키 크기로 제한되지 않습니다. 더 많은 쿼리를 포함하는 더 넓은 인덱스를 만들 수 있습니다.
인덱스 키에서 포함된 열로 열을 이동하면 인덱스 정렬 작업이 더 빨라지므로 인덱스 작성 시간이 줄어듭니다.

테이블에 클러스터형 인덱스가 있는 경우 클러스터형 인덱스 키에 정의된 열 또는 열이 테이블의 각 비클러스터형 비클러스터형 인덱스에 자동으로 추가됩니다. 비클러스터형 인덱스 키 또는 포함된 열로 지정할 필요는 없습니다.

포함된 열이 있는 인덱스에 대한 지침

포함된 열을 사용하여 비클러스터형 인덱스를 디자인할 때 다음 지침을 고려합니다.

포함된 열은 테이블 또는 인덱싱된 뷰의 비클러스터형 인덱스에만 정의할 수 있습니다.
text, ntext및 image를 제외한 모든 데이터 형식을 사용할 수 있습니다.
결정적이면서 정확하거나 정확하지 않은 계산 열은 포괄 열이 될 수 있습니다. 자세한 내용은 계산된 열의 인덱스를 참조하세요.
키 열과 마찬가지로 이미지,ntext 및 텍스트 데이터 형식에서 파생된 계산 열은 계산 열 데이터 형식이 포함된 열에 허용되는 한 열을 포함할 수 있습니다.
INCLUDE 목록과 키 열 목록에서는 모두 열 이름을 지정할 수 없습니다.
열 이름은 INCLUDE 목록에서 반복될 수 없습니다.
인덱스에서 하나 이상의 키 열을 정의해야 합니다. 포함된 열의 최대 수는 1,023개입니다. 테이블 열의 최대 개수에서 1을 뺀 값입니다.
포함된 열이 있는지와 관계없이 인덱스 키 열은 최대 16개 키 열의 기존 인덱스 크기 제한과 총 인덱스 키 크기 900바이트를 따라야 합니다.

포함된 열이 있는 인덱스에 대한 디자인 권장 사항

쿼리 조건자, 집계 및 정렬에 사용되는 열만 키 열이 되도록 인덱스 키 크기가 큰 비클러스터형 인덱스를 다시 디자인하는 것이 좋습니다. 쿼리를 포함하는 다른 모든 열에 키가 아닌 열이 포함되도록 만듭니다. 이러한 방식으로 쿼리를 다루는 데 필요한 모든 열이 있지만 인덱스 키 자체는 작고 효율적입니다.

예를 들어, 다음 쿼리를 처리하기 위해 인덱스를 디자인한다고 가정해 보겠습니다.

SELECT AddressLine1,
       AddressLine2,
       City,
       StateProvinceID,
       PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' AND N'99999';

쿼리를 다루려면 인덱스에서 각 열을 정의해야 합니다. 모든 열을 키 열로 정의할 수 있지만 키 크기는 334바이트가 될 것입니다. 검색 조건으로 사용된 유일한 열은 길이가 30바이트인 PostalCode 열이므로 더 나은 인덱스 디자인은 PostalCode를 키 열로 정의하고 다른 모든 열을 키가 아닌 열로 포함시킵니다.

다음 문은 포괄 열이 있는 인덱스를 만들어 쿼리를 포함합니다.

CREATE INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

인덱스가 쿼리를 포함하는지 확인하려면 인덱스 생성 후 예상 실행 계획을 표시합니다. 실행 계획에 인덱스 검색 연산 IX_Address_PostalCode 자가 표시되면 쿼리는 인덱스로 처리됩니다.

포함된 열이 있는 인덱스에 대한 성능 고려 사항

포함된 열 수가 매우 많은 인덱스를 만들지 않습니다. 인덱스가 더 많은 쿼리를 처리할 수 있지만 다음과 같은 이유로 성능 이점이 감소합니다.

페이지에 맞는 인덱스 행 수가 줄어듭니다. 이렇게 하면 디스크 I/O가 증가하고 캐시 효율성이 줄어듭니다.
인덱스를 저장하는 데 더 많은 디스크 공간이 필요합니다. 특히 포함된 열에 varchar(max), nvarchar(max), varbinary(max) 또는 xml 데이터 형식을 추가하면 디스크 공간 요구 사항이 크게 증가할 수 있습니다. 열 값이 인덱스 리프 수준으로 복사되기 때문입니다. 따라서 열 값이 인덱스와 기본 테이블 모두에 상주합니다.
기반 테이블과 비클러스터형 인덱스 모두에서 많은 열을 수정해야 하므로 데이터 수정 성능이 저하됩니다.

쿼리 성능 향상이 데이터 수정 성능의 감소 및 디스크 공간 요구 사항 증가보다 더 중요한지 확인해야 합니다.

고유 인덱스 디자인 지침

고유 인덱스는 인덱스 키에 중복 값이 없음을 보장합니다. 고유 인덱스 만들기는 고유성이 데이터 자체의 특성인 경우에만 가능합니다. 예를 들어 기본 키가 NationalIDNumber인 경우 HumanResources.Employee 테이블의 EmployeeID 열 값이 고유하도록 하려면 UNIQUE 열에 대해 NationalIDNumber 제약 조건을 만듭니다. 이 제약 조건은 중복된 국가 ID 번호가 있는 행을 도입하려는 시도를 거부합니다.

여러 열로 구성된 고유 인덱스를 사용하면 인덱스는 인덱스 키의 각 값 조합이 고유하도록 합니다. 예를 들어 , LastName및 FirstName 열의 조합에 고유한 인덱스가 만들어지면 테이블의 MiddleName두 행이 이러한 열에 대해 동일한 값을 가질 수 없습니다.

클러스터형 인덱스와 비클러스터형 인덱스는 모두 고유할 수 있습니다. 동일한 테이블에 고유한 클러스터형 인덱스와 여러 개의 고유한 비클러스터형 인덱스를 만들 수 있습니다.

고유 인덱스의 이점은 다음과 같습니다.

데이터 고유성이 필요한 비즈니스 규칙이 적용됩니다.
쿼리 최적화 프로그램에 유용한 추가 정보가 제공됩니다.

PRIMARY KEY 또는 UNIQUE 제약 조건을 만들면 지정된 열에 고유 인덱스가 자동으로 생성됩니다. UNIQUE 제약 조건을 만드는 것과 제약 조건과 무관한 고유 인덱스를 만드는 것 사이에는 큰 차이가 없습니다. 데이터 유효성 검사는 동일한 방식으로 수행되며 쿼리 최적화 프로그램은 제약 조건에서 만든 고유 인덱스나 수동으로 만든 고유 인덱스를 구분하지 않습니다. 그러나 비즈니스 규칙의 적용이 목표인 경우 열에 대한 제약 조건을 만들어야 UNIQUEPRIMARY KEY 합니다. 이 작업을 수행하면 인덱스의 용도가 명확해집니다.

고유 인덱스 고려 사항

데이터에 중복 키 값이 있는 경우 고유 인덱스, UNIQUE 제약 조건 또는 PRIMARY KEY 제약 조건을 만들 수 없습니다.
데이터가 고유하고 고유성을 적용하려는 경우 동일한 열 조합에 고유하지 않은 인덱스 대신 고유 인덱스를 생성하면 보다 효율적인 실행 계획을 생성할 수 있는 쿼리 최적화 프로그램에 대한 추가 정보가 제공됩니다. UNIQUE 이 경우 제약 조건 또는 고유 인덱스 만들기를 권장합니다.
고유한 비클러스터형 인덱스에는 키가 아닌 열이 포함될 수 있습니다. 자세한 내용은 비클러스터형 인덱스에 포함된 열 사용을 참조하세요.
PRIMARY KEY 제약 조건과 달리, 인덱스 키에 nullable 열을 포함하여 UNIQUE 제약 조건이나 고유 인덱스를 만들 수 있습니다. 고유성 적용을 위해 두 개의 NULL이 동일한 것으로 간주됩니다. 예를 들어 단일 열 고유 인덱스에서 열은 테이블의 한 행에 대해서만 NULL일 수 있습니다.

필터링된 인덱스 디자인 지침

필터링된 인덱스는 최적화된 비클러스터형 인덱스이며, 특히 테이블에 작은 데이터 하위 집합이 필요한 쿼리에 적합합니다. 인덱스 정의의 필터 조건자를 사용하여 테이블의 행 일부를 인덱싱합니다. 잘 디자인된 필터링된 인덱스는 전체 테이블 인덱스와 비교하여 쿼리 성능을 향상시키고 인덱스 업데이트 비용을 줄이며 인덱스 스토리지 비용을 줄일 수 있습니다.

필터링된 인덱스는 전체 테이블 인덱스에 비해 다음과 같은 이점이 있습니다.

향상된 쿼리 성능 및 플랜 품질

잘 디자인된 필터링된 인덱스는 전체 테이블 비클러스터형 인덱스보다 작기 때문에 쿼리 성능 및 실행 계획 품질을 향상시킵니다. 필터링된 인덱스에는 필터링된 인덱스의 행만 포함하므로 전체 테이블 통계보다 더 정확한 필터링된 통계가 있습니다.
인덱스 업데이트 비용 절감

인덱스는 DML(데이터 조작 언어) 문이 인덱스의 데이터에 영향을 주는 경우에만 업데이트됩니다. 필터링된 인덱스는 더 작고 인덱스의 데이터가 영향을 받는 경우에만 업데이트되기 때문에 전체 테이블 비클러스터형 인덱스에 비해 인덱스 업데이트 비용을 줄입니다. 특히 자주 영향을 받지 않는 데이터가 포함된 경우 필터링된 인덱스가 많을 수 있습니다. 마찬가지로 필터링된 인덱스에 자주 영향을 받는 데이터만 포함된 경우 인덱스의 크기가 작을수록 통계 업데이트 비용이 줄어듭니다.
줄어든 인덱스 스토리지 비용

필터링된 인덱스를 만들면 전체 테이블 인덱스가 필요하지 않은 경우 비클러스터형 인덱스에 대한 디스크 스토리지를 줄일 수 있습니다. 스토리지 요구 사항을 크게 늘리지 않고 전체 테이블 비클러스터형 인덱스를 여러 필터링된 인덱스로 바꿀 수 있습니다.

필터링된 인덱스는 열에 잘 정의된 데이터 하위 집합이 포함된 경우에 유용합니다. 예제는 다음과 같습니다.

NULL 값이 많은 열들입니다.
데이터 범주를 포함하는 다른 유형의 열입니다.
양, 시간 및 날짜와 같은 값 범위가 포함된 열입니다.

필터링된 인덱스에 대한 업데이트 비용 절감은 전체 테이블 인덱스에 비해 인덱스의 행 수가 작을 때 가장 두드러집니다. 필터링된 인덱스에 테이블의 열이 대부분 포함되어 있을 경우 전체 테이블 인덱스보다 유지 관리 비용이 더 들 수 있습니다. 이 경우에는 필터링된 인덱스 대신 전체 테이블 인덱스를 사용해야 합니다.

필터링된 인덱스는 한 테이블에 정의되며 간단한 비교 연산자만 지원합니다. 복잡한 논리가 있거나 여러 테이블을 참조하는 필터 식이 필요한 경우 인덱싱된 계산 열 또는 인덱싱된 뷰를 만들어야 합니다.

필터링된 인덱스 디자인 고려 사항

효과적인 필터링된 인덱스를 디자인하려면 애플리케이션이 사용하는 쿼리와 이 쿼리가 데이터의 하위 집합과 어떻게 연결되는지를 이해하는 것이 중요합니다. 잘 정의된 하위 집합이 있는 데이터의 몇 가지 예는 NULL이 많은 열, 다른 유형의 범주의 값이 있는 열 및 고유한 값 범위의 열입니다.

다음 디자인 고려 사항은 필터링된 인덱스가 전체 테이블 인덱스에 비해 이점을 제공할 수 있는 경우에 대한 몇 가지 시나리오를 제공합니다.

데이터의 하위 집합에 대한 필터링된 인덱스

열에 쿼리와 관련된 값이 몇 개만 있는 경우 값의 하위 집합에 필터링된 인덱스를 만들 수 있습니다. 예를 들어 열이 대부분 NULL이고 쿼리에 NULL이 아닌 값만 필요한 경우 NULL이 아닌 행을 포함하는 필터링된 인덱스를 만들 수 있습니다.

예를 들어 AdventureWorks 샘플 데이터베이스에는 2,679개의 행이 있는 Production.BillOfMaterials 테이블이 있습니다. 열에는 EndDate NULL이 아닌 값이 포함된 199개의 행만 있고 나머지 2480개 행에는 NULL이 포함됩니다. 다음 필터링된 인덱스는 인덱스에 정의된 열을 반환하며, \에 대해 NULL 값이 아닌 행만을 요구하는 쿼리를 처리합니다.

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;

필터링된 인덱스 FIBillOfMaterialsWithEndDate는 다음 쿼리에 유효합니다. 쿼리 최적화 프로그램에서 이 필터링된 인덱스가 사용되는지 확인하기 위해 쿼리 실행 계획을 표시합니다.

SELECT ProductAssemblyID,
       ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
      AND ComponentID = 5
      AND StartDate > '20080101';

필터링된 인덱스를 생성하는 방법과 필터링된 인덱스 조건자 식을 정의하는 방법에 대한 자세한 내용은 필터링된 인덱스 생성을 참조하세요.

서로 다른 데이터에 필터링된 인덱스

테이블에 다른 유형의 데이터 행이 있는 경우 하나 이상의 데이터 범주에 대해 필터링된 인덱스를 생성할 수 있습니다.

예를 들어 Production.Product 테이블에 나열된 제품은 각각 ProductSubcategoryID에 지정된 다음 제품 범주 Bikes, Components, Clothing 또는 Accessories 같은 제품 범주와 연결됩니다. 이러한 범주는 Production.Product 테이블의 해당 열 값이 밀접하게 연관되어 있지 않기 때문에 이질적입니다. 예를 들어 열 Color, ReorderPoint, ListPrice, Weight, Class 및 Style에는 각 제품 범주에 대한 고유한 특성이 있습니다. 27부터 36까지의 하위 범주가 있는 액세서리에 대한 쿼리가 자주 발생한다고 가정해 보겠습니다. 다음 예와 같이 Accessories 하위 범주에 필터링된 인덱스를 만들어 Accessories에 대한 쿼리의 성능을 향상시킬 수 있습니다.

CREATE NONCLUSTERED INDEX FIProductAccessories
ON Production.Product (ProductSubcategoryID, ListPrice)
INCLUDE (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;

필터링된 인덱스 FIProductAccessories는 쿼리 결과가 인덱스에 포함되어 있고 쿼리 계획상 기본 테이블에 액세스할 필요가 없기 때문에 다음 쿼리를 다룹니다. 예를 들어 쿼리 조건자 식 ProductSubcategoryID = 33은(는) 필터링된 인덱스 조건자 ProductSubcategoryID >= 27 및 ProductSubcategoryID <= 36의 하위 집합이고 쿼리 조건자의 ProductSubcategoryID 및 ListPrice 열은 모두 인덱스의 키 열이며 이름은 인덱스의 리프 수준에 포함된 열로 저장됩니다.

SELECT Name,
       ProductSubcategoryID,
       ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33
      AND ListPrice > 25.00;

필터링된 인덱스의 키 및 포함된 열

쿼리 최적화 프로그램에서 쿼리 실행 계획에 대해 필터링된 인덱스를 선택하는 데 필요한 경우에만 필터링된 인덱스 정의에 적은 수의 열을 추가하는 것이 가장 좋습니다. 쿼리 최적화 프로그램에서는 필터링된 인덱스의 쿼리 처리 여부에 상관없이 쿼리에 대한 필터링된 인덱스를 선택할 수 있습니다. 그러나 쿼리 최적화 프로그램은 쿼리를 포함하는 경우 필터링된 인덱스를 선택할 가능성이 높습니다.

경우에 따라 필터링된 인덱스는 필터링된 인덱스 식의 열을 필터링된 인덱스 정의의 키 또는 포함 열로 포함하지 않고 쿼리를 처리합니다. 다음 지침에서는 필터링된 인덱스 식의 열이 필터링된 인덱스 정의의 키 또는 포괄 열이어야 하는 경우를 설명합니다. 이 예에서는 앞에서 만든 필터링된 인덱스 FIBillOfMaterialsWithEndDate 를 참조합니다.

필터링된 인덱스 식이 쿼리 조건과 동일하고 쿼리가 쿼리 결과와 함께 필터링된 인덱스 식의 열을 반환하지 않는 경우 필터링된 인덱스 식의 열이 키이거나 필터링된 인덱스 정의에 포함된 열일 필요는 없습니다. 예를 들어 쿼리 조건자는 필터 식과 동일하며 FIBillOfMaterialsWithEndDate은(는) 쿼리 결과와 함께 반환되지 않으므로 EndDate에서는 다음 쿼리를 다룹니다. FIBillOfMaterialsWithEndDate 인덱스는 필터링된 인덱스 정의에 키 또는 포함된 열로 EndDate 필요하지 않습니다.

SELECT ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;

쿼리 조건자가 필터링된 인덱스 식과 동일하지 않은 비교에서 열을 사용하는 경우 필터링된 인덱스 식의 열은 필터링된 인덱스 정의에 키 또는 포함된 열이어야 합니다. 예를 들어 FIBillOfMaterialsWithEndDate은(는) 필터링된 인덱스에서 행의 하위 집합을 선택하므로 다음 쿼리에 유효합니다. 그러나 EndDate이(가) 필터링된 인덱스 식과 동일하지 않은 비교 EndDate > '20040101'에 사용되므로 다음 쿼리는 처리하지 않습니다. 쿼리 프로세서는 값을 검사하지 않고는 이 쿼리를 EndDate실행할 수 없습니다. 따라서 EndDate은(는) 필터링된 인덱스 정의의 키 또는 포함된 열이어야 합니다.

SELECT ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate > '20040101';

필터링된 인덱스 식의 열은 열이 쿼리 결과 집합에 있는 경우 필터링된 인덱스 정의의 키 또는 포함된 열이어야 합니다. 예를 들어 FIBillOfMaterialsWithEndDate은(는) 쿼리 결과에 EndDate 열을 반환하므로 다음 쿼리를 처리하지 않습니다. 따라서 EndDate은(는) 필터링된 인덱스 정의의 키 또는 포함된 열이어야 합니다.

SELECT ComponentID,
       StartDate,
       EndDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;

테이블의 클러스터형 인덱스 키는 필터링된 인덱스 정의에 포함된 열이나 키가 아니어도 됩니다. 클러스터형 인덱스 키는 필터링된 인덱스를 포함한 모든 비클러스터형 인덱스에 자동으로 포함됩니다.

필터 조건자의 데이터 변환 연산자

필터링된 인덱스의 필터링된 인덱스 식에 지정된 비교 연산자가 암시적 또는 명시적 데이터 변환을 초래하는 경우 비교 연산자의 왼쪽에서 변환이 발생하면 오류가 발생합니다. 이에 대한 해결 방법은 비교 연산자의 오른쪽에 데이터 변환 연산자(CAST 또는 CONVERT)를 사용하여 필터링된 인덱스 식을 작성하는 것입니다.

다음 예제에서는 서로 다른 데이터 형식의 열이 있는 테이블을 만듭니다.

CREATE TABLE dbo.TestTable
(
    a INT,
    b VARBINARY(4)
);

다음 필터링된 인덱스 정의에서 열 b 은 상수 1과 비교하기 위해 암시적으로 정수 데이터 형식으로 변환됩니다. 이로 인해 오류 메시지 10611이 생성되며 그 이유는 필터링된 조건자에 있는 연산자의 왼쪽에서 변환이 발생하기 때문입니다.

CREATE NONCLUSTERED INDEX TestTabIndex
ON dbo.TestTable (a, b)
    WHERE b = 1;

해결 방법은 다음 예에서와 같이 b 열과 동일한 유형이 되도록 오른쪽에 있는 상수를 변환하는 것입니다.

CREATE INDEX TestTabIndex
ON dbo.TestTable (a, b)
    WHERE b = CONVERT (VARBINARY(4), 1);

데이터 변환을 비교 연산자의 왼쪽에서 오른쪽으로 이동하면 변환 방법이 변경될 수 있습니다. 이전 예제에서 연산자가 CONVERT 오른쪽에 추가되었을 때 비교가 int 비교에서 varbinary 비교로 변경되었습니다.

Columnstore 인덱스 아키텍처

columnstore 인덱스는 columnstore라는 열 형식 데이터 형식을 사용하여 데이터를 저장, 검색 및 관리하는 기술입니다. 자세한 내용은 Columnstore 인덱스: 개요를 참조하세요.

버전 정보와 새로운 기능을 알아보려면 columnstore 인덱스의 새로운 기능을 방문하세요.

이러한 기본 사항을 알고 있으면 이 기술을 효과적으로 사용하는 방법을 설명하는 다른 columnstore 문서를 더 쉽게 이해할 수 있습니다.

데이터 스토리지는 columnstore 및 rowstore를 사용합니다.

columnstore 인덱스에 대해 살펴볼 때 데이터 스토리지에 대한 형식을 강조하기 위해 rowstore 및 columnstore 라는 용어가 사용됩니다. columnstore 인덱스는 두 가지 유형의 스토리지를 모두 사용합니다.

클러스터형 columnstore 인덱스의 다이어그램입니다.

columnstore는 행과 열이 있는 테이블로 논리적으로 구성되고 열 데이터 형식으로 물리적으로 저장되는 데이터입니다.

columnstore 인덱스는 대부분의 데이터를 columnstore 형식으로 물리적으로 저장합니다. Columnstore 형식에서는 데이터가 열로 압축되거나 압축이 풀립니다. 쿼리에서 요청되지 않은 다른 값은 각 행에 압축을 풀지 않아도 됩니다. 따라서 큰 테이블의 전체 열을 빠르게 검색할 수 있습니다.
rowstore는 행과 열이 있는 테이블로 논리적으로 구성된 후 행 데이터 형식으로 물리적으로 저장되는 데이터입니다. 이는 클러스터형 B+ 트리 인덱스 또는 힙과 같은 관계형 테이블 데이터를 저장하는 일반적인 방법입니다.

또한 columnstore 인덱스는 일부 행을 deltastore라는 rowstore 형식으로 물리적으로 저장합니다. 델타 행 그룹이라고도 하는 deltastore는 개수가 너무 적어서 columnstore로 압축할 수 없는 행을 보관하는 장소입니다. 각 델타 행 그룹은 행 저장소인 클러스터형 B+ 트리 인덱스로 구현됩니다.

행 그룹 및 열 세그먼트에서 작업이 수행됨

columnstore 인덱스는 행을 관리 가능한 단위로 그룹화합니다. 이러한 각 단위를 행 그룹이라고 합니다. 최상의 성능을 위해 행 그룹의 행 수는 압축 비율을 개선할 만큼 충분히 크고 메모리 작업에서 이점을 얻을 수 있을 만큼 작습니다.

예를 들어 columnstore 인덱스는 행 그룹에 대해 다음 작업을 수행합니다.

행 그룹을 columnstore로 압축합니다. 압축은 행 그룹 내의 각 열 세그먼트에서 수행됩니다.
삭제된 데이터 제거를 포함하여 작업 중에 ALTER INDEX ... REORGANIZE 행 그룹을 병합합니다.
작업 중에 모든 행 그룹을 다시 만듭니다 ALTER INDEX ... REBUILD .
DMV(동적 관리 뷰)의 행 그룹 상태 및 조각에 대해 보고합니다.

deltastore는 델타 행 그룹이라는 하나 이상의 행 그룹으로 구성됩니다. 각 델타 행 그룹은 행 그룹에 1,048,576개의 행이 포함될 때까지 작은 대량 로드 및 삽입을 저장하는 클러스터형 B+ 트리 인덱스이며, 이때 튜플 이동기 라는 프로세스는 닫힌 행 그룹을 columnstore로 자동으로 압축합니다.

행 그룹 상태에 대한 자세한 내용은 sys.dm_db_column_store_row_group_physical_stats를 참조하세요.

Tip

소규모의 행 그룹이 너무 많으면 columnstore 인덱스 품질이 저하됩니다. 재구성 작업은 삭제된 행을 제거하고 압축된 행 그룹을 결합하는 방법을 결정하는 내부 임계값 정책에 따라 더 작은 행 그룹을 병합합니다. 병합 후 인덱스 품질이 향상됩니다.

SQL Server 2019(15.x) 이상 버전에서 튜플 이동기는 내부 임계값에 따라 일정 시간 동안 존재했던 더 작은 열린 델타 행 그룹을 자동으로 압축하거나 많은 수의 행이 삭제된 압축된 행 그룹을 병합하는 백그라운드 병합 작업의 도움을 받습니다.

각 열에는 각 행 그룹에 일부 값이 있습니다. 이러한 값을 열 세그먼트라고 합니다. 각 행 그룹에는 테이블의 모든 열에 대해 하나의 열 세그먼트가 포함됩니다. 각 열에는 각 행 그룹에 하나의 열 세그먼트가 있습니다.

클러스터형 columnstore 열 세그먼트의 다이어그램입니다.

columnstore 인덱스가 행 그룹을 압축하는 경우 각 열 세그먼트를 별도로 압축합니다. 전체 열의 압축을 풀려면 columnstore 인덱스가 각 행 그룹에서 하나의 열 세그먼트만 압축을 풀면 됩니다.

작은 로드 및 삽입은 deltastore로 이동합니다.

columnstore 인덱스는 한 번에 102,400개 이상의 행을 columnstore 인덱스로 압축하여 columnstore 압축 및 성능을 향상합니다. 행을 대량으로 압축하기 위해 columnstore 인덱스는 deltastore에 작은 로드 및 삽입을 누적합니다. deltastore 작업은 백그라운드에서 처리됩니다. 쿼리 결과를 반환하기 위해 클러스터형 columnstore 인덱스는 columnstore와 deltastore의 쿼리 결과를 결합합니다.

행은 다음과 같은 경우 deltastore로 이동합니다.

INSERT INTO ... VALUES 문과 함께 삽입된 경우
대량 로드가 종료된 후 102,400보다 작은 숫자인 경우
Updated. 각 업데이트는 삭제와 삽입으로 구현됩니다.

또한 deltastore는 삭제된 것으로 표시되었지만 아직 Columnstore에서 물리적으로 삭제되지 않은 삭제된 행에 대한 ID 목록을 저장합니다.

델타 행 그룹이 꽉 차면 columnstore로 압축됨

클러스터형 columnstore 인덱스는 행 그룹을 columnstore로 압축하기 전에 각 델타 행 그룹에 최대 1,048,576개의 행을 수집합니다. 이를 통해 columnstore 인덱스의 압축이 개선됩니다. 델타 행 그룹이 최대 행 수에 도달하면 OPEN에서 CLOSED로 상태가 전환됩니다. 튜플 이동기라는 백그라운드 프로세스는 닫힌 행 그룹이 있는지 확인합니다. 닫힌 행 그룹이 있으면 행 그룹을 압축하여 columnstore에 저장합니다.

델타 행 그룹이 압축된 경우 기존 델타 행 그룹은 TOMBSTONE 상태로 전환되어 나중에 이 그룹에 대한 참조가 없어지면 튜플 이동기에 의해 제거되며 새로운 압축된 행 그룹이 COMPRESSED로 표시됩니다.

행 그룹 상태에 대한 자세한 내용은 sys.dm_db_column_store_row_group_physical_stats를 참조하세요.

ALTER INDEX를 사용하여 인덱스를 다시 작성하거나 다시 구성하면 델타 행 그룹을 columnstore에 강제로 적용할 수 있습니다. 압축하는 동안 메모리 압력이 있을 경우 columnstore 인덱스가 압축된 행 그룹의 행 수를 줄일 수도 있습니다.

각 테이블 파티션에는 고유한 행 그룹과 델타 행 그룹이 있습니다.

분할 개념은 클러스터형 인덱스, 힙 및 columnstore 인덱스에서 동일합니다. 테이블을 분할하면 열 값 범위에 따라 테이블이 더 작은 행 그룹으로 나눕니다. 데이터 관리를 위해 자주 사용됩니다. 예를 들어 매년 데이터에 대한 파티션을 만든 다음 파티션 전환을 사용하여 이전 데이터를 저렴한 스토리지에 보관할 수 있습니다.

행 그룹은 항상 테이블 파티션 내에서 정의됩니다. columnstore 인덱스를 분할하는 경우 각 파티션에 압축된 행 그룹과 델타 행 그룹이 있습니다. 분할되지 않은 테이블에는 하나의 파티션이 포함됩니다.

Tip

columnstore에서 데이터를 제거해야 하는 경우 테이블 분할을 사용하는 것이 좋습니다. 더 이상 필요하지 않은 파티션을 전환하고 잘라내는 것은 columnstore에 조각화를 도입하지 않고 데이터를 삭제하는 효율적인 전략입니다.

각 파티션에는 복수의 델타 행 그룹이 있을 수 있습니다.

각 파티션에는 둘 이상의 델타 행 그룹이 있을 수 있습니다. columnstore 인덱스가 델타 행 그룹에 데이터를 추가해야 하고 델타 행 그룹이 다른 트랜잭션에 의해 잠겨 있는 경우 columnstore 인덱스는 다른 델타 행 그룹에 대한 잠금을 가져오려고 시도합니다. 사용할 수 있는 델타 행 그룹이 없는 경우 columnstore 인덱스는 새 델타 행 그룹을 생성합니다. 예를 들어 파티션이 10개인 테이블에는 흔히 20개 이상의 델타 행 그룹이 있을 수 있습니다.

동일한 테이블의 columnstore 및 rowstore 인덱스 결합

비클러스터형 인덱스에는 기본 테이블의 일부 또는 모든 행과 열의 복사본이 포함됩니다. 인덱스는 테이블의 하나 이상의 열로 정의되며 행을 필터링하는 선택적 조건이 있습니다.

rowstore 테이블에서 업데이트 가능한 비클러스터형 columnstore 인덱스를 만들 수 있습니다. columnstore 인덱스는 데이터 복사본을 저장하므로 추가 스토리지가 필요합니다. 그러나 columnstore 인덱스의 데이터는 rowstore 테이블에 필요한 것보다 훨씬 작은 크기로 압축됩니다. 이렇게 하면 columnstore 인덱스에 대한 분석을 실행하면서 동시에 rowstore 인덱스에서 OLTP 워크로드를 실행할 수 있습니다. rowstore 테이블의 데이터가 변경되면 columnstore가 업데이트되므로 두 인덱스 모두 동일한 데이터에 대해 작동하고 있습니다.

Rowstore 테이블에는 비클러스터형 columnstore 인덱스 한 개가 있을 수 있습니다. 자세한 내용은 Columnstore 인덱스 - 디자인 지침을 참조하세요.

클러스터형 columnstore 테이블에 하나 이상의 비클러스터형 rowstore 인덱스를 가질 수 있습니다. 이렇게 하면 기본 columnstore에서 테이블을 효율적으로 검색할 수 있습니다. 다른 옵션도 사용할 수 있습니다. 예를 들어 rowstore 테이블에 제약 조건을 사용하여 UNIQUE 고유성을 적용할 수 있습니다. 특수하지 않은 값이 rowstore 테이블에 삽입되지 않으면 데이터베이스 엔진은 이 값을 columnstore에 삽입하지 않습니다.

비클러스터형 columnstore 성능 고려 사항

비클러스터형 columnstore 인덱스 정의는 필터링된 조건 사용을 지원합니다. columnstore 인덱스를 추가하는 성능 효과를 최소화하려면 필터 식을 사용하여 분석에 필요한 데이터의 하위 집합에만 비클러스터형 columnstore 인덱스를 만듭니다.

메모리 최적화 테이블에는 하나의 columnstore 인덱스가 있을 수 있습니다. 테이블을 만들 때 만들거나 나중에 ALTER TABLE을 사용하여 추가할 수 있습니다.

자세한 내용은 Columnstore 인덱스 - 쿼리 성능을 참조하세요.

메모리 최적화 해시 인덱스 디자인 지침

In-Memory OLTP를 사용하는 경우 모든 메모리 최적화 테이블에는 하나 이상의 인덱스가 있어야 합니다. 메모리 최적화 테이블의 경우 모든 인덱스도 메모리 최적화됩니다. 해시 인덱스는 메모리 최적화 테이블에 사용할 수 있는 인덱스 유형 중 하나입니다. 자세한 내용은 메모리 최적화 테이블의 인덱스를 참조하세요.

메모리 최적화 해시 인덱스 아키텍처

해시 인덱스는 포인터 배열로 구성되며 배열의 각 요소를 해시 버킷이라고 합니다.

각 버킷은 8바이트이며, 키 항목의 링크 목록의 메모리 주소를 저장하는 데 사용됩니다.
각 항목은 인덱스 키 값과 기본 메모리 최적화 테이블의 해당 행 주소를 더한 값입니다.
각 항목은 모두 현재 버킷에 연결된 항목 링크 목록의 다음 항목을 가리킵니다.

인덱스를 만들 때 버킷 수를 지정해야 합니다.

테이블 행 또는 고유 값에 대한 버킷의 비율이 낮을수록 평균 버킷 링크 목록은 길어집니다.
짧은 링크 목록은 긴 링크 목록보다 수행 속도가 빠릅니다.
해시 인덱스의 최대 버킷 수는 1,073,741,824입니다.

Tip

데이터에 대한 권한 BUCKET_COUNT을(를) 확인하려면 해시 인덱스 버킷 수 구성을 참조하세요.

해시 함수는 인덱스 키 열에 적용되며 함수 결과에 따라 해당 키가 속하는 버킷이 결정됩니다. 각 버킷에는 해시된 키 값이 해당 버킷에 매핑된 행에 대한 포인터가 있습니다.

해시 인덱스에 사용되는 해시 함수의 특징은 다음과 같습니다.

데이터베이스 엔진에는 모든 해시 인덱스에 사용되는 하나의 해시 함수가 있습니다.
해시 함수는 결정적입니다. 동일한 입력 키 값이 항상 해시 인덱스의 동일한 버킷에 매핑됩니다.
여러 인덱스 키를 동일한 해시 버킷에 매핑할 수 있습니다.
해시 함수는 균형을 이루고 있습니다. 즉, 해시 버킷에 대한 인덱스 키 값의 분포는 일반적으로 평평한 선형 분포가 아닌 포아송 또는 종형 곡선 분포를 따릅니다.
포아송 분포는 균일한 분포가 아닙니다. 인덱스 키 값은 해시 버킷에 고르게 분산되지 않습니다.
두 개의 인덱스 키가 동일한 해시 버킷에 매핑되어 있으면 해시 충돌이 발생합니다. 많은 수의 해시 충돌이 읽기 작업에 성능 영향을 미칠 수 있습니다. 현실적인 목표는 버킷의 30%가 서로 다른 두 키 값을 포함하는 것입니다.

해시 인덱스와 버킷의 상호 작용은 다음 이미지에 요약되어 있습니다.

해시 인덱스와 버킷 간의 상호 작용을 보여 주는 다이어그램입니다.

해시 인덱스 버킷 수 구성

해시 인덱스 버킷 수는 인덱스 생성 시 지정되며 ALTER TABLE...ALTER INDEX REBUILD 구문을 사용하여 변경할 수 있습니다.

대부분의 경우 버킷 수는 인덱스 키의 고유 값 수의 1~2배 사이여야 합니다. 특정 인덱스 키에 있는 값 수를 항상 예측할 수 있는 것은 아닙니다. BUCKET_COUNT 값이 실제 키 값 수의 10배 이내이면 일반적으로 성능이 여전히 양호하며 일반적으로 과대평가하는 것이 과소평가하는 것보다 낫습니다.

버킷이 너무 적으면 다음과 같은 단점이 있습니다.

고유 키 값의 해시 충돌이 더 많이 발생했습니다.
각 고유 값은 동일한 버킷을 다른 고유 값과 공유해야 합니다.
버킷당 평균 체인 길이가 증가합니다.
버킷 체인이 길수록 인덱스의 동일성 조회 속도가 느려집니다.

버킷이 너무 많으면 다음과 같은 단점이 있습니다.

버킷 수가 너무 많으면 빈 버킷이 더 많아질 수 있습니다.
빈 버킷은 전체 인덱스 검색의 성능에 영향을 줍니다. 검사를 정기적으로 수행하는 경우 고유한 인덱스 키 값의 수에 가까운 버킷 수를 선택하는 것이 좋습니다.
각 버킷은 8바이트만 사용하지만 빈 버킷도 메모리를 사용합니다.

Note

버킷을 더 추가해도 중복 값을 공유하는 항목의 연결이 줄어들지는 않습니다. 값 중복 비율은 버킷 수를 계산하지 않고 해시 인덱스 또는 비클러스터형 인덱스가 적절한 인덱스 형식인지 여부를 결정하는 데 사용됩니다.

해시 인덱스에 대한 성능 고려 사항

해시 인덱스의 성능은 다음과 같습니다.

WHERE 절의 조건자가 해시 인덱스 키의 각 열에 대해 정확한 값을 지정하는 경우 성능이 매우 좋습니다. 해시 인덱스는 부등식 조건자가 있는 경우 검색으로 되돌아갑니다.
WHERE 절의 조건자가 인덱스 키에서 값 범위를 찾는 경우에 저하됩니다.
WHERE절의 조건자가 두 열 해시 인덱스 키의 첫 번째 열에 대해 하나의 특정 값을 규정하지만 키의 다른 열에 대한 값을 지정하지 않는 경우 성능이 좋지 않습니다.

Tip

조건자는 해시 인덱스 키의 모든 열을 포함해야 합니다. 해시 인덱스를 탐색하려면 전체 키가 필요합니다.

해시 인덱스가 사용되고 고유 인덱스 키 수가 행 수보다 100배 이상 작은 경우 큰 행 체인을 방지하기 위해 더 큰 버킷 수로 늘리거나 비 클러스터형 인덱 스 대신 사용하는 것이 좋습니다.

해시 인덱스 만들기

해시 인덱스 생성 시 다음을 고려합니다.

해시 인덱스는 메모리 최적화 테이블에만 존재할 수 있습니다. 디스크 기반 테이블에는 존재할 수 없습니다.
해시 인덱스는 기본적으로 고유하지 않지만 고유하게 선언할 수 있습니다.

다음 예제에서는 고유한 해시 인덱스 만들기:

ALTER TABLE MyTable_memop ADD INDEX ix_hash_Column2
    UNIQUE HASH (Column2) WITH (BUCKET_COUNT = 64);

메모리 최적화된 테이블의 행 버전 및 가비지 수집

메모리 최적화 테이블에서는 특정 UPDATE 문이 행에 영향을 미칠 때 테이블이 해당 행의 업데이트된 버전을 생성합니다. 업데이트 트랜잭션 중에 다른 세션에서 이전 버전의 행을 읽을 수 있으므로 행 잠금과 관련된 성능 저하를 방지할 수 있습니다.

해시 인덱스에는 업데이트를 반영하기 위해 항목의 다른 버전이 있을 수도 있습니다.

나중에 이전 버전이 더 이상 필요하지 않으면 GC(가비지 수집) 스레드가 버킷과 해당 링크 목록을 트래버스하여 이전 항목을 정리합니다. 링크 목록 체인 길이가 짧은 경우 GC 스레드 성능은 향상됩니다. 자세한 내용은 메모리 내 OLTP 가비지 수집을 참조하세요.

메모리 최적화 비클러스터형 인덱스 디자인 지침

해시 인덱스 외에도 비클러스터형 인덱스는 메모리 최적화 테이블에서 가능한 다른 인덱스 형식입니다. 자세한 내용은 메모리 최적화 테이블의 인덱스를 참조하세요.

메모리 최적화 비클러스터형 인덱스 아키텍처

메모리 최적화 테이블의 비클러스터형 인덱스는 원래 2011년 Microsoft Research에서 구상하고 설명한 Bw-tree라는 데이터 구조를 사용하여 구현됩니다. Bw-트리는 잠금 및 래치 없는 B-트리의 변형입니다. 자세한 내용은 Bw-트리: 새 하드웨어 플랫폼을 위한 B-트리를 참조하세요.

높은 수준에서 Bw-tree는 페이지 ID(PidMap)로 구성된 페이지 맵, 페이지 ID(PidAlloc)를 할당 및 재사용하는 기능 및 페이지 맵과 서로 연결된 페이지 집합으로 이해할 수 있습니다. 이러한 세 가지 상위 수준의 하위 구성 요소가 Bw-트리의 기본 내부 구조를 구성합니다.

이 구조는 각 페이지에 정렬된 키 값 집합이 있고 인덱스에 여러 수준이 있으며 각각이 하위 수준을 가리키고 리프 수준이 데이터 행을 가리킨다는 점에서 일반적인 B-트리와 비슷합니다. 하지만 여러 가지 차이점이 있습니다.

해시 인덱스와 마찬가지로 여러 데이터 행을 함께 연결하여 버전 관리가 지원됩니다. 수준 사이의 페이지 포인터는 논리적 페이지 ID입니다. 이 ID는 페이지 매핑 테이블에 대한 오프셋이며 각 페이지의 물리적 주소를 갖습니다.

인덱스 페이지의 현재 위치 업데이트가 없습니다. 이 목적을 위해 새 델타 페이지가 도입되었습니다.

페이지 업데이트에는 래치 또는 잠금이 필요하지 않습니다.
인덱스 페이지의 크기는 고정되지 않았습니다.

각 비리프 수준 페이지의 키 값은 그것이 참조하는 자식이 가진 가장 큰 값이며, 각 행에는 해당 페이지의 논리 페이지 ID도 포함됩니다. 리프 수준 페이지에는 키 값과 함께 데이터 행의 물리적 주소가 포함됩니다.

지점 조회는 B-트리와 유사합니다. 단, 페이지가 한 방향으로만 연결되기 때문에 데이터베이스 엔진은 오른쪽 페이지 포인터를 따릅니다. 여기서 각 비리프 페이지는 B-트리에서와 같이 가장 낮은 값이 아니라 자식의 값이 가장 높습니다.

리프 수준 페이지를 변경해야 하는 경우 데이터베이스 엔진은 페이지 자체를 수정하지 않습니다. 대신 데이터베이스 엔진은 변경 사항을 설명하는 델타 레코드를 만들고 이전 페이지에 추가합니다. 그런 다음 해당 이전 페이지의 페이지 맵 테이블 주소를 이제 이 페이지의 실제 주소가 되는 델타 레코드의 주소로 업데이트합니다.

Bw-트리 구조를 관리하기 위해 통합, 분할, 병합의 세 가지 작업이 필요할 수 있습니다.

델타 통합

긴 델타 레코드 체인은 인덱스 검색 시 긴 체인 통과가 필요할 수 있으므로 결국 검색 성능을 저하시킬 수 있습니다. 이미 16개의 요소가 있는 체인에 새 델타 레코드가 추가되면 델타 레코드의 변경 사항이 참조된 인덱스 페이지로 통합되고, 페이지가 통합을 트리거한 새 델타 레코드가 나타내는 변경 사항을 포함하여 다시 빌드됩니다. 새로 다시 빌드된 페이지는 페이지 ID는 동일하지만 메모리 주소가 다릅니다.

메모리 최적화 페이지 매핑 테이블을 보여 주는 다이어그램입니다.

분할 페이지

Bw-tree의 인덱스 페이지는 단일 행 저장부터 최대 8KB 저장까지 필요에 따라 확장됩니다. 인덱스 페이지가 8KB로 확장되면 단일 행을 새로 삽입할 경우 인덱스 페이지가 분할됩니다. 내부 페이지의 경우 이는 다른 키 값과 포인터를 추가할 공간이 더 이상 없는 경우를 의미하고, 리프 페이지의 경우, 이는 모든 델타 레코드가 통합되면 행이 너무 커서 페이지에 맞지 않는다는 것을 의미합니다. 리프 페이지의 페이지 머리글에 있는 통계 정보는 델타 레코드를 통합하는 데 필요한 공간을 추적합니다. 이 정보는 각각의 새 델타 레코드가 추가될 때마다 조정됩니다.

분할 작업은 두 개의 원자성 단계로 수행됩니다. 다음 다이어그램에서는 값이 5인 키가 삽입되고 현재 리프 수준 페이지(키 값 4)의 끝을 가리키는 리프가 아닌 페이지가 존재하기 때문에 리프 페이지가 분할을 강제한다고 가정합니다.

1단계: 두 개의 새 페이지 P1과 P2를 할당하고 새로 삽입된 행을 포함하여 이전 P1 페이지의 행을 새 페이지로 분할합니다. 페이지 매핑 테이블의 새 슬롯은 페이지 P2의 실제 주소를 저장하는 데 사용됩니다. P1 페이지 및 P2 페이지는 아직 어떤 동시 액세스 작업에도 액세스할 수 없습니다. 또한 P1에서 P2로의 논리적 포인터가 설정됩니다. 그런 다음 한 원자성 단계에서 페이지 매핑 테이블을 업데이트하여 포인터를 기존 P1에서 새 P1으로 변경합니다.

2단계: 리프가 아닌 페이지는 P1을 가리키지만 리프가 아닌 페이지에서 P2로 이어지는 직접 포인터는 없습니다. P2는 P1을 통해서만 도달할 수 있습니다. 리프가 아닌 페이지에서 P2로 이어지는 포인터를 만들려면 새로운 리프가 아닌 페이지(내부 인덱스 페이지)를 할당하고, 이전 리프가 아닌 페이지의 모든 행을 복사하고, P2를 가리키는 새 행을 추가합니다. 이 작업이 완료되면 한 원자성 단계에서 페이지 매핑 테이블을 업데이트하여 기존 리프가 아닌 페이지에서 새 비-리프 페이지로 포인터를 변경합니다.

병합 페이지

DELETE 작업으로 인해 페이지가 최대 페이지 크기(8KB)의 10% 미만이거나 단일 행이 있는 경우 해당 페이지는 연속 페이지와 병합됩니다.

페이지에서 행이 삭제되면 해당 삭제에 대한 델타 레코드가 추가됩니다. 또한 인덱스 페이지(비리프 페이지)가 병합할 수 있는지 여부를 확인합니다. 이 검사는 행을 삭제한 후 나머지 공간이 최대 페이지 크기의 10퍼센트 미만인지 확인합니다. 자격이 있는 경우 병합은 세 가지 원자적인 단계로 수행됩니다.

다음 이미지에서는 DELETE 작업이 키 값 10을 삭제한다고 가정합니다.

1단계: 키 값 10을 나타내는 델타 페이지(파란색 삼각형)가 생성되고, 리프가 아닌 페이지 Pp1의 포인터는 새로운 델타 페이지로 설정됩니다. 또한 특수 병합 델타 페이지(녹색 삼각형)가 생성되고 델타 페이지를 가리키도록 연결됩니다. 이 단계에서는 두 페이지(델타 페이지 및 병합-델타 페이지)가 동시 트랜잭션에 표시되지 않습니다. 한 원자성 단계에서 페이지 매핑 테이블의 리프 수준 페이지 P1을 가리키는 포인터는 병합-델타 페이지를 가리키도록 업데이트됩니다. 이 단계 후에 10의 키 값 Pp1에 대한 항목은 이제 병합-델타 페이지를 가리킵니다.

2단계: 리프가 아닌 페이지 7에서 키 값 Pp1을 나타내는 행을 제거해야 하며, 키 값 10에 대한 항목을 P1을 가리키도록 업데이트해야 합니다. 이 작업을 수행하기 위해 새로운 리프가 아닌 페이지 Pp2가 할당되고, 키 값 Pp1을 나타내는 행을 제외한 7의 모든 행이 복사되고, 키 값 10의 행은 P1 페이지를 가리키도록 업데이트됩니다. 이 작업이 완료되면 한 원자성 단계에서 Pp1을 가리키는 페이지 매핑 테이블 항목이 Pp2를 가리키도록 업데이트됩니다. 더 이상 Pp1에 연결할 수 없습니다.

3단계: 리프 수준 페이지 P2 및 P1이 병합되고 델타 페이지가 제거됩니다. 이 작업을 위해 새 페이지 P3이 할당되고 P2와 P1의 행이 병합되며 델타 페이지 변경 사항이 새 P3에 포함됩니다. 그런 다음 하나의 원자성 단계에서 페이지 P1을 가리키는 페이지 매핑 테이블 항목이 페이지 P3을 가리키도록 업데이트됩니다.

메모리 최적화 비클러스터형 인덱스에 대한 성능 고려 사항

같지 않음 조건자를 사용하여 메모리 최적화 테이블을 쿼리할 때 해시 인덱스보다 비클러스터형 인덱스의 성능이 더 좋습니다.

메모리 최적화 테이블의 열은 해시 인덱스와 비클러스터형 인덱스 모두의 일부일 수 있습니다.

비클러스터형 인덱스의 키 열에 중복 값이 많으면 업데이트, 삽입 및 삭제 시 성능이 저하될 수 있습니다. 이 상황에서 성능을 향상하는 한 가지 방법은 인덱스 키에서 선택성이 더 좋은 열을 추가하는 것입니다.

인덱스 메타데이터

인덱스 정의, 속성 및 데이터 통계와 같은 인덱스 메타데이터를 검사하려면 다음 시스템 뷰를 사용합니다.

이전 보기는 모든 인덱스 형식에 적용합니다. columnstore 인덱스의 경우 다음 보기를 추가로 사용합니다.

columnstore 인덱스의 경우 모든 열은 메타데이터에 포괄 열로 저장됩니다. columnstore 인덱스에는 키 열이 없습니다.

메모리 최적화 테이블의 인덱스에 대해서는 다음 보기를 추가로 사용합니다.

피드백

이 페이지가 도움이 되었나요?

Last updated on 2025-11-18