클러스터 인덱스 & Non클러스터 인덱스

쿼리 튜닝을 하다보면 클러스터, 넌클러스터 인덱스에 대해서 자주나오는데

그 개념 잡기가 은근 어려웠는데 얼마전에 확실한 개념을 잡았음! ㅎ

아래 표는 간략한 비교

Clustered Index	Non-Clustered Index
테이블 당 1개 존재	테이블에 다수 개 존재
Lead Node가 Data	Leaf Node에 RID(Row ID)를 가짐
Unique Key를 가짐	Clustered Index가 존재하는 경우, RID는 Clustered Index key 칼럼

참고로 SQL Management Studio 에서 테이블을 만들고 Key를 지정하면 기본적으로 Clutered Index로 지정됨

이 방식은 비추함, 경우에 따라 Primary Key를 Non Clutered Index로 지정해야 하기도 하므로...

해당 테이블에서 인덱스를 걸면 가장 효율적일거 같은 컬럼을 클러스터 인덱스를 지정하는게 좋음

- 클러스터 인덱스

: 해당 컬럼을 기준으로 정렬, 테이블당 1개씩만 허용함

이 컬럼은 데이터 입력, 수정, 삭제시 항상 정렬을 유지한다.

책으로 비유하자면 페이지를 알고 있어서 바로 해당 페이지를 펼치는 것과 같음

가능한 한 UNIQUE 해야한다. UNIQUE 해야 한다고 해서 Primary key를 지정하라는건 아니다. (UNIQUE 하지 않은 경우 추가 4bytes 오버헤드발생)

사용빈도도 높고 데이타 길이가 짧은걸로 지정하는게 효율이 높다.

클러스터 인덱스는 넌클러스터 인덱스 뒤에 붙어서 다니다 보니 필드 사이즈가 크면 그만큼 효율이(IO) 떨어점

* sp_Colums를 이용해서 해당 테이블의 데이타 사이즈 확인! UNIQUE 하면서 사이즈 작은 필드 확인!

- 넌클러스터 인덱스

: 한테이블에 여러개를 지정할 수 있음

레코드 원본은 정렬 안됨

테이블당 240개 정도 만들수 있다

책으로 비유하자면 책 뒤에 목차에서 찾고자 하는 내용의 페이지를 찾고 그리고 나서 해당 페이지로 이동하는것과 같음

- 클러스터 & 넌클러스터 변경 예

* tb_master (table 명)

userid varchar(30) primary key

username varchar(50)

companyid varchar(50) primary key

regdate datetime

위와 같은 테이블 스키마에서 primarykey가 userid + companyid 이고 해당 pk가 클러스터 인덱스로 지정되어 있는 테이블이 있다

넌클러스터로는 regdate가 있다

userid + companyid 데이타 길이가 80 으로 효율적이지 못함 따라서 클러스터 인덱스인 userid + companyid 를 넌클러스터로 변경하고

regdate를 클러스터로 변경하는게 더 효율적임

- 쿼리 종류

1) 포인트 쿼리 point query

- 조회되는 데이터가 한두개

ex) select * from tb_master where userid ='hong' -- 값이 하나

2) 범위 쿼리 range query

- 조회되는 데이터가 다수

ex) select * from tb_master where startdate between '2000-01-01' and '2010-12-31' -- 값이 다수

3) 커버드 쿼리 covered query

- 조회의 대상과 조회의 결과 컬럼이 일치하는 상태

인덱스 측면에서 제일 빠른 성능을 냄

ex) select userid from tb_master

where userid='hong' and userdeptid ='support'

- 어떤 컬럼에 인덱스를 걸어야 할까?

1) where 절에 자주 사용되는 컬럼 , Like '%~~' 조심 %는 뒤에만 오게 해야 속도가 빨라짐

2) between A and B (클러스터인덱스가 유리)

--> 범위 쿼리문에서는 클러스터 인덱스가 유리

3) order by 가 항상 사용되는 컬럼

4) join으로 자주 사용되는 컬럼

5) 찾는 결과가 적은 쪽에...

--> 성별, 학년 이라는 필드가 있다면 학년으로 최초 조건을 거는편이 결과가 적게 나오므로 학년에 조건을 걸고 index를 지정함

6) insert, delete가 빈번한 컬럼은 인덱스에 좋은 영향이 아님

[출처] Clustered Index & Non-Clustered Index |작성자 빡스