SQL Server 및 Azure SQL 인덱스 아키텍처와 디자인 가이드

아티클
04/04/2023

적용 대상:SQL Server Azure SQL Database Azure SQL Managed InstanceAzure Synapse Analytics Analytics Platform System(PDW)

데이터베이스 애플리케이션 병목 상태는 주로 잘못 디자인된 인덱스와 인덱스의 부족으로 인해 나타납니다. 효율적인 인덱스를 디자인하는 것은 좋은 데이터베이스 및 애플리케이션 성능을 달성하는 데 가장 중요합니다. 이 인덱스 디자인 가이드에서는 인덱스 아키텍처에 관해 설명하고 애플리케이션 요구 사항을 충족하는 효과적인 인덱스를 디자인하는 데 도움이 되는 모범 사례를 제공합니다.

이 가이드에서는 사용자가 사용할 수 있는 인덱스 유형에 대한 기본적인 지식이 있다고 가정합니다. 인덱스 형식에 대한 일반적인 설명은 인덱스 형식을 참조 하세요.

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

기본 스토리지 형식	인덱스 유형
디스크 기반 rowstore
	클러스터형
	비클러스터형 인덱스
	고유한
	필터링됨
Columnstore
	클러스터형 Columnstore
	비클러스터형 Columnstore
메모리 최적화
	해시
	메모리 최적화 비클러스터형

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

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

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

인덱스 디자인 기본 사항

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

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

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

rowstore 인덱스는 행과 열이 있는 테이블로 논리적으로 구성되고 rowstore 1이라는 행 단위 데이터 형식으로 물리적으로 저장되거나 columnstore라는 열 단위 데이터 형식으로 저장된 데이터를 저장합니다.

데이터베이스 및 해당 워크로드에 적합한 인덱스를 선택하는 것은 쿼리 속도와 업데이트 비용 간의 복잡한 균형 맞추기 작업입니다. 좁은 디스크 기반 rowstore 인덱스나 인덱스 키의 열이 적은 인덱스는 디스크 공간과 유지 관리 오버헤드를 덜 요구합니다. 반면에 와이드 인덱스는 더 많은 쿼리를 다룹니다. 가장 효율적인 인덱스를 찾기 전에 여러 가지 디자인을 실험해야 할 수 있습니다. 데이터베이스 스키마나 애플리케이션에 영향을 주지 않고 인덱스를 추가, 수정 및 삭제할 수 있습니다. 따라서 다른 인덱스를 사용하여 실험하는 것을 주저해서는 안 됩니다.

데이터베이스 엔진 쿼리 최적화 프로그램은 대부분의 경우 가장 효과적인 인덱스를 안정적으로 선택합니다. 전체 인덱스 디자인 전략은 쿼리 최적화 프로그램에서 선택하고 신뢰하여 올바른 결정을 내릴 수 있도록 다양한 인덱스를 제공해야 합니다. 이렇게 하면 분석 시간이 단축되고 다양한 상황에서 성능이 향상됩니다. 쿼리 최적화 프로그램에서 특정 쿼리에 사용하는 인덱스를 확인하려면 SQL Server Management Studio의 쿼리 메뉴에서 실제 실행 계획 포함을 선택합니다.

항상 인덱스 사용량을 좋은 성능과 동일시하지는 않으며 효율적인 인덱스 사용과 좋은 성능을 동일시하지는 않습니다. 인덱스 사용이 항상 최상의 성능을 생성하는 데 도움이 된다면 쿼리 최적화 프로그램의 작업은 간단합니다. 실제로 잘못된 인덱스 선택으로 인해 최적 성능이 저하될 수 있습니다. 따라서 쿼리 최적화 프로그램의 작업은 성능이 향상되는 경우에만 인덱스 또는 인덱스 조합을 선택하고 성능이 저하될 때 인덱싱된 검색을 방지하는 것입니다.

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

인덱스 디자인 작업

다음 작업은 인덱스를 디자인하기 위한 권장 전략을 구성합니다.

데이터베이스의 특징을 이해합니다.
- 데이터 수정 작업이 많고 높은 처리량을 유지해야 하는 OLTP(온라인 트랜잭션 처리)를 예로 들 수 있습니다. 래치 없는 디자인을 제공하므로 메모리 최적화 테이블 및 인덱스가 이 시나리오에 가장 적합합니다. 자세한 내용은 이 가이드의 메모리 최적화 테이블에 대한 인덱스 또는 메모리 최적화 테이블 디자인 지침 및 메모리 최적화 테이블 디자인 지침에 대한 해시 인덱스에 대한 비클러스터형 인덱스를 참조하세요.
- 또는 대량의 데이터 집합을 신속하게 처리해야 하는 DSS(의사 결정 지원) 또는 OLAP(데이터 웨어하우징) 데이터베이스를 예로 들 수 있습니다. Columnstore 인덱스는 일반 데이터 웨어하우징 데이터 집합에 가장 적합합니다. Columnstore 인덱스는 필터링, 집계, 그룹화, 스타 조인 쿼리 등 일반 데이터 웨어하우징 쿼리에 대한 성능을 개선하여 사용자의 데이터 웨어하우징 환경을 바꿀 수 있습니다. 자세한 내용은 이 가이드의 Columnstore 인덱스 개요 또는 Columnstore 인덱스 디자인 지침을 참조하세요.
가장 자주 사용되는 쿼리의 특징을 이해합니다. 예를 들어 자주 사용되는 쿼리가 둘 이상의 테이블을 조인한다는 것을 알면 사용할 최상의 인덱스 유형을 결정하는 데 도움이 됩니다.
쿼리에 사용되는 열의 특징을 이해합니다. 예를 들어 인덱스는 정수 데이터 형식이 있고 고유하거나 null이 아닌 열에 적합합니다. 데이터의 하위 집합이 잘 정의된 열의 경우 SQL Server 2008(10.0.x) 이상 버전에서 필터링된 인덱스를 사용할 수 있습니다. 자세한 내용은 이 가이드의 필터링된 인덱스 디자인 지침을 참조하세요.
인덱스를 만들거나 유지 관리할 때 성능을 향상시킬 수 있는 인덱스 옵션을 파악합니다. 예를 들어 기존 큰 테이블에 클러스터형 인덱스 만들기는 인덱스 옵션의 ONLINE 이점을 누릴 수 있습니다. ONLINE 옵션을 사용하면 인덱스를 만들거나 다시 빌드하는 동안 기본 데이터에 대한 동시 작업을 계속할 수 있습니다. 자세한 내용은 인덱스 옵션 설정을 참조하세요.
인덱스에 가장 적합한 스토리지 위치를 파악합니다.

비클러스터형 인덱스가 기본 테이블과 동일한 파일 그룹 또는 다른 파일 그룹에 저장될 수 있습니다. 인덱스의 스토리지 위치는 디스크 I/O 성능을 향상시켜 쿼리 성능을 향상시킬 수 있습니다. 예를 들어 테이블 파일 그룹과 다른 디스크에 있는 파일 그룹에 비클러스터형 인덱스를 저장하면 여러 디스크를 동시에 읽을 수 있으므로 성능이 향상될 수 있습니다. 또는 클러스터형 인덱스와 비클러스터형 인덱스가 여러 파일 그룹에 하나의 파티션 구성표를 사용할 수 있습니다. 분할을 고려할 때 인덱스가 정렬되어야 하는지, 즉 기본적으로 테이블과 동일한 방식으로 분할할지 또는 독립적으로 분할해야 하는지 결정합니다. 이 문서의 파일 그룹 또는 파티션 구성표에 인덱스 배치 섹션에 대해 자세히 알아봅니다.
sys.dm_db_missing_index_details 및 sys.dm_db_missing_index_columns와 같은 DMV(동적 관리 뷰)를 사용하여 누락된 인덱스를 식별하는 경우 동일한 테이블 및 열에 비슷한 변형의 인덱스가 제공될 수 있습니다. 중복 인덱스를 만들지 않도록 누락된 인덱스 제안과 함께 테이블의 기존 인덱스를 검사합니다. 누락된 인덱스 제안을 사용하여 비클러스터형 인덱스를 조정하는 방법에 대해 자세히 알아봅니다.

일반 인덱스 디자인 지침

