MySQL 中 ANALYZE TABLE 更新统计信息

FreeGuideOnline 最新 2026-07-07

MySQL 中 ANALYZE TABLE 详解：更新统计信息以优化查询性能

在 MySQL 数据库中，查询优化器依赖统计信息来选择最优的执行计划。过时的统计信息会导致查询变慢，而 ANALYZE TABLE 正是手动刷新这些信息的关键命令。本教程将带你从零掌握该命令的核心用法。

1. 什么是统计信息？为什么需要更新？

MySQL 优化器在生成查询计划时，需要回答诸如“这个表有多少行？”、“索引中不同值的数量是多少？”等问题。这些答案存储在表统计信息中，主要包括：

  • 表的行数 (Cardinality)
  • 索引的基数 (Index Cardinality)：索引列中不同值的估算数量。
  • 键值分布直方图 (Histogram)（MySQL 8.0+）：更精确的列数据分布。

为什么需要手动更新？

  • 大量数据变更后：批量 INSERT、DELETE、UPDATE 后，表的实际行数与统计信息严重不符。
  • 自动更新不足：InnoDB 只在表 10% 行发生变化时自动触发后台统计更新，频繁小规模写入可能无法满足阈值。
  • 优化器选错索引：使用 EXPLAIN 发现优化器选择了不该用的全表扫描，而非预期的索引，通常是统计信息过时导致的。

2. 如何使用 ANALYZE TABLE 命令

执行该命令非常简单，但需要注意权限和锁。

基本语法

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE table_name [, table_name ...];
  • table_name：要分析的单个表或多个表，多个表用逗号分隔。
  • NO_WRITE_TO_BINLOG 或 LOCAL：可选，指定后操作不会被记录到二进制日志，仅在当前会话生效。

实际操作示例

1. 更新单个表的统计信息

ANALYZE TABLE employees;

执行后，MySQL 会返回操作结果表，其中 Msg_text 列显示 OK 即表示成功。

2. 同时更新多张表

ANALYZE TABLE orders, order_items, products;

3. 不写入二进制日志（在复制环境中常用于主库）

ANALYZE NO_WRITE_TO_BINLOG TABLE sales_data;

执行过程中发生了什么？

对于 InnoDB 表，ANALYZE TABLE 会执行以下操作：

  1. 随机读取表的部分页面（innodb_stats_sample_pages 控制采样页数，默认 20）。
  2. 根据采样数据重新计算表的行数、索引基数等统计值。
  3. 将新的统计信息持久化到 mysql.innodb_table_stats 和 mysql.innodb_index_stats 系统表中。
  4. 如果表存在直方图，ANALYZE TABLE 也会更新直方图统计（需使用 ANALYZE TABLE ... UPDATE HISTOGRAM 子句另行操作）。

3. 确认统计信息是否更新成功

方法一：查看 MySQL 系统表

-- 查看表的行数估算和索引大小
SELECT * FROM mysql.innodb_table_stats WHERE table_name = 'employees';

-- 查看索引统计信息
SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'employees';

重点观察 n_rows（表行数）和 stat_value（索引基数）是否接近真实值。

方法二：使用 SHOW INDEX 命令

SHOW INDEX FROM employees;

结果中的 Cardinality 字段即为索引的基数估算。理想情况下，Cardinality 越接近表的实际行数，说明该索引区分度越高。

方法三：对比 EXPLAIN 前后变化

在对表执行 ANALYZE TABLE 前后分别运行同一个较复杂的 SELECT 查询并用 EXPLAIN 查看，观察 rows 列、key 列和 Extra 列的变化。

4. 常见使用场景

  • 数据库迁移或备份恢复后：导入大量数据后，所有表的统计信息几乎是空的，务必在所有表上执行一次 ANALYZE TABLE。
  • 定期维护：对于频繁进行大批量数据写入的日志表，可配合事件调度器每周执行一次。
  • 解决特定慢查询：当某条 SQL 突然变慢，且 EXPLAIN 显示扫描行数（rows）远小于或远大于预期时，立即分析相关表。
  • 配合直方图使用：对于某个列数据分布不均匀（如状态码、地区），可先执行 ANALYZE TABLE t UPDATE HISTOGRAM ON col, col2; 创建直方图，后续 ANALYZE TABLE t 会自动更新这些直方图。

5. 重要注意事项与限制

  1. 锁与性能影响

    • ANALYZE TABLE 默认执行读锁（READ LOCK），此时其他会话仍可读取表数据，但无法写入。对于大表，执行时间可能长达数秒到数分钟，可能短暂阻塞写操作。
    • 在 MySQL 5.6.17+ 及 8.0 中，InnoDB 使用更轻量的“字典锁”，影响有所减轻，但仍应避免在极端高峰运行。

  2. 采样精度的权衡 innodb_stats_persistent_sample_pages（持久化统计）和 innodb_stats_transient_sample_pages（临时统计）控制采样页数量。增大该值可提高统计信息精度，但会延长执行时间。默认为 20 页，对于百 GB 级别的大表，建议逐步调整至 100 以上并观察效果。

    -- 仅对当前会话有效，可临时调高采样率进行精确分析
SET SESSION innodb_stats_persistent_sample_pages = 200;
ANALYZE TABLE huge_table;

  3. 自动统计更新仍是主力 不要过度依赖手动分析。InnoDB 的自动统计更新机制能够应对大多数场景。只有出现明显性能衰退且确认是统计信息问题时，才应手工介入。

  4. 与 OPTIMIZE TABLE 的区别 OPTIMIZE TABLE 也会重建表并更新统计信息，但它涉及更重的操作（例如碎片整理、空间回收），会复制整表数据，代价极高。仅在明确需要回收空间或重组表时使用，日常更新统计信息请只使用 ANALYZE TABLE。

  5. 关于 MyISAM 与 MEMORY 表 该命令同样适用于 MyISAM 表，但原理不同：它会刷新键缓存并修复索引文件。在生产环境中，建议统一使用 InnoDB 并掌握本教程中的 InnoDB 行为。

6. 总结

ANALYZE TABLE 是 MySQL 调优工具箱中一款轻量、直接的工具，专门用于同步表统计信息与真实数据状态。合理使用它可以显著改善执行计划的准确性，从而提升查询性能。

最佳实践：

  • 在批量数据导入后，立即分析相关表。
  • 当 (EXPLAIN) 显示优化器做出了不合逻辑的选择时，分析怀疑的表。
  • 避免在业务高峰期执行大表的分析，除非你已测试过其影响并规划好窗口。
  • 始终用 NO_WRITE_TO_BINLOG 避免在复制拓扑中造成不必要的 binlog 膨胀。

通过主动维护统计信息的健康，你可以让 MySQL 优化器始终做出最优决策，确保应用稳定高效运行。