MySQL 다중 컬럼 인덱스(Multi-column Index) 사용 시 주의할 점

kindof

2023. 8. 3. 22:54

0. 테스트 환경 구축

많은 실서비스에서 사용하는 DB에서는 2개 이상의 컬럼을 포함하는 인덱스가 흔히 사용됩니다. 이러한 인덱스를 다중 컬럼 인덱스(Multi-column Index)라고 하는데요.

이번 글에서는 MySQL에서 다중 컬럼 인덱스를 사용할 때 주의해야 할 몇 가지 포인트에 대해 정리해보려고 합니다.

먼저 테스트 환경 구축을 위해 테이블을 생성하고, 더미 데이터를 INSERT 하는 것부터 시작해보겠습니다.

테스트 환경은 아래와 같으며, MySQL Workbench를 사용합니다.

mysql> show VARIABLES LIKE '%VERSION%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| admin_tls_version        | TLSv1.2,TLSv1.3 |
| immediate_server_version | 999999          |
| innodb_version           | 8.0.33          |
| original_server_version  | 999999          |
| protocol_version         | 10              |
| replica_type_conversions |                 |
| slave_type_conversions   |                 |
| tls_version              | TLSv1.2,TLSv1.3 |
| version                  | 8.0.33          |
| version_comment          | Homebrew        |
| version_compile_machine  | arm64           |
| version_compile_os       | macos12.6       |
| version_compile_zlib     | 1.2.13          |
+--------------------------+-----------------+

[DDL]

CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `major` varchar(100) NOT NULL,
  `birth_date` datetime NOT NULL,
  `gender` enum('Male','Female','Other') NOT NULL,
  `minor` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

student(학생) 테이블은 Auto Increment한 id 컬럼을 PK로 가지며 이름, 전공, 부전공, 생년월일, 성별 컬럼을 가지고 있습니다.

이제 해당 테이블에 더미 데이터 1,000,000개를 생성하겠습니다. 편의를 위해 SQL 쿼리 대신에 JAVA 코드를 사용했습니다.

[build.gradle]

...

dependencies {
    implementation 'com.github.javafaker:javafaker:1.0.2'
    implementation 'mysql:mysql-connector-java:8.0.33'
}

[Dummy Data Generate]

public class DummyDataGenerator {

