mysql中的join是如何执行的

青青子衿658
304 阅读12分钟

表结构

t1 100条数据,t2 1000条数据 两个表都有id(主键索引),a(索引),b(没有索引)字段

select t1.*,t2.a from t1 join t2 on (t1.a=t2.a);

执行流程1:

  1. 驱动表确认后(t1),从表t1中读入一行数据 R；
  2. 从数据行R中，取出a字段到表t2里去查找；
  3. 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
  4. 重复执行步骤1到3，直到表t1的末尾循环结束 在形式上，这个过程就跟我们写程序时的嵌套查询(for)类似,并且可以用上被驱动表的索引,所以我们称之为 IndexNested-Loop Join，简称NLJ
  • 对驱动表t1做了全表扫描，这个过程需要扫描100行；
  • 而对于每一行R，根据a字段去表t2查找，走的是树搜索过程。由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行，也是总共扫描100行；
  • 所以，整个执行流程，总扫描行数是200 如果sql改为(指定驱动表):
select * from t1 straight_join t2 on (t1.a=t2.a) where t1.a=1 and t1.b>50 and t2.b>50;

执行流程2:

  1. 驱动表确认后(t1),根据索引条件t1.a=1在引擎层过滤数据,由于t1.b没有索引,所以需要在引擎层进行回表(得到结果集R),将R传给服务层,在服务层过滤t1.b>50,得到结果集R1,然后按执行流程1执行后续操作(表 t1 中通过 WHERE 条件过滤出的数据会在表 t2 对应的索引上进行一一查询。如果驱动表 t1 的数据量不大，上述算法非常高效)

  2. t2表的where条件和t1的处理方式不同,被驱动表的where条件会放到最后进行数据过滤(不管条件是否有索引)。此处被驱动表的关联条件上是有索引的,被驱动表的where条件先过滤和后过滤没有影响(一共只需要扫描100行),如果被驱动表的关联条件上(t2.a)没有索引查询效率就有差别了(被驱动表的where条件放到on中提前过滤或放到where中最后过滤)

在来看个例子:

SELECT ... FROM R INNER JOIN S 
ON R.x = S.x 
WHERE R.y = ? AND S.z = ?

上面这条 SQL 语句是对表 R 和表 S 进行 INNER JOIN，其中关联的列是 x，WHERE 过滤条件分别过滤表 R 中的列 y 和表 S 中的列 z。那么这种情况下可以有以下两种选择(R为驱动表或者S为驱动表)

优化器一般认为，通过索引进行查询的效率都一样，所以 Nested Loop Join 算法主要要求驱动表的数量要尽可能少。

所以，如果 WHERE R.y = ?过滤出的数据少，那么这条 SQL 语句会先使用表 R 上列 y 上的索引，筛选出数据，然后再使用表 S 上列 x 的索引进行关联，最后再通过 WHERE S.z = ?过滤出最后数据。

MySQL在5.6版本后开始引入的Batched Key Acess(BKA)算法了。这个BKA算法，其实就是对NLJ算法的优化。
该算法通过常见的空间换时间，随机I/O转顺序I/O，以此来极大的提升Join的性能。

MRR：
MRR 的全称是 Multi-Range Read Optimization，是优化器将随机 IO 转化为顺序 IO 以降低查询过程中 
IO 开销的一种手段，这对IO-bound类型的SQL语句性能带来极大的提升，适用于range、ref、eq_ref类型的查询

MRR优化的几个好处：
1.使数据访问有随机变为顺序，查询辅助索引是，首先把查询结果按照主键进行排序，按照主键的顺序进行书签查找
2.减少缓冲池中页被替换的次数
3.批量处理对键值的操作

具体流程：
1.通过二级索引，定位到满足条件的记录，将id放入read_rnd_buffer中
2.将read_rnd_buffer中的id进行递增排序
3.排序后的id数组，依次到主键id索引中查记录，并作为结果返回
这里，read_rnd_buffer的大小是由read_rnd_buffer_size参数控制的。如果步骤1中，read_rnd_buffer放
满了，就会先执行完步骤2和3，然后清空read_rnd_buffer。之后继续找二级索引的下个记录，并继续循环

在不使用 MRR 时，优化器需要根据二级索引返回的记录来进行“回表”，这个过程一般会有较多的随机IO，
使用MRR时优化器将二级索引随机的 IO 进行排序，转化为主键的有序排列，从而实现了随机 IO 到顺
序 IO 的转化，提升性能(随机主键不在同一个page里时会导致多次IO和随机读)。

使用MRR优化(MySQL5.6之后)，先根据where条件中的辅助索引获取辅助索引与主键的集合，再将结果集放在
buffer(read_rnd_buffer_size 直到buffer满了)，然后对结果集按照pk_column排序，得到有序的结果集
rest_sort。最后利用已经排序过的结果集，访问表中的数据，此时是顺序IO。即MySQL 将根据辅助索引获取
的结果集根据主键进行排序，将无序化为有序，可以用主键顺序访问基表，将随机读转化为顺序读，多页数据
记录可一次性读入或根据此次的主键范围分次读入，减少IO操作，提高查询效率。

BKA：
Batched Key Access (BKA)  提高表join性能的算法。当被驱动表能够使用索引时，驱动表就先排好顺序，然后
再去检索被驱动表，听起来和MRR类似，实际上MRR也可以想象成 二级索引和 primary key的join
如果被驱动表上没有索引，则使用老版本的BNL策略(BLOCK Nested-loop)

