参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-optimizer-statistics.html

本节介绍如何为InnoDB表配置持久性和非持久性优化器统计信息。

持久性优化器统计信息在服务器重新启动时保持不变，从而实现更高的计划稳定性和更一致的查询性能。 持久优化器统计数据还提供了控制和灵活性，并具有以下额外优势：

• 您可以使用innodb_stats_auto_recalc配置选项来控制是否在对表进行实质性更改后自动更新统计信息。（默认是ON）
• 您可以使用CREATE TABLE和ALTER TABLE语句的STATS_PERSISTENT，STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句来配置各个表的优化程序统计信息。
• 你可以在 mysql.innodb_table_stats 和mysql.innodb_index_stats 表中查询优化器统计信息数据。
• 您可以查看mysql.innodb_table_stats和mysql.innodb_index_stats表的last_update列，以查看统计信息上次更新的时间。
• 您可以手动修改mysql.innodb_table_stats和mysql.innodb_index_stats表来强制执行特定的查询优化计划，或者在不修改数据库的情况下测试替代计划。

持久优化程序统计信息功能默认启用（innodb_stats_persistent = ON）。

在每次服务器重启时以及在其他操作之后清除非永久性优化器统计信息，并在下一次表访问时重新计算。 因此，重新计算统计信息时可能会产生不同的估计值，从而导致选择不同的执行计划和查询性能的变化。

14.6.12.1 配置持久化优化器统计信息参数

持久化优化器统计功能通过将统计数据存储到磁盘并使其在服务器重新启动时保持不变，从而优化器每次对给定查询更可能做出一致的选择，来提高计划稳定性。

当innodb_stats_persistent = ON时或者在使用STATS_PERSISTENT = 1创建或更改单个表时，优化器统计信息会持久保存到磁盘。 innodb_stats_persistent默认启用。

以前，优化器统计信息在每次服务器重启时以及其他一些操作之后都被清除，并在下一次表访问时重新计算。 因此，重新计算统计信息时可能会产生不同的估计值，导致查询执行计划中的选择不同，从而导致查询性能发生变化。

持久统计信息存储在mysql.innodb_table_stats和mysql.innodb_index_stats表中

要恢复使用非持久性优化器统计信息，可以使用ALTER TABLE tbl_name STATS_PERSISTENT = 0语句修改表。

14.6.12.1.1 配置持久性优化器统计信息的自动统计计算

缺省情况下启用的innodb_stats_auto_recalc配置选项确定当表经历实质性更改（超过10％的行）时是否自动计算统计信息。 您还可以在CREATE TABLE或ALTER TABLE语句中使用STATS_AUTO_RECALC子句为单个表配置自动统计信息重新计算。 innodb_stats_auto_recalc默认启用。

由于自动统计重新计算（发生在后台）的异步性质，即使启用了innodb_stats_auto_recalc，运行影响超过10％表的DML操作后，统计数据也不会立即重新计算。 在某些情况下，统计数据重新计算可能会延迟几秒钟。 如果在更改表的重要部分后立即需要最新的统计信息，请运行ANALYZE TABLE以启动统计信息的同步（前台）重新计算。

如果innodb_stats_auto_recalc被禁用，请在对索引列进行重大更改后，通过为每个适用的表发出ANALYZE TABLE语句来确保优化程序统计信息的准确性。 代表性数据加载到表中后，您可以在设置脚本中运行此语句，并在DML操作显着更改索引列的内容之后运行该语句，或在活动较少时按计划定期运行。 将新索引添加到现有表时，无论innodb_stats_auto_recalc的值如何，都会计算索引统计信息并将其添加到innodb_index_stats表中。

警告

为确保在创建新索引时收集统计信息，请启用innodb_stats_auto_recalc选项，或在启用持久统计信息模式后创建每个新索引后运行ANALYZE TABLE

14.6.12.1.2 为单个表配置优化器统计参数

innodb_stats_persistent，innodb_stats_auto_recalc和innodb_stats_persistent_sample_pages是全局配置选项。 要覆盖这些系统范围的设置并为各个表配置优化程序统计信息参数，可以在CREATE TABLE或ALTER TABLE语句中定义STATS_PERSISTENT，STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句。

