MySQL Using temporary; Using filesort INNER JOIN优化

问题

join时出现 Using temporary、Using filesort，sql执行太慢

重现

通过explain，如下：

mysql> explain select d.* from sx_t_atv_drawprize_log d join wx_report3_retailer r on  d.userid = r.userId order by d.id  limit 0,100;
+----+-------------+-------+-------+---------------+-------------+---------+--------------------+-------+-----------------------------------------------------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref                | rows  | Extra                                                     |
+----+-------------+-------+-------+---------------+-------------+---------+--------------------+-------+-----------------------------------------------------------+
|  1 | SIMPLE      | r     | index | userIdIndex   | userIdIndex | 5       | NULL               | 15764 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | d     | ref   | userIdIndex   | userIdIndex | 9       | yunzhi_gd.r.userId |     4 | Using index condition                                     |
+----+-------------+-------+-------+---------------+-------------+---------+--------------------+-------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

下面给出优化后的SQL，唯一的变化就是把连接方式改成了「STRAIGHT_JOIN」：

mysql> explain select d.* from sx_t_atv_drawprize_log d STRAIGHT_JOIN wx_report3_retailer r on  d.userid = r.userId order by d.id  limit 0,100;
+----+-------------+-------+-------+---------------+-------------+---------+--------------------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref                | rows | Extra                    |
+----+-------------+-------+-------+---------------+-------------+---------+--------------------+------+--------------------------+
|  1 | SIMPLE      | d     | index | userIdIndex   | PRIMARY     | 8       | NULL               |  100 | Using where              |
|  1 | SIMPLE      | r     | ref   | userIdIndex   | userIdIndex | 5       | yunzhi_gd.d.userid |    1 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------------+---------+--------------------+------+--------------------------+
2 rows in set (0.00 sec)

对比优化前后两次EXPLAIN的结果来看，没有了 Using temporary、Using filesort，sql执行必然很快。

提醒：注意两次EXPLAIN结果中各个表出现的先后顺序，稍后会解释。

分析

对第一条SQL而言，为什么MySQL优化器选择了一个耗时的执行方案？对第二条SQL而言，为什么把连接方式改成STRAIGHT_JOIN之后就提升了性能？

这一切还得从MySQL对多表连接的处理方式说起，首先MySQL优化器要确定以谁为驱动表，也就是说以哪个表为基准，在处理此类问题时，MySQL优化器采用了简单粗暴的解决方法：哪个表的结果集小，就以哪个表为驱动表，当然MySQL优化器实际的处理方式会复杂许多，具体可以参考：MySQL优化器如何选择索引和JOIN顺序。

说明：在EXPLAIN结果中，第一行出现的表就是驱动表。

继续sx_t_atv_drawprize_log连接wx_report3_retailer的例子，MySQL优化器有如下两个选择，分别是：

以sx_t_atv_drawprize_log为驱动表
以wx_report3_retailer为驱动表
该例中，wx_report3_retailer过滤的结果集更小，所以MySQL优化器选择它作为驱动表，可悲催的是我们还需要以sx_t_atv_drawprize_log表中的id字段来排序，也就是说排序字段不在驱动表里，于是乎不可避免的出现了Using filesort，甚至Using temporary。

知道了来龙去脉，优化起来就容易了，要尽可能的保证排序字段在驱动表中，所以必须以post为驱动表，于是乎必须借助「STRAIGHT_JOIN」强制连接顺序。

实际上在某些特殊情况里，排序字段可以不在驱动表里，比如驱动表结果集只有一行记录，并且在连接其它表时，索引除了连接字段，还包含了排序字段，此时连接表后，索引中的数据本身自然就是排好序的。

既然聊到这里顺带说点题外话，大家可能会遇到类似下面的问题：原本运行良好的查询语句，过了一段时间后，可能会突然变得很糟糕。一个很大可能的原因就是数据分布情况发生了变化，从而导致MySQL优化器对驱动表的选择发生了变化，进而出现索引失效的情况，所以没事最好多查查，关注一下这些情况。