[DB] 클러스터형 인덱스와 세컨더리 인덱스 그리고 활용(커버링 인덱스)

데이터베이스에서 데이터 검색 속도를 높이기 위해 우리는 인덱스를 사용하고, 인덱스는 클러스터형 인덱스(Clustered Index)와 비클러스터형 인덱스(Secondary Index)로 나뉜다.

본 포스팅에서는 클러스터형 인덱스와 세컨더리 인덱스에 대해 알아보고자 한다.

디스크 읽기 방식, 인덱스 그리고 옵티마이저까지 다룰 내용이 너무나도 방대하지만, 본 포스팅에서는 클러스터형 인덱스와 세컨더리 인덱스를 주를 이룬다.

 
 

클러스터형 인덱스(Clustered Index)

클러스터형 인덱스는 테이블 전체가 정렬된 인덱스가 되는 인덱스로 비슷한 레코드들을 묶어서 인덱싱된 형태를 말한다.
 
클러스터형 인덱스의 주요 특징은 다음과 같다.

1. 테이블의 프라이머리 키를 정의하면 해당 컬럼이 자동으로 클러스터형 인덱스가 된다.
2. 클러스터형 인덱스는 테이블당 하나만 가질 수 있다.
3. 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다.

 
특징 1번과 3번은 조금 더 자세히 알아보자.
 

테이블의 프라이머리 키를 정의하면 해당 컬럼이 자동으로 클러스터형 인덱스가 된다.

이 특징은 프라이머리 키를 지정했을 때를 가정하고 쓴 내용이다.
그런데 만약, 프라이머리 키를 지정하지 않았을 때 클러스터형 인덱스는 어떻게 될까.
 
InnoDB 스토리지 엔진인 경우 아래 우선 순위에 따라 프라이머리 키를 대체할 컬럼을 찾아 지정한다.

1. 프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터형 인덱스로 선택
2. NOT NULL과 UNIQUE로 지정한 컬럼을 클러스터형 인덱스로 선택
3. 1,2에 해당되지 않으면 자동으로 유니크한 값을 가지도록 증가되는 컬럼(GET_CLUST_INDEX)을 내부적으로 생성 후 클러스터형 인덱스로 선택

그러나 자동으로 컬럼을 추가하는 3번의 경우 사용자에게 노출되지 않고 쿼리 문장에 명시적으로 사용할 수 없다.
즉, 아무 의미 없는 숫자 값으로 클러스터링 되도록 설정되고 아무런 이점도 주지 않는다.
따라서 되도록이면 프라이머리 키를 명시적으로 설정하여 사용하도록 하자.

MySQL 5.x에서 이 내용으로 잘 설명한 포스팅이 있으니 대략적으로 더 알고싶다면 아래 링크를 참고하길 바란다.
https://omty.tistory.com/60

참고로 GEN_CLUST_INDEX는 8.x 버전에서는 information_schema.innodb_indexs 테이블에서 확인할 수 있다.
공식문서: https://dev.mysql.com/doc/refman/8.0/en/information-schema-innodb-indexes-table.html

 
 

프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다.

그 이유는 클러스터형 테이블은 항상 인덱스(프라이머리) 키를 기준으로 정렬된 상태를 유지하기 때문이다.
만약 프라이머리 키 값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀜을 의미한다.
 
(프라이머리 키의 값이 변경되는 경우는 거의 없지만) 프라이머리 키 값이 변경되면 어떠한 변화가 일어나는지 예시를 들어보겠다.
 
프라이머리 키가 10007인 데이터는 리프 페이지 3번에 위치하고 있고 프라이머리 키를 10007에서 10002로 변경하는 예시이다.

mysql> UPDATE test SET emp_no = 10002 WHERE emp_no = 10007;

 
변경한 이후의 테이블 구조는 아래 이미지와 같다.
프라이머리 키가 변경(10007 -> 10002)으로 변경됨에 따라 페이지 2번으로 변경된 것을 알 수 있다.
다시 말해, 프라이머리 키 값 변경으로 인해 레코드 전체가 다른 페이지로 이동하게 되었다.
 

