[sql-server] 클러스트 인덱스 & 비 클러스트 인덱스

클러스트 인덱스 & 비 클러스트 인덱스 

먼저 인덱스에 대하여 정리하고 넘어가도록 하겠습니다.

인덱스는 테이블의 데이터 위치를 알려주는 역할을 담당합니다. 

우리가 인덱스를 사용하는 주 이유는 빠른 검색을 하기 위해서인데요. 

그렇기 때문에 데이터가 몇 건 되지 않은 테이블에서는 무리하게 인덱스를 생성하는 것은 비효율적입니다. 

그 이유는 다음 예제를 통해 설명할 수 있습니다. 

1. 데이터가 많을 경우

 성동구 AA아파트 BBB동 CCCC호에 사는 홍길동을 찾으려고 합니다. 

 여기저기 물어보는 것보다 동사무소에서 홍길동이 어디서 사는지 물으면 빠릅니다. 

 그 이유는 동사무소에는 그 지역 주민들 별로 주소를 관리하고 있기 때문입니다. 

여기서

[성동구 AA아파트 BBB동 CCCC호에 사는 홍길동] 은 데이터가 되는 것이고 

[동사무소에는 그 지역 주민들 별로 주소] 는 인덱스 페이지가 되는 것으로 이해면 됩니다. 

하지만 데이터가 적을 경우를 예로 들겠습니다. 

2. 데이터가 적을 경우 

시골마을에 가구가 3채밖에 존재하지 않는 곳이 있습니다. 

이때 홍길동 집을 찾아가는 것은 동사무를 거쳐서 가는 것보다 집접 방문하는 것이 더 빠른 길 일 것입니다. 

 

이와 같은 상황으로 과연 빠른 검색에 도움되는 것인가라는 전제하에 인덱스 설정 여부에 의문을 항상 가지고 설계해야 할 것입니다.

요즘 다루고 있는 SQL -Server 2012를 기준으로 인덱스에 대하여 설명을 하도록 하겠습니다. 

아직 다양한 개발을 해보지는 않았지만 우연치 않게 Oracle , Sql -Server , Mysql, Maria-db 다양한 것들을 써보게 되었네요 .. 

그러나 아직 깊게 알지는 못하기 때문에 많이 공부를 해야할 것 같습니다 ㅠ.. 

거두절미하고 인덱스 종류에 대하여 살펴보도록 하겠습니다. 

1. 인덱스 종류

Sql - server에서 인덱스의 종류는 클러스트 인덱스와 비클러스트 인덱스로 나뉩니다. 

1-1) 클러스트 인덱스

클러스트 인덱스는 자기 자신 테이블 내에서 인덱스를 가지고 정렬되는 것을 의미합니다. 

* 참고 

SQL-Server 데이터베이스에 포함되는 테이블 종류는 

1. Heap(힙)의 형태

2. 클러스트형 인덱스 

의 형태로 구분할 수 있습니다. 

1. Heap의 경우 데이터 페이지가 순서에 상관없이 무작위의 형태로 존재하는 테이블을 의미합니다.  

   이러한 Heap에 데이터가 추가될 때 중간에 빈 공간 아무곳이나 추가됩니다. 

   그렇기 떄문에 insert가 자주 발생되는 곳에 사용하기 좋습니다. 

2. 클러스트 형 인덱스는 Heap에서 발생하는 단점을 보완하기 위해 생성된 테이블이라고 보면 됩니다. 

   그 이유는 Heap의 경우 데이터의 순서가 무작위이기 떄문에 검색 시에는 전체 테이블을 뒤지는 그런 불상사가 나타나게 됩니다. 

   이는 데이터가 많다면 엄청난 성능저하의 원인이 될 수 있습니다. 

   그렇기 떄문에 클러스트 형 인덱스는 테이블 내에 자주 검색되는 열을 기준으로 데이터를 정렬해 가지고 있는 것입니다. 

  ( 예를 들자면 고객 테이블이 존재할 때 고객 이름순으로 테이블을 정렬 한다고 보면 됩니다.) 

  

클러스트 인덱스를 만들경우 Heap은 제거됩니다. 

1-2) 비클러스트 인덱스

클러스트 인덱스가 자기 자신 내에서 인덱스를 생성하는 것이 아닌 

즉 데이터를 찾기 위해 본인 테이블을 검색하는 방법이 아니라 인덱스만 따로 위치하는 공간을 가지고 있다는 것이 특징입니다. 

본인이 공부한 책에서는 인데스정보가 저장되므로 인덱스 페이지라고 표현하고 있습니다. 

