MySQL 복합 인덱스, 같은 컬럼인데 순서만 바꾸면 왜 느릴까?

이번 시간에는 MySQL 복합 인덱스의 컬럼 순서가 쿼리에 어떤 차이를 만드는지를, 순서만 다른 인덱스 여러 개와 EXPLAIN ANALYZE로 측정하며 배워가는 시간입니다.

예를 들어서 가게별 주문 관리 화면에서 "가게 7의 6월 결제 완료 주문"을 조회하는 쿼리가 있다고 해볼게요. 조건에 쓰는 컬럼은 가게 번호 store_id, 주문 상태 status, 주문 시간 created_at 세 개예요.

이 세 컬럼으로 복합 인덱스를 만들려고 하면 순서를 정해야 하죠. (store_id, status, created_at), (store_id, created_at, status), (created_at, store_id, status) 모두 같은 세 컬럼으로 만든 인덱스인데, 같은 쿼리에서 인덱스 항목을 몇 개나 확인하는지는 크게 달라요.

실습 준비

이 글의 명령은 모두 실습 코드의 mysql-composite-index/ 디렉터리에서 실행합니다.

• compose.yaml: MySQL 8.4 컨테이너
• setup.sql: 주문 100만 건 테이블과 컬럼 순서만 다른 인덱스 네 개를 만드는 SQL

compose.yaml은 아래와 같아요. name이 compose 프로젝트 이름이 되니까, 아래 docker compose 명령은 모두 이 파일이 있는 디렉터리에서 실행해 주세요. 버퍼 풀 크기 innodb_buffer_pool_size는 기본값 128MB를 그대로 씁니다.

name: mycidx
services:
  mysql:
    image: mysql:8.4
    environment:
      MYSQL_ROOT_PASSWORD: root
    ports:
      - "7370:3306"
    mem_limit: 2g

setup.sql은 가게 1000곳의 주문 100만 건을 shop.store_orders(id, store_id, status, created_at, amount, memo)에 넣어요.

• store_id: 1부터 1000까지 고르게 나눠서 가게마다 약 1000건이에요.
• status: DONE 70%, PAID 15%, READY 10%, CANCEL 4%, REFUND 1%예요.
• created_at: 2025년 10월 1일부터 31.5초 간격으로 늘어나서 1년에 고르게 퍼져 있어요. 기본 키 id가 커질수록 시간도 늦어집니다.

마지막에 컬럼 순서만 다른 인덱스 네 개를 만들어요. 인덱스 이름은 컬럼 머리글자를 순서대로 붙였어요(s는 store_id, st는 status, c는 created_at). idx_s_st_c, idx_s_c_st, idx_c_s_st, idx_st_s_c 네 개입니다.

자주 쓰는 명령은 셸 함수로 만들어 둘게요. MYSQL_PWD 환경 변수로 비밀번호를 넘기는 방식은 실습에서만 써 주세요.

아래 블록은 파일로 만들지 말고 실습 디렉터리에서 연 터미널에 그대로 붙여 넣어 주세요. 이후 명령은 모두 이 터미널에서 실행하고, 터미널을 새로 열면 다시 붙여 넣으시면 됩니다.

sql() { docker compose exec -T -e MYSQL_PWD=root mysql mysql -uroot "$@"; }

only() {
  local stmt="ALTER TABLE shop.store_orders"
  for i in idx_s_st_c idx_s_c_st idx_c_s_st idx_st_s_c; do
    if [ "$i" = "$1" ]; then stmt="$stmt ALTER INDEX $i VISIBLE,"; else stmt="$stmt ALTER INDEX $i INVISIBLE,"; fi
  done
  sql -e "${stmt%,}"
}

checked() {
  sql -e "SET GLOBAL innodb_monitor_reset = 'module_icp'; $1" > /dev/null
  sql -N -e "SELECT COUNT_RESET FROM information_schema.INNODB_METRICS WHERE NAME = 'icp_attempts'"
}

times3() {
  sql -e "$1" > /dev/null
  for i in 1 2 3; do sql -e "EXPLAIN ANALYZE $1\G" | grep -o 'actual time=[^ ]*' | head -n 1; done
}

