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

最近线上遇到一个问题，后台一个查询把服务给整挂了，然后找了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问题总结：

• 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 分析一下。