[SQL] 인덱스(Index)를 알아야 정확한 실행계획을 알수있다

 

테스트 서버에서 개발을 완료하고 난 다음,

실제 서버에 배포하였는데 이게 뭐지...이게 뭐지 왜이렇게 리스트 페이지가 느려라는 생각이 들었습니다.

사실 테스트 서버에서의 데이터는 몇만건이었지만, 실제 서버의 데이터는 200만건정도였습니다.

그래서 부랴부랴, 프로시져에 작성한 쿼리를 실행계획을 보면서 수정하였습니다.

 

쿼리 잘짠다라고 생각했지만, 이렇게 기본적인 내용도 몰랐던 나에 대한 반성을 하면서 Index를 정리해봅니다.

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 으로 검색되어지는 테이블이 있다면, 그 테이블이 왜 힙을 사용하고 있는지 원인 분석이 필요한 것으로 보여집니다.

 

아래 유튜브 보면서 정리했고, 44분 정도 강의인데 시간나실때 보시면 좋은 강의였습니다.