다음을 통해 공유

데이터 웨어하우징의 Columnstore 인덱스

  • 아티클

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

Columnstore 인덱스는 분할과 함께 SQL Server 데이터 웨어하우스를 빌드하는 필수 요소입니다. 이 문서에서는 SQL 데이터베이스 엔진 사용하는 데이터 웨어하우징 디자인의 주요 사용 사례 및 예제에 중점을 둡니다.

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

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 인덱스를 사용하여 CREATE COLUMNSTORE INDEX 및 성능 튜닝을 참조하세요. 순서가 지정된 columnstore 인덱스 가용성은 Ordered 열 인덱스 가용성을 참조 하세요.

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

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

SQL Server 2016(13.x)부터 클러스터형 columnstore 인덱스에 rowstore 비클러스터형 인덱스를 정의할 수 있습니다.

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

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에서의 스냅샷 격리를 참조하세요.

관련 콘텐츠