MySQL, InnoDB 엔진에서의 B-Tree 인덱스 구조와 클러스터링, 커버링 인덱스에 대해

kindof

2023. 9. 9. 14:18

DB에서 인덱스는 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 그 대신 데이터의 읽고 속도를 높이는 기능입니다.

일반적인 애플리케이션에서 가장 많은 비율을 차지하는 API는 DB 조회와 연관되어 있기 때문에 인덱스를 적절하게 '잘' 사용하는 것은 애플리케이션 성능과 직결된 문제가 됩니다.

이번 글에서는 인덱스를 사용할 때 가장 기본적이고, 범용적으로 알아야 할 B-Tree 인덱스 구조에 대해 정리합니다. 그리고 B-Tree 인덱스와 연결지어 Mysql, InnoDB 아래서 클러스터링 인덱스, 커버링 인덱스란 무엇이며 어떻게 활용하는지 살펴보겠습니다.

1. InnoDB 엔진에서 B-Tree 구조와 데이터 탐색

B-Tree(Balanced Tree)는 이진 트리를 확장해서 더 많은 수의 자식을 가질 수 있도록 하며, 모든 리프노드들이 같은 레벨을 가질 수 있도록 자동으로 밸런스를 맞춰주는 트리 자료구조입니다.

위 그림은 일반적으로 인덱스를 B-Tree에 어떻게 저장하고 있는지를 보여주고 있습니다.

DB에서 인덱스와 실제 데이터가 저장된 데이터는 따로 보관되는데, 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주소값을 가지고 있습니다.

다만, 그림에서 알 수 있듯이 인덱스의 키 값은 정렬되어 있지만 데이터 파일의 레코드는 정렬되어 있지 않고 임의의 순서로 저장되어 있는데요. 인덱스는 테이블의 키 컬럼만 저장하므로 전체 데이터(Row)를 읽으려면 데이터 파일에서 해당 레코드를 찾아야 합니다.

하지만 InnoDB 에서는 위 설명이 적용되지 않습니다.

InnoDB 에서 세컨더리 인덱스는 리프 노드에 PK 값을 함께 저장하고 있으며 아래 그림처럼 인덱스에 저장되어 있는 PK 값을 이용해 PK 인덱스를 한 번 더 검색한 후, PK 인덱스의 Leaf Page에 저장되어 있는 레코드를 읽습니다.

즉, 모든 세컨더리 인덱스 검색에서 데이터 레코드를 읽기 위해서 반드시 프라이머리 키를 저장하고 있는 B-Tree를 다시 한 번 검색해야 하는 것이죠.

이게 무슨 말인지 조금 더 자세히 살펴보겠습니다.

2. 클러스터링 인덱스

MySQL 서버에서 클러스터링은 테이블의 레코드를 비슷한 것(PK 기준)들끼리 묶어서 저장하는 형태로 구현되는데, 이를 클러스터링 인덱스라 하며 주로 비슷한 값들이 동시에 조회되는 경우가 많다는 점에서 착안한 것이라고 합니다.

여기서 중요한 것은 PK 값에 의해 레코드의 저장 위치가 결정된다는 것인데요. 'PK 값이 변경된다면(PK 값이 빈번하게 바뀌는 것은 아니겠지만) 그 레코드의 물리적인 저장 위치가 바뀌어야 한다'는 것을 의미하기도 합니다.

아래 그림은 클러스터링 인덱스의 특성을 이해하기 쉽도록 구조를 나타낸 것입니다.

테이블의 구조 자체는 위에서 살펴본 B-Tree와 유사하지만, 세컨더리 인덱스를 위한 B-Tree 리프 노드(PK만을 포인팅하는)와는 달리 클러스터링 인덱스의 리프 노드에는 레코드의 모든 컬럼이 '같이' 저장되어 있음을 알 수 있습니다.

따라서, 만약 위 그림에서 emp_no = 10007 레코드가 10002로 변경되면 해당 데이터는 페이지 번호 '2'번으로 이동해야 하죠.

이 맥락에서, 세컨더리 인덱스가 실제 레코드가 저장된 주소를 가지고 있으면 클러스터링 키 값이 변경될 때마다 데이터의 레코드 주소가 변경되고, 그때마다 해당 테이블의 모든 인덱스에 저장된 주소값을 변경해야 합니다.

이런 오버헤드를 제거하기 위해 InnoDB 테이블(클러스터링 테이블)의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아닌 프라이머리 키 값을 저장하도록 구현되어 있습니다.

이에 따라, 결과적으로 InnoDB 엔진에서는 모든 세컨더리 인덱스 검색에서 데이터 레코드를 읽기 위해서 반드시 프라이머리 키를 저장하고 있는 B-Tree를 다시 한 번 검색해야 하는 단점이 있지만 PK로 조회 시 처리 성능이 매우 빠르고, 테이블의 모든 세컨더리 인덱스가 PK를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많아지게 됩니다.

