MySQL 다중 컬럼 인덱스와 범위 조건: 컬럼 순서가 성능을 좌우하는 이유
들어가며: 인덱스를 만들었는데 왜 쿼리가 느릴까?
데이터베이스 성능 튜닝의 핵심은 단연 인덱스 입니다. 특히 여러 컬럼을 묶어 생성하는 다중 컬럼 인덱스(Multi-column Index)는 잘 사용하면 쿼리 성능을 극적으로 향상시킬 수 있는 강력한 도구입니다. 하지만 많은 개발자들이 다음과 같은 질문에 부딪히곤 합니다.
(col1, col2, col3)순서로 인덱스를 생성했는데, 왜WHERE col1 = 'A' AND col3 = 'X'조건의 쿼리는 인덱스를 제대로 활용하지 못할까? 왜WHERE col1 = 'A' AND col2 > 10조건부터 성능이 저하될까?
이 질문에 대한 답은 다중 컬럼 인덱스의 자료 구조 와 MySQL 옵티마이저의 동작 방식 에 있습니다. 이 글에서는 컬럼 순서와 범위 조건이 인덱스 사용에 미치는 영향을 명확히 이해하고, 이를 바탕으로 최적의 인덱스를 설계하는 전략을 알아보겠습니다.
1. 다중 컬럼 인덱스의 구조: 잘 정렬된 ‘전화번호부’
다중 컬럼 인덱스를 이해하는 가장 좋은 방법은 여러 기준으로 정렬된 거대한 전화번호부 를 떠올리는 것입니다.
예를 들어, users 테이블에 (last_name, first_name, city) 순서로 인덱스를 생성했다고 가정해 보겠습니다.
CREATE INDEX idx_users_name_city ON users (last_name, first_name, city);
이 인덱스의 데이터는 다음과 같은 원리로 정렬됩니다.
- 첫 번째 기준: 모든 데이터는
last_name(성)을 기준으로 먼저 정렬됩니다. (예: 강씨, 김씨, 박씨 순) - 두 번째 기준:
last_name이 같은 데이터 그룹 내에서,first_name(이름)을 기준으로 다시 정렬됩니다. (예: 같은 김씨 내에서 가나다순) - 세 번째 기준:
last_name과first_name이 모두 같은 데이터 그룹 내에서,city(도시)를 기준으로 마지막으로 정렬됩니다.
| last_name | first_name | city |
|---|---|---|
| 김 | 민준 | 서울 |
| 김 | 민준 | 부산 |
| 김 | 서준 | 인천 |
| 박 | 예준 | 서울 |
| 박 | 하준 | 대구 |
이 구조 때문에 인덱스의 선두 컬럼(last_name)을 조건에서 사용하지 않으면 인덱스 자체를 효율적으로 사용할 수 없습니다. 전화번호부에서 이름(first_name)만으로 사람을 찾는 것이 어려운 것과 같은 이치입니다.
2. 인덱스 탐색(Seek) vs. 인덱스 스캔(Scan)
MySQL 옵티마이저는 WHERE 절의 조건을 보고 인덱스를 어떻게 사용할지 결정합니다. 핵심은 탐색(Seek) 과 스캔(Scan) 의 차이를 이해하는 것입니다.
- 인덱스 탐색 (Index Seek): 정렬된 인덱스 구조의 장점을 활용하여 원하는 데이터의 위치로 한번에 점프하는 방식입니다. 매우 빠릅니다.
- 인덱스 스캔 (Index Scan): 인덱스의 특정 지점부터 조건을 만족하는 데이터를 찾아 순차적으로 훑어보는 방식입니다. 탐색보다는 느립니다.
Case 1: 등호(=) 조건만 사용하는 경우 (가장 효율적)
SELECT * FROM users WHERE last_name = '김' AND first_name = '민준';
이 쿼리는 인덱스를 가장 효율적으로 사용합니다.
last_name = '김'조건으로 인덱스에서 ‘김’씨 그룹의 시작점으로 탐색(Seek) 합니다.- 그 안에서
first_name = '민준'조건으로 ‘민준’ 그룹의 시작점으로 다시 한번 탐색(Seek) 합니다. - 결과적으로 단 몇 번의 탐색만으로 원하는 데이터(
김, 민준, 서울과김, 민준, 부산)를 정확히 찾아냅니다.
Case 2: 범위 조건(>, <, BETWEEN, LIKE)이 포함된 경우 (핵심 주제)
SELECT * FROM users WHERE last_name = '김' AND first_name > '민준';
이 쿼리의 동작은 달라집니다.
last_name = '김'조건으로 ‘김’씨 그룹의 시작점으로 탐색(Seek) 합니다. (여기까지는 효율적)first_name > '민준'조건은 ‘민준’보다 뒤에 오는 모든 이름을 의미합니다. 옵티마이저는first_name이 ‘민준’인 지점 다음부터 ‘김’씨 그룹이 끝날 때까지 모든 데이터를 순차적으로 스캔(Scan) 해야 합니다.
3. 핵심 규칙: 범위 조건은 인덱스 추가 활용을 멈춘다
이제 가장 중요한 질문에 답할 차례입니다. “왜 범위 조건 뒤의 컬럼은 인덱스 효율을 떨어뜨리는가?”
다음 쿼리를 보겠습니다.
SELECT * FROM users WHERE last_name = '김' AND first_name > '민준' AND city = '서울';
위 쿼리에서 city = '서울' 조건은 인덱스 탐색에 사용될 수 있을까요? 정답은 ‘아니오’입니다.
first_name > '민준' 조건 때문에 옵티마이저는 ‘김서준’, ‘김예준’ 등 여러 first_name 값을 스캔하게 됩니다. 이 스캔 과정에서 city 컬럼의 값들은 더 이상 정렬된 상태가 아닙니다.
- ‘김서준’의
city값들 (서울,부산,인천…)은 그 자체로 정렬되어 있습니다. - ‘김예준’의
city값들도 그 자체로 정렬되어 있습니다. - 하지만 ‘김서준’의 도시들과 ‘김예준’의 도시들은 서로 섞여 있어 전체적으로 정렬되어 있지 않습니다.
전화번호부 비유로 돌아가면, “김씨 성을 가진 사람 중 이름이 ‘민준’ 뒤에 오는 모든 사람을 찾아라"라는 요청을 받았다면, 당신은 ‘김민준’ 다음 사람부터 페이지를 한 장 한 장 넘겨볼 것입니다. 이 과정에서 그들의 ‘사는 도시’가 무엇인지 확인은 하겠지만, ‘사는 도시’가 ‘서울’이라고 해서 갑자기 수십 페이지를 건너뛸 수는 없습니다. 이미 ‘이름’ 순서로 스캔하고 있기 때문입니다.
이것이 바로 first_name에서 범위 스캔이 시작되면, 그 뒤에 오는 city 컬럼의 정렬이 무용지물이 되는 이유입니다. city = '서울' 조건은 스캔 범위를 줄이는 데 기여하지 못하고, 단지 스캔으로 가져온 데이터를 필터링 하는 용도로만 사용됩니다. (이를 Using where 라고 합니다.)
4. 최적화를 위한 실용 전략
이러한 원리를 이해했다면 더 나은 인덱스와 쿼리를 설계할 수 있습니다.
인덱스 컬럼 순서 선정:
WHERE절에서 등호(=)나 IN 조건 으로 자주 사용되는 컬럼을 인덱스의 앞쪽에 배치해야 합니다. 카디널리티(Cardinality, 값의 고유성)가 높은 컬럼을 앞에 두는 것이 일반적으로 유리합니다.범위 조건 컬럼은 뒤로: 범위 조건(
>,<,BETWEEN,LIKE)으로 사용될 가능성이 높은 컬럼은 인덱스의 뒤쪽으로 보내는 것이 유리합니다.EXPLAIN으로 확인하기: 쿼리 앞에EXPLAIN을 붙여 실행 계획을 반드시 확인하는 습관을 들여야 합니다.key_len을 통해 인덱스의 어느 부분까지 효과적으로 사용되었는지,Extra필드에Using where가 표시되어 불필요한 필터링이 발생하는지 등을 점검할 수 있습니다.
요약
- 다중 컬럼 인덱스는 선두 컬럼부터 순차적으로 정렬 된 자료구조입니다.
WHERE절의 등호(=) 조건 은 인덱스를 효율적으로 탐색(Seek)하는 데 사용됩니다.WHERE절에서 첫 번째 범위(>, < 등) 조건 이 사용되면, 그 컬럼부터는 인덱스 탐색이 아닌 스캔(Scan) 이 시작됩니다.- 범위 스캔이 시작된 이후의 인덱스 컬럼들은 스캔 범위를 줄이는 데 기여하지 못하고, 가져온 데이터를 필터링 하는 역할만 수행합니다.
이 원칙을 기억하고 인덱스를 설계한다면, MySQL이 여러분의 쿼리에 훨씬 더 빠르고 효율적으로 응답하게 만들 수 있을 것입니다.