경험이 많은 데이터베이스 관리자는 인덱스를 잘 디자인할 수 있습니다. 그러나 데이터베이스와 작업이 조금만 복잡해져도 이 태스크는 복잡하고 시간이 많이 걸리며 오류가 쉽게 발생할 수 있습니다. 데이터베이스, 쿼리 및 데이터 열의 특성을 이해하면 최적의 인덱스를 디자인할 수 있습니다.

데이터베이스 고려 사항

인덱스 디자인 시 다음 데이터베이스 지침을 고려합니다.

테이블의 많은 수의 인덱스는 테이블의 INSERT데이터가 변경될 때 모든 인덱스를 적절하게 조정해야 하므로 , UPDATEDELETE및 MERGE 문의 성능에 영향을 줍니다. 예를 들어 열이 여러 인덱스에 사용되고 해당 열의 데이터를 수정하는 문을 실행하는 UPDATE 경우 해당 열이 포함된 각 인덱스와 기본 기본 테이블(힙 또는 클러스터형 인덱스)의 열을 업데이트해야 합니다.
- 많이 업데이트된 테이블을 과도하게 인덱싱하지 말고 인덱스를 좁게 유지합니다. 즉, 가능한 한 적은 수의 열을 사용합니다.
- 많은 인덱스를 사용하여 업데이트 요구 사항이 낮지만 많은 양의 데이터가 있는 테이블에서 쿼리 성능을 향상시킵니다. 쿼리 최적화 프로그램은 가장 빠른 액세스 방법을 결정하기 위해 선택할 수 있는 인덱스가 더 많기 때문에 많은 수의 인덱스를 통해 SELECT 문과 같이 데이터를 수정하지 않는 쿼리의 성능을 높일 수 있습니다.
쿼리 최적화 프로그램이 기본 테이블 검색을 수행하는 것보다 데이터 검색을 트래버스하는 데 더 오래 걸릴 수 있으므로 작은 테이블을 인덱싱하는 것이 최적이 아닐 수 있습니다. 따라서 작은 테이블의 인덱스는 사용되지 않을 수 있지만 테이블의 데이터가 변경될 때 여전히 기본 수집해야 합니다.
뷰의 인덱스는 뷰에 집계, 테이블 조인 또는 집계 및 조인 조합이 포함된 경우 상당한 성능 향상을 제공할 수 있습니다. 쿼리 최적화 프로그램에서 뷰를 사용하기 위해 쿼리에서 뷰를 명시적으로 참조할 필요는 없습니다.
Azure SQL Database에서 주 복제본의 데이터베이스는 인덱스용 데이터베이스 관리자 성능 권장 사항을 자동으로 생성합니다. 필요에 따라 자동 인덱스 튜닝을 사용하도록 설정할 수 있습니다.
쿼리 저장소 최적이하의 성능으로 쿼리를 식별하고 최적화 프로그램에서 선택한 인덱스를 문서화하는 쿼리 실행 계획의 기록을 제공합니다.

쿼리 고려 사항

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

조건자에서 자주 사용되는 열에 비클러스터형 인덱스를 만들고 쿼리의 조인 조건을 만듭니다. 이러한 열은 SARGable¹ 열입니다. 그러나 불필요한 열을 추가하지 않아야 합니다. 인덱스 열을 너무 많이 추가하면 디스크 공간 및 인덱스 유지 관리 성능이 떨어질 수 있습니다.
쿼리 요구 사항을 충족하는 데 필요한 모든 데이터가 인덱스 자체 내에 있기 때문에 인덱스를 포함하면 쿼리 성능이 향상될 수 있습니다. 즉, 요청된 데이터를 검색하려면 테이블 또는 클러스터형 인덱스의 데이터 페이지가 아닌 인덱스 페이지만 필요합니다. 따라서 전체 디스크 I/O를 줄입니다. 예를 들어 A, B, C 열에 대해 만든 복합 인덱스가 포함된 테이블에서 A와 B 열을 쿼리하면 지정된 데이터를 인덱스 자체에서만 검색할 수 있습니다.

Important

포함 인덱스는 기본 테이블에 액세스하지 않고 조회에서 발생하지 않고 직접 하나 이상의 비슷한 쿼리 결과를 확인하는 비클러스터형 인덱스를 지정하는 것입니다.

이러한 인덱스에는 리프 수준에서 필요한 모든 비 SARGable 열이 있습니다. 즉, SELECT 절에서 반환된 열과 모든 WHERE 및 JOIN 인수가 인덱스에 포함됩니다.

테이블 자체의 행 및 열과 비교할 때 인덱스 범위가 적당히 좁으면 쿼리 실행에 훨씬 적은 I/O가 사용될 수 있고, 이는 인덱스가 전체 열의 하위 집합임을 의미합니다.

큰 테이블의 작은 부분을 선택할 때 인덱스를 포함하는 것이 좋습니다. 예를 들어 NULL이 아닌 값이 몇 가지 포함된 스파스 열과 같이 해당 작은 부분이 고정 조건자로 정의됩니다.
여러 쿼리를 사용하여 동일한 행을 업데이트하는 대신 단일 문에서 가능한 한 많은 행을 삽입하거나 수정하는 쿼리를 작성합니다. 하나의 문만 사용하면 최적화된 인덱스 기본 테넌스가 악용될 수 있습니다.
쿼리 유형 및 쿼리에서 열이 사용되는 방식을 평가합니다. 예를 들어 정확히 일치하는 쿼리 형식에 사용되는 열은 비클러스터형 또는 클러스터형 인덱스로 적합합니다.

¹ 관계형 데이터베이스의 SARGable이라는 용어는 인덱스를 사용하여 쿼리 실행 속도를 높일 수 있는 Search ARGument 가능 조건자를 나타냅니다.

열 고려 사항

인덱스 디자인 시 다음 열 지침을 고려합니다.

클러스터형 인덱스의 인덱스 키 길이는 짧게 유지합니다. 또한 클러스터형 인덱스는 고유 열 또는 null이 아닌 열에서 만들어지는 이점을 누릴 수 있습니다.
ntext, text, image, varchar(max), nvarchar(max) 및 varbinary(max) 데이터 형식의 열은 인덱스 키 열로 지정할 수 없습니다. 그러나 varchar(max), nvarchar(max), varbinary(max) 및 xml 데이터 형식은 비클러스터형 인덱스에 키가 아닌 인덱스 열로 참여할 수 있습니다. 자세한 내용은 이 가이드의 '포함된 열이 있는 인덱스' 섹션을 참조하세요.
xml 데이터 형식은 XML 인덱스의 키 열만 될 수 있습니다. 자세한 내용은 XML 인덱스를 참조하세요. SQL Server 2012 SP1에서는 선택적 XML 인덱스라고 하는 새로운 유형의 XML 인덱스를 제공합니다. 이 새로운 인덱스는 XML로 저장된 데이터에 대한 쿼리 성능을 향상시키고, 큰 XML 데이터 워크로드를 더 빠르게 인덱싱할 수 있으며, 인덱스 자체의 스토리지 비용을 줄여 확장성을 향상시킬 수 있습니다. 자세한 내용은 SXI(선택적 XML 인덱스)를 참조하세요.
열 고유성을 검사합니다. 동일한 열 조합의 고유 인덱스 대신 고유 인덱스는 인덱스를 더 유용하게 만드는 쿼리 최적화 프로그램의 추가 정보를 제공합니다. 자세한 내용은 이 가이드의 고유 인덱스 디자인 지침을 참조하세요.
열의 데이터 분산을 조사합니다. 자주 장기 실행 쿼리는 고유 값이 거의 없는 열을 인덱싱하거나 이러한 열에서 조인을 수행하여 발생합니다. 이는 데이터 및 쿼리의 근본적인 문제이며 일반적으로 이 상황을 식별하지 않고는 해결할 수 없습니다. 예를 들어 성에서 사전순으로 정렬된 실제 전화 디렉터리가 도시 내 모든 사람이 Smith 또는 Jones인 경우 신속하게 사람을 찾을 수 없습니다. 데이터 배포에 대한 자세한 내용은 통계를 참조 하세요.
잘 정의된 하위 집합이 있는 열(예: 스파스 열, 주로 NULL 값이 있는 열, 값 범주가 있는 열, 고유한 값 범위의 열)에서 필터링된 인덱스를 사용하는 것이 좋습니다. 잘 디자인된 필터링된 인덱스는 쿼리 성능을 향상시키고, 인덱스 기본 테넌트 비용을 줄이고, 스토리지 비용을 줄일 수 있습니다.
인덱스에 여러 열이 포함된 경우 열의 순서를 고려합니다. WHERE 절에서 같음(), 보다 큼(=), 보다 작음(><) 또는 BETWEEN 검색 조건 또는 조인에 참여하는 열이 먼저 배치되어야 합니다. 추가 열은 고유성 수준에 따라 정렬되어야 합니다. 즉, 가장 고유한 열부터 가장 고유하지 않은 열까지 순서를 지정해야 합니다.

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

