MySQL 5.7 统计信息总结

1.常用SQL

-- 索引
CREATE INDEX idx_name ON test.t1(id,NAME);
ALTER TABLE test.t1 ADD PRIMARY KEY (id);
ALTER TABLE test.t1 DROP INDEX idx_name ;
ALTER TABLE test.t1 DROP PRIMARY KEY ;
SHOW INDEX FROM test.t1;
 
-- 统计信息
SELECT * FROM mysql.`innodb_index_stats`  WHERE database_name='test';
SELECT * FROM mysql.`innodb_table_stats`  WHERE database_name='test';
-- 索引的统计信息
SELECT TABLE_SCHEMA,table_name,index_name,column_name,CARDINALITY 
   FROM information_schema.STATISTICS WHERE table_schema ='test';
-- 收集统计信息   
ANALYZE TABLE test.t1;
show variables like 'innodb_stats%';

2.变量意义

mysql> show variables like 'innodb_stats%';
+--------------------------------------+-------------+
| Variable_name                        | Value       |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc             | ON          |
| innodb_stats_include_delete_marked   | OFF         |
| innodb_stats_method                  | nulls_equal |
| innodb_stats_on_metadata             | OFF         |
| innodb_stats_persistent              | ON          |
| innodb_stats_persistent_sample_pages | 20          |
| innodb_stats_sample_pages            | 8           |
| innodb_stats_transient_sample_pages  | 8           |
+--------------------------------------+-------------+
8 rows in set (0.00 sec)

参数名称 参数意义
innodb_stats_auto_recalc

是否自动触发更新统计信息。当被修改的数据超过10%时就会触发统计信息重新统计计算

innodb_stats_include_delete_marked

控制在重新计算统计信息时是否会考虑删除标记的记录。

innodb_stats_method

对null值的统计方法

innodb_stats_on_metadata

操作元数据时是否触发更新统计信息

innodb_stats_persistent

统计信息是否持久化

innodb_stats_sample_pages

不推荐使用，已经被innodb_stats_transient_sample_pages 替换

innodb_stats_persistent_sample_pages

持久化抽样page数

innodb_stats_transient_sample_pages

瞬时抽样page数

2.1参数innodb_stats_auto_recalc

该参数innodb_stats_auto_recalc控制是否自动重新计算统计信息，当表中数据有大于10%被修改时就会重新计算统计信息（注意，由于统计信息重新计算是在后台发生，而且它是异步处理，这个可能存在延时，不会立即触发，具体见下面介绍）。如果关闭了innodb_stats_auto_recalc,需要通过analyze table来保证统计信息的准确性。不管有没有开启全局变量innodb_stats_auto_recalc。即使innodb_stats_auto_recalc=OFF时，当新索引被增加到表中，所有索引的统计信息会被重新计算并且更新到innodb_index_stats表上。

2.2 参数innodb_stats_include_delete_marked

重新计算统计信息时是否会考虑删除标记的记录.

innodb_stats_include_delete_marked can be enabled to ensure that delete-marked records are included when calculating persistent optimizer statistics.

网上有个关于innodb_stats_include_delete_marked的建议，如下所示，但是限于经验无法对这个建议鉴定真伪，个人觉得还是选择默认关闭，除非有特定场景真有这种需求。

2.3 参数innodb_stats_method

Specifies how InnoDB index statistics collection code should treat NULLs. Possible values are NULLS_EQUAL (default), NULLS_UNEQUAL and NULLS_IGNORED

当变量设置为nulls_equal时，所有NULL值都被视为相同(即，它们都形成一个 value group)。

当变量设置为nulls_unequal时，NULL值不被视为相同。相反，每个NULL value 形成一个单独的 value group，大小为 1。

当变量设置为nulls_ignored时，将忽略NULL值。

更多详细信息，参考官方文档“InnoDB and MyISAM Index Statistics Collection”,另外，还有一个系统变量myisam_stats_method控制MyISAM表对Null值的统计方法。

2.4 参数innodb_stats_on_metadata

参数innodb_stats_on_metadata在MySQL 5.6.6之前的版本默认开启（默认值为O），每当查询information_schema元数据库里的表时（例如，information_schema.TABLES、information_schema.TABLE_CONSTRAINTS … ）或show table status、SHOW INDEX…这类操作时，Innodb还会随机提取其他数据库每个表索引页的部分数据，从而更新information_schema.STATISTICS表，并返回刚才查询的结果。当你的表很大，且数量很多时，耗费的时间就很长，以致很多经常不访问的数据也会进入Innodb_buffer_pool缓冲池中，造成池污染，关闭这个参数，可以加快对于schema库表访问，同时也可以改善查询执行计划的稳定性（对于Innodb表的访问）。所以从MySQL 5.6.6这个版本开始，此参数默认为OFF。

注意：仅当优化器统计信息配置为非持久性时，此选项才生效。这个参数开启的时候，InnoDB会更新非持久统计信息

2.5 参数innodb_stats_persistent

此参数控制统计信息是否持久化，如果此参数启用，统计信息将会保存到mysql数据库的innodb_table_stats和innodb_index_stats表中。从MySQL 5.6.6开始，MySQL默认使用持久化的统计信息，即默认INNODB_STATS_PERSISTENT=ON。 Persistent optimizer statistics were introduced in MySQL 5.6.2 and were made the default in MySQL 5.6.6。设置此参数之后我们就不需要实时去收集统计信息了，因为实时收集统计信息在高并发下可能会造成一定的性能上影响，并且会导致执行计划有所不同。

另外，我们可以使用表的建表参数（STATS_PERSISTENT，STATS_AUTO_RECALC和STATS_SAMPLE_PAGES子句）来覆盖系统变量设置的值，建表选项可以在CREATE TABLE或ALTER TABLE语句中指定。表上面指定的参数会覆盖全局变量，也就是说优先级要高于全局变量。

2.6 参数innodb_stats_persistent_sample_pages

如果参数innodb_stats_persistent设置为ON，该参数表示ANALYZE TABLE更新Cardinality值时每次采样页的数量。默认值为20个页面。innodb_stats_persistent_sample_pages太少会导致统计信息不够准确，太多会导致分析执行太慢。

2.7 参数innodb_stats_sample_pages

已弃用. 已用innodb_stats_transient_sample_pages 替代。

2.8参数innodb_stats_transient_sample_pages

innodb_stats_transient_sample_pages控制采样pages个数，默认为8。Innodb_stats_transient_sample_pages可以runtime设置

innodb_stats_transient_sample_pages在innodb_stats_persistent=0的时候影响采样。注意点：

1.若值太小，会导致评估不准

2.若果值太大，会导致disk read增加。

3.会生产很不同的执行计划，因为统计信息不同。