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

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

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

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

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

一、Multi-Range Read优化

在介绍本期主题时先来理解一个知识点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优化

快一个月没更文了，对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';

三、Block Nested-Loop Join算法优化

非常简单的优化就是在被驱动表上增加索引，这时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算法，从而晋升性能。

四、Hash join

大家还记得这幅图吧！上期文章中复现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系列十六》

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