详细介绍：MySQL优化器统计信息：性能调优关键

这段文档是 MySQL InnoDB 存储引擎中关于“优化器统计信息（Optimizer Statistics）”的配置说明，属于数据库性能调优的核心内容之一。

下面我将用通俗易懂的方式，逐段解读并总结其含义和实际意义。

---

一、什么是“优化器统计信息”？

在 MySQL 执行 SQL 查询时，查询优化器（Query Optimizer） 需要决定使用哪个索引、是否走索引、是否做全表扫描等。这个决策依赖于：

✅ 表和索引的数据分布情况 —— 比如：

• 表有多少行？
• 某个索引列有多少不同值（基数 cardinality）？
• 数据是否均匀分布？

这些信息就是 优化器统计信息（Optimizer Statistics）。

InnoDB 会通过采样分析数据来生成这些统计信息，帮助优化器选择“最优”的执行计划。

---

二、两种统计信息模式

InnoDB 支持两种统计信息管理方式：

类型	是否重启后保留	特点
✅ Persistent（持久化）	是 ✅	重启不丢失，执行计划更稳定
❌ Non-Persistent（非持久化）	否 ❌	每次重启或某些操作后重新计算，可能导致执行计划变化

---

1. 持久化统计信息（Persistent Optimizer Statistics）

✅ 默认开启：

innodb_stats_persistent = ON

这是 MySQL 5.6+ 的默认行为。

优势：

优点	说明
跨重启保持稳定	统计信息保存在磁盘上的系统表中，不会因 MySQL 重启而清空
执行计划更一致	减少因统计信息变化导致的执行计划突变（避免“SQL 昨天快，今天慢”）
可控制自动更新	可设置 innodb_stats_auto_recalc 控制是否在数据大变后自动重算
支持表级定制	可为每张表单独设置统计参数
可查看和手动修改	直接查询甚至修改统计值，用于调试或强制执行计划

---

关键配置参数

（1）全局开关

innodb_stats_persistent = ON/OFF

• 控制是否启用持久化统计。
• 建议始终 ON（默认值）。

innodb_stats_auto_recalc = ON/OFF

• 当表中超过 10% 的数据被修改后，是否自动触发 ANALYZE TABLE 来更新统计信息。
• 默认 ON。
• 如果你希望完全手动控制统计信息更新，可以设为 OFF。

（2）表级别设置（建表或改表时指定）

可以在 CREATE TABLE 或 ALTER TABLE 中指定：

CREATE TABLE t (
id INT PRIMARY KEY,
name VARCHAR(100),
KEY(name)
) ENGINE=InnoDB
STATS_PERSISTENT=1
STATS_AUTO_RECALC=1
STATS_SAMPLE_PAGES=50;

子句	含义
STATS_PERSISTENT=1	强制该表使用持久化统计（即使全局关闭）
STATS_AUTO_RECALC=1	数据变更超 10% 后自动重算统计信息
STATS_SAMPLE_PAGES=50	分析统计时采样多少页数据（默认 20），越大越准但越慢

提示：对于大表，适当增加 STATS_SAMPLE_PAGES（如 50~200）可提高统计准确性，减少错误执行计划。

---

查看统计信息

统计信息存储在两个系统表中：

-- 表级统计
SELECT * FROM mysql.innodb_table_stats WHERE table_name = 'your_table';
-- 索引级统计
SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'your_table';

常见字段解释：

字段	含义
last_update	上次更新统计的时间
n_rows	表的行数估计
clustered_index_size	聚簇索引大小（页数）
sum_of_other_index_sizes	其他二级索引总页数
stat_name, stat_value	统计项名称和值（如 n_diff_pfx01 表示某个前缀的不同值数量）

用途：

• 判断统计信息是否过期
• 排查执行计划异常问题
• 手动调整统计值（高级用法）

---

✍️ 高级技巧：手动修改统计信息

你可以直接更新 mysql.innodb_table_stats 或 mysql.innodb_index_stats 来“欺骗”优化器，测试不同执行计划。

⚠️ 注意：仅限测试环境！生产慎用！

示例：

UPDATE mysql.innodb_table_stats
SET stat_value = 1000000
WHERE table_name = 'orders' AND stat_name = 'n_rows';

→ 让优化器以为这张表有 100 万行，可能影响它选择索引还是全表扫描。

---

2. 非持久化统计信息（Non-Persistent Statistics）

特点：

• 不保存到磁盘。
• 每次 MySQL 重启后、或执行 TRUNCATE TABLE 等操作后，会被清除。
• 下次访问表时重新采样计算。
• 可能每次得到不同的采样结果 → 导致执行计划不稳定。

使用场景：

• 调试执行计划变化
• 特殊测试环境
• 历史遗留系统兼容性

如何启用？

-- 全局关闭持久化
SET GLOBAL innodb_stats_persistent = OFF;
-- 或建表时不指定 STATS_PERSISTENT
CREATE TABLE t (...) ENGINE=InnoDB STATS_PERSISTENT=0;

建议：生产环境不要关闭持久化统计！

---

⏱️ 3. 如何估算 ANALYZE TABLE 的复杂度？

文档提到这一节可以帮助你在“统计准确性”和“执行时间”之间做权衡。

ANALYZE TABLE 做了什么？

它会：

1. 随机读取一定数量的索引页（由 STATS_SAMPLE_PAGES 决定）
2. 分析数据分布，更新 mysql.innodb_*_stats 表
3. 影响后续查询的执行计划

执行时间影响因素：

因素	影响
表越大（数据页越多）	时间越长？❌ 不一定！
采样页数越多（STATS_SAMPLE_PAGES）	时间越长 ✅
索引越多	每个索引都要采样 → 时间增加 ✅
磁盘 I/O 性能	采样需要随机读 → IOPS 低则慢 ✅

关键点：ANALYZE TABLE 的时间 不随表总大小线性增长，而是取决于 采样页数 × 索引数量。

✅ 所以即使是一个几百 GB 的大表，只要 STATS_SAMPLE_PAGES 设置合理（比如 50），ANALYZE TABLE 也可能只需几秒。

---

✅ 实际应用建议总结

场景	推荐做法
所有生产环境	✅ 保持 innodb_stats_persistent=ON
大表频繁 DML	✅ 设置 STATS_AUTO_RECALC=ON，确保数据变化大后自动更新统计
执行计划突然变差	检查 mysql.innodb_table_stats.last_update 是否太久未更新
怀疑统计不准	️ 手动执行 ANALYZE TABLE your_table;
大表统计不准	增加 STATS_SAMPLE_PAGES（如 100~200）再分析
需要测试不同执行计划	可临时修改 innodb_index_stats 中的 n_diff_pfx01 等值进行实验
批量导入/删除后	✅ 建议手动执行 ANALYZE TABLE

---

监控命令推荐

-- 查看某张表的统计最后更新时间
SELECT
table_name,
last_update,
n_rows,
clustered_index_size
FROM mysql.innodb_table_stats
WHERE database_name = 'your_db';
-- 查看索引基数（不同值数量），影响索引选择
SELECT
table_name,
index_name,
stat_name,
stat_value
FROM mysql.innodb_index_stats
WHERE database_name = 'your_db' AND stat_name LIKE 'n_diff%';

---

总结一句话：

持久化统计信息让你的查询执行计划更稳定、更可预测；合理配置采样和自动更新策略，可以在性能与准确性之间取得最佳平衡。

如果你有具体的性能问题（比如某个 SQL 执行计划不稳定），我可以帮你结合统计信息分析原因。