MySQL 实战 45 讲
林晓斌
网名丁奇,前腾讯云数据库负责人
224874 人已学习
新⼈⾸单¥68
登录后,你可以任选4讲全文学习
课程目录
已完结/共 49 讲
实践篇 (37讲)
特别放送 (1讲)
结课测试 (1讲)
MySQL 实战 45 讲
15
15
1.0x
00:00/00:00
登录|注册

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

join_buffer大小影响
小表作为驱动表
小表作为驱动表
使用条件
性能分析
执行流程
使用条件
性能分析
执行流程
undo log空间膨胀
行锁占用
join_buffer大小
使用被驱动表的索引
Block Nested-Loop Join算法
Index Nested-Loop Join算法
Block Nested-Loop Join
Index Nested-Loop Join
长事务的影响
影响因素
选择驱动表
MySQL执行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
收起评论
显示
设置
留言
99+
收藏
沉浸
阅读
分享
手机端
快捷键
回顶部