InnoDB and MyISAM Index Statistics Collection

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: 存储引擎收集表统计信息,供优化器使用,关键数据为平均值组大小,反映相同键前缀值的行数均值。该值影响索引效率,值越大,索引查找行数越多,效用越低。MySQL通过调整`innodb_stats_method`和`myisam_status`系统变量控制统计方法,涉及NULL值处理,如nulls_equal将所有NULL视为同一值组,可能影响索引使用决策。通过设置变量可优化统计信息收集,提升查询性能。

存储引擎收集有关表的统计信息,供优化器使用。表统计基于值组,其中值组是一组具有相同键前缀值的行。出于优化器的目的,一个重要的统计数据是平均值组大小。
MySQL使用平均值组大小的方式如下:
估计每次ref访问必须读取多少行
估计部分连接产生多少行;也就是说,这种形式的操作产生的行数:

随着索引的平均值组大小的增加,该索引对这两个目的的用处较小,因为每次查找的平均行数增加:为了使索引有利于优化,最好每个索引值都针对表中的少量行。当一个给定的索引值产生大量行时,该索引就不那么有用了,MySQL也不太可能使用它。
平均值组大小与表基数有关,表基数是值组的数量。SHOW INDEX语句显示基于N/S的基数值,其中N是表中的行数,S是平均值组大小。该比率得出表中价值组的大致数量。
对于基于<=>比较运算符的连接,NULL的处理方式与任何其他值没有区别:NULL<=>NULL,就像N<=>N对任何其他N一样。
但是,对于基于=运算符的连接,NULL与非NULL值不同:当expr1或expr2(或两者)为NULL时,expr1=expr2不为真。这会影响tbl_name.key=expr形式的比较的ref访问:如果expr的当前值为NULL,MySQL不会访问该表,因为比较不能为真。
For=比较,表中有多少NULL值并不重要。出于优化目的,相关值是非NULL值组的平均大小。但是,MySQL目前无法收集或使用该平均大小。
对于InnoDB和MyISAM表,您可以分别通过InnoDB_stats_method和MyISAM_status系统变量来控制表统计信息的收集。这些变量有三个可能的值,其差异如下:

当变量设置为nulls_equal时,所有NULL值都被视为相同的(也就是说,它们都形成一个值组)。
如果NULL值组的大小远高于平均非NULL值组大小,则此方法会使平均值组大小向上倾斜。这使得在优化器看来,索引对于查找非NULL值的连接来说,并没有真正有用。因此,nulls_equal方法可能会导致优化器在应该使用索引进行ref访问时不使用索引。
当变量设置为nulls_incequal时,NULL值不被视为相同。相反,每个NULL值形成一个大小为1的单独值组。
如果有许多NULL值,此方法会使平均值组大小向下倾斜。如果平均非NULL值组大小较大,将每个NULL值计数为大小为1的组会导致优化器高估查找非NULL值的联接的索引值。因此,当其他方法可能更好时,nulls_incequal方法可能会导致优化器使用此索引进行引用查找。
当变量设置为nulls_ignored时,NULL值将被忽略。

如果你倾向于使用许多使用<=>而不是=的连接,那么NULL值在比较中并不特殊,一个NULL值等于另一个NULL。在这种情况下,nulls_equal是适当的统计方法。

innodb_stats_method系统变量具有全局值;myisam stats method系统变量具有全局值和会话值。
设置全局值会影响从相应存储引擎中收集表的统计信息。设置会话值仅影响当前客户端连接的统计信息收集。
这意味着您可以通过设置myisam_sets_method的会话值,强制使用给定的方法重新生成表的统计信息,而不会影响其他客户端。
要重新生成MyISAM表统计信息,可以使用以下任何方法:

image.png

更改表以使其统计信息过期(例如,插入一行然后删除它),然后设置myisam_status方法并发出ANALYZE table语句

关于使用innodb_stats_method和myisam_states_method的一些注意事项:
您可以强制显式收集表统计信息,如上所述。然而,MySQL也可能自动收集统计数据。
例如,如果在执行表的语句的过程中,其中一些语句修改了表,MySQL可能会收集统计数据。(例如,这可能发生在批量插入或删除,或某些ALTER TABLE语句中。)
如果发生这种情况,则使用innodb_stats_method或myisam_status _method当时的任何值收集统计数据。
因此,如果您使用一种方法收集统计信息,但在稍后自动收集表的统计信息时将系统变量设置为另一种方法,则使用另一种方式。
无法判断使用哪种方法为给定的表生成统计数据。
这些变量仅适用于InnoDB和MyISAM表。其他存储引擎只有一种收集表统计信息的方法。通常它更接近于nulls_equal方法。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
6月前
|
存储 安全 关系型数据库
|
6月前
|
存储 缓存 关系型数据库
MySQL - 存储引擎MyISAM和Innodb
MySQL - 存储引擎MyISAM和Innodb
|
25天前
|
存储 关系型数据库 MySQL
什么是MyISAM和InnoDB
【10月更文挑战第17天】什么是MyISAM和InnoDB
31 0
|
6月前
|
存储 关系型数据库 MySQL
MySQL引擎对决:深入解析MyISAM和InnoDB的区别
MySQL引擎对决:深入解析MyISAM和InnoDB的区别
1472 0
|
6月前
|
存储 SQL 关系型数据库
MySQL存储引擎之MyISAM和InnoDB
MySQL存储引擎之MyISAM和InnoDB
71 0
|
1月前
|
存储 缓存 关系型数据库
详细解析MySQL中的innodb和myisam
总之,InnoDB和MyISAM各有千秋,选择合适的存储引擎应基于对应用程序特性的深入理解,以及对性能、数据完整性和可扩展性的综合考量。随着技术发展,InnoDB因其全面的功能和日益优化的性能,逐渐成为更广泛场景下的首选。然而,在特定条件下,MyISAM依然保留其独特的价值。
115 0
|
3月前
|
存储 关系型数据库 MySQL
一天五道Java面试题----第八天(怎么处理慢查询--------->简述Myisam和innodb的区别)
这篇文章是关于Java面试中关于数据库性能优化和MySQL特性的五个问题,包括处理慢查询、ACID特性保证、MVCC概念、MySQL主从同步原理以及MyISAM和InnoDB存储引擎的区别。
|
4月前
|
存储 SQL 关系型数据库
(十三)MySQL引擎篇:半道出家的InnoDB为何能替换官方的MyISAM?
MySQL是一款支持拔插式引擎的数据库,在开发过程中你可以根据业务特性,从支持的诸多引擎中选择一款适合的,例如MyISAM、InnoDB、Merge、Memory(HEAP)、BDB(BerkeleyDB)、Example、Federated、Archive、CSV、Blackhole.....
|
5月前
|
缓存 关系型数据库 MySQL
MySQL数据库——InnoDB引擎-架构-内存结构(Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer)
MySQL数据库——InnoDB引擎-架构-内存结构(Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer)
98 3
|
5月前
|
存储 关系型数据库 MySQL
MySQL数据库——存储引擎(2)-存储引擎特点(InnoDB、MyISAM、Memory)、存储引擎选择
MySQL数据库——存储引擎(2)-存储引擎特点(InnoDB、MyISAM、Memory)、存储引擎选择
81 1