Using join buffer(Block Nested Loop) 优化

        最近在优化项目中的慢查询SQL，发现了一个Using Join Buffer 引起的慢查询，经过细致排查，终于解决了这个问题，将优化方式记录一下。

1，原始SQL

 这是最开始的SQL，分页查询最开始的50条记录竟然话费了近2分钟的时间，这怎么能忍。

2，原始SQL分析 

对原始SQL进行EXPLAIN分析，结果如图。

发现eqtType和ps这两张表在Extra中出现 Using join buffer(Block Nested Loop) 。

Block Nested Loop 不了解 的可以自行 百度一下，它实际上就是多层嵌套循环。

现在开始分析用到的6张 表之间的关系。

mp -> am 

am -> eqtType 

mp -> pn 

mp -> mt 

mp -> ps 


发现mp和另外四张表都有直接联系，而和eqtType没有直接联系，可能是由于这张表引起的。

下面验证猜想，将 am表和eqtType表单独提出来，发现速度并不慢（耗时在30毫秒左右，我就不贴图了），然而合在一起却很慢。

分析可能是由于eqtType和am表联合查询后，又与mp表联合查询，而eqtType与mp表又没有外键关联，所以就使用am与mp的外键间接进行连接。等于mp表与am Inner Join eqtType 的结果进行了两层的嵌套循环。而mp表和am表都有280多w条数据，这才是慢的原因。

回到项目中，发现查询出的字段eqtType.f_etlName在页面上并没有用，实际这个字段查出来没什么用，果断删除，改写SQL如下。

3，修改SQL

 将eqtType表删除，通过Exists的方式与am建立连接，保证业务逻辑和以前SQL的一致。这样就发现eqtType表的Using join buffer(Block Nested Loop)消失了。测试一下查询耗时。

 

非常的nice,耗时31ms，优化成功。

可能注意到上面的分析结果中，ps表还是有Using join buffer(Block Nested Loop)，这个是由于OR这个关键字引起的，因为业务规则的需要，这个必须要有，而且由于ps表与mp表直接关联，查询性能 并没有影响，所以这个就放过它了。

优化总结，JOIN相关的表要有直接外键关联关系性能才能表现良好，尽量避免间接关联。