[MSSQL] 클러스터 인덱스, 넌 클러스터 인덱스, 클러스터 인덱스 + 넌 클러스터 인덱스 구조

인덱스

---

인덱스는 데이터를 빠르게 검색할 수 있게 해주는 객체입니다.

컬럼을 오름차순 혹은 내림차순으로 정렬한 후에 빠르게 찾을 수 있도록 도와줍니다.

책의 색인을 의미하죠.

그렇다고 인덱스를 무작정 생성하는 것도 좋은 방법은 아닙니다.

인덱스를 만들게 되면 인덱스를 위한 디스크 공간이 필요하며, 인덱스가 있는 테이블은 DML 작업 시 더 많은 비용과 시간을 필요로 하기 때문입니다.

이러한 이유로 인해서 인덱스를 만들 때 해당 테이블의 용도를 정확히 이해한 후에,

적절한 컬럼으로 Clustered Index 와 Non Clustered Index를 구성해야 합니다.

인덱스를 만드는 과정

---

데이터페이지를 인덱스 키 값으로 정렬을 한 후 리프레벨부터 인덱스페이지를 만들어 갑니다.

인덱스페이지가 다 차게 되면 새로운 인덱스 페이지를 만들어 정렬된 데이터를 넣게 되며, 페이지들은 양방향 링크드로 연결됩니다.

리프레벨이 완성이 되면 상위의 넌 리프 레벨들을 만들며, 상위 레벨은 하위 레벨의 각 첫 행의 데이터를 가져와서 만듭니다.

마찬가지로 상위 레벨의 인덱스 페이지가 다 차게 되면 새로운 인덱스 페이지를 만들어 위의 방법으로 구성합니다.

이렇게 최상위 레벨에 인덱스페이지가 한개가 될 때까지 진행됩니다.

루트 페이지 : 최상위 레벨 페이지

넌 리프 페이지 : 최상위 / 최하위 레벨이 아닌 중간 레벨의 페이지

리프 페이지 : 최하위 레벨 페이지

이제 간단한 예시와 함께 구성해 보겠습니다.

CREATE TABLE dbo.MOZI

(

  C1 INT,

  C2 CHAR(2) )

GO

INSERT INTO dbo.MOZI VALUES (9, '자')

INSERT INTO dbo.MOZI VALUES (10, '차')

INSERT INTO dbo.MOZI VALUES (11, '카')

INSERT INTO dbo.MOZI VALUES (4, '라')

INSERT INTO dbo.MOZI VALUES (1, '가')

INSERT INTO dbo.MOZI VALUES (13, '파')

INSERT INTO dbo.MOZI VALUES (2, '나')

INSERT INTO dbo.MOZI VALUES (5, '마')

INSERT INTO dbo.MOZI VALUES (6, '바')

INSERT INTO dbo.MOZI VALUES (7, '사')

INSERT INTO dbo.MOZI VALUES (3, '다')

INSERT INTO dbo.MOZI VALUES (8, '아')

INSERT INTO dbo.MOZI VALUES (12, '타')

INSERT INTO dbo.MOZI VALUES (14, '하')

GO

위 쿼리를 수행하면 데이터 페이지에는 다음처럼 저장이 되게 됩니다.

( 아래 사진은 정말 그렇게가 아닌, 설명하기 편하게 위해 이상적인 상황을 그려놓은 것 입니다. 실제로는 이렇지 않습니다. )

넌 클러스터 인덱스

---

데이터페이지와 같은 인덱스페이지를 구성하게 구성하게 됩니다.

인덱스페이지에는 인덱스를 걸어준 컬럼의 키 값과 데이터페이지 번호 슬롯 번호가 저장됩니다.

CREATE INDEX MOZI_C1 ON dbo.MOZI ( C1 )

C1 컬럼에 인덱스를 생성하면 데이터페이지에 있는 C1 을 정렬하여 인덱스 페이지를 구성합니다.

SELECT * FROM dbo.MOZI WHERE C1 = 8

이 후 C1 = 8 에 대해서 조회를 하면, 인덱스페이지에서 해당 데이터의 데이터페이지 위치를 찾은 후,

데이터페이지에서 결과를 반환하게 됩니다.

