MySQL使用join的正确姿势

使用join的正确姿势：

1. 使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；

2. 如果使用 join 语句的话，需要让小表做驱动表。

“

如果不能使用被驱动表的索引的话，查询效率很低，慎用。比如两张表t1和t2各100行。如果不使用索引，MySQL需要扫描100*100共10000行；而使用索引的情况下，t1全表扫描100行；t2扫描100次，每次扫描1行，共扫描200行。

”

什么是驱动表

MySQL先从哪个表检索，这个表就是驱动表

join语句涉及MySQL中的三种算法

分别是NLJ（Nested-Loop Join）、Simple Nested-Loop Join、Block Nested-Loop Join（BNL）。

• NLJ: 在能够使用被驱动表索引的情况下，MySQL会选择NLJ算法。

• BNL：当无法使用驱动表的索引，MySQL会把表一次性或分段加载到join buffer中，然后再与被驱动表中的数据匹配。这就是BNL算法。

• Simple Nested-Loop Join: 他的执行逻辑与BNL很相似，但是Simple Nested-Loop Join不会把表数据加载到join buffer中，而是buffer pool中，这会间接对MySQL性能产生很大的影响。

Example

有两个表t1和t2，

CREATE TABLE `t2` (
 `id` int(11) NOT NULL,
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `a` (`a`)
) ENGINE=InnoDB;

下面的join语句的一般执行流程是：

• select * from t1 straight_join t2 on (t1.a=t2.a);

（NLJ）

1. 从表 t1 中读入一行数据 R；

2. 从数据行 R 中，取出 a 字段到表 t2 里去查找；

3. 取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分；

4. 重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。

• select * from t1 straight_join t2 on (t1.a=t2.b);

（BLJ）

“

把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存；

”

1. select * from t1 straight_join t2 on (t1.a=t2.b);

2. 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

即将发布的文章

• 大公司为什么不让使用join

关注公众号，回复“MySQL”领取高清PDF《高性能MySQL》