그리고 이를 '커버링 인덱스'라고 하는데요. 이제 커버링 인덱스가 무엇인지 알아봐야할 것 같습니다.

3. 커버링 인덱스

인덱스만으로 쿼리를 수행할 수 있을 때 실행 계획의 Extra 컬럼에 "Using index"라는 메시지가 출력됩니다. 그리고 이렇게 인덱스만으로 처리되는 것을 커버링 인덱스(Covering Index)라고 합니다.

아래는 예제로 생성한 student 테이블입니다. 약 1,000만 개의 row가 있고 id 컬럼이 PK이며 name 컬럼이 인덱스로 설정되어 있습니다.

이 때, 아래 쿼리를 실행해보겠습니다.

SELECT name, birth_date
FROM student
WHERE name BETWEEN "A" AND "C";

위 쿼리는 약 0.06초가 소요되었고, 실행 계획을 보면 'Using index condition'라는 내용을 볼 수 있습니다.

이는 쿼리 옵티마이저가 'name' 이라는 필드를 통해 Index 스캔을 할 수는 있지만, 결국 birth_date 컬럼의 정보를 가져오기 위해서는 데이터 레코드에 대한 조회가 필요하다는 것을 의미합니다.

그런데 만약 위 쿼리 대신, 아래 쿼리를 실행한다고 해보겠습니다.

SELECT name
FROM student
WHERE name BETWEEN "A" AND "C";

이 쿼리는 약 0.001초 안에 실행되는데요. 실행 계획을 보면 Using where; Using index 라는 내용을 확인할 수 있습니다.

현재 인덱스로 사용 중인 name만을 SELECT 절에서 필요로 하기 때문에, 실제 데이터 레코드에 대한 스캔없이 인덱스만으로 쿼리가 온전히 실행될 수 있기 때문입니다.

즉, 커버링 인덱스를 사용해서 인덱스 테이블 조회만으로 쿼리를 끝낼 수 있다면 불필요한 테이블 스캔이 없어지기 때문에 쿼리의 성능이 매우 향상되는 것이죠.

이 내용을 조금 응용해보면, 아래와 같은 쿼리에 대해서도 커버링 인덱스를 적용할 수 있습니다.

EXPLAIN
SELECT *
FROM (
    SELECT id
    FROM student
    WHERE name between "A" and "C"
) as i 
JOIN student as s on i.id = s.id;

이름의 맨 앞글자가 "A"와 "C" 사이인 학생의 모든 컬럼 정보를 가져오는 쿼리입니다. 세컨더리 인덱스인 name은 PK값을 저장하고 있기 때문에 서브 쿼리 안에서 실행 계획은 "Using where; Using index", 즉 커버링 인덱스를 활용할 수 있습니다.

그리고 이 정보를 바탕으로 빠르게 id 만을 추출하여 작은 결과를 만든 뒤 쿼리를 종료시킵니다. 이후에는 Join을 통해 전체 컬럼의 데이터를 가져옵니다.

이 때, 위 쿼리를 아래와 같은 플랫한 쿼리로 수행하게 되면 어떨까요?

SELECT *
FROM student
WHERE name between "A" and "C";

실행 계획을 보면 "Using index condition" 라는 내용을 확인할 수 있습니다. 즉, name 인덱스를 사용하긴 하지만 실제 데이터를 가져오기 위해 name → PK 인덱스 탐색 → PK 인덱스를 통해 모든 테이블 레코드 조회 과정을 거치는 것입니다.

4. 정리 / Reference

이번 글에서는 MySQL, InnoDB 엔진에서 B-Tree 인덱스 구조가 무엇인지 살펴보고, 이를 바탕으로 한 클러스터링 인덱스와 커버링 인덱스에 대한 개념과 성능 테스트를 진행해봤습니다.

DB에서 데이터를 조회할 때 필요한 컬럼만을 프로젝션하는 것은 조회 성능에 중요한 영향을 미치는데, 적절한 인덱스의 설정과 커버링 인덱스 활용을 할 수 있다면 많은 도움이 될 것 같습니다.

감사합니다.

- [Real MySQL 개발자와 DBA를 위한 MySQL 실전 가이드] 백은빈, 이성욱 지음

- https://blog.devops.dev/performance-of-covering-index-in-mysql-1eb4b4d85e62

'MySQL' 카테고리의 다른 글

MySQL의 트랜잭션 격리 수준 실습해보기 (2)	2023.09.14
MySQL 다중 컬럼 인덱스(Multi-column Index) 사용 시 주의할 점 (0)	2023.08.03
M1 Mac 환경에서 MySQL 사용 환경 구축하기 (0)	2023.06.03

JSH 기술 블로그