    public static void main(String[] args) {
        Faker faker = new Faker();
        Random random = new Random();

        String url = "jdbc:mysql://127.0.0.1:3306/test_db";
        String user = "XXXX";
        String password = "XXXX";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            String insertQuery = "INSERT INTO student (name, major, minor, birth_date, gender) VALUES (?, ?, ?, ?, ?)";
            PreparedStatement preparedStatement = conn.prepareStatement(insertQuery);

            for (int i = 0; i < 1000000; i++) {
                String name = faker.name().fullName();
                String major = faker.options().option("Computer Science", "Engineering", "Mathematics", "Physics");
                String minor = null; // 가끔씩 전공이 없는 학생도 있으므로 null로 처리
                LocalDateTime birthDate = getRandomBirthDate();
                String gender = faker.options().option("Male", "Female", "Other");

                preparedStatement.setString(1, name);
                preparedStatement.setString(2, major);
                preparedStatement.setString(3, minor);
                preparedStatement.setTimestamp(4, Timestamp.valueOf(birthDate));
                preparedStatement.setString(5, gender);
                preparedStatement.addBatch();
            }

            preparedStatement.executeBatch();
            preparedStatement.close();

            System.out.println("데이터 삽입 완료!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static LocalDateTime getRandomBirthDate() {
        int year = 1990 + new Random().nextInt(21); // 1990년부터 2010년까지의 랜덤 연도
        int month = 1 + new Random().nextInt(12); // 1월부터 12월까지의 랜덤 월
        int day = 1 + new Random().nextInt(28); // 1일부터 28일까지의 랜덤 일 (단, 간단한 예제를 위해 28일로 제한)

        return LocalDateTime.of(year, month, day, 0, 0);
    }
}

해당 코드를 수행한 뒤 student 테이블을 조회하면 아래와 같이 더미 데이터가 생성된 것을 확인할 수 있습니다.

select major, count(*) as student_num
from student
group by major;

1. 다중 컬럼 인덱스의 정렬

다중 컬럼 인덱스에서 각 인덱스를 구성하는 컬럼은 첫번째 컬럼부터 차례대로 정렬되고, N번째 인덱스 컬럼은 N-1번째 인덱스의 정렬에 의존해서 정렬됩니다.

예를 들어, 우리가 만든 student 테이블에 (major, name) 두 개 컬럼을 바탕으로 하는 인덱스를 생성해보겠습니다.

create index idx_major_name on student(major, name);
show indexes from student;

show indexes ... 쿼리를 통해 각 컬럼의 Seq_in_index, Cardinality, Index_type 등의 정보를 확인할 수 있습니다.

여기서 major 컬럼은 Cardinality가 3이기 때문에 중복된 데이터가 매우 많다는 사실, name은 거의 모든 값이 유니크하다는 사실을 예측할 수 있겠습니다.

다음으로, 인덱스의 각 컬럼의 정렬 방향은 아래와 같습니다.

major 컬럼이 먼저 오름차순으로 정렬되고, 그 뒤에 name 컬럼이 오름차순으로 정렬됩니다.

이와 같은 사실이 조회 성능에 어떻게 영향을 미칠 수 있는지 아래에서 테스트를 통해 검증해보겠습니다.

2. 다중 컬럼 인덱스의 스캔

[1] 인덱스 컬럼의 순서에 따른 성능 차이

먼저 살펴볼 내용은 다중 컬럼 인덱스 내의 컬럼 순서에 따른 성능 차이입니다.

인덱스를 모두 삭제하고 간단한 조회 쿼리를 수행해보겠습니다.

select * from student
where student.major = 'Mathematics'
and student.name = 'Jamaal Torp';

백 만개의 데이터가 있는 상황에서 major, name컬럼을 WHERE절에 포함시켜 단 건 데이터를 조회하는데 약 0.3초의 시간이 걸렸습니다.

이제 위에서 만들었던 (major, name) 인덱스를 생성하고 같은 쿼리를 실행해보겠습니다.

create index idx_major_name on student(major, name);

select * from student
where student.major = 'Mathematics'
and student.name = 'Jamaal Torp';

조회 시간이 약 0.0005초로 0.3초에 비해 엄청나게 짧아졌다는 것을 확인할 수 있는데요. major → name 컬럼 순의 인덱스 조회 시 유효한 데이터가 단 한 개밖에 존재하지 않았기 때문에 빠른 조회가 가능했다고 이해할 수 있습니다.

자, 그런데 쿼리 조건을 살짝만 틀어서 조회해보겠습니다. 전공이 'Mathmaetics'가 아닌, Jamaal Torp 학생을 찾는 쿼리입니다.

select * from student
where student.major != 'Mathmaetics'
and student.name = 'Jamaal Torp';

(major, name) 인덱스가 있음에도 조회 성능이 개선된 것 같은 느낌이 아닙니다.

이 상황에서, (name, major) 라는 컬럼의 순서만 반대인 인덱스를 생성하고 다시 조회해보겠습니다.

create index idx_name_major on student(name, major);

select * from student
where student.major != 'Mathmaetics'
and student.name = 'Jamaal Torp';

컬럼의 순서만 바꾼 인덱스를 생성하니 짧은 시간 내에 데이터 조회가 다시 가능해진 것을 볼 수 있는데요.

두 인덱스 내 컬럼 정렬 방식의 차이에서 위 현상의 원인을 이해할 수 있습니다.

먼저 기존 (major, name) 인덱스에서는 major != 'Mathematics' and name = 'Jammal Torp' 조건을 만족하는 데이터를 찾기 위해서는 사실상 전공이 Mathematics가 아닌 모든 데이터에 대해 name = 'Jammal Torp' 비교를 수행해야 합니다.

[Index Leaf Node]
major        |   name
Mathematics      XXXXXX
Mathematics      Jammal Torp
Mathematics      AAAAAA
Mathematics      BBBBBB
Mathematics      CCCCCC
...              ...

# 여기 아래 데이터를 모두 조회해야 한다.
Physics
...
...

전공이 Mathematics가 아닌 학생이 80만 명이라면 80만 번의 비교 연산이 필요하죠.

하지만 컬럼 순서를 바꾼 (name, major) 인덱스에서는 다릅니다.

[Index Leaf Node]
name        |    major
AAAAAA           Mathematics
BBBBBB           Mathematics
CCCCCC           Mathematics
Jammal Torp      Mathematics   [1] 
Jammal Torp      Physics       [2]
XXXXXX           Mathematics
...              ...

name 컬럼은 정렬되어 있기 때문에 Jammal Torp라는 학생이 발견되면 이름이 달라지는 학생이 나타나기 전까지만 데이터 스캔이 필요합니다.

실제 데이터에서는 Jammal Torp라는 학생은 한 명 뿐이었기 때문에 단 한 번의 데이터 비교로 쿼리가 끝나는 것이죠.

여기까지의 테스트에서 전달하고자 하는 내용은 한 가지입니다.

다중 컬럼 인덱스 내 컬럼의 순서는 성능에 큰 영향을 미칠 수 있으므로 어떤 방식의 조회가 자주 일어나는지, 필터링이 어떻게 될 지 고민해야 한다.

[2] 다중 컬럼 인덱스의 사용 가능 여부

두번째로 테스트할 내용은 위 내용을 제대로 이해했다면 간단하게 이해할 수 있습니다.

(name, major) 인덱스 하나가 존재한다고 가정하고 아래 쿼리를 실행해보겠습니다.

select * from student
where student.major = 'Mathematics'

(name, major) 인덱스가 존재하는 상황에서 major에 대한 조건만 WHERE절에 포함시켰습니다. 0.002초가 걸렸는데, 이 쿼리는 우리가 만든 인덱스를 활용했을까요?

EXPLAIN
select * from student
where student.major = 'Mathematics';

쿼리 실행계획을 확인해보면 위 쿼리는 우리가 만든 인덱스를 사용하지 않고 거의 모든 row를 스캔했다는 것을 볼 수 있습니다.

그 이유는 현재 인덱스가 다중 컬럼으로 구성되어 있기 때문에 name으로 먼저 정렬한 후, major 컬럼으로 정렬되었기 때문인데요. 선행하는 컬럼에 대한 조건이 없는 경우 작업의 범위를 결정할 '작업 범위 결정 조건'을 정하지 못해 인덱스를 사용하지 못하게 됩니다.

이 테스트에서 전하고자 하는 내용도 단순합니다.

다중 컬럼 인덱스에서 선행하는 컬럼에 대한 조건을 WHERE문에 포함하지 않는다면 인덱스를 사용할 수 없다.

3. 마무리

이번 글에서는 MySQL 다중 컬럼 인덱스를 사용할 때 주의할 점 두 가지를 정리해봤습니다.

다중 컬럼 인덱스 내 컬럼의 순서는 성능에 큰 영향을 미칠 수 있으므로 어떤 방식의 조회가 자주 일어나는지, 필터링이 어떻게 될 지 고민해야 한다.
다중 컬럼 인덱스에서 선행하는 컬럼에 대한 조건을 WHERE문에 포함하지 않는다면 인덱스를 사용할 수 없다.

이 외에도 LIKE 조건에 대한 인덱싱, 범위에 대한 인덱싱 등 인덱스를 사용할 때 주의해야 할 내용들이 많이 있는데요. 글이 너무 길어져서 다른 글에서 또 다른 테스트를 가지고 소개해보겠습니다.

감사합니다.

'MySQL' 카테고리의 다른 글

MySQL의 트랜잭션 격리 수준 실습해보기 (2)	2023.09.14
MySQL, InnoDB 엔진에서의 B-Tree 인덱스 구조와 클러스터링, 커버링 인덱스에 대해 (0)	2023.09.09
M1 Mac 환경에서 MySQL 사용 환경 구축하기 (0)	2023.06.03

JSH 기술 블로그