线上mysql出现Block Nested-Loop Join问题

2022-04-25 08:59:43 浏览数 (2)

最近线上遇到一个问题,后台一个查询把服务给整挂了,然后找了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,使用嵌套循环连接,则实现算法如下:

代码语言:javascript复制
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实现的伪代码:
代码语言:javascript复制
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问题总结:

  • join表,尽量用小结果集去连接大结果集,减少外出循环数据量,从而减少内层循环次数。
  • join表,如果实在需要大结果集连接小结果集,我们考虑先把大结果集和小结果集是否能够使用子查询来结果,当然这个还是要看需求是怎么样的,不一定我这里的子查询和你的需求是一样的,不能一概而论,但是只要出现嵌套查询连接,我们一定要优化,避免出现这种。(我们当时线上查询未打分的就通过子查询来处理了。我们的最后子查询:select * from exam where id not in (select exam_id from score) order by id desc limit 10;)
  • 可以考虑控制join buffer size 的大小。
  • jon连表,每个sql一定要记住用explain 分析一下。

0 人点赞