生产慢查询问题分析

1.问题描述

7月1日零点set_24出现大量慢查询告警，经DBA定位为子系统涉及的一条查询语句出现慢查询，引起set_24的cpu使用率突增，触发大量告警，查看生产执行计划发现慢查询为索引跳变引起；具体出现问题的sql语句如下：

该sql为查询三个月内满足条件的还款代金券列表，其中user_prizes表和trans_order表都是大表，数据量达到亿级别，user_prizes表有如下几个索引：

正常情况下该语句走的ecifNo索引，当天零点后该sql语句出现索引跳变，走了createTime索引，导致出现慢查询。

2.问题处理方式

生产定位到问题后，因该sql的查询场景为前端触发，当天为账单日，请求量大，DBA通过kill脚本临时进行处理，同时准备增加强制索引优化的紧急版本，通过加上强制索引force index(ecifNo)处理索引跳变，DBA也同步在备库删除createTime索引观察效果，准备进行主备切换尝试解决，但在备库执行索引删除后查看执行计划发现又走了time_activity索引，最后通过发布增加强制索引优化的紧急版本进行解决。

3.问题分析

mysql优化器选择索引的目的是找到一个最优的执行方案，并用最小的代价去执行语句，扫描行数是影响执行代价的重要因素，扫描行数越少，意味着访问磁盘数据次数越少，消耗的cpu资源越少，除此之外，优化器还会结合是否使用临时表，以及是否排序等因素综合判断；
出现索引跳变的这个sql有order by create_time，且create_time为索引，userPrizes表是按月分区的，数据总量为1亿400多万，通过统计生产数据量分布情况发现，近几个月的分区数据量如下：

该sql7月1日零点后查询的是4月1日之后的数据，3月份分区的数据量为958万多，4月之后分区数量都保持在500多万。也就是7月1日之后查询的数据量突然减少958万多，查询的数据量大量减少，可能导致优化器认为走时间索引createTime的区分度更高，同时还可以避免排序，因而选择了时间索引。查看索引跳变后的执行计划如下：

走createTime索引虽然可以避免排序，但从执行计划的type=range可看出为索引范围的扫描，根据索引createTime扫描记录，通过索引叶子节点的主键值回表查找完整记录，然后判断记录中满足sql过滤条件的数据，再将结果进行返回，而该语句为查找满足条件的1000条数据，正常情况下一个ecifNo满足条件的数据量不会超过1000条，要找到所有满足条件的记录就是索引范围的扫描加回表查询，加上查询范围内的数据量大，因此走createTime索引就会非常慢。

4.总结

由于mysql在真正执行语句的时候，并不能准确的知道满足这个条件的记录有多少，只能通过统计信息来估算记录，而优化器并不是非常智能的，就有可能发生索引跳变的情况，这种情况很难在测试的时候复现出来，生产也可能是突然出现，所以我们只能在使用上尽量的去降低索引发生跳变的可能性，尽量避免出现该问题。我们可以在创建索引和使用sql的时候通过以下几个点进行检视。
(1) 索引的创建
创建索引的时候要注意尽量避免创建单列的时间字段(createTime、updateTime)索引，避免留坑，因为很多场景都可能用到时间字段进行排序，有排序的情况若排序字段又是单列索引字段，就可能引起索引跳变，如果需要使用时间字段作为索引时，尽量使用联合索引，且时间字段放在后面；高效的索引应遵循高区分度字段+避免排序的原则。
创建索引的时候也要尽量避免索引重复，且一张表的索引个数也要控制好，索引过多也会影响增删改的效率。
(2) sql的检视
检视历史和新增的sql是否有order by，且order by的第一个字段是否有单列索引，这种存在索引跳变的风险，需要具体分析后进行优化；
写sql语句的时候，尽可能简单化，像union、排序等尽量少在sql中实现，减少sql慢查询的风险。
根据我们梳理出来的表我们将存在的风险分为几类：

（1） 改造计划