인덱스 특성

인덱스가 쿼리에 적합한지 확인한 후 상황에 가장 적합한 인덱스 유형을 선택할 수 있습니다. 인덱스 특성에는 다음이 포함됩니다.

클러스터형 및 비클러스터형
고유 및 비유니크
단일 열과 다중 열
인덱스의 열에 대한 오름차순 또는 내림차순
비클러스터형 인덱스에 대해 전체 테이블과 필터링됨
Columnstore와 rowstore 비교
메모리 최적화 테이블에 대한 해시 및 비클러스터형

FILLFACTOR 같은 옵션을 설정하면 인덱스의 초기 스토리지 특성을 사용자 지정하여 성능이나 유지 관리를 최적화할 수도 있습니다. 또한 파일 그룹 또는 파티션 구성표를 사용하여 성능을 최적화하여 인덱스 스토리지 위치를 확인할 수 있습니다.

파일 그룹 또는 파티션 구성표의 인덱스 배치

인덱스 디자인 전략을 개발할 때 데이터베이스와 연결된 파일 그룹에 인덱스의 배치를 고려해야 합니다. 파일 그룹 또는 파티션 구성표를 신중하게 선택하면 쿼리 성능이 향상될 수 있습니다.

기본적으로 인덱스는 인덱스가 만들어지는 기본 테이블과 동일한 파일 그룹에 저장됩니다. 분할되지 않은 클러스터형 인덱스와 기본 테이블은 항상 동일한 파일 그룹에 상주합니다. 그러나 다음을 수행할 수 있습니다.

기본 테이블 또는 클러스터형 인덱스의 파일 그룹이 아닌 파일 그룹에 비클러스터형 인덱스를 만듭니다.
여러 파일 그룹에 걸쳐 클러스터형 및 비클러스터형 인덱스를 분할합니다.
클러스터형 인덱스를 삭제한 후 새 파일 그룹이나 파티션 구성표를 DROP INDEX 문의 MOVE TO 절에 지정하거나 CREATE INDEX 문에 DROP_EXISTING 절을 사용하여 한 파일 그룹에서 다른 파일 그룹으로 테이블을 이동합니다.

다른 파일 그룹에 비클러스터형 인덱스를 만들어 파일 그룹이 자체 컨트롤러와 함께 다른 실제 드라이브를 사용하는 경우 성능 향상을 달성할 수 있습니다. 그러면 데이터와 인덱스 정보를 여러 개의 디스크 헤드로 병렬로 읽을 수 있습니다. 예를 들어 파일 그룹과 파일 그룹에서 f1Index_A 둘 다 동일한 쿼리에서 사용되는 경우 Table_A 두 파일 그룹이 f2 경합 없이 완전히 사용되므로 성능 향상을 달성할 수 있습니다. 그러나 쿼리에서 검사되지만 Index_A 참조되지 않는 경우 Table_A 파일 그룹 f1 만 사용됩니다. 성능이 향상되지 않습니다.

어떤 유형의 액세스가 발생하는지 예측할 수 없기 때문에 테이블과 인덱스를 모든 파일 그룹에 분산하는 것이 더 나은 결정일 수 있습니다. 이렇게 하면 모든 데이터와 인덱스가 모든 디스크에 동일하게 분산되기 때문에 어떤 방식으로 데이터에 액세스하든지 모든 디스크에 액세스할 수 있습니다. 이는 시스템 관리자에게 더욱 간단한 방법이기도 합니다.

여러 파일 그룹에 걸친 파티션

여러 파일 그룹에 걸쳐 디스크 기반 클러스터형 및 비클러스터형 인덱스를 분할하는 것을 고려할 수도 있습니다. 분할된 인덱스는 파티션 함수를 기준으로 가로 또는 행으로 분할됩니다. 파티션 함수는 분할 열이라고 하는 특정 열의 값을 기반으로 각 행을 파티션 집합에 매핑하는 방법을 정의합니다. 파티션 구성표는 파티션과 파일 그룹 간의 매핑을 지정합니다.

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

큰 인덱스를 보다 쉽게 관리할 수 있는 확장 가능한 시스템을 제공합니다. 예를 들어 OLTP 시스템에서 파티션을 인식하며 큰 인덱스를 처리하는 애플리케이션을 구현할 수 있습니다.
쿼리를 더 빠르고 효율적으로 실행합니다. 쿼리가 인덱스의 여러 파티션에 액세스하는 경우 쿼리 최적화 프로그램은 개별 파티션을 동시에 처리하고 쿼리의 영향을 받지 않는 파티션을 제외할 수 있습니다.

자세한 내용은 Partitioned Tables and Indexes을 참조하세요.

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

인덱스를 정의할 때 인덱스 키 열의 데이터를 오름차순으로 저장할지 또는 내림차순으로 저장할지 고려합니다. 오름차순은 기본값이며 이전 버전의 데이터베이스 엔진 호환성을 기본. CREATE INDEX, CREATE TABLE 및 ALTER TABLE 문의 구문은 인덱스 및 제약 조건의 개별 열에서 키워드(keyword) ASC(오름차순) 및 DESC(내림차순)를 지원합니다.

인덱스의 키 값 저장 순서를 지정하는 기능은 테이블을 참조하는 쿼리에 해당 인덱스에서 키 열의 다른 방향을 지정하는 ORDER BY 절이 포함된 경우에 유용합니다. 이러한 경우 쿼리 계획에서 SORT 연산자를 사용할 필요가 없어지므로 쿼리의 효율성이 향상됩니다. 예를 들어 Adventure Works Cycles 구매 부서의 구매자는 공급업체에서 구매한 제품의 품질을 평가해야 합니다. 구매자는 대부분 해당 공급업체에서 보낸 제품 중 거부율이 높은 제품을 찾으려고 합니다.

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

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

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

Diagram of an execution plan for this query showing that the query optimizer used a SORT operator to return the result set in the order specified by the ORDER BY clause.

쿼리의 ORDER BY 절에 있는 것과 일치하는 키 열로 디스크 기반 rowstore 인덱스가 생성되는 경우 쿼리 계획에서 SORT 연산자를 제거하여 쿼리 계획의 효율성을 향상시킬 수 있습니다.

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

쿼리가 다시 실행된 후 다음 실행 계획은 SORT 연산자가 제거되고 새로 만든 비클러스터형 인덱스가 사용되었음을 보여 줍니다.

Diagram of an execution plan showing that the SORT operator has been eliminated and the newly created nonclustered index is used.

데이터베이스 엔진 어느 방향으로든 동일하게 효율적으로 이동할 수 있습니다. ORDER BY 절에 있는 열의 정렬 방향이 반대로 바뀌는 쿼리에 대해 정의된 (RejectedQty DESC, ProductID ASC) 인덱스를 계속 사용할 수 있습니다. 예를 들어 ORDER BY 절 ORDER BY RejectedQty ASC, ProductID DESC 이 있는 쿼리는 인덱스로 사용할 수 있습니다.

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

AdventureWorks 샘플 데이터베이스의 코드 예제와 함께 팔로우하는 경우 다음 Transact-SQL을 사용하여 IX_PurchaseOrderDetail_RejectedQty를 삭제할 수 있습니다.

DROP INDEX IX_PurchaseOrderDetail_RejectedQty
ON Purchasing.PurchaseOrderDetail;
GO

메타데이터

이러한 메타데이터 뷰를 사용하여 인덱스의 특성을 확인합니다. 이러한 보기 중 일부에 추가 아키텍처 정보가 포함됩니다.

