Columnstore 인덱스 - Data Warehouse

아티클
07/12/2024

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance Analytics Platform System(PDW)

Columnstore 인덱스는 분할과 함께 SQL Server 데이터 웨어하우스를 빌드하는 필수 요소입니다.

데이터 웨어하우징의 주요 기능

SQL Server 2016(13.x)에서는 columnstore 성능 향상을 위해 다음과 같은 기능을 도입했습니다.

Always On은 읽기 가능 복제본에 대한 columnstore 인덱스 쿼리를 지원합니다.
MARS(Multiple Active Result Set)는 columnstore 인덱스를 지원합니다.
새로운 동적 관리 뷰 sys.dm_db_column_store_row_group_physical_stats(Transact-SQL)는 행 그룹 수준에서 성능 문제 해결 정보를 제공합니다.
columnstore 인덱스의 단일 스레드 쿼리는 일괄 처리 모드에서 실행할 수 있습니다. 이전에는 다중 스레드 쿼리만 일괄 처리 모드에서 실행 가능했습니다.
SORT 연산자는 일괄 처리 모드에서 실행됩니다.
일괄 처리 모드에서 여러 DISTINCT 작업이 실행됩니다.
이제 창 집계는 데이터베이스 호환성 수준 130 이상에 대한 일괄 처리 모드로 실행됩니다.
집계의 효율적인 처리를 위한 집계 푸시다운입니다. 모든 데이터베이스 호환성 수준에서 지원됩니다.
문자열 조건자의 효율적인 처리를 위한 문자열 조건자 푸시다운입니다. 모든 데이터베이스 호환성 수준에서 지원됩니다.
데이터베이스 호환성 수준 130 이상에 대한 스냅샷 격리입니다.
정렬된 클러스터형 columnstore 인덱스는 SQL Server 2022(16.x)에서 사용할 수 있습니다. 자세한 내용은 Columnstore 인덱스 만들기와 순서가 지정된 클러스터형 columnstore 인덱스를 사용한 성능 조정을 참조하세요.

SQL Server 및 Azure SQL 버전 및 플랫폼의 새로운 기능에 대한 자세한 내용은 columnstore 인덱스의 새로운 기능을 참조하세요.

비클러스터형 인덱스와 columnstore 인덱스를 결합하여 성능 향상

SQL Server 2016(13.x)부터 클러스터형 columnstore 인덱스에서 비클러스터형 인덱스를 만들 수 있습니다.

예: 비클러스터형 인덱스를 사용하여 테이블 검색의 효율성 향상

Data Warehouse에서 테이블 검색의 효율성을 개선하기 위해 테이블 검색에 가장 적합한 쿼리를 실행하도록 설계된 비클러스터형 인덱스를 만들 수 있습니다. 예를 들어 일치하는 값을 찾고 작은 범위의 값을 반환하는 쿼리는 columnstore 인덱스보다 B-트리 인덱스에 대해 성능이 더 좋습니다. 왜냐하면 columnstore 인덱스를 통한 전체 테이블 검색이 필요하지 않으며 B-트리 인덱스를 통해 이진 검색을 수행하여 정확한 결과를 더 빠르게 반환하기 때문입니다.

--BASIC EXAMPLE: Create a nonclustered index on a columnstore table.  
  
--Create the table  
CREATE TABLE t_account (  
    AccountKey int NOT NULL,  
    AccountDescription nvarchar (50),  
    AccountType nvarchar(50),  
    UnitSold int  
);  
GO  
  
--Store the table as a columnstore.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account;  
GO  
  
--Add a nonclustered index.  
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);

예: 비클러스터형 인덱스를 사용하여 columnstore 테이블에 기본 키 제약 조건을 적용합니다.

기본적으로 columnstore 테이블은 클러스터형 기본 키 제약 조건을 허용하지 않습니다. 이제 비클러스터형 인덱스를 사용하여 columnstore 테이블에 기본 키 제약 조건을 적용할 수 있습니다. 기본 키는 NULL이 아닌 열에 대한 UNIQUE 제약 조건에 해당하며 SQL Server는 UNIQUE 제약 조건을 비클러스터형 인덱스로 구현합니다. 이러한 사실을 결합하여 다음 예제에서는 NULL이 아닌 열 accountkey에 대해 UNIQUE 제약 조건을 정의합니다. 그 결과 기본 키 제약 조건을 NULL이 아닌 열에 UNIQUE 제약 조건으로 적용하는 비클러스터형 인덱스가 생성됩니다.

