MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
43178 人已学习
课程目录
已完结 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讲
登录|注册

35 | join语句怎么优化?

林晓斌 2019-02-01
在上一篇文章中,我和你介绍了 join 语句的两种算法,分别是 Index Nested-Loop Join(NLJ) 和 Block Nested-Loop Join(BNL)。
我们发现在使用 NLJ 算法的时候,其实效果还是不错的,比通过应用层拆分成多个语句然后再拼接查询结果更方便,而且性能也不会差。
但是,BNL 算法在大表 join 的时候性能就差多了,比较次数等于两个表参与 join 的行数的乘积,很消耗 CPU 资源。
当然了,这两个算法都还有继续优化的空间,我们今天就来聊聊这个话题。
为了便于分析,我还是创建两个表 t1、t2 来和你展开今天的问题。
create table t1(id int primary key, a int, b int, index(a));
create table t2 like t1;
drop procedure idata;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into t1 values(i, 1001-i, i);
set i=i+1;
end while;
set i=1;
while(i<=1000000)do
insert into t2 values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
为了便于后面量化说明,我在表 t1 里,插入了 1000 行数据,每一行的 a=1001-id 的值。也就是说,表 t1 中字段 a 是逆序的。同时,我在表 t2 中插入了 100 万行数据。

Multi-Range Read 优化

