利志分享
view_headline
go教程
clickhouse教程
python教程
shell教程
原创杂文
go教程
clickhouse教程
python教程
shell教程
原创杂文
PHP中高级工程师面试重点讲解视频课程
Go快速入门浅显易懂视频教程-基础篇
Go快速入门浅显易懂视频教程-中级篇
线上mysql出现Block Nested-Loop Join问题
原创杂文 / 时间:2021-02-18 21:43:49 / 阅读:47 / 分享:0
最近线上遇到一个问题,后台一个查询把服务给整挂了,然后找了dba看了下sql慢查询,我们explain一下结果。  一个连表查询出现了:Using join buffer (Block Nested Loop)重新复习一下资料,整理下经验。官方资料如下: https://dev.mysql.com/doc/refman/5.7/en/nested-loop-joins.html mysql涉及到的join算法:Nested-Loop Join 和 Block Nested-Loop Join 关于:Nested-Loop Join 在mysql中,Nested-Loop Join是嵌套循环连接,看下官方例子: select t1.*,t2.*,t3.* from t1 left join t2 on t1.id = t2.id left join t3 on t2.id = t3.id; 关于这个sql,使用嵌套循环连接,则实现算法如下: for each row in t1 matching range { for each row in t2 matching reference key { for each row in t3 { if row satisfies join conditions, send to client } } } 这个会造成t1 * t2 * t3 次查询,其实也就是我们说的笛卡尔积。 关于:Block Nested-Loop Join,是Nested-Loop Join的一种优化,叫缓存块嵌套循环连接,缓存嵌套循环连接是通过一次性缓存多条数据,把参与查询的缓存的列缓存到join buffer中,然后拿join buffer里面的数据匹配和内层数据进行匹配,从而减少内层循环的次数。 先简单看下join buffer 说明 join buffer size默认是256k,可以配置 join buffer会缓存所有参与查询的列而不是只有join的列 我们再看下Nested-Loop Join实现的伪代码: for each row in t1 matching range { for each row in t2 matching reference key { store used columns from t1, t2 in join buffer if buffer is full { for each row in t3 { for each t1, t2 combination in join buffer { if row satisfies join conditions, send to client } } empty join buffer } } } if buffer is not empty { for each row in t3 { for each t1, t2 combination in join buffer { if row satisfies join conditions, send to client } } } 这个查询,如果S指的是t1, t2组合在缓存中的大小,C是这些组合在buffer中的数量,那么t3表被扫描的次数是:(S * C)/join_buffer_size + 1,这个总的查询次数则:t1 * t2 次数 + t3 被扫描次数。 了解了join的原理,我们再来回来看线上的这个问题。我们有两个表:  一个打分表,一个考试表。我们的需求是查询考试打过分的学生列表,查询考试未打过分列表。然后我们写了两个sql,如下: 查询打分的sql: explain select a.*,b.* from exam a left join score b on a.id = b.exam_id where b.score is not null order by a.id desc limit 10; 查询未打分的sql: select a.*,b.* from exam a left join score b on a.id = b.exam_id where b.score is not null order by a.id desc limit 10; 我们对两个sql都进行explain  查询打分的没有出现嵌套循环连接,因为使用到索引,mysql已经知道join的数据没有不用再扫描。查询未打分的出现嵌套循环连接,mysql没使用到索引,mysql join的部分没有数据会扫描b表所有的数据。我们线上当时考试表是50000数据,但是打分的表才700多条数据,由于查询b表的时候是扫描b的所有数据,所以造成线上造成了25000000多次扫描。对线上的连接表,我们一定要慎重,避免出现嵌套循环连接。 **关于这次mysql问题总结:** 1. join表,尽量用小结果集去连接大结果集,减少外出循环数据量,从而减少内层循环次数。 2. join表,如果实在需要大结果集连接小结果集,我们考虑先把大结果集和小结果集是否能够使用子查询来结果,当然这个还是要看需求是怎么样的,不一定我这里的子查询和你的需求是一样的,不能一概而论,但是只要出现嵌套查询连接,我们一定要优化,避免出现这种。(我们当时线上查询未打分的就通过子查询来处理了。我们的最后子查询:select * from exam where id not in (select exam_id from score) order by id desc limit 10;) 3. 可以考虑控制join buffer size 的大小。 4. jon连表,每个sql一定要记住用explain 分析一下。
按时间分类
2021-04-03
2021-03-07
2021-03-05
2021-03-04
2021-02-28
2021-02-21
2021-02-20
2021-02-18
2016-04-10
2016-03-28
2016-02-05
2015-11-22
2015-09-18
2015-09-16
2015-08-24
2015-08-16
2015-08-08
2015-02-27
2014-11-25
2014-11-02
2014-08-27
2014-07-25
2014-06-04
2014-05-25
2014-05-13
2014-05-06
2014-04-20
2014-02-23
2014-02-12
2014-02-10
2014-01-17
2014-01-16
2014-01-15
2014-01-12
2014-01-04
2013-08-13
2013-07-12
2013-07-09
2013-07-06
2013-07-03
2013-07-01
2013-06-29
2013-06-28
2013-06-27
2013-06-26
2013-06-25