唠唠MySQL的join

最新推荐文章于 2022-11-08 14:45:34 发布

一杯可乐加冰

最新推荐文章于 2022-11-08 14:45:34 发布

阅读量153

点赞数

分类专栏： MySQL 文章标签： mysql mysql优化

本文链接：https://blog.csdn.net/qq_41988542/article/details/117264260

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

准备条件：
建个库：

create database test;
use test;

建立t1,t2两张表，然后在t1插入100行数据，t2插入1000行数据：

CREATE TABLE `t2` ( `id` int(11) NOTNULL, `a` int(11) DEFAULTNULL, `b` int(11) DEFAULTNULL, 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)

MYSQL的join
大家都知道mysql的join是用来做一个连表查询的，然后也都听过尽量不要用这种连表查询，就算用了也要做到小表驱动大表。我们来唠唠这是为什么？博主是一个刚入门还在实习阶段的菜鸟，如果有哪里不对或者不好的地方，望指正，感谢！
MySQL使用的连表查询的算法有两种：Index Nested-Loop Join 和 Block Nested-Loop Join。下面我们来分别唠唠这两个玩意：
Index Nested-Loop Join
我们先来执行以下这个语句，使用straight_join 而不是 join 是为了让优化器按照我们指定的方式去JOIN，这个语句中，t1是驱动表：

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

explain执行分析后的结果
通过上面的执行计划分析，我们知道，这次查询使用了t2表中的索引a，大概的执行过程如下：
1，从表t1中读入一行数据 R；
2，从数据行R中，取出a字段到表t2里去查找；
3，取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
4，重复执行步骤1到3，直到表t1的末尾循环结束。
以上的流程在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为“IndexNested-Loop Join”，简称NLJ。执行过程图如下：
NJL的执行过程

在上面的过程中：
1，表t1是全表扫描，也就是扫描了100行；
2，表t2走的是树搜索过程，由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行，也是总共扫描100行；
加起来总共是扫描了200行。
那么我们来讨论能不能使用join呢？
如果不使用join而是拆成两条语句分别执行，也就是：
1，select * from t1; 这里扫描100行
2，for（select *from t2 where t2.a = #t1.a）;这里走的也是树搜索，也是扫描了100行。
扫描的行数是一样的，但是，这时候执行sql语句的次数却是：t1表 1次，t2表 100次，也就是执行了101次sql语句，比join多了100次的交互，这之间的IO时间，还要再加上你需要在select t2的时候拼接sql语句，这样对比显然join更有优势
那为什么说用join的时候，需要用小表来驱动大表呢？

在这个join语句执行过程中，驱动表（t1）是走全表扫描，而被驱动表（t2）是走树搜索。假设 t2 的行数是M。每次在 t2 查一行数据，要先搜索a索引树，拿到对应的主键的值去搜索主键索引树。每次搜索一棵树近似复杂度是以2为底的M的对数，记为log2M，所以在 t2 上查一行记录的时间复杂度是 2 * log2M（这里的2是表示扫描了 a索引树和主键索引树两棵树）。假设 t1 的行数是N，执行过程就要扫描t1 N行，然后对于每一行，到 t2 上匹配一次。因此整个执行过程，近似复杂度是 N+N * 2 * log2M。
显然，N对扫描行数的影响更大，因此应该让小表来做驱动表
结论：
1，join语句比强拆成多个单表语句的性能更好
2，使用join语句需要小表来做驱动表
以上的结论是基于走被驱动表的索引，但如果不走被驱动表的索引呢？这就要说到第二种算法：Block Nested-Loop Join了。
Block Nested-Loop Join
我们先把sql语句改一改：

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

因为b字段上面没有索引，所以如果还是按照NJL那种算法去执行的话，t1的每一行记录的a都拿去t2做一次匹配，这时候走的就是全表扫描了，也就是有可能扫描1000行才能找到匹配的值，然后t1有100行记录，也就是说t2最坏的情况下有可能扫描的次数为：100 * 1000 行，这个数量看起来也不大，毕竟Mysql是相当强大的，百万数据以下的扫描也就是晒晒水了。但是，一旦t1 或者 t2的数据量扩大百倍的时候，这个次数可就不是个小数目了。这个算法叫：Simple Nested-Loop Join，而不是我们上面所说的NJL了。当然了，MySQL也没有采用这个算法，而是用了Block Nested-Loop Join，简称BNJ。
它的执行过程如下：
1，把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存；
2，扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。
执行过程图：
BNJ算法的执行过程
explain语句执行的结果：
explain语句执行结果图
这么一看，t1和t2 都是全表扫描，总共扫了1100行，因为join_buff里面的数据是无序数组，所以对于t2的每一行数据都要做100次的比对，判断次数为100 * 1000。上面提到的SNJ的算法总的扫描行也是这个数，这么看，这两种算法的时间复杂度是一样的，但是因为BNJ是在内存中进行判断操作的，速度上会快很多。怎么知道用的是不是BJL，直接看explain的Extra有没有Using join buffer(Block nested loop)。
那么这时候是否需要选小表作为驱动表呢？

假设小表是N行，大表是M行。那么扫描次数为M+N，判断次数为MN。这么一看选那个表都无所谓（谁会爱上谁），但是join_buff是在内存中的，它的大小肯定是有限的，可以通过调整join_buff_size的值去调整join_buff的大小，那么当驱动表（t1）的行数远大于join_buff的容量的时候，BNJ的执行过程是咋样的：
1，把t1表的数据放入Join_buff中，直到join_buff放满
2，扫描一次表t2，把表t2的每一行去做对比，满足的作为结果集返回
3，清空join_buff
4，重复上面的操作，直到t1的数据读完
那么这时候的情况就不一样了，假设驱动表的行数为N，分为K段放入join_buff中（注意：K不是常数，会随着N 和 join_buff_size的大小变化而变化：K=N/join_buff_size，也就是N越大，join_buff_size越小，K也就越大），被驱动表的行数为M，那么扫描行数为：N+K*M；判断次数为：NM。明显看出判断次数不受影响，但是扫描行数上就N的值有着很大的影响。因此还是要选择小表来做驱动表。
总结
上面唠嗑了join使用的两种算法，总结一下：
该不该用join？
1，如果能使用NJL，也就是走被驱动表的索引，完全是可以用的。
2，如果是使用BJL，也就是不走被驱动表的索引，这时候的扫描行数过多，特别是大表上面的join可能要扫描被驱动表很多次，这时候就不建议用join了。
join时候该不该用小表驱动大表？
1，如果是NJL算法，那么就需要小表来驱动大表，从而减少扫描的次数。
2，如果是BNL算法，join_buff_size足够大时，无所谓；join_buff_size不够大时，还是需要小表驱动大表。
怎么判断小表大表呢？
像上面例子里我们没有任何条件，那就直接根据数据量来判断。但是如果像下面这样呢：

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;

这时候明显小表是t2，因为满足t2.id <=50的记录只有50条，而t1是有着100条记录的。
接下来看看第二个例子：

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;

因为不走被驱动表的索引，那么就是BNL。那么这时候t1和t2的表都是只有100行，但是，t1只查字段b的值，那么也就是说t1放进去join_buff里面的只有100行b字段的值，但t2是100行的整一行数据放进去，肯定就是t1放进去的数据量小，也就是说t1才是这时候的小表。
综上所述：大小表是看表按照条件过滤后，参与join的各个字段的总数据量大小
下一回我这个小菜鸡再给各位看官讲一下join的优化，咳咳，那个，茶水自备一下（手动狗头）

下一回之join的优化