这篇博文讲述如何优化JOIN查询带有排序的情况。大致分为对连接属性排序和对非连接属性排序两种情况。插入测试数据。
CREATE TABLE t1 (
id INT PRIMARY KEY AUTO_INCREMENT,
type INT
);
SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 10000 |
+----------+
CREATE TABLE t2 (
id INT PRIMARY KEY AUTO_INCREMENT,
type INT
);
SELECT COUNT(*) FROM t2;
+----------+
| COUNT(*) |
+----------+
| 100 |
+----------+
对连接属性进行排序
现要求对t1和t2做内连接，连接条件是t1.id=t2.id，并对连接属性id属性进行排序（MySQL为主键id建立了索引）。
有两种选择，方式一[...ORDER BY t1.id]，方式二[...ORDER BY t2.id]，选哪种呢？
首先我们找出驱动表和被驱动表，按照小表驱动大表的原则，大表是t1，小表是t2，所以t2是驱动表，t1是非驱动表，t2驱动t1。然后进行分析，如果我们使用方式一的话，MySQL会先对t1进行排序然后执行表连接算法，如果我们使用方式二的话，只能执行表连接算法后对结果集进行排序（extra：using temporary），效率必然低下。
所以，当对连接属性进行排序时，应当选择驱动表的属性作为排序表中的条件。
-- 对被驱动表字段进行排序
EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.id =t2.id ORDER BY t1.id;
+----+-------+--------+---------+------+---------------------------------+
| id | table | type | key | rows | Extra |
+----+-------+--------+---------+------+---------------------------------+
| 1 | t2 | ALL | NULL | 100 | Using temporary; Using filesort |
| 1 | t1 | eq_ref | PRIMARY | 1 | NULL |
+----+-------+--------+---------+------+---------------------------------+ -- 对驱动表字段进行排序，没有Using temporary，也没有Using filesort
EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.id =t2.id ORDER BY t2.id;
+----+-------+--------+---------+------+-------+
| id | table | type | key | rows | Extra |
+----+-------+--------+---------+------+-------+
| 1 | t2 | index | PRIMARY | 100 | NULL |
| 1 | t1 | eq_ref | PRIMARY | 1 | NULL |
+----+-------+--------+---------+------+-------+
对非连接属性进行排序
现要求对t1和t2做内连接，连接条件是t1.id=t2.id，并对非连接属性t1的type属性进行排序，[...ORDER BY t1.type]。
首先我们找出驱动表和被驱动表，按照小表驱动大表的原则，大表是t1，小表是t2，所以MySQL Optimizer会用t2驱动t1。现在我们要对t1的type属性进行排序，t1是被驱动表，必然导致对连接后结果集进行排序Using temporary（比Using filesort更严重）。所以，能不能不用MySQL Optimizer，用大表驱动小表呢？
有请STRAIGHT_JOIN！
EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.id =t2.id ORDER BY t1.type;
+----+-------+--------+---------+------+---------------------------------+
| id | table | type | key | rows | Extra |
+----+-------+--------+---------+------+---------------------------------+
| 1 | t2 | ALL | NULL | 100 | Using temporary; Using filesort |
| 1 | t1 | eq_ref | PRIMARY | 1 | NULL |
+----+-------+--------+---------+------+---------------------------------+ -- Using temporary没有了，但是大表驱动小表，导致内循环次数增加，实际开发中要从实际出发，
-- 对此作出权衡。
EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON t1.id =t2.id ORDER BY t1.type;
+----+-------+--------+---------+-------+----------------+
| id | table | type | key | rows | Extra |
+----+-------+--------+---------+-------+----------------+
| 1 | t1 | ALL | NULL | 10000 | Using filesort |
| 1 | t2 | eq_ref | PRIMARY | 1 | NULL |
+----+-------+--------+---------+-------+----------------+
最后在MySQL的JOIN（一）：用法那里挖了个坑，现在填上：INNER JOIN、JOIN、WHERE等值连接和STRAIGHT_JOIN都能表示内连接，那平时如何选择呢？一般情况下用INNER JOIN、JOIN或者WHERE等值连接，因为MySQL Optimizer会按照“小表驱动大表的策略”进行优化。当出现上述问题时，才考虑用STRAIGHT_JOIN
总结
《MySQL的JOIN》到此为止。
这系列博文讲述了JOIN的用法，JOIN的原理，以及在JOIN原理的基础上进行优化的手段。希望对大家有帮助吧：）