안녕하세요. SQLER의 코난 김대우입니다.
이번 강좌에서는, 12-4. 인덱스 생성과 관리 - 클러스터형 인덱스를 진행 하겠습니다.
SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록
이번에 진행할 강좌는 인덱스 생성과 관리 - 클러스터형 인덱스입니다.
TL;DR
클러스터형 인덱스의 개념과 특징을 설명하며, 데이터를 물리적으로 재배열해 빠르게 검색을 수행하는 과정을 다룹니다. 클러스터형 인덱스는 한 테이블당 하나만 생성 가능합니다.
클러스터형(Clustered) 인덱스
클러스터형 인덱스는 인덱스의 리프 레벨(Leaf level)이 데이터 페이지와 일치하는 인덱스입니다. 처음 인덱스를 공부하면 당연히 이해가 안 됩니다. 아래와 같은 데이터를 생각해 보세요.
이미지 - 클러스터형 인덱스 데이터 구조
클러스터형 인덱스는 물리적으로 행을 재배열해 인덱스 리프 레벨이 차곡차곡 오른쪽의 데이터 페이지처럼 쌓이게 구성합니다. 다음으로 인덱스를 확인합니다. 인덱스가 설정된 컬럼의 값이 순차적으로 재배열되므로 왼쪽에 보시는 인덱스 페이지는 단지 키값과 페이지의 번호만 저장하면 됩니다.
자 그럼 검색을 해 볼까요? 회원번호 5번 구교환을 찾으려 한다고 가정해 보겠습니다.
클러스터형 인덱스의 값을 보니 5번은 4 < 5 < 7 사이에 위치합니다. 따라서, 페이지 번호 2, 2번 페이지로 가면 됩니다. 2번 페이지로 가서 바로 5번 구교환을 찾습니다.
이미지 - 클러스터형 인덱스 검색
위와 같은 순서로 5번 구교환을 찾습니다. 만약 12번 이하늬를 찾는다면 12번은 10 < 12 < 13 이므로 4번 페이지로 가서 바로 12번 이하늬를 찾을 수 있습니다.
이런 깔끔한 검색 과정이 가능한 이유가 뭘까요? 바로 물리적으로 행이 재배열되어 있기 때문에 가능합니다. 만약 물리적으로 행이 재배열되어 있지 않다면? 이런 검색 작업은 불가능합니다. 또한, 물리적으로 행이 재배열되어 있으므로 범위 검색에도 대단히 유용합니다. 이 말은 선택도가 어느 정도만 높아도, 클러스터형 인덱스의 경우 약 30~35% 선택도의 쿼리도 인덱스를 이용해 데이터를 검색하고 조회할 수 있습니다.
클러스터형 인덱스 아직 완전히는 잘 모르겠지만 꽤나 훌륭하고 좋아 보입니다. 클러스터형 인덱스가 회원 테이블에 하나 생성되어 있는데요. 만약 클러스터형 인덱스를 하나 더 회원 테이블에 만들고 싶다면 어떨까요? 안타깝게도 클러스터형 인덱스는 행을 물리적으로 재배열해 테이블당 1개만 생성할 수 있습니다. 당연하겠죠? 이미 테이블이 정렬된 상태로 재구성되어 있으므로 추가는 불가능합니다.
오직, 테이블에 단 1개의 클러스터형 인덱스만 생성이 가능하니 신중하게 선택하세요.
그럼 앞의 예제 SQL 쿼리를 클러스터형 인덱스로 생성하겠습니다.
--클러스터형 인덱스 생성 CREATE CLUSTERED INDEX idx_id ON IndexDummy (idx); GO --인덱스 정보 조회 sp_helpindex 'IndexDummy'; GO
이전에 진행했던 쿼리를 다시 수행해 보세요. IO와 실행계획을 눈여겨보시길 바랍니다.
--클러스터형 인덱스 생성 후 같은 쿼리를 다시 수행 --IO통계 표시 설정 SET STATISTICS IO ON; SELECT * FROM IndexDummy; -- 실행계획 확인 SELECT * FROM IndexDummy WHERE idx = 5000; --IO통계? SELECT * FROM IndexDummy WHERE idx < 10; --IO통계? SELECT * FROM IndexDummy WHERE idx < 40; --IO통계? SELECT * FROM IndexDummy WHERE idx < 1000; --IO통계? --IO통계 표시 끄기 SET STATISTICS IO OFF;
Clustered Index Scan과 Clustered Index Seek 두 가지 실행계획이 나옵니다.
SQL Server는 클러스터형 인덱스에 대해서 대단히 높은 우선권을 부여합니다. 예를 들어 Clustered Index Scan을 해도 좋을 것 같은데... Clustered Index Seek로 검색하는 경우도 있습니다. 이미 말씀드린 대로 SQL 쿼리 최적화기가 판단하는데 테이블의 크기가 작거나(로우의 건수가 아닌 전체적인 크기) 또는 클러스터형 인덱스의 키값 컬럼이 작을 경우 index Seek를 수행할 수 있습니다.
실제 현업에서는 주로 범위 검색에 종종 이 클러스터형 인덱스를 두게 되므로 예제와 같은 케이스를 만나기는 쉽지 않으실 거에요. 또한 클러스터형 인덱스는 비교적 비클러스터형 인덱스보다 크기가 작습니다. 아울러 클러스터형 인덱스는 크기를 대략적으로 예측할 수 있는 인덱스이기도 합니다. 그럼 비클러스터형 인덱스도 확인해 보겠습니다.
SQL 강좌 책 구매
강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다.
책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022
