MySQL 인덱스, 왜 컬럼 순서가 성능을 좌우할까?
데이터베이스 성능은 인덱스를 얼마나 잘 설계하고 활용하는지에 따라 크게 좌우됩니다. 특히 여러 컬럼을 묶어 생성하는 다중 컬럼 인덱스(Multi-column Index)는 쿼리 성능을 극적으로 향상시킬 수 있는 강력한 도구입니다. 하지만 그 내부 동작 원리를 정확히 이해하지 못하면 기대만큼의 성능을 얻지 못할 수 있습니다.
이번 글에서는 “다중 컬럼 인덱스에서 범위 조건(Range Condition)이 등장한 이후의 컬럼들은 인덱스의 효율을 왜 떨어뜨리는가?” 에 대해 깊이 파고들어 보겠습니다.
1. 다중 컬럼 인덱스의 기본 구조: 정렬된 사전
다중 컬럼 인덱스를 이해하는 가장 좋은 방법은 여러 기준으로 정렬된 거대한 ‘사전’을 떠올리는 것입니다.
예를 들어, (col1, col2, col3) 순서로 인덱스가 생성되었다고 가정해 보겠습니다.
CREATE INDEX idx_example ON my_table (col1, col2, col3);
이 인덱스의 데이터는 다음과 같은 원리로 정렬됩니다.
- 첫 번째 기준: 모든 데이터는
col1을 기준으로 먼저 정렬됩니다. - 두 번째 기준:
col1의 값이 같은 데이터 그룹 내에서,col2를 기준으로 다시 정렬됩니다. - 세 번째 기준:
col1과col2의 값이 모두 같은 데이터 그룹 내에서,col3을 기준으로 마지막으로 정렬됩니다.
이는 마치 우리가 사전을 찾을 때, 첫 글자(ㄱ, ㄴ, ㄷ…)를 먼저 찾고, 그다음 두 번째 글자(ㅏ, ㅑ, ㅓ…)를 찾는 것과 완벽하게 동일한 원리입니다.
| col1 | col2 | col3 |
|---|---|---|
| A | 1 | X |
| A | 1 | Y |
| A | 2 | Z |
| B | 1 | W |
| B | 3 | X |
| B | 3 | Z |
2. 인덱스 스캔 방식: 동등 비교(Equality) vs. 범위 비교(Range)
MySQL 옵티마이저는 WHERE 절의 조건을 보고 인덱스를 어떻게 ‘탐색(seek)‘하고 ‘스캔(scan)‘할지 결정합니다.
Case 1: 동등 비교만 사용하는 경우 (가장 효율적)
SELECT * FROM my_table WHERE col1 = 'A' AND col2 = 2;
이 쿼리는 인덱스를 가장 효율적으로 사용합니다.
col1 = 'A'조건을 사용해 인덱스에서 ‘A’ 그룹으로 바로 점프합니다. (탐색)- 그 안에서
col2 = 2조건을 사용해 ‘2’ 그룹으로 다시 한번 점프합니다. (탐색) - 결과적으로 단 몇 번의 탐색만으로 원하는 데이터의 위치(
A, 2, Z)를 정확히 찾아냅니다.col3까지 조건에 있다면 더 좁혀질 것입니다.
Case 2: 범위 비교가 포함되는 경우 (핵심 주제)
SELECT * FROM my_table WHERE col1 = 'A' AND col2 > 1;
이 쿼리의 동작은 달라집니다.
col1 = 'A'조건을 사용해 인덱스에서 ‘A’ 그룹으로 바로 점프합니다. (여기까지는 효율적)col2 > 1조건은 ‘1보다 큰 모든 것’을 의미합니다. 옵티마이저는col2가 1인 지점 다음부터col1이 ‘A’인 그룹이 끝날 때까지 모든 데이터를 순차적으로 스캔해야 합니다.- 이 스캔 과정에서
col3는 더 이상 정렬 순서의 의미를 갖지 못합니다. 왜냐하면col2의 값이2,3,4… 등으로 계속 바뀌기 때문입니다.col2가2일 때의col3값들과col2가3일 때의col3값들은 서로 정렬되어 있지 않습니다.
결론적으로, col2에서 범위 조건(>, <, BETWEEN, LIKE '...%')이 사용되는 순간, MySQL은 그 지점부터는 더 이상 인덱스를 통한 효율적인 ‘탐색’을 멈추고 ‘스캔’으로 전환합니다. 이로 인해 범위 조건 컬럼 뒤에 오는 col3는 스캔 범위를 줄이는 데 아무런 도움을 주지 못하고, 단지 스캔한 데이터를 필터링하는 용도로만 사용됩니다.
3. 왜 이런 현상이 발생할까?
사전 비유로 다시 돌아가 봅시다. “김씨 성을 가진 사람 중에, 이름이 ‘민준’보다 뒤에 오는 모든 사람을 찾아라"라는 요청을 받았다면, 당신은 사전의 ‘김’ 섹션으로 가서 ‘김민준’을 찾은 뒤, 그 뒤에 나오는 모든 사람(‘김민지’, ‘김서준’, ‘김예원’…)의 페이지를 한 장 한 장 넘겨볼 것입니다. 이 과정에서 그들의 ‘사는 도시’가 사전 순으로 정렬되어 있다고 한들, 페이지를 건너뛰게 해 주지는 못합니다. 이미 ‘이름’ 순서로 스캔하고 있기 때문입니다.
이것이 바로 col2에서 범위 스캔이 시작되면 col3의 정렬이 무용지물이 되는 이유입니다.
4. 최적화를 위한 전략
이러한 원리를 이해했다면 우리는 더 나은 인덱스와 쿼리를 설계할 수 있습니다.
- 인덱스 컬럼 순서 선정:
WHERE절에서=(동등 비교) 조건으로 자주 사용되는 컬럼을 인덱스의 앞쪽에 배치하세요. - 범위 조건 컬럼의 위치: 범위 조건으로 사용될 가능성이 높은 컬럼은 인덱스의 뒤쪽으로 보내는 것이 유리합니다.
- 쿼리 재구성: 가능하다면 범위 조건을 동등 조건으로 바꿀 수 있는지 검토합니다. 예를 들어
status IN ('A', 'B', 'C')는status > 'A'보다 일반적으로 더 효율적입니다. (IN은 내부적으로 여러 번의 동등 비교로 처리될 수 있기 때문입니다.)
요약
- 다중 컬럼 인덱스는 선두 컬럼부터 순차적으로 정렬된 자료구조입니다.
WHERE절에서 동등(=) 조건은 인덱스를 효율적으로 탐색(점프)하는 데 사용됩니다.WHERE절에서 범위(>, <, BETWEEN, LIKE) 조건이 사용되면, 그 컬럼부터는 인덱스 탐색이 아닌 스캔이 시작됩니다.- 범위 스캔이 시작된 이후의 인덱스 컬럼들은 스캔 범위를 줄이는 데 기여하지 못하고, 가져온 데이터를 필터링하는 역할만 수행합니다.
이 원칙을 기억하고 인덱스를 설계한다면, MySQL이 여러분의 쿼리에 훨씬 더 빠르고 효율적으로 응답하게 만들 수 있을 것입니다.