mysql 5.5 join排序_MySQL的JOIN（五）：JOIN优化实践之排序

这篇博文讲述如何优化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