Mysql中的join语句

skyemax
282 阅读4分钟

准备环境

首先创建两张表t1,t2,主键索引id,普通索引a,b字段无索引,存储过程往t2插入1000条记录,往t1插入100条记录.

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)

Index Nested-Loop Join(NLJ)

首先看一下这个语句

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

直接使用join,mysql优化器会自己选择驱动表和被驱动表,所以我们这里使用straight_join 来强制t1为驱动表,t2为被驱动表,我们看一下explain结果。

可以看到这个过程使用到了t2表的a索引。
执行流程:

  1. 从t1中读取一行数据R
  2. 从R中拿到字段a去t2里查找
  3. 从t2中找出满足条件的一行,跟R组成一行,作为结果集
  4. 重复执行1-3,知道t1数据循环完毕

这个过程跟我们平时所写的嵌套查询类似,并且可以用上被驱动表的索引,我们称作“Index Nested-Loop Join”,简称 NLJ。
对应的流程图如下:

在这个流程里:

  1. 对驱动表t1全表扫描,100行
  2. 拿到a到t2里进行树搜索,100行(树搜索查找的是索引树,不会去扫描整行记录,所以每次查询只扫描一行)
  3. 整个执行流程,总扫描行数为200行

如果不用join而使用单表查询

  1. select * from t1; 100行
  2. 遍历这100行数据
    • 取出R.a
    • select * from t2 where a = R.a
    • 组成一行返回结果集

这个过程同样扫描了200行,但是总共执行了101条sql查询,并且客户端还要自己拼接返回结果,显然join效果更好
我们来分析下时间复杂度:被驱动表行数为M,执行树搜索logM,因为select * 所以需要回表,2*logM,在被驱动表上查找一行的时间复杂度为2*logM,驱动表行数为N,首先全表扫描,所以总的近似复杂度:N*2*logM
可以看出N对值的影响更大,所以应减小N,也就是驱动表的行数,即小表驱动大表。
到这里我们总结一下：

  1. 使用join语句比拆分成多个单表查询性能要好
  2. 使用join语句，要让小表做驱动表，前提是能使用到被驱动表的索引

Simple Nested-Loop Join

我们再看下驱动表用不到索引的情况：

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

t2的b字段没有索引,所以要进行全表扫描,时间复杂度N*M,这个算法太笨重,Mysql也没有使用这个算法,而是“Block Nested-Loop Join”

Block Nested-Loop Join(BNL)

当没有用到被驱动表的索引时,算法流程:

  1. 把t1表的数据读入线程内存join buffer中,因为select *,所以把整个t1表放入内存;
  2. 扫描t2,把t2中的数据取出来和join buffer中的比较,整合结果集 流程图:

这条语句的EXPLAIN:

这种情况同样是扫描10万行,但是实在内存join buffer操作,所以性能会更好。
这个算法中,扫描行数是M+N,时间复杂度是M*N。 如果t1是个大表，join buffer放不下怎么办？join buffer的大小是由join_buffer_size大小决定的，默认为256k，如果放不下 ，那就分段放。执行过程：

  1. 把t1表的数据放入join buffer，放到80条放不下了，执行第二步；
  2. 扫描t2,把t2中的数据取出来和join buffer中的比较,整合结果集
  3. 清空join buffer，重新执行步骤2 流程图：

这个过程同样需要判断（80+20）*1000等于10万次。
驱动表行数为N,被驱动表行数为M,假设分成k段,扫描行数N+k*M,k的值受驱动表行数N的影响,N越大,分成的段数k越多,扫描行数也就越多,所以在被驱动表没有用到索引的BNL中,也是需要用小表驱动大表
当然k的值也受join_buff_size大小的影响,size越大,分的段就越少,扫描行数也就越少,所以可以通过增大join_buff_size的大小来优化join查询。

什么叫做小表

第一种情况：

select * from t1 join t2 on (t1.b=t2.b) where t2.id<=50;

该语句无论谁做被驱动表都用不到索引,但是t2表做驱动表的话,只需要往join buffer中放50条数据,所以应选择t2做驱动表

第二种情况：

select t1.b,t2.* from  t1  join t2 on (t1.b=t2.b)

该语句无论谁做被驱动表都用不到索引,但是t1表做驱动表的话,只需要放入b字段,而不需要全放,占用空间更小,所以应选择t1做驱动表

mysql自己会在选择哪个驱动表做优化。

join语句的第一篇完结！

avatar
文章
阅读
粉丝