34 到底可不可以使用join?

34 到底可不可以使用join?

在实际生产中,关于join语句使用的问题,一般会集中在以下两类:

--1 dba不让使用join,使用join有什么问题呢

--2 如果有两个大小不同的表做join,应该用哪个表做驱动表呢?

创建2个表作为测试

CREATE TABLE `t34` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

drop procedure idata_t34;
delimiter ;;
create procedure idata_t34()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t34 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata_t34();

create table t35 like t34;
insert into t35 (select * from t34 where id<=100)
commit;

select * from t34;
select * from t35;

这两个表都有一个主键索引id和一个索引a,字段b上没有索引,存储过程往表t34里插入1000行数据,往表t35里插入100行数据

Index Nested-Loop Join

sql语句

select * from t35 straight_join t34 on (t35.a=t34.a);

如果直接使用join语句,mysql优化器可能会选择表t34或35作为驱动表,这里我们改用straight join直接固定连接方式执行,t35是驱动表,t34是被驱动表

34 到底可不可以使用join?

在这条语句里,被驱动表t34的字段上有a索引,join过程用了这个索引,这个语句的执行过程是这样的:

--1 从表t35中读入一行数据R

--2 从数据行R中,取出a字段到到表t34里去查找

--3 取出表t34中满足条件的行,跟R组成一行,作为结果集的一部分

--4 重复执行步骤1到3,直到表t35的末尾循环结束

这个过程是先遍历表t35,然后根据从表t35中取出的每行数据中的a值,去表t34中查询满足条件的记录,在形式上,这个过程跟我们写程序时的嵌套查询类似,并且可以用上被驱动表的索引,所以我们称为”index nested-loop join”,简称NLJ.

对应的流程图--t1=t35,t2=t34

34 到底可不可以使用join?

在这个流程里面:

--1 对驱动表t35做了全表扫描,这个过程需要扫描100行

--2 而对于每一行R,根据a字段去表t34查找,走的是树搜索过程。由于这里构造的数据是一一对应,因此每次的搜索过程都需要扫描一行,也是总共扫描100

--3 所以,整个执行流程,总扫描行数是200

先看第一个问题:能不能使用join

假设不使用join,都只能用单表查询,

--1 执行select * from t35,查询出所有的数据,100行

--2 循环遍历这100行数据,每一行R取出a的值$a,执行select * from t34 where a=$a,把返回的结果和R构成结果集的一行

可以看到,这个查询过程, 也是扫描了200行,但是总共执行了101条语句,比直接join多了100次交互,除此之外,客户端还要自己拼接sql语句和结果。

在这里,显然直接使用join要好。

第二个问题:怎么选择驱动表

在这个join语句执行的过程中,驱动表是走全表扫描,而被驱动表走的是树搜索。

应该让小表来做驱动表。

得到两个结论:

--1 使用join语句,性能比强行拆成多个单表sql的性能要好

--2 如果使用join,需要让小表做驱动表

--noded:这个结论的前提是”可以使用被驱动表的索引

 

Simple Nested-Loop Join

sql 语句如下

select * from t35 straight_join t34 on (t35.a=t34.b);

由于表t34的字段上没有b索引,因此再用上面的执行流程,每次到t35去匹配的时候,就需要做一次全表扫描

这样算下来,这个sql请求需要扫描t34多大100次,总共扫描100*1000=10w次

在mysql里面,使用了”Block Nested-Loop Join”

 

Block Nested-Loop Join

这时候,被驱动表上没有索引,算法的流程如下:

--1 把表t35的数据读入线程内存join_buffer,由于这里是select *,所以把整个表放入了内存

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

流程图如下:

34 到底可不可以使用join?

Explain的结果如下

34 到底可不可以使用join?

可以看到,这个过程中,对表t35和t34都做了一次全扫描,因此总扫描的行数是1100行,由于join_buffer是以无序数组的方式组织的,因此对表t34中的每一行,都需要做100次判断,总共需要在内存中做的判断次数是100*1000=10w次

这时候,选择大表或小表做驱动表没有多大的差别

如果t35的数据很多,join_buffer放不下,怎么办呢?

join_buffer的大小由join_buffer_size参数决定,默认256k,如果放不下t35的所有数据话,策略很简单,就是分段放

--1 扫描表t35,顺序读取数据放入join_buffer,放完一定行的数据行发现join_buffer满了,

--2 扫描表t34,把表t34中的每一行取出来,跟join_buffer中的数据做对比,满足jon条件的,作为结果集的一部分返回

--3 清空join_buffer

--4 继续扫描表t35,顺序读取最后的行,放入join_buffer,继续执行第二步

34 到底可不可以使用join?

图中步骤4,5,表示清空join_buffer再重复使用

可以看到,这时候由于表t35被分成了2次放入join_buffer,导致表t34会被扫描2次,虽然是分两次放入join_buffer,但是判断等值条件的次数还是不变的,

34 到底可不可以使用join?

第一个问题:

--1如果可以使用index nested-loop join算法,也就是说可以用上被驱动表上的索引,其实也是没问题的

--2 如果使用block nested-loop join算法,扫描行数就会过多,尤其是在大表的join操作,这样可能要扫描被驱动表很多次,会占用大量的系统资源,所以这种join就尽量不要用。

所以在判断要不要使用join语句时,就是看explain结果集的extra字段有没有出现”block nested loop”字样

第二个问题:

--1 如果是index nested-loop join算法,应该选择小表做驱动表

--2 如果是block nested-loop join算法:

---在join_buffer_size足够大的情况下是一样的

---在join_buffer_size不够大的时候,遇到这样情况,应该选择小表做驱动表。

什么叫做”小表

select * from t35 straight_join t34 on (t35.b=t34.b) where t34.id<=50;

select * from t34 straight_join t35 on (t35.b=t34.b) where t34.id<=50;

34 到底可不可以使用join?

34 到底可不可以使用join?

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

上一篇:RAID卡log日志解析


下一篇:OSError: [WinError 126] 找不到指定的模块。