using filesort 和 using temporary 的使用场景?

问题源自这篇文章:MySQL · 答疑释惑· using filesort VS using temporary

文章中:

create table t1(    
id int, col1 int, col2 varchar(10),
key(id, col1));

create table t2(
id int, col1 int, col2 varchar(10),
key(col1));

case2:

mysql> explain select * from t1 force index(id), t2 where t1.id=1 and t1.col1 = t2.col2 order by t1.col2;
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | t1    | ref  | id            | id   | 5       | const |    1 | Using where; Using filesort |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL  |    1 | Using where                 |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+

case3:

mysql> explain select * from t1 force index(id), t2 where t1.id=1 and t1.col1 = t2.col2 order by t2.col1 ;
+----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                                        |
+----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------+
|  1 | SIMPLE      | t1    | ref  | id            | id   | 5       | const |    1 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL  |    1 | Using where; Using join buffer               |
+----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------+

文章结论:

case2: order by谓词,是在第一个表t1上完成,所以只需要在t1表上使用filesort,然后排序后的结果集join t2表。
case 3: order by的字段在t2表上,所以需要把t1,t2表join的结果保存到temporary表上,然后对临时表进行filesort,最后输出结果。

我的问题:

为什么case2 case3都需要做join,为什么case3需要保存到temporary表,而case2不需要呢??
请大佬解惑。

阅读 4.5k
1 个回答

对于case2,没有使用join,而是先筛选出t1.id=1的所有记录,然后排序,最后拿着这些记录的col1,去t2查t1.col1 = t2.col2的记录后返回满足的记录的id,最后一起返回
对于case3,因为必须要用到t2的col1进行排序,而你走的是t1的索引,没有t2的信息,只能使用临时表保存了t2.col1,才能排序
另外说一句,对于case3,如果你没有强制走t1.id的索引,那么根据实际数据量的情况,优化器是可能走t2.id的索引,那么这个时候就和case2一样了

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题