• sql: 컨테이너 안의 mysql 클라이언트로 SQL을 실행해요.
• only: 인덱스 네 개 가운데 하나만 보이게(VISIBLE) 하고 나머지는 보이지 않게(INVISIBLE) 해요. 보이지 않는 인덱스는 옵티마이저가 실행 계획을 세울 때 쓰지 않습니다.
• checked: InnoDB가 인덱스 항목을 꺼내 WHERE 조건을 확인한 횟수(icp_attempts)를 0으로 돌린 뒤 쿼리를 실행하고, 그 횟수를 출력해요.
• times3: 쿼리를 한 번 실행해 데이터를 버퍼 풀에 올린 다음, EXPLAIN ANALYZE를 세 번 실행해 맨 위 단계의 실제 시간을 출력해요. actual time=0.05..0.08에서 앞 숫자는 첫 행이 나오기까지, 뒤 숫자는 모든 행이 나오기까지 걸린 밀리초예요.

이제 컨테이너를 띄우고, MySQL이 연결을 받을 때까지 잠시 기다린 뒤 데이터를 넣을게요. icp_attempts 카운터는 기본으로 꺼져 있어서 함께 켜 줍니다.

docker compose up -d
until sql -e "SELECT 1" > /dev/null 2>&1; do sleep 2; done
sql < setup.sql > /dev/null
sql -e "SET GLOBAL innodb_monitor_enable = 'module_icp';"

실습 1: 등호 조건은 앞에, 범위 조건은 뒤에

처음에 본 쿼리를 Q1이라고 부를게요. 가게 7의 6월 PAID 주문이에요. 쿼리 문장은 셸 변수에 담아 두고 함수에 넘깁니다.

Q1="SELECT * FROM shop.store_orders WHERE store_id = 7 AND status = 'PAID' AND created_at >= '2026-06-01' AND created_at < '2026-07-01'"

조건에 맞는 주문 수, 가게 7의 6월 주문 수, 6월 전체 주문 수를 먼저 세어 둘게요.

sql -N -e "SELECT COUNT(*) FROM shop.store_orders WHERE store_id = 7 AND status = 'PAID' AND created_at >= '2026-06-01' AND created_at < '2026-07-01'; SELECT COUNT(*) FROM shop.store_orders WHERE store_id = 7 AND created_at >= '2026-06-01' AND created_at < '2026-07-01'; SELECT COUNT(*) FROM shop.store_orders WHERE created_at >= '2026-06-01' AND created_at < '2026-07-01';"

13
77
82285

결과는 13건이고, 가게 7의 6월 주문은 77건, 6월 전체 주문은 82285건이에요. 먼저 (store_id, status, created_at)만 보이게 하고 Q1을 실행해 보겠습니다.

only idx_s_st_c
sql -t -e "EXPLAIN $Q1"
sql -e "EXPLAIN ANALYZE $Q1\G"
checked "$Q1"
times3 "$Q1"

+----+-------------+--------------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table        | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | store_orders | NULL       | range | idx_s_st_c    | idx_s_st_c | 51      | NULL |   13 |   100.00 | Using index condition |
+----+-------------+--------------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
*************************** 1. row ***************************
EXPLAIN: -> Index range scan on store_orders using idx_s_st_c over (store_id = 7 AND status = 'PAID' AND '2026-06-01 00:00:00' <= created_at < '2026-07-01 00:00:00'), with index condition: ((shop.store_orders.`status` = 'PAID') and (shop.store_orders.store_id = 7) and (shop.store_orders.created_at >= TIMESTAMP'2026-06-01 00:00:00') and (shop.store_orders.created_at < TIMESTAMP'2026-07-01 00:00:00'))  (cost=8.58 rows=13) (actual time=0.383..0.401 rows=13 loops=1)

14
actual time=0.0896..0.104
actual time=0.167..0.21
actual time=0.0814..0.102

EXPLAIN의 rows는 13이고, EXPLAIN ANALYZE의 over (...)에 읽을 범위가 나와요.

