34 | 到底可不可以使用join？

林晓斌



该思维导图由 AI 生成，仅供参考

在实际生产中，关于 join 语句使用的问题，一般会集中在以下两类：
我们 DBA 不让使用 join，使用 join 有什么问题呢？
如果有两个大小不同的表做 join，应该用哪个表做驱动表呢？
今天这篇文章，我就先跟你说说 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我们来看一下这个语句：
select * from t1 straight_join t2 on (t1.a=t2.a);
如果直接使用 join 语句，MySQL 优化器可能会选择表 t1 或 t2 作为驱动表，这样会影响我们分析 SQL 语句的执行过程。所以，为了便于分析执行过程中的性能问题，我改用 straight_join 让 MySQL 使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去 join。在这个语句里，t1 是驱动表，t2 是被驱动表。
现在，我们来看一下这条语句的 explain 结果。
图 1 使用索引字段 join 的 explain 结果
可以看到，在这条语句里，被驱动表 t2 的字段 a 上有索引，join 过程用上了这个索引，因此这个语句的执行流程是这样的：

公开

同步至部落

取消

完成

0/2000

荧光笔

直线

曲线

笔记

复制

AI

深入了解
翻译
英语
中文简体
中文繁体
法语
德语
日语
韩语
俄语
西班牙语
阿拉伯语
解释
总结

本文深入探讨了在实际生产中使用join语句的问题，重点关注了两个问题：DBA不允许使用join的原因以及在大小不同的表做join时应该选择哪个表作为驱动表。文章首先介绍了join语句的执行过程，以及使用straight_join固定连接方式执行查询的方法。随后详细分析了Index Nested-Loop Join算法的执行流程，并通过对比单表查询的方式，得出了使用join语句性能更优的结论。此外，文章还强调了在使用join语句时，应该让小表作为驱动表的重要性。另外，文章还介绍了Simple Nested-Loop Join算法，并指出了其在没有索引的情况下的不足之处。通过深入的技术分析，为读者提供了关于join语句使用的全面指导，使读者能够更好地理解join语句的执行原理和优化方法。

仅可试看部分内容，如需阅读全部内容，请付费购买文章所属专栏
《MySQL 实战 45 讲》，新⼈⾸单¥68

立即购买

登录后留言

全部留言(181)

最新
精选

没时间了ngu
置顶
join这种用的多的，看完还是有很大收获的。像之前讲的锁之类，感觉好抽象，老是记不住，唉。
作者回复: 嗯嗯，因为其实每个同学的只是背景不一样。这45讲里，每个同学都能从部分文章感觉到有收获，我觉得也很好了😆 不过锁其实用得也多的。。我以前负责业务库的时候，被开发同学问最多的问题之一就是，为啥死锁了^_^
2019-01-30
9
112
信信
老师好，回答本期问题：如果驱动表分段，那么被驱动表就被多次读，而被驱动表又是大表，循环读取的间隔肯定得超1秒，这就会导致上篇文章提到的：“数据页在LRU_old的存在时间超过1秒，就会移到young区”。最终结果就是把大部分热点数据都淘汰了，导致“Buffer pool hit rate”命中率极低，其他请求需要读磁盘，因此系统响应变慢，大部分请求阻塞。
作者回复: 👍
2019-01-30
19
365
斜面镜子 Bill
因为 join_buffer 不够大，需要对被驱动表做多次全表扫描，也就造成了“长事务”。除了老师上节课提到的导致undo log 不能被回收，导致回滚段空间膨胀问题，还会出现：1. 长期占用DML锁，引发DDL拿不到锁堵慢连接池； 2. SQL执行socket_timeout超时后业务接口重复发起，导致实例IO负载上升出现雪崩；3. 实例异常后，DBA kill SQL因繁杂的回滚执行时间过长，不能快速恢复可用；4. 如果业务采用select *作为结果集返回，极大可能出现网络拥堵，整体拖慢服务端的处理；5. 冷数据污染buffer pool，block nested-loop多次扫描，其中间隔很有可能超过1s，从而污染到lru 头部，影响整体的查询体验。
作者回复: 👍很赞之前知识点的也都加进来啦
2019-01-31
6
227
老杨同志
对被驱动表进行全表扫描，会把冷数据的page加入到buffer pool.,并且block nested-loop要扫描多次，两次扫描的时间可能会超过1秒，使lru的那个优化失效，把热点数据从buffer pool中淘汰掉，影响正常业务的查询效率
作者回复: 漂亮👍
2019-01-30

65
Zzz
林老师，我没想清楚为什么会进入young区域。假设大表t大小是M页>old区域N页，由于Block Nested-Loop Join需要对t进行k次全表扫描。第一次扫描时，1~N页依次被放入old区域，访问N+1页时淘汰1页，放入N+1页，以此类推，第一次扫描结束后old区域存放的是M-N+1~M页。第二次扫描开始，访问1页，淘汰M-N+1页，放入1页。可以把M页想象成一个环，N页想象成在这个环上滑动的窗口，由于M>N，不管是哪次扫描，需要访问的页都不会在滑动窗口上，所以不会存在“被访问的时候数据页在 LRU 链表中存在的时间超过了 1 秒“而被放入young的情况。我能想到的会被放入young区域的情况是，在当次扫描中，由于一页上有多行数据，需要对该页访问多次，超过了1s，不管这种情况就和t大小没关系了，而是由于page size太大，而一行数据太少。
作者回复: 你说得对，分两类情况，小于bp 3/8的情况会跑到young，大于3/8的会影响young部分的更新
2019-01-30
7
44
清风浊酒
老师您好，left join 和 right join 会固定驱动表吗？
作者回复: 不会强制，但是由于语义的关系，大概率上是按照语句上写的关系去驱动，效率是比较高的
2019-01-30
2
43
泡泡爱dota
explain select * from t1 straight_join t2 on (t1.a=t2.a) where t1.a < 50; 老师, 这条sql为什么t1.a的索引没有用上, t1还是走全表
作者回复: 如果数据量不够多，并且满足a<50的行，占比比较高的话，优化器有可能会认为“还要回表，还不如直接扫主键id”
2019-01-31
4
38
郝攀刚จุ๊บ
业务逻辑关系，一个SQL中left join7，8个表。这我该怎么优化。每次看到这些脑壳就大！
作者回复: 😓 Explain下，没用用index nested-loop 的全要优化
2019-01-30
7
28
萤火虫
年底了有一种想跳槽的冲动身在武汉的我想出去看看可一想到自身的能力和学历又不敢去了苦恼...
作者回复: 今年这情况还是要先克制一下^_^ 先把内功练起来😆
2019-01-30
12
25
呵呵
老师，新年好！优化器会自动选择小表作为驱动表，那么我们人为把小表写成驱动表还有意义吗？
作者回复: 新年好嗯优化器大部分时候会选对，如果选不对，我们就得自己强行指定了哈其实了解这个原理主要还是指导我们根据最优的join顺序，来创建被驱动表字段上的索引
2019-02-11

17

收起评论