join
Index Nested-Loop Join
Simple Nested-Loop Join
Block Nested-Loop Join
join_buffer_size
Multi-Range Read
read_rnd_buffer_size
set optimizer_switch="mrr_cost_based=off"
Batched Key Access
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
hash join
ads:
关注以下公众号查看更多文章
先创建两个表 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;
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)
这两个表都有一个主键索引 id 和一个索引 a,字段 b 上无索引。存储过程 idata() 往表 t2 里插入了 1000 行数据,在表 t1 里插入的是 100 行数据。
Join 的两种算法
Index Nested-Loop Join
select * from t1 straight_join t2 on (t1.a=t2.a);
这条语句的 explain 结果
在这条语句里,被驱动表 t2 的字段 a 上有索引,join 过程用上了这个索引,因此这个语句的执行流程是这样的:
- 从表 t1 中读入一行数据 R;
- 从数据行 R 中,取出 a 字段到表 t2 里去查找;
- 取出表 t2 中满足条件的行,跟 R 组成一行,作为结果集的一部分;
- 重复执行步骤 1 到 3,直到表 t1 的末尾循环结束。
在形式上,这个过程就跟我们写程序时的嵌套查询类似,并且可以用上被驱动表的索引,所以我们称之为“Index Nested-Loop Join”,简称 NLJ。
在这个 join 语句执行过程中,驱动表是走全表扫描,而被驱动表是走树搜索。整个执行过程,近似复杂度是 N + N*2*log2M。显然,N 对扫描行数的影响更大,因此应该让小表来做驱动表。
Block Nested-Loop Join
select * from t1 straight_join t2 on (t1.a=t2.b);
由于表 t2 的字段 b 上没有索引,每次到 t2 去匹配的时候,就要做一次全表扫描。
被驱动表上没有可用的索引,算法的流程是这样的:
- 把表 t1 的数据读入线程内存 join_buffer 中,由于我们这个语句中写的是 select *,因此是把整个表 t1 放入了内存;
- 扫描表 t2,把表 t2 中的每一行取出来,跟 join_buffer 中的数据做对比,满足 join 条件的,作为结果集的一部分返回。
这条 SQL 语句的 explain 结果如下所示
在这个过程中,对表 t1 和 t2 都做了一次全表扫描,因此总的扫描行数是 1100。由于 join_buffer 是以无序数组的方式组织的,因此对表 t2 中的每一行,都要做 100 次判断,总共需要在内存中做的判断次数是:100*1000=10 万次。 Block Nested-Loop Join 算法的这 10 万次判断是内存操作,速度上会快很多,性能也更好。
join_buffer 的大小是由参数 join_buffer_size 设定的,默认值是 256k。如果放不下表 t1 的所有数据话,策略很简单,就是分段放。
执行过程就变成了:
- 扫描表 t1,顺序读取数据行放入 join_buffer 中,放完第 88 行 join_buffer 满了,继续第 2 步;
- 扫描表 t2,把 t2 中的每一行取出来,跟 join_buffer 中的数据做对比,满足 join 条件的,作为结果集的一部分返回;
- 清空 join_buffer;
- 继续扫描表 t1,顺序读取最后的 12 行数据放入 join_buffer 中,继续执行第 2 步。
这种 join 方式应该让小表当驱动表。join_buffer_size 越大,一次可以放入的行越多,分成的段数也就越少,对被驱动表的全表扫描次数就越少。如果你的 join 语句很慢,就把 join_buffer_size 改大。
结论:
- 如果可以使用 Index Nested-Loop Join 算法,也就是说可以用上被驱动表上的索引,其实是没问题的;
- 如果使用 Block Nested-Loop Join 算法,扫描行数就会过多。尤其是在大表上的 join 操作,这样可能要扫描被驱动表很多次,会占用大量的系统资源。所以这种 join 尽量不要用。
- 应该使用小表做驱动表。在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与 join 的各个字段的总数据量,数据量小的那个表,就是“小表”,应该作为驱动表。
Join 语句的优化
Multi-Range Read 优化
Multi-Range Read 优化 (MRR),这个优化的主要目的是尽量使用顺序读盘。
回表
回表是指,InnoDB 在普通索引 a 上查到主键 id 的值后,再根据一个个主键 id 的值到主键索引上去查整行数据的过程。
select * from t1 where a>=1 and a<=100;
主键索引是一棵 B+ 树,在这棵树上,每次只能根据一个主键 id 查到一行数据。因此,回表肯定是一行行搜索主键索引的,
如果随着 a 的值递增顺序查询的话,id 的值就变成随机的,那么就会出现随机访问,性能相对较差。虽然“按行查”这个机制不能改,但是调整查询的顺序,还是能够加速的。
因为大多数的数据都是按照主键递增顺序插入得到的,所以我们可以认为,如果按照主键的递增顺序查询的话,对磁盘的读比较接近顺序读,能够提升读性能。
就是 MRR 优化的设计思路。此时,语句的执行流程变成了这样:
- 根据索引 a,定位到满足条件的记录,将 id 值放入 read_rnd_buffer 中 ;
- 将 read_rnd_buffer 中的 id 进行递增排序;
- 排序后的 id 数组,依次到主键 id 索引中查记录,并作为结果返回。
这里,read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数控制的。如果步骤 1 中,read_rnd_buffer 放满了,就会先执行完步骤 2 和 3,然后清空 read_rnd_buffer。之后继续找索引 a 的下个记录,并继续循环。
如果你想要稳定地使用 MRR 优化的话,需要设置set optimizer_switch="mrr_cost_based=off"
explain 结果中,可以看到 Extra 字段多了 Using MRR,表示的是用上了 MRR 优化。而且,由于在 read_rnd_buffer 中按照 id 做了排序,所以最后得到的结果集也是按照主键 id 递增顺序的。MRR 能够提升性能的核心在于,这条查询语句在索引 a 上做的是一个范围查询(也就是说,这是一个多值查询),可以得到足够多的主键 id。这样通过排序以后,再去主键索引查数据,才能体现出“顺序性”的优势。
Batched Key Access
MySQL 在 5.6 版本后开始引入的 Batched Key Access(BKA) 算法了。这个 BKA 算法,其实就是对 NLJ 算法的优化。NLJ 算法执行的逻辑是:从驱动表 t1,一行行地取出 a 的值,再到被驱动表 t2 去做 join。也就是说,对于表 t2 来说,每次都是匹配一个值。这时,MRR 的优势就用不上了。
那怎么才能一次性地多传些值给表 t2 呢?方法就是,从表 t1 里一次性地多拿些行出来,一起传给表 t2。把表 t1 的数据取出来一部分,放入 join_buffer 。
如果要使用 BKA 优化算法的话,你需要在执行 SQL 语句之前,先设置
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
其中,前两个参数的作用是要启用 MRR。这么做的原因是,BKA 算法的优化要依赖于 MRR。
总体来看,不论是在原表上加索引,还是用有索引的临时表,我们的思路都是让 join 语句能够用上被驱动表上的索引,来触发 BKA 算法,提升查询性能。
推荐阅读