sql join

Join 能用吗？

前提 ：如果join 的表有索引

如果没有索引：不建议jsoin，这样可能要扫描被驱动表很多次，会占用大量的系统资源。

你在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样。

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

Join 怎么优化？

1 - Multi-Range Read 优化

在介绍 join 语句的优化方案之前，我需要先和你介绍一个知识点，即：Multi-Range Read 优化 (MRR)。这个优化的主要目的是尽量使用顺序读盘。

意思就是说，根据索引查出来的数据，也就是B+树查出来的数据，是id，需要回表操作，如果一个一个回表，就多次使用b+树，但是如果我们把根据索引查出来的id，有序的存起来，再回表查的时候，因为b+树是有序的，就很好查，增加查询速度。

 

另外需要说明的是，如果你想要稳定地使用 MRR 优化的话，需要设置set optimizer_switch="mrr_cost_based=off"。（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用 MRR，把 mrr_cost_based 设置为 off，就是固定使用 MRR 了。）

 

从图 3 的 explain 结果中，我们可以看到 Extra 字段多了 Using MRR，表示的是用上了 MRR 优化。而且，由于我们在 read_rnd_buffer 中按照 id 做了排序，所以最后得到的结果集也是按照主键 id 递增顺序的。

MRR 能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

2 Batched Key Access

理解了 MRR 性能提升的原理，我们就能理解 MySQL 在 5.6 版本后开始引入的 Batched Key Access(BKA) 算法了。这个 BKA 算法，其实就是对 NLJ 算法的优化。

解释思考：就是说表t1 在链接表t2是一条一条的链接，就用不上MRR优化了，这时候需要加BKA优化，啥意思呢就是把表t1查出来的数据，放到join_buffer里就是放到一个缓存里，一起传给t2 这样就能用MRR优化了。

BKA 算法到底要怎么启用呢？

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

• 使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好，因为会走索引。
• 如果使用 join 语句的话，需要让小表做驱动表，也就是数据量小的链接大的。