开篇引入
"我明明建了索引，为什么查询还是全表扫描？" "EXPLAIN显示type=ALL，但我的WHERE条件明明可以用索引啊！"
这不是MySQL的bug，是优化器的选择。MySQL优化器基于成本模型选择执行计划，它认为全表扫描比用索引更快，所以选了全表扫描。
《高性能MySQL》第8章讲查询执行，这篇文章帮你理解MySQL优化器的工作原理。
MySQL执行查询的过程
查询执行流程
SQL语句
↓
语法解析器 → 生成解析树
↓
预处理器 → 检查表/列是否存在，权限
↓
查询优化器 → 生成执行计划
↓
存储引擎 → 执行计划，返回结果
两类优化策略
-- 静态优化：基于规则的优化
-- 编译时完成，与数据无关
SELECT * FROM users WHERE id = 1;
-- 优化器知道id是主键，直接用const访问
-- 动态优化：基于成本的优化
-- 执行时分析，需要读取统计信息
SELECT * FROM users WHERE age > 25;
-- 优化器需要知道有多少用户满足age>25
成本模型
成本计算的组成
-- MySQL优化器计算每个执行计划的总成本
-- 成本 = CPU成本 + IO成本
-- CPU成本：
-- - 读取和计算每行的成本
-- - 排序的成本
-- IO成本：
-- - 读取页面的成本
-- - 随机读 vs 顺序读
查看优化器决策
-- MySQL 8.0+: 使用optimizer_trace
SET optimizer_trace = 'enabled=on';
SET optimizer_trace_max_mem_size = 1048576;
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM information_schema.optimizer_trace;
成本参数
SHOW VARIABLES LIKE 'optimizer_cost%';
-- 成本参数（可调整）
-- optimizer_trace显示的cost_*值就是基于这些参数计算的
常见不走索引的原因
原因1：统计信息不准
-- 表数据变化后，统计信息可能过时
-- 导致优化器估算错误
-- 解决：ANALYZE TABLE更新统计信息
ANALYZE TABLE orders;
-- InnoDB采样页面数
SHOW VARIABLES LIKE 'innodb_stats_sample_pages';
-- 默认8，可以调大
SET GLOBAL innodb_stats_sample_pages = 64;
原因2：选择性太低
-- 假设status只有3个值：pending, paid, cancelled
-- 30%的记录是paid
-- 优化器认为：
-- - 索引扫描：需要读取30%数据 + 回表
-- - 全表扫描：顺序读取100%数据
-- - 结论：全表扫描可能更快
-- 解决：联合索引增加选择性
CREATE INDEX idx_user_status ON orders(user_id, status);
原因3：范围查询阻断
-- 索引：(user_id, created_at)
-- 查询：WHERE user_id > 100 AND created_at > '2024-01-01'
-- 问题：
-- user_id > 100 是范围查询
-- created_at > '2024-01-01' 无法利用索引
-- 解决：重写查询或调整索引
-- 改写为：
SELECT * FROM orders
WHERE user_id > 100
AND (created_at > '2024-01-01' OR created_at IS NULL);
原因4：数据量太小
-- 表只有100行
-- 优化器认为全表扫描更快
-- 索引维护成本 > 扫描成本
-- 解决：数据量增长后会自然使用索引
-- 或者FORCE INDEX强制使用
SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id = 1;
原因5：函数/运算阻断
-- WHERE YEAR(created_at) = 2024
-- 索引列被函数包装，无法使用
-- 解决：改写查询
WHERE created_at >= '2024-01-01'
AND created_at < '2025-01-01'
-- 或者：使用生成列+索引
ALTER TABLE orders ADD COLUMN year INT GENERATED ALWAYS AS (YEAR(created_at));
CREATE INDEX idx_year ON orders(year);
optimizer_trace分析
开启追踪
SET optimizer_trace = 'enabled=on',
optimizer_trace_max_mem_size = 1048576;
-- 执行查询
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
-- 查看追踪结果
SELECT * FROM information_schema.optimizer_trace\G
追踪结果解读
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [...]
}
},
{
"join_optimization": {
"cost_before_conditions": 1234.56,
"condition_processing": [...],
"substitute_derived_conditions": [...],
"table_dependencies": [...],
"ref_optimizer_key_candidates": [...],
"considered_execution_plans": [
{
"plan_prefix": [],
"plan": {
"rows_estimation": [
{
"table": "orders",
"range_analysis": {
"chosen_range_access_summary": {
"range_scan_alternatives": [
{
"type": "ref",
"index": "idx_user_status",
"rows": 100,
"cost": 120
}
]
}
}
}
]
},
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"estimated_cost": 120,
"rows": 100,
"use_extended_summary": true
},
{
"access_type": "scan",
"cost": 500,
"rows": 50000
}
]
}
}
]
}
}
]
}
关键信息：
considered_execution_plans: 所有被考虑的执行计划rows_estimation: 每个计划的行数估算best_access_path: 优化器选择的方案
优化器开关
控制优化器行为
SHOW VARIABLES LIKE 'optimizer_switch';
-- 常用开关：
-- index_merge: 索引合并
-- engine_condition_pushdown: 引擎条件下推
-- index_condition_pushdown: 索引条件下推（ICP）
-- derived_merge: 派生表合并
-- materialization: 物化
禁用某个优化
-- 禁用索引合并
SET optimizer_switch = 'index_merge=off';
-- 禁用ICP
SET optimizer_switch = 'index_condition_pushdown=off';
强制使用索引
USE INDEX
-- 建议优化器使用某个索引
SELECT * FROM orders USE INDEX (idx_user_id) WHERE user_id = 1;
FORCE INDEX
-- 强制使用某个索引（比USE更强）
SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id = 1;
-- 场景：优化器选错了索引
-- 可能是因为统计信息不准
IGNORE INDEX
-- 忽略某个索引（让优化器不选它）
SELECT * FROM orders IGNORE INDEX (idx_status) WHERE user_id = 1;
STRAIGHT_JOIN
-- 强制按查询写的顺序JOIN
SELECT * FROM orders STRAIGHT_JOIN users ON orders.user_id = users.id;
-- 告诉优化器：orders是驱动表，users是被驱动表
统计信息管理
InnoDB统计信息
-- InnoDB通过采样估算统计信息
-- 采样页面数由innodb_stats_sample_pages控制
-- 手动设置统计信息
ANALYZE TABLE orders;
-- 持久化统计信息（MySQL 8.0+）
ALTER TABLE orders STATS_PERSISTENT = 1;
-- 查看表统计信息
SELECT * FROM information_schema.tables
WHERE table_name = 'orders';
统计信息触发条件
-- InnoDB在以下情况更新统计信息：
-- 1. ANALYZE TABLE
-- 2. 表首次打开
-- 3. 表大小变化超过1/16
-- 4. 大数据删除（>表的1/16）
-- 可以关闭自动更新
SET GLOBAL innodb_stats_auto_recalc = OFF;
优化器限制
无法感知的因素
-- 优化器不知道：
-- 1. 数据在内存还是磁盘
-- 2. 其他查询的并发
-- 3. 应用缓存情况
-- 4. 网络延迟
优化器错误
-- 优化器不是完美的，可能选错计划
-- 常见原因：
-- 1. 统计信息过时
-- 2. 成本模型不准确
-- 3. 多表JOIN时组合爆炸
-- 解决：
-- 1. ANALYZE TABLE
-- 2. 调整成本参数
-- 3. 使用optimizer hints
-- 4. 改写查询
优化器Hints
表级Hints
-- 告诉优化器表扫描顺序
SELECT * FROM orders STRAIGHT_JOIN users ON orders.user_id = users.id;
-- 设置join buffer大小
SELECT /*+ JOIN_BUFFER_SIZE(1024 * 1024) */ * FROM orders o JOIN users u ON o.user_id = u.id;
索引级Hints
-- 使用指定索引
SELECT /*+ INDEX(orders idx_user_id) */ * FROM orders WHERE user_id = 1;
-- 忽略指定索引
SELECT /*+ NO_INDEX(orders idx_status) */ * FROM orders WHERE user_id = 1;
优化器参数Hints
-- 设置优化器成本参数
SELECT /*+ SET_VAR(optimizer_switch = 'index_merge=off') */
* FROM orders WHERE user_id = 1 OR status = 'paid';
常见问题解答
Q: 为什么LIKE '%abc'不能走索引？
-- LIKE 'abc%' 可以走索引（前缀匹配）
-- LIKE '%abc' 不能走索引（前导通配符）
-- LIKE '%abc%' 也不能走索引
-- 解决方案：
-- 1. 使用全文索引
-- 2. 使用搜索引擎（如Elasticsearch）
-- 3. 反转字符串+前缀索引
Q: 为什么OR两边都要建索引才能走索引？
-- WHERE a = 1 OR b = 2
-- 需要同时建(a)索引和(b)索引
-- MySQL会用Index Merge合并两个索引结果
-- 如果只有(a)索引，b条件会变成全表扫描
Q: 为什么优化器选择全表扫描而不是索引？
-- 原因可能是：
-- 1. 表太小（<100行）
-- 2. 索引选择性低（返回大部分数据）
-- 3. 统计信息不准
-- 4. 范围查询导致回表成本高
-- 解决：ANALYZE TABLE 或 FORCE INDEX
小结
- 优化器基于成本选择执行计划：不是基于规则
- 统计信息是成本估算的基础：过时会导致错误决策
- ANALYZE TABLE修复统计信息：很多"不走索引"问题可以解决
- optimizer_trace是分析神器：可以看到优化器的完整决策过程
- 选择性低的索引不受青睐：优化器认为全表扫描更快
- 范围查询阻断后续列：设计索引时考虑WHERE顺序
- optimizer hints强制优化器行为：USE INDEX / FORCE INDEX
- 优化器不是完美的：在特殊场景下可能选错计划
理解优化器的工作原理，才能在它"犯错"时正确干预。
延伸阅读
- 《高性能MySQL》第8章 查询性能优化
- MySQL 8.0 Reference Manual: Optimizer Hints
- MySQL 8.0 Reference Manual: Controlling the Optimizer
- optimizer_trace: dev.mysql.com/doc/refman/…
