mysql之join浅析

1.可以使用join吗?使用join有什么问题呢？-- >超过3个表不使用join,笛卡尔积问题 -->这些问题是怎么造成的呢？

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

如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。这种 join 尽量不要用。

2.如果有两个大小不同的表做join,应该用哪个表做驱动表呢？-->小表 -->为什么要使用小表呢？

3.join 语句是怎么执行呢？

4.join语句怎么优化呢？

首先，创建两个表 t1 t2

CREATE TABLE `t2` (
  `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;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

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

//这两个表都有一个主键索引 id 和一个索引 a，字段 b 上无索引。存储过程 idata() 往表 t2 里插入了 1000 行数据，在表 t1 里插入的是 100 行数据

Index Nested-Loop Join (NLJ)

select * from t1 straight_join t2 on (t1.a=t2.a); //t1 是驱动表，t2 是被驱动表
执行explain

被驱动表 t2 的字段 a 上有索引，join 过程用上了这个索引，因此这个语句的执行流程是这样的：

  1. 从表 t1 中读入一行数据 R；

  2. 从数据行 R 中，取出 a 字段到表 t2 里去查找；

  3. 取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分；

  4. 重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。

在这个流程中，总扫描行数200

如果不使用join，只能单表查询

select * from t1； //查出表 t1 的所有数据，这里有 100 行；
select * from t2 where a=$R.a；
在这个查询过程，也是扫描了 200 行，但是需要执行两条以上sql比直接join 多了与数据库的交互，除此之外，客户端还要自己拼接sql语句和结果，显然这样做还不如直接join

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

 

假设被驱动表的行数是 M。每次在被驱动表查一行数据，要先搜索索引 a，再搜索主键索引。每次搜索一棵树近似复杂度是以 2 为底的 M 的对数，记为 log2M，所以在被驱动表上查一行的时间复杂度是 2*log2M。假设驱动表的行数是 N，执行过程就要扫描驱动表 N 行，然后对于每一行，到被驱动表上匹配一次。因此整个执行过程，近似复杂度是 N + N*2*log2M。显然，N 对扫描行数的影响更大，因此应该让小表来做驱动表。

这个结论的前提是“可以使用被驱动表的索引”。

如果被驱动表用不上索引，jion语句是怎么执行的呢？

Simple Nested-Loop Join

select * from t1 straight_join t2 on (t1.a=t2.b);
由于表 t2 的字段 b 上没有索引，因此再用上面 的执行流程时，每次到 t2 去匹配的时候，就要做一次全表扫描。

这样算来，这个 SQL 请求就要扫描表 t2 多达 100 次，总共扫描 100*1000=10 万行。

MySQL 也没有使用这个 Simple Nested-Loop Join 算法，而是使用了另一个叫作“Block Nested-Loop Join”的算法，简称 BNL。

Block Nested-Loop Join（BNL）

这时候，被驱动表上没有可用的索引，算法的流程是这样的：

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

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

对应地，这条 SQL 语句的 explain 结果如下所示：

在这个过程中，对表 t1 和 t2 都做了一次全表扫描，所以总的扫描行数是 1100。由于 join_buffer 是以无序数组的方式组织的，所以对表 t2 中的每一行，都要做 100 次判断，总共需要在内存中做的判断次数是：100*1000=10 万次。

前面使用 Simple Nested-Loop Join 算法进行查询，扫描行数也是 10 万行。因此，从时间复杂度上来说，这两个算法是一样的。但是，Block Nested-Loop Join 算法的这 10 万次判断是内存操作，速度上会快很多，性能也更好

在这种情况下，应该选择哪个表做驱动表？

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

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

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

这时调换M N 没有差别，所以这时候选择大表还是小表做驱动表，执行耗时是一样的。

但是如果表 t1 是一个大表，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 步。

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

在这种情况下如何选择驱动表呢？

假设驱动表数据行是N,需要分成λ*N段，

所以，在这个算法的执行过程中：

  1. 扫描行数是 N+λ*N*M；

  2. 内存判断 N*M 次。

所以当N小一些的时候,整个算式结果会更小，所以应该让小表做驱动表

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

比如

select * from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=50;
select * from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=50;
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;

优化：思路是让 join 语句能够用上被驱动表上的索引，触发 BKA 算法(对 NLJ 算法的优化)，提升查询性能（原表上加索引，或者使用有索引的临时表）

hash join ：BNL算法join buffer中如果存储的是hash结构，内存判断就不是N*M次了，就变成了M次hash查找，MySQL目前不支持哈希join，这个优化思路可以在自己的业务端实现。

比如：

1.select * from t1;取得表 t1 的全部 100行数据，在业务端存入一个 hashMap中。

2.select * from t2 ; 获取表 t2 中的 1000行数据。把这 1000 行数据，一行一行地取到业务端，到 hash 结构的数据表中寻找匹配的数据。满足匹配的条件的这行数据，就作为结果集的一行。

 

 

ps:启用BKA算法

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

 
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/chcha1/p/14323580.html