이미지 출처: https://steady-coding.tistory.com/565

 
이처럼 프라이머리 키 값에 따라 저장 위치가 결정되므로 클러스터형 테이블은 프라이머리 키 값 자체에 대한 의존도가 상당히 크다. 
따라서 프라이머리 키 값을 정함에 있어 신중하게 정하고, 프라이머리 키 값이 바뀌지 않는 것을 권장한다.

참고
InnoDB를 제외한 MyISAM이나 다른 스토리지 엔진에서는 프라이머리 키나 인덱스 키 값이 변경된다고 해서 실제 데이터 레코드의 위치가 변경되지 않는다.

여담으로 잊을만하면 UUID를 프라이머리 키를 설정하는 것에 관한 걸 보게 된다.
보안적인 측면을 고려했을 때 UUID의 고유성이라는 특징을 활용해 적절한 상황도 있겠지만 일반적으로는 인덱스 키 값이 커지고, 아무런 의미를 갖지 않는 값이라 권장하지 않는다고 한다.
혹시라도 UUID를 (단일키든 복합키로든) 프라이머리 키로 사용했을 때의 좋고, 나빴던 경험이 있다면 댓글로 공유해 주시면 감사하겠습니다 :)

 
 

세컨더리 인덱스(Secondary Index)

세컨더리 인덱스는 물리적으로 테이블을 정렬하지 않는 대신 데이터 테이블과 별개로 정렬된 인덱스 페이지를 생성하고 관리한다. 세컨더리 인덱스는 비클러스터형 인덱스나 보조 인덱스라고도 불린다.
 
아래 그림이 그 예시이다.

이미지 출처: https://hudi.blog/db-clustered-and-non-clustered-index/

 
앞서 이야기한 것처럼 세컨더리 인덱스는 클러스터형 인덱스와 달리 인덱스 페이지와 데이터 페이지가 구분되어 있다.
즉, 세컨더리 인덱스는 실 데이터에 접근하려면 인덱스 페이지에서 데이터 페이지로 이동하여 실제 데이터 레코드를 가져오는 과정이 추가된다.
 
 
 

클러스터형 인덱스와 세컨더리 인덱스의 구조

클러스터형 인덱스와 세컨더리 인덱스의 구조에 대해서도 알아보자.

용어
각각의 노드를 데이터베이스에서는 페이지라고 부르는데, 각 페이지는 고유의 페이지 번호를 가지고 있다.

 
클러스터형 인덱스

 
클러스터형 인덱스의 구조는 세컨더리 인덱스 테이블(B-Tree)의 형태와 비슷하다.
그러나 세컨더리 인덱스의 리프 노드와는 달리 클러스터형 인덱스의 리프는 레코드의 모든 컬럼이 저장되어 있다.
 
emp_no 컬럼이 프라이머리 키로 지정된 클러스터형 테이블은 다음과 같은 구조가 된다.
 

이미지 출처: https://steady-coding.tistory.com/565

• 루트 노드: 키로 프라이머리 키를 가지고, 값으로 페이지 번호를 가진다
• 리프 노드: 키로 프라이머리 키를 가지고, 값으로 실제 데이터를 가진다

 
반면, 세컨더리 인덱스의 경우에는 어떨까
 
세컨더리 인덱스
세컨더리 인덱스는 프라이머리 키 이외의 컬럼에 적용된 인덱스로 특정 컬럼을 인덱스 키로 지정해 만들어진 인덱스 테이블 구조(B-Tree)는 다음과 같다.
 

이미지 출처: https://steady-coding.tistory.com/565

 
세컨더리 인덱스는 클러스터형 인덱스와는 다르게 리프 노드에 실제 데이터가 아닌 실제 데이터가 담긴 데이터 페이지의 주소를 갖는다는 걸 알 수 있다.

