乐趣区

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

jiezi

在一个风和日丽的下午，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 期间，会对该表加一个读锁。

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

退出移动版