• 首先高风险和较高风险的我们是安排版本紧急解决。
  但是版本时间比较紧急，先采用直观的方式来处理，通过force强制索引将这些语句固化。在force固化的时候需要注意动态sql的处理，防止强制索引不生效的情况。如果动态sql场景多，就需要按照实际业务场景组合拆分sql，然后分情况去处理。
• 按计划分多版本处优化表的不合理索引，冗余索引。
  a）在分析过程中，发现一些表的索引有冗余和多种组合索引的情况。冗余索引可以按计划清理掉，多种组合索引的情况要从需求分析是否能精简，索引越多对于优化器的索引选择确定性越低。
  b）对于大表来说，时间的索引比较容易出问题，对于时间的单索引要谨慎选择。有需要的情况下，按照建议是将别的区分度更高的索引和时间索引做联合。
• 从业务需求设计上重新优化。
  在分析过程中，一些可以从需求设计上来避免慢查询的风险。比如：
  a）涉及大表的复杂查询逻辑后置，避免首页进行一些大表的复杂查询，降低触发风险的可能性和影响。
  b）对于一些报表类的查询需要时间索引，同业务沟通使用其他区分度高的字段和时间进行联合查询的方式，不再支持单时间维度的实时查询(如果需要走BDP的异步查询机制)。
• 根源上大表的处理。
  在系统中，核心的子系统早期设计在ADM。这也是导致大表出现的一个原因。从大表的处理上后续准备按照以下思路来处理：
  a）调整大表分区的回收时间策略。
  之前时间保留比较长，同业务沟通降低大表分区保存的时间跨度。为了避免对业务产生影响，需要同步做下处理。
  例如：我们在回收订单表时，先将客户比较关心的历史已使用的券迁移出来（这部分数据在整体订单量中占比比较小），放在独立的表中，用来支持较长时间跨度的查询。
  b）分表处理。
  将部分大表进行手动分表处理，不同的表按照实际业务场景进行拆分。
  例如：营销表可以按照活动ID的维度来拆分子表。

（2）设计上的思考

除了上述一些针对问题本身的处理方式之外，这次给我启发更多的是设计原则上的一些思考。
从我们的应用部署方式上来说，应用层是可以横向扩展的，我们的瓶颈往往集中在数据库层面。
所以我们应当在数据处理层要处理得更加简洁，尽量减少复杂联合查询，优化掉区分度较低的索引，部分数据的逻辑处理可以在应用内存中去解决。
大表的索引添加要非常谨慎的选择，这就要求我们后续从需求交互到系统设计上更加严格。

（3）一些额外的思考

除了代码层面的处理，对于索引突变问题最近也在思考一些预警机制。
我们有慢查询的一些监控机制，我们也支持耗时统计的功能，准备和运维同学一起看下是否满足在生产开放统计的要求，方便我们监控sql查询的耗时变化情况。
但是索引选择受到的影响因素的确很多（数据量，数据分布，磁盘IO等），测试环境有时很难模拟出生产的一些情况。
最近看到一些基于AI+数据驱动的慢查询索引推荐方案，正在探索是否能带来一些不一样的机制。

附录

优化器到底是如何选择执行计划？选择优化器认为最优的索引的呢？MySQL的优化器优化方法是——基于成本的优化选择，首先，一条查询语句在MySQL中的查询成本是由两部分组成的：

I/O成本：指从磁盘到内存的加载过程损耗的时间
CPU成本：读取记录以及检测记录是否满足对应搜索条件、对结果集进行排序等这些操作所消耗的时间

对于我们常见的InnoDB引擎来说：页是磁盘和内存之间进行交互的基本单位，MySQL的设计者规定：读取一个页面花费的成本默认为1.0；读取以及检测一条记录是否满足搜索条件的成本默认是0.2（读取时不需要检测是否符合搜索条件成本也为0.2）这里的0.2 和 1.0 称之为成本常数（MySQL的engine_cost表中保存了一些存储引擎层面进行操作对应的成本常数，可通过手动调节成本常数更为精确的控制生成执行计划时的成本计算过程，不过并不建议这么做！）