• 루트 노드: 키로 인덱스로 지정한 컬럼을 가지고, 값으로 페이지 번호를 가진다.
• 리프 노드: 키로 인덱스로 지정한 컬럼을 가지고, 값으로 실제 데이터가 저장된 데이터 페이지의 특정 행을 가리키는 주소를 가진다

 
 
따라서 클러스터형 인덱스의 장단점을 다음과 같이 정리할 수 있다.
 
클러스터형 인덱스의 장점 (빠른 읽기)

• 인덱스(프라이머리) 키를 기반으로 정렬되어 저장되기 때문에 넓은 범위 검색에 용이하다.
• 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 클러스터형 인덱스만으로도 처리 가능한 경우가 있다.

 
클러스터형 인덱스의 단점 (느린 쓰기)

• 프라이머리 키가 커지면 인덱스의 크기가 커져서 데이터베이스의 디스크와 메모리 부하가 발생할 수 있다.
• 변경 작업이 일어나면 프라이머리 키에 의해 저장 위치가 결정되기 때문에 처리 성능이 다소 느리다.

 
 

클러스터형 인덱스의 활용

이전 프로젝트를 진행할 때 페이지네이션의 성능을 개선하는 과정에서 커버링 인덱스라는 걸 알게 되었다.
 
커버링 인덱스란 원하는 데이터를 인덱스에서만 추출할 수 있는 인덱스(인덱스의 종류는 아님)를 의미한다.
쉽게 말해, 쿼리를 만드는데 필요한 모든 컬럼이 인덱스로 구성되어 있는 것을 의미한다.
 
여기서 중요한 것은 쿼리를 만드는 SELECT / WHERE / GROUP BY / ORDER BY 등에 활용되는 모든 컬럼이 인덱스여야 한다는 것이다. 즉, 데이터 블록을 보지않고도 필요 컬럼을 인덱스 레벨에서 검색할 수 있기 때문에 쿼리 성능 개선에 도움이 된다.
 
 
해당 프로젝트의 상품(product) 테이블의 데이터 레코드 수는 대략 1700만 건이 존재했고, 이 테이블을 대상으로 특정 조건에 맞게 페이지네이션을 해야 하는 상황이었다.

 
그리고 프라이머리 키(product_id)와 배송(delivery) 컬럼에 인덱스가 걸려있는 상태이다.

 
 
커버링 인덱스를 적용하지 않고 세컨더리 인덱스가 걸린 배송 컬럼으로 조회를 하더라도 프라이머리 키(클러스터 키)로 데이터 블록을 찾는 과정이 필요하기 때문에 조회 성능에 낭비되는 시간이 존재한다. 
(참고로 OFFSET 방식으로 인해 발생하는 성능 저하는 고려하지 않았다)

select * 
from product 
where delivery like '초고속 배송%' 
limit 5000000, 10

 
반면 커버링 인덱스를 적용하면 어떨까
커버링 인덱스를 적용할 경우 실 데이터에 액세스하는 시간이 없어지기 때문에 조회 쿼리에 낭비되는 시간 없이 데이터를 완성할 수 있게 된다.

select product_id, delivery 
from product 
where delivery like '초고속%' 
limit 5000000, 10;

 
커버링 인덱스를 적용하지 않았을 때와의 검색 성능 차이는 약 22배의 차이가 발생한 것을 알 수 있다.
 
각각의 실행 계획은 다음과 같다.

 
위 예시는 커버링 인덱스를 적용했을 때와 적용하지 않았을 때의 간단한 예시를 든거지만 복잡한 조건이 추가되거나 GROUP BY / ORDER BY 등이 적용된다면 실행 시간이 지금보다 월등히 더 차이가 날 것이다.
 
따라서 커버링 인덱스를 잘 활용할 경우 대용량 데이터에서도 유용할 것이다.
 
 

잘못된 내용이나 문제될 만한 내용이 있다면 댓글로 남겨주세요 :)

 
 
참고

• https://hudi.blog/db-clustered-and-non-clustered-index/
• https://steady-coding.tistory.com/565
• https://jojoldu.tistory.com/476
• Real MySQL 8.0 (이성욱 저)