store_id = 7 AND status = 'PAID'로 위치를 정하고, 그 안에서 created_at이 6월 1일 이상 7월 1일 미만인 구간만 읽었어요.

인덱스 항목은 14개를 확인했어요. 결과 13개와, 범위가 끝났다는 것을 확인한 마지막 1개예요. 세 번 잰 시간은 0.10~0.21ms입니다.

이번에는 범위 조건 컬럼 created_at이 맨 앞인 (created_at, store_id, status)로 같은 Q1을 실행해 볼게요.

only idx_c_s_st
sql -t -e "EXPLAIN $Q1"
sql -e "EXPLAIN ANALYZE $Q1\G"
checked "$Q1"
times3 "$Q1"

+----+-------------+--------------+------------+-------+---------------+------------+---------+------+--------+----------+-----------------------+
| id | select_type | table        | partitions | type  | possible_keys | key        | key_len | ref  | rows   | filtered | Extra                 |
+----+-------------+--------------+------------+-------+---------------+------------+---------+------+--------+----------+-----------------------+
|  1 | SIMPLE      | store_orders | NULL       | range | idx_c_s_st    | idx_c_s_st | 51      | NULL | 164290 |     0.02 | Using index condition |
+----+-------------+--------------+------------+-------+---------------+------------+---------+------+--------+----------+-----------------------+
*************************** 1. row ***************************
EXPLAIN: -> Index range scan on store_orders using idx_c_s_st over ('2026-06-01 00:00:00' <= created_at <= '2026-07-01 00:00:00' AND 7 <= store_id AND 'PAID' <= status), with index condition: ((shop.store_orders.`status` = 'PAID') and (shop.store_orders.store_id = 7) and (shop.store_orders.created_at >= TIMESTAMP'2026-06-01 00:00:00') and (shop.store_orders.created_at < TIMESTAMP'2026-07-01 00:00:00'))  (cost=108561 rows=164290) (actual time=16.4..16.4 rows=13 loops=1)

82286
actual time=10.2..10.2
actual time=9.64..9.64
actual time=9.64..9.64

이번에는 첫 컬럼이 범위 조건이에요. 그러면 6월 1일부터 7월 1일까지 모든 가게의 항목을 훑으면서 store_id = 7이고 status = 'PAID'인 항목을 골라야 하죠.

출력을 보면 확인한 항목은 82286개예요. 6월 전체 주문 수(82285)에 범위 끝 하나를 더한 수입니다. 시간은 9.6~10.2ms로, 앞의 인덱스보다 100배쯤 길었어요.

EXPLAIN ANALYZE의 실제 rows가 그래도 13인 건 인덱스 컨디션 푸시다운(Index Condition Pushdown, ICP) 때문이에요. Using index condition이 그 표시로, InnoDB가 인덱스 항목에 들어 있는 컬럼으로 WHERE 조건을 먼저 확인하고 맞는 항목만 테이블 행을 읽어 서버로 넘겨요.

다만 ICP는 인덱스 항목을 확인하는 횟수 자체를 줄이지는 못해요. 읽는 범위를 좁히는 일은 컬럼 순서가 정합니다.

정리하면 등호 조건 컬럼을 앞에 두고 범위 조건 컬럼을 뒤에 둡니다. 범위 조건 뒤에 오는 컬럼은 읽은 항목을 걸러 내는 데만 쓰여요.

실습 2: 등호 조건끼리는 순서보다 다른 쿼리를 봅니다

복합 인덱스 순서를 정할 때 "카디널리티가 높은 컬럼을 앞에 둔다"는 말을 자주 들으셨을 거예요. 인덱스별 컬럼과 Cardinality를 볼게요. Cardinality는 그 컬럼까지의 서로 다른 값 개수를 통계로 추정한 값이에요.

sql -t -e "SELECT INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns, GROUP_CONCAT(CARDINALITY ORDER BY SEQ_IN_INDEX) AS cardinality FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'store_orders' GROUP BY INDEX_NAME;"

