JOIN语句实现和优化

join语句会用到两个算法Index Nested-Loop和Block Nested—Loop;这里会介绍何时会用到这两种算法和优化方法

1.本文参考极客时间《MySQL实战45讲》，用于个人复习总结 2.为了实验测试，创建2个表t1,t2(表结构一样)，表结构在文章末尾

1.Index Nested-Loop Join

用explain直接分析sql查询结果，straight_join让t1为驱动表，t2为被驱动表；因为t2.a字段有建索引，所以去表t2查找是直接走索引。

1

explain select * from t1 straight_join t2 on t1.a = t2.a;

t1做为t2的驱动表，语句的执行流程是：

从表 t1 中读入一行数据 R;
从数据行 R 中，取出 a 字段到表 t2 里去查找;
取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分;
重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。

流程：

对驱动表 t1 做了全表扫描，这个过程需要扫描 100 行;
而对于每一行 R，根据 a 字段去表 t2 查找，走的是树搜索过程。由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行，也是总共扫描 100 行;
所以，整个执行流程，总扫描行数是 200。

2.Block Nested—Loop Join（被驱动表用不上索引）

用explain直接分析sql查询结果，由于表t2.b字段没有索引，每次去表t2匹配都要做一次全表扫描。

1

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

被驱动表上没有可用的索引，算法的流程是这样的:

把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select * ，因此是把整个表 t1 放入了内存;
扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。 执行过程就变成了:

扫描表 t1，顺序读取数据行放入 join_buffer 中，放完第 88 行 join_buffer 满了，继续第 2 步;
扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回;
清空 join_buffer;
继续扫描表 t1，顺序读取最后的 12 行数据放入 join_buffer 中，继续执行第 2 步。

3.到底要不要用join？

如果可以使用被驱动表的索引，join 语句还是有其优势的;
不能使用被驱动表的索引，只能使用 Block Nested-Loop Join 算法，这样的语句就尽量不要使用;
在使用 join 的时候，应该让小表做驱动表。

4.join优化

Multi-Range Read 优化：（MRR）尽量使用顺序读盘；磁盘的读比较接近顺序读，能够提升读性能；

1.NJL算法优化

MySQL 在 5.6 版本后开始引入的 Batched Key Acess(BKA) 算法了,其实就是对 NLJ 算法的优化。

1

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

2.BNL转BKA

优化思路：

把表 t2 中满足条件的数据放在临时表 tmp_t 中;
为了让 join 使用 BKA 算法，给临时表 tmp_t 的字段 b 加上索引;
让表 t1 和 tmp_t 做 join 操作。

1
2
3


create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb; 
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

总结

BKA 优化是 MySQL 已经内置支持的，建议你默认使用;
BNL 算法效率低，建议你都尽量转成 BKA 算法。优化的方向就是给被驱动表的关联字段加上索引;
基于临时表的改进方案，对于能够提前过滤出小数据的 join 语句来说，效果还是很好的;
MySQL 目前的版本还不支持 hash join，但你可以配合应用端自己模拟出来，理论上效果要好于临时表的方案。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24


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;
drop procedure idata; 
delimiter ;;
create procedure idata() 
begin
declare i int; 
set i=1; 
while(i<=1000)do
   insert into t2 values(i, i, i);
   set i=i+1; 
end while;
end;; 
delimiter ; 
call idata();

create table t1 like t2;
insert into t1 (select * from t2 where id<=100);

Contents