• STATS_PERSISTENT指定是否启用InnoDB表的持久统计信息。 值DEFAULT导致表的持久统计信息设置由innodb_stats_persistent配置选项确定。 值1启用该表的持续统计信息，而值0将关闭此功能。 通过CREATE TABLE或ALTER TABLE语句启用持久性统计信息后，在将代表性数据加载到表中后，发出ANALYZE TABLE语句来计算统计信息。
• STATS_AUTO_RECALC指定是否自动重新计算InnoDB表的持久统计信息。 值DEFAULT导致表的持久统计信息设置由innodb_stats_auto_recalc配置选项确定。 当表中数据的10％已更改时，值1会导致重新计算统计信息。 值0会阻止该表的自动重新计算，在此设置下，请在对表格进行重大更改之后发出ANALYZE TABLE语句来重新计算统计信息。
• STATS_SAMPLE_PAGES指定估算索引列的基数和其他统计数据（例如由ANALYZE TABLE计算的数据）时要抽样的索引页数。

所有三个子句在以下CREATE TABLE示例中指定：

CREATE TABLE `t1` ( `id` int(8) NOT NULL auto_increment, `data` varchar(255), `date` datetime, PRIMARY KEY (`id`), INDEX `DATE_IX` (`date`) ) ENGINE=InnoDB, STATS_PERSISTENT=1, STATS_AUTO_RECALC=1, STATS_SAMPLE_PAGES=25;

14.6.12.1.3配置InnoDB优化器统计的采样页数

MySQL查询优化器使用关于密钥分布的统计信息来评估执行计划的索引相对选择性。 诸如ANALYZE TABLE之类的操作会使InnoDB从表中的每个索引中抽取随机页面来估计索引的基数。 （这种技术被称为 random dives。）

为了让您能够控制统计估计的质量（从而为查询优化器提供更好的信息），可以使用可在运行时设置的参数innodb_stats_persistent_sample_pages来更改采样页面的数量。

innodb_stats_persistent_sample_pages的默认值为20.作为一般表中，请在遇到以下问题时考虑修改此参数：

• 在EXPLAIN 输出中出现 Statistics are not accurate enough and the optimizer chooses suboptimal plans

统计的准确性可以通过比较索引的实际基数（通过在索引列上运行SELECT DISTINCT返回）与在mysql.innodb_index_stats持久统计信息表中提供的估计值来检查。

如果确定统计数据不够准确，则应增加innodb_stats_persistent_sample_pages的值，直到统计数据估计足够精确。 但是，增加innodb_stats_persistent_sample_pages太多会导致ANALYZE TABLE运行缓慢。

• ANALYZE TABLE 太慢。在这种情况下 应该减少innodb_stats_persistent_sample_pages，直到ANALYZE TABLE执行时间可以接受为止。但是，如果将价值降低太多，可能会导致统计数据不准确和较差优查询执行计划的问题。

如果在准确的统计信息和ANALYZE TABLE执行时间之间无法实现平衡，请考虑减少表中索引列的数量或限制分区数以减少ANALYZE TABLE复杂性。 因为主键列被附加到每个非唯一索引，所以表的主键列数也很重要。

14.6.12.14 在持续统计计算中包括删除标记的记录

默认情况下，InnoDB在计算统计信息时读取未提交的数据。 对于从表中删除行的未提交事务，InnoDB在计算行估计和索引统计信息时，排除被删除标记的记录，除READ UNCOMMITTED以外的事务隔离级别之外，这可能会导致对在表上并行使用的其他事务执行非最佳执行计划。 为了避免这种情况，可以启用innodb_stats_include_delete_marked（默认是OFF）来确保InnoDB在计算持久化优化器统计信息时包含删除标记的记录。

当启用innodb_stats_include_delete_marked时，ANALYZE TABLE会在重新计算统计信息时考虑删除标记的记录。

innodb_stats_include_delete_marked是一个影响所有InnoDB表的全局设置，它仅适用于持久化优化器统计信息。

innodb_stats_include_delete_marked是在MySQL 5.7.16中引入的。

14.6.12.1.5 InnoDB 持久化统计信息表

持久统计信息功能依赖于mysql数据库中的内部管理表，名为innodb_table_stats和innodb_index_stats。 这些表在所有安装，升级和源代码构建过程中自动设置。

innodb_table_stats和innodb_index_stats表都包含一个last_update列，显示InnoDB上次更新索引统计信息的时间

