Clustered vs Non-clustered Index: 익숙하지만 익숙하지 않은 정의

오늘은 우리가 개발을 하면서 작업적으로는 이미 알고 있으나, 막상 누군가에게 설명하려고 하면 머뭇거려지는 부분인 Clustered Index와 Non-clustered Index에 대해서 정리를 해보고자 한다!

(사실, 이 개념은 이미 우리들이 알고 있는 개념들이다. 용어로 겁먹지 말자!)

Index

데이터베이스의 검색 속도향상을 위해, 유지 관리하는 구조

Clustered Index

한마디로 말하자면 우리가 흔히 알고있는 PK와 같은 개념이다.

특징

한 테이블 당 한 개만 생성 가능한 인덱스
PK를 설정한 경우, 기본값으로 PK가 클러스터드 인덱스로 설정되어진다. (테이블 구조에 영향을 미치는 인덱스이다.)
인덱스 자체적으로 Data가 저장되어 있는 구조
실제 물리적인 정렬순서와 인덱스의 정렬 순서가 같음. (데이터를 인덱스로 지정한 컬럼에 맞춰서 정렬한다.)
클러스터 키로 검색 시, 처리 성능이 매우 빠르다는 특징을 가지고 있다.
하지만 데이터의 입력/수정/삭제는 느리다. (인덱스를 생성/수정/삭제할 때 페이지 분할이 일어나며 느려진다.)

예시

만약 pk를 선언하지 않고 Fruit 테이블을 생성하여 정보를 저장했다고 가정하자. (Fruit Table: id / name)
1~8의 id를 역순으로 저장한 상태로, PK를 id 컬럼으로 지정하였다.
그런 후 다시 SELECT를 하게 되면, 기존에 pk값이 없던 상태에서 조회된 내용과 다르게 id(클러스터드 인덱스)는 해당 특징대로 생성됨과 동시에 정렬이 이뤄진다.

결과

40만 건으로 테스트를 해본 결과: 클러스터드 인덱스가 없는 경우는 0.219sec가 걸렸으나, 클러스터드 인덱스가 있는 경우는 0.000sec로 확연하게 결과 차이가 나게 되었다.

Non-Clustered Index: 보조 인덱스

한마디로 말하자면 우리가 흔히 알고있는 UNIQUE or UNIQUE ALL의 개념이다.

특징

한 테이블에 여러 개의 인덱스 생성이 가능한 인덱스
인덱스 자체적으로 Data를 가지고 있지 않으나, 해당 Data의 주소 값이 저장되는 구조 (인덱스 키 값에는 해당 데이터에 대한 포인터가 존재한다.)
실제 물리적인 정렬순서와 인덱스의 정렬 순서는 다르다. (인덱스의 구조는 데이터 행과 독립적이다.)
넌클러스터 키로 검색 시, 처리 성능이 클러스터 인덱스보다 느리다는 특징을 가지고 있다.
하지만 데이터의 입력/수정/삭제는 더 빠르다.
남용할 경우에는 시스템 성능을 떨어뜨리는 결과를 가져온다.

Clustered vs Non-clustered Index

	Clustered Index	Non-clustered Index
배열	행을 물리적으로 재배열 한다.	행을 물리적으로 배열하지 않는다.
크기	인덱스 페이지 용량이 작다.	인덱스 페이지 용량이 크다.
선택도	30% 이내 사용권장	3% 이내 사용권장
테이블 당 사용 갯수	1개	249개

B-Tree 자료구조: 어떻게 인덱스는 처리속도를 빠르게 만들어줄까?

인덱스가 없는 경우

해당 테이블의 모든 행을 Full-Scan하게 되며, 이런 경우 처음부터 끝까지 모든 행을 검사하기 때문에 시간이 굉장히 오랜시간 소요 됩니다.

인덱스가 있는 경우

B-Tree라는 자료구조로 생성된 인덱스 페이지에서 데이터를 찾게 됩니다.

예시로 1부터 27까지의 데이터가 저장되어 있는 테이블에서 7이라는 숫자를 어떻게 찾는지 확인해보겠습니다.

B-Tree 자료구조는 위와 같이 생성되어지게 되며, 순서는 아래와 같습니다.

최상위 노드에서 부터 탐색이 이뤄집니다. 최상위 노드에서 7이 9보다 작으므로, 9보다 작은 연산으로 보냅니다. [9 -> 6]
그 다음 노드에서 7이 6보다 크므로 6보다 큰 연산으로 보냅니다. [6 -> 7]
마지막 노드에서 대소관계를 비교해 7을 찾아 데이터를 가져옵니다.

위와 같은 흐름으로 1부터 27까지 모두 스캔하지 않고, 단 3번의 연산으로 결과 값을 빠르게 도출해냈습니다.

이와 같은 방식이 데이터를 조회하는 방식이 DB에서 인덱스를 통하여 데이터를 조회하는 것입니다.

