MySQL 优化器：成本模型、统计信息、索引选择与优化器误判（117）

知识库文档 118 / 251

阅读时间：约 45 分钟 | 难度：中高级 | 编号：117

一、引言：优化器为什么有时"聪明反被聪明误"？

MySQL 优化器（Optimizer）是 SQL 执行的"大脑"。它负责决定使用哪个索引、以什么顺序 JOIN 表、是否使用临时表等关键决策。但优化器并非万能——它基于统计信息和成本模型做决策，而这两者都可能出错。

理解优化器的工作原理，才能解释为什么有时加了索引反而更慢，为什么 EXPLAIN 显示的 rows 和实际相差甚远，以及为什么优化器会选择"看起来不对"的执行计划。

二、成本模型：优化器的"计价器"

2.1 什么是成本模型

优化器不为每个查询做实际执行，而是通过**成本模型（Cost Model）**估算各种执行计划的代价，选择成本最低的方案。

成本 = I/O 成本 + CPU 成本

code

总成本 = (读取的页数 × io_block_read_cost) + (扫描的行数 × row_evaluate_cost)

MySQL 的默认成本常量：

参数	默认值	含义
io_block_read_cost	1.0	从磁盘读取一个页的代价
memory_block_read_cost	0.25	从内存读取一个页的代价
row_evaluate_cost	0.1	处理一行的 CPU 代价

2.2 成本计算示例

sql

-- 表 users: 100 万行，每页约 32 行，约 31250 个页
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 18;

优化器计算：

全表扫描成本：31250（页数）× 1.0（I/O）+ 1000000（行数）× 0.1（CPU）= 31250 + 100000 = 131250
使用索引 + 回表成本：索引扫描 + 回表扫描，可能更高
优化器选择成本最低的方案

三、统计信息：优化器的"眼睛"

3.1 统计信息的作用

优化器依赖统计信息来估算行数和数据分布。统计信息存储在 mysql.innodb_index_stats 和 mysql.innodb_table_stats 中。

sql

SELECT * FROM mysql.innodb_table_stats WHERE table_name = 'users';
-- n_rows: 估算的行数
-- clustered_index_size: 聚簇索引的页数
-- sum_of_other_index_sizes: 所有二级索引的页数

3.2 统计信息的更新机制

触发条件	说明
表数据变更超过 10%	自动更新（`innodb_stats_auto_recalc=ON`）
手动 `ANALYZE TABLE`	立即更新
首次打开表	如果统计信息为空，自动采样
持久化统计信息	`innodb_stats_persistent=ON` 时持久化到磁盘

3.3 统计信息的采样

InnoDB 通过随机采样20 个页（innodb_stats_persistent_sample_pages）来估算行数和数据分布。

sql

-- 增加采样页数，提高统计信息准确性
SET GLOBAL innodb_stats_persistent_sample_pages = 200;

采样页数越多，统计信息越准确，但 ANALYZE TABLE 耗时越长。

四、索引选择：优化器如何选索引

4.1 索引选择的决策过程

列出所有可能的索引（possible_keys）
对每个索引，估算使用它的成本
同时估算全表扫描的成本
选择成本最低的方案

sql

EXPLAIN SELECT * FROM users WHERE name = 'Alice' AND age = 25;
-- possible_keys: idx_name, idx_age
-- key: idx_name  ← 优化器认为 idx_name 成本更低

4.2 为什么优化器有时放弃索引

场景： 查询返回大量行（超过表的 20-30%）

sql

-- 80% 的行满足 age > 18
SELECT * FROM users WHERE age > 18;

优化器计算：

全表扫描：31250 页 × 顺序 I/O
索引 + 回表：80 万次随机 I/O（每次回表一个页）

全表扫描的成本远低于大量随机回表。 优化器选择全表扫描是合理的。

五、优化器误判：为什么它会"犯错"

5.1 误判的常见原因