+------------+----------------------------+----------------------+
| INDEX_NAME | columns                    | cardinality          |
+------------+----------------------------+----------------------+
| idx_c_s_st | created_at,store_id,status | 996522,996522,996522 |
| idx_s_c_st | store_id,created_at,status | 1001,996522,996522   |
| idx_s_st_c | store_id,status,created_at | 1001,4957,996522     |
| idx_st_s_c | status,store_id,created_at | 4,4026,996522        |
| PRIMARY    | id                         | 996522               |
+------------+----------------------------+----------------------+

store_id로 시작하는 인덱스는 첫 컬럼이 1001이고, idx_st_s_c의 첫 컬럼 status는 값이 5종류뿐이라 추정값도 4로 작아요. 이 말대로라면 (store_id, status, created_at)이 (status, store_id, created_at)보다 나아야 하죠. 두 인덱스로 Q1을 실행해 볼게요.

only idx_s_st_c
checked "$Q1"
only idx_st_s_c
checked "$Q1"

14
14

두 인덱스 모두 확인한 항목은 14개예요. 등호 조건이 둘 다 있으면 어느 쪽이 앞이든 두 조건을 모두 만족하는 항목이 한곳에 모여 있어서, 범위 조건 created_at이 맨 뒤에 있기만 하면 순서는 결과에 영향을 주지 않았어요.

차이는 한쪽 컬럼만 쓰는 쿼리에서 나요. 상태 기준으로 "6월 환불 주문 전체"를 보는 쿼리 R1을 두 인덱스로 실행해 볼게요.

R1="SELECT * FROM shop.store_orders WHERE status = 'REFUND' AND created_at >= '2026-06-01' AND created_at < '2026-07-01'"

only idx_s_st_c
sql -e "EXPLAIN ANALYZE $R1\G"
times3 "$R1"
only idx_st_s_c
sql -e "EXPLAIN ANALYZE $R1\G"
times3 "$R1"

*************************** 1. row ***************************
EXPLAIN: -> Filter: ((shop.store_orders.`status` = 'REFUND') and (shop.store_orders.created_at >= TIMESTAMP'2026-06-01 00:00:00') and (shop.store_orders.created_at < TIMESTAMP'2026-07-01 00:00:00'))  (cost=101435 rows=27676) (actual time=4.41..293 rows=820 loops=1)
    -> Table scan on store_orders  (cost=101435 rows=996522) (actual time=0.852..230 rows=1e+6 loops=1)

actual time=1.28..276
actual time=1.13..252
actual time=1.25..263
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on store_orders using idx_st_s_c (status='REFUND'), with index condition: ((shop.store_orders.created_at >= TIMESTAMP'2026-06-01 00:00:00') and (shop.store_orders.created_at < TIMESTAMP'2026-07-01 00:00:00'))  (cost=3165 rows=18812) (actual time=1.35..4.3 rows=820 loops=1)

actual time=1.35..3.65
actual time=1.58..3.81
actual time=1.51..3.99

• idx_s_st_c로 R1: 첫 컬럼 store_id에 조건이 없어서 인덱스를 쓰지 못하고 테이블 100만 행을 모두 읽었어요. 252~276ms가 걸렸습니다.
• idx_st_s_c로 R1: status = 'REFUND'로 환불 항목을 찾고 created_at은 ICP로 걸러 820행을 넘겼어요. 3.7~4.0ms예요.

카디널리티가 높은 store_id를 앞에 둔 인덱스가 상태만으로 찾는 쿼리에서는 가장 느렸어요. 그래서 등호 조건끼리의 순서는 카디널리티보다, 그 인덱스를 함께 쓸 다른 쿼리들이 어떤 컬럼만으로 찾는지를 보고 정해 주세요.

실습 3: ORDER BY까지 인덱스로

주문 관리 화면은 보통 최근 주문부터 20건씩 보여 주죠. 등호 조건 뒤에 정렬 컬럼이 이어지는 Q2를 볼게요.

Q2="SELECT * FROM shop.store_orders WHERE store_id = 7 AND status = 'PAID' ORDER BY created_at DESC LIMIT 20"

only idx_s_st_c
sql -t -e "EXPLAIN $Q2"
sql -e "EXPLAIN ANALYZE $Q2\G"

