SQL MySQL 的 innodb_stats_sample_pages 与采样页数对执行计划准确性的影响

innodb_stats_sample_pages值过小会导致索引统计失真，引发执行计划错选；建议大表设为100～200，但需权衡analyze table耗时与锁影响，mysql 8.0起优先使用直方图替代盲目调大该参数。

innodb_stats_sample_pages 值太小会导致执行计划错判

MySQL 5.6 及之后版本中，innodb_stats_sample_pages 控制 InnoDB 在计算索引统计信息（比如索引基数、列值分布）时，每棵 B+ 树索引采样的页数。这个值不是“越大越好”，但过小（比如默认的 20）在大表或数据倾斜严重时，会让优化器误以为某个索引选择性很高，从而选错索引甚至走全表扫描。

常见错误现象：EXPLAIN 显示走了 idx_status，但实际慢得离谱；加了 FORCE INDEX 后快十倍；表数据量翻倍后，同一条查询突然变慢——这些往往不是 SQL 写法问题，而是统计不准引发的执行计划漂移。

• 默认值是 20，对千万级以下表勉强够用；超过 1000 万行且有高频范围查询时，建议设为 100～200
• 该参数只影响 ANALYZE TABLE 时的采样精度，不影响实时查询性能，但会影响后续所有基于该统计的执行计划
• 修改后需手动触发 ANALYZE TABLE，否则旧统计仍生效；自动更新统计（innodb_stats_auto_recalc=ON）也依赖此参数
• 注意：MySQL 8.0 引入了更稳定的持久化统计（innodb_stats_persistent=ON），此时调大 innodb_stats_sample_pages 效果更明显

为什么不能无脑调大 innodb_stats_sample_pages

采样页数越多，ANALYZE TABLE 耗时越长，尤其在 SSD 性能一般或 IOPS 受限的实例上，一次分析可能卡住几秒到几十秒。更关键的是，它会阻塞 DML（INSERT/UPDATE/delete）——因为 InnoDB 在分析期间会对索引加意向锁，高并发写入场景下容易引发锁等待堆积。

使用场景：线上 OLTP 主库通常不建议设超过 200；从库或低峰期批量维护可临时调到 400，但必须配合 ANALYZE TABLE 手动执行并观察锁表现。

• innodb_stats_sample_pages = 800 在单表 5 亿行时，ANALYZE TABLE 可能持续 20 秒以上，期间写入延迟明显升高
• 该参数对内存占用无直接影响，但采样过程会读取更多页面进 buffer pool，间接增加压力
• 若开启 innodb_stats_transient_sample_pages（MySQL 5.7+），它仅用于非持久化统计场景，和主参数互不影响，别混淆

替代方案：用直方图代替盲目调大采样页数

MySQL 8.0+ 支持列级直方图（CREATE STATISTICS），它不依赖页采样，而是对列值做等深或等宽分桶，对 WHERE status IN ('pending','processing') 这类低基数字段效果远超调大 innodb_stats_sample_pages。

性能影响小：直方图构建只扫描一次目标列，不锁表（LOCK=NONE 可选），且体积小、更新快；兼容性上，8.0.19+ 才支持 json 类型列直方图，老版本不适用。

• 建直方图示例：ANALYZE TABLE orders UPDATE HISTOGRAM ON status, created_at WITH 16 BUCKETS;
• 直方图优先级高于页采样统计，只要存在，优化器就会优先用它估算 WHERE 条件的选择性
• 注意：直方图不会自动更新，需定期重建（如每天凌晨），或监听 information_schema.COLUMN_STATISTICS 表变化
• 不要给高基数、高更新频率的列（如 user_id）建直方图——收益低，维护成本高

怎么验证当前统计是否靠谱

别只看 EXPLAIN 的 rows 估算值，要对比真实扫描行数。最直接的方法是打开 optimizer_trace，查 rows_estimation 段里各索引的 records_per_key 是否合理。

常见陷阱：用 SHOW INDEX FROM tbl 看到的 Cardinality 是粗略整数，且滞后于实际数据变更；而 INFORMATION_SCHEMA.STATISTICS 中的 INDEX_COMMENT 字段可能为空，不代表没统计——得看 SEQ_IN_INDEX 和 NULLABLE 组合是否被优化器真正用了。

• 快速检查命令：select table_name, index_name, cardinality FROM information_schema.statistics WHERE table_schema='db' AND table_name='orders' ORDER BY cardinality DESC;
• 如果某索引 cardinality 明显偏离实际唯一值数量（比如 status 列只有 4 个值，但显示 1200），基本可判定采样失效
• 执行 EXPLAIN FORMAT=JSON 后，在 query_block->table->rows 和 used_columns 下找 range_analysis，确认优化器是否参考了直方图

采样页数不是调优银弹，它和直方图、索引设计、数据分布是耦合的。最容易被忽略的是：即使把 innodb_stats_sample_pages 设成 1000，如果表里有大量 NULL 或重复前缀（比如 URL 字段前 20 字节全一样），统计依然会失真——这时候得换索引策略，而不是继续加采样页。