DW 시스템에서 대량 데이터 집계를 할 때 유용한 인덱스 유형이 추가되어 소개드립니다.
이번 글에서는 BOL 있는 Columnstore Indexes 특징/제약조건에 대해서 간단히 알아보고, 대량 데이터 집계 작업에서 얼마나 성능
차이가 있는지 테스트를 진행해보도록 하겠습니다.
추가적으로 Columnstore Indexes에 대해서 궁금하신 분이 계실 듯 하여 관련 자료를 하단에 첨부해 놓았으니 참고 부탁드립니다.
[특징]
1. Columnar data format
- 일반적으로 사용해왔던 rowstore format의 구조가 아니라 columnar data format을 구조를 가지는 인덱스입니다.
- Columnar data format 구조란 하나의 컬럼에 대해서 묶어서 저장한다는 점입니다.
(표현이 좀 모호한데, 아래의 그림 참고 부탁드립니다."Columnstore Indexes for Fast DW QP SQL Server 11" 문서에서 발취)
2. Faster query results (아래와 같은 이유로 빠르게 집계 및 연산이 되어짐)
- 필요한 컬럼만 읽는다. (디스크 I/O와 메모리 사용량을 최소화할 수 있음)
- 컬럼데이터에 대해서 압축이 된다. (인덱스 자체에 압축이 적용되어지기 때문에 페이지 압축을 사용불가함)
- 개선된 쿼리 처리 알고리즘을 사용하여 CPU 사용률이 낮다.
3. Clustered index key
- CL 인덱스가 있는 테이블의 경우 NC Columnstore Index 내에는 클러스터키 컬럼을 모두 가지고 있는다.
4. Partitioning
- 파티션 테이블에서도 사용 가능
5. Record size
- 인덱스 레코드 크기는 900 bytes를 초과할 수 없다.
[제약조건]
1. Columnstore index의 경우 CL로 만들 수 없다.
2. Unique index로 만들 수 없다.
3. View나 indexed view위에 만들 수 없다.
4. Primary key나 foreign key로써 사용할 수 없다.
5. Alert Index 구문으로 변경이 불가하다. Drop 후 re-create 로 처리해야 한다.
6. 인덱스 정렬을 위해서 asc, desc를 사용할 수 없다. (압축 알고리즘으로 인하여 정렬은 불가하다.)
7. 아래의 기능과 같이 사용 불가하다.
- Page and row compression
- Replication
- Change tracking
- Change data capture
- Filestream
[테스트]
1. 소개
- sys.sysobjects 테이블을 가지고 약 3500만건으로 뻥튀기한다.
- 해당 테이블에 일반 인덱스와 컬럼스토어 인덱스를 만든다.
- 각각의 인덱스를 사용하여 집계 쿼리를 수행하여 얼마나 시간이 소요되어지는지 확인한다.
2. 진행
2-1. 테스트 데이터 만들기
-- 테스트 데이터베이스 생성
create database testdb
use testdb
go
-- 임시 테이블 생성
-- drop table ColumnstoreTable
select
identity(int, 1, 1) as seqNo
, a.*
into ColumnstoreTable
from sys.sysobjects a
cross join sys.sysobjects b
cross join sys.sysobjects c
cross join (select top 500 * from sys.sysobjects) d
Result> 소요시간 : 3분 41초
(35153041개 행이 영향을 받음)
2-2. 인덱스 생성
-- 컬럼스토어 인덱스 생성
create columnstore index idx_ColumnstoreTable_name on ColumnstoreTable (name) ;
go
-- 일반 인덱스 생성
create index idx_ColumnstoreTable_name_normal on ColumnstoreTable (name) ;
go
2-3. name 컬럼 기준으로 몇건의 데이터가 있는지 집계 쿼리 수행
-- 컬럼스토어 인덱스 사용
select name, count(*)
from ColumnstoreTable with (readuncommitted)
group by name
-- 실행계획
-- 결과
-- 일반 인덱스 사용
select name, count(*)
from ColumnstoreTable with (readuncommitted, index(idx_ColumnstoreTable_name_normal))
group by name
-- 결과
3. 느낀점
- 집계 쿼리의 경우 일반인덱스와 비교자체가 불가할 정도로 빠릅니다. 위에 테스트 결과를 보더라도 거의 100배이상으로 빠른 것을 볼 수 있습니다.
일반 인덱스에 비하여 빠른 이유는 압축으로 인한 효과가 크지 않을까 생각해봅니다. 어차피 넌클러스터 인덱스도 해당 컬럼 데이터만 가지고 있기 때문에
해당 컬럼 데이터만 사용하기 때문이라는 이유로는 설명이 안됩니다. 중복데이터가 많은 경우 columnstore index의 효과가 더 높지 않을까합니다.
- CTP3 기준으로 "columnstore index가 있는 테이블의 데이터 변경이 불가하다"는 제약사항이 있어 운영환경에서 사용시 많은 제약이 따릅니다.
- 사이베이스에서 볼 수 있는 기능이였는데, 드디어 sql server에서도 볼 수 있게 되었네요.
- 개인적으로 오라클에서의 비트맵인덱스와 비슷하다고 생각을 하는데, 비트맵인덱스의 경우 온라인 상태에서 데이터 변경 및 추가가 가능하니까,
곧 columnstore index도 가능한 날이 오지 않을까 생각해봅니다.
(구조가 틀리기는 하지만 만들어지게 된 목적이나 컨셉은 비슷하다고 느껴집니다. 공감하기 힘들수도....)
[참고문서_columnstore Indexes]
Column_Store_Tutorial_VLDB09.pdf
sigmod08current-101103093816-phpapp02.ppt[참고자료]
- http://msdn.microsoft.com/en-us/library/gg509105(v=SQL.110).aspx
- http://www.b-eye-network.com/blogs/mcknight/archives/2010/11/microsoft_unvei.php
- http://cs-www.cs.yale.edu/homes/dna/talks/Column_Store_Tutorial_VLDB09.pdf
- http://www.slideshare.net/abadid/columnstores-vs-rowstores-how-different-are-they-really
- http://www.benjaminnevarez.com/tag/column-store-indexes/
- http://sql-articles.com/articles/general/columnstore-indexsql-server-2011/
- http://www.infoq.com/news/2011/03/Columnstore-index