+----+-------------+--------------+------------+------+---------------+------------+---------+-------------+------+----------+---------------------+
| id | select_type | table        | partitions | type | possible_keys | key        | key_len | ref         | rows | filtered | Extra               |
+----+-------------+--------------+------------+------+---------------+------------+---------+-------------+------+----------+---------------------+
|  1 | SIMPLE      | store_orders | NULL       | ref  | idx_s_st_c    | idx_s_st_c | 46      | const,const |  103 |   100.00 | Backward index scan |
+----+-------------+--------------+------------+------+---------------+------------+---------+-------------+------+----------+---------------------+
*************************** 1. row ***************************
EXPLAIN: -> Limit: 20 row(s)  (cost=36 rows=20) (actual time=0.161..0.29 rows=20 loops=1)
    -> Index lookup on store_orders using idx_s_st_c (store_id=7, status='PAID') (reverse)  (cost=36 rows=103) (actual time=0.154..0.281 rows=20 loops=1)

Extra에 정렬 작업(Using filesort)이 없고 Backward index scan이 있어요.

store_id = 7 AND status = 'PAID'인 항목은 이미 created_at 순서로 놓여 있으니, 그 구간의 끝에서 거꾸로 읽으면 최근 주문부터 나오는 거예요. EXPLAIN의 rows는 가게 7의 PAID 주문 103건이지만, 실제로는 20행을 읽고 LIMIT에서 멈췄어요.

WHERE 조건만 보고 인덱스를 만들면 정렬이 따로 붙을 수 있어요. 그러니 LIMIT이 있는 목록 쿼리라면 ORDER BY 컬럼이 등호 조건 바로 뒤에 오는지 함께 확인해 주세요.

정리

범위: mysql:8.4, 주문 100만 건(6월 82,285건), 컬럼 순서만 다른 인덱스를 보이게, 보이지 않게 바꿔 가며 같은 쿼리를 비교, 시간은 세 번 잰 범위.

• Q1(가게 7, PAID, 6월)에서 (store_id, status, created_at)은 인덱스 항목 14개, (created_at, store_id, status)는 82,286개를 확인했고 마지막은 9.6~10.2ms로 100배쯤 길었습니다. 등호 조건 컬럼을 앞에, 범위 조건 컬럼을 뒤에 두세요.
• 등호 조건 두 개의 순서를 바꿔도 Q1은 둘 다 14개를 확인했지만, 한쪽 컬럼만 쓰는 쿼리는 그 컬럼이 맨 앞인 인덱스에서만 빨랐고 아니면 테이블 100만 행을 읽어 252~276ms가 걸렸습니다. 등호 조건끼리의 순서는 카디널리티가 아니라 같은 인덱스를 쓸 다른 쿼리를 보고 정하세요.
• 등호 조건 바로 뒤에 created_at이 오는 인덱스는 ORDER BY created_at DESC LIMIT 20에서 정렬 없이 20행만 읽었습니다. LIMIT이 있는 목록 쿼리는 ORDER BY 컬럼이 등호 조건 바로 뒤에 오는지 확인해 주세요.
• 이 글에 싣지 않은 추가 측정에서는 amount를 붙인 커버링 인덱스(쿼리에 필요한 컬럼이 모두 들어 있어 테이블을 다시 읽지 않는 인덱스)가 같은 521행을 읽고 1.30~1.77ms를 0.19~0.22ms로 줄였습니다. 컬럼을 붙이는 건 자주 실행되고 시간이 중요한 조회 쿼리에만 하세요.
• 이 글에 싣지 않은 추가 측정에서는 첫 컬럼 조건이 없을 때 쓰는 스킵 스캔이 둘째 컬럼 조건이 등호(status = 'REFUND')일 때 기본으로 골라지지 않았고, 범위(store_id BETWEEN 1 AND 10)일 때 골라져 10,085개를 5.75ms에 확인했습니다(끄면 296ms). 자주 실행되는 쿼리에는 그 조건 컬럼으로 시작하는 인덱스를 따로 두세요.