[DB] 클러스터

클러스터란?

디스크로부터 데이터를 읽어오는 시간을 줄이기 위해서 조인이나 자주 사용되는 테이블의 데이터를 디스크의 같은 위치에 저장시키는 방법 입니다.

클러스터의 장점

그룹된 컬럼 데이터 행들이 같은 데이터 Block에 저장되기 때문에 디스크 I/O를 줄여 줍니다.

클러스터된 테이블 사이에 조인이 발생할 경우 그 처리 시간이 단축 됩니다.

클러스터 키 열을 공유하여 한번만 저장하므로 저장 영역의 사용을 줄입니다. 

테이블 클러스터링의 특징

클러스터는 데이터 조회 성능을 향상 시키지만 데이터 저장, 수정, 삭제 또는 한 테이블 전체 Scan의 성능을 감소 시킵니다 

클러스터 하기 좋은 테이블

주로 조회가 자주 발생하고 수정이 거의 발생하지 않는 테이블

컬럼안의 많은 중복 데이터를 가지는 테이블

자주 Join되는 테이블 

클러스터 Key가 되기 좋은 컬럼

데이터 값의 범위가 큰 컬럼

테이블 간의 조인에 사용되는 컬럼 

클러스터 key가 되기 나쁜 컬럼

특정 데이터 값이 적은 컬럼

자주 데이터 수정이 발생하는 컬럼

LONG, LONG RAW 컬럼은 포함할 수 없습니다.

클러스터 인덱스와 논클러스터 인덱스

표에서 비교하는 내용들은 아래에서 자세히 설명되어 있습니다.

비교	클러스터 인덱스	논클러스터 인덱스
차이	물리적으로 행을 재배열	물리적으로 재배열 하지 않음
크기	인덱스 페이지 용량이 넌 클러스터드 인덱스 페이지 용량보다 작다.	클러스터드 인덱스 페이지 용량보다 큼
선택도	30% 이내에서 사용해야 좋은 선택도	3% 이내에서 사용해야 좋은 선택도
최대 갯수	테이블당 1개	테이블당 249개
	PRIMARY KEY 설정 시, 해당 컬럼은 자동적으로 클러스터 인덱스 생성	논클러스터 인덱스를 명시해야 생성 (인덱스페이지를 따로 만듬 - 로그파일에 저장)
		point query는 성능발휘되지만 range query는 장담 못함

차이

클러스터 인덱스는 물리적으로 행을 재배열합니다. 이 말 뜻의 의미를 이해하기 위해서 테스트 테이블을 생성한 후 데이터를 저장해 보았습니다.

아래와 같이 LOG_DATE, MEDIA_ID 두 개의 복합키는 자동적으로 클러스터 인덱스로 생성이 됩니다.

CREATE TABLE TBL_CLUSTERED_TEST ( LOG_DATE CHAR(8) NOT NULL, MEDIA_ID CHAR(1) NOT NULL, PROCEEDS DOUBLE DEFAULT NULL, PRIMARY KEY (LOG_DATE,MEDIA_ID) );

다음  5개의 테스트 데이터를 저장합니다. 첫 번째 저장하는 데이터의 날짜는 6월 18일이고, 마지막에 저장되는 데이터의 날짜는 6월 13일입니다.

INSERT INTO TBL_CLUSTERED_TEST (LOG_DATE, MEDIA_ID, PROCEEDS) VALUES ('20130618', 'A', 1000); INSERT INTO TBL_CLUSTERED_TEST (LOG_DATE, MEDIA_ID, PROCEEDS) VALUES ('20130619', 'A', 1000); INSERT INTO TBL_CLUSTERED_TEST (LOG_DATE, MEDIA_ID, PROCEEDS) VALUES ('20130619', 'C', 2000); INSERT INTO TBL_CLUSTERED_TEST (LOG_DATE, MEDIA_ID, PROCEEDS) VALUES ('20130619', 'B', 1000); INSERT INTO TBL_CLUSTERED_TEST (LOG_DATE, MEDIA_ID, PROCEEDS) VALUES ('20130613', 'B', 3000);

