클러스터 인덱스 vs 넌클러스터 인덱스

학과 공부와 시험을 준비하다가 클러스터 인덱스와 넌클러스터 인덱스를 정리해 본다.

개념

영어 사전을 생각해보자. 기본적으로 A~Z 알파벳 순으로 책 자체가 정렬되어 있다. 이건 클러스터 인덱스이다. 사전 마지막에는 “색인”이 있는데, 필요한 단어를 찾고, 거기에 적힌 페이지로 간다. 이게 넌클러스터 인덱스이다.

클러스터 인덱스

테이블 당 1개만 허용
기본 키 설정시 자동으로 만들어짐
테이블 자체가 인덱스 (클러스터 인덱스를 기준으로 테이블을 정렬하기 때문에 인덱스 페이지가 없다)
데이터 입력, 수정, 삭제 시 항상 정렬을 유지함
기본적으로 접근 성능이 좋음

넌클러스터 인덱스

테이블 당 최대 240개 생성 가능
인덱스 페이지를 별도로 저장
테이블 자체는 정렬되지 않고, 인덱스 페이지에만 정렬
성능 증가는 정말 “Case By Case”

부가적으로 설명하자면, 클러스터 인덱스는 항상 정렬을 유지하기 때문에 기본적으로 성능이 보장된다. AUTO_INCREMENT 같은 id에 클러스터 인덱스가 있어도 조회 성능이 향상된다. 하지만 테이블을 정렬 상태로 유지해야하므로 데이터를 입력, 수정, 삭제하는 경우에 즉각 정렬이 일어나기 때문에 느려진다. Trade Off라고 생각하면 된다. 따라서 DML 작업이 자주 일어나는 테이블에 클러스터 인덱스는 정말 신중하게 결정해야 한다.

넌클러스터 인덱스는 DML 작업이 일어나도 괜찮다. 별도의 인덱스 페이지가 있기 때문에 테이블 전체를 정렬하는 것보다 훨씬 낫다. 하지만 어디에 넌클러스터 인덱스를 걸어야 할 지는 정말 신중해야 한다. 바보같이 걸면 성능도 바보가 된다. 인덱스가 없는 것보다 느려질 수도 있다.

활용

클러스터 인덱스

쿼리를 기준으로 예를 들면,

SELECT year(hire_date), count(*)
FROM employees 
WHERE hire_date >= '1997-01-01'
GROUP BY year(hire_date);

조건절에 hire_date가 있고, 범위 탐색이다. 이 경우 hire_date에 클러스터 인덱스를 보여하면 성능이 엄청나게 향상된다. 여기서 WHERE 절만 빼보자.

1
2
3

SELECT year(hire_date), count(*)
FROM employees 
GROUP BY year(hire_date);

여기서 마찬가지로 hire_date에 클러스터 인덱스를 건다면? 성능 향상에 도움이 안되거나, 데이터가 많아지는 경우 오히려 느려진다. 스캔 방식을 생각해야 한다. 클러스터 인덱스가 없는 경우 기본적으로 Heap 테이블 스캔이 이루어진다. 클러스터 인덱스가 있는 경우에는 클러스터 인덱스 스캔이 이루어진다. 하지만 조건절이 없으므로 무식하게 다 읽는건 Heap 테이블 스캔이 빠르다.

넌클러스터 인덱스

위의 예시와 같은 쿼리이다.

SELECT year(hire_date), count(*)
FROM employees 
WHERE hire_date >= '1997-01-01'
GROUP BY year(hire_date);

여기서 hire_date에 넌클러스터 인덱스를 건다면? 놀랍게도 클러스터 인덱스를 걸었을 때보다 더 빠르다. 이 경우 때문에 구글링을 2시간은 한 것 같다. 내가 내린 결론은, 넌클러스터 인덱스가 탐색 범위에 포함되었기 때문에 옵티마이저에서는 인덱스 스캔이 아닌 Non-Clustered Index Seek 방식을 선택한다. Index Scan은 인덱스의 모든 행을 인덱스 순서로 읽는 반면에, Index Seek은 필터 기준에 따라 일치하는 행이나 한정된 행만 찾으려고 리프 노드를 거치기 때문에 논리적 읽기 수가 훨씬 감소한다.(고 한다)

하지만 포인트 쿼리인지 범위 쿼리인지, 범위 쿼리라면 조건절의 탐색 범위는 얼마나인지에 따라서 경우가 달라진다. 결국 실무에서 클러스터링 최적화를 위해서는 테스트를 거친 후 적용해야 하고, 클러스터 인덱스를 걸 때는 인덱스를 거는 행동만으로 데이터 정렬에 비용이 소요되므로 신중하게 결정해야 한다.