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优化实践之排序

posted @ 2017-09-18 10:56  付大石  阅读(15122)  评论(1编辑  收藏  举报