MySQL 8.4直方图自动更新实战：优化器统计精准配置

本文是“MySQL 8.4 LTS”系列中的一篇，将详细介绍如何开启直方图的自动更新功能，并通过实例验证其效果。建议在实际操作中，选择业务低峰期进行ANALYZE及统计信息刷新测试，以便清晰观察执行计划的变化和评估潜在的资源消耗。

概述

MySQL 8.4版本为优化器的直方图统计功能引入了重要的自动化能力。通过执行ANALYZE TABLE ... UPDATE HISTOGRAM ... AUTO UPDATE命令，可以为指定列开启直方图的自动更新。开启后，在后续执行表分析或由InnoDB持久化统计信息机制触发自动重新计算时，相关联的直方图将被同步刷新。这一特性显著提升了统计信息的时效性与准确性，进而帮助优化器做出更优的选择性估计。

核心价值：

• 稳定查询性能：确保统计信息及时反映真实的数据分布，减少因统计信息过期导致的执行计划误判，从而避免性能劣化。
• 降低运维成本：将统计信息维护从频繁的手动操作，转变为自动化或策略驱动的模式，减轻了数据库管理员的负担。

适用场景：

• 数据分布随时间频繁发生变化，且业务查询的执行计划对优化器统计信息高度敏感的核心业务表。

快速验证指南

下面通过一个简单的例子来演示如何配置和验证直方图的自动更新。

1. 准备测试环境并启用自动更新 首先，我们创建一张测试表并插入一些示例数据，然后为其product_id列创建直方图并启用自动更新功能。

   CREATE TABLE sales (
      id INT PRIMARY KEY AUTO_INCREMENT,
      product_id INT,
      quantity INT
   );
   INSERT INTO sales(product_id, quantity)
      VALUES (101,20),(101,15),(5000,5),(5000,7);
   
   -- 为product_id列创建直方图，并启用自动更新
   ANALYZE TABLE sales
      UPDATE HISTOGRAM ON product_id WITH 64 BUCKETS AUTO UPDATE;

2. 触发统计信息刷新 当表数据发生较大变化后，可以通过执行标准的ANALYZE TABLE语句来刷新统计信息。此时，已启用AUTO UPDATE的直方图会被自动重新计算。

   -- 此操作将触发自动更新启用的直方图被刷新
   ANALYZE TABLE sales;

3. 观察效果

   • 您可以通过查询information_schema.COLUMN_STATISTICS表来查看直方图的具体信息及其最后更新时间。
   • 对比启用自动更新前后，针对该表的复杂查询的执行计划（使用EXPLAIN查看），观察优化器是否因获得了更新的统计信息而选择了更优的访问路径。

注意事项与最佳实践

• 默认模式：直方图创建的默认更新模式为MANUAL UPDATE（手动更新）。只有在创建或更新时显式指定AUTO UPDATE，才会启用自动更新。
• 资源评估：对于数据量巨大或写入非常频繁的高负载表，开启自动更新可能会增加ANALYZE操作时的系统资源消耗（CPU、I/O）。建议在业务低峰期进行评估测试。
• Bucket数量：直方图的精确度与Bucket（桶）的数量相关。需要根据数据列的基数（Cardinality）、数据分布特征以及具体查询模式来合理选择桶的数量。桶数过多可能会增加统计计算和维护的开销，而过少则可能影响统计信息的准确性。

参考资料

• MySQL 8.4官方文档 - ANALYZE TABLE语法：https://dev.mysql.com/doc/refman/8.4/en/analyze-table.html
• MySQL 8.4官方文档 - InnoDB持久化统计信息：https://dev.mysql.com/doc/refman/8.4/en/innodb-persistent-stats.html