SQL (20) - Clustered vs NonClustered

이번 포스트에서는 인덱스를 다루는 2가지 방식인 Cluster와 Non-Cluster에 대해 알아보겠습니다.

간단하게 2가지 방식은 다음과 같이 동작합니다.

Clustered의 경우 데이터가 정렬된 상태로 저장되고,

Non-Clustered의 경우 외부에서 관리하는 LOOK UP 테이블희 형태로 사용합니다.

이제 이 2가지 방식에 대해 더 자세히 알아보겠습니다.

 

1) Clustered와 Non-Clustered

1 - 1) Clustered

실제 물리적 데이터들이 Clustered 인덱스의 값에 따라 정렬됩니다.

또한 Heap Table이 아닌 Leaf Table에 Data Page들이 저장됩니다.
따라서 Clustered Index 키 순서로 정렬된 Data Page에서

Clusteted의 인덱스 값에 맞는 데이터를 검색할 수 있습니다.

 

1 - 2) Non-Clustered

Non-Clustered 방식은 2가지 경우에 따라 다르게 동작합니다.

 

1 - 2 - 1) Clustered Index가 없는 경우

데이터는 Heap Table에 저장됩니다.
데이터를 찾고 싶다면 Heap RID를 사용하여 Heap Table에 접근하여 데이터를 추출합니다.

 

1 - 2 - 2) Clustered Index가 있는 경우

Heap Table은 없고, 실제 데이터는 Leaf Table에 저장됩니다.
Heap RID도 존재하지 않고,

Clustered Index의 실제 키 값을 사용하여 Leaf Table에 접근하여 데이터를 추출합니다.

 

 

2) 예제

이제 실제 예제를 통해 Clustered 방식과 Non-Clustered 방식의 차이점을 알아보겠습니다.

간단한 데이터 테이블을 만들어보겠습니다.

USE Northwind;

SELECT *
INTO TestOrderDetails
FROM [Order Details];

SELECT *
FROM TestORderDetails;

 

2 - 1) Non-Clustered

-- 인덱스 추가
CREATE INDEX Index_OrderDetails
ON TestOrderDetails(OrderID, ProductID);

 

위와 같이 인덱스를 추가하였습니다.

-- 인덱스 정보
EXEC sp_helpindex 'TestOrderDetails';

 

위와 같이 Non-Clustered 상태의 인덱스가 추가된 것을 롹인할 수 있습니다.

 

-- 인덱스 번호 찾기
SELECT index_id, name
FROM sys.indexes
WHERE object_id = object_id('TestOrderDetails');

 

 

추가된 Non-Clustered 인덱스는 2번의 index_id를 갖고 있습니다.

 

-- 인덱스 조회
DBCC IND('Northwind', 'TestOrderDetails', 2);

 

 

	     9144
9104 9112 9113 9114 9115 9116

 

조회 결과는 위의 사진처럼 나왔고,
생성된 트리 구조를 간단하게 표현하면 위와 같습니다.

 

-- 페이지 조회
-- 페이지 조회에서 볼수 있는 PageType이 1인 경우 Data Page이고, 2인 경우 Index Page임
DBCC PAGE('Northwind', 1, 9104, 3);

 

 

현재는 Non-Clustered 인덱스만을 갖고 있습니다.
따라서 다음과 같이 Heap RID와 Heap Table이 구성됩니다.
데이터를 찾는데 사용하는 Heap RID는

([페이지 주소(=4바이트)] [파일ID=(2바이트)] [슬롯(=2바이트)] ROW)의 형태로 생성됩니다.
페이지를 담고 있는 Heap Table ({page} {page} {page} {page})도 다음과 같이 생성됩니다.
따라서 Heap RID를 사용하여 Heap Table의 페이지에 접근하여 데이터를 찾을 수 있습니다.

 

2 - 2) Clustered 인덱스 추가하고 Non-Clustered 인덱스 살펴보기

우선 Clustered 인덱스를 추가하겠습니다.

Create CLUSTERED INDEX Index_OrderDetails_Clustered
On TestOrderDetails(OrderID);

 

-- 인덱스 정보
EXEC sp_helpindex 'TestOrderDetails';

 

 

인덱스 정보는 위와 같이 구성되었습니다.

새로운 Clustered 인덱스가 생성되었습니다.

 

SELECT index_id, name
FROM sys.indexes
WHERE object_id = object_id('TestOrderDetails');

 

 

기존의 Non-Clustered 인덱스는 2번에 위치합니다.

 

-- 기존의　Non-Clustered 인덱스 조회 
-- Page 번호가 바뀌었음(물리 데이터들이 Clustered Index값에 의해 정렬됨)
DBCC IND('Northwind', 'TestOrderDetails', 2);

 

 

이전과 다르게 PagePID가 변화한 것을 확인할 수 있습니다.

 

-- 페이지 조회
-- PageId가 변경됨, Heap RID가 사라짐 그리고 새로운 열인 UNIQUIFIER가 추가됨
DBCC PAGE('Northwind', 1, 9128, 3);

 

 

페이지를 조회해보면

PageId가 변경, 사라진 Heap RID, 새로운 열인 UNIQUIFIER 추가를 확인할 수 있습니다.

같은 Clustered안에서 묶인 데이터를 정렬하는 용도로 UNIQUIFIER를 사용합니다.

 

2 - 3) Clustered 인덱스 살펴보기

-- 새로운 Clustered 인덱스 조회 
DBCC IND('Northwind', 'TestOrderDetails', 1);

 

 

Non-Clustered와는 다르게 페이지들이 실제 물리적 데이터를 저장하고 있음을 알 수 있습니다.

 

정리하면 Non-Clustered 방식과 Clustered 방식은 다음과 같이 동작합니다.

1) Non - Clustered

• 이진 Tree에서 루트 노드
• 자식 노드의 Heap RID
• Heap Table에 접근
• Heap Table에서 찾고자 하는 페이지

2) Clustered

• 이진 Tree에서 루트 노드
• 자식 노드의 Clustered Key
• Leaf Table에서 찾고자 하는 페이지