참고 항목

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

sys.indexes(Transact-SQL)

sys.index_columns(Transact SQL)

sys.partitions(Transact-SQL)

sys.internal_partitions(Transact-SQL)

sys.dm_db_index_operational_stats(Transact-SQL)

sys.dm_db_index_physical_stats(Transact-SQL)

sys.column_store_segments(Transact-SQL)

sys.column_store_dictionaries(Transact-SQL)

sys.column_store_row_groups(Transact-SQL)

sys.dm_db_column_store_row_group_operational_stats(Transact-SQL)

sys.dm_db_column_store_row_group_physical_stats(Transact-SQL)

sys.dm_column_store_object_pool(Transact-SQL)

sys.dm_db_column_store_row_group_operational_stats(Transact-SQL)

sys.dm_db_xtp_hash_index_stats(Transact-SQL)

sys.dm_db_xtp_index_stats(Transact-SQL)

sys.dm_db_xtp_object_stats(Transact-SQL)

sys.dm_db_xtp_nonclustered_index_stats(Transact-SQL)

sys.dm_db_xtp_table_memory_stats(Transact-SQL)

sys.hash_indexes(Transact-SQL)

sys.memory_optimized_tables_internal_attributes(Transact-SQL)

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

클러스터형 인덱스는 그 키 값에 기반하여 테이블에 데이터 행을 정렬하고 저장합니다. 데이터 행 자체는 한 순서로만 정렬할 수 있으므로 테이블당 하나의 클러스터형 인덱스만 있을 수 있습니다. 몇 가지 예외를 제외하고 모든 테이블에는 다음을 제공하는 열 또는 열에 정의된 클러스터형 인덱스가 있어야 합니다.

자주 사용되는 쿼리에 사용할 수 있습니다.
높은 수준의 고유성을 제공합니다.

참고 항목

PRIMARY KEY 제약 조건을 만들면 열 또는 열에 고유한 인덱스가 자동으로 만들어집니다. 기본적으로 이 인덱스는 클러스터형입니다. 그러나 제약 조건을 만들 때 비클러스터형 인덱스도 지정할 수 있습니다.
범위 쿼리에서 사용할 수 있습니다.

클러스터형 인덱스가 속성으로 UNIQUE 만들어지지 않으면 데이터베이스 엔진 테이블에 4 바이트 고유 열이 자동으로 추가됩니다. 필요한 경우 데이터베이스 엔진 행에 uniqueifier 값을 자동으로 추가하여 각 키를 고유하게 만듭니다. 이 열과 해당 값은 내부적으로 사용되며 사용자가 보거나 액세스할 수 없습니다.

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

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

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

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

데이터 체인의 페이지와 데이터 체인의 행은 클러스터형 인덱스 키의 값에 따라 정렬됩니다. 삽입된 행의 키 값이 기존 행의 순서 순서에 맞는 지점에서 모든 삽입이 이루어집니다.

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

Diagram showing the structure of a clustered index in a single partition.

쿼리 고려 사항

클러스터형 인덱스를 만들기 전에 데이터에 액세스하는 방법을 이해합니다. 다음을 수행하는 쿼리에는 클러스터형 인덱스를 사용하십시오.

, >>=, <및 <=와 같은 BETWEEN연산자를 사용하여 값 범위를 반환합니다.

클러스터형 인덱스를 사용하여 첫 번째 값이 있는 행을 찾은 후에는 후속 인덱싱된 값이 있는 행이 물리적으로 인접하도록 보장됩니다. 예를 들어 쿼리가 판매 주문 번호 범위 간의 레코드를 검색하는 경우 열 SalesOrderNumber 의 클러스터형 인덱스는 시작 판매 주문 번호가 포함된 행을 빠르게 찾은 다음 마지막 판매 주문 번호에 도달할 때까지 테이블의 모든 연속 행을 검색할 수 있습니다.
큰 결과 집합을 반환합니다.
절을 사용합니다 JOIN . 일반적으로 외래 키 열입니다.
사용 ORDER BY 또는 GROUP BY 절입니다.

ORDER BY 또는 GROUP BY 절에 지정된 열의 인덱스는 행이 이미 정렬되어 있으므로 데이터베이스 엔진 데이터를 정렬할 필요가 없습니다. 따라서 쿼리 성능도 향상됩니다.

열 고려 사항

일반적으로 가능한 한 적은 열로 클러스터형 인덱스 키를 정의해야 합니다. 다음 특성 중 하나 이상이 있는 열을 고려합니다.

고유하거나 여러 고유 값을 포함합니다.

예를 들어 직원 ID는 직원을 고유하게 식별합니다. 열의 EmployeeID 클러스터형 인덱스 또는 PRIMARY KEY 제약 조건은 직원 ID 번호를 기반으로 직원 정보를 검색하는 쿼리의 성능을 향상시킵니다. 또는 LastName, FirstName, MiddleName 에 클러스터형 인덱스를 생성할 수 있는데 이는 직원 레코드가 이러한 방법으로 자주 그룹화 및 쿼리되기 때문이며, 이러한 열을 결합하더라도 높은 수준의 고유성을 제공합니다.

팁

다르게 지정하지 않으면 PRIMARY KEY 제약 조건을 만들 때 데이터베이스 엔진 해당 제약 조건을 지원하는 클러스터형 인덱스가 만들어집니다. uniqueidentifier를 사용하여 고유성을 PRIMARY KEY로 적용할 수 있지만 효율적인 클러스터링 키는 아닙니다. uniqueidentifier를 PRIMARY KEY로 사용하는 경우 비클러스터형 인덱스로 만들고 클러스터형 인덱스와 같은 IDENTITY 다른 열을 사용하여 클러스터형 인덱스 만들기를 권장합니다.
순차적으로 액세스

예를 들어 제품 ID는 데이터베이스의 테이블에 AdventureWorks2022 있는 Production.Product 제품을 고유하게 식별합니다. WHERE ProductID BETWEEN 980 and 999와 같이 순차 검색이 지정된 쿼리라면 ProductID에 클러스터형 인덱스를 사용하는 것이 좋습니다. 행이 해당 키 열에 정렬된 순서로 저장되기 때문입니다.
로 정의됨 IDENTITY
테이블에서 검색된 데이터를 정렬하는 데 자주 사용됩니다.

열을 쿼리할 때마다 정렬 작업의 비용을 절약하기 위해 해당 열에서 테이블을 클러스터 (물리적으로 정렬)하는 것이 좋습니다.

클러스터형 인덱스는 다음 특성에 적합하지 않습니다.

자주 변경되는 열

따라서 데이터베이스 엔진 행의 데이터 값을 실제 순서로 유지해야 하므로 전체 행이 이동하게 됩니다. 다음은 일반적으로 데이터가 불안정한 대량 트랜잭션 처리 시스템에서 고려해야 할 사항입니다.
와이드 키

와이드 키는 여러 열 또는 여러 개의 큰 열의 복합 키입니다. 클러스터형 인덱스의 키 값은 모든 비클러스터형 인덱스에서 조회 키로 사용됩니다. 비클러스터형 인덱스 항목에는 클러스터링 키와 함께 해당 비클러스터형 인덱스에 대해 정의된 키 열도 포함되기 때문에 동일한 테이블에 정의된 비클러스터형 인덱스가 훨씬 큽니다.

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

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

책에서 인덱스를 사용하는 방식과 마찬가지로 쿼리 최적화 프로그램은 비클러스터형 인덱스를 검색하여 테이블에서 데이터 값의 위치를 찾은 다음 해당 위치에서 직접 데이터를 검색하여 데이터 값을 검색합니다. 이렇게 하면 인덱스에 쿼리에서 검색되는 데이터 값 테이블의 정확한 위치를 설명하는 항목이 포함되어 있으므로 비클러스터형 인덱스가 정확한 일치 쿼리에 가장 적합합니다. 예를 들어 특정 관리자에게 보고하는 모든 직원의 테이블을 쿼리 HumanResources.Employee 하기 위해 쿼리 최적화 프로그램은 비클러스터형 인덱스를 사용할 수 있습니다. 이 인덱 IX_Employee_ManagerID스는 ManagerID 키 열로 사용됩니다. 쿼리 최적화 프로그램은 인덱스 내 지정된 ManagerID항목과 일치하는 모든 항목을 빠르게 찾을 수 있습니다. 각 인덱스 진입점은 테이블의 정확한 페이지와 행 또는 해당 데이터를 찾을 수 있는 클러스터형 인덱스입니다. 쿼리 최적화 프로그램은 인덱스에서 모든 항목을 찾은 후 정확한 페이지와 행으로 직접 이동하여 데이터를 검색할 수 있습니다.

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

