SQL Server 및 Azure SQL 인덱스 아키텍처와 디자인 가이드
적용 대상:
Microsoft Fabric의 SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW) SQL 데이터베이스
데이터베이스 애플리케이션 병목 상태는 주로 잘못 디자인된 인덱스와 인덱스의 부족으로 인해 나타납니다. 효율적인 인덱스를 디자인하는 것은 좋은 데이터베이스 및 응용 프로그램 성능을 달성하기 위해 매우 중요합니다. 이 인덱스 디자인 가이드에서는 인덱스 아키텍처에 관해 설명하고 애플리케이션 요구 사항을 충족하는 효과적인 인덱스를 디자인하는 데 도움이 되는 모범 사례를 제공합니다.
이 가이드에서는 사용자가 사용할 수 있는 인덱스 유형에 대한 기본적인 지식이 있다고 가정합니다. 인덱스 형식에 대한 일반적인 설명은 인덱스를 참조하세요.
이 가이드에서는 다음과 같은 형식의 인덱스에 대해 설명합니다.
| 기본 스토리지 형식 | 인덱스 유형 |
|---|---|
| 디스크 기반 rowstore | |
| 클러스터형 | |
| 비클러스터형 인덱스 | |
| 고유한 | |
| 필터링됨 | |
| Columnstore | |
| 클러스터형 Columnstore | |
| 비클러스터형 Columnstore | |
| 메모리 최적화 | |
| 해시 | |
| 메모리 최적화 비클러스터형 |
XML 인덱스에 대한 자세한 내용은 XML 인덱스(SQL Server) 및 SXI(선택적 XML 인덱스)를 참조하세요.
공간 인덱스에 대한 자세한 내용은 공간 인덱스 개요를 참조하세요.
전체 텍스트 인덱스에 대한 내용은 전체 텍스트 인덱스 채우기를 참조하세요.
인덱스 디자인 기본 사항
일반적인 책을 생각해 보세요. 책의 끝에는 책에서 정보를 신속하게 찾는 데 도움이 되는 인덱스가 있습니다. 인덱스는 정렬된 키워드 목록이며 각 키워드 옆에는 각 키워드를 찾을 수 있는 페이지를 가리키는 여러 페이지 번호가 있습니다.
rowstore 인덱스도 다르지 않습니다. 값의 정렬된 목록이며, 각 값에는 해당 값이 있는 데이터 페이지의 포인터가 있습니다. 인덱스 자체는 인덱스 페이지라고 하는 페이지에 저장됩니다. 일반 책에서 인덱스가 여러 페이지에 걸쳐등장하고 SQL이라는 단어가 포함된 모든 페이지에 대한 포인터를 찾아야 하는 경우 키워드 SQL이 포함된 인덱스 페이지를 찾을 때까지 계속 훑어봐야 합니다. 여기서 모든 책 페이지의 포인터를 따릅니다. 인덱스의 시작 부분에 각 문자를 찾을 수 있는 위치의 사전순 목록이 포함된 단일 페이지를 만들면 이 기능을 더욱 최적화할 수 있습니다. 예: "A에서 D까지 - 페이지 121", "E에서 G까지 - 페이지 122" 등. 이 추가 페이지는 시작 장소를 찾기 위해 인덱스로 리프하는 단계를 제거합니다. 일반적인 책에는 이러한 페이지가 없지만 rowstore 인덱스에는 있습니다. 이 단일 페이지를 인덱스의 루트 페이지라고 합니다. 루트 페이지는 인덱스에서 사용되는 트리 구조의 시작 페이지입니다. 트리 비유를 따라 실제 데이터의 포인터가 포함된 끝 페이지는 트리의 “리프 페이지”라고 합니다.
인덱스는 테이블이나 보기와 연결된 디스크상 또는 메모리 내 구조로, 테이블이나 보기의 행 검색 속도를 높입니다. rowstore 인덱스에는 테이블이나 뷰에 있는 하나 이상의 열로 작성되는 키가 포함됩니다. rowstore 인덱스에서 이러한 키는 데이터베이스 엔진이 키 값과 연결된 행을 빠르고 효율적으로 찾을 수 있는 트리 구조(B-트리)에 저장됩니다.
rowstore 인덱스는 행과 열이 있는 테이블로 논리적으로 구성되는 데이터를 저장하며, rowstore 1라는 행 데이터 서식이나 columnstore라는 열 데이터 서식으로 물리적으로 저장됩니다.
데이터베이스 및 해당 워크로드에 적합한 인덱스를 선택하는 것은 쿼리 속도와 업데이트 비용 간의 복잡한 균형 맞추기 작업입니다. 좁은 디스크 기반 rowstore 인덱스나 인덱스 키의 열이 적은 인덱스는 디스크 공간과 유지 관리 오버헤드를 덜 요구합니다. 반면에 와이드 인덱스는 더 많은 쿼리를 다룹니다. 가장 효율적인 인덱스를 찾기까지 여러 가지 디자인을 실험해야 할 수 있습니다. 데이터베이스 스키마나 애플리케이션에 영향을 주지 않고 인덱스를 추가, 수정 및 삭제할 수 있습니다. 따라서 주저하지 말고 다양한 인덱스를 실험해 보아야 합니다.
데이터베이스 엔진의 쿼리 최적화 프로그램은 대부분의 경우 가장 효과적인 인덱스를 안정적으로 찾습니다. 전반적인 인덱스 디자인 전략은 쿼리 최적화 프로그램에서 선택할 수 있는 다양한 인덱스를 제공하고 이를 신뢰하여 올바른 결정을 내릴 수 있도록 해야 합니다. 이렇게 하면 분석 시간이 단축되고 다양한 상황에서 만족스러운 성능이 제공됩니다. 쿼리 최적화 프로그램에서 특정 쿼리에 사용하는 인덱스를 확인하려면 SQL Server Management Studio의 쿼리 메뉴에서 실제 실행 계획 포함을 선택합니다.
인덱스를 많이 사용한다고 해서 항상 성능이 좋은 것은 아니고, 좋은 성능이 항상 효율적인 인덱스 사용을 의미한다고 생각하지 마세요. 인덱스를 사용하는 것이 성능을 극대화하는 데 도움이 된다면 쿼리 최적화 프로그램의 작업은 간단할 것입니다. 실제로 잘못된 인덱스 선택으로 인해 최적 성능이 구현되지 못할 수 있습니다. 따라서 쿼리 최적화 프로그램은 성능이 향상되는 경우에만 인덱스 또는 인덱스 조합을 선택하고 성능을 저해하는 경우에는 인덱싱된 검색을 방지하는 역할을 합니다.
1 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에서 주 복제본의 데이터베이스는 인덱스용 데이터베이스 관리자 성능 권장 사항을 자동으로 생성합니다. 필요에 따라 자동 인덱스 튜닝을 사용하도록 설정할 수 있습니다.
쿼리 저장소에서 최적이 아닌 성능으로 쿼리를 식별할 수 있으며 최적화 프로그램에서 선택한 인덱스를 문서화하는 쿼리 실행 계획 기록을 제공합니다.
쿼리 고려 사항
인덱스를 디자인할 때 다음과 같은 쿼리 지침을 고려합니다.
쿼리의 조건자 및 조인 조건에 자주 사용되는 열에 대해 비클러스터형 인덱스를 만듭니다. 이러한 열은 SARGable1 열입니다. 하지만 불필요한 열은 추가하지 않아야 합니다. 인덱스 열을 너무 많이 추가하면 디스크 공간 및 인덱스 유지 관리 성능이 떨어질 수 있습니다.
인덱스를 포함하면 인덱스 자체에 쿼리 요구 사항을 충족하는 데 필요한 모든 데이터가 포함되기 때문에 쿼리 성능이 향상될 수 있습니다. 즉, 요청된 데이터를 검색하려면 테이블 또는 클러스터형 인덱스의 데이터 페이지가 아닌 인덱스 페이지만 필요하기 때문에 전체 디스크 I/O가 줄어듭니다. 예를 들어
A,B,C열에 대해 만든 복합 인덱스가 포함된 테이블에서A와B열을 쿼리하면 지정된 데이터를 인덱스 자체에서만 검색할 수 있습니다.포함 인덱스는 기본 테이블에 액세스하지 않고 조회에서 발생하지 않고 직접 하나 이상의 비슷한 쿼리 결과를 확인하는 비클러스터형 인덱스를 지정하는 것입니다.
이러한 인덱스에는 리프 수준에서 필요한 SARGable이 아닌 열이 모두 있습니다. 다시 말해
SELECT절이나 모든WHERE및JOIN인수에 의해 반환된 열이 인덱스에 포함됩니다.테이블 자체의 행 및 열과 비교할 때 인덱스 범위가 적당히 좁으면 쿼리 실행에 훨씬 적은 I/O가 사용될 수 있고, 이는 인덱스가 전체 열의 하위 집합임을 의미합니다.
큰 테이블의 작은 부분을 선택할 때, 그리고 그 작은 부분이 NULL이 아닌 값이 몇 개만 포함된 스파스 열처럼 고정된 조건자에 의해 정의되는 경우, 인덱스를 포함하는 것이 좋습니다.
같은 행을 업데이트하기 위해 여러 쿼리를 사용하는 대신 단일 문에서 가능한 한 많은 행을 삽입하거나 수정하는 쿼리를 작성하세요. 하나의 문만 사용하면 최적화된 인덱스 유지 관리를 활용할 수 있습니다.
쿼리 형식과 쿼리에서 열이 사용되는 방식을 평가하세요. 예를 들어 정확히 일치하는 쿼리 형식에 사용되는 열은 비클러스터형 또는 클러스터형 인덱스로 적합합니다.
1 관계형 데이터베이스에서 SARGable이라는 용어는 인덱스를 사용하여 쿼리 실행 속도를 높일 수 있는 Search ARGument-able 조건자를 나타냅니다.
열 고려 사항
인덱스를 디자인할 때 다음 열 지침을 고려하세요.
클러스터형 인덱스의 인덱스 키 길이는 짧게 유지합니다. 또한 클러스터형 인덱스는 고유 열이나 null이 아닌 열에 생성되는 이점이 있습니다.
데이터 형식이 ntext, text, image, varchar(max), nvarchar(max) 및 varbinary(max)인 열은 인덱스 키 열로 지정할 수 없습니다. 하지만 varchar(max), nvarchar(max), varbinary(max) 및 xml 데이터 형식은 비클러스터형 인덱스에 키가 아닌 인덱스 열로 참여할 수 있습니다. 자세한 내용은 이 가이드의 포괄 열이 있는 인덱스 섹션을 참조하세요.
xml 데이터 형식은 XML 인덱스의 키 열만 될 수 있습니다. 자세한 내용은 XML 인덱스(SQL Server)를 참조하세요. 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')에서만 검색하는 쿼리에는 인덱스를 사용하지 않습니다.계산 열을 인덱싱하는 것이 좋습니다. 자세한 내용은 계산된 열의 인덱스를 참조하세요.
인덱스 특성
인덱스가 쿼리에 적합하다고 판단되면 상황에 가장 적합한 인덱스 형식을 선택할 수 있습니다. 인덱스 특성에는 다음 목록이 포함됩니다.
- 클러스터형 및 비클러스터형
- 고유 및 비고유
- 단일 열과 다중 열
- 인덱스의 열에서 오름차순 또는 내림차순 정렬
- 비클러스터형 인덱스에 대해 전체 테이블 및 필터링됨
- columnstore 및 rowstore
- 메모리 최적화 테이블의 해시 및 비클러스터형
FILLFACTOR 같은 옵션을 설정하면 인덱스의 초기 스토리지 특성을 사용자 지정하여 성능이나 유지 관리를 최적화할 수도 있습니다. 또한 파일 그룹 또는 파티션 구성표를 사용하여 성능을 최적화하여 인덱스 스토리지 위치를 확인할 수 있습니다.
파일 그룹의 인덱스 순위 또는 파티션 구성표
인덱스 디자인 전략을 개발할 때 데이터베이스와 연결된 파일 그룹의 인덱스 배치를 고려해야 합니다. 파일 그룹이나 파티션 구성표를 신중하게 선택하면 쿼리 성능이 향상될 수 있습니다.
기본적으로 인덱스는 인덱스가 생성되는 기본 테이블과 같은 파일 그룹에 저장됩니다. 비분할 클러스터형 인덱스와 기본 테이블은 항상 같은 파일 그룹에 상주합니다. 하지만 다음 단계와 같은 작업을 수행할 수 있습니다.
- 기본 테이블 또는 클러스터형 인덱스의 파일 그룹이 아닌 파일 그룹에 비클러스터형 인덱스를 생성합니다.
- 클러스터형 및 비클러스터형 인덱스를 여러 파일 그룹에 걸쳐 분할합니다.
- 클러스터형 인덱스를 삭제한 후 새 파일 그룹이나 파티션 구성표를
DROP INDEX문의MOVE TO절에 지정하거나CREATE INDEX문에DROP_EXISTING절을 사용하여 한 파일 그룹에서 다른 파일 그룹으로 테이블을 이동합니다.
다른 파일 그룹에 비클러스터형 인덱스를 만들면 파일 그룹이 자체 컨트롤러와 함께 다른 물리적 드라이브를 사용하는 경우 성능을 향상시킬 수 있습니다. 그러면 데이터와 인덱스 정보를 여러 개의 디스크 헤드로 병렬로 읽을 수 있습니다. 예를 들어 파일 그룹 f1의 Table_A과(와) 파일 그룹 f2의 Index_A이(가) 모두 같은 쿼리에서 사용되는 경우 두 파일 그룹이 경합 없이 모두 사용되기 때문에 성능이 향상될 수 있습니다. 그러나 Table_A이(가) 쿼리에서 검사되지만 Index_A은(는) 참조되지 않는 경우에는 파일 그룹 f1만 사용됩니다. 성능이 향상되지 않습니다.
어떤 유형의 액세스가 언제 발생할지 예측할 수 없기 때문에 테이블과 인덱스를 모든 파일 그룹에 분배하는 것이 더 좋을 수 있습니다. 이렇게 하면 모든 데이터와 인덱스가 모든 디스크에 동일하게 분산되기 때문에 어떤 방식으로 데이터에 액세스하든지 모든 디스크에 액세스할 수 있습니다. 이는 시스템 관리자에게 더욱 간단한 방법이기도 합니다.
여러 파일 그룹에 걸친 파티션
여러 파일 그룹에 걸쳐 디스크 기반 클러스터형 및 비클러스터형 인덱스를 분할하는 것을 고려할 수도 있습니다. 분할된 인덱스는 파티션 함수를 기준으로 가로 또는 행으로 분할됩니다. 파티션 함수는 분할 열이라고 하는 특정 열의 값을 근거로 각 행이 파티션 집합에 매핑되는 방식을 정의합니다. 파티션 구성표는 파티션과 파일 그룹 간의 매핑을 지정합니다.
인덱스 분할은 다음과 같은 이점을 제공할 수 있습니다.
큰 인덱스를 보다 쉽게 관리할 수 있는 확장 가능한 시스템을 제공합니다. 예를 들어 OLTP 시스템에서 파티션을 인식하며 큰 인덱스를 처리하는 애플리케이션을 구현할 수 있습니다.
쿼리가 더 빠르고 효율적으로 실행되도록 합니다. 쿼리가 인덱스의 여러 파티션에 액세스하는 경우 쿼리 최적화 프로그램은 개별 파티션을 동시에 처리하고 쿼리의 영향을 받지 않는 파티션을 제외할 수 있습니다.
자세한 내용은 분할된 테이블 및 인덱스를 참조하세요.
인덱스 정렬 순서 디자인 지침
인덱스를 정의할 때 인덱스 키 열의 데이터를 오름차순으로 저장할지 또는 내림차순으로 저장할지 고려합니다. 오름차순이 기본값이며 이전 버전의 데이터베이스 엔진과의 호환성을 유지합니다. 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 절로 지정된 순서로 결과 집합을 반환했음을 보여줍니다.
쿼리의 ORDER BY 절에 있는 것과 일치하는 키 열로 디스크 기반 rowstore 인덱스가 생성되는 경우 쿼리 계획에서 SORT 연산자를 제거하여 쿼리 계획의 효율성을 향상시킬 수 있습니다.
CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty
ON Purchasing.PurchaseOrderDetail
(RejectedQty DESC, ProductID ASC, DueDate, OrderQty);
GO
쿼리가 다시 실행된 후 다음 실행 계획에서는 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.column_store_dictionaries
- sys.column_store_row_groups
- sys.column_store_segments
- sys.dm_column_store_object_pool
- sys.dm_db_column_store_row_group_operational_stats
- sys.dm_db_column_store_row_group_physical_stats
- sys.dm_db_index_operational_stats
- sys.dm_db_index_physical_stats
- sys.dm_db_xtp_hash_index_stats
- sys.dm_db_xtp_index_stats
- sys.dm_db_xtp_nonclustered_index_stats
- sys.dm_db_xtp_object_stats
- sys.dm_db_xtp_table_memory_stats
- sys.hash_indexes
- sys.index_columns
- sys.indexes
- sys.internal_partitions
- sys.memory_optimized_tables_internal_attributes
- sys.partitions
클러스터형 인덱스 디자인 지침
클러스터형 인덱스는 그 키 값에 기반하여 테이블에 데이터 행을 정렬하고 저장합니다. 데이터 행 자체는 한 순서로만 정렬될 수 있으므로 테이블당 클러스터형 인덱스는 하나만 있을 수 있습니다. 몇 가지 예외를 제외하고 모든 테이블에는 다음을 제공하는 하나 또는 여러 개의 열에 정의된 클러스터형 인덱스가 있어야 합니다.
자주 사용되는 쿼리에 사용할 수 있습니다.
높은 수준의 고유성을 제공합니다.
참고 항목
PRIMARY KEY제약 조건을 만들면 하나 또는 여러 개의 열에 고유한 인덱스가 자동으로 생성됩니다. 이 인덱스는 기본적으로 클러스터링되어 있습니다. 하지만 제약 조건을 만들 때 비클러스터형 인덱스를 지정할 수 있습니다.범위 쿼리에서 사용할 수 있습니다.
클러스터형 인덱스가 UNIQUE 속성으로 생성되지 않는 경우 데이터베이스 엔진은 4바이트 고유 식별자 열을 테이블에 자동으로 추가합니다. 필요한 경우 데이터베이스 엔진은 고유 식별자 값을 자동으로 행에 추가하여 각 키를 고유하게 만듭니다. 이 열과 해당 값은 내부적으로 사용되며 사용자는 보거나 액세스할 수 없습니다.
클러스터형 인덱스 아키텍처
Rowstore 인덱스는 B+ 트리로 구성됩니다. 인덱스 B+ 트리의 각 페이지를 인덱스 노드라고 합니다. B+ 트리 맨 위 노드를 루트 노드라고 합니다. 인덱스의 맨 아래에 있는 노드를 리프 노드라고 합니다. 루트 노드와 리프 노드 사이의 모든 인덱스 수준을 총칭하여 중간 수준이라고 합니다. 클러스터형 인덱스에서 리프 노드에는 기본 테이블의 데이터 페이지가 포함되어 있습니다. 루트 노드와 중간 수준 노드에는 인덱스 행을 포함하는 인덱스 페이지가 있습니다. 각 인덱스 행에는 키 값과 함께 B+ 트리의 중간 수준 페이지에 대한 포인터나 인덱스 리프 수준의 데이터 행에 대한 포인터가 있습니다. 인덱스의 각 수준에 있는 페이지는 이중으로 연결된 목록에 연결됩니다.
클러스터형 인덱스에는 sys.partitions에서 행 하나를 가지며 인덱스에서 사용하는 각 파티션에 대해 index_id = 1입니다. 기본적으로 클러스터형 인덱스에는 단일 파티션이 있습니다. 클러스터형 인덱스가 다중 파티션을 사용하는 경우 각 파티션은 해당 특정 파티션에 대한 데이터를 포함하는 B+ 트리 구조를 갖습니다. 예를 들어 클러스터형 인덱스가 4개의 파티션을 사용하면 파티션마다 하나씩 총 4개의 B+ 트리 구조가 있습니다.
클러스터형 인덱스의 데이터 형식에 따라 각 클러스터형 인덱스 구조에는 특정 파티션에 대한 데이터를 저장하고 관리하는 할당 단위가 하나 이상 있습니다. 최소한 각 클러스터형 인덱스에는 파티션당 하나의 IN_ROW_DATA 할당 단위가 있습니다. 또한 클러스터형 인덱스에는 LOB(Large Object) 열이 포함된 경우 파티션당 하나의 LOB_DATA 할당 단위가 있습니다. 8,060바이트 행 크기 제한을 초과하는 가변 길이 열이 포함된 경우 파티션당 하나의 ROW_OVERFLOW_DATA 할당 단위도 있습니다.
데이터 체인의 페이지와 해당 페이지 안의 행은 클러스터형 인덱스 키 값에 따라 정렬됩니다. 모든 삽입은 삽입된 행의 키 값이 기존 행 간의 정렬 시퀀스에 맞는 지점에서 이루어집니다.
이 일러스트레이션에서는 단일 파티션에 있는 클러스터형 인덱스의 구조를 보여 줍니다.
쿼리 고려 사항
클러스터형 인덱스를 만들기 전에 데이터에 액세스하는 방법을 이해하세요. 다음을 수행하는 쿼리에는 클러스터형 인덱스를 사용하십시오.
BETWEEN,>,>=,<및<=.등의 연산자를 사용하여 일정한 범위의 값을 반환합니다.클러스터형 인덱스를 사용하여 첫 번째 값이 있는 행을 찾으면 후속 인덱스 값이 있는 행이 항상 물리적으로 인접하게 됩니다. 예를 들어, 쿼리에서 여러 판매 주문 번호 범위 사이의 레코드를 검색하는 경우
SalesOrderNumber열의 클러스터형 인덱스는 시작 판매 주문 번호가 포함된 행을 빠르게 찾은 다음 마지막 판매 주문 번호에 도달할 때까지 테이블의 모든 연속 행을 검색할 수 있습니다.큰 결과 집합을 반환합니다.
JOIN절을 사용하세요. 일반적으로 외래 키 열입니다.ORDER BY또는GROUP BY절을 사용하세요.ORDER BY또는GROUP BY절에 지정된 열의 인덱스를 사용하면 행이 이미 정렬되어 있으므로 데이터베이스 엔진에서 데이터를 정렬할 필요가 없을 수 있습니다. 따라서 쿼리 성능도 향상됩니다.
열 고려 사항
일반적으로 클러스터형 인덱스 키는 가능한 한 적은 수의 열로 정의해야 합니다. 다음 특성 중 하나 이상이 있는 열을 고려하세요.
고유하거나 수많은 고유 값을 포함합니다.
예를 들어 직원 ID는 직원을 고유하게 식별합니다.
EmployeeID열에 클러스터형 인덱스나 기본 키 제약 조건을 적용하면 직원 ID 번호로 직원 정보를 검색하는 쿼리 성능이 향상됩니다. 또는LastName,FirstName,MiddleName에 클러스터형 인덱스를 생성할 수 있는데 이는 직원 레코드가 이러한 방법으로 자주 그룹화 및 쿼리되기 때문이며, 이러한 열을 결합하더라도 높은 수준의 고유성을 제공합니다.팁
다르게 지정하지 않을 경우 기본 키 제약 조건을 만들면 데이터베이스 엔진은 해당 제약 조건을 지원하기 위한 클러스터형 인덱스를 만듭니다.
uniqueidentifier를 사용하여
PRIMARY KEY로 고유성을 적용할 수 있지만 효율적인 클러스터링 키는 아닙니다.uniqueidentifier를
PRIMARY KEY로 사용하는 경우 비클러스터형 인덱스로 생성하고, 클러스터링된 인덱스를 생성하려면IDENTITY과(와) 같은 다른 열을 사용하는 것이 좋습니다.순차적으로 액세스됨
예를 들어 제품 ID는
Production.Product데이터베이스의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) |
키 열(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(Large Object) 열이 포함된 경우 파티션당 하나의 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). CREATE INDEX 문의 INCLUDE 절을 사용하면 인덱스 키를 (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를 제외한 모든 데이터 형식을 사용할 수 있습니다.
결정적이면서 정확하거나 정확하지 않은 계산 열은 포괄 열이 될 수 있습니다. 자세한 내용은 계산된 열의 인덱스를 참조하세요.
키 열과 마찬가지로 image, ntext 및 text 데이터 형식에서 파생된 계산 열의 경우 계산 열 데이터 형식이 키가 아닌 인덱스 열로 허용되는 한 키가 아닌(포괄) 열이 될 수 있습니다.
INCLUDE목록과 키 열 목록에서는 모두 열 이름을 지정할 수 없습니다.열 이름은
INCLUDE목록에서 반복될 수 없습니다.
열 크기 지침
하나 이상의 키 열을 정의해야 합니다. 키가 아닌 열의 최대 수는 1,023개 열입니다. 테이블 열의 최대 개수에서 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 데이터 형식을 키가 아닌 인덱스 열로 추가하면 디스크 공간 요구 사항이 크게 늘어날 수 있습니다. 열 값이 인덱스 리프 수준으로 복사되기 때문입니다. 따라서 열 값이 인덱스와 기본 테이블 모두에 상주합니다.
인덱스 유지 관리를 위해 기본 테이블 또는 인덱싱된 뷰에 대해 수정, 삽입, 업데이트 또는 삭제하는 시간이 늘어납니다.
데이터를 수정해야 할지, 디스크 공간을 추가할지 결정할 때 성능에 미치는 영향과 쿼리 성능 향상 중 어느 것이 더 중요한지를 결정해야 합니다.
고유 인덱스 디자인 지침
고유 인덱스는 인덱스 키에 중복 값이 없으므로 테이블의 모든 행이 어떤 식으로든 고유합니다. 고유 인덱스를 지정하는 것은 고유성이 데이터 자체의 특성인 경우에만 의미가 있습니다. 예를 들어 기본 키가 EmployeeID인 경우 HumanResources.Employee 테이블의 NationalIDNumber 열 값이 고유하도록 하려면 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 샘플 데이터베이스에는 2,679개의 행이 있는 Production.BillOfMaterials 테이블이 있습니다. EndDate 열에는 NULL이 아닌 값이 포함된 199개의 행만 있고 나머지 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
필터링된 인덱스를 생성하는 방법과 필터링된 인덱스 조건자 식을 정의하는 방법에 대한 자세한 내용은 필터링된 인덱스 생성을 참조하세요.
서로 다른 데이터에 필터링된 인덱스
테이블에 다른 유형의 데이터 행이 있는 경우 하나 이상의 데이터 범주에 대해 필터링된 인덱스를 생성할 수 있습니다.
예를 들어 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 를 참조합니다.
필터링된 인덱스 식이 쿼리 조건과 동일하고 쿼리가 쿼리 결과와 함께 필터링된 인덱스 식의 열을 반환하지 않는 경우 필터링된 인덱스 식의 열이 키이거나 필터링된 인덱스 정의에 포함된 열일 필요는 없습니다. 예를 들어 쿼리 조건자는 필터 식과 동일하며 EndDate은(는) 쿼리 결과와 함께 반환되지 않으므로 FIBillOfMaterialsWithEndDate에서는 다음 쿼리를 다룹니다. FIBillOfMaterialsWithEndDate에는 필터링된 인덱스 정의에 키 또는 포함된 열로 EndDate이(가) 필요하지 않습니다.
SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
쿼리 조건자가 필터링된 인덱스 식과 동일하지 않은 비교에서 열을 사용하는 경우 필터링된 인덱스 식의 열은 필터링된 인덱스 정의에 키 또는 포함된 열이어야 합니다. 예를 들어 FIBillOfMaterialsWithEndDate은(는) 필터링된 인덱스에서 행의 하위 집합을 선택하므로 다음 쿼리에 유효합니다. 그러나 EndDate이(가) 필터링된 인덱스 식과 동일하지 않은 비교 EndDate > '20040101'에 사용되므로 다음 쿼리는 처리하지 않습니다. 쿼리 프로세서는 EndDate의 값을 조회하지 않고 이 쿼리를 실행할 수 없습니다. 따라서 EndDate은(는) 필터링된 인덱스 정의의 키 또는 포함된 열이어야 합니다.
SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate > '20040101';
필터링된 인덱스 식의 열은 열이 쿼리 결과 집합에 있는 경우 필터링된 인덱스 정의의 키 또는 포함된 열이어야 합니다. 예를 들어 FIBillOfMaterialsWithEndDate은(는) 쿼리 결과에 EndDate 열을 반환하므로 다음 쿼리를 처리하지 않습니다. 따라서 EndDate은(는) 필터링된 인덱스 정의의 키 또는 포함된 열이어야 합니다.
SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
테이블의 클러스터형 인덱스 키는 필터링된 인덱스 정의에 포함된 열이나 키가 아니어도 됩니다. 클러스터형 인덱스 키는 필터링된 인덱스를 포함한 모든 비클러스터형 인덱스에 자동으로 포함됩니다.
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 인덱스는 두 가지 유형의 스토리지를 모두 사용합니다.
columnstore는 행과 열이 있는 테이블로 논리적으로 구성되고 열 데이터 형식으로 물리적으로 저장되는 데이터입니다.
columnstore 인덱스는 대부분의 데이터를 columnstore 형식으로 물리적으로 저장합니다. Columnstore 형식에서는 데이터가 열로 압축되거나 압축이 풀립니다. 쿼리에서 요청되지 않은 다른 값은 각 행에 압축을 풀지 않아도 됩니다. 따라서 큰 테이블의 전체 열을 빠르게 검색할 수 있습니다.
rowstore는 행과 열이 있는 테이블로 논리적으로 구성된 후 행 데이터 형식으로 물리적으로 저장되는 데이터입니다. 이는 힙 또는 클러스터형 B+ 트리 인덱스와 같은 관계형 테이블 데이터를 저장하는 기존 방법이었습니다.
또한 columnstore 인덱스는 물리적으로 일부 행을 deltastore라는 rowstore 형식으로 저장합니다. 델타 행 그룹이라고도 하는 deltastore는 개수가 너무 적어서 columnstore로 압축할 수 없는 행을 보관하는 장소입니다. 각 델타 행 그룹은 클러스터형 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를 참조하세요.
팁
소규모의 행 그룹이 너무 많으면 columnstore 인덱스 품질이 저하됩니다. 재구성 작업은 삭제된 행을 제거하고 압축된 행 그룹을 결합하는 방법을 결정하는 내부 임계값 정책에 따라 더 작은 행 그룹을 병합합니다. 병합한 후에는 인덱스 품질이 향상되어야 합니다.
SQL Server 2019(15.x) 이상 버전에서는 내부 임계값에 따라 특정 시간 동안 존재하던 더 작은 OPEN 델타 행 그룹을 자동으로 압축하거나 다수의 행이 삭제된 곳에서 COMPRESSED 행 그룹을 병합하는 백그라운드 병합 작업이 튜플 이동기를 지원합니다.
각 열에는 각 행 그룹에 일부 값이 있습니다. 이러한 값을 열 세그먼트라고 합니다. 각 행 그룹에는 테이블의 모든 열에 대해 하나의 열 세그먼트가 포함됩니다. 각 열에는 각 행 그룹에 하나의 열 세그먼트가 있습니다.
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를 참조하세요.
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배 사이여야 합니다.
특정 인덱스 키에 얼마나 많은 값이 지정될지 항상 예측할 수 있는 것은 아닙니다. BUCKET_COUNT 값이 실제 키 값 수의 10배 이내이면 일반적으로 성능이 여전히 양호하며 일반적으로 과대평가하는 것이 과소평가하는 것보다 낫습니다.
버킷이 너무 적으면 다음과 같은 단점이 있습니다.
- 고유 키 값의 해시 충돌이 더 많이 발생했습니다.
- 각 고유 값은 동일한 버킷을 다른 고유 값과 공유해야 합니다.
- 버킷당 평균 체인 길이가 증가합니다.
- 버킷 체인이 길수록 인덱스의 동일성 조회 속도가 느려집니다.
버킷이 너무 많으면 다음과 같은 단점이 있습니다.
- 버킷 수가 너무 많으면 빈 버킷이 더 많아질 수 있습니다.
- 빈 버킷은 전체 인덱스 검색의 성능에 영향을 줍니다. 검사를 정기적으로 수행하는 경우 고유한 인덱스 키 값의 수에 가까운 버킷 수를 선택하는 것이 좋습니다.
- 각 버킷은 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 데이터베이스, Azure SQL Managed Instance
메모리 내 비클러스터형 인덱스 아키텍처
메모리 내 비클러스터형 인덱스는 Bw-트리라고 하는 데이터 구조를 사용하여 구현되며, 2011에 Microsoft Research를 통해 처음으로 고안 및 설명되었습니다. Bw-트리는 잠금 및 래치 없는 B-트리의 변형입니다. 자세한 내용은 Bw-트리: 새 하드웨어 플랫폼을 위한 B-트리를 참조하세요.
상위 수준에서 살펴보자면, Bw-트리는 페이지 ID를 통해 구성되는 페이지 맵(PidMap)이자, 페이지 ID를 할당 및 재사용하기 위한 기능(PidAlloc)이자, 페이지 맵에서 연결된 그리고 서로 연결된 페이지 집합으로 이해할 수 있습니다. 이러한 세 가지 상위 수준의 하위 구성 요소가 Bw-트리의 기본 내부 구조를 구성합니다.
이 구조는 각 페이지에 정렬된 키 값 집합이 있고 인덱스에 여러 수준이 있으며 각각이 하위 수준을 가리키고 리프 수준이 데이터 행을 가리킨다는 점에서 일반적인 B-트리와 비슷합니다. 하지만 여러 가지 차이점이 있습니다.
해시 인덱스와 마찬가지로 여러 데이터 행을 함께 연결할 수 있습니다(버전). 수준 사이의 페이지 포인터는 논리적 페이지 ID입니다. 이 ID는 페이지 매핑 테이블에 대한 오프셋이며 각 페이지의 물리적 주소를 갖습니다.
인덱스 페이지의 현재 위치 업데이트가 없습니다. 이 목적을 위해 새 델타 페이지가 도입되었습니다.
- 페이지 업데이트에는 래치 또는 잠금이 필요하지 않습니다.
- 인덱스 페이지의 크기는 고정되지 않았습니다.
표시된 각 리프가 아닌 수준 페이지의 키 값은 해당 페이지가 가리키는 자식이 포함하고 있는 가장 높은 값이며 각 행은 해당 페이지의 논리 페이지 ID를 포함합니다. 리프 수준 페이지에는 키 값과 함께 데이터 행의 물리적 주소가 포함됩니다.
페이지가 한 방향으로만 연결되기 때문에 SQL Server 데이터베이스 엔진이 오른쪽 페이지 포인터를 따르고 각 리프가 아닌 페이지는 가장 낮은 값을 갖는 B-트리와는 달리 자식의 가장 높은 값을 갖는다는 점을 제외하면 B-트리와 비슷합니다.
리프 수준 페이지를 변경해야 하는 경우 SQL Server 데이터베이스 엔진에서 페이지 자체를 수정하지 않습니다. 대신 SQL Server 데이터베이스 엔진은 변경 내용을 설명하는 델타 레코드를 생성하여 이전 페이지에 추가합니다. 그런 다음 해당 이전 페이지의 페이지 맵 테이블 주소를 이제 이 페이지의 실제 주소가 되는 델타 레코드의 주소로 업데이트합니다.
Bw-트리 구조를 관리하기 위해 통합, 분할, 병합의 세 가지 작업이 필요할 수 있습니다.
델타 통합
델타 레코드의 긴 체인은 인덱스를 검색할 떄 긴 체인을 트래버스하는 것을 의미할 수 있으므로 결국 검색 성능을 떨어뜨릴 수 있습니다. 이미 16개의 요소가 있는 체인에 새 델타 레코드가 추가되면 델타 레코드의 변경 사항이 참조된 인덱스 페이지로 통합되고, 페이지가 통합을 트리거한 새 델타 레코드가 나타내는 변경 사항을 포함하여 다시 빌드됩니다. 새로 다시 빌드된 페이지는 페이지 ID는 동일하지만 메모리 주소가 다릅니다.
분할 페이지
Bw-tree의 인덱스 페이지는 단일 행 저장부터 최대 8KB 저장까지 필요에 따라 확장됩니다. 인덱스 페이지가 8KB로 확장되면 단일 행을 새로 삽입할 경우 인덱스 페이지가 분할됩니다. 내부 페이지의 경우 이는 다른 키 값과 포인터를 추가할 공간이 더 이상 없는 경우를 의미하고, 리프 페이지의 경우, 이는 모든 델타 레코드가 통합되면 행이 너무 커서 페이지에 맞지 않는다는 것을 의미합니다. 리프 페이지의 페이지 머리글에 있는 통계 정보는 델타 레코드를 통합하는 데 필요한 공간을 추적합니다. 이 정보는 각각의 새 델타 레코드가 추가될 때마다 조정됩니다.
분할 작업은 두 개의 원자성 단계로 수행됩니다. 다음 다이어그램에서는 값이 5인 키가 삽입되고 현재 리프 수준 페이지(키 값 4)의 끝을 가리키는 리프가 아닌 페이지가 존재하기 때문에 리프 페이지가 분할을 강제한다고 가정합니다.
1단계: 두 개의 새 페이지 P1과 P2를 할당하고 새로 삽입된 행을 포함하여 이전 P1 페이지의 행을 새 페이지로 분할합니다. 페이지 매핑 테이블의 새 슬롯은 페이지 P2의 실제 주소를 저장하는 데 사용됩니다. 이러한 페이지, P1 및 P2는 아직 동시 실행 작업에 액세스할 수 없습니다. 또한 P1에서 P2로의 논리적 포인터가 설정됩니다. 그런 다음 한 원자성 단계에서 페이지 매핑 테이블을 업데이트하여 포인터를 기존 P1에서 새 P1으로 변경합니다.
2단계: 리프가 아닌 페이지는 P1을 가리키지만 리프가 아닌 페이지에서 P2로 이어지는 직접 포인터는 없습니다. P2는 P1을 통해서만 도달할 수 있습니다. 리프가 아닌 페이지에서 P2로 이어지는 포인터를 만들려면 새로운 리프가 아닌 페이지(내부 인덱스 페이지)를 할당하고, 이전 리프가 아닌 페이지의 모든 행을 복사하고, P2를 가리키는 새 행을 추가합니다. 이 작업이 완료되면 한 원자성 단계에서 페이지 매핑 테이블을 업데이트하여 기존 리프가 아닌 페이지에서 새 비-리프 페이지로 포인터를 변경합니다.
페이지 병합
DELETE 작업의 결과로 최대 페이지 크기의 10퍼센트 미만인 페이지(현재 8KB) 또는 단일 행이 포함된 페이지가 생성되는 경우 해당 페이지는 인접한 페이지와 병합됩니다.
페이지에서 행이 삭제되면 해당 삭제에 대한 델타 레코드가 추가됩니다. 또한 인덱스 페이지(리프가 아닌 페이지)가 병합 대상인지 확인하는 검사가 수행됩니다. 이 검사는 행을 삭제한 후 나머지 공간이 최대 페이지 크기의 10퍼센트 미만인지 확인합니다. 병합 대상인 경우 세 원자성 단계를 통해 병합이 수행됩니다.
다음 이미지에서는 DELETE 작업이 키 값 10을 삭제한다고 가정합니다.
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을 가리키도록 업데이트됩니다.
성능 고려 사항
비클러스터형 인덱스의 성능은 부등식 조건자를 사용하여 메모리 최적화 테이블을 쿼리할 때 비클러스터형 해시 인덱스보다 낫습니다.
메모리 최적화 테이블의 열은 해시 인덱스와 비클러스터형 인덱스 모두의 일부일 수 있습니다.
비클러스터형 인덱스의 키 열에 중복 값이 많으면 업데이트, 삽입 및 삭제 시 성능이 저하될 수 있습니다. 이 상황에서 성능을 향상하는 한 가지 방법은 인덱스 키에서 선택성이 더 좋은 열을 추가하는 것입니다.