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

总结

《MySQL的JOIN》到此为止。

这系列博文讲述了JOIN的用法，JOIN的原理，以及在JOIN原理的基础上进行优化的手段。希望对大家有帮助吧：）

MySQL的JOIN（一）：用法

MySQL的JOIN（二）：JOIN原理

MySQL的JOIN（三）：JOIN优化实践之内循环的次数

MySQL的JOIN（四）：JOIN优化实践之快速匹配

MySQL的JOIN（五）：JOIN优化实践之排序