[COLUMNSTORE INDEX] SQL 프로파일러 이용하여 성능 테스트
테이블 생성하고
CREATE TABLE COLUMTABLE(
IDX INT NOT NULL,
CDATA INT NULL,
ODATA INT NOT NULL,
DATA1 VARCHAR(20) NULL,
DATA2 VARCHAR(20) NULL,
DATA3 VARCHAR(20) NULL,
DATA4 VARCHAR(20) NULL,
DATA5 VARCHAR(20) NULL,
DATA6 INT NULL,
DATA7 INT NULL,
DATA8 INT NULL,
DATA9 INT NULL,
DATA10 INT NULL,
DATA11 INT NULL,
DATA12 INT NULL,
DATA13 INT NULL,
DATA14 INT NULL,
DATA15 INT NULL,
DATA16 FLOAT NULL,
DATA17 FLOAT NULL,
DATA18 FLOAT NULL,
DATA19 FLOAT NULL,
DATA20 FLOAT NULL
);
DROP TABLE COLUMTABLE
데이터를 넣고
DECLARE @COUNT INT --INSERT 14:25
SET @COUNT = 1
-- SELECT @COUNT = 1
WHILE @COUNT < 5000000
BEGIN
INSERT INTO [dbo].[COLUMTABLE]
VALUES(
@COUNT,
CAST
(CONVERT
(VARCHAR,
DATEADD(DAY,ABS(CHECKSUM(NEWID()) % 365)
,'2020-11-24'),112)AS INTEGER),
CAST
(CONVERT
(VARCHAR,
DATEADD(DAY,ABS(CHECKSUM(NEWID()) % 365)
,'2020-11-24'),112)AS INTEGER),
'TEST' + CAST(ROUND(RAND()*100,0)AS VARCHAR),
'TEST' + CAST(ROUND(RAND()*100,0)AS VARCHAR),
'TEST' + CAST(ROUND(RAND()*100,0)AS VARCHAR),
'TEST' + CAST(ROUND(RAND()*100,0)AS VARCHAR),
'TEST' + CAST(ROUND(RAND()*100,0)AS VARCHAR),
ROUND(RAND()*100000,0),
ROUND(RAND()*100000,0),
ROUND(RAND()*100000,0),
ROUND(RAND()*100000,0),
ROUND(RAND()*100000,0),
ROUND(RAND()*100000,0),
ROUND(RAND()*100000,0),
ROUND(RAND()*100000,0),
ROUND(RAND()*100000,0),
ROUND(RAND()*100000,0),
ROUND(RAND()*100000,2),
ROUND(RAND()*100000,2),
ROUND(RAND()*100000,2),
ROUND(RAND()*100000,2),
ROUND(RAND()*100000,2)
)
SET @COUNT = @COUNT + 1
-- SELECT @COUNT = @COUNT + 1
END
GO
SELECT * 로 하는데 걸린시간 측정
SELECT * FROM COLUMTABLE -- 1:12
데이터 하나하나 찍어 걸린시간 측정(원래 4초정도 더 적게나옴)
SELECT -- 1:12
IDX,
CDATA,
ODATA,
DATA1, DATA2, DATA3,DATA4,DATA5,
DATA6, DATA7, DATA8,DATA9,DATA10,
DATA11, DATA12, DATA13,DATA14,DATA15,
DATA16, DATA17, DATA18,DATA19,DATA20
FROM COLUMTABLE
인덱스 생성
NONCLUSTERED INDEX
CREATE NONCLUSTERED INDEX
IX_ColumTable_NON
ON
ColumTable (data1, data16);
GO
NONCLUSTERED COLUMNSTORE INDEX
CREATE NONCLUSTERED COLUMNSTORE INDEX
IX_ColumTable_ColStore
ON
ColumTable (data1, data16);
GO
검색 성능 비교해보기
-- NONCLUSTERED INDEX 검색
SELECT DATA1, AVG(DATA16)
FROM ColumTable
GROUP BY DATA1
-- 힌트사용
OPTION (TABLE HINT(COLUMTABLE, INDEX(IX_COLUMTABLE_NON)))
-- NONCLUSTERED COLUMNSTORE INDEX 검색
SELECT DATA1, AVG(DATA16)
FROM ColumTable
GROUP BY DATA1
결과: 위(NONCLUSTERED INDEX) 아래(NONCLUSTERED COLUMNSTORE INDEX)
NONCLUSTERED COLUMNSTORE INDEX 가
NONCLUSTERED INDEX보다 성능이 훨 좋다!
columnstore 인덱스란?
Columnstore 인덱스는 columnstore 라는 칼럼 데이터 서식을 사용하여 데이터를 저장, 검색, 관리하는 기술입니다
columnstore
columnstore는 열과 행이 있는 테이블로 논리적으로 구성되는 데이터로, 열 데이터 서식으로 물리적으로 저장됩니다
Columnstore 인덱스를 사용해야 하는 이유
Columnstore 인덱스는 매우 높은 수준의 데이터 압축(일반적으로 10배)을 제공하여 데이터 웨어하우스 스토리지 비용을 크게 줄일 수 있습니다. 분석을 위해 columnstore 인덱스는 btree 인덱스보다 몇 배 더 나은 성능을 제공합니다. Columnstore 인덱스는 데이터 웨어하우징 및 분석 작업에 대한 기본 설정된 데이터 스토리지 형식입니다. SQL Server 2016(13.x)부터 운영 워크로드에 대한 실시간 분석에 columnstore 인덱스를 사용할 수 있습니다.
Columnstore 인덱스가 빠른 이유는 다음과 같습니다.
-
열이 동일한 도메인의 값을 저장하고 일반적으로 유사한 값을 가지므로 압축 비율이 높습니다. 시스템의 I/O 병목 상태가 최소화되거나 제거되며 메모리 사용 공간이 크게 감소합니다.
-
압축 비율이 높으면 메모리 내 사용 공간이 감소되어 쿼리 성능이 향상됩니다. 또한 SQL Server가 더 많은 쿼리 및 데이터 작업을 메모리 내에서 수행할 수 있으므로 쿼리 성능도 개선할 수 있습니다.
-
일괄 처리 실행은 여러 행을 함께 처리하여 쿼리 성능을 개선합니다(일반적으로 2~4배).
-
쿼리는 대개 테이블에서 적은 수의 열만 선택하므로 실제 미디어의 총 I/O가 감소됩니다.