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

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

在一个风和日丽的下午，云掣秀发飘逸的奋哥突然接到业务方线上业务数据库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期间，会对该表加一个读锁。

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

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