MySQL一次Join表的性能优化分析，性能相差上千倍

背景

在某技术群里有人发了两条sql，问为什么性能有这么大的差距，下面是当时的问题

线索

SQL 1

SELECT * FROM T1 LEFT JOIN T2 ON T1.f1 = T2.f1 AND T2.f2 IS NULL WHERE T1.f1 = '2875205';      

explain

SQL 2

SELECT * FROM T1 LEFT JOIN T2 ON T1.f1 = T2.f1 WHERE T1.f1 = '2875205' AND T2.f2 IS NULL;      

提问：T1.f1，T2.f1，T2.f2都有索引，T1表大约3万行，T2表大约100万行（T2.f2字段90%是null），这两条sql的结果集是一模一样的，为什么第一条sql要3秒，第二条sql只有1毫秒？

分析

性能分析很多情况下看explain能看出很多问题，我们首先看一下两条sql的explain，首先都是使用T1.f1的idx_f1索引，type是ref，row是1·，可以先忽略，着重看第二行

sql1：

因为join条件里有T2.f2 is null，所以选择了T2.f2的索引idx_f2，扫描行数是1+1*90w，就是说T1每匹配一行，都需要扫描T2的90%的行，虽然被驱动表走了索引，但又不完全走，约等于BNL算法被驱动表全表扫描

sql2：

T1.f1 = T2.f1选择了T2.f1的索引idx_f1，扫描行数是1+n（因为群友没提供满足条件说里面有多少行满足，但必定远小于90w）且是索引查询

扩展知识

MySQL JOIN算法

Nested-Loop Join Algorithm（NLJ）

• 从驱动表取符合条件的一行
• 根据驱动表的数据查询被驱动表
• 返回符合条件的数据为结果集

整个过程类似于嵌套循环

https://dev.mysql.com/doc/refman/8.0/en/nested-loop-joins.html#nested-loop-join-algorithm

Block Nested-Loop Join Algorithm（BNL）

• 查询驱动表的数据写到join_buffer（ join_buffer_size 确定大小），写不下就分批执行以下操作
• 查询被驱动表的数据匹配join_buffer

https://dev.mysql.com/doc/refman/8.0/en/nested-loop-joins.html#block-nested-loop-join-algorithm

Hash Join Optimization

MySQL 8.0.18新增的算法，具体类似BNL，并且删除了BNL算法，只是会将join_buffer的数据转成散列表存储

• 确定大小）存储成散列表，写不下就分批执行以下操作
• 查询被驱动表的数据匹配join_buffer，因为是散列表，所以时间复杂度是O(1)

https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html