44 | 答疑文章(三):说一说这些好问题
该思维导图由 AI 生成,仅供参考
join 的写法
- 深入了解
- 翻译
- 解释
- 总结
SQL join语句执行顺序和性能优化是本文的主题。作者通过回答读者在评论区提出的问题,深入讨论了使用left join时左表是否一定是驱动表、join语句中条件放在on部分和where部分的区别,以及left join语义下被驱动表字段放在where条件中的影响。通过实际案例和执行计划的分析,详细解释了这些问题的原理和执行过程。文章突出的技术特点在于对SQL join语句执行流程和优化器的改写策略进行了深入剖析,帮助读者更好地理解SQL语句的执行逻辑和优化过程。此外,还讨论了distinct 和 group by的性能、备库自增主键问题以及分区表t创建自增主键等问题。通过这些讨论,读者可以快速了解SQL join语句的执行细节和优化策略,对于提升SQL查询性能和编写高效的SQL语句具有一定的参考价值。
《MySQL 实战 45 讲》,新⼈⾸单¥68
全部留言(57)
- 最新
- 精选
- 玉哥老师,BNl算法,如果where条件中有驱动表的过滤条件,也不会在join时候全部载入内存吧?
作者回复: 对,驱动表现过滤,然后进join buffer
2019-02-25239 - 龙文老师你好,我在第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-231132 - 还一棵树看到 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-2624 - 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-2522 - 大宝老师你好,看了你回复 @彼得.林 时说,“在线服务最好不要让索引树超过4层“;那么有没有什么好的方法或者工具可以查看当前表的层数吗?
作者回复: innblock 可以了解下😆
2019-04-0616 - 梦康😂留言的人太多,辛苦老实答疑了。虽然我的问题没能被翻牌子
作者回复: 不好意思,确实你的问题比较难一些 最近在做收尾的工作,后面一定会把问题都清理掉的哈。 你的问题质量高,是我喜欢回答的问题类型😆
2019-02-2516 - 千木老师您好,join使用join_buffer和内存区别那个问题的第一点解释我还是有些纳闷,你说由于从磁盘拿数据到内存里面会导致等等的性能问题我能够理解,但是说即使使用nbl算法也会涉及到从磁盘拿数据到内存吧,所以这点导致两种算法执行差异貌似不太合理,您觉得呢?
作者回复: BNL算法拿的数据是确定的只会拿一次(遍历一遍) 而simple nested loop join是会遍历多次的
2019-02-23614 - 白永伟老师,关于备库自增id我有一个问题。既然binlog不管是statement模式还是row模式,里面的insert语句跟着的自增id都是固定的。那假如发生主备切换,备库变成主库后,客户端往新主库里插入数据时,自增id的起始值是多少,有没有可能跟已有的记录id冲突?尤其是备库还没有处理完同步过来的binlog就开始接受客户端请求时。如果要求备库必须处理完binlog才能接受客户端请求,那么怎么保证主备切换的过程中,不影响用户使用。谢谢。
作者回复: “自增id的起始值是多少,有没有可能跟已有的记录id冲突?” 如果没有主备延迟就不会出现; “尤其是备库还没有处理完同步过来的binlog就开始接受客户端请求时。” , 对,这种情况就会。 “如果要求备库必须处理完binlog才能接受客户端请求,那么怎么保证主备切换的过程中,不影响用户使用” 一般都是有这个要求的。要尽量减少影响,就是控制主备延迟。
2019-02-22210 - 彼得.林不知道老师还会不会回复问题: b+树索引结构的层次和表数据量的关系是怎么样的?也就是说15万的数据量是三层结构?达到多少数据量是四层次? 一般在线服务中一个表的数据量一般多大合适?
作者回复: 你可以这么理解, N层放不下的时候,就增加一层来放。 这个行为是由页分裂触发的 在线服务最好不要让索引树超过4层
2019-03-1639 - 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-2327