그럼 논클러스터드 인덱스는 어떻게 처리될까?

논클러스터드 인덱스도 마찬가지로 B-Tree 자료구조를 통해서 DB를 조회하게 됩니다.

차이점은 인덱스에 데이터가 직접 저장되지 않고, 자료의 주소가 저장된다는 것입니다. (위의 차이표 참고)

인덱스 생성 시, 고려해야할 부분은 뭘까?

우선 인덱스는 만능은 아닙니다.

위에 특징들에서도 언급했듯 인덱스는 검색에는 유리하나, 데이터 작업 (insert, delete)와 같은 작업에는 불리합니다.

새로운 행이 추가 혹은 제거될 때, 위의 인덱스도 B-Tree 자료구조가 유지되어야 하기 때문에 insert, delete가 빈번하게 이뤄지는 테이블에서는 오히려 성능저하의 원인이 되기도 합니다.

이러한 이유들로 몇 가지를 정리해보고자 합니다.

업데이트가 많이 일어나는 컬럼은 인덱스 컬럼으로 선언하지 않는다.
1. 인덱스는 update시, 실제 update 작업이 아닌 사용하지 않음 처리가 되어 여전히 남아있다. 따라서 업데이트가 빈번하게 되면 데이터보다 인덱스가 더 많이 생성될 수 있다.
기본키 및 외부키(조인)로 인덱스를 생성한다.
지나치게 많은 인덱스는 오히려 많은 오버헤드를 초래한다.
분포도가 좋은 컬럼으로 선정한다. (분표도 = (인덱스 특정값의 행의 수 / 전체 행의 수) * 100)
조건절에서 자주 사용되는 컬럼
1. 조건절에서 자주 사용한다고 하여 무조건 선정해야하는 것은 아니지만, 자주 사용하지 않는 조건절의 컬럼을 굳이 인덱스로 선정하기보다는 자주 사용하는 조건절 컬럼이 더 좋다. (과도한 인덱스 추가는 성능저하의 원인이 될 수 있다.)
insert, update, delete 구문이 자주 사용되지 않는 테이블에서 사용해야한다.

Clustered & Non-clustered Index: 같이 쓸 수는 없을까?

실질적으로 클러스터 인덱스와 넌클러스터 인덱스를 같이 사용경우가 종종 있습니다.

검색은 테이블 안에 데이터가 있어야 유의미한 작업입니다.

그러기에 검색만 하는 데이터 or 삽입,수정,삭제만 하는 데이터는 존재할 수 없습니다.

그렇기 때문에 대부분의 경우, 하나의 테이블에 클러스터형 인덱스, 넌클러스터 인덱스를 같이 사용하게 됩니다.

(두 마리의 토끼를 잡아보자!)

예시

위의 그림은 넌클러스터 인덱스, 아래 그림은 클러스터 인덱스입니다.

넌클러스터 인덱스에는 원래라면 데이터의 주소값이 들어가게 되지만, 다른 데이터가 들어가 있는 것으로 확인할 수 있습니다.

이 데이터는 인덱스의 루트페이지의 Key값을 사용하여 실제 데이터가 존재해 있는 데이터 페이지로 이동할 것입니다.

이러한 구조의 장점은 데이터 삽입,삭제,수정 때문에 만들어지게 되었습니다.

위의 구조라면 데이터 페이지의 마지막에 새로운 데이터가 들어왔다고 해도 단순히 클러스터형 인덱스 페이지의 루트 페이지만을 참조하고 있기 때문에 데이터를 따로 수정할 필요가 없습니다.

이렇게 되면 데이터를 검색하는데는 약간의 성능저하가 있을 수 있지만 삽입,삭제,수정하는데 큰 성능상의 이점을 보이게 됩니다.

결론

클러스터드 인덱스는 PK -> 데이터
넌클러스터 인덱스는 UNIQUE -> 데이터 주소
인덱스는 B-Tree 자료구조를 기반으로 빠른 탐색을 수행하고 있다.
인덱스는 만능이 아니다. 고려할 부분들을 잘 고려해서 효율적인 검색을 만들어보자!

References

인덱스의 개념(클러스터, 논클러스터, B-tree)과 인덱스 성능 테스트

안녕하세요. 오늘은 인덱스에 대해 알아보겠습니다. 인덱스를 생성하면 DB의 검색성능이 크게 향상된다는 얘기를 많이 들어보았지만 그동안 제가 사용하고 관리했던 DB는 저장된 정보가 많지 않

developer111.tistory.com

DB - 클러스터드 인덱스, 넌 클러스터드 인덱스 차이점은?

cluster index vs nonclustered index 안녕하세요. 고코더 입니다. 면접 질문으로 가장 인기가 많은 질문입니다. "클러스터드 인덱스와 넌 클러스터드 인덱스의 차이점을 설명하시오" 항상 받는 질문이지

gocoder.tistory.com

저작자표시 비영리

노노아의 개발기 :9