Columnstore 인덱스 - 디자인 지침

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

columnstore 인덱스 디자인을 위한 개략적인 권장 사항입니다. 몇 가지 적절한 디자인 결정을 통해 columnstore 인덱스가 제공하도록 설계된 높은 데이터 압축 및 쿼리 성능을 달성할 수 있습니다.

필수 조건

이 문서에서는 columnstore 아키텍처 및 용어에 대해 잘 알고 있다고 가정합니다. 자세한 내용은 Columnstore 인덱스: 개요 및 Columnstore 인덱스 아키텍처를 참조하세요.

데이터 요구 사항 확인

columnstore 인덱스를 디자인하기 전에 데이터 요구 사항을 가능한 한 많이 이해합니다. 예를 들어 다음 질문에 대한 답변을 생각해 보세요.

• 테이블이 얼마나 큰가요?
• 쿼리는 대부분 다양한 값을 검색하는 분석을 수행하나요? Columnstore 인덱스는 특정 값을 조회하는 대신 큰 범위 검색에 적합하도록 설계되었습니다.
• 워크로드에서 업데이트 및 삭제를 많이 수행하나요? Columnstore 인덱스는 데이터가 안정적일 때 잘 작동합니다. 쿼리는 행의 10% 미만을 업데이트하고 삭제해야 합니다.
• 데이터 웨어하우스에 대한 팩트 및 차원 테이블이 있나요?
• 트랜잭션 워크로드에 대해 분석을 수행해야 하나요? 그렇다면 실시간 운영 분석에 대한 columnstore 디자인 지침을 참조하세요.

columnstore 인덱스가 필요하지 않을 수 있습니다. 힙 또는 클러스터형 인덱스가 있는 Rowstore(또는 B-트리) 테이블은 데이터를 검색하는 쿼리, 특정 값을 검색 또는 작은 범위의 값에 대한 쿼리에 가장 적합합니다. Rowstore 인덱스는 큰 범위의 테이블 검색 대신 주로 테이블 찾기가 필요한 경향이 있으므로 트랜잭션 워크로드에서 사용됩니다.

요구에 맞는 최상의 columnstore 인덱스 선택

columnstore 인덱스는 클러스터형이거나 비클러스터형입니다. 하나의 클러스터형 columnstore 인덱스에서 하나 이상의 비클러스터형 B-트리 인덱스를 사용할 수 있습니다. Columnstore 인덱스는 쉽게 시도할 수 있습니다. 테이블을 columnstore 인덱스로 만드는 경우 columnstore 인덱스를 삭제하여 테이블을 rowstore 테이블로 다시 쉽게 변환할 수 있습니다.

다음은 옵션 및 권장 사항에 대한 요약입니다.

큰 데이터 웨어하우스 테이블에 클러스터형 columnstore 인덱스 사용

클러스터형 columnstore 인덱스는 인덱스 이상이며 기본 테이블 스토리지입니다. 큰 데이터 웨어하우징 팩트 및 차원 테이블에서 높은 데이터 압축을 제공하며 쿼리 성능을 현저하게 향상시킵니다. 클러스터형 columnstore 인덱스는 트랜잭션 쿼리보다는 분석 쿼리에 적합합니다. 분석 쿼리는 특정 값을 조회하는 대신 다양한 값에서 작업을 수행하는 경향이 있기 때문입니다.

다음과 같은 경우 클러스터형 columnstore 인덱스 사용을 고려합니다.

• 각 파티션에는 백만 개 이상의 행이 있습니다. Columnstore 인덱스에는 각 파티션 내에 행 그룹이 있습니다. 테이블이 너무 작아서 각 파티션 내의 행 그룹을 채울 수 없는 경우 columnstore 압축 및 쿼리 성능의 이점을 얻을 수 없습니다.
• 쿼리는 주로 값 범위에 대한 분석을 수행합니다. 예를 들어 열의 평균 값을 찾으려면 쿼리가 모든 열 값을 검색해야 합니다. 그런 다음 값을 더하여 집계하고 평균을 결정합니다.
• 대부분의 삽입은 최소한의 업데이트 및 삭제로 많은 양의 데이터에서 수행됩니다. IOT(사물 인터넷)와 같은 많은 워크로드는 최소한의 업데이트 및 삭제로 대량의 데이터를 삽입합니다. 이러한 워크로드는 클러스터형 columnstore 인덱스를 사용하여 발생하는 압축 및 쿼리 성능 향상을 활용할 수 있습니다.

