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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:

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来手动更新统计信息,否则统计信息永远不变。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
12天前
|
SQL 存储 关系型数据库
MySQL索引(二)索引优化方案有哪些
MySQL索引(二)索引优化方案有哪些
30 0
|
2天前
|
SQL 关系型数据库 MySQL
MySQL索引与事务
MySQL索引与事务
|
2天前
|
关系型数据库 MySQL
Mysql基础第十三天,创建计算字段
Mysql基础第十三天,创建计算字段
9 0
|
2天前
|
存储 SQL 关系型数据库
【MySQL 数据库】6、一篇文章学习【索引知识】,提高大数据量的查询效率【文末送书】
【MySQL 数据库】6、一篇文章学习【索引知识】,提高大数据量的查询效率【文末送书】
42 0
|
2天前
|
监控 关系型数据库 MySQL
MySQL创建索引的注意事项
在索引的世界中,权衡是关键。权衡读写性能,权衡索引的数量和类型,权衡查询的频率和数据分布。通过谨慎的设计、定期的维护和持续的监控,我们能够确保索引在数据库中的角色得到最大的发挥,为应用提供更加高效和可靠的数据访问服务。在数据库优化的旅途中,索引是我们的得力助手,正确使用它将使数据库系统更具竞争力和可维护性。
10 0
|
4天前
|
SQL 关系型数据库 MySQL
京东三面:什么情况会导致 MySQL 索引失效?
为了验证 MySQL 中哪些情况下会导致索引失效,我们可以借助 explain 执行计划来分析索引失效的具体场景。
19 0
|
5天前
|
SQL 存储 关系型数据库
Mysql内核查询成本计算
Mysql内核查询成本计算
|
5天前
|
存储 监控 关系型数据库
Mysql内部在索引层面的优化
Mysql内部在索引层面的优化
|
5天前
|
SQL 算法 关系型数据库
MySQL索引优化实战二
MySQL索引优化实战二
|
5天前
|
SQL 存储 关系型数据库
Mysql索引优化实战一
Mysql索引优化实战一