인덱스 디자인

  • 20분

SQL Server는 여러 워크로드를 지원하기 위해 여러 인덱스 유형을 제공합니다. 높은 수준에서 인덱스는 테이블 또는 뷰와 연결된 디스크 내 구조로 간주할 수 있으므로 SQL Server는 전체 테이블을 검색하는 것과 비교하여 인덱스 키(테이블 또는 뷰에서 하나 이상의 열로 구성됨)와 연결된 행 또는 행을 더 쉽게 찾을 수 있습니다.

클러스터형 인덱스

일반적인 DBA 면접 질문은 인덱스가 SQL Server의 기본 데이터 스토리지 기술이므로 클러스터형 인덱스와 비클러스터형 인덱스의 차이점을 후보에게 물어보는 것입니다. 클러스터형 인덱스는 키 값에 따라 정렬된 순서로 저장된 기본 테이블입니다. 행을 하나의 순서로만 저장할 수 있으므로 지정된 테이블에는 하나의 클러스터형 인덱스만 있을 수 있습니다. 클러스터형 인덱스가 없는 테이블을 힙이라고 하며 힙은 일반적으로 준비 테이블로만 사용됩니다. 중요한 성능 디자인 원칙은 클러스터형 인덱스 키를 최대한 좁게 유지하는 것입니다. 클러스터형 인덱스에 대해 하나 이상의 키 열을 고려할 때 고유하거나 많은 고유 값을 포함하는 열을 선택해야 합니다. 좋은 클러스터형 인덱스 키의 또 다른 속성은 순차적으로 액세스되고 테이블에서 검색된 데이터를 정렬하는 데 자주 사용되는 레코드에 대한 것입니다. 정렬에 사용되는 열에 클러스터형 인덱스를 사용하면 데이터가 이미 원하는 순서로 저장되므로 쿼리가 실행될 때마다 정렬 비용이 발생하지 않을 수 있습니다.

비고

테이블이 특정 순서로 '저장'되어 있다고 말할 때 실제 디스크 내 순서가 아닌 논리적 순서를 참조합니다. 인덱스에는 페이지 사이에 포인터가 있으며 포인터는 논리적 순서를 만드는 데 도움이 됩니다. 인덱스를 순서대로 검색할 때 SQL Server는 페이지에서 페이지로 포인터를 따릅니다. 인덱스를 만든 직후에는 디스크에 실제 순서로 저장될 가능성이 높지만 데이터를 수정하기 시작하고 새 페이지를 인덱스에 추가해야 하는 경우 포인터는 여전히 올바른 논리적 순서를 제공하지만 새 페이지는 실제 디스크 순서가 아닐 가능성이 큽니다.

비클러스터형 인덱스

비클러스터형 인덱스는 데이터 행과 별도의 구조체입니다. 비클러스터형 인덱스에는 인덱스에 대해 정의된 키 값과 키 값이 포함된 데이터 행에 대한 포인터가 포함됩니다. SQL Server의 포함된 열 기능을 사용하여 비클러스터형 인덱스의 리프 수준에 키가 아닌 열을 추가하여 더 많은 열을 포함할 수 있습니다. 테이블에 여러 개의 비클러스터형 인덱스를 만들 수 있습니다.

다음 예제에서는 인덱스를 추가하거나 기존 비클러스터형 인덱스에 열을 추가해야 하는 경우를 보여 줍니다.

키 조회 연산자를 사용하여 쿼리 및 쿼리 실행 계획

쿼리 계획은 인덱스 검색을 사용하여 검색된 각 행에 대해 클러스터형 인덱스(테이블 자체)에서 더 많은 데이터를 검색해야 했음을 나타냅니다. 비클러스터형 인덱스가 있지만 제품 열만 포함합니다. 쿼리의 다른 열을 비클러스터형 인덱스에 추가하는 경우 실행 계획이 변경되어 키 조회를 제거할 수 있습니다.

키 조회 없이 인덱스와 쿼리 계획 변경하기

위에서 만든 인덱스는 커버링 인덱스의 예입니다. 키 열 외에도 쿼리를 포함하고 테이블 자체에 액세스할 필요가 없도록 추가 열을 포함합니다.

비클러스터형 인덱스와 클러스터형 인덱스는 모두 고유하게 정의할 수 있으므로 키 값이 중복되지 않을 수 있습니다. 테이블에 PRIMARY KEY 또는 UNIQUE 제약 조건을 만들 때 고유 인덱스가 자동으로 만들어집니다.

이 섹션에서는 행 저장소 인덱스라고도 하는 SQL Server의 b-트리 인덱스에 중점을 둡니다. 다음 이미지는 b-트리의 일반 구조를 나타냅니다.

SQL Server 및 Azure SQL 인덱스의 B-트리 아키텍처

