Mysql 调优记： INNER JOIN查询 Using temporary; Using filesort 问题优化

近期笔者在生产环境中发现一条执行非常慢的sql。大概时间为5s左右，于是乎对改SQL场景进行EXPLAIN 分析，发现一个在执行过程中出现对“Using temporary; Using filesort ”。即在执行过程中产生了临时表来存储结果，并在排序时根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序。经过合理对优化，最终EXPLAIN后，没有再发现明细对SQL性能瓶颈，并且SQL执行时间在1s以内。遂将这点经验记录下来。

先看看SQL和大致的表结构和数据量：

订单表 order 表：大概有15w数据

CREATE TABLE `order` (
  `id` char(32) NOT NULL COMMENT 'id',
  `order_status` varchar(5) NOT NULL DEFAULT '0' COMMENT '订单状态',
  `order_time` datetime DEFAULT NULL COMMENT '下单时间',
   PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单数据';

与订单表关联的付款表：大概11w数据

CREATE TABLE `order_payment` (
  `id` char(32) NOT NULL COMMENT 'id',
  `order_id` char(32) NOT NULL DEFAULT '' COMMENT '订单id',
  `money` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '付款金额',
 PRIMARY KEY (`id`),
 KEY `i_order_id` (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单付款数据';

大概了解表结构后，来看一个很普通sql查询场景，查询所有运单状态未完成的订单，并获取其订单ID和付款金额，并按下单时间倒序。

SQL 很简单：

select o.id, op.money from `order` o 
inner join order_payment op on o.id = op.order_id 
where o.order_status = '4' order by o.order_time DESC;

然后通过EXPLAIN 分析，发现：

对于上述EXPLAIN的各列含义本文不在多赘述，有兴趣的可以查阅笔者的其他博文有介绍。

首先，了解Using temporary; Using filesort 到底是什么，为什么会成为性能杀手。

Using temporary

表示由于排序没有走索引、使用union、子查询连接查询、使用某些视图等原因，因此创建了一个内部临时表。注意这里的临时表可能是内存上的临时表，也有可能是硬盘上的临时表，理所当然基于内存的临时表的时间消耗肯定要比基于硬盘的临时表的实际消耗小。
当mysql需要创建临时表时，选择内存临时表还是硬盘临时表取决于参数tmp_table_size和max_heap_table_size，内存临时表的最大容量为tmp_table_size和max_heap_table_size值的最小值，当所需临时表的容量大于两者的最小值时，mysql就会使用硬盘临时表存放数据。

无论是基于内存，还是基于硬盘。构建临时表的过程是比较耗时的一个操作

Using filesort

Using filesort的含义不能望字生意，它并不代表使用文件排序。仅仅表示没有使用索引的排序。
filesort使用的算法是QuickSort，即对需要排序的记录生成元数据进行分块排序，然后再使用mergesort方法合并块。其中filesort可以使用的内存空间大小为参数sort_buffer_size的值，默认为2M。当排序记录太多sort_buffer_size不够用时，mysql会使用临时文件来存放各个分块，然后各个分块排序后再多次合并分块最终全局完成排序。
笔者个人的理解是，Using filesort并不一定是性能杀手。因为要优化掉Using filesort的唯一办法就是让排序走索引字段，但这情况要根据实际情况来取舍，毕竟建立索引也是有代价的。

了解了上述两个问题产生的原因后，回看之前的sql为和为出现这两个问题。

Using filesort就不用说了，没有走索引导致的。重点来看Using temporary。为何会产生临时表。

MYSQL优化器：JOIN中的顺序选择

Mysql在遇到inner join联接语句时，MySQL表关联的算法是 Nest Loop Join（嵌套联接循环），Nest Loop Join就是通过两层循环手段进行依次的匹配操作，最后返回结果集合。SQL语句只是描述出希望连接的对象和规则，而执行计划和执行操作要切实际将一行行的记录进行匹配。Nest Loop Join的操作过程很简单，很像我们最简单的排序检索算法，两层循环结构。进行连接的两个数据集合（数据表）分别称为外侧表（驱动表）和内侧表（非驱动表）。Mysql又会怎样去确定，哪张表是驱动表，哪张表又是非非驱动表呢？mysql它以表中数据最小的一张表作为驱动表(也就是基表)，而另一张表就叫做非驱动表，首先处理驱动表中每一行符合条件的数据，之后的每一行数据和非驱动表进行连接匹配操作，直到循环结束，最后合并结果、返回结果给用户。对于驱动表的字段它是可以直接排序的，然而对于非驱动表的字段排序需要通过循环查询的合并结果（临时表）进行排序，因此，order by o.order_time 时，就先产生了 using temporary（使用临时表）。

前面我们知道 order_payment 的数据量只有11w，那么理所当然的order_payment是驱动表。所以，为了避免 using temporary，就必须使用order作为驱动表，这个时候STRAIGHT_JOIN关键字就来了。

STRAIGHT_JOIN 强指定驱动表

explain select o.id, op.money from `order` o straight_join order_payment op on o.id = op.order_id  where o.order_status = '4' order by o.order_time DESC;

分析结果，你会发现Using temporary 已经消失了。

最好解决掉Using filesort就很简单了，只需要给order_time增加索引就好了。

但是注意，在这个查询场景下，不能只对order_time一个字段增加索引，这样是避免不了 Using filesort的。因为innerDB索引的特性，B TREE 树的叶子节点存储的是索引列数据和主键ID。当只有一个order_time索引时，因为还有查询条件order_status在，所以无法使用到该索引

最佳索引方案

create index i_order_status_time on `order`(`order_status`,`order_time`)

这样B TREE树的叶子节点存储的会是索引列数据包含（order_status和order_time）和主键ID。该索引既能排序又能走查询。在上述条件查询中做到最佳性能。

最终SQL分析：

查询时候大幅提升。