다음과 같은 경우 클러스터형 columnstore 인덱스를 사용하지 않습니다.

• 테이블에는 varchar(max), nvarchar(max) 또는 varbinary(max) 데이터 형식이 필요합니다. 또는 이러한 열이 포함되지 않도록 columnstore 인덱스를 디자인합니다(적용 대상: SQL Server 2016(13.x) 및 이전 버전).
• 테이블 데이터가 영구적이지 않습니다. 데이터를 신속하게 저장하고 삭제해야 하는 경우 힙이나 임시 테이블을 사용하는 것이 좋습니다.
• 테이블에 파티션당 100만 개 미만의 행이 있습니다.
• 테이블에 대한 작업의 10% 이상이 업데이트 및 삭제입니다. 많은 수의 업데이트 및 삭제로 인해 조각화가 발생합니다. 조각화는 모든 데이터를 columnstore로 강제 적용하고 조각화를 제거하는 재구성이라는 작업을 실행할 때까지 압축 속도 및 쿼리 성능에 영향을 줍니다. 자세한 내용은 columnstore 인덱스에서 인덱스 조각화 최소화를 참조하세요.

자세한 내용은 데이터 웨어하우징의 Columnstore 인덱스를 참조 하세요.

큰 데이터 웨어하우스 테이블에 정렬된 클러스터형 columnstore 인덱스 사용

정렬된 Columnstore 인덱스의 가용성은 Columnstore 인덱스: 개요을 참조하세요.

다음 시나리오에서 정렬된 클러스터형 columnstore 인덱스를 사용하는 것이 좋습니다.

• 데이터가 상대적으로 정적이며(자주 쓰기 및 삭제 없음) 정렬된 클러스터형 columnstore 인덱스 키가 정적인 경우, 정렬된 클러스터형 columnstore 인덱스는 비순차형 클러스터형 columnstore 인덱스 또는 분석 워크로드에 대한 rowstore 클러스터형 인덱스에 비해 상당한 성능 이점을 제공할 수 있습니다.
• 정렬된 클러스터형 columnstore 인덱스 키의 첫 번째 열에서 고유 값이 많을수록 정렬된 클러스터형 columnstore 인덱스의 성능이 향상될 수 있습니다. 이는 문자열 데이터에 대한 세그먼트 제거가 개선되었기 때문입니다. 자세한 내용은 세그먼트 제거를 참조하세요.
• 자주 쿼리되고 세그먼트 제거, 특히 키의 첫 번째 열을 활용할 수 있는 정렬된 클러스터형 columnstore 인덱스 키를 선택합니다. 테이블의 다른 열에서 세그먼트 제거로 인한 성능 향상은 예측하기 어렵습니다.
• 가장 최근의 분석 데이터만 쿼리(예: 지난 15초)해야 하는 사용 사례에서는 정렬된 클러스터형 columnstore 인덱스가 이전 데이터에 대한 세그먼트 제거를 제공할 수 있습니다. 정렬된 클러스터형 columnstore 데이터의 키에 있는 첫 번째 열은 삽입되거나 생성된 날짜/시간과 같은 날짜/시간 데이터여야 합니다. 세그먼트 제거는 정렬되지 않은 클러스터형 columnstore 인덱스보다 정렬된 클러스터형 columnstore 인덱스에서 더 효과적입니다.
• 이제 uniqueidentifier 데이터 형식을 세그먼트 제거에 사용할 수 있는 GUID 데이터와 함께 키를 포함하는 테이블에서 정렬된 클러스터형 columnstore 인덱스를 고려합니다.

순서가 지정된 클러스터형 columnstore 인덱스는 다음 시나리오에서 효과적이지 않을 수 있습니다.

• 다른 columnstore 인덱스와 마찬가지로 삽입 작업의 비율이 높으면 과도한 스토리지 I/O가 생성됩니다.
• 쓰기 작업이 많은 워크로드의 경우 튜플 이동기의 행 그룹 유지 관리로 인해 시간이 지남에 따라 세그먼트 제거 품질이 감소합니다. 이는 ALTER INDEX REORGANIZE를 사용하여 columnstore 인덱스의 정기적인 유지 관리로 완화할 수 있습니다.

효율적인 테이블 검색을 위해 B-트리 비클러스터형 인덱스 추가

SQL Server 2016(13.x)부터 클러스터형 columnstore 인덱스에 보조 인덱스로 비클러스터형 B-트리 또는 rowstore 인덱스를 만들 수 있습니다. 비클러스터형 B-트리 인덱스는 columnstore 인덱스가 변경될 때 업데이트됩니다. 이는 유용하게 사용할 수 있는 강력한 기능입니다.

