Columnstore 인덱스: 개요

  • 아티클

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System(PDW)

Columnstore 인덱스는 대규모 데이터 웨어하우징 팩트 테이블을 저장하고 쿼리하는 표준입니다. 이 인덱스는 열 기반 데이터 스토리지 및 쿼리 처리를 사용하여 데이터 웨어하우스에서 기존 행 기반 스토리지보다 최대 10배 높은 쿼리 성능을 실현합니다. 또한 압축되지 않은 데이터 크기보다 최대 10배의 데이터 압축 향상을 얻을 수 있습니다. SQL Server 2016(13.x) SP1부터 columnstore 인덱스를 사용하면 트랜잭션 워크로드에서 성능이 높은 실시간 분석을 실행할 수 있는 운영 분석을 사용할 수 있습니다.

관련 시나리오에 대해 알아봅니다.

columnstore 인덱스란?

Columnstore 인덱스는 columnstore라는 칼럼 데이터 서식을 사용하여 데이터를 저장, 검색, 관리하는 기술입니다.

주요 용어 및 개념

다음은 columnstore 인덱스와 관련된 주요 용어와 개념입니다.

columnstore

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

일괄 처리 모드

rowstore는 행과 열이 있는 테이블로 논리적으로 구성되고 행 단위 데이터 형식으로 물리적으로 저장되는 데이터입니다. 이 형식은 관계형 테이블 데이터를 저장하는 일반적인 방법입니다. SQL Server에서 rowstore는 기본 데이터 스토리지 형식이 힙, 클러스터형 인덱스 또는 메모리 최적화 테이블인 테이블을 참조합니다.

참고 항목

Columnstore 인덱스에 대한 설명에서는 데이터 스토리지에 대한 서식을 강조하기 위해 rowstore 및 columnstore라는 용어를 사용합니다.

행 그룹

행 그룹은 columnstore 서식으로 동시에 압축되는 행의 그룹입니다. 열 그룹에는 대개 1,048,576개(행 그룹당 최대 행 수)의 행이 포함됩니다.

Columnstore 인덱스는 성능과 압축률을 높이기 위해 테이블을 여러 행 그룹으로 조각화한 후에 각 행 그룹을 열 방식으로 압축합니다. 행 그룹의 행 수는 압축 속도를 향상시킬 만큼 충분히 크고 메모리 내 작업의 이점을 얻을 수 있을 만큼 작아야 합니다.

모든 데이터가 삭제된 행 그룹은 COMPRESSED에서 TOMBSTONE 상태로 전환되고 나중에 튜플 이동기라는 백그라운드 프로세스에 의해 제거됩니다. 행 그룹 상태에 대한 자세한 내용은 sys.dm_db_column_store_row_group_physical_stats(Transact-SQL)를 참조하세요.

팁

행 그룹이 너무 많으면 columnstore 인덱스 품질이 저하됩니다. SQL Server 2017(14.x)까지는 삭제된 행을 제거하고 압축된 행 그룹을 결합하는 방법을 결정하는 내부 임계값 정책에 따라 더 작은 COMPRESSED 행 그룹을 병합하려면 재구성 작업이 필요합니다.
SQL Server 2019(15.x)부터 백그라운드 병합 작업은 많은 수의 행이 삭제된 압축된 행 그룹을 병합하는 작업도 작동합니다.
더 작은 행 그룹을 병합한 후에는 인덱스 품질이 향상되어야 합니다.

참고 항목

AZURE Synapse Analytics의 SQL Server 2019(15.x), Azure SQL Database, Azure SQL Managed Instance 및 전용 SQL 풀부터 튜플 이동기는 내부 임계값에 따라 일정 시간 동안 존재했던 더 작은 OPEN 델타 행 그룹을 자동으로 압축하거나 많은 수의 행이 삭제된 압축된 행 그룹을 병합하는 백그라운드 병합 작업의 도움을 받습니다. 그러면 시간이 지남에 따라 columnstore 인덱스 품질이 향상됩니다.

열 세그먼트