다음으로 테이블은 클러스터형 columnstore 인덱스로 변환됩니다. 변환하는 동안 비클러스터형 인덱스는 유지됩니다. 그 결과는 기본 키 제약 조건을 적용하는 비클러스터형 인덱스를 포함한 클러스터형 columnstore 인덱스입니다. columnstore 테이블에 대한 업데이트 또는 삽입은 비클러스터형 인덱스에도 영향을 주므로 고유 제약 조건을 위반하고 NULL이 아닌 모든 작업은 전체 작업이 실패합니다.

그 결과는 두 인덱스에서 기본 키 제약 조건을 적용하는 비클러스터형 인덱스를 포함한 columnstore 인덱스입니다.

--EXAMPLE: Enforce a primary key constraint on a columnstore table.   
  
--Create a rowstore table with a unique constraint.  
--The unique constraint is implemented as a nonclustered index.  
CREATE TABLE t_account (  
    AccountKey int NOT NULL,  
    AccountDescription nvarchar (50),  
    AccountType nvarchar(50),  
    UnitSold int,  
  
    CONSTRAINT uniq_account UNIQUE (AccountKey)  
);  
  
--Store the table as a columnstore.   
--The unique constraint is preserved as a nonclustered index on the columnstore table.  
CREATE CLUSTERED COLUMNSTORE INDEX t_account_cci ON t_account  
  
--By using the previous two steps, every row in the table meets the UNIQUE constraint  
--on a non-NULL column.  
--This has the same end-result as having a primary key constraint  
--All updates and inserts must meet the unique constraint on the nonclustered index or they will fail.  
  
--If desired, add a foreign key constraint on AccountKey.  
  
ALTER TABLE [dbo].[t_account]  
WITH CHECK ADD FOREIGN KEY([AccountKey]) REFERENCES my_dimension(Accountkey);

행 수준 및 행 그룹 수준 잠금을 사용하도록 설정하여 성능 향상

columnstore 인덱스 기능의 비클러스터형 인덱스를 보완하기 위해 SQL Server 2016(13.x)은 선택, 업데이트 및 삭제 작업을 위한 세분화된 잠금 기능을 제공합니다. columnstore 인덱스에 대한 전체 테이블 검사에서 비클러스터형 인덱스 및 행 그룹 수준 잠금에 대해 인덱스 검색에 행 수준 잠금을 사용하여 쿼리를 실행할 수 있습니다. 행 수준 및 행 그룹 수준 잠금을 적절하게 사용하여 읽기/쓰기 동시성을 높이려면 이 방법을 사용합니다.

--Granular locking example  
--Store table t_account as a columnstore table.  
CREATE CLUSTERED COLUMNSTORE INDEX taccount_cci ON t_account  
GO  
  
--Add a nonclustered index for use with this example  
CREATE UNIQUE INDEX taccount_nc1 ON t_account (AccountKey);  
GO  
  
--Look at locking with access through the nonclustered index  
SET TRANSACTION ISOLATION LEVEL repeatable read;  
GO  
  
BEGIN TRAN  
    -- The query plan chooses a seek operation on the nonclustered index  
    -- and takes the row lock  
    SELECT * FROM t_account WHERE AccountKey = 100;  
COMMIT TRAN

스냅샷 격리 및 읽기 커밋된 스냅샷 격리

SI(스냅샷 격리)를 사용하여 트랜잭션 일관성을 보장하고 RCSI(읽기 커밋된 스냅샷 격리)를 사용하여 columnstore 인덱스의 쿼리에 대한 문 수준 일관성을 보장합니다. 이렇게 하면 데이터 기록기를 차단하지 않고 쿼리를 실행할 수 있습니다. 이 비차단 동작 덕분에 복잡한 트랜잭션에 대한 교착 상태의 가능성이 크게 줄어듭니다. 자세한 내용은 SQL Server에서의 스냅샷 격리를 참조하세요.

다음을 통해 공유