보조 B-트리 인덱스를 사용하면 모든 행을 검사하지 않고도 특정 행을 효율적으로 검색할 수 있습니다. 다른 옵션도 사용할 수 있습니다. 예를 들어 B-트리 인덱스에서 UNIQUE 제약 조건을 사용하여 기본 키 또는 외래 키 제약 조건을 적용할 수 있습니다. 고유하지 않은 값은 B-트리 인덱스에 삽입되지 않으므로 SQL Server에서 columnstore에 값을 삽입할 수 없습니다.

columnstore 인덱스에서 B-트리 인덱스로 다음을 수행할 수 있습니다.

• 특정 값 또는 작은 값 범위를 검색하는 쿼리를 실행합니다.
• 기본 키 또는 외래 키 제약 조건과 같은 제약 조건을 적용합니다.
• 업데이트 및 삭제 작업을 효율적으로 수행합니다. B-트리 인덱스는 테이블의 전체 테이블 또는 파티션을 검사하지 않고도 업데이트 및 삭제에 대한 특정 행을 빠르게 찾을 수 있습니다.
• B-트리 인덱스 저장에 사용할 수 있는 추가 스토리지가 있습니다.

실시간 분석을 위해 비클러스터형 columnstore 인덱스 사용

SQL Server 2016(13.x)부터 rowstore 디스크 기반 테이블 또는 메모리 내 OLTP 테이블에 비클러스터형 columnstore 인덱스가 있을 수 있습니다. 이렇게 하면 트랜잭션 테이블에서 실시간으로 분석을 실행할 수 있습니다. 트랜잭션이 기본 테이블에서 발생하는 동안 columnstore 인덱스에서 분석을 실행할 수 있습니다. 한 테이블에서 두 인덱스를 모두 관리하므로 rowstore와 columnstore 인덱스 모두에 대한 변경 내용을 실시간으로 사용할 수 있습니다.

columnstore 인덱스는 rowstore 인덱스보다 10배 더 나은 데이터 압축을 달성하므로 적은 양의 추가 스토리지만 필요합니다. 예를 들어 압축된 rowstore 테이블이 20GB를 사용할 경우 columnstore 인덱스에는 추가 2GB가 필요할 수 있습니다. 필요한 추가 공간은 비클러스터형 columnstore 인덱스의 열 수에 따라 달라집니다.

다음과 같은 경우 비클러스터형 columnstore 인덱스 사용을 고려합니다.

• 트랜잭션 rowstore 테이블에서 실시간으로 분석을 실행합니다. 분석을 위해 설계된 기존 B-트리 인덱스를 비클러스터형 columnstore 인덱스로 바꿀 수 있습니다.

• 별도의 데이터 웨어하우스가 필요하지 않습니다. 일반적으로 회사는 rowstore 테이블에서 트랜잭션을 실행한 다음, 별도의 데이터 웨어하우스에 데이터를 로드하여 분석을 실행합니다. 많은 워크로드의 경우 트랜잭션 테이블에 비클러스터형 columnstore 인덱스를 만들어 로드 프로세스와 별도의 데이터 웨어하우스를 제거할 수 있습니다.

SQL Server 2016(13.x)은 이 시나리오를 수행하기 위한 몇 가지 전략을 제공합니다. OLTP 애플리케이션을 변경하지 않고 비클러스터형 columnstore 인덱스로 설정할 수 있으므로 매우 쉽게 사용해 볼 수 있습니다.

추가 처리 리소스를 추가하려면 읽기 가능한 보조 데이터베이스에서 분석을 실행할 수 있습니다. 읽기 가능한 보조 데이터베이스를 사용하면 트랜잭션 워크로드 및 분석 워크로드의 처리가 구분됩니다.

자세한 내용은 실시간 운영 분석에 대한 Columnstore 시작을 참조 하세요.

최상의 columnstore 인덱스 선택에 대한 자세한 내용은 Sunil Agarwal의 블로그 내 워크로드에 적합한 columnstore 인덱스는 무엇인가요?를 참조하세요.

데이터 관리 및 쿼리 성능에 테이블 파티션 사용

columnstore 인덱스는 데이터를 관리하고 보관하는 데 적합한 방법인 분할을 지원합니다. 분할은 작업을 하나 이상의 파티션으로 제한하여 쿼리 성능을 향상시킵니다.