열 세그먼트는 행 그룹 내의 데이터 열입니다.

  • 각 행 그룹에는 테이블의 모든 열에 대해 하나의 열 세그먼트가 포함됩니다.
  • 각 열 세그먼트는 함께 압축되며 실제 미디어에 저장됩니다.
  • 세그먼트를 읽지 않고도 세그먼트를 빠르게 제거하도록 각 세그먼트에 대한 메타데이터가 있습니다.

Column segment

클러스터형 columnstore 인덱스

클러스터형 columnstore 인덱스는 전체 테이블에 대한 실제 스토리지입니다.

Clustered columnstore index

columnstore 인덱스는 열 세그먼트의 조각화를 줄이고 성능을 향상하기 위해 삭제된 행에 대한 ID의 B-트리 목록과 함께 deltastore라는 클러스터형 인덱스에 일부 데이터를 임시로 저장할 수 있습니다. deltastore 작업은 백그라운드에서 처리됩니다. 정확한 쿼리 결과를 반환하기 위해 클러스터형 columnstore 인덱스는 columnstore와 deltastore의 쿼리 결과를 모두 결합합니다.

참고 항목

SQL Server 설명서는 인덱스를 지칭할 때 B-트리라는 용어를 사용합니다. rowstore 인덱스에서 SQL Server는 B+ 트리를 구현합니다. 이는 columnstore 인덱스나 메모리 내 데이터 저장소에는 적용되지 않습니다. 자세한 내용은 SQL Server 및 Azure SQL 인덱스 아키텍처 및 디자인 가이드를 참조 하세요.

델타 행 그룹

델타 행 그룹은 columnstore 인덱스에만 사용되는 클러스터형 B-트리 인덱스입니다. 델타 행 그룹은 행 수가 임계값(1,048,576개 행)에 도달한 후에 columnstore로 이동할 때까지 행을 저장하여 columnstore 압축 및 성능을 개선합니다.

델타 행 그룹이 최대 행 수에 도달하면 OPEN에서 CLOSED 상태로 전환됩니다. 닫힌 행 그룹에 대한 튜플 이동기 검사 백그라운드 프로세스입니다. 닫힌 행 그룹이 있으면 델타 행 그룹을 압축하여 columnstore에 COMPRESSED 행 그룹으로 저장합니다.

델타 행 그룹이 압축되면 기존 델타 행 그룹이 TOMBSTONE 상태로 전환되어 나중에 참조가 없는 경우 튜플 이동기에서 제거됩니다.

행 그룹 상태에 대한 자세한 내용은 sys.dm_db_column_store_row_group_physical_stats(Transact-SQL)를 참조하세요.

참고 항목

SQL Server 2019(15.x)부터 튜플 이동기는 내부 임계값에 따라 일정 시간 동안 존재했던 더 작은 OPEN 델타 행 그룹을 자동으로 압축하거나 많은 수의 행이 삭제된 곳에서 COMPRESSED 행 그룹을 병합하는 백그라운드 병합 작업의 도움을 받습니다. 그러면 시간이 지남에 따라 columnstore 인덱스 품질이 향상됩니다.

Deltastore

columnstore 인덱스에는 둘 이상의 델타 행 그룹이 포함될 수 있습니다. 모든 델타 행 그룹을 통칭하여 deltastore라고 합니다.

대량 로드가 많은 동안 대부분의 행은 deltastore를 통과하지 않고 columnstore로 직접 이동합니다. 대량 로드의 끝에 있는 일부 행은 행 그룹의 최소 크기(102,400개 행)를 충족하기에는 너무 적을 수 있습니다. 결과적으로 최종 행은 columnstore 대신 deltastore로 이동합니다. 행이 102,400개 미만인 작은 대량 로드의 경우 모든 행이 deltastore로 직접 이동합니다.

비클러스터형 columnstore 인덱스

비클러스터형 columnstore 인덱스와 클러스터형 columnstore 인덱스가 동일하게 작동합니다. 차이점은 비클러스터형 인덱스가 rowstore 테이블에 만들어지는 보조 인덱스이지만 클러스터형 columnstore 인덱스가 전체 테이블의 기본 스토리지라는 점입니다.

비클러스터형 인덱스에는 기본 테이블의 일부 또는 모든 행과 열 복사본이 포함됩니다. 인덱스는 테이블의 하나 이상의 열로 정의되며 행을 필터링하는 선택적 조건이 있습니다.

