MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
42926 人已学习
课程目录
已完结 48 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 这一次,让我们一起来搞懂MySQL
免费
基础篇 (8讲)
01 | 基础架构:一条SQL查询语句是如何执行的?
02 | 日志系统:一条SQL更新语句是如何执行的?
03 | 事务隔离:为什么你改了我还看不见?
04 | 深入浅出索引(上)
05 | 深入浅出索引(下)
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
07 | 行锁功过:怎么减少行锁对性能的影响?
08 | 事务到底是隔离的还是不隔离的?
实践篇 (37讲)
09 | 普通索引和唯一索引,应该怎么选择?
10 | MySQL为什么有时候会选错索引?
11 | 怎么给字符串字段加索引?
12 | 为什么我的MySQL会“抖”一下?
13 | 为什么表数据删掉一半,表文件大小不变?
14 | count(*)这么慢,我该怎么办?
15 | 答疑文章(一):日志和索引相关问题
16 | “order by”是怎么工作的?
17 | 如何正确地显示随机消息?
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
19 | 为什么我只查一行的语句,也执行这么慢?
20 | 幻读是什么,幻读有什么问题?
21 | 为什么我只改一行的语句,锁这么多?
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
23 | MySQL是怎么保证数据不丢的?
24 | MySQL是怎么保证主备一致的?
25 | MySQL是怎么保证高可用的?
26 | 备库为什么会延迟好几个小时?
27 | 主库出问题了,从库怎么办?
28 | 读写分离有哪些坑?
29 | 如何判断一个数据库是不是出问题了?
30 | 答疑文章(二):用动态的观点看加锁
31 | 误删数据后除了跑路,还能怎么办?
32 | 为什么还有kill不掉的语句?
33 | 我查这么多数据,会不会把数据库内存打爆?
34 | 到底可不可以使用join?
35 | join语句怎么优化?
36 | 为什么临时表可以重名?
37 | 什么时候会使用内部临时表?
38 | 都说InnoDB好,那还要不要使用Memory引擎?
39 | 自增主键为什么不是连续的?
40 | insert语句的锁为什么这么多?
41 | 怎么最快地复制一张表?
42 | grant之后要跟着flush privileges吗?
43 | 要不要使用分区表?
44 | 答疑文章(三):说一说这些好问题
45 | 自增id用完怎么办?
特别放送 (1讲)
直播回顾 | 林晓斌:我的 MySQL 心路历程
结束语 (1讲)
结束语 | 点线网面,一起构建MySQL知识网络
MySQL实战45讲
登录|注册

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

