Composite index 설계 원칙: leftmost prefix와 equality/range 조건
MySQL Composite index의 leftmost prefix, equality와 range 조건의 경계, 정렬 지원 여부를 실행 계획과 함께 설명한다.
Composite index는 여러 column을 한 묶음으로 만든다는 사실보다 어떤 순서로 정렬된 하나의 B+Tree인가를 이해하는 것이 중요하다. (tenant_id, status, created_at) 인덱스는 세 개의 독립 인덱스가 아니다. tenant_id로 먼저 정렬하고, 같은 tenant 안에서 status, 같은 tenant와 status 안에서 created_at 순서로 정렬한 하나의 탐색 구조다.
이 구조를 놓치면 “WHERE에 인덱스 column이 모두 있으므로 전부 효율적으로 사용한다”, “선택도가 높은 column을 무조건 맨 앞에 둔다”, “조건문에 적은 순서가 인덱스 순서와 같아야 한다”와 같은 잘못된 규칙을 적용하기 쉽다. 실제 설계에서는 leftmost prefix, equality prefix, 첫 range 조건, 정렬 요구, covering 여부와 전체 query family를 함께 보아야 한다.
이 글은 MySQL 8.0 이상을 기준으로 Composite index의 탐색 구간이 어떻게 만들어지는지 설명한다. 재현 SQL은 임시 MySQL 8.0 환경에서 실행 검증하며, 실행 계획의 rows 추정치는 통계와 minor version에 따라 달라질 수 있으므로 type, key, key_len, Extra와 실제 조건 구조를 중심으로 해석한다.
1. Composite index는 column tuple의 사전식 정렬이다
INDEX (tenant_id, status, created_at, order_id)의 leaf entry는 개념적으로 다음 tuple 순서로 정렬된다.
(tenant_id, status, created_at, order_id)
비교는 왼쪽 column부터 시작한다. 왼쪽 값이 다르면 오른쪽 값은 순서를 결정하는 데 참여하지 않는다. 왼쪽 값이 같을 때만 다음 column을 비교한다. 사전에서 첫 글자가 같은 단어끼리 둘째 글자를 비교하는 방식과 같다.
flowchart LR
Q["WHERE 조건과 ORDER BY"] --> P["왼쪽부터 연속된 prefix 확인"]
P --> E{"equality 조건인가?"}
E -->|예| N["다음 index column으로 진행"]
N --> E
E -->|아니요: range| R["연속 탐색 구간의 경계 형성"]
R --> T["뒤 column은 대체로<br/>추가 필터·ICP·covering에 사용"]
P -->|중간 column 없음| G["prefix 단절<br/>뒤 column으로 탐색 범위를 직접 좁히기 어려움"]
T --> O["ORDER BY 충족 여부와<br/>table row 접근 여부를 별도 판단"]
G --> O
여기서 leftmost prefix란 인덱스 정의의 맨 왼쪽부터 끊기지 않은 column 묶음을 뜻한다. 위 인덱스에서 구조적으로 자연스러운 prefix는 다음과 같다.
(tenant_id)(tenant_id, status)(tenant_id, status, created_at)(tenant_id, status, created_at, order_id)
(status)나 (status, created_at)는 이 인덱스의 leftmost prefix가 아니다. MySQL이 index full scan, Index Skip Scan 또는 다른 최적화를 선택할 가능성은 있지만, 이를 일반적인 point/range 탐색의 기본 성질로 기대해서는 안 된다. 특히 skip scan은 data distribution과 cost estimate에 따라 선택되는 실행 전략이지, 앞 column이 없는 모든 query를 안정적으로 빠르게 만드는 대체재가 아니다.
2. Equality prefix는 탐색 경로를 다음 column까지 고정한다
tenant_id = 7 AND status = 'PAID'처럼 왼쪽 column이 equality 조건으로 고정되면 B+Tree 탐색은 정확한 tenant 구간 안의 정확한 status 구간으로 내려갈 수 있다. 그다음 created_at에 range가 있으면 해당 tenant와 status 내부의 시간 구간만 연속해서 읽는다.
개념적인 탐색 범위는 다음처럼 표현할 수 있다.
하한: (7, 'PAID', '2026-02-01 00:00:00', 최소값)
상한: (7, 'PAID', '2026-02-11 00:00:00', 최소값) 미만
SQL의 predicate 작성 순서는 중요하지 않다. Optimizer는 status = 'PAID' AND tenant_id = 7을 인덱스 정의 순서에 맞춰 분석할 수 있다. 중요한 것은 WHERE 절의 문자 순서가 아니라 인덱스 column 순서와 각 predicate의 의미다.
Equality로 취급할 수 있는 대표 조건은 =와 상수에 대한 null-safe equality <=>다. IN (...)은 내부적으로 여러 equality range로 전개될 수 있지만 값이 여러 개면 하나의 연속 prefix로 고정된 것과는 다르다. range 수, 정렬 요구와 cost estimate를 함께 확인해야 한다.
3. 첫 range 조건이 연속 탐색 구간의 경계를 만든다
일반적인 B+Tree range access에서 >, >=, <, <=, BETWEEN, prefix LIKE 'abc%' 같은 조건은 범위를 만든다. 왼쪽 equality prefix 다음의 첫 range column이 탐색 구간을 결정하면, 그 뒤 column은 보통 같은 방식으로 연속 구간을 더 좁히지 못한다.
예를 들어 다음 조건을 보자.
INDEX (tenant_id, status, created_at)
WHERE tenant_id = 7
AND status >= 'PAID'
AND created_at >= '2026-02-01'
AND created_at < '2026-02-02'
status가 첫 range다. 인덱스는 (tenant_id=7, status>='PAID') 구간을 읽을 수 있지만, 여러 status 값 각각에 흩어진 하루치 created_at만 하나의 연속 범위로 표현할 수는 없다. created_at 조건은 Index Condition Pushdown(ICP)으로 secondary leaf 단계에서 거르거나 server layer에서 residual predicate로 평가될 수 있다. 즉 “인덱스에서 조건을 검사했다”와 “그 조건이 읽을 index entry 수를 줄였다”는 서로 다른 의미다.
반대로 (tenant_id, created_at, status)라면 tenant equality 뒤의 하루 시간 범위를 먼저 좁히고 status를 추가 필터로 평가할 수 있다. 어느 인덱스가 좋은지는 status query와 시간 범위 query 중 무엇이 핵심 workload인지에 따라 달라진다.
4. 중간 column이 빠지면 뒤 column의 탐색력이 약해진다
INDEX (tenant_id, status, created_at)에 다음 조건이 있다고 가정한다.
WHERE tenant_id = 7
AND created_at >= '2026-02-01'
AND created_at < '2026-02-11'
맨 왼쪽 tenant_id는 사용할 수 있지만 중간 status가 고정되지 않았다. created_at 값은 각 status 구간 안에서 다시 정렬되므로 tenant 전체에서 하나의 연속 시간 범위가 아니다. MySQL은 tenant에 해당하는 많은 index entry를 읽고 created_at을 검사할 수 있지만, (tenant_id, created_at, status)가 제공하는 직접적인 시간 범위 탐색과는 읽기 양이 다를 수 있다.
이 원리는 “인덱스 column이 WHERE에 등장하는가”가 아니라 탐색 시작점과 종료점을 얼마나 좁게 만들 수 있는가를 물어야 한다는 뜻이다. EXPLAIN에서 같은 key가 표시되더라도 key_len, access type과 estimated rows가 달라지는 이유다.
5. Equality column의 순서는 선택도 하나로 정하지 않는다
“가장 선택도가 높은 column을 항상 맨 앞에 둔다”는 규칙은 불완전하다. 모든 핵심 query가 tenant_id와 user_id를 둘 다 equality로 지정한다면 두 column은 모두 equality prefix에 포함될 수 있다. 이 경우 순서는 다음 query family를 얼마나 지원하는지가 더 중요하다.
- tenant만 지정하는 관리·집계 query가 많은가?
- user는 tenant 내부에서만 유일한가, 전역적으로 유일한가?
- 뒤에 어떤 range column과
ORDER BY가 이어지는가? - uniqueness constraint의 업무 의미는 무엇인가?
- tenant별 data skew가 심한가?
- 하나의 index로 covering하려는 projection은 무엇인가?
다중 tenant OLTP에서는 tenant 격리와 query 관례 때문에 tenant_id가 앞에 오는 경우가 많다. 하지만 모든 조회가 globally unique한 order_id 하나로 찾는다면 별도 UNIQUE(order_id)가 더 직접적일 수 있다. 낮은 cardinality의 status도 tenant와 결합해 status별 queue를 읽는 workload에서는 유용한 두 번째 column이 될 수 있다. 선택도는 column 단독 통계가 아니라 앞 prefix 안에서의 조건부 분포와 실제 query pattern으로 판단해야 한다.
6. 실행 환경과 재현 데이터 준비
먼저 버전과 Optimizer의 주요 기능 상태를 확인한다.
SELECT VERSION() AS mysql_version,
@@optimizer_switch LIKE '%index_condition_pushdown=on%' AS icp_enabled,
@@optimizer_switch LIKE '%skip_scan=on%' AS skip_scan_enabled;
실행 결과(MySQL 8.0.x):
mysql> SELECT VERSION() AS mysql_version,
-> @@optimizer_switch LIKE '%index_condition_pushdown=on%' AS icp_enabled,
-> @@optimizer_switch LIKE '%skip_scan=on%' AS skip_scan_enabled;
+---------------+-------------+-------------------+
| mysql_version | icp_enabled | skip_scan_enabled |
+---------------+-------------+-------------------+
| 8.0.46 | 1 | 1 |
+---------------+-------------+-------------------+
1 row in set (0.00 sec)
재현용 table에는 순서가 다른 두 Composite index를 둔다. idx_tenant_status_created는 status equality 뒤의 시간 range와 정렬에 맞고, idx_tenant_created_status는 status가 없거나 range인 시간 조회에 맞는다.
DROP TABLE IF EXISTS order_index_demo;
CREATE TABLE order_index_demo (
order_id BIGINT UNSIGNED NOT NULL,
tenant_id INT UNSIGNED NOT NULL,
status VARCHAR(12) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
created_at DATETIME NOT NULL,
amount DECIMAL(10,2) NOT NULL,
payload VARCHAR(120) NOT NULL,
PRIMARY KEY (order_id),
KEY idx_tenant_status_created
(tenant_id, status, created_at, order_id),
KEY idx_tenant_created_status
(tenant_id, created_at, status, order_id)
) ENGINE=InnoDB;
SET SESSION cte_max_recursion_depth = 10000;
INSERT INTO order_index_demo
(order_id, tenant_id, status, created_at, amount, payload)
WITH RECURSIVE seq AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < 9600
)
SELECT n,
MOD(n - 1, 12) + 1,
ELT(MOD(FLOOR((n - 1) / 12), 4) + 1,
'NEW', 'PAID', 'SHIPPED', 'CANCELLED'),
TIMESTAMP('2026-01-01 00:00:00')
+ INTERVAL MOD(FLOOR((n - 1) / 48), 80) DAY
+ INTERVAL FLOOR((n - 1) / 3840) HOUR,
MOD(n * 137, 50000) / 100,
RPAD(CONCAT('order-', n), 120, 'x')
FROM seq;
ANALYZE TABLE order_index_demo;
SELECT COUNT(*) AS total_rows,
COUNT(DISTINCT tenant_id) AS tenants,
COUNT(DISTINCT status) AS statuses,
MIN(created_at) AS first_created_at,
MAX(created_at) AS last_created_at
FROM order_index_demo;
실행 결과(MySQL 8.0.x):
준비 DDL과 반복 CTE의 입력 행은 화면 폭을 위해 줄였으며, DDL·DML 성공 상태와 검증에 필요한 집계 결과를 발췌했다.
mysql> CREATE TABLE order_index_demo (...);
Query OK, 0 rows affected (0.00 sec)
mysql> SET SESSION cte_max_recursion_depth = 10000;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO order_index_demo (...) WITH RECURSIVE seq AS (...) SELECT ...;
Query OK, 9600 rows affected (0.09 sec)
Records: 9600 Duplicates: 0 Warnings: 0
mysql> ANALYZE TABLE order_index_demo;
+----------------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------------------------+---------+----------+----------+
| mysql_tech_note.order_index_demo | analyze | status | OK |
+----------------------------------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) AS total_rows,
-> COUNT(DISTINCT tenant_id) AS tenants,
-> COUNT(DISTINCT status) AS statuses,
-> MIN(created_at) AS first_created_at,
-> MAX(created_at) AS last_created_at
-> FROM order_index_demo;
+------------+---------+----------+---------------------+---------------------+
| total_rows | tenants | statuses | first_created_at | last_created_at |
+------------+---------+----------+---------------------+---------------------+
| 9600 | 12 | 4 | 2026-01-01 00:00:00 | 2026-03-21 01:00:00 |
+------------+---------+----------+---------------------+---------------------+
1 row in set (0.01 sec)
이 데이터는 실행 계획 비교를 위한 결정적 fixture다. 실제 workload의 tenant 크기, status 분포와 시간 편향을 대표하는 측정값이 아니다.
7. 인덱스 column 순서를 metadata로 확인하기
인덱스 이름만 보고 column 순서를 추측하지 말고 information_schema.STATISTICS의 SEQ_IN_INDEX를 확인한다. CARDINALITY는 표본 통계라 실행마다 달라질 수 있으므로 이 예제에서는 제외한다.
SELECT INDEX_NAME,
SEQ_IN_INDEX,
COLUMN_NAME,
COLLATION
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'order_index_demo'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;
실행 결과(MySQL 8.0.x):
mysql> SELECT INDEX_NAME,
-> SEQ_IN_INDEX,
-> COLUMN_NAME,
-> COLLATION
-> FROM information_schema.STATISTICS
-> WHERE TABLE_SCHEMA = DATABASE()
-> AND TABLE_NAME = 'order_index_demo'
-> ORDER BY INDEX_NAME, SEQ_IN_INDEX;
+---------------------------+--------------+-------------+-----------+
| INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION |
+---------------------------+--------------+-------------+-----------+
| idx_tenant_created_status | 1 | tenant_id | A |
| idx_tenant_created_status | 2 | created_at | A |
| idx_tenant_created_status | 3 | status | A |
| idx_tenant_created_status | 4 | order_id | A |
| idx_tenant_status_created | 1 | tenant_id | A |
| idx_tenant_status_created | 2 | status | A |
| idx_tenant_status_created | 3 | created_at | A |
| idx_tenant_status_created | 4 | order_id | A |
| PRIMARY | 1 | order_id | A |
+---------------------------+--------------+-------------+-----------+
9 rows in set (0.00 sec)
운영에서는 SUB_PART, NULLABLE, IS_VISIBLE, expression column 여부도 함께 확인한다. 같은 이름의 인덱스가 환경마다 다른 정의를 갖지 않도록 schema migration과 실제 metadata를 대조해야 한다.
8. Equality prefix 뒤에 range를 두는 기본형
다음 query는 tenant_id와 status를 equality로 고정한 뒤 created_at range를 읽는다. projection도 인덱스에 포함되어 있어 clustered index row를 다시 찾지 않는 covering access가 가능하다.
EXPLAIN
SELECT order_id, tenant_id, status, created_at
FROM order_index_demo FORCE INDEX (idx_tenant_status_created)
WHERE status = 'PAID'
AND created_at >= '2026-02-01 00:00:00'
AND tenant_id = 7
AND created_at < '2026-02-11 00:00:00'
ORDER BY created_at, order_id;
SELECT COUNT(*) AS matched_rows
FROM order_index_demo
WHERE status = 'PAID'
AND created_at >= '2026-02-01 00:00:00'
AND tenant_id = 7
AND created_at < '2026-02-11 00:00:00';
실행 결과(MySQL 8.0.x):
mysql> EXPLAIN
-> SELECT order_id, tenant_id, status, created_at
-> FROM order_index_demo FORCE INDEX (idx_tenant_status_created)
-> WHERE status = 'PAID'
-> AND created_at >= '2026-02-01 00:00:00'
-> AND tenant_id = 7
-> AND created_at < '2026-02-11 00:00:00'
-> ORDER BY created_at, order_id;
+----+-------------+------------------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | order_index_demo | NULL | range | idx_tenant_status_created | idx_tenant_status_created | 23 | NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+------------------+------------+-------+---------------------------+---------------------------+---------+------+------+----------+--------------------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) AS matched_rows
-> FROM order_index_demo
-> WHERE status = 'PAID'
-> AND created_at >= '2026-02-01 00:00:00'
-> AND tenant_id = 7
-> AND created_at < '2026-02-11 00:00:00';
+--------------+
| matched_rows |
+--------------+
| 29 |
+--------------+
1 row in set (0.00 sec)
FORCE INDEX는 이 절에서 의도한 인덱스의 특성을 안정적으로 관찰하기 위한 교육용 장치다. 실제 운영 query에 hint를 넣기 전에는 hint 없는 계획, 다른 후보 인덱스와 장기적인 data distribution 변화를 먼저 검증해야 한다. WHERE에서 status, created_at, tenant_id 순서로 적었어도 Optimizer는 인덱스 정의에 맞는 equality prefix를 구성한다.
9. 중간 status가 빠진 시간 range 비교
동일한 query를 column 순서가 다른 두 인덱스로 강제해 비교한다. 첫 인덱스는 tenant_id 뒤의 status가 비어 있어 시간 조건으로 탐색 구간을 직접 좁히기 어렵다. 두 번째 인덱스는 (tenant_id, created_at)이 연속 prefix이므로 시간 range를 사용할 수 있다.
EXPLAIN
SELECT order_id, tenant_id, status, created_at
FROM order_index_demo FORCE INDEX (idx_tenant_status_created)
WHERE tenant_id = 7
AND created_at >= '2026-02-01 00:00:00'
AND created_at < '2026-02-11 00:00:00';
EXPLAIN
SELECT order_id, tenant_id, status, created_at
FROM order_index_demo FORCE INDEX (idx_tenant_created_status)
WHERE tenant_id = 7
AND created_at >= '2026-02-01 00:00:00'
AND created_at < '2026-02-11 00:00:00';
실행 결과(MySQL 8.0.x):
아래 표는 두 EXPLAIN 결과에서 비교에 필요한 열만 발췌한 것이다.
mysql> EXPLAIN SELECT ... FORCE INDEX (idx_tenant_status_created) ...;
+-------+---------------------------+---------+------+----------------------------------------+
| type | key | key_len | rows | Extra |
+-------+---------------------------+---------+------+----------------------------------------+
| range | idx_tenant_status_created | 23 | 88 | Using where; Using index for skip scan |
+-------+---------------------------+---------+------+----------------------------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT ... FORCE INDEX (idx_tenant_created_status) ...;
+-------+---------------------------+---------+------+--------------------------+
| type | key | key_len | rows | Extra |
+-------+---------------------------+---------+------+--------------------------+
| range | idx_tenant_created_status | 9 | 1 | Using where; Using index |
+-------+---------------------------+---------+------+--------------------------+
1 row in set (0.00 sec)
이 검증 환경은 skip_scan=on이므로 첫 계획이 네 개 status 값을 건너가며 시간 범위를 찾는 Index Skip Scan을 선택했다. 이는 leftmost prefix 규칙이 사라졌다는 뜻이 아니라, distinct status 수와 비용 추정을 바탕으로 여러 하위 범위를 반복 탐색한 예외적 실행 전략이다. 두 번째 인덱스는 (tenant_id, created_at) 자체가 연속 prefix이므로 skip scan 없이 직접 range를 구성한다. Skip Scan 선택은 data distribution과 비용에 따라 바뀔 수 있으므로 이를 schema 보장으로 사용해서는 안 된다. 또한 Using index는 이 projection이 covering임을 주로 나타낸다.
10. status가 range이면 뒤의 created_at은 어떤 역할을 하는가
이번에는 status >= 'PAID'가 첫 range가 된다. (tenant_id, status, created_at)에서는 status 이후의 날짜 조건이 필터에는 기여해도 하나의 좁은 연속 탐색 범위를 만들기 어렵다. (tenant_id, created_at, status)에서는 하루 범위를 먼저 좁힌 다음 status를 검사할 수 있다.
EXPLAIN ANALYZE
SELECT COUNT(*)
FROM order_index_demo FORCE INDEX (idx_tenant_status_created)
WHERE tenant_id = 7
AND status >= 'PAID'
AND created_at >= '2026-02-01 00:00:00'
AND created_at < '2026-02-02 00:00:00';
EXPLAIN ANALYZE
SELECT COUNT(*)
FROM order_index_demo FORCE INDEX (idx_tenant_created_status)
WHERE tenant_id = 7
AND status >= 'PAID'
AND created_at >= '2026-02-01 00:00:00'
AND created_at < '2026-02-02 00:00:00';
SELECT COUNT(*) AS matched_rows
FROM order_index_demo
WHERE tenant_id = 7
AND status >= 'PAID'
AND created_at >= '2026-02-01 00:00:00'
AND created_at < '2026-02-02 00:00:00';
실행 결과(MySQL 8.0.x):
다음은 검증된 TREE 출력에서 핵심 iterator만 발췌한 것이다. 실행 시간은 환경에 따라 달라지므로 생략하고 actual rows와 탐색 범위를 보존했다.
mysql> EXPLAIN ANALYZE SELECT COUNT(*)
-> FROM order_index_demo FORCE INDEX (idx_tenant_status_created) ...;
-> Aggregate: count(0) (actual rows=1 loops=1)
-> Filter: tenant_id = 7 AND status >= 'PAID' AND 하루 범위
(actual rows=6 loops=1)
-> Covering index range scan using idx_tenant_status_created
over (tenant_id = 7 AND 'PAID' <= status AND 날짜 하한)
(actual rows=307 loops=1)
mysql> EXPLAIN ANALYZE SELECT COUNT(*)
-> FROM order_index_demo FORCE INDEX (idx_tenant_created_status) ...;
-> Aggregate: count(0) (actual rows=1 loops=1)
-> Filter: tenant_id = 7 AND status >= 'PAID' AND 하루 범위
(actual rows=6 loops=1)
-> Covering index range scan using idx_tenant_created_status
over (tenant_id = 7 AND 날짜 범위 AND 'PAID' <= status)
(actual rows=10 loops=1)
mysql> SELECT COUNT(*) AS matched_rows FROM order_index_demo WHERE ...;
+--------------+
| matched_rows |
+--------------+
| 6 |
+--------------+
1 row in set (0.00 sec)
같은 6건을 반환했지만 첫 순서는 307개 index entry를 range scan한 뒤 6건을 남겼고, 두 번째 순서는 날짜를 앞에서 좁혀 10개를 읽고 6건을 남겼다. 작은 fixture의 절대 시간 차이는 일반화할 수 없지만, 첫 range와 뒤 column의 순서가 읽는 entry 수를 바꾸는 메커니즘은 확인할 수 있다.
문자열 range는 collation 순서에 따라 의미가 달라진다. 이 fixture는 비교를 명확히 하려고 ascii_bin을 사용했다. 업무 status의 순서를 문자열 대소 관계로 표현하는 설계는 권장하지 않는다. 운영에서는 명시적인 status 집합과 정확한 predicate를 사용해야 한다.
11. 검색과 정렬을 동시에 지원하는 경우
(tenant_id, status, created_at, order_id)는 앞의 두 column이 하나의 값으로 고정되면 leaf 순서가 곧 (created_at, order_id) 순서가 된다. 따라서 다음 첫 query는 별도 filesort 없이 처리될 가능성이 높다. 반면 status가 두 값이면 PAID 구간과 SHIPPED 구간이 각각 시간순으로 정렬되어 있을 뿐, 두 구간을 합친 전역 시간순은 아니므로 filesort가 필요할 수 있다.
EXPLAIN
SELECT order_id, tenant_id, status, created_at
FROM order_index_demo FORCE INDEX (idx_tenant_status_created)
WHERE tenant_id = 7
AND status = 'PAID'
ORDER BY created_at, order_id
LIMIT 10;
EXPLAIN
SELECT order_id, tenant_id, status, created_at
FROM order_index_demo FORCE INDEX (idx_tenant_status_created)
WHERE tenant_id = 7
AND status IN ('PAID', 'SHIPPED')
ORDER BY created_at, order_id
LIMIT 10;
실행 결과(MySQL 8.0.x):
아래 표는 두 EXPLAIN 결과의 핵심 열만 발췌했다.
mysql> EXPLAIN SELECT ... WHERE tenant_id = 7 AND status = 'PAID'
-> ORDER BY created_at, order_id LIMIT 10;
+------+---------------------------+---------+-------------+------+-------------+
| type | key | key_len | ref | rows | Extra |
+------+---------------------------+---------+-------------+------+-------------+
| ref | idx_tenant_status_created | 18 | const,const | 200 | Using index |
+------+---------------------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT ... WHERE tenant_id = 7
-> AND status IN ('PAID', 'SHIPPED')
-> ORDER BY created_at, order_id LIMIT 10;
+-------+---------------------------+---------+------+------------------------------------------+
| type | key | key_len | rows | Extra |
+-------+---------------------------+---------+------+------------------------------------------+
| range | idx_tenant_status_created | 18 | 400 | Using where; Using index; Using filesort |
+-------+---------------------------+---------+------+------------------------------------------+
1 row in set (0.00 sec)
IN을 equality의 단순한 별칭으로 취급하면 이 차이를 놓치기 쉽다. 한 값의 equality와 여러 값의 여러 range는 filtering 관점에서는 비슷해 보여도 index ordering을 보존하는 방식이 다르다. EXPLAIN의 Extra에서 Using filesort 여부를 확인하고, 실제 latency와 examined rows를 함께 측정한다.
12. covering index와 탐색 범위는 별개다
Composite index 뒤에 projection column을 추가하면 table lookup을 피하는 covering index가 될 수 있다. 하지만 covering이라고 해서 탐색 범위까지 좁아지는 것은 아니다.
예를 들어 (tenant_id, status, created_at, order_id)에서 status가 빠진 tenant+time query는 order_id, status, created_at을 leaf에서 모두 읽을 수 있다. 그래서 Using index가 표시될 수 있다. 그러나 시간 range 앞의 status gap은 여전히 존재한다. 많은 leaf entry를 읽되 clustered lookup이 없는 계획일 수 있다.
반대도 가능하다. 탐색 범위는 매우 좁지만 SELECT에 큰 payload가 필요하면 secondary leaf에서 Primary Key를 얻은 뒤 clustered index를 다시 찾는다. 따라서 실행 계획은 두 질문으로 나누어 해석한다.
- 얼마나 많은 index entry를 탐색하는가? — equality/range prefix, access type, estimated/actual rows
- 각 entry에서 table row를 다시 읽는가? — covering 여부,
Using index, projection column
모든 SELECT column을 인덱스에 넣는 것은 해답이 아니다. 넓은 covering index는 storage, Buffer Pool, redo, DML latency와 schema 변경 비용을 늘린다. 자주 실행되고 latency에 민감한 query에 한해 write amplification과 교환할 가치가 있는지 측정해야 한다.
13. 흔한 오해와 실패 형태
13.1 WHERE에 등장한 모든 column이 탐색 범위를 줄인다고 생각한다
뒤 column이 ICP나 covering에 사용될 수는 있지만, 첫 range 또는 prefix gap 뒤에서는 읽을 index entry 수를 줄이지 못할 수 있다. possible_keys와 key만 보지 말고 key_len, access type, rows와 EXPLAIN ANALYZE의 actual rows를 확인한다.
13.2 선택도가 가장 높은 column을 무조건 첫 번째로 둔다
단일 column 선택도는 query family, tenant 격리, equality 조합, 정렬과 range 요구를 대체하지 못한다. 여러 equality 조건이 항상 함께 온다면 그 뒤의 range·ORDER BY와 단독 prefix query를 더 중요하게 보아야 한다.
13.3 한 Composite index가 모든 부분 조합을 대신한다고 생각한다
(a,b,c)는 일반적으로 (a), (a,b) query에는 유용하지만 (b), (c), (b,c)를 자동으로 최적화하지 않는다. skip scan이 선택될 수 있다는 가능성을 schema 설계의 보장으로 바꾸어서는 안 된다.
13.4 IN 목록을 단일 equality와 동일하게 본다
여러 값의 IN은 여러 탐색 구간을 만들 수 있다. filtering은 효율적이어도 뒤 column의 전역 정렬을 보존하지 못하거나 range 조합 수가 커질 수 있다. 큰 목록은 plan size, range optimizer memory와 실제 실행 비용까지 확인한다.
13.5 Using index를 “가장 좋은 index range”라는 뜻으로 읽는다
Using index는 대개 covering access 신호다. prefix가 끊긴 넓은 scan도 covering일 수 있다. 반대로 Using index condition은 ICP가 적용되었다는 뜻이지 모든 predicate가 B+Tree 경계를 만들었다는 뜻은 아니다.
13.6 비슷한 인덱스를 계속 추가한다
(tenant_id,status,created_at), (tenant_id,status,created_at,order_id), (tenant_id,created_at,status)를 모두 두면 read plan 선택지는 늘지만 insert/update마다 여러 B+Tree를 갱신해야 한다. 중복 prefix 인덱스는 uniqueness, covering, 정렬과 실제 query 사용량을 확인한 뒤 통합하거나 제거한다. 제거 후보는 MySQL 8.0의 Invisible index로 영향도를 검증할 수 있다.
14. 운영에서 확인할 지표와 진단 순서
Composite index 변경은 EXPLAIN 한 번으로 끝내지 않는다. 다음 순서로 검증한다.
- slow query 또는 statement digest에서 대표 query와 bind value 분포를 수집한다.
- equality, range,
IN,IS NULL,LIKE, ORDER BY와 projection을 query family별로 분류한다. - 실제 DDL의 column 순서, prefix length, visibility와 uniqueness를 확인한다.
EXPLAIN FORMAT=TREE또는EXPLAIN ANALYZE로 access path와 actual rows를 확인한다.- 예상 rows와 actual rows가 크게 다르면 table/index statistics, histogram과 column correlation을 점검한다.
- cold cache와 warm cache, 일반 tenant와 대형 tenant를 나누어 latency를 측정한다.
- 새 인덱스의 크기, DML latency, redo, Buffer Pool churn과 replica apply 영향을 비교한다.
- 배포 후 statement digest의 실행 횟수, rows examined와 tail latency를 지속 관찰한다.
EXPLAIN ANALYZE는 query를 실제로 실행한다. 읽기 query라도 큰 범위를 읽거나 lock과 resource를 소비할 수 있으므로 운영 primary에서 무조건 실행하지 않는다. production과 유사한 staging, read replica 또는 안전한 범위·시간대를 선택한다.
15. Aurora MySQL에서의 운영 해석
Aurora MySQL도 MySQL 호환 Optimizer와 B+Tree index를 사용하므로 leftmost prefix와 equality/range 원리는 그대로 적용된다. 분산 storage가 잘못된 Composite index 순서를 자동으로 보정하거나 불필요한 index entry scan을 없애지는 않는다.
Aurora 환경에서는 다음 차이를 함께 고려한다.
- Writer와 Reader는 compute instance별 Buffer Pool을 갖는다. 같은 SQL과 index라도 cache 상태, instance class와 동시 부하 때문에 체감 latency가 다를 수 있다.
- read scaling이 넓은 scan 자체를 저렴하게 만드는 것은 아니다. 비효율적인 query를 여러 Reader에 분산하면 총 I/O와 CPU 소비가 확대될 수 있다.
- Query plan과 실제 latency를 Database Insights 또는 Performance Insights, slow query log와 statement digest 관측치로 연결한다. 제품 기능과 보존 기간은 사용 중인 Aurora version과 설정을 확인한다.
- index 생성·변경은 writer의 DDL 영향, metadata lock, storage 증가, replica 가시성과 배포 시간을 사전에 검증한다. “online DDL” 표시만으로 무중단을 보장한다고 해석하지 않는다.
- cluster parameter group의 Optimizer 관련 설정과 engine version이 환경마다 다르면 계획이 달라질 수 있다. writer와 reader endpoint에서 실제 version과 설정을 확인한다.
Aurora에서도 인덱스 설계의 출발점은 “storage가 빠르다”가 아니라 “어떤 tuple 범위를 몇 건 읽고, 몇 건을 상위 layer에서 버리는가”다.
16. Composite index 설계 체크리스트
Query 구조
- 대표 query를 equality, range,
IN -
ORDER BY,GROUP BY
인덱스 정의
실행 검증
- hint 없는
EXPLAIN에서 실제 선택된key -
type,key_len, rows,Extra -
Using index - 안전한 환경에서
EXPLAIN ANALYZE
배포와 운영
17. 재현 객체 정리
DROP TABLE order_index_demo;
실행 결과(MySQL 8.0.x):
mysql> DROP TABLE order_index_demo;
Query OK, 0 rows affected (0.00 sec)
결론
Composite index 설계의 핵심은 column 이름을 많이 넣는 것이 아니라 왼쪽부터 어떤 equality prefix가 고정되고, 어디서 첫 range가 시작되며, 그 뒤 column이 탐색·필터·정렬·covering 중 어떤 역할을 하는지 구분하는 것이다. (a,b,c)라는 정의는 단순한 목록이 아니라 tuple의 정렬 계약이다.
실무에서는 “equality column을 앞에, range column을 뒤에”라는 출발 규칙을 사용하되, 이를 절대 공식으로 만들지 않아야 한다. 조건이 빠지는 query family, 여러 값의 IN, ORDER BY, data skew, covering과 쓰기 비용이 최종 순서를 결정한다. 다음 단계에서는 Composite index의 selectivity와 column correlation이 Optimizer의 cardinality 추정에 어떤 오차를 만들고, extended statistics와 histogram을 언제 검토해야 하는지 연결해 살펴볼 수 있다.