MySQL实战：join 语句怎么优化？

Simple Nested-Loop Join

我们来看一下当进行 join 操作时，mysql是如何工作的。常见的 join 方式有哪些？

如图，当我们进行连接操作时，左边的表是驱动表，右边的表是被驱动表

Simple Nested-Loop Join 这种连接操作是从驱动表中取出一条记录然后逐条匹配被驱动表的记录，如果条件匹配则将结果返回。然后接着取驱动表的下一条记录进行匹配，直到驱动表的数据全都匹配完毕

因为每次从驱动表取数据比较耗时，所以MySQL并没有采用这种算法来进行连接操作

Block Nested-Loop Join

既然每次从驱动表取数据比较耗时，那我们每次从驱动表取一批数据放到内存中，然后对这一批数据进行匹配操作。这批数据匹配完毕，再从驱动表中取一批数据放到内存中，直到驱动表的数据全都匹配完毕

批量取数据能减少很多IO操作，因此执行效率比较高，这种连接操作也被MySQL采用

对了，这块内存在MySQ中有一个专有的名词，叫做 join buffer，我们可以执行如下语句查看 join buffer 的大小

show variables like '%join_buffer%'

把我们之前用的 single_table 表搬出来，基于 single_table 表创建2个表，每个表插入1w条随机记录

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

create table t1 like single_table;
create table t2 like single_table;

如果直接使用 join 语句，MySQL优化器可能会选择表 t1 或者 t2 作为驱动表，这样会影响我们分析sql语句的过程，所以我们用 straight_join 让mysql使用固定的连接方式执行查询

select * from t1 straight_join t2 on (t1.common_field = t2.common_field)

运行时间为0.035s

执行计划如下

在Extra列中看到了 Using join buffer ，说明连接操作是基于 Block Nested-Loop Join 算法

Index Nested-Loop Join

了解了 Block Nested-Loop Join 算法之后，可以看到驱动表的每条记录会把被驱动表的所有记录都匹配一遍，非常耗时，能不能提高一下被驱动表匹配的效率呢？

估计这种算法你也想到了，就是给被驱动表连接的列加上索引，这样匹配的过程就非常快，如图所示

我们来看一下基于索引列进行连接执行查询有多快？

select * from t1 straight_join t2 on (t1.id = t2.id)

执行时间为0.001秒，可以看到比基于普通的列进行连接快了不止一个档次

执行计划如下

驱动表的记录并不是所有列都会被放到 join buffer，只有查询列表中的列和过滤条件中的列才会被放入 join buffer，因此我们不要把 * 作为查询列表，只需要把我们关心的列放到查询列表就好了，这样可以在 join buffer 中放置更多的记录