데이터 관리 간소화를 위해 파티션 사용

큰 테이블의 경우 데이터 범위를 관리하는 유일한 실용적인 방법은 파티션을 사용하는 것입니다. rowstore 테이블에서 파티션의 장점은 columnstore 인덱스에도 적용됩니다.

예를 들어 rowstore와 columnstore 테이블 모두 파티션을 사용하여 다음을 수행합니다.

• 증분 백업의 크기를 제어합니다. 파티션을 별도의 파일 그룹에 백업한 다음 읽기 전용으로 표시할 수 있습니다. 이렇게 하면 이후 백업은 읽기 전용 파일 그룹을 건너뜁니다.
• 이전 파티션을 저렴한 스토리지로 이동하여 스토리지 비용을 절감합니다. 예를 들어 파티션 전환을 사용하여 파티션을 저렴한 스토리지 위치로 이동할 수 있습니다.
• 작업을 파티션으로 제한하여 효율적으로 작업을 수행합니다. 예를 들어 인덱스 유지 관리를 위해 조각화된 파티션만 대상으로 지정할 수 있습니다.

또한 columnstore 인덱스로 분할을 사용하여 다음을 수행합니다.

• 스토리지 비용을 추가 30% 절감합니다. 압축 옵션을 사용하여 이전 파티션을 압축할 COLUMNSTORE_ARCHIVE 수 있습니다. 쿼리 성능을 위해 데이터 속도가 느려지므로 파티션이 자주 쿼리되는 경우 적합합니다.

파티션을 사용하여 쿼리 성능 향상

파티션을 사용하면 특정 파티션만 검색하도록 쿼리를 제한하여 검색할 행 수를 제한할 수 있습니다. 예를 들어 인덱스가 연도별로 분할되고 쿼리가 작년의 데이터를 분석하는 경우 한 파티션의 데이터만 검색하면 됩니다.

columnstore 인덱스에 더 적은 수의 파티션 사용

데이터 크기가 충분히 크지 않은 경우 columnstore 인덱스는 rowstore 인덱스에 사용할 수 있는 것보다 더 적은 파티션으로 최상의 성능을 얻을 수 있습니다. 파티션당 백만 개 이상의 행이 있지 않으면 대부분의 행이 columnstore 압축의 성능 혜택을 받지 못하는 deltastore로 이동할 수 있습니다. 예를 들어 파티션이 10개인 테이블에 100만 개의 행을 로드하고 각 파티션이 100,000개의 행을 수신하는 경우 모든 행이 델타 행 그룹으로 이동합니다.

예시:

• 하나의 파티션 또는 분할되지 않은 테이블에 1,000,000개의 행을 로드합니다. 1,000,000개의 행이 있는 하나의 압축된 행 그룹을 가져옵니다. 이는 높은 데이터 압축 및 빠른 쿼리 성능에 적합합니다.
• 1,000,000개 행을 10개의 파티션에 균등하게 로드합니다. 각 파티션은 columnstore 압축의 최소 임계값보다 작은 100,000개 행을 받습니다. 결과적으로 columnstore 인덱스에는 각각 100,000개의 행이 있는 10개의 델타 행 그룹이 있을 수 있습니다. 델타 행 그룹을 columnstore로 강제 적용하는 여러 가지 방법이 있습니다. 그러나 columnstore 인덱스의 유일한 행인 경우 최상의 압축 및 쿼리 성능을 제공하기에 압축된 행 그룹이 너무 작습니다.

분할에 대한 자세한 내용은 Sunil Agarwal의 블로그 게시물인 columnstore 인덱스를 분할해야 할까요?를 참조하세요.

적절한 데이터 압축 방법 선택

columnstore 인덱스는 데이터 압축에 대 한 두 가지 선택 항목을 제공 합니다. 바로 columnstore 압축과 보관 압축입니다. 인덱스 생성 시 압축 옵션을 선택하거나 나중에 ALTER INDEX ... REBUILD를 사용하여 변경할 수 있습니다.

최상의 쿼리 성능을 위해 columnstore 압축 사용

일반적으로 columnstore 압축은 rowstore 인덱스에 비해 10배 더 뛰어난 압축률을 제공합니다. columnstore 인덱스에 대한 표준 압축 방법이며 빠른 쿼리 성능을 지원합니다.

최상의 데이터 압축을 위해 보관 압축 사용

