1. 配置持久化（Persistent）统计信息参数
1.1 配置自动触发更新统计信息参数
1.2 配置每张表的统计参数
1.3 配置InnoDB优化器统计信息的采样页数参数
1.4 包含Delete-marked的记录参数
1.5 InnoDB持久化统计表
1.6 InnoDB持久化统计表示例
1.7 使用innodb_index_stats表查询索引大小
2. 配置非持久化（Non-Persistent）统计信息参数
2.1 优化器统计信息更新
2.2 配置采样页数参数
3. Analyze Table复杂性度
本文档介绍如何为InnoDB表配置持久性和非持久性统计信息。
持久化统计信息在服务器重新启动时保持不变，从而使执行计划和查询性能更加稳定。持久化统计信息还提供了控制和灵活性以及这些额外的好处：
可以使用innodb_stats_auto_recalc配置选项来控制在对表进行实质性更改后是否自动更新统计信息。
可以将STATS_PERSISTENT，STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句与CREATE TABLE和ALTER TABLE语句一起使用，以配置各个表的优化程序统计信息。
可以在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。
非持久化统计信息在每次重新启动服务器和其他一些操作后清除，并在下一个表访问时重新计算。因此，在重新计算统计数据时可能会产生不同的估计值，从而导致执行计划中的不同选择和查询性能的变化。
本文档还提供了有关估计ANALYZE TABLE复杂性的信息，这在尝试实现精确统计信息和ANALYZE TABLE执行时间之间的平衡时可能很有用。
1. 配置持久化（Persistent）统计信息参数
innodb_stats_persistent
参数含义：是否启用持久化统计信息功能
默认值：ON
持久化统计信息功能通过将统计信息存储到磁盘并使其在服务器重新启动期间保持不变来提高执行计划的稳定性，以便优化器更有可能每次为给定查询做出一致的选择。
当innodb_stats_persistent = ON或使用STATS_PERSISTENT = 1创建或更改单个表时，统计信息将持久保存到磁盘。innodb_stats_persistent默认启用。
要恢复使用非持久化统计信息，可以使用ALTER TABLE tbl_name STATS_PERSISTENT = 0语句修改表。
1.1 配置自动触发更新统计信息参数
innodb_stats_auto_recalc
参数含义：是否自动触发更新统计信息
触发阈值：
表修改时，确认变化的数据是否超过10%，超过自动收集统计信息
表，索引统计信息是持久化存储
默认值：ON
由于自动统计信息重新计算（发生在后台）是异步，在运行影响超过10％的表的DML操作时（即使innodb_stats_auto_recalc启用后），可能不会立即重新计算统计信息 。在某些情况下，统计重新计算可能会延迟几秒钟(10s)。如果在更改表的重要部分之后立即需要最新统计信息，请运行ANALYZE TABLE以启动统计信息的同步（前台）重新计算。
如果禁用了innodb_stats_auto_recalc，请在对索引列进行实质性更改后，通过为每个适用的表发出ANALYZE TABLE语句来确保统计信息的准确性。
在表上添加索引或者添加删除索引中的列时，将自动计算索引统计信息并将其添加到innodb_index_stats表，不受innodb_stats_auto_recalc的值影响。
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：关闭此功能
STATS_AUTO_RECALC
指定是否自动重新计算InnoDB表的持久统计信息。
DEFAULT：表示表的持久统计信息设置由innodb_stats_auto_recalc配置选项确定
1：表示表中10％的数据发生更改时将重新计算统计信息
0：禁用自动重新计算此表
STATS_SAMPLE_PAGES
指定在估计索引列的基数和其他统计信息时要采样的索引页数
示例
复制代码
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;
复制代码
1.3 配置InnoDB优化器统计信息的采样页数参数
innodb_stats_persistent_sample_pages
参数含义：配置持久化统计信息采样的页数
默认值：20
在什么情况下需要修改此参数呢：
统计信息不够准确，优化器选择次优计划
如果确定统计信息不够准确，则应增加innodb_stats_persistent_sample_pages的值，直到统计估计值足够准确。但是，过多地增加innodb_stats_persistent_sample_pages可能会导致ANALYZE TABLE运行缓慢。
ANALYZE TABLE太慢
在这种情况下，应减少innodb_stats_persistent_sample_pages，直到ANALYZE TABLE执行时间可以接受。但是，过多地降低该值可能会导致生成不准确的统计信息和次优查询执行计划的问题。
1.4 包含Delete-marked的记录参数
innodb_stats_include_delete_marked
参数含义：在MySQL 5.7.16中引入的此参数，默认为不启用，表示在未提交的事务有从表中删除行，则InnoDB在收集统计信息时，将会排除这些delete_marked行。这可能会导致除READ UNCOMMITTED之外的事务隔离级别的事务，运行的不是最佳的执行计划。
为了避免这种情况，可以启用innodb_stats_include_delete_marked以确保在计算持久化统计信息时InnoDB包含Delete-marked记录。
默认值：OFF
1.5 InnoDB持久化统计表
持久化统计信息功能依赖于mysql数据库中的内部表，名为innodb_table_stats和innodb_index_stats。
这些表在所有安装，升级和源代码构建过程中自动设置。
mysql.innodb_table_stats
复制代码
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+--------------------------
| Field | Type | Null | Key | Default | Extra |注释
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+--------------------------
| database_name | varchar(64) | NO | PRI | NULL | |数据库名称
| table_name | varchar(199) | NO | PRI | NULL | |表名，分区名或子分区名
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |最后一次更新统计信息时间
| n_rows | bigint(20) unsigned | NO | | NULL | |表中的行数
| clustered_index_size | bigint(20) unsigned | NO | | NULL | |主键索引大小（单位page）
| sum_of_other_index_sizes | bigint(20) unsigned | NO | | NULL | |其他索引总大小（单位page）
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+--------------------------
复制代码
mysql.innodb_index_stats
复制代码
+------------------+---------------------+------+-----+-------------------+-----------------------------+----------------------------
| Field | Type | Null | Key | Default | Extra | 注释
+------------------+---------------------+------+-----+-------------------+-----------------------------+----------------------------
| database_name | varchar(64) | NO | PRI | NULL | | 数据库名称
| table_name | varchar(199) | NO | PRI | NULL | | 表名，分区名或子分区名
| index_name | varchar(64) | NO | PRI | NULL | | 索引名称
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | 最后一次更新统计信息的时间
| stat_name | varchar(64) | NO | PRI | NULL | | 统计信息的名称，其值在stat_value列中报告
| stat_value | bigint(20) unsigned | NO | | NULL | | 在stat_name列中命名的统计信息的值
| sample_size | bigint(20) unsigned | YES | | NULL | | 在stat_value列中提供的估计值的页面采样数
| stat_description | varchar(1024) | NO | | NULL | | 在stat_name列中命名的统计信息的描述
+------------------+---------------------+------+-----+-------------------+-----------------------------+----------------------------
复制代码
start_name:
为size时 ：此时stat_value显示索引的page数量。
为n_leaf_pages时 ：此时stat_value显示叶子节点的数量。
为n_diff_pfxNN时 ：显示索引字段上唯一值的数量
【注意】：
innodb_table_stats和innodb_index_stats表是普通表，可以手动更新。
如果手动更新统计信息，请发出FLUSH TABLE tbl_name命令以使MySQL重新加载更新的统计信息。
1.6 InnoDB持久化统计表示例
创建表t1包含主索引（列a，b）二级索引（列c，d）和唯一索引（列e，f）：
复制代码
CREATE 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;
复制代码
插入5行数据后
复制代码
insert into t1 select 1,1,10,11,100,101;
insert into t1 select 1,2,10,11,200,102;
insert into t1 select 1,3,10,11,100,103;
insert into t1 select 1,4,10,12,200,104;
insert into t1 select 1,5,10,12,100,105;
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％阈值，则会在几秒钟内自动更新统计信息）
复制代码
mysql> ANALYZE TABLE t1;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0.02 sec)
复制代码
表最后一次收集统计信息时间为2018-08-29 11:10:03,表的行数为 5，主键索引大小为 1page，其他索引的总大小为 2 page。
复制代码
mysql> select * from mysql.innodb_table_stats t where t.database_name='test' and t.table_name='t1'\G
*************************** 1. row ***************************
database_name: test
table_name: t1
last_update: 2018-08-29 11:10:03
n_rows: 5
clustered_index_size: 1
sum_of_other_index_sizes: 2
1 row in set (0.00 sec)
复制代码
innodb_index_stats表包含每个索引的多行。 innodb_index_stats表中的每一行都提供与特定索引统计信息相关的数据，该统计信息在stat_name列中命名并在stat_description列中进行了描述。 例如：
复制代码
mysql> select * from mysql.innodb_index_stats t where t.database_name='test' and t.table_name='t1';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test | t1 | PRIMARY | 2018-08-29 11:10:03 | n_diff_pfx01 | 1 | 1 | a |
| test | t1 | PRIMARY | 2018-08-29 11:10:03 | n_diff_pfx02 | 5 | 1 | a,b |
| test | t1 | PRIMARY | 2018-08-29 11:10:03 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | t1 | PRIMARY | 2018-08-29 11:10:03 | size | 1 | NULL | Number of pages in the index |
| test | t1 | i1 | 2018-08-29 11:10:03 | n_diff_pfx01 | 1 | 1 | c |
| test | t1 | i1 | 2018-08-29 11:10:03 | n_diff_pfx02 | 2 | 1 | c,d |
| test | t1 | i1 | 2018-08-29 11:10:03 | n_diff_pfx03 | 2 | 1 | c,d,a |
| test | t1 | i1 | 2018-08-29 11:10:03 | n_diff_pfx04 | 5 | 1 | c,d,a,b |
| test | t1 | i1 | 2018-08-29 11:10:03 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | t1 | i1 | 2018-08-29 11:10:03 | size | 1 | NULL | Number of pages in the index |
| test | t1 | i2uniq | 2018-08-29 11:10:03 | n_diff_pfx01 | 2 | 1 | e |
| test | t1 | i2uniq | 2018-08-29 11:10:03 | n_diff_pfx02 | 5 | 1 | e,f |
| test | t1 | i2uniq | 2018-08-29 11:10:03 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
| test | t1 | i2uniq | 2018-08-29 11:10:03 | size | 1 | NULL | Number of pages in the index |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
14 rows in set (0.00 sec)
复制代码
stat_name列显示以下类型的统计信息：
如果stat_name = size，则stat_value列显示索引中的总大小（单位page）。
如果stat_name = n_leaf_pages，则stat_value列显示索引中的叶子页数。
如果stat_name = n_diff_pfx01，stat_value列显示索引第一列中的不同值的数量。 当 stat_name = n_diff_pfx02，stat_value列显示索引前两列中的不同值的数量，依此类推。 此外，在stat_name = n_diff_pfxNN的情况下，stat_description列显示了计算的索引列。
进一步说明基数数据的n_diff_pfxNN统计量：
表t1使用主索引（列a，b），辅助索引（列c，d）和唯一索引（列e，f）。
对于PRIMARY索引，有两个n_diff％行。行数等于索引中的列数。
【注意】
对于非唯一索引，索引会附加主键列。
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％行
对于二级索引（i1），有四个n_diff％行。 仅为辅助索引（c，d）定义了两列，但是辅助索引有四个n_diff％行，因为InnoDB使用主键为所有非唯一索引添加后缀。
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）。通过查看表c1中的列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）和（100105）。 计数列（e，f）显示在结果集的stat_description列中。
1.7 使用innodb_index_stats表查询索引大小
可以使用innodb_index_stats表查询表，分区或子分区的索引大小，例如：
复制代码
SELECT SUM(stat_value) pages,
index_name,
SUM(stat_value) * @@innodb_page_size size
FROM mysql.innodb_index_stats
WHERE table_name = 't1' AND database_name = 'test' AND stat_name = 'size'
GROUP BY index
