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

44 | 答疑文章(三):说一说这些好问题

林晓斌 2019-02-22
这是我们专栏的最后一篇答疑文章,今天我们来说说一些好问题。
在我看来,能够帮我们扩展一个逻辑的边界的问题,就是好问题。因为通过解决这样的问题,能够加深我们对这个逻辑的理解,或者帮我们关联到另外一个知识点,进而可以帮助我们建立起自己的知识网络。
在工作中会问好问题,是一个很重要的能力。
经过这段时间的学习,从评论区的问题我可以感觉出来,紧跟课程学习的同学,对 SQL 语句执行性能的感觉越来越好了,提出的问题也越来越细致和精准了。
接下来,我们就一起看看同学们在评论区提到的这些好问题。在和你一起分析这些问题的时候,我会指出它们具体是在哪篇文章出现的。同时,在回答这些问题的过程中,我会假设你已经掌握了这篇文章涉及的知识。当然,如果你印象模糊了,也可以跳回文章再复习一次。

join 的写法

在第 35 篇文章《join 语句怎么优化?》中,我在介绍 join 执行顺序的时候,用的都是 straight_join。@郭健 同学在文后提出了两个问题:
如果用 left join 的话,左边的表一定是驱动表吗?
如果两个表的 join 包含多个条件的等值匹配,是都要写到 on 里面呢,还是只把一个条件写到 on 里面,其他条件写到 where 部分?
为了同时回答这两个问题,我来构造两个表 a 和 b:
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(37)

  • 宝玉
    老师,BNl算法,如果where条件中有驱动表的过滤条件,也不会在join时候全部载入内存吧?

    作者回复: 对,驱动表现过滤,然后进join buffer

    2019-02-25
    5
  • 还一棵树
    看到 BNL 算法,你就应该知道这条语句的执行流程其实是这样
    文章中的流程是写错了?还是我理解的有问题
    1、如果是a表数据放入join buffer,根据b的每一条记录去判断是否在a中 如果在则保留记录
    这个更像是b left join a。 而不是a left join b
    2、如果按照这个流程,比如a里面有2行重复的数据, 如果拿b的数据在a中判断,存在则保留,那结果集只有一条数据, 而按照a left join b 会出现2条结果的

    作者回复:
    “如果按照这个流程,比如a里面有2行重复的数据, 如果拿b的数据在a中判断,存在则保留,那结果集只有一条数据,”

    不会呀,你看它是这样的:
    假设join buffer中有两个行1

    然后被驱动表取出一个1,
      跟join buffer中第一个1比较,发现满足条件,放到结果集;
      跟join buffer中第二个1比较,发现满足条件,放到结果集;
     
      是得到两行的

    2019-02-26
    4
  • 白永伟
    老师,关于备库自增id我有一个问题。既然binlog不管是statement模式还是row模式,里面的insert语句跟着的自增id都是固定的。那假如发生主备切换,备库变成主库后,客户端往新主库里插入数据时,自增id的起始值是多少,有没有可能跟已有的记录id冲突?尤其是备库还没有处理完同步过来的binlog就开始接受客户端请求时。如果要求备库必须处理完binlog才能接受客户端请求,那么怎么保证主备切换的过程中,不影响用户使用。谢谢。

    作者回复: “自增id的起始值是多少,有没有可能跟已有的记录id冲突?”
    如果没有主备延迟就不会出现;

    “尤其是备库还没有处理完同步过来的binlog就开始接受客户端请求时。” , 对,这种情况就会。

    “如果要求备库必须处理完binlog才能接受客户端请求,那么怎么保证主备切换的过程中,不影响用户使用” 一般都是有这个要求的。要尽量减少影响,就是控制主备延迟。

    2019-02-22
    4
  • Dovelol
    老师,看评论包括您的回复说“ left join 后加上 where 的话, 肯定会被优化器优化成 join where 的形式, 那是否下次写 left join ..where 的时候, 不如直接写成 join .. where”,这个也是分情况的吧比如还是文章中的2张表,select * from a left join b on(a.f1=b.f1) where (a.f2=2);/*Q5*/和select * from a join b on(a.f1=b.f1) where (a.f2=2);/*Q6*/ 这个left join和join的语意和返回结果都不一样,怎么能直接写成join呢,如果是where b.f2=xx 的where条件可以直接写成join因为根据结果是不需要left的。

    作者回复: 嗯 我的意思是,如果where条件里面,用到了b.f2的判断,干脆就直接写成join,不需要left join了


    如果业务逻辑需要left join, 就要把条件都放到on里面

    业务逻辑正确性还是优先的

    2019-02-25
    3
  • 千木
    老师您好,join使用join_buffer和内存区别那个问题的第一点解释我还是有些纳闷,你说由于从磁盘拿数据到内存里面会导致等等的性能问题我能够理解,但是说即使使用nbl算法也会涉及到从磁盘拿数据到内存吧,所以这点导致两种算法执行差异貌似不太合理,您觉得呢?

    作者回复: BNL算法拿的数据是确定的只会拿一次(遍历一遍)
    而simple nested loop join是会遍历多次的

    2019-02-23
    3
  • 龙文
    老师你好,我在第21讲求助了一个死锁问题,当时你回复说后面会解答,不过我浏览了下后续文章没找到解答,所以再次求助下。ps:用的阿里云的rds,提了工单没效果啊
    作者回复: 有的,你看一下第40篇 “insert 唯一键冲突”这一段

    ps:我已经离开阿里云挺久的了 😆

    ---------------------------------------------
    谢谢老师,我看了第40篇,还是有地方不太明白,再打扰下
    mysql 版本5.6
    隔离级别为rc
    CREATE TABLE `uk_test` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `a` int(11) NOT NULL,
      `b` int(11) NOT NULL,
      `c` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `uk_a_b` (`a`,`b`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4
    表中数据:
    +----+---+---+---+
    | id | a | b | c |
    +----+---+---+---+
    | 1 | 1 | 1 | 2 |
    | 6 | 1 | 2 | 1 |
    +----+---+---+---+
    sql:执行顺序
    session1:begin;
    session2:begin;
    session1:select * from uk_test where a = 1 and b = 1 for update;
    session2:select * from uk_test where a = 1 and b = 1 for update;
    session1:insert into uk_test (a,b,c) values(1,1,2) on duplicate key update c = 2;
    session2:ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

    我的疑问是:
    1.rc隔离级别下对唯一键的insert也会加next-key lock吗?

    2.死锁日志显示
      session 1已经成功加上行锁(lock_mode X locks rec but not gap),

      session 2在等待同一个行锁(lock_mode X locks rec but not gap waiting),

      session1这时因为等待lock_mode X waiting而死锁。
      这里的lock_mode X waiting是指next-key lock吗?
      如果是的话,没想明白这里怎么形成资源循环等待了?
    我的猜测是session1 这时持有行锁,要next-key lock 所以要去加gap锁。session 2持有gap锁在等行锁。但如果是这样为什么session2 在rc下select for update,且记录存在时会加gap锁?还有gap锁加锁不是不互斥吗?
     

    作者回复: 1. 会
    2. 你这里
      session 1 成功加锁一个record lock;

      session 2执行的是一个select 语句,而且a=1 and b=1就只锁一行(a,b上有联合唯一索引),这里就是要申请一个记录行锁(but not gap waiting)。
    这里虽然没有加锁成功,但是已经加入了锁队列(只是这个锁是处于等待状态)
      ---这时候队列里面有两个锁对象了

    然后session 1 再insert失败的时候,就要加next-key lock,(注意这个锁对象跟第一个锁对象不同)。
       然后死锁检测看到,2号锁在等1号锁;3号要等2号,而3和1又是同一个session,就认为是死锁了。

    2019-02-23
    3
  • 大宝
    老师你好,看了你回复 @彼得.林 时说,“在线服务最好不要让索引树超过4层“;那么有没有什么好的方法或者工具可以查看当前表的层数吗?

    作者回复: innblock 可以了解下😆

    2019-04-06
    2
  • 梦康
    😂留言的人太多,辛苦老实答疑了。虽然我的问题没能被翻牌子

    作者回复: 不好意思,确实你的问题比较难一些

    最近在做收尾的工作,后面一定会把问题都清理掉的哈。

    你的问题质量高,是我喜欢回答的问题类型😆

    2019-02-25
    2
  • yhui
    我真的很迫切需要你的帮助,我是台湾人,遇到一个很神奇的问题
    where in a(1,2,3,5,7,8,9) and b in(3) and c in(1,2) order by id desc
    我建了一个abc联合索引,发现这语句尽然没用上联合索引 很奇怪,in里删一两个值又能选对索引,这in有什么规律 奥秘吗?多谢指点,我邀请我同事都买了你的课程

    作者回复: 你可否把表结构、插入数据语句都贴一下?

    就是有没有稳定的复现方法(带上MySQL版本号)

    2019-08-23
    1
  • godtrue
    这篇答疑很实用呀!第一个问题之前也发现有这样的现象,不过解释不清楚为什么,不过按照自己想要的也算使用对了left join 。
    感谢老师,专栏接近尾声,现在回想一下,确实增加了不少见识,不过由于记忆不太好,加之有些知识消化的不好有些已经模糊啦😄
    第一遍结束,再来第二遍,把实验都操作操作,应该会好一些。
    感谢😊
    2019-08-10
    1
  • 彼得.林
    不知道老师还会不会回复问题:
    b+树索引结构的层次和表数据量的关系是怎么样的?也就是说15万的数据量是三层结构?达到多少数据量是四层次?
    一般在线服务中一个表的数据量一般多大合适?

    作者回复: 你可以这么理解, N层放不下的时候,就增加一层来放。

    这个行为是由页分裂触发的
    在线服务最好不要让索引树超过4层

    2019-03-16
    1
  • Chris
    这两天在线上遇到一个比较诡异的事情,突然有几分钟连不上MySQL,通过error日志和监控的processlist显示,MySQL把很多链接都kill掉了,但处于sleep状态和show status的语句没有kill,看监控的资源使用情况不是很高,只是innodb rows read指标特别高,现在完全是没头绪了

    作者回复: 看看是不是有什么外部工具在工作

    2019-03-15
    1
  • 龙文
    明白了 谢谢老师!

    作者回复: 👍

    2019-02-24
    1
  • 龙文
    老师你好,我在第21讲求助了一个死锁问题,当时你回复说后面会解答,不过我浏览了下后续文章没找到解答,所以再次求助下。ps:用的阿里云的rds,提了工单没效果啊

    作者回复: 有的,你看一下第40篇 “insert 唯一键冲突”这一段

    ps:我已经离开阿里云挺久的了 😆

    2019-02-23
    1
  • 夜空中最亮的星(华仔)
    这么快就要结束,好快啊

    作者回复: 跟进得很快啊大家😆

    2019-02-22
    1
  • 万勇
    感谢老师上一期的解答,还请教一个分区表的问题,分区表创建的聚集索引是分区本地维护的吧,但是主键索引要保证全局唯一性。那分区和主键索引之间是不是要建立一种关系?另外分区表如果我们创建普通索引,按道理可以分区创建的,分区维护自己的普通索引,各分区之间互不影响。

    作者回复: 就是我这篇末尾建议的几种建表方法,就是建立联系了

    2019-02-22
    1
  • 长期规划
    老师,你讲了Innodb和Memory引擎使用场景,那MyIsam引擎呢,什么情况下会用到呢,感觉应用场景很少啊
    2019-11-24
  • Omer
    老师你好,我是一员开发同学,我花了一个月的时间疯狂从你这边补习Mysql的知识,收获很大,首先很感谢你花了那么多精力传授功力!!现在我有一个很疑惑的问题想要请教一下,希望你看到能回复,毕竟我算是很晚才来的学生了~~就是我看高性能mysql里面说的:在可重复读的隔离级别下, InnoBD只查找版本早于当前事务版本的数据行,这样可以确保事务读取的行,要么是在事务开始前已经存在,要么是事务自身插入或者修改过的。 但是又看到里面说,快照读是读执行select时候已经全部提交过的最新的数据,这里难道不就跟前面那句那个事务版本的互相冲突了吗?? 而且我自己也测试了,我开启一个事务A,先sleep5秒,再执行全表查询,然后在事务A查询前执行另一个事务B,往里面插入了数据,这个时候我看到事务A查出来的数据是有事务B新插入的,这就让我对前面那句事务版本产生了很大的疑惑 - -。很晚了,不知道老师您还会不会看到,希望你看到了能帮我解答一下,再次感谢你耐心传教了那么多篇好文~~
    2019-10-16
  • eden.tang
    老师好,有个问题想请教一下。
    实际行排重后是13万,而count(distinct)确有38万为何

    38万
    select count(distinct(tid)) from tb_logs where ch='a'

    13万
    select count(*) from (select distinct(tid) from tb_logs where ch='a') t

    ch为其它的数据都正常,几年下来也一直正常,今天突然数据异常,就ch='a'这个条件的数据。
    2019-10-11
  • eden.tang
    实际行排重后是13万,而count(distinct)确有38万为何

    38万
    select count(distinct(tid)) from tb_logs

    13万
    select count(*) from (select distinct(tid) from tb_logs) t
    2019-10-11
收起评论
37
返回
顶部