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

아티클
04/04/2023

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

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

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

이 가이드에서는 다음과 같은 유형의 인덱스를 다룹니다.

기본 스토리지 형식	인덱스 유형
디스크 기반 rowstore
	클러스터형
	비클러스터형 인덱스
	고유한
	Filtered
columnstore
	클러스터형 Columnstore
	비클러스터형 Columnstore
메모리 최적화
	Hash
	메모리 최적화 비클러스터형

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

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

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

인덱스 디자인 기본 사항

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

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(온라인 트랜잭션 처리)를 예로 들 수 있습니다. 래치 없는 디자인을 제공하므로 메모리 최적화 테이블 및 인덱스가 이 시나리오에 가장 적합합니다. 자세한 내용은 본 지침의 메모리 최적화 테이블에 대한 인덱스 또는 메모리 최적화 테이블에 대한 비클러스터형 인덱스 디자인 지침 및 메모리 최적화 테이블에 대한 해시 인덱스 디자인 지침을 참조하세요.
- 또는 대량의 데이터 집합을 신속하게 처리해야 하는 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, UPDATE, DELETE 및 MERGE 문의 성능이 저하될 수 있습니다. 예를 들어 열이 여러 인덱스에서 사용되고 열 데이터를 수정하는 UPDATE 문을 실행하는 경우 열을 포함하는 각 인덱스뿐만 아니라 기본 테이블(힙 또는 클러스터형 인덱스)에 있는 열도 업데이트되어야 합니다.
- 과도하게 업데이트되는 테이블에 대한 인덱스를 너무 많이 만들지 말고 가능한 열 수가 적은 좁은 인덱스를 만듭니다.
- 많은 인덱스를 사용하면 업데이트 요구는 적지만 데이터 양이 많은 테이블의 쿼리 성능이 향상될 수 있습니다. 쿼리 최적화 프로그램에는 가장 빠른 액세스 방법을 결정하기 위해 선택할 수 있는 인덱스가 더 많기 때문에 많은 수의 인덱스가 SELECT 문과 같이 데이터를 수정하지 않는 쿼리의 성능에 도움이 될 수 있습니다.
쿼리 최적화 프로그램이 기본 테이블 검색을 수행하는 것보다 데이터 검색을 트래버스하는 데 더 오래 걸릴 수 있으므로 작은 테이블을 인덱싱하는 것이 최적이 아닐 수 있습니다. 따라서 작은 테이블에 대한 인덱스는 전혀 사용되지 않을 수도 있지만 테이블의 데이터가 변경될 때마다 유지 관리되어야 합니다.
뷰에 집계, 테이블 조인 또는 집계와 조인의 조합이 포함되어 있으면 뷰에 대한 인덱스 성능이 크게 향상될 수 있습니다. 뷰를 사용하려면 쿼리 최적화 프로그램에서 뷰를 명시적으로 참조할 필요가 없습니다.
Azure SQL Database에서 주 복제본의 데이터베이스는 인덱스용 데이터베이스 관리자 성능 권장 사항을 자동으로 생성합니다. 필요에 따라 자동 인덱스 튜닝을 사용하도록 설정할 수 있습니다.
쿼리 저장소 최적이하의 성능으로 쿼리를 식별하는 데 도움이 되며 최적화 프로그램에서 선택한 인덱스를 문서화하는 쿼리 실행 계획의 기록을 제공합니다.

쿼리 고려 사항

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

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

중요

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

