关于运维:基本功统计信息对SQL执行效率的影响

35次阅读

共计 2036 个字符,预计需要花费 6 分钟才能阅读完成。

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

对于数据库以后正在产生中的问题,咱们首先从数据库实时会话信息中尝试抓取无效信息,能够看到该告警实例的会话曾经呈现沉积状态,大量会话处于 ”Sending data“ 状态且从 TIME 字段能够看到这些会话长时间执行未完结。会话长时间执行示意以后会话始终占用的数据库资源未开释,且沉积会话根本为同一类型的业务 SQL,这也就是导致咱们数据库资源打高的问题 SQL。

咱们拎出这个问题 SQL 登录数据库查看 SQL 的执行打算,对 问题 SQL进行剖析,从 SQL 执行打算中咱们很显著发现一个资源耗费比拟大的操作 ”ALL” 全表扫描操作,而且比拟诡异的一点是,a 表进行表关联 possible_keys 明明是 primary 然而却没有应用,所以咱们下一步的方向就是排查为什么表关联没有无效利用索引。

导致索引生效的问题的起因最常见的就是隐式转换,对于隐式转换咱们之前的文章也做过比拟具体的解说,总体概括次要是以下几个场景

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

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

emmmm,查问到的后果却仿佛有些不尽人意,表关联字段均是 bigint 类型,完满的躲避掉了以上所有可能。

再次陷入深思,在没有产生隐式转换的状况下索引个别都是会无效利用的,除非 MySQL 优化器认为 ALL 全表扫描的效率并不差。咱们晓得,MySQL 优化器会通过具体表的统计信息基于 CBO 进行代价计算,帮咱们抉择最佳执行打算。然而统计信息并不是齐全准确的,某些时候可能会呈现肯定的误差,也正是因为统计信息的误差,就可能导致 MySQL 优化器谬误的抉择一个并不是很好的 ”最佳执行打算“。接下来咱们就能够进一步查看表的统计信息以及 hint 进行验证。

表关联对应的统计信息

通过 hint 强制走 primary 索引察看执行打算、并测试执行效率

问题排查到这里,导致该 SQL 大量 耗费 CPU 资源 的起因也就上不着天; 下不着地了。对于业务方目前的 CPU 打高的状况,咱们能够倡议业务方先将目前沉积的会话进行 Kill,防止影响其余失常的业务查问,等数据库 CPU 资源有所回落后,在数据库执行 ”analyze table“ 对问题表的统计信息从新采集,统计信息更新后 MySQL 优化器就能够正确的抉择最佳执行打算。

统计信息更新:

执行打算更新:

尽管客户的问题曾经解决,对于本案例还是有一些点值得咱们思考:

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

  • 隐式转换
  • 统计信息不精确

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 期间,会对该表加一个读锁。

在我探寻了技术的真谛后,我又默默的端起了已经放下的枸杞。

正文完
 0