问题这是一道线上题，从日志平台查询SQL的执行时间是11.146s，算是慢查询，美化SQL如下：首先找到表和索引的定义如下：可见，有两个联合索引：status,to_account_id和status,from_account_id问题分析先用explain查看一下执行计划：先看解释栏的意思。id：Nothing只是一个ID，如果没有子查询，通常只有一行。select_type：大致分为简单查询和复杂查询两种。复杂查询分为简单子查询、派生表（from中的子查询）和联合。一般我们看到比较简单的，就是不包含subquery和union。如果有一个复杂的查询，它将被标记为主要的。table：表名type：表示关联类型，决定了Mysql如何查找行数据。这通常是我们查看查询时的关键信息点。比如ALL是全表扫描；index表示使用索引；range表示一个有限的扫描索引，比直接扫描所有索引要好；ref也是一个索引查找，会返回匹配特定值的行数据，还有一些其他的类型，比如eq_ref只返回一条匹配的记录，const会被优化转换成常量。possible_keys：显示可以使用但不一定使用的索引。key：实际使用的索引。key_len：索引使用的字节数。ref：表示在上面的键列中进行索引查找时使用的列或常量值。rows：要读取的行数以查找符合条件的数据。filtered：表示满足查询条件的数据占表行数的百分比。rows*filtered可以粗略的获取关联行数，Mysql5.1之后新增的一个字段。extra：额外的信息，比如usingindex表示使用覆盖索引，usingwhere表示在存储引擎之后过滤，usingtemporary表示使用临时表，usingfilesort表示在外部对结果进行排序。根据上面的经验，我们看到索引和扫描行数都没有问题，但是我们发现执行计划中使用了usingfilesort。综合执行sql和tabledefinition后，基本得出问题出在ORDERBYamountdesc,create_timeasc，生产线上有很多条数据记录。使用orderby语句后，造成filesort，导致外部排序，降低了SQL的查询性能。了解一下orderby的工作原理，可以帮助我们更好的优化SQL。一般情况下，如果执行计划中出现usingfilesort，就会按照上面的执行流程。对于Mysql，如果数据量小，会在内存中排序，如果数据量大，则需要在磁盘中排序。这个过程统称为文件排序。先根据索引找到对应的数据，然后将数据放入排序缓冲区。如果待排序数据的实际大小没有超过缓冲区的大小，则采用内存排序，如快速排序，然后取出符合条件的数据返回。如果超过缓冲区的大小，则需要外部排序。该算法一般采用多路归并排序。首先将数据分块，然后对每块数据进行排序。排序结果存储在磁盘中，最后合并排序结果。除了知道排序过程之外，排序使用的是字段定义的最大长度，而不是实际存储的长度，所以会占用更多的空间。另外，在5.6之前的版本中，如果涉及到多表关联查询，排序字段来自不同的表，关联结果会保存在临时表中。这是我们平时使用temporary看到的场景；使用文件排序。使用limit，排序后会出现limit，也可能导致排序后的数据量非常大。在整体情况下，缓冲区大小、排序字段的数据长度、查询数据的条数等都会影响查询性能。分析了整个排序过程，指导的优化思路是尽量不要使用usingfilesort，尤其是排序的数据量比较大的时候，那么优化的方式就是尽量让查询到的数据已经排序好，这是合理的使用组合索引和覆盖索引。优化方向优化一：调整索引结构优化二：代码结构优化另外，我们发现了一段代码，在一个for循环中运行，然后更新DB表中的状态，会导致1500次DB更新。可以考虑更新DB对更新做批处理，减少DB的写入次数，比如对100条记录进行一次DB更新，这样会大大减少写入DB的次数。这样，每次方法调用都会将3000次写操作减少到30次写操作。当然，批量大小是可以调整的。这里只关注SQL调优，代码问题暂时不考虑。性能结果测试环境数据量为30万条数据。优化前，查询时间超过1.5s。优化后，查询时间约为0.4s。查询性能提升3~4倍。从生产从库查询，数据量约3KW+，满足where条件的数据约300万条。优化前查询耗时11s到14s。优化后查询耗时0.8s左右，性能提升10倍以上。虽然这个优化比较简单，但是我们还是要有扎实的基础，才能选择最合理的优化方式。本文转载自微信公众号“爱小仙”，可通过以下二维码关注。转载本文请联系艾小仙公众号。
