MySQL InnoDB什么时候更新索引的统计信息?

MySQL InnoDB的索引统计信息在什么时候更新呢? 或者说什么事件会触发InnoDB索引的统计信息更新呢?下面结合参考资料When Does InnoDB Update the Index Statistics? (Doc ID 1463718.1)[1]简单总结梳理一下(文中大部分知识点来自参考资料)。

1: ANALYZE TABLE命令

ANALYZE TABLE命令会显式强制更新表的索引统计信息。例子:

ANALYZE TABLE <TABLE_NAME>;

2:OPTIMIZE TABLE命令

对InnoDB的表执行OPTIMIZE TABLE命令时, OPTIMIZE TABLE重新组织表的数据和关联索引数据的物理存储,以减少存储空间并提高访问表时的I/O效率。通俗点理解就是碎片整理。它会重建表并执行ANALYZE TABLE命令,因此索引的统计信息也会被更新。

3:元数据查看触发

如果您开启了瞬态统计信息(transient statistics)并且innodb_stats_on_metadata参数为ON(MySQL 5.5及更早版本中的默认值,但在MySQL 5.6及更高版本中不是默认值)的话,那么InnoDB会在以下情况下更新索引的统计信息:

查询元数据信息的SQL语句:

  • SHOW TABLE STATUS
  • SHOW INDEX

或者当你访问INFORMATION_SCHEMA下一些表时也会触发统计信息更新,例如:

  • TABLES
  • STATISTICS。

其实实际环境中,这种条件很少触发索引更新统计信息,因为它的条件一般很难满足,正常情况下,这些参数都不会这样设置。

另外这里补充一下transient statistics的知识:

关于瞬态统计信息(transient statistics), 其实transient statistics也叫non-persistent optimizer statistics,翻译成非持久统计信息,它是当innodb_stats_persistent=OFF 或使用 STATS_PERSISTENT=0时,创建或更改单个表时,优化程序统计信息不会保存到磁盘。相反,统计信息存储在内存中,并在服务器关闭时丢失。统计信息也会通过某些操作和特定条件下定期更新。

4 统计信息自动更新

对于使用瞬态统计信息的表,统计信息是在首次打开表时计算的。这包括FLUSH TABLE和FLUSH TABLES WITH READ LOCK。

此外,当表的有大量数据发生变化时,InnoDB还将触发重新计算索引统计信息。逻辑取决于使用的是持久统计信息还是瞬态统计信息:

  • 持久统计信息:为表启用 STATS_AUTO_RECALC(默认情况下设置为默认启用的 innodb_stats_auto_recalc 值)时,当 1/10 (10%) 行发生更改时,索引统计信息将更新,限制为每次更新之间必须至少经过 10 秒。

  • 瞬态统计信息:自上次更新统计信息以来,表中至少 1/16 (6.25%) 的行已被修改。

另外,关于transient statistics,其实有些版本还是有一些Bug的,例如Bug #98546 Transient indexes statistics are updated in foreground causing performance issue

参考资料

[1]

1: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=342584987828510&id=1463718.1&_afrWindowMode=0&_adf.ctrl-state=5hdz2z4vn_4,

扫描上面二维码关注我
如果你真心觉得文章写得不错,而且对你有所帮助,那就不妨帮忙“推荐"一下,您的“推荐”和”打赏“将是我最大的写作动力!
本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接.