问题
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优化器对驱动表的选择发生了变化,进而出现索引失效的情况,所以没事最好多查查,关注一下这些情况。
(转载本站文章请注明作者和出处 MySQL Using temporary; Using filesort INNER JOIN优化 )