MySQL中Innodb如何计算索引的统计信息？

MySQL查询优化器的执行计划是根据统计信息中键值的分布选择合适的索引，这是基于索引的选择性的。innodb通过抽样的方式来计算统计信息，首先随机的读取少量的索引页面，然后以此为样本计算索引的统计信息。老的innodb默认样本页面数为8，新版本可以通过innodb_stats_transient_sample_pages（5.6.3之前是innodb_stats_sample_pages）来设置样本页的数量。样本页的数量设置的更大，理论上来说是可以得到更准确的统计信息，特别是对于超大的表。但是具体设置多大合适还是需要根据实际情况

 

innodb索引的统计信息存储方式有两种，一种是非持久性存储，既存储在内存中，如果服务器重启就会丢失；一种是持久性存储，即存储到磁盘上，可以永久保存。通过参数innodb_stats_persistent来控制。在MySQL5.6.6之后，默认是持久性存储。

 

两种存储方式：

1ã  非持久性存储，通过设置innodb_stats_persistent=OFF或者使用STATS_PERSISTENT=0创建，通过以下操作可以触发计算统计信息：

a)         执行analyze table

b)         在使用show table status、show index等命令的时候，或者在查询系统表INFORMATION_SCHEMA.TABLES 和 INFORMATION_SCHEMA.STATISTICS的时候。需要一个参数控制是否会触发更新统计信息，innodb_stats_on_metadata=on时。

 

这里需要注意的是，数据库中有大量的表或者索引的时候，会给数据库的IO带来更大的压力；并且如果频繁的更新统计信息，MySQL的执行计划的稳定性也会受到影响。

c)         在启动mysql客户端的时候采用--auto-rehash参数。

d)         一个表首次被打开的时候。

e)         表发生非常大的变化的时候（大小变化超过1/16或者新插入20亿行数据）。

2ã  持久性存储，设置innodb_stats_persistent=ON，或者STATS_PERSISTENT=1创建。

持久化的信息存储在MySQL的系统表mysql.innodb_table_stats 和mysql.innodb_index_stats 中。

 

因为是持久性存储到磁盘上，所以在表一段时间之后或者是进行大的改动的时候需要手动执行analyze table来更新统计信息。

 

总结：建议设置持久性存储到磁盘上，可以得到更稳定的执行计划，并且在系统重启之后可以更快速的生成统计信息。但是需要周期性的执行analyze table来手动更新统计信息，否则统计信息永远不变。