디스크 기반 rowstore 비클러스터형 인덱스는 다음의 두 가지 주요 차이점을 제외하고 클러스터형 인덱스와 동일한 B-트리 구조를 갖습니다.

기본 테이블의 데이터 행은 비클러스터형 키에 따라 정렬되고 저장되지 않습니다.
비클러스터형 인덱스의 리프 수준은 데이터 페이지 대신 인덱스 페이지로 구성됩니다. 비클러스터형 인덱스의 리프 수준에 있는 인덱스 페이지에는 키 열과 포함된 열이 포함되어 있습니다.

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

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

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

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

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

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

클러스터형 인덱스	비클러스터형 인덱스 정의	행 로케이터를 사용하는 비클러스터형 인덱스 정의	설명
키 열이 있는 고유 클러스터형 인덱스(`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`)	비클러스터형 인덱스는 고유하므로 행 로케이터가 포함된 열에 추가됩니다.

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

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

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

Diagram showing the structure of a nonclustered index in a single partition.

데이터베이스 고려 사항

비클러스터형 인덱스를 디자인할 때 데이터베이스의 특성을 고려합니다.

업데이트 요구 사항이 낮지만 많은 양의 데이터가 있는 데이터베이스 또는 테이블은 많은 비클러스터형 인덱스를 통해 쿼리 성능을 향상시킬 수 있습니다. 전체 테이블 비클러스터형 인덱스에 비해 쿼리 성능을 향상시키고, 인덱스 스토리지 비용을 절감하고, 인덱스 기본 테넌트 비용을 줄이기 위해 잘 정의된 데이터 하위 집합에 대해 필터링된 인덱스를 만드는 것이 좋습니다.

의사 결정 지원 시스템 애플리케이션 및 주로 읽기 전용 데이터를 포함하는 데이터베이스는 많은 비클러스터형 인덱스의 이점을 얻을 수 있습니다. 쿼리 최적화 프로그램은 가장 빠른 액세스 방법을 결정하기 위해 선택할 수 있는 인덱스가 더 많으며 데이터베이스의 낮은 업데이트 특성은 인덱스 기본테넌스가 성능을 저해하지 않음을 의미합니다.
OLTP(온라인 트랜잭션 처리) 애플리케이션 및 많이 업데이트된 테이블을 포함하는 데이터베이스는 과잉 인덱싱을 피해야 합니다. 또한 인덱스는 가능한 적은 수의 열을 포함하는 좁은 인덱스여야 합니다.

테이블의 많은 수의 인덱스는 테이블의 데이터가 변경될 때 모든 인덱스를 적절하게 조정해야 하므로 INSERT, UPDATE, DELETE 및 MERGE 문의 성능에 영향을 줍니다.

쿼리 고려 사항

비클러스터형 인덱스를 만들기 전에 데이터에 액세스하는 방법을 이해해야 합니다. 다음 특성이 있는 쿼리에 비클러스터형 인덱스를 사용하는 것이 좋습니다.

사용 JOIN 또는 GROUP BY 절입니다.

조인 및 그룹화 작업과 관련된 열에 여러 개의 비클러스터형 인덱스를 만들고 외래 키 열에 클러스터형 인덱스를 만듭니다.
큰 결과 집합을 반환하지 않는 쿼리입니다.

큰 테이블에서 잘 정의된 행 하위 집합을 반환하는 쿼리를 포함하는 필터링된 인덱스를 만듭니다.

팁

일반적으로 CREATE INDEX 문의 WHERE 절은 처리 중인 쿼리의 WHERE 절과 일치합니다.
정확히 일치하는 항목을 반환하는 WHERE 절과 같은 쿼리의 검색 조건에 자주 관련된 열을 포함합니다.

팁

새 인덱스를 추가할 때 비용 및 이점을 고려합니다. 추가 쿼리 요구 사항을 기존 인덱스에 통합하는 것이 좋을 수 있습니다. 예를 들어 기존 인덱스가 여러 개의 중요 쿼리의 포함을 허용할 경우에는, 중요 쿼리당 정확히 하나씩 포함 인덱스를 만들기 보다는 기존 인덱스에 한두 개의 추가 리프 수준 열을 추가해 보세요.

열 고려 사항

다음 특성 중 하나 이상이 있는 열을 고려합니다.

쿼리에 사용되는 열 모두 포함

인덱스에 쿼리의 모든 열이 포함된 경우 성능이 향상됩니다. 쿼리 최적화 프로그램은 인덱스 내의 모든 열 값을 찾을 수 있습니다. 테이블 또는 클러스터형 인덱스 데이터에 액세스하지 않아 디스크 I/O 작업이 줄어듭니다. 넓은 인덱스 키를 만드는 대신 포함된 열이 있는 인덱스를 사용하여 포함 인덱스를 추가합니다.

테이블에 클러스터형 인덱스가 있으면 클러스터형 인덱스에 정의된 열이 자동으로 테이블의 각 비클러스터형 인덱스에 추가됩니다. 비클러스터형 인덱스 정의에 클러스터형 인덱스 열을 지정하지 않고도 적용된 쿼리를 생성할 수 있습니다. 예를 들어 테이블에 열에 클러스터형 인덱스가 있고 열C에 고유하지 않은 비클러스터형 인덱스가 B 있고 A 키 값 열BA이 있는 경우 및 C 자세한 내용은 비클러스터형 인덱스 아키텍처를 참조 하세요.
클러스터형 인덱스가 다른 열에 사용되는 경우 성과 이름의 조합 같은 고유 값 많이 포함

1과 0과 같이 고유 값이 거의 없는 경우 테이블 검색이 일반적으로 더 효율적이기 때문에 대부분의 쿼리는 인덱스를 사용하지 않습니다. 이 데이터 형식의 경우 몇 개의 행에서만 발생하는 고유 값에 필터링된 인덱스를 만드는 것이 좋습니다. 예를 들어 대부분의 값이 0이면 쿼리 최적화 프로그램에서 1이 포함된 데이터 행에 대해 필터링된 인덱스를 사용할 수 있습니다.

포함된 열을 사용하여 비클러스터형 인덱스 확장

비클러스터형 인덱스의 리프 수준에 키가 아닌 열을 추가하여 비클러스터형 인덱스의 기능을 확장할 수 있습니다. 키가 아닌 열을 포함하면 더 많은 쿼리를 포함하는 비클러스터형 인덱스를 만들 수 있습니다. 키가 아닌 열에는 다음과 같은 이점이 있기 때문입니다.

인덱스 키 열로 허용되지 않는 데이터 형식일 수 있습니다.
인덱스 키 열 또는 인덱스 키 크기의 수를 계산할 때 데이터베이스 엔진 고려되지 않습니다.

키가 아닌 열이 포함된 인덱스는 쿼리의 모든 열이 키 또는 키가 아닌 열로 인덱스에 포함될 때 쿼리 성능을 크게 향상시킬 수 있습니다. 쿼리 최적화 프로그램에서 인덱스 내의 모든 열 값을 찾을 수 있으므로 성능이 향상됩니다. 테이블 또는 클러스터형 인덱스 데이터에 액세스하지 않아 디스크 I/O 작업이 줄어듭니다.

참고 항목

인덱스에 쿼리에서 참조하는 모든 열이 포함된 경우 일반적으로 쿼리를 포함하는 열이라고 합니다.

키 열은 인덱스의 모든 수준에 저장되지만 키가 아닌 열은 리프 수준에서만 저장됩니다.

포함된 열을 사용하여 크기 제한 방지

비클러스터형 인덱스에 키가 아닌 열을 포함시키면 현재 인덱스 크기 제한인 최대 16개의 키 열 및 최대 900바이트의 인덱스 키 크기가 초과되는 것을 피할 수 있습니다. 데이터베이스 엔진 인덱스 키 열 또는 인덱스 키 크기 수를 계산할 때 키가 아닌 열을 고려하지 않습니다.

