本文预览：1.项目背景介绍1.1涉及表结构1.2明确查询诉求2.索引问题确认与调优2.1问题发现2.2问题验证2.3索引优化3.总结Part1项目背景介绍看过上一篇文章的同学应该记得，在描述索引原理和实际案例时，我们列举了一个阿里分布式事务中的主事务表的例子。巧合的是，前段时间由于业务需要，我们开发了一个长效交易一致性引擎来处理广告系统中的计费数据上下游一致性问题，其中也涉及到这样一张表。但是在最近的迭代代码走查中，发现索引有问题。0.1涉及的表结构如上图所示，数据库的字段和索引结构大概是这样的。tx_id全局唯一增量字段为主键。status字段标识了记录的当前状态，用于区分未成功执行的记录的创建时间和更新字段，用于辅助time-decay序列的异步恢复。对于各个字段的具体作用，有兴趣的可以浏览一下之前写的文章《分布式事务从入门到放弃(二)--详述DT引擎一致性原理及设计》。0.2明确的查询要求该表的作用是检索那些没有达到最终状态的记录，进行异常恢复。为了避免记录被系统处理，所以时间限制在1分钟前。为了尽可能高效，时间范围限制在前10分钟，较长的故障记录交给频率较低的定时任务处理。为了实现异步处理失败后的时间衰减，使用了modify，也是为了避免旧数据处理出现问题导致新生成的数据积压。申诉其实比较简单：前1分钟到前10分钟的定时钓鱼，状态属于某些状态记录，即：select*fromactivity_twherestatusin(1,2)andgmt_modified>='2021-01-01xx:xx:10'andgmt_modified<'2021-01-01xx:xx:01'orderbygmt_create;Part2索引问题确认与调优0.3问题发现——唯一索引和联合索引PRIMARYKEY(`tx_id`),KEY`idx_status_time`(`status`,`gmt_create`,`gmt_modified`)当前表有两种索引：唯一索引tx_id，联合索引status_ctime_mtime。当然，我们希望的是这个索引的存在，可以让之前的查询语句更加高效。乍一看，好像查询条件和排序条件都包含在联合索引中了。其实上面的查询语句，结合当前的索引，是否能达到预期的效果呢？根据我们上一篇文章中的索引知识，我们可以得出结论，这个索引会很有用，但功能不全。因为在联合索引下，后面位置的索引字段起作用的前提是前面位置的字段值相同。0.4问题验证Explain工具派上用场。键=idx_status_time。键标识此查询实际使用的索引。所以说明我们的联合指数起到了一定的作用。key_len=4。key_len标识的已使用索引字段的长度。对于mysql5.7，status是int类型，占4个，time字段是datetime类型，占5个。而这里len=4，说明只用了status一个索引字段。类型=范围。range表示查询状态已经是一个范围查询。行数=167。说明为了找到结果，遍历了167个。extra='使用索引条件；使用文件排序'。太糟糕了，排序语句触发了文件排序。从上面的结果我们可以知道之前的索引设置不合适，没有使用时间索引，排序的时候使用了额外的文件排序。效率和性能影响比较大，需要淘汰。另外，理论上，随着查询优化器的存在，发现status的区分度不高，可以直接使用索引中的time字段代替status。毕竟这个数据只有两个值，量级上的差异并不算太大。那么，根据创建索引的字段需要有足够的区分度的原则，是否有必要将status字段放在索引中呢？我们带着问题来看一下。0.5索引优化那么，我们应该如何调整索引来实现高效查询呢？调整索引字段的顺序首先考虑调整gmt_modified和gmt_create的顺序。因为，在联合索引下，当中间缺少索引字段时，后面的字段就不起作用了。调整两个时间顺序后，查看索引使用情况：我们看到一个变化：key_len=9。指示使用gmt_modified索引字段。行数=2。这个改动说明我们的调整是有效的，只需要遍历两次就可以查询到数据了。与之前的167相比，效率要高很多。但是，文件排序仍然存在。是否需要在索引中建立状态？我们先把status从索引中删除，再看explain的结果：没有status索引的参与，如果要在where条件中过滤，会比之前消耗更多的性能。因此，状态是必要的。filesort如何在不使用索引的情况下优化排序字段，是否可以为其创建单独的索引？答案是不。因为SQL查询只会使用一个索引，如果查询条件使用了索引，那么排序就不会使用索引。其实你可以看一下：所以单独为排序字段创建索引是没有用的。怎么做？考虑修改sql，让排序字段使用索引。首先我们要知道，mysql在执行orderby时，会先检查执行计划中参与排序的字段是否使用了索引：如果使用了索引，则表示对结果进行了排序，否则，进行排序操作被执行。修改sql如下：select*fromactivity_twherestatusin(1,2)andgmt_modified>='2021-01-01xx:xx:10'andgmt_modified<'2021-01-01xx:xx:01'orderbystatus,gmt_modified,gmt_create;还将querycondition字段添加到sort字段中，可以看到此时Extra中没有filesort。当然，对于排序，你可以考虑是否真的有必要。如果每次处理的异常数据很小，其实不排序也可以。这样你就可以节省一些索引空间。Part3总结本文从一个sql查询和数据索引构建演练，发现索引失效问题，并根据索引知识一步步检查验证，直到我们认为OK。希望通过上面的考察验证过程，结合上一篇文章中的索引原理，让大家对索引有更好的认识。