이 인덱스 페이지에서는 테이블과 연결될 key값과 위치값(RID)을 가지고 있습니다. 

RID(Row Idenrifier)는 행의 주소로 [파일번호;페이지번호;슬롯]과 같은 형태를 가져 그 데이터가 어느 데이터 파일의 몇번째 페이지의 몇번째 줄에 기록되어 있는지 알려줍니다. 

아래의 그림은 비클러스트 인덱스에서 어떻게 숫자 8을 검색하는지 보여줍니다. 

                                                      루트레벨/ 루트 페이지           ->        중간 레벨     -> 리프레벨/리프페이지/퍼스트페이지  

 

1-2-1) 루트 레벨/ 루트 페이지

인덱스 페이지의 가장 상위 수준을 루트 레벨이라고 합니다. 

루트 레벨에 포함된 인덱스 페이지를 루트 페이지라고 부르며 루트 페이지는 인덱스의 정점이기 때문에 하나만 존재합니다. 

인덱스를 통해 검색을 할경우 가장 최상위인 루트 레벨에서부터 검색을 찾게되며 

이를 위해 SQL-Server는 내부적으로 각각의 인덱스의 루트 페이지에 대한 위치정보를 별도로 기록해 보관합니다. 

1-2-2) 중간 레벨

루트 레벨과 리프레벨(뒤에 나옴) 사이의 인덱스 수준을 중간 레벨이라고 부릅니다. 

중간 레벨은 인덱스 크기에 따라 존재할 수도 있고 존재하지 않을 수도 있습니다. 

중간 레벨에 새로운 중간 레벨이 추가되는 것은 쉽게 발생되지 않으며 엄청난 데이터가 추가되어야 중간 레벨 하나가 추가되며 일반적으로 1~2개 정도의 중간 레벨을 가지고 있습니다. 

1-2-3) 리프 레벨/ 리프페이지/ 퍼스트 페이지

인덱스 페이지의 최하위 수준을 리프 레벨이라고 부르며 리프 리벨에 포함된 인덱스 페이지를 리프 페이지라고 부릅니다. 

그리고 첫번째 리프 페이지는 퍼스트 페이지라 부르며 

인덱스란 결론적으로 데이터들의 위치 정보를 모아놓은 개체이기 때문에 인덱스의 리프 레벨이 인덱스 전체구조의 가장 중요한 역할을 하게 됩니다. 

2. 언제 어떤 인덱스를 선택해야하는가?

위에서 인덱스의 종류에 대하여 공부해보았습니다. 

그렇다면 우리들은 이 인덱스를 언제 어떻게 사용해야할지가 관건인데요.

여기서 많은 DBA들은 차이, 크기, 선택도, 최대 개수에 대한 기준으로 많이 판단한다고 합니다. 

	클러스터드 인덱스	넌 클러스터드 인덱스
차이	물리적으로 행을 재배열	물리적으로 재배열 하지 않는다.
크기	인덱스 페이지 용량이 넌 클러스터드 인덱스 페이지 용량보다 작다.	클러스터드 인덱스 페이지 용량보다 크다.
선택도	30% 이내에서 사용해야 좋은 선택도	3% 이내에서 사용해야 좋은 선택도
최대 갯수	테이블당 1개	테이블당 249개

                                                             ( 참고 URL : http://lng1982.tistory.com/144) 

* 선택도란 

테이블의 전체 데이터 집합 중에서 특정 조건에 의해 데이터가 걸러지는 비율을 의미합니다. 

여기서 클러스트 인덱스는 테이블의 데이터가 물리적으로 정렬되어 저장되어 있기떄문에 부분 범위 처리에 활용하면 적은 IO를 통해 원하는 데이터를 추출해 낼 수 있습니다. 

반면에 비클러스트 인덱스의 경우 리프레벨에 테이블 데이터 위치가 저장되어 있기 때문에 테이블 데이터가 있는 리프레벨까지 찾아간 뒤 해당 table에 접근할 수 있습니다. 

그렇기 떄문에 비클러스 인덱스에서는 선택도가 3%이내일떄 효율적입니다. ?? ( 3%는 너무 작은 범위인듯 ... 맞는 지 정확히 모르겠음) 

* 최대 개수

클러스트 인덱스는 물리적으로 정렬된 상태로 데이터가 저장되기 떄문에 딱 1개만 생성 가능하다. 

만약 2개의 클러스트 인덱스가 생성된다면 데이터 정렬이 꼬이기 떄문에 딱 1개만 생성할 수 있는 것이다.