[MS-SQL] Clustered vs. Nonclustered Index Structures in SQL Server

SQL Server(MS SQL)의 클러스터드 인덱스와 넌클러스터드 인덱스 스트럭쳐

Clustered vs. Nonclustered Index Structures in SQL Server

CREATE TABLE dbo.PhoneBook
(
    LastName varchar(50) NOT NULL,
    FirstName varchar(50) NOT NULL,
    PhoneNumber varchar(50) NOT NULL
);

아무런 인덱스 없이 데이터가 쌓이는 것은 heap 이라고 한다. 데이터는 어떤 순서없이, 빈자리에 쌓이게 된다.

heap 에서는 어떤 조건에 맞는 row를 찾으려면, 모든 데이터를 scan 해야 한다. scan 중간에 조건에 맞는 row를 찾는다고 해도, 다른 조건에 맞는 row가 있을 수 있기 때문에, 전체를 끝까지 scan 해야만 한다. 매우 비효율적이다.

전화번호부에서는 성과 이름으로 정렬이 되어 있다. 이렇게 정렬하는 기준을 index key라고 한다.

테이블은 간단하게 성과 이름으로 정렬하여 일렬로 재배열 할 수 있다. 이렇게 하는 것만으로는 효율이 높아지지는 않는다. 단지 언제 scan 을 그만둘지는 알 수 있게 된다. 정렬된 키를 상위 인덱스 페이지에 모아서 트리구조를 만드는 것을 클러스터디 인덱스라고 한다. 이런 구조를 만들면, 전체 리스트를 훑지 않고, 인덱스 페이지 트리를 따라 찾아갈 수 있으므로 검색이 빨라진다.

CREATE TABLE dbo.PhoneBook
(
    LastName varchar(50) NOT NULL,
    FirstName varchar(50) NOT NULL,
    PhoneNumber varchar(50) NOT NULL
);


CREATE CLUSTERED INDEX IX_PhoneBook_CI
    ON dbo.PhoneBook(LastName, FirstName)

인덱스를 이용한 검색 과정은 전화번호부에서 이름을 가지고 원하는 사람의 정보를 찾아가는 과정과 비슷하다고 할 수 있다. 또한 클러스터드 인덱스를 사용하면, 데이터가 클러스터드 인덱스의 키를 따라 배치되기 때문에, 두가지 다른 클러스터드 인덱스를 사용하는 것은 불가능하다.

여기서 넌 클러스터드 인덱스가 나온다.

두번째 인덱스를 만들기 위해서는 새로운 클러스터드 인덱스 트리와 비슷한 인덱스 트리구조를 만들고, 최하위레벨에 베이스데이터가 있는 것이 아니라, 베이스데이터가 있는 위치를 가리키는 레퍼런스(포인터)를 가지고 있는 것이다. 이렇게 하여 베이스데이터 테이블의 정렬순서와 독립적인 넌클러스터드 인덱스 스트럭쳐를 구성할 수 있는 것이다.

CREATE TABLE dbo.PhoneBook
(
    LastName varchar(50) NOT NULL,
    FirstName varchar(50) NOT NULL,
    PhoneNumber varchar(50) NOT NULL
);  

CREATE NONCLUSTERED INDEX IX_PhoneBook_NCI
    ON dbo.PhoneBook(LastName, FirstName)

예제에서 베이스데이터는 힙이고, 레퍼런스는 RID 즉 로우아이디 이다. 테이블에 클러스터드 인덱스가 이미 있으면, 넌클러스터드 인덱스의 레퍼런스는 클러스터드 인덱스의 키값이 된다.

넌클러스터드 인덱스에는 모든 컬럼이 아닌 일부 컬럼정보만 들어있기 때문에, 넌클러스터드 인덱스 페이지가 포함하고 있지 않은 컬럼에 대한 조건검색을 하게 되면, 베이스데이터까지 다녀오는 부하가 발생할 수 있고, 쿼리 옵티마이저는 인덱스 대신에 힙스캔을 수행하게 할 수도 있다.

프라이머리 키 제약사항은 클러스터드 인덱스를 만들고, 유니크 키 제약사항은 넌클러스터드 인덱스를 만드는 것이 디폴트이다. 테이블에 키 제약사항을 설정할 때 이 사항을 기억하고 있어야 한다.

• http://lng1982.tistory.com/144 : 이 블로그포스팅도 참조.
• http://ksewookk.blog.me/100125150508
• https://www.littlekendra.com/2017/02/02/index-types-heaps-primary-keys-clustered-and-nonclustered-indexes-dear-sql-dba-episode-28/