目录
前言
在实际开发中,我们一般会有两类问题,为什么我们 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?-极客时间