在介绍 join 语句的优化方案之前,我需要先和你介绍一个知识点,即:Multi-Range Read 优化 (MRR)。这个优化的主要目的是尽量使用顺序读盘。
第 4 篇文章中,我和你介绍 InnoDB 的索引结构时,提到了“回表”的概念。我们先来回顾一下这个概念。回表是指,InnoDB 在普通索引 a 上查到主键 id 的值后,再根据一个个主键 id 的值到主键索引上去查整行数据的过程。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(59)

  • IceGeek17
    节后补课,有几个问题:

    问题一:
    对于BKA算法的流程理解,用文中的例子,先把t1表(小表)中查询需要的字段放入join_buffer, 然后把join_buffer里的字段值批量传给t2表,先根据索引a查到id,然后得到一批主键id,再根据主键id排序,然后再根据排完序的id去主键索引查数据(这里用到MRR)
    理解是否正确?
    这里对于主键id排序是在哪里做的,是在join_buffer里,还是另外再开辟一块临时内存?如果在join_buffer里,那join_buffer里的每行内容是不是:t2.id + t1查询必须的字段,并且join_buffer里是根据id排序的?

    问题二:
    虽然MySQL官方没有支持hash join,但是之前看到文章说,MariaDB已经支持hash join,能不能后续在答疑文章中简单总结下mariaDB支持的join算法

    问题三:
    在实际项目中,一个比较困惑的问题,看到过这样的类似写法:
    select xxx from t1 join t2 on t1.id = t2.id for update (目的是获取几个表上最新的数据,并且加上锁,防止数据被更新)
    这里有几个问题:
    1) 像这样 join + for update,表上的加锁规则是怎么样的?是不是在需要join的两个表上根据具体的查询执行过程都加上锁?
    2)像这样 join + for update 的用法是否合理?碰到这样的场景,应该怎么去做?

    问题四:
    看过阿里输出的开发手册里,强调 “最多不超过三表join”,实际项目中,给我感觉很难做到所有业务都不超过三表join,那这里的问题就是,有什么相关的经验方法,可以尽量降低参与join的数据表?
    比如,在数据表里添加冗余字段,可以降低参与join的数据表数量,还有什么其他好的方法?


    2019-02-18
    17
  • 郭健
    老师,有几个问题还需要请教一下:
    1.上一章t1表100条数据,t21000条数据,mysql会每次都会准确的找出哪张表是合理的驱动表吗?还是需要人为的添加straight_join。
    2.像left join这种,左边一定是驱动表吧?以左边为标准查看右边有符合的条件,拼成一条数据,看到你给其他同学的评论说可能不是,这有些疑惑。
    3.在做join的时候,有些条件是可以放在on中也可以放在where中,比如(t1.yn=1 和t2.yn=1)这种简单判断是否删除的。最主要的是,需要根据两个条件才能join的(productCode和custCode),需要两个都在on里,还是一个在on中,一个在where中更好呢?

    作者回复: 1. 正常是会自己找到合理的,但是用前explain是好习惯哈
    2. 这个问题的展开我放到答疑文章中哈
    3. 这也是好问题,需要分析是使用哪种算法,也放到答疑文章展开哈。

    新年快乐~

    2019-02-07
    8
  • 天王
    join语句的优化,NLJ算法的优化,MRR优化器会在join_buffer进行主键的排序,然后去主键索引树上一个个的查找,因为按照主键顺序去主键索引树上查找,性能会比较高,MRR优化接近顺序读,性能会比较高。BKA算法是对NLJ算法的优化,一次取出一批数据的字段到join_buffer中,然后批量join,性能会比较好。BKA算法依赖于MRR,因为批量join找到被驱动表的非聚集索引字段通过MRR去查找行数据

    作者回复: 👍

    2019-02-13
    6
  • 老杨同志
    我准备给
    t1增加索引c
    t2增加组合索引b,c
    t3增加组合索引b,c
    select * from t1 straight_join t2 on(t1.a=t2.a) straight_join t3 on (t2.b=t3.b) where t1.c>=X and t2.c>=Y and t3.c>=Z;

    另外我还有个问题,开篇提到的这句sql select * from t1 where a>=1 and a<=100;
    a是索引列,如果这句索引有order by a,不使用MRR 优化,查询出来就是按a排序的,使用了mrr优化,是不是要额外排序

    作者回复: 对,好问题,用了order by就不用MRR了

    2019-02-01
    5
  • asdf100
    最近遇到这个需求,in里面的值个数有5万左右,出现的情况很少但存在,这种情况怎么处理。?手动创建临时表再join?

    另外in内的值用不用手动排序?

    作者回复: 不需要手动排序

    不过5万个值太凶残了,语句太长不太好

    这种就是手动创建内存临时表,建上hash索引,填入数据,然后join

    2019-02-01
    2
    5
  • 涛哥哥
    老师,对于现在的固态硬盘,这样类似顺序读写的数据库优化,不就不起作用了啊?

    作者回复: 固态硬盘的顺序写还是比随机写快的

    2019-03-20
    4
  • Mr.Strive.Z.H.L
    老师您好,新年快乐~~

    关于三表join有一个疑惑点需要确认:

    老师您在评论中说到,三表join不会是前两个表join后得到结果集,再和第三张表join。
    针对这句话,我的理解是:
    假设我们不考虑BKA,就按照一行行数据来判断的话,流程应该如下(我会将server端和innodb端分的很清楚):
    表是t1 ,t2 ,t3。 t1 straight_join t2 straight_join t3,这样的join顺序。
    1. 调用innodb接口,从t1中取一行数据,数据返回到server端。
    2. 调用innodb接口,从t2中取满足条件的数据,数据返回到server端。
    3. 调用innodb接口,从t3中取满足条件的数据,数据返回到server端。
    上面三步之后,驱动表 t1的一条数据就处理完了,接下来重复上述过程。
    (如果采用BKA进行优化,可以理解为不是一行行数据的提取,而是一个范围内数据的提取)。

    按照我上面的描述,确实没有前两表先join得结果集,然后再join第三张表的过程。
    不知道我上面的描述的流程对不对?(我个人觉得,将innodb的处理和server端的处理分隔清晰,对于sql语句的理解,会透彻很多)

    作者回复: 新年快乐,分析得很好。

    可以再补充一句,会更好理解你说的这个过程 :
      如果采用BKA进行优化,每多一个join,就多一个join_buffer

    2019-02-02
    4
  • xxxl
    刚刚凌乱了的那个问题,经explain验证,explain SELECT a.* FROM sys_xxtx a JOIN baq_ryxx r ON a.ryid = r.ID WHERE a.dwbh = '7E0A13A14101D0A8E0430A0F23BCD0A8' ORDER BY txsj DESC LIMIT 0,20;
    使用的索引是txsj ;
    explain SELECT a.* FROM sys_xxtx a JOIN baq_ryxx r ON a.ryid = r.ID WHERE a.dwbh = '7E0A13A14101D0A8E0430A0F23BCD0A8' ORDER BY txsj DESC LIMIT 5000,20;使用的索引是dwbh ;
    然后回忆起了第10张:MySQL为什么有时候会选错索引?
    但是从扫描行数、是否使用排序等来看在 LIMIT 5000,20时候也应该优选txsj ?可是这个时候选择的索引是dwbh, 查询时间也大大缩短

    作者回复: 嗯,这个跟我们第十篇那个例子挺像的

    我们把limit 1 改成limit 100的时候,MySQL认为,要扫描到“100行那么多”,
    你这里是limit 5000,200, 这个5000会让优化器认为,选txsj会要扫“很多行,可能很久”

    这个确实是优化器还不够完善的地方,有时候不得不用force index~

    2019-02-01
    4
  • HuaMax
    前提假设:t1.c>=X可以让t1成为小表。同时打开BKA和MRR。
    1、t1表加(c,a)索引。理由:A、t1.c>=X可以使用索引;B、加上a的联合索引,join buffer里放入的是索引(c,a)而不是去主键表取整行,用于与表t2的t1.a = t2.a的join查询,不过返回SELECT * 最终还是需要回表。
    2、t2表加(a,b,c)索引。理由:A、加上a避免与t1表join查询的BNL;B、理由同【1-B】;C、加上c不用回表判断t2.c>=Y的筛选条件
    3、t3表加(b,c)索引。理由:A、避免与t2表join查询的BNL;C、理由同【2-C】

    问题:
    1、【1-B】和【2-B】由于select *要返回所有列数据,不敢肯定join buffer里是回表的整行数据还是索引(c,a)的数据,需要老师解答一下;不过值得警惕的是,返回的数据列对sql的执行策略有非常大的影响。
    2、在有join查询时,被驱动表是先做join连接查询,还是先筛选数据再从筛选后的临时表做join连接?这将影响上述的理由【2-C】和【3-C】

    使用straight_join强制指定驱动表,我会改写成这样:select * from t2 STRAIGHT_JOIN t1 on(t1.a=t2.a) STRAIGHT_JOIN t3 on (t2.b=t3.b) where t1.c>=X and t2.c>=Y and t3.c>=Z;
    考虑因素包括:
    1、驱动表使用过滤条件筛选后的数据量,使其成为小表,上面的改写也是基于t2是小表
    2、因为t2是跟t1,t3都有关联查询的,这样的话我猜测对t1,t3的查询是不是可以并行执行,而如果使用t1,t3作为主表的话,是否会先跟t2生成中间表,是个串行的过程?
    3、需要给t1加(a,c)索引,给t2加(c,a,b)索引。

    作者回复: 👍 很深入的思考哈
    1. select * ,所以放整行;你说得对,select * 不是好习惯;
    2. 第一次join后就筛选;第二次join再筛选;
    新春快乐~

    2019-02-02
    3
  • Destroy、
    BNL 算法效率低,建议你都尽量转成 BKA 算法。优化的方向就是给驱动表的关联字段加上索引;
    老师最后总结的时候,这句话后面那句,应该是给被驱动表的关联字段加上索引吧。

    作者回复: 对的,👍细致

    已经发起勘误,谢谢你哦,新年快乐

    2019-02-01
    3
  • xxxl
    order by cjsj desc limit 0,20 explain Extra只是显示 Using where ,执行时间 7秒钟
    order by cjsj desc limit 5000,20 explain Extra只是显示 Using index condition; Using where; Using filesort, 执行时间 0.1 秒
    有些许的凌乱了@^^@

    作者回复: 这正常的,一种可能是这样的:
       Using where 就是顺序扫,但是这个上要扫很久才能扫到满足条件的20个记录;
       虽然有filesort,但是如果参与排序的行数少,可能速度就更快,而且limit 有堆排序优化哦

    2019-02-01
    3
  • jane_pop
    老师,我想请教下,在inner join on后面加过滤条件,与呀where后面加过滤条件,性能是一样的吗?
    2019-07-17
    2
  • 唯她命
    存储过程 插入100万
    Query OK, 1 row affected (1 hour 53 min 57.59 sec)
    这么恐怖😱!
    2019-04-09
    2
    2
  • Mr.Strive.Z.H.L
    老师你好,今天在回顾这篇文章做总结的时候,突然有一个疑惑:

    我们假设t2的b上面有索引,该语句是左连接

    select * from t1 left join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;



    select * from t1 left join t2 on (t1.b=t2.b) and t2.b>=1 and t2.b<=2000;

    到底在内部执行流程上到底有什么区别??
    因为实际工作中左连接用得挺多的。
    (这篇文章都是直连,所以使用on和where最后的结果都一样,但是左连接就不是了)

    作者回复: 你这两个语句是一样的。。是不是第二个语句多了left?

    left join因为语义上要求所有左边表的数据行都必须存在结果里面,所以执行流程不太一样,我在答疑文章中说哈

    2019-02-13
    2
  • 天王
    BNL算法优化,BNL算法,如果读取的是冷表,而且量比较大,循环读取,第一次数据会进入old区域,如果一秒之后没有访问,不会移到LRU头部,大表join对io影响查询完就结束了,但是buffer pool需要大量的查询把冷数据冲掉。BNL算法有3个问题,1 多次扫描被驱动表,占用磁盘io 2 判断join会耗费大量的cpu资源 3 会热数据淘汰,影响buffer pool的命中率

    作者回复: 👍

    2019-02-13
    2
  • Geek_02538c
    过年了,还有新文章,给个赞。 另,where 和 order 与索引的关系,都讲过了,group by 是否也搞个篇章说一下。

    作者回复: 你说得对^_^ 第37篇就是,新年快乐

    2019-02-02
    2
  • Ryoma
    read_rnd_buffer_length 参数应该是 read_rnd_buffer_size,见文档:https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_rnd_buffer_size

    作者回复: 你说得对,多谢

    发起勘误了

    新年快乐

    2019-02-02
    2
  • poppy
    select * from t1 join t2 on(t1.a=t2.a) join t3 on (t2.b=t3.b) where t1.c>=X and t2.c>=Y and t3.c>=Z;
    老师,我的理解是真正做join的三张表的大小实际上是t1.c>=X、t2.c>=Y、t3.c>=Z对应满足条件的行数,为了方便快速定位到满足条件的数据,t1、t2和t3的c字段最好都建索引。对于join操作,按道理mysql应该会优先选择join之后数量比较少的两张表先来进行join操作,例如满足t1.a=t2.a的行数小于满足t2.b=t3.b的行数,那么就会优先将t1和t2进行join,选择t1.c>=X、t2.c>=Y中行数少的表作为驱动表,另外一张作为被驱动表,在被驱动表的a的字段上建立索引,这样就完成了t1和t2的join操作并把结果放入join_buffer准备与t3进行join操作,则在作为被驱动表的t3的b字段上建立索引。不知道举的这个例子分析得是否正确,主要是这里不知道t1、t2、t3三张表的数据量,以及满足t1.c>=X ,t2.c>=Y ,t3.c>=Z的数据量,还有各个字段的区分度如何,是否适合建立索引等。

    作者回复: 嗯 这个问题就是留给大家自己设定条件然后分析的,分析得不错哦

    2019-02-01
    2
  • 郭江伟
    select * from t1 join t2 on(t1.a=t2.a) join t3 on (t2.b=t3.b) where t1.c>=X and t2.c>=Y and t3.c>=Z;
    这个语句建索引需要考虑三个表的数据量和相关字段的数据分布、选择率、每个条件返回行数占比等
    我的测试场景是:
    t1 1000行数据 t2 100w行数据 t3 100w行,关联字段没有重复值,条件查询返回行数占比很少,此时索引为:
    alter table t1 add key t1_c(c);
    alter table t2 add key t2_ac(a,c);
    alter table t3 add key t3_bc(b,c);
    测试sql无索引是执行需要2分钟多,加了索引后需要0.01秒,加索引后执行计划为:
    mysql> explain select * from t1 join t2 on(t1.a=t2.a) join t3 on (t2.b=t3.b) where t1.c>=100 and t2.c>=10 and t3.c>=90;
    +----+-------------+-------+------------+------+---------------+-------+---------+---------------+------+----------+------------------------------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+-------+------------+------+---------------+-------+---------+---------------+------+----------+------------------------------------+
    | 1 | SIMPLE | t1 | NULL | ALL | t1_a | NULL | NULL | NULL | 1000 | 90.10 | Using where |
    | 1 | SIMPLE | t2 | NULL | ref | t2_ac | t2_ac | 5 | sysbench.t1.a | 1 | 33.33 | Using index condition; Using where |
    | 1 | SIMPLE | t3 | NULL | ref | t3_bc | t3_bc | 5 | sysbench.t2.b | 1 | 33.33 | Using index condition |
    +----+-------------+-------+------------+------+---------------+-------+---------+---------------+------+----------+------------------------------------+
    另外,select * 如果改成具体字段的话考虑覆盖索引 可能需要建立不同的索引。

    作者回复: 👍验证的结果最有说服力

    2019-02-01
    2
  • 脱缰的野马
    老师您好,“Multi-Range Read 优化 (MRR),这个优化的主要目的是尽量使用顺序读盘”
    我更底层的理解是:顺序读之所以更优,是不是因为innodb在查数据的时候,如果内存缓存的所有数据页都没有当前要查的数据,就会进行磁盘IO从磁盘将要查询的数据刷新到内存,而刷新的时候又是按页刷新到内存(那对于数据页肯定是按主键顺序递增保存的),也就是说后续的顺序读在内存中的命中率会高,进行磁盘IO的次数也就会降低,并且如果不是随机查,内存缓存数据页的刷新也不会非常频繁,进而起到了更优的效果。请问老师这样理解是否正确呢?
    2019-12-03
    1
收起评论
59
返回
顶部