인덱스 b-트리의 각 페이지를 인덱스 노드라고 하며 b-트리의 최상위 노드를 루트 노드라고 합니다. 인덱스의 아래쪽 노드를 리프 노드라고 하며 리프 노드의 컬렉션은 리프 수준입니다.

인덱스 디자인은 예술과 과학의 혼합입니다. 키에 열이 거의 없는 좁은 인덱스는 업데이트 시간이 적고 유지 관리 오버헤드가 낮습니다. 그러나 더 많은 열을 포함하는 더 넓은 인덱스만큼 많은 쿼리에는 유용하지 않을 수 있습니다. 애플리케이션의 쿼리에서 선택한 열을 기반으로 여러 인덱싱 방법을 실험해야 할 수 있습니다. 쿼리 최적화 프로그램은 일반적으로 쿼리에 가장 적합한 기존 인덱스로 간주되는 것을 선택합니다. 그러나 빌드할 수 있는 더 나은 인덱스가 없다는 의미는 아닙니다.

데이터베이스를 올바르게 인덱싱하는 작업은 복잡할 수 있습니다. 테이블에 대한 인덱스를 계획할 때는 다음과 같은 몇 가지 기본 원칙을 염두에 두어야 합니다.

  • 시스템의 워크로드를 이해합니다. 삽입 작업에 주로 사용되는 테이블은 읽기 작업이 많은 데이터 웨어하우스 작업에 사용되는 테이블에 비해 추가 인덱스의 이점이 적습니다.
  • 가장 자주 실행되는 쿼리를 중심으로 인덱스를 최적화합니다.
  • 쿼리의 열에 적절한 데이터 형식을 선택합니다. 인덱스는 정수 데이터 형식, 고유 열 또는 null이 아닌 열에 가장 적합합니다.
  • 조건자 및 조인 절에 자주 사용되는 열에 비클러스터형 인덱스를 만들어 오버헤드를 최소화하기 위해 최대한 좁게 유지합니다.
  • 데이터 크기/볼륨을 고려합니다. 작은 테이블의 테이블 검색은 비교적 저렴하지만 큰 테이블의 스캔은 비용이 많이 듭니다.

SQL Server에서 제공하는 또 다른 옵션은 필터링된 인덱스를 만드는 것입니다. 필터링된 인덱스는 행의 상당 비율이 해당 열에서 동일한 값을 공유하는 큰 테이블의 열에 적합합니다. 다음 예제는 퇴사 또는 퇴직한 직원을 포함하여 모든 직원의 레코드를 저장하는 직원 테이블입니다.

