MSSQL 컬럼스토어 인덱스 팁 (180GB 테이블을 컬럼스토어 인덱스로 변경한 결과)

MSSQL에는 컬럼스토어라는 특수한 저장 방법이 존재한다.

잘 사용하면 저장 공간과 속도 측면에서 엄청난 성능을 발휘할 수 있으며 적용 전에 컬럼스토어란 무엇인가에 대해서 알아보자.

컬럼스토어란?

MSSQL은 8KB 사이즈 페이지 단위로 데이터를 저장하고 읽는다. (페이지는 오라클의 블록하고 같은 개념이다.) 이 페이지 안에 테이블의 행이 저장되어 있는 구조이다.

예를들어 어떤 테이블에 컬럼이 많아서 한 ROW의 사이즈가 1KB이다.

그럼 조회결과가 100만건일 경우에는 8KB * 100만 = 약 7.6GB를 읽어야한다.

컬럼 스토어란 컬럼별로 데이터를 저장하여 컬럼들끼리 데이터를 모아놓는 것이다. 

쉽게 생각해서 ROW 스토어가 수평적으로 저장하면 컬럼스토어는 수직적으로 저장된다고 보면 된다.

이는 열이 동일한 타입이기 때문에 유사한 값을 저장하므로 압축 비율이 높고 일반적으로 테이블을 조회할 때 모든 컬럼을 SELECT 하지 않기때문에 실제로는 더 큰 효과를 낼 수 있다.

 

다른 DBMS에서 컬럼스토어를 흉내내려면 각 컬럼을 경우에 수에 따라 쪼개놓고 모든 컬럼에 인덱스를 만든다고 생각하면 될 것이다.

특이사항

1. 제약사항은 테이블당 1개만 생성이 가능하다. 

(사실 각 컬럼에 인덱스를 만드는 것과 같으므로 1개만 있으면 된다.)

2. 업데이트가 발생할 경우 데이터를 메모리에 올려놓고 압축을 풀고 업데이트를 하고 다시 압축해서 디스크에 쓰므로 비용 발생이 크다.

3. 컬럼이 1024개가 최대

4. ALTER INDEX, REORGRANIZE 불가하므로 삭제 후에 다시 생성해야됨

5. 메모리 제약(8MB X 컬럼 수 X DOP )

등이 있다.

컬럼스토어 권장 상황

• 1. 테이블 - ROW가 100만건 이상(압축률 보장)
• 2. 쿼리 - 집계형, GROUP BY, full scan이 자주 발생하는 테이블
• 3. 트랜잭션 - OLAP 환경. 데이터가 자주 변경되지 않는 테이블

로우스토어 권장 상황

• 1. 테이블 - 적은 ROW
• 2. 쿼리 - 집계형이 아닌 일반적 필터링 쿼리가 주로 사용
• 3. 트랜잭션 - OLTP 환경. 데이터가 자주 삽입, 삭제, 업데이트가 발생하는 테이블

정리하면 일반적으로 OLTP보다는 집계성 OLAP 환경에 더 적합하며 적절하게 사용하면 압축률이 좋아서 I/O 성능이 매우 향상된다.

사용 결과

실제로 180GB 테이블을 컬럼스토어 인덱스로 변경한 결과이다.

테이블 A와 테이블 B의 스키마에 컬럼을 추가할 일이 생겨서 스키마를 바꾸기로 했다.
크게 바뀐 것이 있다면 아무래도 테이블 특성 상 기간별 조회가 이루어지고 추후에 데이터를 지울 계획이라서 파티션을 줬고, 용량이나 성능을 향상시키기 위해 컬럼 스토어 인덱스로 변경하였다.

최근 기준으로 테이블 A는 하루 평균 150만건 정도 데이터가 쌓인다.
최근 테이블 B는 하루 평균 250만 건 정도 데이터가 쌓인다.

변경 전 테이블 A 스키마는 다음과 같다.

변경 전 테이블 B 스키마는 다음과 같다.

A 테이블을 다음과 같이 변경하였다. (10일파티션, 컬럼스토어 적용)

B 테이블을 다음과 같이 변경하였다. (10일파티션, 컬럼스토어 적용)

바뀐 점은 컬럼 하나를 추가하고(VARCHAR(10)) 10일 파티션을 주었으며 컬럼스토어 인덱스를 생성하였다.

변경 결과를 보자.

테이블 A와 테이블 B의 모습이고 인덱스 조각화가 없진 않을테니 인덱스 리빌드를 하면 차지하는 용량이 더 줄어들텐데 워낙 ROW수가 많다보니 리빌드 할 엄두가 안났고, 그냥 바로 새로운 테이블에 데이터를 붓기 시작했다.

다음은 변경 전 A, B 테이블 사이즈이다.

테이블 A는 ROW수가 8억건에 순수 데이터 사이즈만 52GB 이상이다.

테이블 B는 ROW수가 13억8천건에 순수 데이터 사이즈만 128GB 이상이다.

두 테이블 모두 약 2년 반정도의 데이터를 갖고 있었다.

테이블 A에서 테이블 A_NEW 에 붓는 작업은 약 2시간이 소요되었다.

테이블 B에서 테이블 B_NEW 에 붓는 작업은 약 4시간이 소요되었다.

결과는 다음과 같았다.
1. A테이블 52GB -> 22GB
2. B테이블 128GB -> 25GB

특히 테이블 B의 사이즈가 크게 작아진 것을 확인할 수 있었다.

아무래도 DISK에서 읽어야할 SIZE가 훨씬 줄어들었다보니 I/O에서 큰 성능을 보일 것으로 기대되며 데이터가 자주 변경되지 않는 집계성 테이블이라서 이런 결정을 할 수 있었다.