登录
首页 >  数据库 >  MySQL

技本功|统计信息对SQL执行效率的影响

来源:SegmentFault

时间:2023-02-16 15:16:59 347浏览 收藏

哈喽!今天心血来潮给大家带来了《技本功|统计信息对SQL执行效率的影响》,想必大家应该对数据库都不陌生吧,那么阅读本文就都不会很困难,以下内容主要涉及到MySQL、数据库、技术、运维、大数据,若是你正在学习数据库,千万别错过这篇文章~希望能帮助到你!

在一个风和日丽的下午,云掣秀发飘逸的奋哥突然接到业务方线上业务数据库CPU资源告警信息,立马放下手里的枸杞登录业务方阿里云控制台查看具体问题。

对于数据库当前正在发生中的问题,我们首先从数据库实时会话信息中尝试抓取有效信息,可以看到该告警实例的会话已经出现堆积状态,大量会话处于"Sending data"状态且从TIME字段可以看到这些会话长时间执行未结束。会话长时间执行表示当前会话一直占用的数据库资源未释放,且堆积会话基本为同一类型的业务SQL,这也就是导致我们数据库资源打高的问题SQL。

image

我们拎出这个问题SQL登录数据库查看SQL的执行计划,对问题SQL进行分析,从SQL执行计划中我们很明显发现一个资源消耗比较大的操作"ALL"全表扫描操作,而且比较诡异的一点是,a表进行表关联possible_keys明明是primary但是却没有使用,所以我们下一步的方向就是排查为什么表关联没有有效利用索引。

image

导致索引失效的问题的原因最常见的就是隐式转换,关于隐式转换我们之前的文章也做过比较详细的讲解,总体概括主要是以下几个场景

1)传递数据类型和字段类型不一致
2)关联字段类型不一致
3)关联字段字符集不一致
4)校验规则不一致

在表关联字段索引失效的情况下,可能导致索引失效的场景主要是2~4,于是我们马上查看表关联字段相关信息进行一一验证。

emmmm,查询到的结果却似乎有些不尽人意,表关联字段均是bigint类型,完美的规避掉了以上所有可能。

image

再次陷入沉思,在没有发生隐式转换的情况下索引一般都是会有效利用的,除非MySQL优化器认为ALL全表扫描的效率并不差。我们知道,MySQL优化器会通过具体表的统计信息基于CBO进行代价计算,帮我们选择最佳执行计划。但是统计信息并不是完全精确的,某些时候可能会出现一定的误差,也正是因为统计信息的误差,就可能导致MySQL优化器错误的选择一个并不是很好的"最佳执行计划"。接下来我们就可以进一步查看表的统计信息以及hint进行验证。

表关联对应的统计信息

image
通过hint强制走primary索引观察执行计划、并测试执行效率

image

问题排查到这里,导致该SQL大量消耗CPU资源的原因也就水落石出了。对于业务方目前的CPU打高的情况,我们可以建议业务方先将目前堆积的会话进行Kill,避免影响其他正常的业务查询,等数据库CPU资源有所回落后,在数据库执行"analyze table"对问题表的统计信息重新采集,统计信息更新后MySQL优化器就可以正确的选择最佳执行计划。

统计信息更新:

image

执行计划更新:

image

虽然客户的问题已经处理,对于本案例还是有一些点值得我们思考:

索引失效的场景都有哪些?
  • 隐式转换
  • 统计信息不准确
MySQL统计信息是如何更新采集?

在MySQL中有一些参数设置决定了统计信息采集的行为方式,一般情况下不会做特别设置,我们需要正确的理解这些参数,明白统计信息只是一个统计估计值,并不是绝对精准。

  • 统计信息相关参数

innodb_stats_method 默认nulls_equal,表示统计信息时把所有的null当作等值对待

innodb_stats_auto_recalc 是否打开自动化采集统计数据 ,默认打开,当表数据量更新10%触发重新采集统计信息

innodb_stats_on_metadata 默认关闭,若该参数开启时表示数据库执行"show table status",访问"INFORMATION_SCHEMA.TABLES or

INFORMATION_SCHEMA.STATISTICS"时,都会触发重新采集统计信息的操作

innodb_stats_persistent 统计信息是否持久化到磁盘,默认打开。持久化磁盘当数据库重新启动后可从磁盘读取。

innodb_stats_persistent_sample_pages 默认20,对于持久化存储统计信息的表,每次重新采集信息需要采集20个索引页进行分析

innodb_stats_transient_sample_pages 默认8,对于非持久化的表,其统计信息重新采集需要扫描8个索引页进行分析

  • MySQL几种重新采集统计信息的时机

新打开一张表时

表数据变更超过10%触发该表的统计信息重新采集

当innodb_stats_on_metadata参数打开,数据库执行"show table status",访问"INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS"时

手动执行analyze tables时

关于analyze table操作

执行该操作需要具有该表的select/insert权限

支持Innodb、Myisam、NDB存储引擎下的表,不支持视图

支持对分区表中某个分区单独执行统计分析:alter table ... analyze partition在执行analyze期间,会对该表加一个读锁。

在探寻了技术的真理后,奋哥又默默的端起了曾经放下的枸杞。

好了,本文到此结束,带大家了解了《技本功|统计信息对SQL执行效率的影响》,希望本文对你有所帮助!关注golang学习网公众号,给大家分享更多数据库知识!

声明:本文转载于:SegmentFault 如有侵犯,请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>