SQL Server 기본키 없이 인덱스가 존재할 때

기본키 없이 인덱스가 존재할 때 인덱스와 관련된 필드가 업데이트되면 엄청난 성능 저하를 경험할 수 있다.

이는 하드디스크의 디스크 엄청난 부하를 가져오게 되나, DBMS에서 실행계획이나 프로파일러 등을 통해서 확인이 매우 어려우며, 쿼리문만을 분석해서는 도무지 답이 나오지 않는다. 

이는 MSSQL에서 인덱스를 생성할 때 기본키가 존재하는 지에 따라 생성방식의 차이를 보이기 때문이다. 

위에서는 기본키와 인덱스로 표기했으나, 정확한 명칭은 클러스터형 인덱스와 비클러스터형 인덱스라고 설명하는 것이 더욱 타당하다. 

 

비클러스터형을 생성할 때 클러스터형 인덱스가 존재하는 경우와 아닌 경우에 따라 내부적으로 저장하는 방식이 다르다고 볼 수 있다.  

비클러스터형 인덱스의 생성 방식에 대한 내용은 많은 블로그를 검색한 결과 아래 블로그에서 가장 상세하게 설명하고 있다. 

mozi.tistory.com/320

[MSSQL] 클러스터 인덱스, 넌 클러스터 인덱스, 클러스터 인덱스 + 넌 클러스터 인덱스 구조

혹시 상위 블로그의 글이 삭제된다면, 아래 PDF에서 내용 확인이 가능하다. 

[MSSQL] 클러스터 인덱스, 넌 클러스터 인덱스, 클러스터 인덱스 + 넌 클러스터 인덱스 구조.pdf

1.25MB

 

위 블로그에서 설명한 것과 같이 논클러스터형 인덱스가 클러스터형 인덱스 없이 생성될 경우에 데이터 페이지 번호와 슬롯 번호를 저장하게 된다. 데이터의 삽입/삭제/수정으로 인해 데이터 페이지가 변경되거나 갱신이 발생하게 된다면, 인덱스의 Leaf Node에 저장된 데이터 페이지 번호와 슬롯 번호의 광범위한 수정이 발생하게 된다. 또한, 여러수개의 레코드가 변경된다면 , 각 레코드가 업데이트 될 때마다 인덱스의 갱신이 발생하게 된다. 

 

ex: N개의 레코드가 업데이트 될 경우

(첫번째 레코드 삽입) -> (인덱스의 데이터페이지 번호 및 슬롯번호 갱신) ->

(두번째 레코드 삽입) -> (인덱스의 데이터페이지 번호 및 슬롯번호 갱신) ->

...

(N-1번째 레코드 삽입) -> (인덱스의 데이터페이지 번호 및 슬롯번호 갱신) ->

(N번째 레코드 삽입) -> (인덱스의 데이터페이지 번호 및 슬롯번호 갱신)

 

따라서 논클러스터형 인덱스를 생성하게 된다면, 반드시 클러스터형 인덱스를 사전에 가지고 있기를 추천한다.