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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

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

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
2月前
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
2月前
|
存储 关系型数据库 MySQL
MySQL数据库索引的数据结构?
MySQL中默认使用B+tree索引,它是一种多路平衡搜索树,具有树高较低、检索速度快的特点。所有数据存储在叶子节点,非叶子节点仅作索引,且叶子节点形成双向链表,便于区间查询。
98 4
|
4月前
|
存储 关系型数据库 MySQL
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
|
18天前
|
存储 关系型数据库 MySQL
介绍MySQL的InnoDB引擎特性
总结而言 , Inno DB 引搞 是 MySQL 中 高 性 能 , 高 可靠 的 存 储选项 , 宽泛 应用于要求强 复杂交易处理场景 。
59 15
|
5月前
|
存储 网络协议 关系型数据库
MySQL8.4创建keyring给InnoDB表进行静态数据加密
MySQL8.4创建keyring给InnoDB表进行静态数据加密
149 1
|
1月前
|
存储 关系型数据库 MySQL
使用命令行cmd查询MySQL表结构信息技巧分享。
掌握了这些命令和技巧,您就能快速并有效地从命令行中查询MySQL表的结构信息,进而支持数据库维护、架构审查和优化等工作。
198 9
|
2月前
|
存储 SQL 关系型数据库
MySQL 核心知识与索引优化全解析
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。 索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。 索引优化部分,列出了索引创建的六大原则
|
3月前
|
存储 关系型数据库 MySQL
MySQL覆盖索引解释
总之,覆盖索引就像是图书馆中那些使得搜索变得极为迅速和简单的工具,一旦正确使用,就会让你的数据库查询飞快而轻便。让数据检索就像是读者在图书目录中以最快速度找到所需信息一样简便。这样的效率和速度,让覆盖索引成为数据库优化师傅们手中的尚方宝剑,既能够提升性能,又能够保持系统的整洁高效。
122 9
|
4月前
|
机器学习/深度学习 关系型数据库 MySQL
对比MySQL全文索引与常规索引的互异性
现在,你或许明白了这两种索引的差异,但任何技术决策都不应仅仅基于理论之上。你可以创建你的数据库实验环境,尝试不同类型的索引,看看它们如何影响性能,感受它们真实的力量。只有这样,你才能熟悉它们,掌握什么时候使用全文索引,什么时候使用常规索引,以适应复杂多变的业务需求。
108 12
|
5月前
|
SQL 缓存 关系型数据库
使用温InnoDB缓冲池启动MySQL测试
使用温InnoDB缓冲池启动MySQL测试
104 0

推荐镜像

更多