예를 들어 테이블에서 다음 열을 인덱싱하려는 경우를 가정합니다.Document

Title NVARCHAR(50)
Revision NCHAR(5)
FileName NVARCHAR(400)

nchar 및 nvarchar 데이터 형식은 각 문자에 대해 2바이트가 필요하므로 위의 3열이 포함된 인덱스는 900바이트의 크기 제한을 10바이트 초과하게 됩니다(455 * 2). 문의 절을 INCLUDECREATE INDEX 사용하여 인덱스 키를 (Title, Revision)로 정의하고 FileName 키가 아닌 열로 정의할 수 있습니다. 이러한 방식으로 인덱스 키 크기는 110바이트(55 * 2)이며 인덱스에는 필요한 모든 열이 계속 포함됩니다. 다음 문은 이러한 인덱스(index)를 만듭니다.

CREATE INDEX IX_Document_Title
ON Production.Document (Title, Revision)
INCLUDE (FileName);
GO

코드 예제와 함께 팔로우하는 경우 이 Transact-SQL 문을 사용하여 관련 인덱스를 삭제할 수 있습니다.

DROP INDEX IX_Document_Title
ON Production.Document;
GO

포괄 열이 있는 인덱스 지침

포괄 열이 있는 비클러스터형 인덱스를 디자인하는 경우 다음 지침을 고려합니다.

키가 아닌 열은 CREATE INDEX 문의 INCLUDE 절에서 정의됩니다.
키가 아닌 열은 테이블 또는 인덱싱된 뷰의 비클러스터형 인덱스에만 정의할 수 있습니다.
text, ntext및 image를 제외한 모든 데이터 형식을 사용할 수 있습니다.
결정적이면서 정확하거나 정확하지 않은 계산 열은 포괄 열이 될 수 있습니다. 자세한 내용은 Indexes on Computed Columns을 참조하세요.
키 열과 마찬가지로 이미지, ntext 및 텍스트 데이터 형식에서 파생된 계산 열은 계산 열 데이터 형식이 키가 아닌 인덱스 열로 허용되는 한 키가 아닌 열(포함)일 수 있습니다.
INCLUDE 목록과 키 열 목록에서 열 이름을 지정할 수 없습니다.
INCLUDE 목록에서 열 이름을 반복할 수 없습니다.

열 크기 지침

하나 이상의 키 열을 정의해야 합니다. 키가 아닌 열의 최대 수는 1023개 열입니다. 테이블 열의 최대 개수에서 1을 뺀 값입니다.
키가 아닌 인덱스 키 열은 최대 16개의 키 열과 총 인덱스 키 크기 900바이트의 기존 인덱스 크기 제한을 따라야 합니다.
키가 아닌 모든 열의 총 크기는 INCLUDE 절에 지정된 열의 크기로만 제한됩니다. 예를 들어 varchar(max) 열은 2GB로 제한됩니다.

열 수정 지침

포함된 열로 정의된 테이블 열을 수정하는 경우 다음 제한 사항이 적용됩니다.

인덱스를 먼저 삭제하지 않으면 키가 아닌 열을 테이블에서 삭제할 수 없습니다.
다음을 수행하는 경우를 제외하고 키가 아닌 열은 변경할 수 없습니다.
- 열의 null 허용도를 NOT NULL에서 NULL로 변경합니다.
- varchar, nvarchar 또는 varbinary 열의 길이를 늘입니다.
  
  참고 항목
  
  이러한 열 수정 제한은 인덱스 키 열에도 적용됩니다.

디자인 권장 사항

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

예를 들어 다음 쿼리를 포함하도록 인덱스 디자인하려는 경우를 가정합니다.

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

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

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

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

인덱스가 쿼리를 포함하는지 확인하려면 인덱스 생성 후 예상 실행 계획을 표시합니다.

실행 계획에 IX_Address_PostalCode 인덱스의 SELECT 연산자와 Index Seek 연산자만 표시되면 쿼리는 인덱스로 “적용”됩니다.

다음 문을 사용하여 인덱스를 삭제할 수 있습니다.

DROP INDEX IX_Address_PostalCode
ON Person.Address;
GO

성능 고려 사항

불필요한 열을 추가하지 않습니다. 키 또는 키가 아닌 인덱스 열을 너무 많이 추가하면 다음과 같은 성능에 영향을 미칠 수 있습니다.

페이지에 맞는 인덱스 행 수가 줄어듭니다. 이로 인해 I/O가 증가하고 캐시 효율성이 저하될 수 있습니다.
인덱스 저장을 위해 더 많은 디스크 공간이 필요합니다. 특히 varchar(max), nvarchar(max), varbinary(max) 또는 xml 데이터 형식을 키가 아닌 인덱스 열로 추가하면 디스크 공간 요구 사항이 크게 증가할 수 있습니다. 열 값이 인덱스 리프 수준으로 복사되기 때문입니다. 따라서 인덱스와 기본 테이블 모두에 상주합니다.
인덱스 유지 관리를 위해 기본 테이블 또는 인덱싱된 뷰에 대해 수정, 삽입, 업데이트 또는 삭제하는 시간이 늘어납니다.

쿼리 성능 향상이 데이터 수정 중 및 추가 디스크 공간 요구 사항의 성능에 미치는 영향보다 더 큰지 확인해야 합니다.

고유 인덱스 디자인 지침

고유 인덱스는 인덱스 키에 중복 값이 없으므로 테이블의 모든 행이 어떤 식으로든 고유합니다. 고유 인덱스 지정은 고유성이 데이터 자체의 특성인 경우에만 의미가 있습니다. 예를 들어 기본 키가 NationalIDNumber 인 경우 HumanResources.Employee 테이블의 EmployeeID열 값이 고유하도록 하려면 NationalIDNumber 열에 대해 UNIQUE 제약 조건을 만듭니다. 사용자가 두 명 이상의 직원에 대해 해당 열에 동일한 값을 입력하려고 하면 오류 메시지가 표시되고 중복 값이 입력되지 않습니다.

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

클러스터형 인덱스와 비클러스터형 인덱스는 모두 고유할 수 있습니다. 열의 데이터가 고유한 경우 같은 테이블에서 하나의 고유 클러스터형 인덱스와 여러 개의 고유 비클러스터형 인덱스를 만들 수 있습니다.

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

정의된 열의 데이터 무결성이 보장됩니다.
쿼리 최적화 프로그램에 유용한 추가 정보가 제공됩니다.

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

고려 사항

데이터에 중복 키 값이 있는 경우 고유 인덱스, UNIQUE 제약 조건 또는 PRIMARY KEY 제약 조건을 만들 수 없습니다.
데이터가 고유하고 고유성을 적용하려는 경우 동일한 열 조합에 고유하지 않은 인덱스 대신 고유 인덱스를 만들면 쿼리 최적화 프로그램에서 보다 효율적인 실행 계획을 생성할 수 있는 추가 정보가 제공됩니다. 이 경우 고유 인덱스(UNIQUE 제약 조건을 만드는 것이 좋습니다)를 만드는 것이 좋습니다.
고유 비클러스터형 인덱스에는 키가 아닌 열이 포함될 수 있습니다. 자세한 내용은 포함된 열이 있는 인덱스를 참조 하세요.

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

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

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

향상된 쿼리 성능 및 계획 품질

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

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

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

필터링된 인덱스는 쿼리가 SELECT 문에서 참조하는 데이터의 잘 정의된 하위 집합이 열에 포함되는 경우 유용합니다. 예를 들면 다음과 같습니다.

NULL이 아닌 값이 몇 가지 포함된 스파스 열입니다.
데이터 범주를 포함하는 다른 유형의 열입니다.
달러 금액, 시간 및 날짜와 같은 값 범위가 포함된 열입니다.
열 값에 대해 간단한 비교 논리로 정의되는 테이블 파티션

