一、前言
优化器基于数据库统计信息为单个查询计算多个执行计划的成本，选择成本最低的执行计划作为SQL最终实际执行的执行路径，统计信息是十分重要，不准确的统计信息可能会误导优化器未选择最优的执行计划，进而影响SQL的执行性能。本文将简单介绍在MySQL中与统计信息收集相关的参数。
二、统计信息相关参数
1.information_schema_stats_expiry:
此为整数类型的会话级变量，该变量定义缓存的统计信息过期之前的时间。默认值为86400，单位秒，最大值365天，即31536000秒。设置方法：
# 1.会话级别修改，只对当前会话生效，会话退出之后参数设置失效 set session information_schema_stats_expiry = 0; ## 或者 set @@information_schema_stats_expiry = 0; ## 或者 set @@session.information_schema_stats_expiry = 0; # 2.全局修改，参数设置对新建立的会话生效，当前会话无效；数据库重启之后，参数设置失效 set global information_schema_stats_expiry = 0; ## 或者 set @@global.information_schema_stats_expiry = 0; # 3.persist，全局修改变量，并将参数配置持久化到数据目录下的mysqld-auto.cnf文件中，数据库下一次重启依旧生效 set persist information_schema_stats_expiry = 0; ## 或者 set @@persist.information_schema_stats_expiry = 0; # 4.persist_only,仅将参数配置持久化到mysqld-auto.cnf文件中，数据库重启之后，参数配置才生效 set persist_only information_schema_stats_expiry = 0; ## 或者 set @@persist_only.information_schema_stats_expiry = 0; # 5.参数查看 ## 查看当前会话的系统变量配置 show variables like '%expiry%'; ## 查看全局的系统变量配置 show global variables like '%expiry%';复制
表的统计信息存储在某些information_schema表列中，例如：
STATISTICS.CARDINALITY TABLES.AUTO_INCREMENT TABLES.AVG_ROW_LENGTH TABLES.CHECKSUM TABLES.CHECK_TIME TABLES.CREATE_TIME TABLES.DATA_FREE TABLES.DATA_LENGTH TABLES.INDEX_LENGTH TABLES.MAX_DATA_LENGTH TABLES.TABLE_ROWS TABLES.UPDATE_TIME复制
这些列表示表的动态元数据，即随着表内容的变化而变化的信息。
当这些列被查询时，MySQL默认会从mysql.index_stats和mysql.table_stats 字典表中检索缓存值，这比直接从存储引擎层中检索最新的统计信息更高效。若缓存的统计信息不可用或已过期，则MySQL将从存储引擎层中检索最新的统计信息并缓存至mysql.index_stats和mysql.table_stats 字典表中，随后的查询将检索缓存统计信息直到其过期，服务器第一次重启或第一次打开mysql.index_stats 和mysql.table_stats表不会自动更新缓存统计信息。
可以使用analyze table命令来更新指定表的缓存统计信息。
将information_schema_stats_expiry设置为0，将绕过缓存统计信息从存储引擎层获取最新的统计信息。
在下述情况中，查询统计信息列时不会存储或更新mysql.index_stats 和 mysql.table_stats字典表中的统计信息：
- 缓存统计信息未过期
- information_schema_stats_expiry被设置为0
- 服务器处理read_only, super_read_only, transaction_read_only, 或innodb_read_only模式
- 查询需要获取Performance Schema数据
在知道事务是否提交之前，可以在多语句事务执行期间更新统计数据缓存。因此，缓存可能包含与已知提交状态不对应的信息。这可以在autocommit=0或START TRANSACTION之后发生。
information_schema_stats_expiry是会话级别的变量，每个客户端会话都可以定义自己的过期值，每个会话共享从存储引擎层获取并缓存的统计信息。
2.innodb_stats_auto_recalc
该参数用于设置当表中有大量数据发生变更（阈值默认为表总行数的10%）后，InnoDB是否自动重新计算持久化统计信息，默认值为on，是一个全局变量(需要使用set global来修改)。此设置适用于innodb_stats_persistent启用时创建的表。可以通过在create table 或alter table语句中指定STATS_AUTO_RECALC=1来配置自动重算统计信息，例如：
create table test4(id int,name varchar(10)) stats_auto_recalc = 1; alter table test4 stats_auto_recalc=0;复制
通过变量innodb_stats_persistent_smaple_pages来控制用于产生统计信息的样本数据量。
说明：
boolean类型的变量可以被设置为on,true,1,off,false,0。
3.innodb_stats_include_delete_marked
此为boolean类型的全局变量，默认值为off。在计算统计信息时InnoDB默认会读取未提交的数据。就一个删除表记录的未提交事务而言，InnoDB在计算统计信息时会排除被标记为删除的记录，这会导致隔离级别为非read uncommitted且并发操作同一张表的其他事务不选择最优的执行计划。为了避免这种情况的发生，可以配置 innodb_stats_include_delete_marked为on以确保InnoDB在计算统计信息时包含被标记为删除的记录。我们通过一个实践来验证以上表述，我这里存在一张test3表，共有9行记录，操作之前已经执行了analyze table test3语句更新缓存统计信息，操作如下：
# 1.先查看变量innodb_stats_include_delete_marked设置 show variables like '%delete_marked%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | innodb_stats_include_delete_marked | OFF | +------------------------------------+-------+ # 2.开启一个事务，并删除test3表中的部分记录 select table_rows from information_schema.tables where table_name = 'test3'; +------------+ | TABLE_ROWS | +------------+ | 9 | +------------+ begin; delete from test3 where id < 5; Query OK, 4 rows affected (0.00 sec) # 3.另外启动一个会话，收集表的统计信息,并查看表的行数统计信息 analyze table test3; select table_rows from information_schema.tables where table_name = 'test3'; +------------+ | TABLE_ROWS | +------------+ | 5 | +------------+复制
通过上述的操作，我们发现在计算统计信息时，未提交事务所删除的记录被排除了，同时也印证了上面的陈述，是不是更加容易理解了。
当innodb_stats_include_delete_marked设置为on，重算统计信息时analyze table 会考虑被标记为删除的记录。
4.innodb_stats_method
此为枚举类型的全局变量，默认值为nulls_equal,有效值为：nulls_equal，nulls_unequal，nulls_ignored。此参数用于配置当服务器收集InnoDB表的索引值分布统计信息时如何对待null值。对于nulls_equal,所有null索引值被认为是相等的并且都组成单个组。对于nulls_unequal,NULL值被认为不相等。对于nulls_ignored,NULL值将被忽略。
5.innodb_stats_on_metadata
此为boolean类型的全局变量，默认值为off。此参数仅适用于优化器统计信息被设置为非持久化，当禁用innodb_stats_persistent、创建或修改单个表的STATS_PERSISTENT为0时，优化器统计信息不会持久化到磁盘上。
当启用innodb_stats_on_metadata后，执行像show table status的元数据语句或访问Information Schema 下的tables、statistics表时，InnoDB会更新非持久化的统计信息（这种更新与analyze table类似）。当禁用之后，在这些操作期间则不会更新统计信息。如何理解这段陈述？我仍旧用test3表作为例子，先前删除记录的未提交事务已提交，test3表有5条记录，操作如下：
# 1.启用参数innodb_stats_on_metadata，禁用参数innodb_stats_persistent set global innodb_stats_on_metadata = on; set global innodb_stats_persistent = off; # 2.查询information_schema.tables表 select table_rows from information_schema.tables where table_name = 'test3'; +------------+ | TABLE_ROWS | +------------+ | 5 | +------------+ # 3.删除test3表id=5的记录，并提交 show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ delete from test3 where id = 5; Query OK, 1 rows affected (0.00 sec) # 4.再次查看information_schema.tables表 select table_rows from information_schema.tables where table_name = 'test3'; +------------+ | TABLE_ROWS | +------------+ | 4 | +------------+ ## 可以发现统计信息更新了复制
对于拥有大量表或索引的模式，保持禁用该参数，可以提速访问速度，同时对于涉及InnoDB表的查询也能提高其执行计划的稳定性。
6.innodb_stats_persistent
此为boolean类型的全局变量，默认值为on。用于指定InnoDB索引统计信息是否持久化到磁盘上。如果禁用该参数，统计信息将被频繁的更新，这会引起查询执行计划的变动。也可以在创建表之前在全局级别设置innodb_stats_persistent,或者通过create table和alter table的STATS_PERSISTENT子句覆盖系统范围的设置为单个表配置持久化统计信息。
7.innodb_stats_persistent_sample_pages
此为整数类型的全局变量，默认值为20，最小值为1，最大值为18446744073709551615。
当评估索引列的基数和其它统计信息时，作为样本的索引页数量。增加该参数值可以使索引统计信息更精确，这会改善查询执行计划，同时在对InnoDB表执行analyze table操作会增加IO成本和执行时间。
innodb_stats_persistent_sample_pages仅对启用innodb_stats_persistent的表适用，若禁用innodb_stats_persistent，将用innodb_stats_transient_sample_pages代替。
8.innodb_stats_transient_sample_pages
此为整数类型的全局变量，默认值为8，最小值为，最大值为18446744073709551615。功能与innodb_stats_persistent_sample_pages一致，这里就不再赘述。
更多详细信息请参考：https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_stats_persistent