CREATE TABLE [HumanResources].[Employee](
     [BusinessEntityID] [int] NOT NULL,
     [NationalIDNumber] [nvarchar](15) NOT NULL,
     [LoginID] [nvarchar](256) NOT NULL,
     [OrganizationNode] [hierarchyid] NULL,
     [OrganizationLevel] AS ([OrganizationNode].[GetLevel]()),
     [JobTitle] [nvarchar](50) NOT NULL,
     [BirthDate] [date] NOT NULL,
     [MaritalStatus] [nchar](1) NOT NULL,
     [Gender] [nchar](1) NOT NULL,
     [HireDate] [date] NOT NULL,
     [SalariedFlag] [bit] NOT NULL,
     [VacationHours] [smallint] NOT NULL,
     [SickLeaveHours] [smallint] NOT NULL,
     [CurrentFlag] [bit] NOT NULL,
     [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
     [ModifiedDate] [datetime] NOT NULL)

이 표에는 직원이 현재 고용되어 있는지를 나타내는 열이 있습니다 CurrentFlag. 이 예제에서는 두 값을 나타내는 데이터 형식을 사용합니다 bit . 하나는 현재 사용 중이고 0은 현재 사용되지 않습니다. 열 WHERE CurrentFlag = 1에 CurrentFlag 필터링된 인덱스를 만들면 현재 직원을 효율적으로 쿼리할 수 있습니다.

또한 뷰에 인덱스를 만들 수 있습니다. 이 인덱스는 뷰에 집계 및/또는 테이블 조인과 같은 쿼리 요소가 포함될 때 상당한 성능 향상을 제공할 수 있습니다.

Columnstore 인덱스

Columnstore 인덱스는 대규모 집계 워크로드와 관련된 쿼리에 대해 향상된 성능을 제공합니다. 처음에 데이터 웨어하우스를 대상으로 하는 columnstore 인덱스는 이후 큰 테이블의 쿼리 성능 문제를 해결하기 위해 다양한 다른 워크로드에 채택되었습니다. b-트리 인덱스와 마찬가지로 클러스터형 columnstore 인덱스는 특별한 방식으로 저장된 테이블 자체를 나타내고 비클러스터형 columnstore 인덱스는 테이블과 독립적으로 저장됩니다. 클러스터형 columnstore 인덱스는 기본적으로 테이블의 모든 열을 포함하지만 정렬되지는 않습니다.

비클러스터형 columnstore 인덱스는 일반적으로 두 가지 시나리오에서 사용됩니다. 첫 번째는 columnstore 인덱스(예: XML, CLR, sql_variant, ntext, text 및 image)에서 열의 데이터 형식이 지원되지 않는 경우입니다. 클러스터형 columnstore 인덱스는 항상 테이블의 모든 열을 포함하므로 비클러스터형 인덱스가 유일한 옵션입니다. 두 번째 시나리오에는 보고서가 동시에 실행되는 동안 데이터가 테이블에 로드되는 하이브리드 HTAP(트랜잭션 분석 처리) 아키텍처에 사용되는 필터링된 인덱스가 포함됩니다. 인덱스 필터링(일반적으로 날짜 필드)을 사용하면 효율적인 삽입 및 보고 성능을 사용할 수 있습니다.

Columnstore 인덱스는 각 열을 독립적으로 저장하여 필요한 열만 검색하여 IO를 줄이고 열 내의 유사한 데이터로 인해 압축을 더 크게 하는 두 가지 이점을 제공합니다. 데이터 웨어하우스의 팩트 테이블과 같은 대규모 데이터 집합을 검사하는 분석 쿼리에서 가장 잘 수행됩니다. 단일 값 조회에 대한 b-트리 비클러스터형 인덱스를 사용하여 columnstore 인덱스를 보강할 수 있습니다.

또한 이러한 인덱스는 일괄 처리 실행 모드의 이점을 활용하여 행 집합(일반적으로 약 900개)을 하나씩 처리하는 대신 한 번에 처리합니다. 이 방법은 CPU 지침을 크게 줄입니다.

SELECT SUM(Sales) FROM SalesAmount;

일괄 처리 모드는 기존 행 처리보다 성능이 향상됩니다. rowstore에 대한 일괄 처리 모드는 columnstore 인덱스와 동일한 수준의 읽기 성능을 갖지 않지만 분석 쿼리는 최대 5배의 성능 향상을 볼 수 있습니다.

데이터 웨어하우스 워크로드에 대한 columnstore 인덱스의 또 다른 장점은 102,400개 이상의 행 대량 삽입 작업에 최적화된 로드 경로입니다. columnstore에 직접 로드할 최소값은 102,400이지만 행 그룹이라고 하는 각 행 컬렉션은 최대 약 1,024,000개의 행일 수 있습니다. 행 그룹이 적지만 더 꽉 차면 SELECT 요청된 레코드를 검색하기 위해 검색해야 하는 행 그룹이 적기 때문에 쿼리의 효율성이 높아집니다. 이러한 로드는 메모리에서 발생하며 인덱스에 직접 로드됩니다. 더 작은 볼륨의 경우 데이터는 델타 저장소라는 b-트리 구조에 기록되고 인덱스에 비동기적으로 로드됩니다.

Columnstore 인덱스 로드 예제

이 예제에서는 동일한 데이터가 FactResellerSales_CCI_Demo와 FactResellerSales_Page_Demo 두 개의 테이블에 로드됩니다. FactResellerSales_CCI_Demo는 클러스터형 컬럼스토어 인덱스를 가지고 있으며, FactResellerSales_Page_Demo는 두 개의 열을 가진 클러스터형 B-트리 인덱스가 있으며 페이지 압축됨. 각 테이블이 FactResellerSalesXL_CCI 테이블에서 1,024,000개의 행을 로드하는 것을 볼 수 있습니다. SET STATISTICS TIME가 ON일 때, SQL Server는 쿼리 실행의 경과 시간을 추적합니다. columnstore 테이블에 데이터를 로드하는 데 약 8초가 걸렸으며, 페이지 압축 테이블에 로드하는 데는 거의 20초가 걸렸습니다. 이 예제에서는 columnstore 인덱스로 들어가는 모든 행이 단일 행 그룹에 로드됩니다.

단일 작업에서 행 수가 102,400개 미만인 데이터를 columnstore 인덱스에 로드하는 경우, 델타 저장소라는 b-트리 구조에 로드됩니다. 데이터베이스 엔진은 튜플 이동기라는 비동기 프로세스를 사용하여 이 데이터를 columnstore 인덱스로 이동합니다. 이러한 레코드를 읽는 것이 columnstore에서 읽는 것보다 효율성이 떨어지기 때문에 열린 델타 저장소가 있으면 쿼리 성능에 영향을 줄 수 있습니다. 델타 저장소를 columnstore 인덱 COMPRESS_ALL_ROW_GROUPS 스에 추가 및 압축하도록 강제하기 위해 옵션을 사용하여 인덱스를 다시 구성할 수도 있습니다.