对于多表join语句，当MySQL使用索引访问第二个join表的时候，使用一个join buffer来收集第一个操作对
象生成的相关列值。BKA构建好key后，批量传给引擎层做索引查找。key是通过MRR接口提交给引擎的（mrr目
的是较为顺序）MRR使得查询更有效率。
具体流程：
1.BKA使用join buffer保存由join的第一个操作产生的符合条件的数据
2.BKA算法构建key来访问被连接的表，并批量使用MRR接口提交keys到数据库存储引擎去查找
3.提交keys之后，MRR使用最佳的方式来获取行并反馈给BKA

BKA主要是指在被驱动表上有索引可以利用，那么就在行提交给被驱动表之前，对这些行按照索引字段进行
排序，因此减少了随机IO，所以要想通过BKA算法来提高性能，不但需要确保联接的列参与match的操作（联
接的列可以是唯一索引或者普通索引，但不能是主键），还要有非主键列的search(非覆盖索引)如果直接覆盖索
引就无需回表,何来随机IO转顺序IO一说

如果被驱动表的关联字段是主键，那么表中的记录访问都是比较有序的，但是如果联接的列是非主键索引，那么对于表中记录的回表访问可能就是非常离散的(个人理解这句话的意思:BKA构建好的key本来就是有序的,如果key是主键,则无需在通过MRR接口,直接通过key查询就是顺序IO,如果key是二级索引,虽然key是有序但对应的主键就是乱序了,此时需要将key传给MRR接口,MRR通过收到的Key，根据其对应的主键(ROWID)进行排序，然后再通过主键进行回表查询 随机IO转顺序IO)

通常情况下,因为查询数据量较小、语句相对简单、大多使用索引连接表之间的数据。这种情况下，优化器大多会用 NLJ 或者 BKA 算法，而当业务中的查询数据量较大、关联表的数量非常多、两张表之间连接条件没有索引、二级索引回表(数据量比较大时)的时候,这两种算法就不太适合了,所以会用 Hash Join 算法，直接扫描全表效率会更高。MySQL 8.0 版本及以后增加了 Hash Join 算法

Hash Join会扫描关联的两张表：

  • 首先会在扫描驱动表的过程中创建一张哈希表；
  • 接着扫描第二张表时，会在哈希表中搜索每条关联的记录，如果找到就返回记录。

Hash Join 选择驱动表和 Nested Loop Join 算法大致一样，都是较小的表作为驱动表。如果驱动表比较大，创建的哈希表超过了内存的大小，MySQL 会自动把结果转储到磁盘。

接下来，我们再看看被驱动表用不上索引的情况
Simple Nested-Loop Join 现在，我们把SQL语句改成这样:

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

由于表t2的字段b上没有索引，每次到t2去匹配的时候，就要做一次全表扫描。
这个SQL请求就要扫描表t2多达100次，总共扫描100*1000=10万行。

这个算法基本用不到了,来看下这个算法的优化算法 Block Nested-Loop Join,简称 BNL 这时候，被驱动表上没有可用的索引，算法的流程是这样的:

  1. 把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存
  2. 扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回 可以看到，在这个过程中，对表t1和t2都做了一次全表扫描，因此总的扫描行数是1100。由于 join_buffer是以无序数组的方式组织的，因此对表t2中的每一行，都要做100次判断，总共需要 在内存中做的判断次数是：100*1000=10万次。
    前面所说的 Simple Nested-Loop Join 算法进行查询，扫描行数也是10万行。因此，从时间复杂度上来说，这两个算法是一样的。但是，BNL 算法的这10万次判断是内存操作，速度上会快很多，性能也更好

如果我们不强制选择驱动表,mysql会选择哪个表做驱动表呢?

假设小表的行数是N，大表的行数是M，那么在这个算法里：

  1. 两个表都做一次全表扫描，所以总的扫描行数是M+N

  2. 内存中的判断次数是M*N

可以看到，调换这两个算式中的M和N没差别，因此这时候选择大表还是小表做驱动表，执行耗时是一样的。
然后，你可能马上就会问了，这个例子里表t1才100行，要是表t1是一个大表，join_buffer放不下怎么办呢？
join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。如果放不下表 如 t1t 的所有数的据话，策略很简单，就是分段放。
分段放的执行过程:

  1. 扫描表t1，顺序读取数据行放入join_buffer中，放完第88行join_buffer满了，继续第2步；

  2. 扫描表t2，把t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回；

  3. 清空join_buffer；

  4. 继续扫描表t1，顺序读取最后的12行数据放入join_buffer中，继续执行第2步。
    这个流程才体现出了这个算法名字中“Block”的由来，表示“分块去join”。

可以看到，这时候由于表t1被分成了两次放入join_buffer中，虽然分成两次放入join_buffer，但是判断等值条件的次数还是不变的，依然是(88+12)*1000=10万次,但是这样会导致表t2会被扫描两次。

显然，内存判断次数是不受选择哪个表作为驱动表影响的。而考虑到扫描行数，在M和N大小确定的情况下，N小一些，整个算式的结果会更小(判断N的大小不能只根据条数,还有查询的字段多少等有关系)

所以结论是，应该让小表当驱动表。

总结：
Nested Loop Join算法
将驱动表/外部表的结果集作为循环基础数据，然后循环该结果集，每次获取一条数据作为下一个表的过滤条件
查询数据，然后合并结果，获取结果集返回给客户端。Nested-Loop一次只将一行传入内层循环, 所以外层循
环(的结果集)有多少行, 内存循环便要执行多少次，效率非常差。

Block Nested-Loop Join算法
将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循
环的次数。主要用于当被join的表上无索引。

Batched Key Access算法
当被join的表能够使用索引时，就先好顺序，然后再去检索被join的表。对这些行按照索引字段进行排序，因
此减少了随机IO。如果被Join的表上没有索引，则使用老版本的BNL策略(BLOCK Nested-loop)。
avatar
文章
阅读
粉丝