关于mysql:MySQL对JOIN做了那些不为人知的优化

大家好，我是咔咔 不期速成，日拱一卒

通过上期文章晓得了在 MySQL 中存在三种 join 的算法，别离为NLJ、BNLJ、BNL，总结来说分为索引嵌套循环连贯、缓存块嵌套循环连贯、粗犷循环连贯。

另外还晓得了一个新的概念join_buffer，作用就是把关联表的数据全副读入 join_buffer 中，而后从 join_buffer 中一行一行的拿数据去被驱动表中查问。因为是在内存中获取数据，因而效率还是会有所晋升。

同时在上期文章中遇到了一个生疏的概念 hash_join，在上期中没有具体阐明，本期会进行详述。

在介绍本期主题时先来理解一个知识点Multi-Range Read，次要的作用是尽量让程序读盘，在任何畛域只有是有程序的都会有肯定的性能晋升。

比方 MySQL 的索引，当初你应该晓得索引天生具备有序性从而防止服务器对数据再次排序和建设长期表的问题。

接下来应用一个案例来实操一下这个优化是怎么做的

创立 join_test1、join_test2 两张表

CREATE TABLE `join_test1` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `a` int(11) unsigned NOT NULL,
 `b` int(11) unsigned NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `join_test2` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `a` int(11) unsigned NOT NULL,
 `b` int(11) unsigned NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

给两张表增加一些数据，用于案例演示

drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into join_test1 (a,b) values (1001-i, i);
    set i=i+1;
  end while;
  
  set i=1;
  while(i<=1000000)do
    insert into join_test2 (a,b)  values (i, i);
    set i=i+1;
  end while;

end;;
delimiter ;
call idata();

表 join_test1 的字段 a 上存在索引的，那么在查问时就会应用该索引。

执行流程大抵为获取到字段 a 所有的值，而后依据 a 的值一行一行的进行回表到主键索引上获取数据

当初的状况是如果随着 a 的值递增程序查问的话，id 的值就会变相的为顺叙，尽管看起来是依据主键 ID 间断顺叙的，但在生产环境下必定不是间断的，就会造成随机拜访，那就必定会造成性能变差。

为什么说随机拜访会影响性能？

MySQL 的索引天生具备有序性，同时 MySQL 也同样借鉴了局部性原理，局部性原理是数据和程序都默认有汇集成群的偏向，在拜访到一行数据后，会有极大可能性再次拜访到这条数据或这条数据相邻的数据。

当初你应该晓得了 MySQL 在读取数据时并不是只读查问的数据，默认会读取 16kb 的数据，这个值是依据 innodb_page_size 决定的。

因而程序查问是十分快的，是因为不必每次都通过执行器获取数据，而是间接在内存中获取，但若拜访变为随机性就会每次通过执行器进行获取数据，所以这才是性能变差的起因。

MRR 的作用

说了这么多当初你应该晓得了 MRR 的作用就是把查问变为主键 ID 的递增查问，对磁盘的读尽可能的靠近程序读，就能够晋升性能。

因而，执行语句的执行流程就会变成这样

先依据索 a，获取到所有满足条件的数据，并且将主键 id 的值放入 read_rnd_buffer 中
在 read_rnd_buffer 中把 id 的值进行正序排序
再依据排序后得主键 ID 值，顺次到主键索引上获取数据，并返回后果集

如何开启 read_rnd_buffer

read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数管制的，默认值为 256kb，但你要晓得的是对于 MRR 的优化在优化器的判断策略中会更偏向于不应用，如果要应用则须要进行配置批改即可。

set optimizer_switch="mrr_cost_based=off"

mrr 默认值

read_rnd_buffer 存不下怎么办？

回顾下在上期中提到的 join_buffer 不够用是怎么解决的，会把上次读取的数据从 buffer 中清空，再放入剩下的数据，在 MySQL 中对于存储后果集的 buffer 内存不够状况下大多数都是这么解决的。

应用了 read_rnd_buffer 后的 SQL 执行流程就变成了这样

explain 的结果显示

留神点

假如当初把查问范畴扩充，看一下会有什么变动

能够看到当把范畴扩充至靠近全表数据时，会不再应用索引 a 从而进行了全表扫描，也就无奈再应用 mrr 优化了

因而想要应用 MRR 进行晋升性能是基于两个十分重要的点，一个是在索引上进行范畴查问，另一个就是必须能应用上索引，当然这个索引要是范畴查问的列

快一个月没更文了，对 Nested-Loop Join 的算法还能回顾多少，SQL 的执行流程大抵如下：

从 join_test1 表读取一行数据 R
从 R 中取 id 字段到表 join_test2 去查找索引 a，并通过主键 ID 获取到满足的行
取出 join_test2 中满足条件的行，跟 R 组成一行
反复前三个步骤，直到表 join_test1 满足条件的数据扫描完结

NLJ 算法的逻辑就是从驱动表取一行数据后就间接到被驱动表中做 join 操作，对于驱动表来说就变成了每次都匹配一个值，这时就不满足 MRR 优化的条件了。