보관 압축은 쿼리 성능이 중요하지 않은 경우 최대 압축을 위해 설계되었습니다. columnstore 압축보다 더 높은 데이터 압축률을 달성하지만 대가가 따릅니다. 데이터를 압축 및 압축 해제하는 데 시간이 오래 걸리므로 빠른 쿼리 성능에 적합하지 않습니다.

rowstore 테이블을 columnstore 인덱스로 변환할 때 최적화 사용

데이터가 이미 rowstore 테이블에 있는 경우 CREATE COLUMNSTORE INDEX를 사용하여 테이블을 클러스터형 columnstore 인덱스로 변환할 수 있습니다. 테이블을 변환한 후 쿼리 성능을 향상시키는 몇 가지 최적화 방법이 있으며, 이어서 설명하겠습니다.

MAXDOP를 사용하여 행 그룹 품질 향상

힙 또는 클러스터형 B-트리 인덱스를 columnstore 인덱스로 변환하기 위한 최대 프로세서 수를 구성할 수 있습니다. 프로세서를 구성하려면 최대 병렬 처리 수준 옵션(MAXDOP)을 사용합니다.

많은 양의 데이터가 있는 경우 MAXDOP 1이 너무 느릴 수 있습니다. MAXDOP를 4로 높이면 제대로 작동합니다. 행 수가 최적이 아닌 몇 개의 행 그룹이 생성되면 ALTER INDEX REORGANIZE를 실행하여 백그라운드에서 병합할 수 있습니다.

B-트리 인덱스의 정렬된 순서 유지

B-트리 인덱스는 이미 정렬된 순서로 행을 저장하므로 행이 columnstore 인덱스로 압축될 때 해당 순서를 유지하면 쿼리 성능이 향상될 수 있습니다.

columnstore 인덱스가 데이터를 정렬하지 않지만 메타데이터를 사용하여 각 행 그룹에 있는 각 열 세그먼트의 최소값과 최대값을 추적합니다. 값 범위를 검색할 때 행 그룹을 건너뛸 시기를 신속하게 컴퓨팅할 수 있습니다. 데이터가 정렬되면 더 많은 행 그룹을 건너뛸 수 있습니다.

변환 중에 정렬된 순서를 유지하려면 다음을 수행합니다.

• DROP_EXISTING 절과 함께 CREATE COLUMNSTORE INDEX를 사용합니다. 그러면 인덱스의 이름도 유지됩니다. rowstore 인덱스의 이름을 이미 사용하는 스크립트가 있는 경우 해당 스크립트를 업데이트할 필요가 없습니다.

  다음은 MyFactTable 테이블의 클러스터형 rowstore 인덱스를 클러스터형 columnstore 인덱스로 변환하는 예제입니다. 인덱스 이름 ClusteredIndex_d473567f7ea04d7aafcac5364c241e09가 동일하게 유지됩니다.

  CREATE CLUSTERED COLUMNSTORE INDEX ClusteredIndex_d473567f7ea04d7aafcac5364c241e09
  ON MyFactTable
  WITH (DROP_EXISTING = ON);
  

세그먼트 제거 이해

각 행 그룹에는 테이블의 모든 열에 대해 하나의 열 세그먼트가 포함됩니다. 각 열 세그먼트는 함께 압축되며 실제 미디어에 저장됩니다.

세그먼트를 읽지 않고도 세그먼트를 빠르게 제거할 수 있도록 각 세그먼트에 메타데이터가 있습니다. 데이터 형식 선택 항목은 columnstore 인덱스의 쿼리에 대한 일반적인 필터 조건자를 기반으로 쿼리 성능에 상당한 영향을 미칠 수 있습니다. 자세한 내용은 세그먼트 제거를 참조하세요.

관련 작업

다음은 columnstore 인덱스를 만들고 유지하기 위한 태스크입니다.

관련 콘텐츠

다음에 대해 빈 columnstore 인덱스을 만듭니다.

• SQL Server 또는 SQL Database는 CREATE TABLE(Transact-SQL)을 참조하세요.
• Azure Synapse Analytics의 경우 CREATE TABLE(Azure Synapse Analytics)을 참조하세요.

기존 rowstore 힙 또는 B-트리 인덱스를 클러스터형 columnstore 인덱스로 변환하는 방법 또는 비클러스터형 columnstore 인덱스를 만드는 방법에 대한 자세한 내용은 CREATE COLUMNSTORE INDEX(TRANSACT-SQL)를 참조하세요.

• columnstore 인덱스의 새로운 기능
• 데이터 웨어하우징의 Columnstore 인덱스
• Columnstore 인덱스 쿼리 성능