MySQL 8.4 发行说明
持久化优化器统计信息功能通过将统计信息存储到磁盘并使其在服务器重启后保持持久性来提高计划稳定性，以便优化器更有可能在每次针对给定查询时做出一致的选择。
当 innodb_stats_persistent=ON 或使用 STATS_PERSISTENT=1 定义单个表时，优化器统计信息将持久保存到磁盘。默认情况下启用 innodb_stats_persistent。
以前，在重启服务器和执行其他一些类型的操作后，优化器统计信息会被清除，并在下次访问表时重新计算。因此，重新计算统计信息时可能会产生不同的估计值，从而导致查询执行计划的不同选择以及查询性能的差异。
持久化统计信息存储在 mysql.innodb_table_stats 和 mysql.innodb_index_stats 表中。请参阅 第 17.8.10.1.5 节，“InnoDB 持久化统计信息表”。
如果您不想将优化器统计信息持久保存到磁盘，请参阅 第 17.8.10.2 节，“配置非持久化优化器统计信息参数”
innodb_stats_auto_recalc 变量默认启用，它控制当表发生更改超过其行数的 10% 时是否自动计算统计信息。您还可以通过在创建或更改表时指定 STATS_AUTO_RECALC 子句来为单个表配置自动统计信息重新计算。
由于自动统计信息重新计算的异步特性（在后台发生），即使启用了 innodb_stats_auto_recalc，在运行影响超过 10% 表的 DML 操作后，统计信息也可能不会立即重新计算。在某些情况下，统计信息重新计算可能会延迟几秒钟。如果立即需要最新的统计信息，请运行 ANALYZE TABLE 以启动统计信息的同步（前台）重新计算。
如果禁用了 innodb_stats_auto_recalc，则可以通过在对索引列进行大量更改后执行 ANALYZE TABLE 语句来确保优化器统计信息的准确性。您还可以考虑将 ANALYZE TABLE 添加到加载数据后运行的设置脚本中，并在活动较少的时间按计划运行 ANALYZE TABLE。
将索引添加到现有表时，或者添加或删除列时，无论 innodb_stats_auto_recalc 的值如何，都会计算索引统计信息并将其添加到 innodb_index_stats 表中。
对于启用了 AUTO UPDATE 的直方图（请参阅 直方图统计分析），自动重新计算持久化统计信息也会导致直方图更新。
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禁用该功能。 为单个表启用持久化统计信息后，请在加载表数据后使用ANALYZE TABLE计算统计信息。STATS_AUTO_RECALC指定是否自动重新计算 持久化统计信息。 值DEFAULT会导致表的持久化统计信息设置由innodb_stats_auto_recalc设置确定。 值1会导致在 10% 的表数据发生更改时重新计算统计信息。 值0会阻止对表进行自动重新计算。 使用值 0 时，请在对表进行大量更改后使用ANALYZE TABLE重新计算统计信息。STATS_SAMPLE_PAGES指定在例如通过ANALYZE TABLE操作为索引列计算基数和其他统计信息时要采样的索引页数。
以下 CREATE TABLE 示例中指定了所有三个子句
Press CTRL+C to copyCREATE 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;
优化器使用关于键分布的估计 统计信息 来根据索引的相对 选择性 来选择执行计划的索引。 ANALYZE TABLE 等操作会导致 InnoDB 从表上每个索引中随机采样页面，以估计索引的 基数。 这种采样技术被称为 随机探查。
innodb_stats_persistent_sample_pages 控制采样页数。 您可以在运行时调整该设置，以管理优化器使用的统计信息估计值的质量。 默认值为 20。 在遇到以下问题时，请考虑修改该设置
统计信息不够准确，优化器选择了次优计划，如
EXPLAIN输出中所示。 您可以通过将索引的实际基数（通过在索引列上运行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的复杂性。 表的主键中的列数也很重要，因为主键列会附加到每个非唯一索引。
默认情况下，InnoDB 在计算统计信息时会读取未提交的数据。 如果未提交的事务从表中删除了行，则在计算行估计值和索引统计信息时会排除标记为删除的记录，这可能会导致使用除 READ UNCOMMITTED 之外的隔离级别同时操作该表的其他事务的执行计划不佳。 为了避免这种情况，可以启用 innodb_stats_include_delete_marked，以确保在计算持久化优化器统计信息时包含标记为删除的记录。
启用 innodb_stats_include_delete_marked 后，ANALYZE TABLE 会在重新计算统计信息时考虑标记为删除的记录。
innodb_stats_include_delete_marked 是影响所有 InnoDB 表的全局设置，并且仅适用于持久化优化器统计信息。
持久化统计信息功能依赖于 mysql 数据库中名为 innodb_table_stats 和 innodb_index_stats 的内部管理表。 这些表在所有安装、升级和从源代码构建过程中自动设置。
表格 17.6 innodb_table_stats 的列
| 列名 | 说明 |
|---|---|
database_name |
数据库名称 |
table_name |
表名、分区名或子分区名 |
last_update |
指示 InnoDB 上次更新此行的时间戳 |
n_rows |
表中的行数 |
clustered_index_size |
主索引的大小（以页为单位） |
sum_of_other_index_sizes |
其他（非主键）索引的总大小（以页为单位） |
表格 17.7 innodb_index_stats 的列
| 列名 | 说明 |
|---|---|
database_name |
数据库名称 |
table_name |
表名、分区名或子分区名 |
index_name |
索引名称 |
last_update |
指示该行上次更新时间的时间戳 |
stat_name |
统计信息的名称，其值在 stat_value 列中报告 |
stat_value |
stat_name 列中命名的统计信息的值 |
sample_size |
为 stat_value 列中提供的估计值采样的页数 |
stat_description |
stat_name 列中命名的统计信息的说明 |
innodb_table_stats 和 innodb_index_stats 表包含一个 last_update 列，该列显示上次更新索引统计信息的时间
Press CTRL+C to copymysql> SELECT * FROM innodb_table_stats \G *************************** 1. row *************************** database_name: sakila table_name: actor last_update: 2014-05-28 16:16:44 n_rows: 200 clustered_index_size: 1 sum_of_other_index_sizes: 1 ...
Press CTRL+C to copymysql> SELECT * FROM innodb_index_stats \G *************************** 1. row *************************** database_name: sakila table_name: actor index_name: PRIMARY last_update: 2014-05-28 16:16:44 stat_name: n_diff_pfx01 stat_value: 200 sample_size: 1 ...
innodb_table_stats 和 innodb_index_stats 表可以手动更新，这使得在不修改数据库的情况下强制执行特定查询优化计划或测试备用计划成为可能。 如果手动更新统计信息，请使用 FLUSH TABLE 语句加载更新的统计信息。tbl_name
持久化统计信息被视为本地信息，因为它们与服务器实例相关。 因此，当进行自动统计信息重新计算时，innodb_table_stats 和 innodb_index_stats 表不会被复制。 如果运行 ANALYZE TABLE 以启动统计信息的同步重新计算，则该语句会被复制（除非您抑制了它的日志记录），并且会在副本上进行重新计算。
innodb_table_stats 表包含每个表的一行。 以下示例演示了收集的数据类型。
表格 t1 包含一个主键（列 a、b）、一个二级索引（列 c、d）和一个唯一索引（列 e、f）
Press CTRL+C to copyCREATE TABLE t1 ( a INT, b INT, c INT, d INT, e INT, f INT, PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f) ) ENGINE=INNODB;
插入五行示例数据后，表格 t1 如下所示
Press CTRL+C to copymysql> SELECT * FROM t1; +---+---+------+------+------+------+ | a | b | c | d | e | f | +---+---+------+------+------+------+ | 1 | 1 | 10 | 11 | 100 | 101 | | 1 | 2 | 10 | 11 | 200 | 102 | | 1 | 3 | 10 | 11 | 100 | 103 | | 1 | 4 | 10 | 12 | 200 | 104 | | 1 | 5 | 10 | 12 | 100 | 105 | +---+---+------+------+------+------+
要立即更新统计信息，请运行 ANALYZE TABLE（如果启用了 innodb_stats_auto_recalc，则假设已达到更改表行的 10% 阈值，统计信息会在几秒钟内自动更新）
Press CTRL+C to copymysql> ANALYZE TABLE t1; +---------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------+---------+----------+----------+ | test.t1 | analyze | status | OK | +---------+---------+----------+----------+
表格 t1 的表统计信息显示 InnoDB 上次更新表统计信息的时间（2014-03-14 14:36:34）、表中的行数（5）、聚簇索引大小（1 页）以及其他索引的组合大小（2 页）。
Press CTRL+C to copymysql> SELECT * FROM mysql.innodb_table_stats WHERE table_name like 't1'\G *************************** 1. row *************************** database_name: test table_name: t1 last_update: 2014-03-14 14:36:34 n_rows: 5 clustered_index_size: 1 sum_of_other_index_sizes: 2
innodb_index_stats 表包含每个索引的多行。 innodb_index_stats 表中的每一行都提供与特定索引统计信息相关的数据，该统计信息在 stat_name 列中命名，并在 stat_description 列中描述。 例如
Press CTRL+C to copymysql> SELECT index_name, stat_name, stat_value, stat_description FROM mysql.innodb_index_stats WHERE table_name like 't1'; +------------+--------------+------------+-----------------------------------+ | index_name | stat_name | stat_value | stat_description | +------------+--------------+------------+-----------------------------------+ | PRIMARY | n_diff_pfx01 | 1 | a | | PRIMARY | n_diff_pfx02 | 5 | a,b | | PRIMARY | n_leaf_pages | 1 | Number of leaf pages in the index | | PRIMARY | size | 1 | Number of pages in the index | | i1 | n_diff_pfx01 | 1 | c | | i1 | n_diff_pfx02 | 2 | c,d | | i1 | n_diff_pfx03 | 2 | c,d,a | | i1 | n_diff_pfx04 | 5 | c,d,a,b | | i1 | n_leaf_pages | 1 | Number of leaf pages in the index | | i1 | size | 1 | Number of pages in the index | | i2uniq | n_diff_pfx01 | 2 | e | | i2uniq | n_diff_pfx02 | 5 | e,f | | i2uniq | n_leaf_pages | 1 | Number of leaf pages in the index | | i2uniq | size | 1 | Number of pages in the index | +------------+--------------+------------+-----------------------------------+
stat_name 列显示以下类型的统计信息
size：其中stat_name=size，stat_value列显示索引中的总页数。n_leaf_pages：其中stat_name=n_leaf_pages，stat_value列显示索引中的叶页数。n_diff_pfx：其中NNstat_name=n_diff_pfx01，stat_value列显示索引第一列中不同值的个数。 其中stat_name=n_diff_pfx02，stat_value列显示索引前两列中不同值的个数，依此类推。 其中stat_name=n_diff_pfx，NNstat_description列显示要计数的索引列的逗号分隔列表。
为了进一步说明提供基数数据的 n_diff_pfx 统计信息，让我们再次考虑前面介绍的 NNt1 表示例。如下所示，t1 表创建时包含一个主键（列 a、b）、一个辅助索引（列 c、d）和一个唯一索引（列 e、f）。
Press CTRL+C to copyCREATE TABLE t1 ( a INT, b INT, c INT, d INT, e INT, f INT, PRIMARY KEY (a, b), KEY i1 (c, d), UNIQUE KEY i2uniq (e, f) ) ENGINE=INNODB;
插入五行示例数据后，表格 t1 如下所示
Press CTRL+C to copymysql> SELECT * FROM t1; +---+---+------+------+------+------+ | a | b | c | d | e | f | +---+---+------+------+------+------+ | 1 | 1 | 10 | 11 | 100 | 101 | | 1 | 2 | 10 | 11 | 200 | 102 | | 1 | 3 | 10 | 11 | 100 | 103 | | 1 | 4 | 10 | 12 | 200 | 104 | | 1 | 5 | 10 | 12 | 100 | 105 | +---+---+------+------+------+------+
查询 index_name、stat_name、stat_value 和 stat_description 时，其中 stat_name LIKE 'n_diff%'，将返回以下结果集：
Press CTRL+C to copymysql> SELECT index_name, stat_name, stat_value, stat_description FROM mysql.innodb_index_stats WHERE table_name like 't1' AND stat_name LIKE 'n_diff%'; +------------+--------------+------------+------------------+ | index_name | stat_name | stat_value | stat_description | +------------+--------------+------------+------------------+ | PRIMARY | n_diff_pfx01 | 1 | a | | PRIMARY | n_diff_pfx02 | 5 | a,b | | i1 | n_diff_pfx01 | 1 | c | | i1 | n_diff_pfx02 | 2 | c,d | | i1 | n_diff_pfx03 | 2 | c,d,a | | i1 | n_diff_pfx04 | 5 | c,d,a,b | | i2uniq | n_diff_pfx01 | 2 | e | | i2uniq | n_diff_pfx02 | 5 | e,f | +------------+--------------+------------+------------------+
对于 PRIMARY 索引，有两行 n_diff%。行数等于索引中的列数。
注意
对于非唯一索引，InnoDB 会追加主键的列。
在
index_name=PRIMARY且stat_name=n_diff_pfx01的情况下，stat_value为1，这表示索引的第一列（列a）中有一个不同的值。可以通过查看表t1中列a中的数据来确认列a中不同值的个数，其中只有一个不同的值（1）。统计的列（a）显示在结果集的stat_description列中。在
index_name=PRIMARY且stat_name=n_diff_pfx02的情况下，stat_value为5，这表示索引的两列（a,b）中有五个不同的值。可以通过查看表t1中列a和b中的数据来确认列a和b中不同值的个数，其中有五个不同的值：（1,1）、（1,2）、（1,3）、（1,4）和（1,5）。统计的列（a,b）显示在结果集的stat_description列中。
对于辅助索引（i1），有四行 n_diff%。虽然只为辅助索引定义了两列（c,d），但辅助索引有四行 n_diff%，因为 InnoDB 会在所有非唯一索引后面加上主键。因此，有四行 n_diff% 而不是两行，以考虑辅助索引列（c,d）和主键列（a,b）。
在
index_name=i1且stat_name=n_diff_pfx01的情况下，stat_value为1，这表示索引的第一列（列c）中有一个不同的值。可以通过查看表t1中列c中的数据来确认列c中不同值的个数，其中只有一个不同的值：（10）。统计的列（c）显示在结果集的stat_description列中。在
index_name=i1且stat_name=n_diff_pfx02的情况下，stat_value为2，这表示索引的前两列（c,d）中有两个不同的值。可以通过查看表t1中列c和d中的数据来确认列c和d中不同值的个数，其中有两个不同的值：（10,11）和（10,12）。统计的列（c,d）显示在结果集的stat_description列中。在
index_name=i1且stat_name=n_diff_pfx03的情况下，stat_value为2，这表示索引的前三列（c,d,a）中有两个不同的值。可以通过查看表t1中列c、d和a中的数据来确认列c、d和a中不同值的个数，其中有两个不同的值：（10,11,1）和（10,12,1）。统计的列（c,d,a）显示在结果集的stat_description列中。在
index_name=i1且stat_name=n_diff_pfx04的情况下，stat_value为5，这表示索引的四列（c,d,a,b）中有五个不同的值。可以通过查看表t1中列c、d、a和b中的数据来确认列c、d、a和b中不同值的个数，其中有五个不同的值：（10,11,1,1）、（10,11,1,2）、（10,11,1,3）、（10,12,1,4）和（10,12,1,5）。统计的列（c,d,a,b）显示在结果集的stat_description列中。
对于唯一索引（i2uniq），有两行 n_diff%。
在
index_name=i2uniq且stat_name=n_diff_pfx01的情况下，stat_value为2，这表示索引的第一列（列e）中有两个不同的值。可以通过查看表t1中列e中的数据来确认列e中不同值的个数，其中有两个不同的值：（100）和（200）。统计的列（e）显示在结果集的stat_description列中。在
index_name=i2uniq且stat_name=n_diff_pfx02的情况下，stat_value为5，这表示索引的两列（e,f）中有五个不同的值。可以通过查看表t1中列e和f中的数据来确认列e和f中不同值的个数，其中有五个不同的值：（100,101）、（200,102）、（100,103）、（200,104）和（100,105）。统计的列（e,f）显示在结果集的stat_description列中。
您可以使用 innodb_index_stats 表检索表、分区或子分区的索引大小。在以下示例中，检索了表 t1 的索引大小。有关表 t1 的定义和相应的索引统计信息，请参见第 17.8.10.1.6 节“InnoDB 持久统计表示例”。
Press CTRL+C to copymysql> 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 的分区的索引大小：
Press CTRL+C to copymysql> 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;