인덱스를 사용하여 최적화

인덱스는 테이블 행에 최적화된 조회 경로를 만들어 데이터 검색을 가속화하는 데이터 구조입니다. 인덱스가 없으면 데이터베이스 엔진은 테이블의 모든 행을 검색하여 일치하는 레코드를 찾아야 하며, 테이블이 늘어나면 전체 테이블 검색 속도가 엄청나게 느려집니다.

인덱스가 책의 인덱스처럼 작동합니다. 문서를 찾기 위해 모든 페이지를 읽는 대신 인덱스로 이동하여 관련 페이지로 직접 이동합니다. 데이터베이스는 인덱스를 비슷하게 사용하여 잠재적으로 수백만 개의 행 비교를 소수의 효율적인 조회로 변환합니다.

그러나 데이터베이스가 데이터와 함께 인덱스 구조를 유지해야 하므로 인덱스는 스토리지 공간을 차지하게 되고, INSERT, UPDATE, 및 DELETE 작업의 속도를 저하시킬 수 있습니다. 이러한 절충을 통해 인덱스 선택은 쿼리 성능과 쓰기 처리량 모두에 직접적인 영향을 주는 중요한 디자인 결정입니다.

인덱스 유형이 다르면 용도가 다릅니다.

rowstore 인덱스 사용

효율적인 인덱스를 디자인하는 것은 좋은 데이터베이스 및 애플리케이션 성능을 달성하는 데 중요합니다. 인덱스 부족, 과잉 인덱싱 또는 잘못 디자인된 인덱스가 데이터베이스 성능 문제의 주요 원인입니다.

Rowstore 인덱스는 행 형식으로 데이터를 구성하여 행의 모든 열을 동일한 페이지에 함께 저장하므로 전체 레코드를 검색하거나 자주 업데이트하는 트랜잭션 워크로드에 가장 적합합니다.

클러스터형 인덱 스는 키 값에 따라 테이블의 데이터 행을 정렬하고 저장합니다. 이러한 키 값은 인덱스 정의에 포함된 열입니다. 데이터 행 자체는 한 가지 순서로만 저장될 수 있으므로 테이블당 클러스터형 인덱스는 하나만 있을 수 있습니다.

비클러스터형 인덱 스에는 데이터 행과 별개의 구조가 있습니다. 비클러스터형 인덱스에는 비클러스터형 인덱스 키 값이 포함되며 각 키 값 항목에는 해당 키 값이 포함된 데이터 행에 대한 포인터가 있습니다. 테이블 또는 인덱싱된 뷰에 비클러스터형 인덱스를 여러 개 만들 수 있습니다.

-- Create clustered index on primary key (defines physical row order)
CREATE CLUSTERED INDEX IX_Product_ProductID 
ON Product(ProductID);

-- Create non-clustered index on frequently searched column
CREATE NONCLUSTERED INDEX IX_Product_Category 
ON Product(Category) 
INCLUDE (ProductName, Price);

클러스터형 인덱스는 실제 행 순서를 정의하고 정렬된 데이터에 대한 검사를 최적화하기 때문에 효율적인 범위 쿼리, 안정적이고 좁은 키 또는 ID 열 또는 날짜 필드와 같은 자연 정렬 순서가 필요한 경우에 가장 적합합니다.

비클러스터형 인덱스는 클러스터형 키에 맞지 않는 특정 조건자, 조인 또는 정렬 패턴에 대해 빠른 조회가 필요하거나 키 조회를 방지하기 위해 추가 열을 포함하여 쿼리를 처리하려는 경우에 이상적입니다.

데이터 중에서 선택하는 방법은 데이터에 액세스하는 방법에 따라 달라집니다. 기본 액세스 경로 및 비클러스터형 인덱스에 대해 클러스터형 인덱스를 사용하여 쓰기 작업 시 발생하는 비용의 균형을 유지하면서 대체, 고도로 선택적 또는 자주 쿼리되는 패턴을 지원합니다.

columnstore 인덱스를 이해하기

기존 rowstore 인덱스는 행 단위로 데이터를 저장합니다. 이는 개별 레코드를 검색하는 트랜잭션 시스템에 적합합니다. 그러나 수백만 개의 행을 검색하여 집계(SUM, AVG, COUNT)를 계산하는 분석 쿼리는 필요하지 않은 열을 읽는 데 시간을 낭비합니다. Columnstore 인덱스는 데이터 열을 열별로 저장하고 쿼리에 필요한 열만 읽어 이 문제를 해결하는 것을 목표로 합니다.

columnstore 아키텍처 이해하기