通过上期文章，当初你应该晓得了 join_buffer 在 BNL 算法中的作用，但在 NLJ 算法中并没有应用。

那想方法把驱动表的数据批量传给被驱动表进行 join 操作不就行了？

没错，MySQL 团队在 5.6 版本引入了此计划，在驱动表中取出一部分数据，放到长期内存，这个长期内存就是上期的 join_buffer。

那么执行流程图就会变成这样

这里须要留神没有把索引 a 在 read_rnd_buffer 中的流程画进去，如果不了解就到上文去看那副图哈！

上图中，咱们仍然查问了 1000 条数据，那么 join_buffer 就会存着 1000 条数据，如果存不下就会分段进行，直到执行完结。

对于 NLJ 算法的优化官网也给起来了一个名为Batched Key Access

BKA 算法的启用

既然要应用 MRR 优化，那就要开启 MRR，开启 MRR 的同时还要开启 batched_key_access=on 即可

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

非常简单的优化就是在被驱动表上增加索引，这时 BNL 的算法就自然而然的变为 BKA 算法了

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

这条 SQL 在 join_test2 上只查问了 2000 行数据，如果你的 MySQL 机器对内存不那么看重的话间接给字段 b 加个索引即可。

反之，就须要另辟奇径了

再来温习下 BNL 算法的执行流程

取出 join_test1 的所有数据，存储 join_buffer 中
扫描 join_test2 用每行数据跟 join_buffer 中的数据进行比照，不满足跳过，满足存储后果集

因为被驱动表字段 b 是没有索引的，因而从 join_buffer 中读取进去的每条数据都要对 join_test2 进行全表扫描。

案例中 join_test2 表共 100W 数据，那么须要扫描的行数就是 1000*100W = 10 亿次，只须要 2000 条数据却要执行 10 亿次，这个性能可想而知。

这时，咱们就能够应用奇径 长期表 来解决这个问题，实现思路大抵如下

先把 join_test2 中满足条件的数据寄存在长期表中 tmp_join_test2 中
此时长期表的数据只有条件范畴的 2000 数据，因而是齐全能够给字段 b 增加索引的
最初再让 join_buffer 跟 tmp_join_test2 做 join 操作

对应的 SQL 操作如下

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

扫描行数

insert 是对表 join_test2 进行的全表扫描，此时扫描行数为 100W 行

join_test1 进行全表扫描一次扫描行数为 1000 行

每次 join 操作是一条数据，共计 1000 次，扫描行数为 1000 行

应用了长期表后总体扫描行数从 10 亿次到了 100W+2000 次，执行查问的后果返回预计都不到一秒工夫。

总结

不论是应用 BKA 算法还是应用长期表都有一个共同点，那就是让被驱动表上能用上索引来被动触发 BKA 算法，从而晋升性能。

大家还记得这幅图吧！上期文章中复现 Block Nested-Loop Join 算法呢！后果返回了一个 hash_join，上期并没有阐明。

因为 hash_join 算法是在 MySQL8.0.18 才有的

hash_join 失效的前提是被驱动表 join 的字段没有索引，在 MySQL8.0.18 中还有一个束缚就是条件对等，例如案例中的join_test1.b=tmp_join_test2.b

但在 8.0.20 中勾销了条件对等的束缚，并全面反对non-equi-join，Semijoin，Antijoin，Left outer join/Right outer join

其实 hash_join 算法的实现原理很简略

驱动表中的 join 字段进行计算 hash 值
在内存中创立一个 hash_table，把驱动表所有的 hash 值寄存进去
获取被驱动表中满足条件的数据，例如 join_test2 中的select * from join_test2 where b>=1 and b<=20002000 行数据
把这 2000 行数据，一行一行的跟 hash_table 中的数据进行比照，条件满足的数据作为后果集进行返回

能够看到 hash_join 算法的扫描行数跟长期表大差不差，那么为什么 MySQL 会默认应用 hash_join 这种算法呢？这个问题就要留给大家去深究了

本期次要分享了 NLJ、BNJ 的算法优化

在这些优化中，hash_join 在 MySQL8.0.18 中曾经内置反对了，但低版本的还是默认为 BKA 算法

倡议给被驱动表须要 join 字段加上索引，把 BNL 算法转为 BKA 或者 hash_join 算法

同时还给大家提供了一个长期表的计划，长期表在开发过程中是非常容易疏忽的一个优化点，能够在适当的环境下学会应用长期表

死磕 MySQL 系列总目录

重重封闭，让你一条数据都拿不到《死磕 MySQL 系列十三》

闯祸了，生成环境执行了 DDL 操作《死磕 MySQL 系列十四》

聊聊 MySQL 的加锁规定《死磕 MySQL 系列十五》

为什么不让用 join？《死磕 MySQL 系列十六》

保持学习、保持写作、保持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮忙，我是咔咔，下期见。

关于mysql:MySQL对JOIN做了那些不为人知的优化

一、Multi-Range Read 优化

二、Nested-Loop Join 优化

三、Block Nested-Loop Join 算法优化

四、Hash join

五、总结

举荐浏览