原因	说明	影响
统计信息过时	大量 INSERT/DELETE 后未更新	rows 估算严重偏差
数据分布不均	采样假设数据均匀分布	选择性估算错误
JOIN 顺序错误	多表 JOIN 时估算误差累积	驱动表选择错误
索引相关性	多列之间的相关性	过滤条件估算错误
LIMIT 未考虑	成本模型不考虑 LIMIT	可能选择非最优索引

5.2 典型案例

案例一：LIMIT 导致误判

sql

SELECT * FROM orders WHERE status = 'PENDING' ORDER BY create_time DESC LIMIT 10;
-- 优化器可能选择 idx_create_time（避免排序），但实际 idx_status 更优（过滤后只有少量行）

案例二：统计信息过时

sql

-- 插入了 500 万行新数据，但统计信息未更新
-- 优化器仍按旧的 100 万行估算，选择了错误的索引

六、干预优化器：FORCE INDEX 与 Hints

6.1 索引提示

sql

-- 强制使用指定索引
SELECT * FROM users FORCE INDEX(idx_name) WHERE name = 'Alice';

-- 忽略指定索引
SELECT * FROM users IGNORE INDEX(idx_name) WHERE name = 'Alice';

-- 建议使用（优化器可忽略）
SELECT * FROM users USE INDEX(idx_name) WHERE name = 'Alice';

6.2 优化器提示（Optimizer Hints）

MySQL 5.7+ 提供了更灵活的 Optimizer Hints：

sql

-- 指定 JOIN 顺序
SELECT /*+ JOIN_ORDER(t2, t1) */ * FROM t1 JOIN t2 ON t1.a = t2.a;

-- 指定索引
SELECT /*+ INDEX(t idx_name) */ * FROM t WHERE name = 'Alice';

-- 禁止特定优化
SELECT /*+ NO_ICP(t) */ * FROM t WHERE name LIKE 'A%' AND age = 25;

6.3 使用原则

不轻易使用 FORCE INDEX。 优化器的判断在大多数情况下是正确的。只有在确认优化器误判，且数据分布短期内不会改变时，才使用索引提示。

七、Optimizer Trace：窥探优化器的决策过程

7.1 开启 Optimizer Trace

sql

SET optimizer_trace = 'enabled=on';
SET optimizer_trace_max_mem_size = 1000000;

SELECT * FROM users WHERE name = 'Alice' AND age = 25;

SELECT * FROM information_schema.OPTIMIZER_TRACE;
-- 输出优化器的完整决策过程

SET optimizer_trace = 'enabled=off';

7.2 Optimizer Trace 的关键信息

json

{
  "considered_execution_plans": [{
    "table": "users",
    "best_access_path": {
      "considered_access_paths": [{
        "access_type": "ref",
        "index": "idx_name",
        "rows": 100,
        "cost": 50.5,
        "chosen": true
      }, {
        "access_type": "ref",
        "index": "idx_age",
        "rows": 50000,
        "cost": 25000.0,
        "chosen": false,
        "cause": "cost"
      }]
    }
  }]
}

八、实战案例

案例一：优化器选择了"错误"的索引

sql

SELECT * FROM orders WHERE user_id = 100 AND status = 'PENDING' ORDER BY id DESC LIMIT 10;

idx_user_status: (user_id, status)
idx_id: (id) — 主键

优化器选择了 idx_id（因为 ORDER BY id DESC 可以利用主键顺序，且 LIMIT 10 可能很快凑够），但实际 user_id=100 AND status='PENDING' 的行很少，用 idx_user_status 更优。

解决方案： 使用 FORCE INDEX(idx_user_status) 或创建联合索引 (user_id, status, id)

案例二：统计信息过时导致全表扫描

sql

-- 批量插入后
SELECT * FROM logs WHERE create_time > '2024-01-01';
-- 优化器选择全表扫描，但实际满足条件的只有 1% 的数据

解决方案：ANALYZE TABLE logs 更新统计信息

