앞서 MySQL 옵티마이저가 실행 계획을 세우는 과정을 살펴봤다. 옵티마이저는 통계 정보(Cardinality)를 기반으로 여러 실행 계획 후보 중 가장 비용(Cost)이 낮은 계획을 선택한다고 했다.
그렇다면 더 깊이 들어가보자. "옵티마이저는 Cost를 정확히 어떻게 계산하는가?"
이번 글에서는 MySQL 옵티마이저의 Cost Model을 상세히 분석하고, 실제 계산 공식과 과정을 파헤쳐본다.
1. Cost Model 개요
Cost란 무엇인가?
MySQL에서 Cost는 쿼리 실행 계획의 예상 자원 소비량을 나타내는 논리적 단위다.
Cost는 두 가지 요소로 구성된다:
- I/O Cost: 디스크/메모리에서 데이터 페이지를 읽는 비용
- CPU Cost: 행을 평가하고 조건을 비교하는 연산 비용
Total Cost = I/O Cost + CPU CostCost의 기원: 역사적으로 1 Cost는 1990년대 하드디스크에서 1번의 랜덤 I/O 비용을 나타냈다. 현대의 SSD 환경에서는 절대적 의미보다는 상대적 비교 지표로 사용된다.
Cost Model의 관리 방식
MySQL은 Cost 파라미터를 두 개의 시스템 테이블로 관리한다:
-- Server 레벨 Cost 파라미터 확인
SELECT * FROM mysql.server_cost;
-- Storage Engine 레벨 Cost 파라미터 확인
SELECT * FROM mysql.engine_cost;이 테이블들의 특징:
- 서버 시작 시 메모리에 로드됨
FLUSH OPTIMIZER_COSTS명령으로 재로드 가능- 세션 시작 시의 값이 해당 세션 동안 유지됨
2. Cost 파라미터 상세
server_cost 테이블
Server Layer에서 수행하는 작업의 비용
| 파라미터명 | 기본값 | 설명 |
|---|---|---|
row_evaluate_cost | 0.2 | 행 조건 평가 비용 (WHERE 절) |
key_compare_cost | 0.1 | 인덱스 키 비교 비용 (정렬, Filesort) |
memory_temptable_create_cost | 2.0 | 메모리 임시 테이블 생성 비용 |
memory_temptable_row_cost | 0.2 | 메모리 임시 테이블의 row 처리 비용 |
disk_temptable_create_cost | 40.0 | 디스크 임시 테이블 생성 비용 |
disk_temptable_row_cost | 1.0 | 디스크 임시 테이블의 row 처리 비용 |
engine_cost 테이블
Storage Engine에서 수행하는 작업의 비용 (InnoDB 기준)
| 파라미터명 | 기본값 | 설명 |
|---|---|---|
io_block_read_cost | 1.0 | 디스크에서 블록 읽기 비용 |
memory_block_read_cost | 1.0 | 메모리 버퍼에서 블록 읽기 비용 |
주의사항:
- MySQL 8.0부터
memory_block_read_cost와io_block_read_cost가 동일하게 1.0 - MySQL 8.0 이전에는
memory_block_read_cost가 0.25였음 - MySQL 8.0은 실제 메모리 상주 비율을 동적으로 계산하여 반영
3. Cost 파라미터는 가중치다
많은 사람들이 궁금해하는 부분: "Cost 파라미터는 어떻게 사용되는가?"
답: 각 파라미터는 해당 작업의 상대적 비용 가중치로 작동한다.
계산 방식 예시
-- Full Table Scan Cost 계산
I/O Cost = (페이지 수) × io_block_read_cost
CPU Cost = (행 수) × row_evaluate_cost
-- 구체적 예시:
Total Cost = (1000 pages × 1.0) + (50000 rows × 0.2)
= 1000 + 10000
= 11000가중치 조정의 효과
만약 row_evaluate_cost를 0.2 → 1.0으로 증가시키면:
UPDATE mysql.server_cost
SET cost_value = 1.0
WHERE cost_name = 'row_evaluate_cost';
FLUSH OPTIMIZER_COSTS;변화:
Before: 1000 + (50000 × 0.2) = 11000
After: 1000 + (50000 × 1.0) = 51000→ Table Scan이 약 5배 비싸진 것처럼 보이므로, 옵티마이저는 Index를 더 선호하게 된다.
Cost의 실제 의미
Cost 파라미터는:
- 절대적 시간이 아님 (1 Cost = 1ms 같은 개념이 아님)
- 상대적 비교 지표
- "A 작업이 B 작업보다 N배 비싸다"는 의미
따라서:
io_block_read_cost = 1.0,row_evaluate_cost = 0.2- → "디스크 읽기는 행 평가보다 5배 비싸다"
4. server_cost vs engine_cost: 누가 무엇을 하는가?
직관적 의문: "조건 평가는 연산인데 왜 server_cost지?"
많은 사람들이 이렇게 생각한다:
- "WHERE 조건 평가 = 연산 = Engine이 수행 = engine_cost"
하지만 실제로는 다르다.
핵심 차이: 책임 주체
| 구분 | server_cost | engine_cost |
|---|---|---|
| 책임 주체 | MySQL Server | Storage Engine (InnoDB) |
| 주요 작업 | CPU 연산, 조건 평가, 정렬 | 디스크/메모리 I/O, 페이지 읽기 |
| 스토리지 독립성 | 모든 Engine 공통 | Engine별로 다를 수 있음 |
구체적인 실행 흐름
┌─────────────────────────────────────┐
│ MySQL Server Layer │
│ │
│ - WHERE 조건 평가 ← server_cost │
│ - JOIN 처리 │
│ - ORDER BY 정렬 ← server_cost │
│ - 임시 테이블 ← server_cost │
│ │
└──────────────┬──────────────────────┘
│ "데이터 주세요"
↓
┌─────────────────────────────────────┐
│ Storage Engine Layer (InnoDB) │
│ │
│ - 페이지 읽기 ← engine_cost │
│ - 인덱스 탐색 ← engine_cost │
│ - 데이터 반환 │
│ │
└─────────────────────────────────────┘실제 코드로 보는 흐름
// MySQL Server 코드 (의사 코드)
for (row in storage_engine.scan()) {
// ← 여기서 조건 평가! (Server Layer)
if (row.status == 'active') { // row_evaluate_cost 발생
results.add(row);
}
}핵심:
- Storage Engine은 단순히 row를 반환만 함
- 조건 평가는 MySQL Server가 수행 →
server_cost
예외: Index Condition Pushdown (ICP)
MySQL 8.0에서는 일부 조건을 Engine으로 "push down" 가능:
SELECT * FROM users
WHERE age > 30 AND name LIKE 'John%';
-- age > 30: Engine에서 평가 (Index 있음)
-- name LIKE 'John%': Engine에서 평가 가능 (ICP)EXPLAIN에 Using index condition이 나오면 ICP가 작동한 것이다.
5. Page: I/O의 최소 단위
Page 개념 이해하기
InnoDB의 기본 페이지 크기: 16KB
┌─────────────────────────────────────┐
│ Table Data │
├─────────────────────────────────────┤
│ Page 1 (16KB) │
│ - Row 1, Row 2, ... Row ~80 │
├─────────────────────────────────────┤
│ Page 2 (16KB) │
│ - Row 81, Row 82, ... Row ~160 │
├─────────────────────────────────────┤
│ Page 3 (16KB) │
│ - Row 161, Row 162, ... │
└─────────────────────────────────────┘왜 Page 단위로 읽는가?
OS와 디스크는 개별 row가 아닌 Block 단위로 I/O를 수행하기 때문이다.
Application: "Row 1개만 주세요"
↓
Storage Engine: "Row가 속한 Page 전체를 읽어야 함"
↓
OS: "디스크에서 16KB Block 읽기"따라서:
- Row 1개를 읽어도 → Page 전체(16KB)를 읽음
- Cost 계산도 Page 단위로 수행
실제 예시
CREATE TABLE users (
id INT,
name VARCHAR(100),
email VARCHAR(100)
);
-- Row 크기: 약 200 bytes
-- 1 Page (16KB)에 약 80개의 row가 들어감
-- 16384 bytes / 200 bytes ≈ 80 rows/page
-- 100만 개 row가 있다면
-- → 약 12,500 pages (1,000,000 / 80)Buffer Pool과 Page
MySQL의 메모리 관리:
┌─────────────────────────────────────┐
│ Buffer Pool (메모리) │
├─────────────────────────────────────┤
│ Cached Page 1 │
│ Cached Page 2 │
│ Cached Page 3 │
│ ... │
└─────────────────────────────────────┘
↕ (캐시 미스 시)
┌─────────────────────────────────────┐
│ Disk Storage │
└─────────────────────────────────────┘Cost 계산 시:
- Page가 메모리에 있으면:
memory_block_read_cost(빠름) - Page가 디스크에 있으면:
io_block_read_cost(느림)
Page 크기 확인
SHOW VARIABLES LIKE 'innodb_page_size';
-- 기본값: 16384 (16KB)6. 주요 Access Method별 Cost 계산 공식
이제 실제로 어떻게 Cost가 계산되는지 구체적인 공식을 살펴보자.
6.1 Full Table Scan (전체 테이블 스캔)
Total Cost = I/O Cost + CPU CostI/O Cost 계산:
I/O Cost = Number of Pages × Cost per Page
Cost per Page = (In-Memory Ratio × memory_block_read_cost)
+ (On-Disk Ratio × io_block_read_cost)구체적 계산:
Number of Pages = 테이블의 Clustered Index 페이지 수
= table.stat_clustered_index_sizeMySQL 8.0의 개선사항:
- 인덱스의 메모리 상주 비율을 Buffer Pool 통계를 통해 동적으로 계산
- 예: 인덱스의 70%가 메모리에 있다면
Cost per Page = (0.7 × 1.0) + (0.3 × 1.0) = 1.0
CPU Cost 계산:
CPU Cost = Number of Rows × row_evaluate_cost + 기본 오버헤드
= Table Rows × 0.2 + 0.01전체 예시:
-- 테이블 정보
-- Pages: 1000
-- Rows: 30000
-- Memory 비율: 100%
I/O Cost = 1000 × 1.0 = 1000
CPU Cost = 30000 × 0.2 + 0.01 = 6000.01
Total Cost = 1000 + 6000.01 = 7000.01Full Table Scan = Clustered Index Scan
중요한 사실: InnoDB에서 테이블 데이터는 Clustered Index 구조로 저장된다.
InnoDB 테이블 = Clustered Index
┌─────────────────────────────────────┐
│ Clustered Index (Primary Key) │
├─────────────────────────────────────┤
│ Leaf Node = 실제 데이터 │
│ - Row 1: (id=1, name, email) │
│ - Row 2: (id=2, name, email) │
│ - Row 3: (id=3, name, email) │
└─────────────────────────────────────┘따라서:
-- Full Table Scan
SELECT * FROM users; -- WHERE 없음
-- 실제로는 Clustered Index 전체를 스캔
-- → "인덱스의 메모리 상주 비율" 관련 있음!6.2 Index Scan (인덱스 풀 스캔)
보조 인덱스를 처음부터 끝까지 스캔하는 경우다.
Total Cost = I/O Cost + CPU CostI/O Cost 계산:
Number of Index Pages = Estimated Rows / Records per Page
Records per Page = Page Size / (Secondary Index Key Length + Primary Key Length)InnoDB의 특성:
- InnoDB는 보조 인덱스에 Primary Key를 자동으로 포함
- 인덱스 크기 = 보조 인덱스 컬럼 + Primary Key 컬럼
Index I/O Cost = Number of Index Pages ×
(Memory Ratio × memory_block_read_cost +
Disk Ratio × io_block_read_cost)예시:
-- 테이블 구조
-- Primary Key: id (4 bytes)
-- Secondary Index: name (50 bytes)
-- Page Size: 16KB = 16384 bytes
-- Total Rows: 10000
Records per Page = 16384 / (50 + 4) = 303
Number of Index Pages = 10000 / 303 ≈ 33
I/O Cost = 33 × 1.0 = 33
CPU Cost = 10000 × 0.2 = 2000
Total Cost = 33 + 2000 = 20336.3 Index Range Scan (인덱스 범위 스캔)
WHERE 조건으로 인덱스의 일부만 스캔하는 경우다.
Total Cost = Range I/O Cost + CPU Cost + (Table Lookup Cost)Range I/O Cost:
Range I/O Cost = Full Scan I/O Cost × (Ranges + Estimated Rows) / Total Rows여기서:
Ranges: 읽을 범위의 개수Estimated Rows: 조건을 만족하는 예상 행 수Total Rows: 테이블 전체 행 수
Row 수 추정 방법:
1) Index Dive (정확한 방법)
-- 실제 인덱스 통계를 직접 조회하여 정확한 row 수 계산
-- eq_range_index_dive_limit 파라미터로 제어
-- 기본값: 200 (IN 절의 값이 200개 이하일 때만 사용)2) records_per_key 통계 사용 (빠른 방법)
Estimated Rows = Total Rows / CardinalityTable Lookup Cost (Secondary Index인 경우):
보조 인덱스 사용 시 실제 데이터를 읽기 위한 Primary Key Lookup이 필요하다.
Table Lookup Cost = Estimated Rows × io_block_read_cost
= Estimated Rows × 1.0MySQL은 각 row마다 하나의 페이지 읽기가 필요하다고 단순화하여 계산한다.
전체 예시:
-- Index Range Scan with Table Lookup
-- WHERE status = 'active'
-- Index: idx_status (Secondary Index)
-- Estimated Rows: 1000
-- Total Rows: 100000
-- Index Pages: 100
Range I/O Cost = 100 × (1 + 1000) / 100000 = 1.01
Table Lookup Cost = 1000 × 1.0 = 1000
CPU Cost = 1000 × 0.2 = 200
Total Cost = 1.01 + 1000 + 200 = 1201.016.4 Primary Key Lookup (단일 행 조회)
SELECT * FROM users WHERE id = 123;Total Cost = I/O Cost + CPU Cost
= 1.0 + 0.2
= 1.2- I/O Cost: Primary Key는 B+Tree이므로 tree height만큼의 I/O 필요
- 일반적으로 3-4 depth이지만, MySQL은 단순화하여 1.0으로 계산
- CPU Cost: 행 평가 비용 0.2
7. Index Scan vs Index Range Scan 비교
많은 개발자들이 헷갈려하는 부분을 명확히 정리해보자.
Index Scan (Full Index Scan)
인덱스의 처음부터 끝까지 순차적으로 읽기
-- 예시: ORDER BY만 있고 WHERE 없음
SELECT * FROM users ORDER BY name;
-- name에 인덱스가 있다면
-- → Index Scan: 인덱스 전체를 순차 읽기Index: idx_name
┌──────────────────────────┐
│ Start │
├──────────────────────────┤
│ 'Alice' → Read │
│ 'Bob' → Read │
│ 'Charlie' → Read │
│ 'David' → Read │
│ 'Eve' → Read │
│ ... → Read │
│ 'Zoe' → Read │
├──────────────────────────┤
│ End │
└──────────────────────────┘Index Range Scan
인덱스의 특정 범위만 읽기
-- 예시: WHERE 조건으로 범위 제한
SELECT * FROM users WHERE name BETWEEN 'B' AND 'D';Index: idx_name
┌──────────────────────────┐
│ Start │
├──────────────────────────┤
│ 'Alice' (Skip) │
│ 'Bob' → Read ✓ │
│ 'Charlie' → Read ✓ │
│ 'David' → Read ✓ │
│ 'Eve' (Skip) │
│ ... (Skip) │
│ 'Zoe' (Skip) │
├──────────────────────────┤
│ End │
└──────────────────────────┘비교표
| 특징 | Index Scan | Index Range Scan |
|---|---|---|
| WHERE 조건 | 없거나 무관 | 있음 (범위 조건) |
| 읽는 범위 | 인덱스 전체 | 인덱스 일부 |
| EXPLAIN type | index | range |
| Cost | 높음 (전체 읽기) | 낮음 (일부만 읽기) |
실제 예시
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
order_date DATE,
status VARCHAR(20),
INDEX idx_date(order_date),
INDEX idx_status(status)
);1) Index Scan 발생
EXPLAIN SELECT * FROM orders ORDER BY order_date;
-- Result:
-- type: index (Index Scan)
-- key: idx_date
-- Extra: Using index2) Index Range Scan 발생
EXPLAIN SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';
-- Result:
-- type: range (Index Range Scan)
-- key: idx_date
-- Extra: Using index condition3) Index Range Scan (IN 조건)
EXPLAIN SELECT * FROM orders
WHERE status IN ('pending', 'processing');
-- Result:
-- type: range
-- key: idx_statusCost 차이
테이블 정보:
- Total rows: 1,000,000
- Index pages: 10,000
Index Scan:
모든 페이지 읽기 = 10,000 pages
Cost ≈ 10,000 + (1,000,000 × 0.2) = 210,000
Index Range Scan (10% 선택):
일부만 읽기 = 1,000 pages
Cost ≈ 1,000 + (100,000 × 0.2) = 21,000
→ 약 10배 차이!8. JOIN Cost 계산
Nested Loop Join (NLJ)
가장 기본적인 JOIN 방식: 중첩 반복문
# 의사 코드
for outer_row in outer_table:
for inner_row in inner_table:
if outer_row.key == inner_row.key:
result.add(merge(outer_row, inner_row))특징:
- 가장 단순한 방식
- Inner Table에 인덱스가 있으면 매우 효율적
- 작은 Outer Table + 큰 Inner Table with Index
Cost 계산:
Total Cost = Outer Table Cost +
(Outer Table Rows × Inner Table Cost per Row)예시:
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date = '2024-01-01';
-- orders (Outer): 100 rows (날짜 필터 후)
-- customers (Inner): 1,000,000 rows
-- customers.id: Primary Key (인덱스 있음)실행 과정:
1. orders 테이블에서 100개 행 읽기
2. 각 행마다:
- customers.id 인덱스로 O(log N) 조회
- 100번 × log(1,000,000) ≈ 100 × 20 = 2,000번 조회Cost:
Outer Table Cost: 100
Inner Table Cost: 100 rows × 1.2 (PK lookup) = 120
Total: 220EXPLAIN 표시:
EXPLAIN SELECT ...
-- Result:
id | table | type | key | rows
1 | orders | ref | idx_date| 100 -- Outer
1 | customers | eq_ref | PRIMARY | 1 -- Inner (PK lookup)Hash Join (MySQL 8.0.18+)
Hash Table을 사용한 효율적인 JOIN
1. Build Phase:
작은 테이블로 Hash Table 생성
┌─────────────────────────┐
│ Hash Table │
├─────────────────────────┤
│ Hash(key1) → Row1 │
│ Hash(key2) → Row2 │
│ Hash(key3) → Row3 │
└─────────────────────────┘
2. Probe Phase:
큰 테이블을 스캔하며 Hash Table 조회특징:
- 등호 조건(=) 에서 매우 효율적
- 인덱스 없어도 빠름
- 메모리 사용량 높음 (Hash Table)
- O(N + M) 시간 복잡도
Cost 계산:
Total Cost = Build Phase Cost + Probe Phase Cost
Build Phase Cost = Smaller Table Scan Cost + Hash Table Creation
= Rows × row_evaluate_cost
Probe Phase Cost = Larger Table Scan Cost + Hash Lookup
= Rows × row_evaluate_cost예시:
SELECT *
FROM large_table1 l1
JOIN large_table2 l2 ON l1.id = l2.foreign_id
-- 둘 다 큰 테이블, 인덱스 없음EXPLAIN 표시:
EXPLAIN FORMAT=TREE SELECT ...
-- Result:
-> Hash join (l1.id = l2.foreign_id)
-> Table scan on l1
-> Hash
-> Table scan on l29. JOIN 알고리즘 선택 기준
MySQL 옵티마이저는 다음과 같은 기준으로 JOIN 알고리즘을 선택한다:
Decision Tree:
Inner Table에 인덱스 있나?
├─ Yes → Nested Loop Join
│ (인덱스로 빠른 조회)
│
└─ No → 조건이 등호(=)인가?
├─ Yes → Hash Join (MySQL 8.0.18+)
│ (Hash Table로 빠른 조회)
│
└─ No → Block Nested Loop / Hash Join
(부등호, LIKE 등)실제 비교 예시
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(10,2),
INDEX idx_user_id(user_id) -- ← 인덱스 있음!
);
-- 100 users
-- 10,000 ordersCase 1: Nested Loop (인덱스 있음)
EXPLAIN SELECT *
FROM users u
JOIN orders o ON u.id = o.user_id;
-- Result:
-- type: ALL (users), ref (orders)
-- Extra: Using index (orders의 idx_user_id 사용)
Cost:
Users scan: 100
Orders lookup: 100 × 1.2 = 120
Total: 220Case 2: Hash Join (인덱스 제거)
ALTER TABLE orders DROP INDEX idx_user_id;
EXPLAIN FORMAT=TREE SELECT *
FROM users u
JOIN orders o ON u.id = o.user_id;
-- Result:
-- Hash join (u.id = o.user_id)
Cost:
Build Hash (users): 100
Probe (orders): 10,000
Total: 10,100Case 3: Nested Loop WITHOUT Index (최악)
-- 인덱스 없이 Nested Loop 강제
SELECT /*+ NO_HASH_JOIN(u, o) */ *
FROM users u
JOIN orders o ON u.id = o.user_id;
Cost:
Users scan: 100
Orders scan × 100번: 100 × 10,000 = 1,000,000
Total: 1,000,100 (!)10. 실전 Cost 계산 예제
이론을 실제 시나리오에 적용해보자.
예제 1: Table Scan vs Index Scan 비교
-- 테이블 정보
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100),
status VARCHAR(20),
INDEX idx_status(status)
);
-- 통계
-- Total Rows: 1,000,000
-- Clustered Index Pages: 10,000
-- status 인덱스 Pages: 2,000
-- status Cardinality: 5 (active, inactive, pending, deleted, banned)-- 쿼리
SELECT * FROM users WHERE status = 'active';Option 1: Table Scan
I/O Cost = 10,000 × 1.0 = 10,000
CPU Cost = 1,000,000 × 0.2 = 200,000
Total = 210,000Option 2: Index Range Scan + Table Lookup
Estimated Rows = 1,000,000 / 5 = 200,000
Index I/O Cost = 2,000 × (1 + 200,000) / 1,000,000 = 404
Table Lookup Cost = 200,000 × 1.0 = 200,000
CPU Cost = 200,000 × 0.2 = 40,000
Total = 404 + 200,000 + 40,000 = 240,404결론: Table Scan이 더 효율적! (210,000 < 240,404)
이것이 바로 "Selectivity가 낮으면 Index를 타지 않는다"의 원리다.
예제 2: Covering Index의 효과
-- 동일한 쿼리에 Covering Index 사용
CREATE INDEX idx_status_covering ON users(status, email);
SELECT email FROM users WHERE status = 'active';Option 2-1: Covering Index Scan (Table Lookup 불필요)
Estimated Rows = 200,000
Index Pages = 3,000 -- email 포함으로 증가
Index I/O Cost = 3,000 × (1 + 200,000) / 1,000,000 = 606
CPU Cost = 200,000 × 0.2 = 40,000
Total = 606 + 40,000 = 40,606결론: Covering Index는 Table Lookup을 제거하여 비용을 대폭 감소!
Table Scan: 210,000
Index + Table Lookup: 240,404
Covering Index: 40,606 ← 가장 효율적!11. EXPLAIN으로 Cost 확인하기
EXPLAIN FORMAT=JSON
EXPLAIN FORMAT=JSON
SELECT * FROM users WHERE status = 'active'\G{
"query_cost": "240404.00",
"table": {
"cost_info": {
"read_cost": "200404.00", // I/O Cost + Range Scan Cost
"eval_cost": "40000.00", // CPU Cost (rows × 0.2)
"prefix_cost": "240404.00", // Total Cost
"data_read_per_join": "152M"
},
"rows_examined_per_scan": 200000,
"rows_produced_per_join": 200000
}
}Cost 구성:
read_cost= Index I/O + Table Lookupeval_cost= Rows × 0.2prefix_cost= read_cost + eval_cost
EXPLAIN ANALYZE (MySQL 8.0.18+)
실제 실행 결과와 추정 비교:
EXPLAIN ANALYZE
SELECT * FROM users WHERE status = 'active';-> Filter: (users.status = 'active')
(cost=240404 rows=200000)
(actual time=0.123..456.789 rows=150000 loops=1)
-> Table scan on users
(cost=210000 rows=1000000)
(actual time=0.089..432.123 rows=1000000 loops=1)분석:
cost=240404: 옵티마이저가 예상한 비용rows=200000: 옵티마이저가 예상한 row 수actual rows=150000: 실제 반환된 row 수- 추정과 실제의 차이 → 통계 정보 갱신 필요 신호
12. Optimizer Trace로 Cost 계산 과정 확인
가장 상세한 Cost 계산 과정을 보려면 Optimizer Trace를 사용한다.
-- Optimizer Trace 활성화
SET optimizer_trace='enabled=on';
SET optimizer_trace_max_mem_size=1000000;
-- 쿼리 실행
SELECT * FROM users WHERE status = 'active';
-- Trace 확인
SELECT * FROM information_schema.optimizer_trace\GTrace에서 확인할 수 있는 정보:
{
"considered_execution_plans": [
{
"plan_prefix": [],
"table": "users",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_status",
"rows": 200000,
"cost": 240404,
"chosen": true
},
{
"access_type": "scan",
"rows": 1000000,
"cost": 210000,
"chosen": false,
"cause": "cost"
}
]
},
"cost_for_plan": 240404,
"rows_for_plan": 200000,
"chosen": true
}
]
}Trace로 알 수 있는 것:
- 각 인덱스별 예상 row 수
- 각 실행 계획의 Cost 계산 과정
- 왜 특정 인덱스를 선택했는지
- 왜 특정 인덱스를 선택하지 않았는지
13. Cost Model 튜닝
언제 Cost Model을 수정해야 하는가?
수정 고려 상황:
1. SSD vs HDD 환경 차이 반영
2. 특정 워크로드에서 일관되게 잘못된 계획 선택
3. 메모리 기반 vs 디스크 기반 우선순위 조정
주의사항:
- Cost Model 변경은 모든 쿼리에 영향
- 프로덕션에서는 매우 신중하게 접근
- 대부분의 경우 Query Hint나 Index 추가가 더 안전
Cost Model 수정 예시
-- 예: SSD 환경에서 io_block_read_cost 감소
UPDATE mysql.engine_cost
SET cost_value = 0.5
WHERE cost_name = 'io_block_read_cost';
FLUSH OPTIMIZER_COSTS;-- 예: Row 평가 비용 증가하여 Table Scan을 덜 선호하게
UPDATE mysql.server_cost
SET cost_value = 0.5
WHERE cost_name = 'row_evaluate_cost';
FLUSH OPTIMIZER_COSTS;효과:
Before: I/O Cost 우선 (io=1.0, row_eval=0.2)
After: Row 평가 비용 증가 (io=0.5, row_eval=0.5)
→ Table Scan이 상대적으로 더 비싸짐
→ Index를 더 선호하게 됨14. 핵심 요약
Cost 계산의 기본 원리
1. Cost = I/O Cost + CPU Cost
- I/O: 페이지 읽기 비용 (디스크/메모리)
- CPU: 행 평가 및 비교 비용
2. Cost 파라미터 = 가중치
- 절대적 시간이 아닌 상대적 비교 지표
작업량 × 가중치 = Cost
3. server_cost vs engine_cost
- server: MySQL Server의 CPU 작업 (조건 평가, 정렬)
- engine: Storage Engine의 I/O 작업 (페이지 읽기)
4. Page = I/O의 최소 단위
- 기본 16KB
- Row 1개를 읽어도 Page 전체를 읽음
5. Full Table Scan = Clustered Index Scan
- InnoDB는 테이블을 Clustered Index로 저장
- "테이블 스캔"은 사실 "Clustered Index 전체 스캔"
Access Method별 Cost
| Method | I/O Cost | CPU Cost | 특징 |
|---|---|---|---|
| Table Scan | Pages × 1.0 | Rows × 0.2 | 모든 데이터 읽기 |
| Index Scan | Index Pages × 1.0 | Rows × 0.2 | 인덱스 전체 읽기 |
| Index Range | Partial Pages × 1.0 | Filtered Rows × 0.2 | 인덱스 일부만 |
| Index + Lookup | Index + (Rows × 1.0) | Rows × 0.2 | Secondary Index |
| PK Lookup | 1.0 | 0.2 | 단일 row |
JOIN 알고리즘 선택
인덱스 있음? → Nested Loop (최고)
↓ 없음
등호 조건? → Hash Join (빠름)
↓ 아님
Block NL / Hash Join (느림)최적화 체크리스트
통계 정보:
-ANALYZE TABLE로 통계 갱신
- Cardinality가 정확한가?
- 히스토그램이 필요한가?
인덱스:
- JOIN 컬럼에 인덱스 있는가?
- WHERE 조건에 인덱스 있는가?
- Covering Index로 개선 가능한가?
쿼리 분석:
EXPLAIN으로 실행 계획 확인EXPLAIN ANALYZE로 실제 vs 예상 비교- Optimizer Trace로 상세 분석
Cost 검증:
- Selectivity가 적절한가?
- Table Lookup이 과도한가?
- JOIN 순서가 최적인가?
마무리
MySQL 옵티마이저의 Cost 계산 메커니즘을 이해하면:
- "왜 이 인덱스를 선택했을까?" 를 수치로 설명할 수 있다
- "어떤 인덱스를 추가해야 할까?" 를 정량적으로 판단할 수 있다
- "실행 계획이 왜 잘못됐을까?" 를 근본 원인부터 분석할 수 있다
옵티마이저는 완벽하지 않다. 통계 정보의 부정확성, 균등 분포 가정, 컬럼 간 상관관계 무시 등의 한계가 있다. 하지만 Cost 계산 원리를 이해하면 이러한 한계를 인지하고 보완할 수 있다.
단순히 "인덱스를 타면 빠르다"를 넘어, "이 쿼리는 Cost가 240,404이고, Covering Index를 쓰면 40,606으로 줄어든다" 라고 말할 수 있는 수준에 도달한 것이다.
이제 여러분은 MySQL 옵티마이저와 대화할 수 있다. 🚀
참고 자료
시은이의 살아남기 시리즈!