1. 먼저 루트 레벨에서 8 데이터가 있을 위치를 찾습니다.

    5보다는 크고 9보다는 작기 때문에 데이터는 인덱스페이지 202 에 있음을 알 수 있습니다.

2. 202 페이지에가서 C1 는 8 인 데이터를 찾으니 103 데이터페이지에 4번째 슬롯 위치에 있음을 알 수 있습니다.

3. 103 데이터페이지 4번째 슬롯에서 C1 = 8 의 데이터를 찾은 뒤, '아' 의 값을 같이 반환합니다.

클러스터 인덱스

---

데이터페이지 자체가 인덱스 키 값에 의해 물리적으로 정렬이 되게 됩니다.

즉, 데이터페이지는 리프 레벨이라고 볼 수 있습니다.

CREATE CLUSTERED INDEX MOZI_C1 ON dbo.MOZI ( C1 )

SELECT * FROM dbo.MOZI WHERE C1 = 8

먼저 8 은 5와 9 사이에 있기 때문에 102 페이지에 있음을 알 수 있습니다.

102 페이지로 접근한 뒤 해당 페이지에서 8의 데이터를 찾아 반환합니다.

클러스터 인덱스 + 넌 클러스터 인덱스

---

C1 에는 Clustered Index 를 C2 에는 Non Clustered Index 를 생성한 후에 C2 에 대해 조회하는 경우를 알아보겠습니다.

CREATE CLUSTERED INDEX MOZI_C1 ON dbo.MOZI ( C1 )

CREATE INDEX MOZI_C2 ON dbo.MOZI ( C2 )

위와 같이 구성하는 경우 Non Clustered Index 의 Bookmark 에는 Clustered Index Key 가 저장됩니다.

이것이 Clustered Index Key 가 긴 경우 넌 클러스터 인덱스의 키 길이에 영향을 미치게 되는 이유입니다.

SELECT * FROM dbo.MOZI WHERE C2 = '다'

1. C2 는 Non Clustered Index 이기 때문에 넌 클러스터 인덱스 페이지를 먼저 탐색합니다.

2. 루트페이지에서 '다' 는 301 페이지에 있습니다.

3. 301 페이지에서 '다' 의 Clustered Index Key 는 3 입니다.

4. 클러스터 인덱스 루트 페이지에서 3 은 101 페이지에 있습니다.

5. 101 페이지에서 3 의 데이터에 접근하여 데이터를 가져옵니다.

물론, 지금과 같은 경우에는 Non Clustered Index 에 Clustered Key 값도 포함되어 있기 때문에 Clustered Index 페이지까지 찾아가지 않아도 됩니다.

이렇게 출력되어야 하는 모든 컬럼이 인덱스 구성에 포함되어 있는 경우를 Covered Index ( 커버드 인덱스 ) 라고 부릅니다.

실제 플랜으로도 출력해 봐도, Clustered Index 에는 접근하지 않는 것을 확인할 수 있습니다.

위와 같은 접근 방법을 위해서는 커버드 인덱스가 구성이 되어서는 안됩니다.

CREATE TABLE dbo.MOZI

(

  C1 INT,

  C2 CHAR(2),

  C3 CHAR(3) )

GO

INSERT INTO dbo.MOZI VALUES (1, '가', '가')

INSERT INTO dbo.MOZI VALUES (2, '나', '나')

INSERT INTO dbo.MOZI VALUES (3, '다', '다')

INSERT INTO dbo.MOZI VALUES (4, '라', '라')

INSERT INTO dbo.MOZI VALUES (5, '마', '마')

INSERT INTO dbo.MOZI VALUES (6, '바', '바')

INSERT INTO dbo.MOZI VALUES (7, '사', '사')

INSERT INTO dbo.MOZI VALUES (8, '아', '아')

INSERT INTO dbo.MOZI VALUES (9, '자', '자')

INSERT INTO dbo.MOZI VALUES (10, '차', '차')

INSERT INTO dbo.MOZI VALUES (11, '카', '카')

INSERT INTO dbo.MOZI VALUES (12, '타', '타')

INSERT INTO dbo.MOZI VALUES (13, '파', '파')

INSERT INTO dbo.MOZI VALUES (14, '하', '하')

GO

CREATE CLUSTERED INDEX MOZI_C1 ON dbo.MOZI ( C1 )

CREATE INDEX MOZI_C2 ON dbo.MOZI ( C2 )