九、总结

优化器是 MySQL 的核心组件，但它的决策基于估算而非精确计算：

成本模型：I/O 成本 + CPU 成本，选择总成本最低的方案
统计信息：随机采样估算，可能不准确，需要定期维护
索引选择：基于成本比较，回表代价高时可能选择全表扫描
误判场景：统计信息过时、数据分布不均、LIMIT 未考虑
干预手段：FORCE INDEX、Optimizer Hints、Optimizer Trace

十、面试官视角提问

Q1：EXPLAIN 显示 possible_keys 有 3 个索引，但 key 显示为 NULL（全表扫描）。优化器为什么放弃所有索引？给出三种可能的原因。

Q2：**innodb_stats_persistent_sample_pages=20** 意味着什么？如果设为 200 会有什么影响？在什么场景下应该增大这个值？

Q3：**SELECT * FROM t WHERE a=1 ORDER BY b LIMIT 10**，有索引 idx_a(a) 和 idx_b(b)。优化器会选哪个？如果数据分布不同，选择会变化吗？

Q4：Optimizer Trace 中 **chosen: false, cause: "cost"** 是什么意思？**cause: "heuristic"** 又是什么意思？

Q5：MySQL 8.0 引入了 Hash Join 替代 BNL。这对优化器的 JOIN 成本计算有什么影响？Hash Join 在什么场景下比 Nested Loop 更优？

Q6：你有 1 亿行的大表，**ANALYZE TABLE** 需要多久？在生产环境执行 ANALYZE TABLE 有什么风险？如何安全地更新大表的统计信息？

作者注：本文为系列第 117 篇，属于「08 MySQL 与关系型数据库」分类。

十一、面试官提问 · 参考答案

Q1：possible_keys 有 3 个索引，但 key=NULL 全表扫描。优化器为什么放弃所有索引？

参考答案：

三种可能原因：

回表代价太高：结果集占比超过 20-30%，优化器计算全表扫描（顺序 I/O）比索引+回表（随机 I/O）更便宜
统计信息严重不准：表刚进行了大量 INSERT/DELETE，统计信息显示行数很少，实际数据量很大，优化器基于错误信息做了错误判断
索引列被函数或隐式转换包裹：虽然是索引列，但 WHERE 条件中列被函数包裹（如 DATE(create_time)），实际上无法使用索引，possible_keys 只是候选，实际无法使用

Q2：意味着什么？什么时候该增大？

参考答案：

意味着 InnoDB 在 ANALYZE TABLE 时随机采样 20 个页来估算表的行数和数据分布。

增大到 200 的影响：

优点：统计信息更准确，优化器决策更可靠
缺点：ANALYZE TABLE 耗时增加约 10 倍

应该增大的场景：

大表（>1 亿行）且数据分布不均
经常出现优化器误判（选错索引、选错 JOIN 顺序）
表数据变更频繁但 ANALYZE 间隔长

不应该增大的场景：

小表（<100 万行）
数据分布均匀的表
频繁执行 ANALYZE TABLE 的场景

Q3：，索引 idx_a(a) 和 idx_b(b)，优化器选哪个？

参考答案：

取决于数据分布，优化器会基于成本估算选择。

场景一：a=1 的行很少（<100 行）

优化器选择 idx_a：用 idx_a 找到 a=1 的少量行，回表后排序（filesort），成本低
选择 idx_b 的代价：扫描 idx_b 全索引，对每行回表判断 a=1，凑够 10 行再停，可能很快也可能很慢

场景二：a=1 的行很多（>10000 行）

优化器可能选择 idx_b：利用 idx_b 天然有序，扫描时判断 a=1，凑够 10 行就停，不需要 filesort
选择 idx_a 的代价：找到大量行后还需要 filesort

最优方案：创建联合索引 idx_ab(a, b)，同时解决过滤和排序。

Q4：Optimizer Trace 中 vs 的区别？

参考答案：