비클러스터형 columnstore 인덱스를 사용하면 OLTP 워크로드가 기본 클러스터형 인덱스를 사용하는 동시에 columnstore 인덱스에서 분석이 동시에 실행되는 실시간 운영 분석을 사용할 수 있습니다. 자세한 내용은 실시간 운영 분석을 위한 columnstore 시작을 참조하세요.

일괄 처리 모드 실행

일괄 처리 모드 실행은 여러 행을 함께 처리하는 데 사용되는 쿼리 처리 방법입니다. 배치 모드 실행은 columnstore 스토리지 형식과 긴밀히 통합되고 그에 맞게 최적화되어 있습니다. 일괄 처리 모드 실행을 벡터 기반 또는 벡터화된 실행이라고도 합니다. Columnstore 인덱스에 대한 쿼리는 일반적으로 쿼리 성능을 2~4배 개선하는 일괄 처리 모드 실행을 사용합니다. 자세한 내용은 쿼리 처리 아키텍처 가이드를 참조하세요.

Columnstore 인덱스를 사용해야 하는 이유

columnstore 인덱스가 데이터 웨어하우스 스토리지 비용을 크게 줄이기 위해 매우 높은 수준의 데이터 압축(일반적으로 10배)을 제공할 수 있습니다. columnstore 인덱스는 B-트리 인덱스보다 뛰어난 분석 성능을 제공합니다. Columnstore 인덱스는 데이터 웨어하우징 및 분석 워크로드에 대한 기본 데이터 스토리지 형식입니다. SQL Server 2016(13.x)부터 운영 워크로드에 대한 실시간 분석에 columnstore 인덱스를 사용할 수 있습니다.

columnstore 인덱스가 너무 빠른 이유:

  • 열은 동일한 do기본 값을 저장하며 일반적으로 비슷한 값을 가지며, 이로 인해 압축 속도가 높습니다. 시스템의 I/O 병목 현상이 최소화되거나 제거되고 메모리 공간이 크게 줄어듭니다.

  • 압축 비율이 높으면 메모리 내 사용 공간이 감소되어 쿼리 성능이 향상됩니다. SQL Server는 메모리에서 더 많은 쿼리 및 데이터 작업을 수행할 수 있으므로 쿼리 성능이 향상될 수 있습니다.

  • 일괄 처리 실행은 여러 행을 함께 처리하여 쿼리 성능을 일반적으로 2~4회 향상시킵니다.

  • 쿼리는 종종 테이블에서 몇 개의 열만 선택하여 실제 미디어의 총 I/O를 줄입니다.

Columnstore 인덱스를 사용해야 하는 경우

권장 사용 사례:

  • 클러스터형 columnstore 인덱스를 사용하여 데이터 웨어하우징 워크로드에 대한 팩트 테이블과 큰 차원 테이블을 저장합니다. 이 메서드는 쿼리 성능 및 데이터 압축을 최대 10배 향상시킵니다. 자세한 내용은 데이터 웨어하우징용 columnstore 인덱스를 참조하세요.

  • 비클러스터형 columnstore 인덱스를 사용하여 OLTP 워크로드에 대한 실시간 분석을 수행할 수 있습니다. 자세한 내용은 실시간 운영 분석을 위한 columnstore 시작을 참조하세요.

  • columnstore 인덱스에 대한 더 많은 사용 시나리오는 요구 사항에 가장 적합한 columnstore 인덱스 선택을 참조 하세요.

Rowstore 인덱스와 Columnstore 인덱스 간에 선택하려면 어떻게 해야 합니까?

Rowstore 인덱스는 데이터를 검색하는 쿼리, 특정 값을 검색할 때 또는 작은 범위의 값에 대한 쿼리에 가장 적합합니다. rowstore 인덱스는 주로 테이블 검색 대신 테이블 검색이 필요한 경향이 있으므로 트랜잭션 워크로드와 함께 사용합니다.

Columnstore 인덱스는 특히 큰 테이블에서 많은 양의 데이터를 검색하는 분석 쿼리에 대해 고성능 향상을 제공합니다. 데이터 웨어하우징 및 분석 워크로드, 특히 팩트 테이블에서 columnstore 인덱스를 사용합니다. 테이블 검색보다는 전체 테이블 검색이 필요한 경향이 있기 때문입니다.

