MySQL中的统计信息相关参数介绍

统计信息的作用

上周同事在客户现场遇到了由于统计信息的原因，导致应用数据迁移时间过慢，整个迁移差点失败。关键时刻同事发现测试环境与生产环境SQL语句执行计划不一致，立刻收集统计信息才保证迁移得以正常完成。 
统计信息对于SQL的执行时间有重要的影响，统计信息的不准确会导致SQL的执行计划不准确，从而致使SQL执行时间变慢，Oracle DBA非常了解统计信息的收集规则，同样在MySQL中也有相关的参数去控制统计信息。

相关参数

innodb_stats_auto_recalc

控制innodb是否自动收集统计信息，默认是打开的。当表中数据变化超过%10时候，就会重新计算统计信息。参数的生效依赖于建表时指定innodb_stats_persistent是打开的或CREATE TABLE , ALTER TABLE 时指定STATS_PERSISTENT=1采样page的个数通过参数innodb_stats_persistent_sample_pages来控制。

• 测试验证

创建一张测试表，并在表上创建一个索引:

create table dhytest (id int) STATS_PERSISTENT=1; create index idx_id on dhytest(id); 

通过mysql.innodb_index_stats可以查看索引最后收集统计信息的时间,这里的聚集索引我们删除先不用去看，只看自己创建的二级索引

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:37:43]>select * from mysql.innodb_index_stats where database_name = 'test';
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ | test          | dhytest    | idx_id          | 2017-07-10 22:36:06 | n_diff_pfx01 |          0 |           1 | id                                |
| test          | dhytest    | idx_id          | 2017-07-10 22:36:06 | n_diff_pfx02 |          0 |           1 | id,DB_ROW_ID                      |
| test          | dhytest    | idx_id          | 2017-07-10 22:36:06 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | dhytest    | idx_id          | 2017-07-10 22:36:06 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ 7 rows in set (0.00 sec) 

我们手工往表中插入数据，让数据的变化超过%10

 [root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:37:56]>insert into dhytest values (10);
Query OK, 1 row affected (0.00 sec)

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:17]>insert into dhytest select * from dhytest;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:28]>insert into dhytest select * from dhytest;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:31]>insert into dhytest select * from dhytest;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:34]>insert into dhytest select * from dhytest;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:35]>insert into dhytest select * from dhytest;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0 

这时我们在查看下mysql.innodb_index_stats表,last_update时间发生了变化

	

		[root@shadow:/root 5.7.18-log_Instance1 root@localhost:test 22:38:36]>select * from mysql.innodb_index_stats where database_name = 'test';
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ | database_name | table_name | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ | test          | dhytest    | idx_id          | 2017-07-10 22:38:28 | n_diff_pfx01 |          1 |           1 | id                                |
| test          | dhytest    | idx_id          | 2017-07-10 22:38:28 | n_diff_pfx02 |          2 |           1 | id,DB_ROW_ID                      |
| test          | dhytest    | idx_id          | 2017-07-10 22:38:28 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| test          | dhytest    | idx_id          | 2017-07-10 22:38:28 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+ 7 rows in set (0.00 sec)
	

innodb_stats_persistent

控制是否将统计信息持久到磁盘当中，设置此参数之后我们就不需要实时去收集统计信息了，因为实时收集统计信息在高并发下可能会造成一定的性能上影响，并且会导致执行计划有所不同。建议是将此参数打开，将innodb_stats_auto_recalc参数进行关闭。

innodb_stats_persistent_sample_pages

控制收集统计信息时采样的page数量，默认是20。收集的page数量越多，每次收集统计信息的实际则越长，但是统计信息也相对比较准确。

我们可以在创建表的时候对不同的表指定不同的page数量、是否将统计信息持久化到磁盘上、是否自动收集统计信息

	

		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;
	

innodb_stats_on_metadata

此参数在5.6.5版本之前是默认开启的，设置此参数后当我们执行show index 或者 show table status 或者访问INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS表时就会收集统计信息，但是这样可能会导致执行计划改变。 

在以前当表中记录变化超过1/16就会收集统计信息，但是现在如果设置了innodb_stats_persistent就不会有这样的说法了。

innodb_stats_include_delete_marked

5.6.35版本中新增的参数，就是在未提交的事务中如果我们删除了记录，收集统计信息的时候是排查这些删除了的记录的。这样就可能导致统计信息并不是很准确，设置此参数之后就是收集统计信息的时候包含未提交事务中被标记为已删除的数据。

innodb_stats_method

控制统计信息针对索引中NULL值的算法 

当设置为nulls_equal 所有的NULL值都视为一个value group 
当设置为nulls_unequal每一个NULL值被视为一个value group 
设置为nulls_ignored时 NULL值被忽略 

这个参数同事彭许生做过一些测试发现nulls_equal和nulls_unequal没有发现show index中的cardinality有不同的地方，但是如果设置为nulls_ignored的时候会有所不同。

• 测 试

表结构数据

设置为nulls_ignored

设置为nulls_unequal

设置为nulls_equal

推荐配置

• innodb_stats_method 统计信息的自动收集在高并发情况下可能会带来性能的抖动，建议将此参数关闭。 

• innodb_stats_persistent 建议打开此参数将统计信息持久化到磁盘上 。

• innodb_stats_include_delete_marked建议设置开启，这样可以针对未提交事务中删除的数据也收集统计信息 。

• innodb_stats_method经过测试和mos查看到的按默认配置就可以，当然如果设置nulls_ignored时候会让你的语句走到索引，但是效率并不一定是好的。