**cause: "cost"**：优化器计算了该方案的成本，发现比其他方案成本高，基于成本模型放弃。

**cause: "heuristic"**：优化器基于启发式规则直接放弃，不计算成本。例如：

索引不满足最左前缀 → 直接放弃
索引类型不匹配（如全文索引用于等值查询）

面试官评分要点： 候选人是否理解"cost"是成本比较的结果，"heuristic"是规则层面的直接排除。

Q5：MySQL 8.0 的 Hash Join 对优化器成本计算有什么影响？

参考答案：

Hash Join 改变了 JOIN 成本计算的基准。

MySQL 8.0 之前： 只有 Nested Loop Join（BNL），大表 JOIN 成本极高，优化器可能选择全表扫描+BNL。

MySQL 8.0 之后： Hash Join 让大表 JOIN 成本大幅降低：

构建阶段：将驱动表（较小的表）构建为 Hash Table（内存操作）
探测阶段：扫描被驱动表，对每行用 Hash 查找匹配（O(1)）

Hash Join 的优势场景：

两个表都很大，且没有合适的索引
JOIN 条件不是等值（如 ON t1.a > t2.b，MySQL 8.0.20+ 支持非等值 Hash Join）

成本计算的变化： Hash Join 的 CPU 成本高于 Nested Loop，但 I/O 成本远低于 BNL。对于大表 JOIN，Hash Join 的总成本更低。

Q6：1 亿行大表，ANALYZE TABLE 需要多久？生产环境的风险？

参考答案：

耗时： 取决于 innodb_stats_persistent_sample_pages。默认 20 个采样页，约 1-5 秒。设为 200 约 10-50 秒。

生产环境风险：

MDL 锁：ANALYZE TABLE 需要获取表的 MDL 读锁，会阻塞 DDL 操作
统计信息突变：采样结果可能和之前差异很大，导致优化器突然改变执行计划
Buffer Pool 污染：采样时会读取随机页，可能挤出热点页

安全更新大表统计信息的方法：

在业务低峰期执行
先在从库执行，观察执行计划变化
使用 SET PERSIST 增加采样页数，而非频繁 ANALYZE
定期（如每周）在维护窗口执行，而非每次数据变更后执行

全文完 | 编号 117 | 分类 08 MySQL 与关系型数据库

MySQL 优化器：成本模型、统计信息、索引选择与优化器误判（117）

一、引言：优化器为什么有时"聪明反被聪明误"？

二、成本模型：优化器的"计价器"

2.1 什么是成本模型

2.2 成本计算示例

三、统计信息：优化器的"眼睛"

3.1 统计信息的作用

3.2 统计信息的更新机制

3.3 统计信息的采样

四、索引选择：优化器如何选索引

4.1 索引选择的决策过程

4.2 为什么优化器有时放弃索引

五、优化器误判：为什么它会"犯错"

5.1 误判的常见原因

5.2 典型案例

六、干预优化器：FORCE INDEX 与 Hints

6.1 索引提示

6.2 优化器提示（Optimizer Hints）

6.3 使用原则

七、Optimizer Trace：窥探优化器的决策过程

7.1 开启 Optimizer Trace

7.2 Optimizer Trace 的关键信息

八、实战案例

案例一：优化器选择了"错误"的索引

案例二：统计信息过时导致全表扫描

九、总结

十、面试官视角提问

十一、面试官提问 · 参考答案

Q1：possible_keys 有 3 个索引，但 key=NULL 全表扫描。优化器为什么放弃所有索引？

Q2： 意味着什么？什么时候该增大？

Q3：，索引 idx_a(a) 和 idx_b(b)，优化器选哪个？

Q4：Optimizer Trace 中 vs 的区别？

Q5：MySQL 8.0 的 Hash Join 对优化器成本计算有什么影响？

Q6：1 亿行大表，ANALYZE TABLE 需要多久？生产环境的风险？

Q2：意味着什么？什么时候该增大？