DB

[Database][BTree] Clustered index & Non clustered index의 이해

Binceline 2016. 9. 25. 07:33




리그오브레전드 랭킹을 보여주는 사이트 구현한다고 생각해 보자.

테이블에 유저이름과 랭점이 하나의 Row를 구성하고, 이렇게 100만 개의 Row가 있다고 치자.

물론 아무것도 안 해 놨는데 데이터베이스가 자동으로 정렬을 하진 않았을 것이다.


어떤 이가 사이트에 접속을 했다.

이걸 그냥 ORDER BY로 처리할 수도 있을 것이다. 느리겠지만.

하지만.... 이렇게 계속 사이트에 접속하는 유저 한명한명 정렬해주다간 더더더 느려질 것이다...


그럼 당연히 생각나는 건 하나다.

'데이터를 넣을 때 정렬을 하게 하면 되지!'

그게 바로 index 방식에서 하는 일이다.



Clustered index : 데이터를 인덱스 + 물리적으로 모두 정렬되어 있음.


Non-Clustered index : 데이터를 인덱스로만 정렬해 놓음. 물리적으론 정렬하지 않음.



둘 다 정렬을 하고, 둘 다 BTree를 사용한다. BTree는 Balanced Tree의 약자다.(B+ Tree, B- Tree 등등 쓰던뎅)


그럼 이 둘의 차이는 뭘까. 물리적으로 정렬한 것과 안 한 것의 차이.


캐시 적중률.. 뭐시기 하는 키워드들이 뇌리를 스치운다ㅋㅋ


물리 메모리는 '페이지' 라는 메모리 덩어리의 기본 단위로 관리되는데, 연속된 메모리를 접근하면 같은 페이지 내에 존재할 확률이 높기 때문에, 


다른 페이지들에 막 접근할 필요가 없어서 속도가 빠르게 된다. 이걸 캐시 적중률이라고 함.



Clustered index key ?


예를 들어,

테이블에 랭크점수를 뜻하는 rankpoint 칼럼이 있고, Clustered index key로 지정되었다고 하고, 테이블에 데이터들을 마구마구 INSERT를 했다.


그럼 INSERT할 때마다 자동으로 정렬이 된다. rankpoint를 기준으로.


여기서 SELECT username FROM loltable WHERE rankpoint > 2500 이런 식으로 고수 플레이어들을 가려내서 가져오겠다..


Clustered index로 지정된 key, 그리고 그 row 자체가 value가 되며(최하위 노드의 Value만) 하나의 노드로 이루어지고 Btree에 정렬이 되어 저장이 된다.


트리에서 인덱스로 찾아가면 바로 물리 메모리! 게다가 이미 정렬되어 있어서 다른 페이지의 메모리들을 건드리지 않는다. 빠르다.


Btree특성상 같은 레벨의 노드끼리 탐색이 가능하기도 하고..


클러스터형 인덱스의 수준

출처 : https://technet.microsoft.com/ko-kr/library/ms177443(v=sql.105).aspx


여튼 빠르게 rankpoint 2500점 이상의 고수들을 정렬된 형태로 가져올 수 있다.




NonClustered Index 였다면?


기본적으로 물리적으로 정렬되어 있지 않기 때문에, 다른 페이지의 메모리를 건드려야 할 확률이 높다. (그치만 무조건이라 생각해야 한다)


즉, 느려진다.(말은 이렇게 했지만 인덱스 사용을 안 한, 100% 스캔하는 방식보다는 훨씬 빠르다. 정렬되어 있으니까!) 


하지만, 범위가 아니라 특정 데이터를 1개(Unique)만 콕 찝어서 가져오길 원한다면 정말 빠르고 효율적이다.


NonClustered 방식은 인덱스를 여러 개 설정할 수 있는데,  


그렇다는 건 중복되는 데이터를 포함하는 경우에 대해서 Index를 사용할 수 있다는 것이다. 이걸 결합 인덱스 라고 하던데..


이건 정말 잘 써야 한다. 예를 들면 


'항아리30개(보라색/25개 노란색5개) 중 보물이 들어 있는 보라색 항아리를 찾아야 한다. 보물이 들어있는 보라색 항아리 1개를 찾아라.

(보물이 들어 있는 항아리는 보라/노랑 합쳐서 총 3개다)'


1. 항아리 30개를 보면서 색깔을 검사한다. (스캔 30번)

2. 보물이 있는지 검사한다 (스캔 최대 25번 : 노랑항아리에 2개 있을 수도 있으니.)

- 최대 30 + 25번 스캔.


1. 항아리 30개를 보면서 보물이 들었는지 검사한다. (스캔 30번) -> 3개 찾음

2. 색깔을 검사한다. (스캔 최대 3번)  

- 최대 30 + 3번 스캔.


이런 경우 때문에 인덱스 순서를 잘 맞춰 주어야 한다.



비클러스터형 인덱스의 수준

출처 : https://technet.microsoft.com/ko-kr/library/ms177484(v=sql.105).aspx




단점이라면..


Index를 사용하게 되면 


INSERT/DELETE/UPDATE 시 부하가 크다. 


UPDATE를 할 경우엔 특히 Clustered가 NonClustered보다 부하가 크게 되는데,


Clustered는 물리적으로 정렬을 해야 하기 때문에 데이터 전체가 움직여야 할 테고, index 재구성을 하지만


NonClustered는 index 재구성만 하면 된다.


이 부분은 


Clustered&NonClustered Update에 대해


를 참고.




161226 추가 기억할 사항


Clustered index

- 범위 쿼리문

- order by문 자주 사용 예상될 시

- join으로 자주 사용되는 컬럼



정리해 보자면, 보통 책으로 비유해서 나도....

책 : 테이블

페이지 : 물리적인 데이터 페이지(메모리에서 말하는 페이지와 같음)


클러스터 인덱스 : 페이지를 다 뜯어서 어떤 기준대로 정렬해버린다. 페이지번호도 그에 따라 바꿔버림. 어떤 기준으로 정렬했는지를 알기 때문에, 마치 배열 엑세스해서 메모리에 바로 접근하듯 할 수 있게 됨.


그냥 인덱스 : 어떤 기준대로 정렬된 목차를 만든다. 그럼 이 목차를 거쳐서 특정 페이지로 이동한다. 목차에서 정렬되어 있다고 해서 범위 쿼리문을 사용하게 되면 실제로는 여러 페이지를 보아야 하는 것이기 때문에, 느려진다.


인덱스 없음 : 책을 한 페이지씩 넘기면서 직접 검색한다.

반응형