innodb_table_stats和innodb_index_stats表是普通表，可以手动更新。 通过手动更新统计信息的功能，可以强制执行特定的查询优化计划或测试备选计划，而无需修改数据库。 如果手动更新统计信息，请发出FLUSH TABLE tbl_name命令以使MySQL重新加载更新的统计信息。

持久性统计信息被视为本地信息，因为它们与服务器实例相关。 因此innodb_table_stats和innodb_index_stats表在重新计算自动统计信息时不会被复制。 如果运行ANALYZE TABLE以启动统计信息的同步重新计算，则会复制此语句（除非您禁止对其进行日志记录），并且重新计算发生在复制从服务器上。

14.6.12.1.7 使用innodb_index_stats表检索索引大小

表，分区或子分区的索引大小可以使用innodb_index_stats表来检索。 在以下示例中，为表t1检索索引大小。

mysql> SELECT SUM(stat_value) pages, index_name, SUM(stat_value)*@@innodb_page_size size FROM mysql.innodb_index_stats WHERE table_name='t1' AND stat_name = 'size' GROUP BY index_name;

+-------+------------+-------+

| pages | index_name | size |

+-------+------------+-------+

| 1 | PRIMARY | 16384 |

| 1 | i1 | 16384 |

| 1 | i2uniq | 16384 |

+-------+------------+-------+

对于分区或子分区，可以使用具有修改的WHERE子句的相同查询来检索索引大小。 例如，以下查询将检索表t1分区的索引大小：

mysql> SELECT SUM(stat_value) pages, index_name, SUM(stat_value)*@@innodb_page_size size FROM mysql.innodb_index_stats WHERE table_name like 't1#P%' AND stat_name = 'size' GROUP BY index_name;

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

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

从MySQL 5.6.6开始，默认情况下优化器统计信息会保留在磁盘上，并且innodb_stats_persistent选项配置启用。

优化器统计信息 更新

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

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

当MySQL 5.6.6中默认启用持久优化器统计信息时，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时）。

• 使用--auto-rehash选项启用mysql客户端，这是默认选项。 自动重新刷新选项会导致所有InnoDB表打开，并且打开的表操作会导致重新计算统计信息。

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

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

配置采样页面的数量

MySQL查询优化器使用关于密钥分发的估计统计信息来根据索引的相对选择性来选择执行计划的索引。 当InnoDB更新优化器统计信息时，它会从表中的每个索引中抽取随机页面来估计索引的基数。 （这种技术被称为 random dives.）

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

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

• 像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使用较高的值。

14.6.12.3 估算InnoDB表的ANALYZE TABLE复杂度

InnoDB表的ANALYZE TABLE复杂度取决于：

• 采样数量，由innodb_stats_persistent_sample_pages定义。
• 表中索引列的数量
• 分区的数量。 如果一个表没有分区，则分区的数量被认为是1。

使用这些参数，估计ANALYZE TABLE复杂性的近似公式将为：

innodb_stats_persistent_sample_pages的值*表中索引列的数量*分区的数量

通常，结果值越大，ANALYZE TABLE的执行时间就越长。

注意：

innodb_stats_persistent_sample_pages定义在全局级别采样的页数。 要设置单个表的采样页数，请在CREATE TABLE或ALTER TABLE中使用STATS_SAMPLE_PAGES选项。

如果innodb_stats_persistent = OFF，则采样的页数由innodb_stats_transient_sample_pages定义

有关估计ANALYZE TABLE复杂性的更深入方法，请考虑以下示例。

在Big O表示法中，ANALYZE TABLE复杂度描述如下：

O(n_sample * (n_cols_in_uniq_i + n_cols_in_non_uniq_i + n_cols_in_pk * (1 + n_non_uniq_i)) * n_part)

这里：

• n_sample是采样的页数（由innodb_stats_persistent_sample_pages定义）
• n_cols_in_uniq_i是所有唯一索引中所有列的总数（不包括主键列）
• n_cols_in_non_uniq_i是所有非唯一索引中所有列的总数
• n_cols_in_pk是主键中的列数（如果没有定义主键，InnoDB会在内部创建一个列主键）
• n_non_uniq_i是表中非唯一索引的数量
• n_part是分区的数量。 如果没有定义分区，则该表被视为单个分区。