MySQL基于成本的优化步骤：
1、根据搜索条件，找出所有可能使用的索引
2、计算全表扫描的代价
3、计算使用不同索引执行查询的代价
4、对比各种执行方案的代价，找出成本最低的方案

分别来分析下几种情况：
单表查询的成本：

按照上述步骤，我们逐条分析：
首先会根据搜索条件，列出所有可能用到的索引，也就是possible keys
然后计全表扫描的代价：把聚簇索引中的记录依次与给定的搜索条件进行比较，并把符合条件的记录加到结果集中。这个过程的成本代价=I/O成本+CPU成本，而计算这个值需要得知两个信息：聚簇索引占用的页面数和该表中的记录数，对应值可以在表的统计信息里看到： show table status like ‘tableName’; 统计信息中维护了两个字段 Rows (表中的记录条数) 和 Data_length (表占用的存储空间字节数)，由此可以算出全表扫描的总成本=聚簇索引占用的页面数(I/O成本) + 统计表中表的记录数 (CPU成本)
计算不同索引执行查询的代价：此时优化器会优先计算一二级索引的成本，然后才会计算普通索引的成本。而对于使用索引的成本计算=扫描的区间数量+预估的二级索引记录条数 (I/O成本) + 读取二级索引的记录条数 + 读取并检测回表操作后聚簇索引记录的成本 (CPU成本) + 可能存在的index merge
对比各种执行方案的代价，找出成本最低的方案

两表连接查询的成本：

MySQL中连接查询使用的是循环嵌套连接算法，驱动表会被访问一次，被驱动表可能会被访问多次，所以对于连接查询，查询成本=单次查询驱动表成本 + 多次查询被驱动表成本（具体取决于针对驱动表查询后的结果集中的记录条数）。我们把查询驱动表后得到的记录条数称之为驱动表的扇出 fanout，驱动表的扇出值越小，对被驱动表的查询次数越少，连接查询的总成本就越小。那么此时存在两种情况：1、全表扫描驱动表；2、根据索引查询，同时除了计算满足索引的查询条件以外还有没有其他满足搜索条件的记录，这个过程也被称为条件过滤。
也就是说，我们可以将上述公式转化的更清晰一点：查询成本= 单次查询驱动表成本 + 驱动表扇出值 * 单次查询被驱动表成本。

对于外连接（左连接、右连接）查询来说：驱动表是固定的，只需要分别为驱动表和被驱动表选择成本最低的访问方案即可得到最优的执行计划。对于内连接来说：驱动表和被驱动表可以换位置，此情况需要考虑最优的连接顺序，然后分别计算连接查询的成本，从而计算出最优的执行计划。
针对于两表连接查询的优化建议：尽量减少驱动表的扇出；访问被驱动表的成本尽量低

多表连接查询的成本：

对于表A、B两表连接查询的连接顺序只可能是 AB 或者 BA，而对于多表来说就存在多种情况 ———— n表连接的连接顺序共有n!种顺序。那么其实对MySQL优化器来讲其实就需要计算n!查询成本。针对这种情况，MySQL优化器做了优化来减少计算不同连接顺序的性能损耗：

提前结束某种连接顺序的成本评估：MySQL会维护一个全局变量用于表示当前最小的连接查询成本，在分析某种连接成本时，如果已经超过当前值，则提前结束成本计算

系统变量optimizer_search_depth：如果连接表的数量小于该值，则穷举分析每种连接顺序的成本，否则只计算optimizer_search_depth值相同的表连接顺序，该值越大，成本分析越精确，越容易得到更优的执行计划，但是消耗的分析时间也越长

某些规则下根本不考虑某些连接顺序：optimizer_prune_level参数用于控制这些启发式规则（根据以往经验制定的规则，凡是不满足规则的场景一律不计算成本）

熟悉以上的优化规则，可以帮助我们写出更优的SQL和建立更加合理的索引。