index

페이지(Page)란?

• 데이터 파일을 구성하는 논리 단위

• SQL Server의 기본 데이터 저장 단위(8KB)

• 데이터를 쓸 때 행을 페이지에 기록됨

• 데이터를 읽을 때 페이지 내의 모든 행이 읽어짐

• 페이지 내의 행이 많을 수록 I/O 효율 증가

• 0 ~ n 사이의 순차적인 번호 -> 페이지 번호

데이터를 INSERT 하게 된다면 페이지에 들어가게 되고, 테이블에 쿼리를 날려 조회하는 것이 아니라 페이지에서 SELECT하는 것으로 생각하면 됩니다. 그러므로 페이지라는 것을 숙지해야합니다.

 

인덱스(Index) 는 왜 사용하고 장점은 무엇인가?

• 빠른 데이터를 검색하기 위해서입니다. 찾는 데이터를 가지고 있다면 직접 주거나, 없다면 어디 있는지 알려줍니다.

• 데이터의 중복을 방지할 수 있습니다. (Primary Key, Unique)

• 잠금을 최소화 시켜줍니다. (동시성을 높여줍니다.)

 

인덱스(Index) 의 단점은 무엇인가?

• 물리적인 공간을 차지하게 됩니다. (인덱스도 테이블처럼 데이터를 가지므로 물리적인 공간을 차지하게 됩니다.)

• 페이지를 가지고 있는 존재는 데이터와 인덱스 두가지입니다. (프로시져/뷰는 사이즈가 없습니다.)

• 인덱스에 대한 유지관리 부담이 존재합니다.

• 데이터가 극히 적다면은 얻는 효과보다 유지관리 부담이 더 클 수 있습니다.

테이블의 존재 형태는 아래 그림 처럼 두 가지로 나뉩니다. 힙(heap)과 클러스터 형 인덱스 입니다.

 

 

 

 

힙(Heap) 이란?

• 정렬의 기준이 없이 저장 된 테이블의 형태를 말합니다.

• 데이터 페이지 내의 행들 간에 순서가 없고, 페이들 간에도 순서가 없습니다.

• 클러스터 형 인덱스가 없는 테이블이라고 생각하시면 쉽습니다.

힙의 장점과 단점은 무엇인가?

• 장점은 INSERT 문이 좋아하는 테이블 형태입니다. 새로운 행을 기존 페이지의 빈 곳에 추가하면 되고, 빈 공간이 없으면 새로운 페이지에 추가하면 되기 때문입니다.

• 단점은 SELECT 문이 싫어하는 테이블 형태입니다. 데이터가 극히 적으면 상관없겠지만, 데이터가 많으면 전체적으로 모든 테이블을 스캔해야하기 때문에 데이터를 찾기가 어렵습니다.

위에서도 말했듯이, 원하는 데이터를 찾기 위해서는 Table Scan 을 해야 합니다. 전체적으로 페이지를 다 읽는겁니다. 전체적으로 모든 페이지를 다 읽는다고 하니, 당연히 좋지 않습니다.

 

클러스터 형 인덱스란?

• 특정 열(또는 열들)을 기준으로 데이터가 정렬되어 있습니다. (물리적 정렬이 아닌, 논리적 정렬)

• 테이블 당 하나의 클러스터 형 인덱스를 설정 가능합니다.

 

 

 

다시 한번 말하면, 테이블의 형태는 두가지로 나뉘게 됩니다.

정렬되어 있지 않은 힙과 특수한 Key 값으로 정렬되어진 클러스터 형 인덱스로 나누어집니다. 우리는 선택을 해야 합니다. 힙을 사용할지, 클러스터 형 인덱스를 선택할지. 힙이 무조건 좋지 않다라고 말할 수 없습니다. 상황마다 다르기 때문에 그 상황에 맞게 테이블을 설계하는 것이 개발자의 몫입니다.

 

 

클러스트 형의 데이터 찾기

이제부터 많은 용어가 나옵니다. 그러한 용어는 필수적으로 숙지해야합니다.

 

 

 

• Clustered Index Seek : Root 페이지부터 찾아가서, 아주 빠른 성능을 보여줍니다.

• Clustered Index Scan : 모든 데이터 페이지를 읽기 때문에 힙에서 사용했던 방식인 Table Scan과 다를바 없습니다.

힙 + 비 클러스터 형 인덱스

• 비 클러스터 형 인덱스에는 인덱스 키 열의 모든 데이터와 RID(행의 주소)를 가지고 있습니다.

• RID를 가지고 있는 이유는 RID는 거의 변하지 않고 크기가 크지 않기 때문에 한번에 찾아갈 수 있기 때문입니다.

• 999개 까지 만들 수 있습니다.

• Index Seek : NonClustered 가 생략되어졌고, Root 페이지를 이용하기 때문에 성능이 좋습니다.

• RID Lookup :  비 클러스터형 인덱스에 RID 값이 저장되어 있기 때문에, 힙을 검색할때에는 RID값을 이용해서 검색하기 때문에 RID Lookup이 됩니다.

 

 

 

클러스터 형 인덱스 + 비 클러스터 형 인덱스

• 비 클러스터 형 인덱스에는 클러스터 형 인덱스 키 열과 인덱스 키 열의 모든 데이터를 가지고 있습니다.

• 클러스터 형 인덱스의 키 열은 거의 변하지 않습니다. (힙의 RID는 변할 가능성이 높습니다.)

• 999개 까지 만들 수 있습니다.

• Key Lookup : 비 클러스터 형 인덱스가 가지지 못한 데이터를 찾아가는 과정입니다.

 

 

 

결론

아래 그림으로 한방에 정리됩니다. 테이블은 클러스터 형 인덱스가 잡혀있는 테이블과 아닌 테이블로 구성되어 진다는 것을 알 수 있습니다.

그리고 비 클러스터 형 인덱스가 결합되어지면, 힙에는 RID 값을 가지고 있고 클러스터 형 인덱스는 키 값을 가지고 있습니다. 비 클러스터 형 인덱스에서 힙으로 검색될 때에는 RID Lookup, 클러스터 형 인덱스에 검색 되어 질 때에는 Key Loopup 이라는 용어가 사용되어 집니다.

현업에서 RID Lookup 으로 검색되어지는 테이블이 있다면, 그 테이블이 왜 힙을 사용하고 있는지 원인 분석이 필요한 것으로 보여집니다.