MySQL 복합 인덱스 순서 자동 지정, 이거 진짜에요?

Backend/MYSQL

MySQL 복합 인덱스 순서 자동 지정, 이거 진짜에요?

Bombo_ 2025. 5. 12. 08:16

728x90

배경

MySQL에서 복합 인덱스를 지정하면 Where 절에는 복합 인덱스에 순서에 맞게 조건 절을 지정을 해주어야 의도한대로 동작하는 것으로 이해를 하고 있었습니다. 그런데, 어느 날 오랜만에 만난 개발자 지인분께서 갑자기 이런 말을 해주시는겁니다.

"형, 그거 알아? 복합 인덱스를 지정하고 MySQL 컬럼 순서를 순서대로 지정하지 않아도 알아서 조건 절을 최적화해서 인덱스를 태울 수 있도록 바꿔준다?"

진짜. 이 말이 나올 수 밖에 없다. "그게 진짜에요?" 나도 모르는 사이에 놓쳤을 수 있고, 만약에 진짜 된다면 정말 좋은 기능이기에 얘기를 듣고 넘어가는 것이 아닌 직접 테스트를 진행해봤습니다.

환경 준비

먼저, 테스트를 진행하기 위한 적절한 데이터 양을 가진 테이블이 필요하기때문에 TEST용 mysql schema를 찾아서 넣어주어야 했습니다. 찾아보니 깃허브에 https://github.com/datacharmer/test_db 해당 깃허브에 Sample 데이터가 있는 것을 확인 할 수 있었고 Docker로 MySQL을 띄우고 employees.sql만 실행해주었습니다.

현재, 전체 데이터의 레코드 수는 299069개이고, 컬럼도 date 컬럼과 varchar 컬럼이 조합되어 테스트 해보기 아주 유용한 데이터임을 확인 할 수 있었습니다. 이제 테스트를 진행해보겠습니다.

테스트

인덱스 X 테스트

먼저 테스트를 하기 위해서 적절하게 가지고 올 수 있는 쿼리를 하나 만들었습니다.

특히, 날짜 같은 경우에는 카디널리티가 적당히 높은 수준의 데이터 컬럼이기때문에 birth_date 컬럼을 중점적으로 적절한 수의 데이터를 가지고 와야 합니다. 너무 많은 데이터를 가지고오면 Optimizer가 자체적으로 Table Full Scan을 할 수도 있기 때문입니다.

SELECT COUNT(*) 
FROM employees 
WHERE birth_date BETWEEN '1950-01-01' AND '1952-12-31';

약 7% 정도의 데이터를 가지고 오는 적절한 쿼리 인 것 같습니다. 이제 EXPLAIN ANALYZE를 수행하여 쿼리 탐색 결과를 한 번 살펴보도록 하겠습니다.

EXPLAIN ANALYZE SELECT * 
FROM employees 
WHERE birth_date BETWEEN '1950-01-01' AND '1952-12-31';

현재는 인덱스가 없기 때문에, Table Full Scan을 통해서 데이터를 처리해서 가지고 오는 것을 볼 수 있습니다.

인덱스 생성 후 테스트

이제, 인덱스를 한 번 추가해보도록 합시다. 실무에서는 주로 기간 별로 데이터를 조회해서 가지고 오는 쿼리가 많기 때문에 birth_date를 선행 인덱스 컬럼으로 지정하고, first_name을 후행 인덱스 컬럼으로 지정하여 인덱스를 생성해보도록 하겠습니다.

CREATE INDEX employees_birth_date_first_name_index ON employees (birth_date, first_name);

인덱스가 생성되었으므로, 이전과 동일하게 EXPLAIN ANALYZE 를 해보도록 하겠습니다.

이제, 인덱스 범위 스캔이 이루어지고 있음을 확인 할 수 있습니다. 그럼 이어서, 복합 인덱스를 걸어두었으니 first_name으로 'Bezalel'인 사람을 기존 복합 인덱스 순서대로 조회하여 쿼리 분석을 해보도록 하겠습니다.

EXPLAIN ANALYZE
SELECT *
FROM employees
WHERE birth_date BETWEEN '1950-01-01' AND '1952-12-31' AND first_name = 'Bezalel';

기존에 알고 있던 사실과 동일하게 적절하게 Index 범위 스캔을 통해 데이터를 가지고 오고 있는 것을 볼 수 있습니다.

그럼, 이제 WHERE 조건 절의 순서를 바꿔서 시도해보도록 하겠습니다.

복합 인덱스 순서와 다르게 조건절 추가

먼저, birth_date 와 first_name의 순서를 변경해보도록 하겠습니다.

EXPLAIN ANALYZE
SELECT *
FROM employees
WHERE first_name = 'Bezalel' AND birth_date BETWEEN '1950-01-01' AND '1952-12-31';

진짜입니다..! 정말로 복합 인덱스의 순서와 조건 절의 순서가 달라도 동일하게 범위 인덱스를 통해서 데이터를 조회하는 것을 볼 수 있습니다. 그렇다면, 중간에 카디널리티가 낮은 성별을 추가한다면 어떻게 되는지 문득 궁금해졌습니다.

EXPLAIN ANALYZE
SELECT *
FROM employees
WHERE first_name = 'Bezalel' AND gender = 'M' AND birth_date BETWEEN '1950-01-01' AND '1952-12-31';

이럴수가... 이 조차도 Engine이 Optimize하여 범위 스캔을 통해서 데이터를 먼저 가지고 오고 난 이후 조회를 시도 한 것을 확인 할 수 있습니다.

결론

복합 인덱스가 있을 때 조건 절의 순서를 다르게 하더라도 ENGINE이 자체적으로 쿼리를 최적화해서 인덱스를 태울 수 있도록 해주고 있다는 부분을 확인 할 수 있었습니다.

그러면 다음과 같은 질문을 하거나 이런 생각이 들 수도 있을 것 같습니다.

"이제, 그럼 쿼리를 작성 할 때 인덱스 순서에 맞게 WHERE 절을 작성하지 않아도 되겠다!"

물론, 현재는 데이터의 컬럼 개수도 적고 데이터 수 자체도 적어서 큰 문제는 없을 것 같으나 결국 최적화라는 것은 ENGINE 자체적으로 구성된 알고리즘을 통해서 최적화가 되는 것일텐데, 의도하지 않은 동작이 일어날 가능성은 배제 할 수 없지 않을까? 라는 생각이 한 편으로는 듭니다.

하지만, 가장 큰 이유는 아무래도 해당 사실을 모르고 있는 개발자가 쿼리를 봤을 때는 복합 인덱스의 순서랑 WHERE 절 순서가 다른데!?!? 하면서 애플리케이션 로직의 Query를 변경하는데 리소스를 사용할 수도 있기 때문입니다. 편하게 최적화해서 동작해주기는 하지만 개발은 혼자하는 것이 아니고 같이 하는 것이고 암시적이기보다는 명시적인 것이 더 명확하기에 그대로 사용하는게 좋지 않을까 싶습니다~