필터링된 인덱스에 대해 줄어든 유지 관리 비용은 인덱스의 행 수가 전체 테이블 인덱스에 비해 적을 때 가장 분명하게 드러납니다. 필터링된 인덱스에 테이블의 열이 대부분 포함되어 있을 경우 전체 테이블 인덱스보다 유지 관리 비용이 더 들 수 있습니다. 이 경우 필터링된 인덱스 대신 전체 테이블 인덱스 대신 사용해야 합니다.

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

디자인 고려 사항

효과적인 필터링된 인덱스를 디자인하려면 애플리케이션이 사용하는 쿼리와 이 쿼리가 데이터의 하위 집합과 어떻게 연결되는지를 이해하는 것이 중요합니다. 잘 정의된 하위 집합이 있는 데이터의 몇 가지 예로는 대개 NULL 값이 있는 열, 범주가 다른 값이 있는 열 및 특정 범위의 값이 있는 열이 있습니다. 다음 디자인 고려 사항은 필터링된 인덱스가 전체 테이블 인덱스에 비해 이점을 제공할 수 있는 경우에 대한 다양한 시나리오를 제공합니다.

팁

비클러스터형 columnstore 인덱 스 정의는 필터링된 조건 사용을 지원합니다. OLTP 테이블에 columnstore 인덱스 추가의 성능 영향을 최소화하려면 필터링된 조건을 사용하여 운영 워크로드의 콜드 데이터에만 비클러스터형 columnstore 인덱스 만들기

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

열에 쿼리에 대한 몇 가지 관련 값만 있는 경우 값의 하위 집합에 필터링된 인덱스를 만들 수 있습니다. 예를 들어 열의 값이 대부분 NULL이고 쿼리가 NULL이 아닌 값에서만 선택하는 경우 NULL이 아닌 데이터 행에 대해 필터링된 인덱스를 만들 수 있습니다. 결과 인덱스는 더 작고 기본 동일한 키 열에 정의된 전체 테이블 비클러스터형 인덱스보다 비용이 적게 듭니다.

예를 들어 AdventureWorks 샘플 데이터베이스에는 2679개의 행이 있는 Production.BillOfMaterials 테이블이 있습니다. 열에는 EndDate NULL이 아닌 값이 포함된 199개의 행만 있고 나머지 2480개 행에는 NULL이 포함됩니다. 다음 필터링된 인덱스는 인덱스에 정의된 열을 반환하고 NULL 값이 아닌 행만 선택하는 쿼리를 EndDate다룹니다.

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

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

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

필터링된 인덱스를 만드는 방법 및 필터링된 인덱스 조건자 식을 정의하는 방법에 대한 자세한 내용은 필터링된 인덱스 만들기를 참조 하세요.

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

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

예를 들어 Production.Product 테이블에 나열된 제품은 각각 ProductSubcategoryID에 지정된 다음 제품 범주 Bikes, Components, Clothing 또는 Accessories 같은 제품 범주와 연결됩니다. 이러한 범주는 테이블의 열 값 Production.Product 과 밀접한 상관 관계가 없으므로 이질적입니다. 예를 들어 열Color, , ReorderPointListPrice, WeightClass및 Style 각 제품 범주에 대한 고유한 특성이 있습니다. 27에서 36 사이의 하위 범주가 포함된 액세서리에 대한 쿼리가 자주 있다고 가정합니다. 다음 예와 같이 Accessories 하위 범주에 필터링된 인덱스를 만들어 Accessories에 대한 쿼리의 성능을 향상시킬 수 있습니다.

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

쿼리 결과가 인덱 FIProductAccessories 스 및 쿼리 계획에 기본 테이블 조회를 포함하지 않으므로 필터링된 인덱스가 다음 쿼리를 포함합니다. 예를 들어 쿼리 조건자 식 ProductSubcategoryID = 33 은 필터링된 인덱스 조건 ProductSubcategoryID >= 27 자의 하위 집합이며 ProductSubcategoryID <= 36쿼리 ProductSubcategoryID 조건자의 열과 ListPrice 쿼리 조건자의 열은 모두 인덱스의 키 열이며 이름은 포함된 열로 인덱스의 리프 수준에 저장됩니다.

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

키 열

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

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

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

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

쿼리 조건자가 필터링된 인덱스 식과 동일하지 않은 비교에서 열을 사용하는 경우 필터링된 인덱스 식의 열은 필터링된 인덱스 정의에 키 또는 포함된 열이어야 합니다. 예를 들어 FIBillOfMaterialsWithEndDate 필터링된 인덱스에서 행의 하위 집합을 선택하므로 다음 쿼리에 유효합니다. 그러나 필터링된 인덱스 식과 동일하지 않은 비교EndDate > '20040101'에 사용되므로 다음 쿼리 EndDate 는 다루지 않습니다. 쿼리 프로세서는 값을 조회하지 않고는 이 쿼리를 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;

테이블의 클러스터형 인덱스 키는 필터링된 인덱스 정의에 키 또는 포함된 열일 필요가 없습니다. 클러스터형 인덱스 키는 필터링된 인덱스를 포함하여 모든 비클러스터형 인덱스에 자동으로 포함됩니다.

FIBillOfMaterialsWithEndDate 및 FIProductAccessories 인덱스를 삭제하려면 다음 문을 실행합니다.

DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
DROP INDEX FIProductAccessories
    ON Production.Product;
GO

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

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

다음 예제에서는 다양한 데이터 형식의 테이블을 만듭니다.

CREATE TABLE dbo.TestTable (a int, b varbinary(4));
GO

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

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

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

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

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

다음 문을 실행하여 이 예제에서 만든 개체를 삭제합니다.

DROP TABLE TestTable;
GO

Columnstore 인덱스 아키텍처

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

버전 정보를 확인하고 새로운 것을 알아보려면 Columnstore 인덱스 - 새로운 기능입니다.

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

데이터 스토리지는 columnstore 및 rowstore 압축을 사용함

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

Diagram of a clustered columnstore index.

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

columnstore 인덱스가 대부분의 데이터를 columnstore 형식으로 물리적으로 저장합니다. columnstore 형식에서 데이터는 열로 압축되고 압축되지 않습니다. 쿼리에서 요청하지 않은 각 행의 다른 값을 압축 해제할 필요가 없습니다. 이렇게 하면 큰 테이블의 전체 열을 빠르게 검색할 수 있습니다.
rowstore는 행과 열이 있는 테이블로 논리적으로 구성된 다음 행 단위 데이터 형식으로 물리적으로 저장되는 데이터입니다. 이는 힙 또는 클러스터형 B+ 트리 인덱스와 같은 관계형 테이블 데이터를 저장하는 기존 방법이었습니다.

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

columnstore 용어 및 개념에 대한 자세한 내용은 Columnstore 인덱스: 개요를 참조하세요.

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

columnstore 인덱스는 행을 관리 가능한 단위로 그룹화합니다. 이러한 각 단위를 행 그룹이라고 합니다. 최상의 성능을 위해 행 그룹의 행 수는 압축 속도를 향상시킬 만큼 충분히 크고 메모리 내 작업의 이점을 얻을 수 있을 만큼 작습니다.

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

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

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

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

팁

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

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

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

Diagram of a clustered columnstore column segment.

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

columnstore 용어 및 개념에 대한 자세한 내용은 Columnstore 인덱스: 개요를 참조하세요.

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

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

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

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

또한 deltastore는 삭제된 것으로 표시되었지만 columnstore에서 실제로 삭제되지 않은 삭제된 행에 대한 ID 목록도 저장합니다.

columnstore 용어 및 개념에 대한 자세한 내용은 Columnstore 인덱스: 개요를 참조하세요.

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

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

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

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

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

columnstore 용어 및 개념에 대한 자세한 내용은 Columnstore 인덱스: 개요를 참조하세요.

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

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

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

팁

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

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

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

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

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

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

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

성능 고려 사항

비클러스터형 columnstore 인덱스 정의는 필터링된 조건 사용을 지원합니다. OLTP 테이블에 columnstore 인덱스 추가의 성능 영향을 최소화하려면 필터링된 조건을 사용하여 운영 워크로드의 콜드 데이터에만 비클러스터형 columnstore 인덱스 만들기
메모리 내 테이블에는 columnstore 인덱스가 한 개만 있을 수 있습니다. 테이블을 만들 때 만들거나 나중에 ALTER TABLE(Transact-SQL)을 사용하여 추가할 수 있습니다. SQL Server 2016(13.x) 이전에는 디스크 기반 테이블만 columnstore 인덱스가 있을 수 있습니다.

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

