背景

最近一个后台功能列表，业务人员反馈查询和导出速度非常慢。

通过定位发现列表查询和数据导出都是使用的同样的一个连表查询SQL。

这个功能刚上线不久，起初查询和导出速度都是蛮快的，把这个SQL放到测试环境也是挺快的。

对比了一下测试环境和生产环境相关表结构都是一样的，之后我们把目光放在了数量的问题上面，但是几张关联表的数据量也不大，不到1w的数据量为何会这么慢呢。

排查

通过Explain发现，连表查询中的table c没有使用到索引且是全表扫描。另外在Extra中特别说明了Using join buffer (Block Nested Loop)。

其中table c中的filtered=100% 表示右表没有应用索引下推（ICP），因为where条件没有索引。

另外Using join buffer (Block Nested Loop)是因为右表没有在join列上建索引导致嵌套循环。

索引下推：

解决

通过对table c中的连接字段content_id和user_no分别加上了索引，

加上索引后的执行计划如下 

需要注意

知识延伸

MySQL使用嵌套循环算法或其变种来进行表之间的连接。

在5.5版本之前，MySQL只支持一种表间关联方式，也就是嵌套循环(Nested Loop)。如果关联的表数据量很大，那么join关联的时间会很长。在5.5版本以后，MySQL引入了BNL算法来优化嵌套循环。

1.嵌套循环连接算法(Nested-Loop Join Algorithm)

一个简单的嵌套循环连接（NLJ）算法从循环中的第一个表中逐行读取一行，将每行传递给处理连接中下一个表的嵌套循环。 这个过程会重复多次，因为还有剩余的表被连接。

假定要使用以下连接类型执行三个表t1，t2和t3之间的连接：

Table   Join Type
t1      range
t2      ref
t3      ALL

如果使用一个简单的NLJ算法，连接就像这样处理：

for(row_1 in table_1){
    for(row_2 in table_2){
        if(row_1,row_2满足join条件){
            ...

            for(row_n in table_n){
                if(row_1,row_2...row_n都满足join条件){
                    把row_1,row_2...row_n的join结果加到结果集

                }

            }    

      }       

如图所示

 这种算法缺陷也很明显，随着join表数量的增加，计算量呈指数上升。如果其中出现了一张数据量很大的表，对整个过程的效率也影响很大。

于是，mysql5.5对这个算法进行了优化，新增了Index Nested-loop Join，Block Nested-loop Join，其中Index Nested-loop Join是针对有索引的情况，而Block Nested-loop Join是针对没有命中索引的情况

 由于索引的效率要比逐条循环效率高，所以当使用索引联表时，能大大加快查询速度，但是索引也不是万能的，如果你需要取索引以外的字段，那么依旧需要回到表中查出相应的数据

 而对于没有索引的情况，优化的效果就非常有限，只是从逐条匹配变为先读取一批放到缓存中，然后缓存批量匹配，能够一定程度上减少IO操作的开销

由于NLJ算法从外循环向内循环一次传递一行，因此它通常会多次读取在内循环中处理的表。

本篇文章如有帮助到您，请给「翎野君」点个赞，感谢您的支持。

首发链接：https://www.cnblogs.com/lingyejun/p/16074343.html