이러한 인덱스에는 모든 필요한 비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인 경우 사람을 신속하게 찾을 수 없습니다. 데이터 분산에 대한 자세한 내용은 Statistics를 참조하십시오.
잘 정의된 하위 집합이 포함된 열(예: 스파스 열, 값이 대부분 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 절을 사용하여 한 파일 그룹에서 다른 파일 그룹으로 테이블을 이동합니다.

다른 파일 그룹에서 자체의 컨트롤러를 가진 여러 개의 물리적 드라이브를 사용하는 경우에는 파일 그룹에 비클러스터형 인덱스를 만들어 성능 향상을 이룰 수 있습니다. 그러면 데이터와 인덱스 정보를 여러 개의 디스크 헤드로 병렬로 읽을 수 있습니다. 예를 들어 Table_A 파일 그룹의 f1 와 Index_A 파일 그룹의 f2 가 모두 같은 쿼리에서 사용되는 경우에는 두 파일 그룹이 모두 경합 없이 충분히 활용되므로 성능 향상이 이루어질 수 있습니다. 그러나 가 쿼리에서 검사되지만 Index_A 참조되지 않는 경우 Table_A 파일 그룹 f1 만 사용됩니다. 성능이 향상되지 않습니다.

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

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

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

인덱스를 분할하면 다음 이점이 제공됩니다.

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

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

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

인덱스를 정의할 때 인덱스 키 열의 데이터를 오름차순으로 저장할지 또는 내림차순으로 저장할지 고려합니다. 오름차순은 기본값이며 이전 버전의 데이터베이스 엔진과의 호환성을 유지합니다. CREATE INDEX, CREATE TABLE 및 ALTER TABLE 문의 구문은 인덱스와 제약 조건의 개별 열에서 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 절로 지정된 순서로 결과 집합을 반환했음을 보여 줍니다.

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

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

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

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

SORT 연산자가 제거되었으며 새로 만든 비클러스터형 인덱스가 사용됨을 보여 주는 실행 계획의 다이어그램

데이터베이스 엔진 은 어느 방향으로든 동일하게 효율적으로 이동할 수 있습니다. 로 (RejectedQty DESC, ProductID ASC) 정의된 인덱스는 ORDER BY 절에서 열의 정렬 방향이 반전되는 쿼리에 계속 사용할 수 있습니다. 예를 들어 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 할당 단위가 있습니다.

데이터 체인의 페이지와 페이지 행은 클러스터형 인덱스 키의 값에 따라 정렬됩니다. 삽입된 행의 키 값이 기존 행 간의 순서대로 정렬될 때 모든 삽입이 끝납니다.

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

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

쿼리 고려 사항

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

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

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

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

열 고려 사항

특별한 상황이 아니라면 클러스터형 인덱스 키를 가능한 적은 열로 정의해야 합니다. 다음 중 하나 이상의 특성이 있는 열을 고려하십시오.

고유한 열이거나 고유한 값이 많음

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

팁

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

예를 들어 Production.Product 데이터베이스에서 제품 ID는 AdventureWorks2022 테이블의 제품을 고유하게 나타냅니다. 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`)	키 열(, `A`) 및 포함된 열(`B`,`G`)이 있는 고유하지 않은 비클러스터형 인덱스`E`	키 열(`B`, `A`, `C`) 및 포함된 열(`E`, `G`)	비클러스터형 인덱스는 고유하지 않으므로 행 로케이터가 인덱스 키에 있어야 합니다. 행 로케이터의 `B`와 `A` 열이 이미 있으므로 `c` 열만 추가됩니다. `c` 열이 키 열 목록 끝에 추가됩니다.
키 열이 있는 고유 클러스터형 인덱스(`A`)	키 열(, `C`) 및 포함된 열(`B`)이 있는 고유하지 않은 비클러스터형 인`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 할당 단위가 있습니다.

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

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

데이터베이스 고려 사항

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

자주 업데이트하지는 않지만 데이터가 많은 데이터베이스나 테이블의 경우 비클러스터형 인덱스가 많으면 쿼리 성능이 향상될 수 있습니다. 전체 테이블 비클러스터형 인덱스에 비해 인덱스 유지 관리 비용을 줄이고, 인덱스 스토리지 비용을 줄이고, 쿼리 성능을 향상시킬 수 있도록 데이터의 잘 정의된 하위 집합에 대한 필터링된 인덱스를 만듭니다.

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

테이블에 대한 인덱스를 많이 만들면 테이블의 데이터가 변경될 경우 인덱스도 모두 적절하게 조정되어야 하므로 INSERT, UPDATE, DELETE 및 MERGE 문의 성능이 저하될 수 있습니다.

쿼리 고려 사항

비클러스터형 인덱스를 만들기 전에 데이터에 액세스하는 방법을 이해해야 합니다. 다음과 같은 특성이 있는 쿼리의 경우 비클러스터형 인덱스 사용을 고려하십시오.

JOIN 또는 GROUP BY 절을 사용합니다.

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

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

팁

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

팁

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

열 고려 사항

비클러스터형 인덱스를 만들 때 열에 대한 다음 특성을 고려하십시오.

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

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

테이블에 클러스터형 인덱스가 있으면 클러스터형 인덱스에 정의된 열이 자동으로 테이블의 각 비클러스터형 인덱스에 추가됩니다. 이를 통해 비클러스터형 인덱스 정의에 클러스터형 인덱스 열을 지정하지 않고도 쿼리에 사용되는 열이 모두 포함될 수 있습니다. 예를 들어 테이블에 열에 클러스터형 인덱스가 있고 열 C에 고유하지 않은 비클러스터형 인덱스가 B 있고 A 가 키 값 B인 , A및 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). INCLUDE 문의 CREATE INDEX 절을 사용하면 인덱스 키는 (Title, Revision)으로 정의되고 FileName 은 키가 아닌 열로 정의됩니다. 이 방법으로 인덱스 키 크기는 110바이트(55 * 2)가 되지만 인덱스는 필요한 모든 열을 포함합니다. 다음 문은 이와 같은 인덱스를 만듭니다.

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을 참조하세요.
키 열과 마찬가지로 image, ntext및 text 데이터 형식에서 파생된 계산 열은 계산 열 데이터 형식이 키가 아닌 인덱스 열로 허용되는 동안 키가 아닌 포괄 열이 될 수 있습니다.
열 이름은 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 제약 조건을 만듭니다. 사용자가 두 명 이상의 직원에 대해 해당 열에 동일한 값을 입력하려고 하면 오류 메시지가 표시되고 중복 값이 입력되지 않습니다.

