【ACDU 翻译】 MySQL 15.8.10.2 配置非持久优化器统计参数

本节介绍如何配置非持久优化器统计信息。innodb_stats_persistent=OFF优化器统计信息不会在创建或更改单个表 时保存到磁盘 STATS_PERSISTENT=0。相反，统计信息存储在内存中，并在服务器关闭时丢失。统计数据也会通过某些操作和在某些条件下定期更新。

优化器统计信息默认保存到磁盘，由innodb_stats_persistent 配置选项启用。有关持久优化器统计信息的信息，请参阅第 15.8.10.1 节，“配置持久优化器统计参数”。

优化器统计更新

非持久优化器统计信息在以下情况下更新：

• 运行ANALYZE TABLE。

• 在启用该选项 的情况下 运行SHOW TABLE STATUS、 SHOW INDEX或查询 INFORMATION_SCHEMA.TABLES或 INFORMATION_SCHEMA.STATISTICS 表 。innodb_stats_on_metadata

  的默认设置 innodb_stats_on_metadata是 OFF。启用 innodb_stats_on_metadata 可能会降低具有大量表或索引的模式的访问速度，并降低涉及InnoDB 表的查询的执行计划的稳定性。 innodb_stats_on_metadata使用 SET语句全局配置。

  SET GLOBAL innodb_stats_on_metadata=ON
  复制

  笔记

  innodb_stats_on_metadata 仅当优化器 统计信息配置为非持久性（ innodb_stats_persistent 禁用时）时才适用。

• 在启用选项的情况下启动mysql客户端 --auto-rehash，这是默认设置。该 auto-rehash选项导致 InnoDB打开所有表，并且打开表操作导致重新计算统计信息。

  要提高mysql客户端 的启动时间和更新统计信息，您可以auto-rehash使用该 --disable-auto-rehash 选项 关闭 。该auto-rehash 功能可以为交互式用户自动完成数据库、表和列名的名称。

• 首先打开一个表。

• InnoDB检测到自上次更新统计信息以来已修改了 1 / 16 的表。

配置采样页数

MySQL 查询优化器使用有关键分布的估计 统计信息来根据索引的相对选择性为执行计划 选择索引。更新优化器统计信息时InnoDB，它会从表上的每个索引中随机抽取页面以估计索引的 基数。（这种技术被称为 随机潜水。）

为了让您控制统计估计的质量（从而为查询优化器提供更好的信息），您可以使用参数更改采样页面的数量 innodb_stats_transient_sample_pages。默认的采样页数为 8，这可能不足以产生准确的估计，从而导致查询优化器的索引选择不佳。这种技术对于大型表和 连接中使用的表尤其重要。对此类表进行不必要 的全表扫描可能是一个重大的性能问题。有关调整此类查询的提示，请参阅 第 8.2.1.23 节，“避免全表扫描”。 innodb_stats_transient_sample_pages 是一个可以在运行时设置的全局参数。

时的值 会影响所有 表和索引 innodb_stats_transient_sample_pages 的索引采样。当您更改索引样本大小时，请注意以下潜在的重大影响： InnoDBinnodb_stats_persistent=0

• 像 1 或 2 这样的小值可能会导致对基数的估计不准确。
• 增加该 innodb_stats_transient_sample_pages 值可能需要更多磁盘读取。远大于 8 的值（例如 100）可能会导致打开表或执行SHOW TABLE STATUS.
• 优化器可能会根据对索引选择性的不同估计选择非常不同的查询计划。

无论哪种值 innodb_stats_transient_sample_pages 最适合系统，设置选项并将其保留为该值。选择一个值，该值可以对数据库中的所有表进行合理准确的估计，而不需要过多的 I/O。由于统计信息会在执行 之外的不同时间自动重新计算 ，因此增加索引样本大小、运行、然后再次减小样本大小 ANALYZE TABLE是没有意义的 。ANALYZE TABLE

较小的表通常比较大的表需要更少的索引样本。如果您的数据库有许多大表，请考虑使用 innodb_stats_transient_sample_pages 比大多数小表更高的值。