join 语句使用

目录

前言

创建数据

知识点补充

Join算法Index Nested-Loop

小结：

Join算法Block Nested-Loop

join_buffer放不下驱动表情况

小结：

小表是什么？

总结：

参考内容

---

前言

在实际开发中，我们一般会有两类问题，为什么我们 DBA 不让使用 join，使用 join 有什么问题呢？如果有两个大小不同的表做 join，应该用哪个表做驱动表呢？MySQL 执行 join 语句的两种可能算法（Index Nested-Loop Join ，Block Nested-Loop Join），这两种算法是由能否使用被驱动表的索引决定的。

创建数据

首先创建表与数据

CREATE TABLE `high`.`testjoin2` 
( `id` INT NOT NULL, 
`index` INT NULL, 
`num` INT NULL, 
PRIMARY KEY ( `id` ), 
INDEX `index` ( `index` ) );

 批量添加数据

PRIMARY KEY ( `id` ), INDEX `index` ( `index` ) );
delimiter ;;
drop procedure idata;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into testjoin2 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

create table testjoin1 like testjoin2;
insert into testjoin1 (select * from testjoin2 where id<=100)

知识点补充

straight_join ：目的是MySQL 使用固定的连接方式执行查询。

这里简单说明一下，join语句如果可以用上被驱动表的索引的算法是Index Nested-Loop Join，反之使用的Block Nested-Loop Join算法。

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。

Join算法Index Nested-Loop

这里我们知道了可以用上被驱动表的索引的算法是Index Nested-Loop Join，已知创建的表的index有索引所以sql如下：

EXPLAIN select * from  testjoin1 straight_join testjoin2 on (testjoin1.index=testjoin2.index);

 explain 结果:

 从结果可以看出join的过程中使用了testjoin2的index字段索引。

语句执行流程：

• 从表 testjoin1中读入一行数据A（扫描 100 行）；
• 从该数据A行中，取出 a 字段到表 testjoin2 里去查找（扫描 100 行）；
• 取出表 testjoin2中满足条件的行，跟数据A组成一行，作为结果集的一部分；
• 重复执行步骤 1 到 3，直到表 testjoin1的末尾循环结束（总共扫描200行）。

这个过程就跟我们写的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为“Index Nested-Loop Join。同时这个过程中驱动表是走全表扫描，而被驱动表是走树搜索。每次在被驱动表查一行数据，要先搜索索引 a，再搜索主键索引。

小结：

使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；

如果使用 join 语句的话，需要让小表做驱动表。

Join算法Block Nested-Loop

我们知道用不上被驱动表的索引时了mysql选择的Block Nested-Loop Join算法，所以我们将sql改一下：

EXPLAIN select * from testjoin1 straight_join testjoin2 on (testjoin1.index=testjoin2.num);

 explain 结果:

Extra:Using where; Using join buffer (Block Nested Loop) 可以看出使用的Block Nested Loop算法

sql语句执行流程：

• 把表 testjoin1的数据读入线程内存 join_buffer 中，因为我们这个语句中写的是 select *，所以是把整个表 testjoin1放入了内存；
• 扫描表 testjoin2，把表 testjoin2中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

从结果可以看出testjoin1扫描了100行，testjoin2扫描了1000行，因为join_buffer 是以无序数组的方式组织的，因此对表 testjoin2中的每一行，都要做 100 次判断（Block Nested-Loop Join 算法的这 10 万次判断是内存操作），总共需要在内存中做的判断次数是：100*1000=10 万次，(扫描行数=testjoin1.rows*testjoin2.rows)。这时候选择大表还是小表做驱动表，扫描行数、执行耗时是样的。

join_buffer放不下驱动表情况

sql语句执行流程：

• 把表 testjoin1的数据读入线程内存 join_buffer 中时，放了一部分数据放满了，然后紧接着去扫描testjoin2表 testjoin2中的每一行取出来，跟 join_buffer 中的数据做对比，满足条件的数据放入结果集；
• 把表 testjoin1的剩下的数据分次放入join_buffer，重复以上操作

所以说testjoin1越小或者是join_buffer越大，分的次数就越小，应该让小表当驱动表

小结：

如果在大表上的 join 操作时，可能回扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。

使用join时，如果join_buffer_size足够大，无论那个表做驱动表结果都一样；如果join_buffer_size不够大时，应该使用小表当驱动表。

小表是什么？

join语法的两个表按照各自的条件过滤，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

总结：

如果可以使用被驱动表的索引可以使用join 语句；

如果不能使用被驱动表的索引，然后使用 Block Nested-Loop Join 算法，尽量不要使用join；

在使用 join 的时候，应该让小表做驱动表。

参考内容

如果想知道更多详细内容请自行学习《极客时间mysql实战45讲》34 | 到底可不可以使用join？-极客时间