해당 테이블을 select 하면 insert 되어 있는 순서대로 데이터가 누적되지 않습니다. LOG_DATE, MEDIA_ID는 클러스터드 인덱스로 생성이 되어 있기 때문에 물리적으로 LOG_DATE를 정렬한 후 MEDIA_ID를 정렬하게 됩니다. 물리적으로 정렬을 한다는 말은 실제 DB의 데이터 파일에 정렬이 되어 있는 상태로 디스크에 저장이 된다는 것입니다.

 

테이블 조회를 해보면 아래와 같이 데이터가 정렬되어 있는 것을 확인할 수 있습니다. (6월 13일 데이터가 가장 위에 있음)

select * from tbl_clustered_test;   | LOG_DATE | MEDIA_ID | PROCEEDS | +----------+----------+----------+ | 20130613 | B | 3000 | | 20130618 | A | 1000 | | 20130619 | A | 1000 | | 20130619 | B | 1000 | | 20130619 | C | 2000 |

논 클러스터 인덱스는 일반적으로 SELECT문 성능 향상을 위해서 논 클러스터드 인덱스를 생성하여 사용하곤 합니다. 허나, 이 인덱스는 클러스터 인덱스와는 다르게 물리적으로 데이터가 정렬되어 저장되지 않습니다.

크기

논 클러스터 인덱스는 별도의 공간에 인덱스 테이블을 생성하여 데이터를 정렬합니다. 다음의 그림은 논 클러스터 인덱스에서 숫자 8을 어떻게 검색하는지에 대해서 보여줍니다.

다음의 그림은 클러스터 인덱스에서 숫자 8을 어떻게 검색하는 지에 대해서 보여줍니다.

클러스터 인덱스 이미 데이터가 정렬되어 있는 상태로 저장되기 때문에 leaf level의 인덱스 테이블이 필요하지 않습니다. 즉, 클러스터 인덱스는 논 클러스터 인덱스보다 DB 용량을 덜 차지합니다.

선택도

선택도는 테이블의 전체 데이터 집합 중에 특정 조건에 의해 데이터가 걸러지는 비율을 뜻합니다. 여기서 클러스터 인덱스는 테이블의 데이터가 물리적으로 정렬되어 저장되어 있기 때문에 부분 범위 처리에 활용하면 적은 I/O를 통해 원하는 데이터를 추출할 수 있습니다. 예를 들어 7, 8, 9의 데이터를 추출한다고 했을 때 클러스터 인덱스는 단 한 번의 I/O를 통해서 데이터를 추출할 수 있다. 왜냐하면 7, 8, 9는 3번 데이터 페이지에 모두 존재하기 때문입니다. (위 그림 참조)

 

논 클러스터 인덱스는 leaf level 인덱스 페이지에 테이블 데이터의 위치가 저장되어 있기 때문에 테이블 데이터로 직접 access가 가능합니다. 위의 예에서 설명한 것과 같이 논 클러스터 인덱스에서 7, 8, 9의 데이터를 추출하게 된다면 2번의 I/O가 발생합니다. 왜냐하면 7값은 2번 데이터 페이지에 존재하고 8, 9는 3번 데이터 페이지에 존재하기 때문입니다. 그래서 논 클러스터 인덱스는 선택도의 범위가 3% 이내일 때 효율적입니다.

최대 갯수

클러스터 인덱스는 물리적으로 정렬되어 있는 상태로 데이터가 저장되기 때문에 딱 1개만 생성 가능합니다. 만약 2개의 클러스터 인덱스가 생성된다면 데이터 정렬이 꼬이기 때문에 딱 한 개만 생성할 수 있는 것입니다. 이에 반해 넌 클러스터드 인덱스는 인덱스 테이블을 별도로 생성하여 데이터를 정렬하기에  충분히(249개) 생성할 수 있습니다.

용어

Table scan: 전체 검색

Index seek: 원하는 부분만 검색

Point query: 조회되는 데이터가 1~2개

select * from test where name = 'kim'

Range query: 조회되는 데이터가 다수

select * from test where date = '2015-01-01'

Covered query: 조회의 대상과 조회의 결과 컬럼이 동일한 상태. 인덱스 측면에서 가장 빠른 성능

select * from test where name = 'kim' and age = 20 -- Covered query 아님 select name from test where name = 'kim' and age = 20 -- Covered query