14.8.11.2 配置非持久性优化器统计参数

本节介绍如何配置非持久性优化器统计信息。当innodb_stats_persistent=OFF或使用STATS_PERSISTENT=0创建或更改单个 table 时，优化器统计信息不会保留在磁盘上。相反，统计信息存储在内存中，并且在服务器关闭时会丢失。统计信息还可以通过某些操作在特定条件下定期更新。

从 MySQL 5.6.6 开始，默认情况下，优化程序统计信息会保存在磁盘上，并通过innodb_stats_persistent配置选项启用。有关持久性优化器统计信息，请参阅第 14.8.11.1 节“配置持久性优化器统计参数”。

优化器统计信息更新

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

• Running ANALYZE TABLE.

• 运行显示 table 格状态，SHOW INDEX或在启用innodb_stats_on_metadata选项的情况下查询INFORMATION_SCHEMA.TABLES或INFORMATION_SCHEMA.STATISTICStable。

在 MySQL 5.6.6 中默认启用持久性优化器统计信息后，innodb_stats_on_metadata的默认设置已更改为OFF。启用innodb_stats_on_metadata可能会降低具有大量 table 或索引的模式的访问速度，并降低涉及InnoDBtable 的查询的执行计划的稳定性。 innodb_stats_on_metadata是使用SET语句全局配置的。

SET GLOBAL innodb_stats_on_metadata=ON

• 在启用了--auto-rehash选项的情况下启动mysqlClient 端，这是默认设置。 auto-rehash选项将导致所有InnoDBtable 被打开，并且打开 table 操作将导致重新计算统计信息。

为了缩短mysqlClient 端的启动时间并更新统计信息，您可以使用--disable-auto-rehash选项关闭auto-rehash。 auto-rehash功能为交互式用户启用数据库，table 和列名称的自动名称完成。

• 首先打开一个 table。

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

配置采样页数

MySQL 查询优化器基于索引的相对selectivity，使用有关键分布的估计值statistics选择执行计划的索引。 InnoDB更新优化程序统计信息时，它将对 table 中每个索引的随机页面进行采样，以估计索引的cardinality。 (此技术称为random dives。)

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

innodb_stats_transient_sample_pages的值会影响所有InnoDBtable 和innodb_stats_persistent=0时的索引的索引采样。更改索引 samples 大小时，请注意以下潜在的重大影响：

• 较小的值(例如 1 或 2)可能会导致基数估计不准确。

• 增大innodb_stats_transient_sample_pages值可能需要更多的磁盘读取。大于 8 的值(例如 100)会导致打开 table 或执行SHOW TABLE STATUS的时间明显变慢。

• 优化器可能基于对索引选择性的不同估计来选择非常不同的查询计划。

无论innodb_stats_transient_sample_pages的哪个值最适合系统，请设置该选项并将其保留为该值。选择一个可以对数据库中的所有 table 进行合理准确估计的值，而无需过多的 I/O。因为统计数据会在除执行ANALYZE TABLE以外的其他时间自动重新计算，所以增大索引 samples 大小，运行ANALYZE TABLE并再次减小 samples 大小没有意义。

较小的 table 通常比较大的 table 需要更少的索引 samples。如果您的数据库有很多大 table，请考虑为您的innodb_stats_transient_sample_pages使用较大的 table。