join的使用与优化

最新推荐文章于 2024-09-26 10:54:06 发布

其实系一个须刨

最新推荐文章于 2024-09-26 10:54:06 发布

阅读量436

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/lianggx3/article/details/116381291

版权

mysql 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

先创建两个表，结构一样：

下面讲解集中join语句的情况。

Index Nested-Loop Join

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

用straight_join让MySQL使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去join。在这个语句里，t1 是驱动表，t2是被驱动表。

先遍历表t1，然后根据从表t1中取出的每行数据中的a值，去表t2中查找满足条件的记录，并且可以用被驱动表上的索引。这种就是“Index Nested-Loop Join”，简称NLJ

1. 对驱动表t1做了全表扫描，这个过程需要扫描100行；

2. 而对于每一行R，根据a字段去表t2查找，走的是树搜索过程。由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行，也是总共扫描100行；

3. 所以，整个执行流程，总扫描行数是200。

为了更好的利用被驱动表上的索引，所以一般用小表作索引。

Simple Nested-Loop Join

没有索引的场景下：select * from t1 straight_join t2 on (t1.a=t2.b);

这样子的话，两边都要作一次全扫描，无所谓驱动表还是被驱动表。这样子的话，整个join就会非常慢。为此Mysql采用了“Block Nested-Loop Join”的算法，简称BNL。

Block Nested-Loop Join

算法的流程是这样的：

1. 把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存；

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

BNL的判断是在join_buffer内存里面的，所以执行速度会快很多。join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。

如果放不下的话执行的流程就变成了：

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

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

3. 清空join_buffer；

4. 继续扫描表t1，顺序读取最后的12行数据放入join_buffer中，继续执行第2步。

Join算法的选择

1. 如果可以使用Index Nested-Loop Join算法，也就是说可以用上被驱动表上的索引，其实是没问题的；

2. 如果使用Block Nested-Loop Join算法，扫描行数就会过多。尤其是在大表上的join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种join尽量不要用。所以你在判断要不要使用join语句时，就是看explain结果里面，Extra字段里面有没有出现“Block Nested Loop”字样。

Join驱动选择

1. 如果是Index Nested-Loop Join算法，应该选择小表做驱动表；

2. 如果是Block Nested-Loop Join算法：

在join_buffer_size足够大的时候，是一样的；
在join_buffer_size不够大的时候（这种情况更常见），应该选择小表做驱动表。

所以，这个问题的结论就是，总是应该使用小表做驱动表。

小表的定义

select t1.b,t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=100;

select t1.b,t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100;

两条语句是不一样的：

表t1只查字段b，因此如果把t1放到join_buffer中，则join_buffer中只需要放入b的值；
表t2需要查所有的字段，因此如果把表t2放到join_buffer中的话，就需要放入三个字段id、a和b

小表选择时，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

NLJ算法的优化

一. Multi-Range Read

优化的主要目的是尽量使用顺序读盘。在索引查找的回表的时候，回表肯定是一行行搜索主键索引的，当要查找的数据很多，回表可能就变成了随机访问，那么这里就有一个优化点。我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。这，就是MRR优化的设计思路。此时，语句的执行流程变成了这样：

1. 根据索引a，定位到满足条件的记录，将id值放入read_rnd_buffer中;

2. 将read_rnd_buffer中的id进行递增排序；

3. 排序后的id数组，依次到主键id索引中查记录，并作为结果返回。

二. Batched Key Access

Batched KeyAcess(BKA)算法是对NLJ算法的优化。其实就是用一个join_buffer缓存加入到NLJ的算法中，一次从驱动表中取多行，只去查询需要的字段，再去匹配。

BNL算法的优化

之前的文章提到，扫描数据的时候利用young区和old区的LRU算法保证缓存的命中率，但是如果全盘扫描执行的语句大多数超过1s，那么就可能会影响到young区的缓存，降低命中率。

一. BNL转BKA

加上索引，但是加索引也有浪费的问题，比如select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;这个语句，在表t2中插入了100万行数据，但是经过where条件过滤后，需要参与join的只有2000行数据。其实joinbuffer上只放了满足条件的1000行，然后再扫描t2，与join buffer中的所有数据对比。这个工作量也大，而且在表t2的字段b上创建索引浪费了建立索引要用的资源。

但是如果不建立索引的话，对于表t2的每一行，判断join是否满足的时候，都需要遍历join_buffer中的所有行。因此判断等值条件的次数是1000*100万=10亿次，这个判断的工作量很大。

二. 临时表

1. 把表t2中满足条件的数据放在临时表tmp_t中；

2. 为了让join使用BKA算法，给临时表tmp_t的字段b加上索引；

3. 让表t1和tmp_t做join操作。

对应的SQL语句的写法如下