여러 열로 구성된 고유 인덱스를 사용하면 인덱스 키의 각 값 조합이 고유합니다. 예를 들어 LastName, FirstName및 MiddleName 열의 조합에 대해 고유 인덱스를 만들면 테이블에 있는 각 행에서 이러한 열의 값 조합이 모두 서로 다릅니다.

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

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

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

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 열에는 199개의 행만 NULL이 아닌 값이 들어 있고 나머지 2,480개의 행에는 NULL이 들어 있습니다. 다음 필터링된 인덱스는 이 인덱스에서 정의된 열을 반환하고 EndDate에 필요한 NULL이 아닌 값이 있는 행만 선택하는 쿼리를 처리합니다.

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

필터링된 인덱스를 만드는 방법 및 필터링된 인덱스 조건자 식을 정의하는 방법은 Create Filtered Indexes를 참조하십시오.

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

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

예를 들어 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;
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

다음 필터링된 인덱스 정의에서 상수 1과 비교하기 위해 b 열이 정수 데이터 형식으로 암시적으로 변환됩니다. 이로 인해 오류 메시지 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 인덱스는 두 가지 유형의 스토리지를 사용합니다.

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

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

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

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

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

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

columnstore 인덱스는 행을 관리할 수 있는 단위로 그룹화합니다. 이러한 각 단위를 행 그룹이라고 합니다. 성능을 최적화하려면 행 그룹의 행 수가 압축률을 높일 만큼 크고, 메모리 내 작업을 활용할 만큼 작아야 합니다.

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

행 그룹을 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 행 그룹을 병합하는 백그라운드 병합 작업이 튜플 이동기를 지원합니다.

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

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

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 데이터베이스 및 Azure SQL Managed Instance.

해시 인덱스 아키텍처

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

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

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

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

팁

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

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

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

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

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

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

해시 인덱스 버킷 수 구성

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

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

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

고유 키 값의 해시 충돌이 더 많아 집니다.
각 고유 값에서 다른 고유 값과 동일한 버킷을 강제로 공유합니다.
버킷 당 평균 체인 길이가 증가합니다.
버킷 체인이 길어질수록 인덱스에서 같음 조회 속도는 느려집니다.

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

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

참고

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

성능 고려 사항

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

WHERE 절의 조건자에서 해시 인덱스 키의 각 열에 대한 정확한 값을 지정할 때 뛰어난 성능을 보입니다. 해시 인덱스는 같지 않음 조건자가 주어진 경우 검색으로 되돌아갑니다.
WHERE 절의 조건자에서 인덱스 키에 있는 값 범위를 찾을 때에는 성능이 저하됩니다.
절의 조건자가 WHERE 두 열 해시 인덱스 키의 첫 번째 열에 대해 특정 값을 하나만 규정하지만 키의 다른 열에 대한 값을 지정하지 않으면 좋지 않습니다.

팁

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

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

선언 시 고려 사항

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

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

UNIQUE(또는 Non-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 데이터베이스 및 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는 동일하지만 새 메모리 주소를 갖습니다.

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

페이지 분할

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% 미만인지 확인합니다. 병합 대상인 경우 세 원자성 단계를 통해 병합이 수행됩니다.

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

메모리 최적화 인덱스 병합 작업을 보여 주는 다이어그램

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

성능 고려 사항

디자인 지침

해시 인덱스 디자인 지침

해시 인덱스 아키텍처

해시 인덱스 버킷 수 구성

성능 고려 사항

선언 시 고려 사항

행 버전 및 가비지 수집

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

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

델타 통합

페이지 분할

페이지 병합

성능 고려 사항

다음 단계

추가 리소스

추가 리소스