SQL Server 2022(16.x)부터 순서가 지정된 클러스터형 columnstore 인덱스는 순서가 지정된 열 조건자를 기반으로 쿼리의 성능을 향상시킵니다. 순서가 지정된 columnstore 인덱스는 행 그룹 제거를 개선할 수 있으므로 행 그룹을 모두 건너뛰어 성능이 향상될 수 있습니다. 자세한 내용은 순서가 지정된 클러스터형 columnstore 인덱스를 참조하세요.

같은 테이블에 rowstore와 columnstore를 결합할 수 있나요?

예. SQL Server 2016(13.x)부터 rowstore 테이블에 업데이트할 수 있는 비클러스터형 columnstore 인덱스 만들 수 있습니다. columnstore 인덱스는 선택한 열의 복사본을 저장하므로 이 데이터에 대한 추가 공간이 필요하지만 선택한 데이터는 평균 10번 압축됩니다. columnstore 인덱스에서 분석을 실행하고 이와 동시에 rowstore 인덱스에서 트랜잭션을 실행할 수 있습니다. rowstore 테이블의 데이터가 변경되면 columnstore가 업데이트되므로 두 인덱스 모두 동일한 데이터에 대해 작동합니다.

SQL Server 2016(13.x)부터 columnstore 인덱스에 하나 이상의 비클러스터형 rowstore 인덱스를 만들고 기본 columnstore에서 효율적인 테이블 검색을 수행할 수 있습니다. 다른 옵션도 사용할 수 있습니다. 예를 들어 rowstore 테이블에서 UNIQUE 제약 조건을 사용하여 기본 키 제약 조건을 적용할 수 있습니다. 특수하지 않은 값이 rowstore 테이블에 삽입되지 않으므로 SQL Server에서 columnstore에 값을 삽입할 수 없습니다.

메타데이터

columnstore 인덱스에 있는 모든 열이 메타데이터에 포괄 열로 저장됩니다. columnstore 인덱스에는 키 열이 없습니다.

sys.indexes(Transact-SQL)

sys.index_columns(Transact SQL)

sys.partitions(Transact-SQL)

