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

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

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

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

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

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

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

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

在表关联字段索引失效的情况下,可能导致索引失效的场景主要是2~4,于是我们马上查看表关联字段相关信息进行一一验证。emmmm,查询到的结果却似乎有些不尽人意,表关联字段均是bigint类型,完美的规避掉了以上所有可能。

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

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

表关联对应的统计信息

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

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

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

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

统计信息更新:

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

执行计划更新:

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

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

索引失效的场景都有哪些?

隐式转换
统计信息不准确
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期间,会对该表加一个读锁。

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

上一篇:如何选择容器服务Kubernetes集群的网络插件


下一篇:KubeCon 18会议分享 -- k8s网络复杂性和诊断