columnstore 인덱스는 각각 최대 1,048,576 개의 행을 포함하는 행 그룹으로 데이터를 구성합니다. 각 행 그룹 내에서 엔진은 각 열을 열 세그먼트 로 개별적으로 저장하고 독립적으로 압축합니다. 이 아키텍처를 사용하면 쿼리 최적화 프로그램에서 관련 없는 데이터를 완전히 건너뛰고 쿼리에 필요한 열만 읽을 수 있습니다.

데이터를 삽입할 때 작은 일괄 처리는 먼저 B+ 트리 인덱스를 사용하는 임시 rowstore 구조인 deltastore로 이동합니다. 델타 행 그룹이 충분한 행(최소 102,400개)을 누적하면 튜플 이동기 라는 백그라운드 프로세스가 이를 columnstore로 압축합니다. 102,400개 이상의 행을 대량으로 로드할 경우 해당 행들은 deltastore를 우회하여 columnstore로 직접 압축됩니다.

다음 표에서는 columnstore 인덱스에 대한 권장 사항을 설명합니다.

CCI(클러스터형 Columnstore 인덱스) 사용

CCI(Clustered Columnstore Index)는 기존 클러스터형 rowstore 인덱스를 대체하는 전체 테이블의 기본 스토리지 구조가 되는 columnstore 인덱스의 유형입니다. rowstore 테이블과 함께 보조 열 형식 복사본을 만드는 NCCI(비클러스터형 columnstore 인덱스)와 달리 CCI는 모든 테이블 데이터를 열 형식으로만 저장합니다.

테이블에는 기존 행 기반 스토리지가 없으며, 엔진이 각 열을 개별적으로 압축하여 저장합니다. CCI와 NCCI는 모두 동일한 열 형식 압축 및 일괄 처리 최적화를 사용하지만 분석이 기본 워크로드이고 행 수준 트랜잭션 액세스 패턴이 필요하지 않은 경우 CCI를 사용합니다. 반면 NCCI를 사용하면 트랜잭션 쿼리에 대한 rowstore 인덱스를 유지하면서 동일한 테이블의 분석 쿼리에 대한 열 형식 구조를 제공할 수 있습니다.

CREATE CLUSTERED COLUMNSTORE INDEX 문을 사용하여 클러스터형 columnstore 인덱스를 만들 수 있습니다. 예제는 다음과 같습니다.

-- Create clustered columnstore index (replaces clustered rowstore)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesHistory
ON SalesHistory;

-- Rebuild to improve compression
ALTER INDEX CCI_SalesHistory ON SalesHistory REBUILD;

NCCI(비클러스터형 Columnstore 인덱스) 사용

NCCI(비클러스터형 Columnstore 인덱스)는 기존 rowstore 테이블과 함께 선택한 열의 별도의 열 형식 복사본을 만들어 동일한 테이블이 트랜잭션 및 분석 워크로드를 효율적으로 처리할 수 있도록 합니다. 이 테이블은 빠른 단일 행 조회 및 업데이트를 위해 원래 클러스터형 rowstore 인덱스를 유지 관리하는 반면 NCCI는 분석 쿼리에 최적화된 열 기반 액세스를 제공합니다. 쿼리 최적화 프로그램은 쿼리 패턴에 따라 rowstore와 columnstore 구조 중에서 자동으로 선택합니다.

비클러스터형 columnstore 인덱스를 CREATE NONCLUSTERED COLUMNSTORE INDEX 문을 사용하여 만듭니다. 예제는 다음과 같습니다.

-- Create non-clustered columnstore for analytics
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Product_Analytics
ON Product(Price, StockQuantity, Category, ProductName);

columnstore 인덱스를 모니터링하다

동적 관리 뷰 sys.dm_db_column_store_row_group_physical_stats를 쿼리하여 columnstore 인덱스의 상태 및 성능을 모니터링할 수 있습니다.

다음 쿼리는 상태, 행 수, 삭제된 행 및 스토리지 크기를 포함한 행 그룹 통계를 보여 줍니다. 열린 행 그룹은 여전히 deltastore에서 삽입을 허용하고 있으며, 닫힌 행 그룹은 튜플-무버가 압축하기를 기다리고 있으며, 압축된 행 그룹은 데이터를 열 형식으로 저장합니다. 삭제된 행이 많거나 작은 행 그룹이 많을 경우, 이는 ALTER INDEX REORGANIZE를 통해 해결할 수 있는 조각화를 나타냅니다.

-- Check columnstore health
SELECT 
    object_name(object_id) AS TableName,
    state_desc,
    total_rows,
    deleted_rows,
    size_in_bytes / 1024 / 1024 AS SizeMB
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('SalesHistory');

인덱스 선택은 쿼리 성능과 쓰기 처리량 모두에 직접적인 영향을 줍니다. 초기 개발 중에 인덱스를 신중하게 디자인하여 프로덕션 환경에서 비용이 많이 드는 다시 빌드 및 성능 문제를 방지합니다.