sys.internal_partitions(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_index_operational_stats(Transact-SQL)

sys.dm_db_index_physical_stats(Transact-SQL)

모든 관계형 테이블은 클러스터형 columnstore 인덱스로 지정하지 않는 한 rowstore를 기본 데이터 형식으로 사용합니다. CREATE TABLE 는 옵션을 지정 WITH CLUSTERED COLUMNSTORE INDEX 하지 않는 한 rowstore 테이블을 만듭니다.

문을 사용하여 테이블을 CREATE TABLE 만들 때 옵션을 지정하여 WITH CLUSTERED COLUMNSTORE INDEX 테이블을 columnstore로 만들 수 있습니다. rowstore 테이블이 이미 있고 columnstore로 변환하려는 경우 문을 사용할 CREATE COLUMNSTORE INDEX 수 있습니다.

작업 참조 문서 주의
테이블을 columnstore로 만듭니다. CREATE TABLE(Transact-SQL) SQL Server 2016(13.x)부터 테이블을 클러스터형 columnstore 인덱스로 만들 수 있습니다. 먼저 rowstore 테이블을 만든 다음 columnstore로 변환할 필요가 없습니다.
columnstore 인덱스가 있는 메모리 최적화 테이블을 만듭니다. CREATE TABLE(Transact-SQL) SQL Server 2016(13.x)부터 columnstore 인덱스가 있는 메모리 최적화 테이블을 만들 수 있습니다. 구문을 사용하여 ALTER TABLE ADD INDEX 테이블을 만든 후에 columnstore 인덱스를 추가할 수도 있습니다.
rowstore 테이블을 columnstore로 변환합니다. CREATE COLUMNSTORE INDEX(Transact-SQL) 기존 힙 또는 B-트리를 columnstore로 변환합니다. 이 변환을 수행할 때 기존 인덱스 및 인덱스의 이름을 처리하는 방법을 보여 줍니다.
columnstore 테이블을 rowstore로 변환합니다. CREATE CLUSTERED INDEX(Transact-SQL) 또는 columnstore 테이블을 다시 rowstore 힙으로 변환 일반적으로 이 변환은 필요하지 않지만 변환해야 하는 경우가 있을 수 있습니다. 예제에서는 columnstore를 힙 또는 클러스터형 인덱스로 변환하는 방법을 보여 줍니다.
Rowstore 테이블에 columnstore 인덱스를 만듭니다. CREATE COLUMNSTORE INDEX(Transact-SQL) rowstore 테이블에는 하나의 columnstore 인덱스가 있을 수 있습니다. SQL Server 2016(13.x)부터 columnstore 인덱스가 필터링된 조건을 가질 수 있습니다. 예제에서는 기본 구문을 보여 줍니다.
운영 분석을 위한 성능 인덱스를 만듭니다. 실시간 운영 분석을 위한 columnstore 시작 OLTP 쿼리가 B-트리 인덱스를 사용하고 분석 쿼리가 columnstore 인덱스를 사용하도록 상호 보완적인 columnstore 및 B-트리 인덱스를 만드는 방법을 설명합니다.
데이터 웨어하우징에 대한 성능이 좋은 columnstore 인덱스를 만듭니다. 데이터 웨어하우스용 Columnstore 인덱스 columnstore 테이블에서 B-트리 인덱스를 사용하여 성능이 좋은 데이터 웨어하우징 쿼리를 만드는 방법을 설명합니다.
B-트리 인덱스로 columnstore 인덱스로 기본 키 제약 조건을 적용합니다. 데이터 웨어하우스용 Columnstore 인덱스 B-트리 및 columnstore 인덱스를 결합하여 columnstore 인덱스에서 기본 키 제약 조건을 적용하는 방법을 보여 줍니다.
Columnstore 인덱스를 삭제합니다. DROP INDEX(Transact-SQL) columnstore 인덱스를 삭제할 때는 B-트리 인덱스에서 사용하는 표준 DROP INDEX 구문을 사용합니다. 클러스터형 columnstore 인덱스를 삭제하면 columnstore 테이블이 힙으로 변환됩니다.
columnstore 인덱스에서 행을 삭제합니다. DELETE (Transact-SQL) DELETE(Transact-SQL)를 사용하여 행을 삭제합니다.

columnstore 행: SQL Server는 행을 논리적으로 삭제된 것으로 표시하지만 인덱스를 다시 작성할 때까지 행에 대한 실제 스토리지를 회수하지 않습니다.

deltastore 행: SQL Server가 행을 논리적으로 또는 물리적으로 삭제합니다.
columnstore 인덱스 행을 업데이트합니다. UPDATE(Transact-SQL) UPDATE(Transact-SQL)를 사용하여 행을 업데이트합니다.

columnstore 행: SQL Server는 행을 논리적으로 삭제된 것으로 표시한 다음 업데이트된 행을 deltastore에 삽입합니다.

deltastore 행: SQL Server는 deltastore의 행을 업데이트합니다.
데이터를 columnstore 인덱스로 로드합니다. Columnstore는 데이터 로드를 인덱싱합니다.
deltastore의 모든 행이 columnstore로 이동하도록 합니다. ALTER INDEX(Transact-SQL) ... REBUILD

인덱스 다시 구성 및 다시 작성		 REBUILD 옵션과 함께 ALTER INDEX를 사용하면 모든 행이 강제로 columnstore로 이동합니다.
columnstore 인덱스 조각 모음 ALTER INDEX(Transact-SQL) ALTER INDEX ... REORGANIZE columnstore 인덱스를 온라인으로 조각 모음합니다.
columnstore 인덱스와 테이블을 병합합니다. MERGE(Transact-SQL)

다음 단계

columnstore 인덱스의 새로운 기능
Columnstore는 데이터 로드를 인덱싱합니다.
버전이 지정된 columnstore 인덱스 기능 요약
Columnstore 인덱스는 쿼리 성능을 인덱싱합니다.
실시간 운영 분석을 위한 columnstore 시작
데이터 웨어하우스용 Columnstore 인덱스
Columnstore 인덱스 조각 모음
SQL Server 인덱스 디자인 가이드
Columnstore 인덱스 아키텍처
CREATE COLUMNSTORE INDEX(Transact-SQL)