디자인 지침

Rowstore 테이블에는 업데이트할 수 있는 비클러스터형 columnstore 인덱스 한 개가 있을 수 있습니다. SQL Server 2014(12.x) 이전에는 비클러스터형 columnstore 인덱스가 읽기 전용이었습니다.

자세한 내용은 Columnstore 인덱스 - 디자인 지침을 참조하세요.

해시 인덱스 디자인 지침

행을 함께 연결하는 인덱스이므로 모든 메모리 최적화 테이블에는 하나 이상의 인덱스가 있어야 합니다. 메모리 최적화 테이블에서는 모든 인덱스도 메모리 최적화됩니다. 해시 인덱스는 메모리 최적화 테이블에 사용할 수 있는 인덱스 유형 중 하나입니다. 자세한 내용은 메모리 최적화 테이블에 대한 인덱스를 참조하세요.

적용 대상: SQL Server, Azure SQL Database 및 Azure SQL Managed Instance.

해시 인덱스 아키텍처

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

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

인덱스 정의 시 버킷 수를 지정해야 합니다.

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

팁

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

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

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

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

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

Diagram showing interaction between hash index and buckets.

해시 인덱스 버킷 수 구성

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

대부분의 경우 버킷 수는 인덱스 키에 있는 고유한 값 수의 1~2배 사이여야 합니다.
특정 인덱스 키에 얼마나 많은 값이 지정될지 항상 예측할 수 있는 것은 아닙니다. 값이 실제 키 값 수의 10배 이내이고 과대 평가가 일반적으로 과소 평가보다 더 나은 경우 BUCKET_COUNT 성능은 여전히 좋습니다.

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

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

버킷이 너무 많 을 경우 다음과 같은 단점이 있습니다.

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

참고 항목

버킷을 더 추가해도 중복 값을 공유하는 항목의 연결이 줄어들지 않습니다. 값 중복 속도는 버킷 수를 계산하기 위함이 아니라 해시가 적절한 인덱스 유형인지 결정하는 데 사용됩니다.

성능 고려 사항

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

절의 조건자가 해시 인덱스 키의 WHERE 각 열에 대한 정확한 값을 지정하는 경우 우수합니다. 해시 인덱스는 같지 않음 조건자가 지정된 검사에 되돌리기.
절의 조건자가 인덱스 키에서 WHERE 값 범위를 찾는 경우 부실합니다.
절의 조건자가 WHERE 두 열 해시 인덱스 키의 첫 번째 열에 대해 하나의 특정 값을 규정하지만 키의 다른 열에 대한 값을 지정하지 않는 경우 부실합니다.

팁

조건자는 해시 인덱스 키의 모든 열을 포함해야 합니다. 해시 인덱스에는 인덱스를 검색할 키(해시)가 필요합니다.
인덱스 키가 두 열로 구성되고 절이 WHERE 첫 번째 열만 제공하는 경우 데이터베이스 엔진 해시할 완전한 키가 없습니다. 이 때문에 인덱스 검색 쿼리 계획이 만들어집니다.

해시 인덱스가 사용되고 고유한 인덱스 키의 수가 행 수보다 100배 이상 많은 경우 행 체인이 커지지 않도록 버킷 수를 늘리거나 비클러스터형 인덱스를 사용하는 방법을 고려해 봅니다.

선언 고려 사항

해시 인덱스는 메모리 최적화 테이블에만 존재할 수 있습니다. 디스크 기반 테이블에는 존재할 수 없습니다.

해시 인덱스를 다음으로 선언할 수 있습니다.

UNIQUE이거나 기본값이 고유하지 않은 것으로 설정할 수 있습니다.
기본값인 NONCLUSTERED입니다.

다음은 CREATE TABLE 문 외부에서 해시 인덱스를 만드는 구문의 예입니다.

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

행 버전 및 가비지 수집

메모리 최적화 테이블에서 행의 영향을 받는 UPDATE경우 테이블은 업데이트된 버전의 행을 만듭니다. 업데이트 트랜잭션 중에 다른 세션에서 이전 버전의 행을 읽을 수 있으므로 행 잠금과 관련된 성능 저하를 방지할 수 있습니다.

또한 해시 인덱스는 업데이트를 수용하기 위해 항목의 버전이 다를 수 있습니다.

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

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

비클러스터형 인덱스는 메모리 최적화 테이블에서 가능한 인덱스 유형 중 하나입니다. 자세한 내용은 메모리 최적화 테이블에 대한 인덱스를 참조하세요.

적용 대상: SQL Server, Azure SQL Database 및 Azure SQL Managed Instance.

메모리 내 비클러스터형 인덱스 아키텍처

메모리 내 비클러스터형 인덱스는 Bw-트리라고 하는 데이터 구조를 사용하여 구현되며, 2011에 Microsoft Research를 통해 처음으로 고안 및 설명되었습니다. Bw-트리는 잠금 및 래치 없는 B-트리의 변형입니다. 자세한 내용은 Bw-트리: 새 하드웨어 플랫폼을 위한 B-트리를 참조하세요.

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

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

해시 인덱스와 마찬가지로 여러 데이터 행을 함께 연결할 수 있습니다(버전). 수준 사이의 페이지 포인터는 논리적 페이지 ID이며, 이는 페이지 매핑 테이블에 오프셋되어 각 페이지에 대한 실제 주소가 있습니다.

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

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

표시된 각 비리프 수준 페이지의 키 값은 자식이 포함하는 가장 높은 값이며 각 행에는 해당 페이지 논리 페이지 ID도 포함됩니다. 리프 수준 페이지에서 키 값과 함께 데이터 행의 실제 주소를 포함합니다.

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

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

Bw-tree의 구조를 관리하는 데 필요한 세 가지 작업인 통합, 분할 및 병합이 있습니다.

델타 통합

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

Diagram showing the memory-optimized page mapping table.

분할 페이지

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

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

Diagram showing a memory-optimized index split operation.

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

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

페이지 병합

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

페이지에서 행이 삭제되면 해당 삭제에 대한 델타 레코드가 추가됩니다. 또한 인덱스 페이지(리프가 아닌 페이지)가 병합에 적합한지 여부를 확인하기 위한 검사 만들어집니다. 이 검사 행을 삭제한 후 다시 기본 공간이 최대 페이지 크기의 10% 미만인지 확인합니다. 병합 대상인 경우 세 원자성 단계를 통해 병합이 수행됩니다.

아래 그림에서 작업에서 DELETE 키 값 10을 삭제하는 것으로 가정합니다.

Diagram showing a memory-optimized index merge operation.

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

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

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

성능 고려 사항

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

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

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

SQL Server 및 Azure SQL 인덱스 아키텍처와 디자인 가이드

인덱스 디자인 기본 사항

인덱스 디자인 작업

일반 인덱스 디자인 지침

데이터베이스 고려 사항

쿼리 고려 사항

열 고려 사항

인덱스 특성

파일 그룹 또는 파티션 구성표의 인덱스 배치

여러 파일 그룹에 걸친 파티션

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

메타데이터

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

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

쿼리 고려 사항

열 고려 사항

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

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

데이터베이스 고려 사항

쿼리 고려 사항

열 고려 사항

포함된 열을 사용하여 비클러스터형 인덱스 확장

포함된 열을 사용하여 크기 제한 방지

포괄 열이 있는 인덱스 지침

열 크기 지침

열 수정 지침

디자인 권장 사항

성능 고려 사항

고유 인덱스 디자인 지침

고려 사항

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

디자인 고려 사항

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

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

키 열

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

Columnstore 인덱스 아키텍처

데이터 스토리지는 columnstore 및 rowstore 압축을 사용함

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

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

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

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

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

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

성능 고려 사항

디자인 지침

해시 인덱스 디자인 지침

해시 인덱스 아키텍처

해시 인덱스 버킷 수 구성

성능 고려 사항

선언 고려 사항

행 버전 및 가비지 수집

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

메모리 내 비클러스터형 인덱스 아키텍처

델타 통합

분할 페이지

페이지 병합

성능 고려 사항

다음 단계

피드백

피드백

추가 리소스