林晓斌 2019-01-30
在实际生产中,关于 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/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(94)

  • 没时间了ngu 置顶
    join这种用的多的,看完还是有很大收获的。像之前讲的锁之类,感觉好抽象,老是记不住,唉。

    作者回复: 嗯嗯,因为其实每个同学的只是背景不一样。
    这45讲里,每个同学都能从部分文章感觉到有收获,我觉得也很好了😆

    不过 锁其实用得也多的。。
    我以前负责业务库的时候,被开发同学问最多的问题之一就是,为啥死锁了^_^

    2019-01-30
    13
  • 信信
    老师好,回答本期问题:如果驱动表分段,那么被驱动表就被多次读,而被驱动表又是大表,循环读取的间隔肯定得超1秒,这就会导致上篇文章提到的:“数据页在LRU_old的存在时间超过1秒,就会移到young区”。最终结果就是把大部分热点数据都淘汰了,导致“Buffer pool hit rate”命中率极低,其他请求需要读磁盘,因此系统响应变慢,大部分请求阻塞。

    作者回复: 👍

    2019-01-30
    2
    65
  • 斜面镜子 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
    39
  • 老杨同志
    对被驱动表进行全表扫描,会把冷数据的page加入到buffer pool.,并且block nested-loop要扫描多次,两次扫描的时间可能会超过1秒,使lru的那个优化失效,把热点数据从buffer pool中淘汰掉,影响正常业务的查询效率

    作者回复: 漂亮👍

    2019-01-30
    16
  • 抽离の❤️
    早上听老师一节课感觉获益匪浅

    作者回复: 好早呀🤝

    2019-01-30
    12
  • 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
    8
  • amazon1011
    这个专栏受益匪浅,老师再搞个内核源码专栏:)
    2019-01-30
    8
  • 萤火虫
    年底了有一种想跳槽的冲动 身在武汉的我想出去看看 可一想到自身的能力和学历 又不敢去了 苦恼...

    作者回复: 今年这情况还是要先克制一下^_^
    先把内功练起来😆

    2019-01-30
    8
  • 清风浊酒
    老师您好,left join 和 right join 会固定驱动表吗?

    作者回复: 不会强制,但是由于语义的关系,大概率上是按照语句上写的关系去驱动,效率是比较高的

    2019-01-30
    7
  • 泡泡爱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
    1
    5
  • 柚子
    join在热点表操作中,join查询是一次给两张表同时加锁吧,会不会增大锁冲突的几率?
    业务中肯定要使用被驱动表的索引,通常我们是先在驱动表查出结果集,然后再通过in被驱动表索引字段,分两步查询,这样是否比直接join委托点?

    作者回复: join也是普通查询,都不需要加锁哦,参考下MVCC那篇;

    就是我们文中说的,“分两步查询,先查驱动表,然后查多个in”,如果可以用上被驱动表的索引,我觉得可以用上Index Nested-Loop Join算法,其实效果是跟拆开写类似的

    2019-01-30
    1
    5
  • 1024
    文中解释NLJ和BNL时间复杂度相同,都是M*N。但是对于BNL性能好于NLJ的原因只是提到:"BNL的判断是在内存中操作,速度上会快很多,性能也更好"。请问老师?这句话的言外之意是: NLJ的判断不是在内存中操作吗?不将数据加载到内存,CPU如何进行判断呢?

    作者回复: 这个我在答疑文章中展开哈,其实还是“内存数据是从哪里来的”的问题。

    我们这里说的是BNL比Simple nested loop 快哈

    2019-02-19
    1
    4
  • 403
    用那个作为驱动表,mysql会自己优化么?

    作者回复: 会的

    2019-02-09
    4
  • 郝攀刚จุ๊บ
    业务逻辑关系,一个SQL中left join7,8个表。这我该怎么优化。每次看到这些脑壳就大!

    作者回复: 😓
    Explain下,没用用index nested-loop 的全要优化

    2019-01-30
    4
  • Franis
    老师,想问一下,如果是“一对多”的多个表进行join的话,应该选择“多”的表作为驱动表,还是选择“一”的表作为驱动表呢?

    作者回复: 如果有严格的一对多,而且要join够快一般都会在join 字段上创建索引,
    这时候应该选“一”为驱动表

    2019-03-03
    3
  • 思考特~
    老师,这边想请教一个困扰很久的问题,用mysql经常会制定这么一个规则,不允许多表join。从实际情况看,几乎不太可能遵守这个规则,有个交易的业务场景涉及 用户表 300W、订单表 900W、支付表 900W,每次需要查一个用户下面的订单信息可能就有点慢了,但是还能接受,如果是查询一个团体的订单信息,这个量就非常可观了,查询有时候根本返回不了结果。根本无法避免多表Join,所以想问问老师,在这种需要多表Join业务场景下,如何设计表,来提升性能?或者有这方面推荐的资料可以参考的

    作者回复: 我的建议就是用好NLJ和BKA算法😆

    2019-03-03
    3
  • 长期规划
    老师,既然MySQL会自己优化使用哪个表当驱动表,那程序员在写jion语句时就不用纠结该写A jion B还是B jion A了吧
    2019-07-23
    2
  • Cy190622
    专栏已经更新完毕,首先谢谢老师;有以下几个小问题,希望老师有时间能够看到。
    1.普通的join查询是被执行器优化执行过的吧?但是优化执行是否做到最好呢?怎么验证一下优化过程用的是那种方式呢?是用两个表互换explain一下吗?
    2.这种查询优化在Mysql哪个版本中做了较大的加强。
    3.SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1.a=t2.b); (t1是以a为索引的100行数据,t2中b非索引1000条数据)t1做了驱动表,属于BNL情形;如果用t2作为驱动表,t1的a索引就可以用,属于NLJ情形,选择哪个表作为驱动表更好呢?

    作者回复: 1. 对,explai
    2. 如果你写了STRAIGHT_JOIN,那没得选啦,就是t1做驱动表。
       这种情况,如果你改用join,应该会选t2做驱动表

    2019-03-12
    2
  • Franis
    如果我产品表有十条数据,订单表有一万条数据。
    为什么我用订单表作为驱动表会比用产品表作为驱动表速度快呢?

    作者回复: 要看索引😓

    2019-03-04
    2
  • felix
    不让用join,那用什么呢,用逗号分隔两表?
    join有多个条件的话,写在on后面和where后面有什么区别吗?

    作者回复: 逗号分隔两表还是join 😄
    下一篇会讲到优化,主要思路就是用上被驱动表索引哈

    2019-01-31
    2
收起评论
94
返回
顶部