一、序

1： 数据库版本 My SQL 5.67

2：业务

A、B两个表，需要查出A表中有，B表中没有的数据。。

二、尝试过程

1：SQL语句

SELECt a.claim_no from t_qgws_sale_check a 
left   join   t_swcas_bu_qgws_claim b on a.claim_no=b.claim_no
where plan_code =''  and b.claim_no is null;

(1)SQL分析

从上面的sql语句执行计划来看：
1：两个表的数据都是10万级的（感 觉不多）。
2：两个表也都用到索引，都是覆盖索引。

(2)执行结果

数据却是出不来的。。
我们注意到这里，什么是 Block Nested Loop呢。。大概意思就是循环匹配吧，这里不细说。 参考

寻思着，Block Nested Loop要怎么避免呢。。

2:SQL语句

EXPLAIN
SELECt * from t_qgws_sale_check a
where a.plan_code='' and not EXISTS (SELECt 1 from t_swcas_bu_qgws_claim b where  a.claim_no=b.claim_no)
;

(1)SQL解析

1：把left join 换成了 not exists
2：从执行计算看出，Block Nested Loop没有再出现。

(2)执行结果

数据依然没有出来。
可想而知，这里的条件 not EXISTS (SELECt 1 from t_swcas_bu_qgws_claim b where a.claim_no=b.claim_no) 是这个嵌套查询。。
要执行27万次，其本上跟 Block Nested Loop 没有区别。
回想，left join不应该会出现 Block Nested Loop，感觉left join 的实现不应该这么low。

3:SQL3 换个思维方式

(1)：把B表复制一份到C并且只复制需要的字段。并且把claim_no做为主键。

create table t_qgws_sale_check2 as 
select claim_no from t_swcas_bu_qgws_claim where CLAIM_CODE is not null;

(2):把C表替换 B表去查询，把前面两次的查询的语句都试一次。

(3):left join版本

(4):not exist 版本

(5)：分析

1:查询计算里面也没再出现 Block Nested Loop
2：无论是left join 还是 not exists 都是能出来的，
3：从耗时来看，left join 优化于 not exist。

PS:
为什么换了个临时表就能出来（我已经排除了锁表的情况），理论上业务表使用的是覆盖索引，也不需要去回表查询。效果等同于主健。
无解。

4： 尝试其它查询方式

  SELECt
	a.claim_no
FROM
	t_qgws_sale_check a
WHERe
	a.plan_code = ''
AND a.claim_no NOT IN (
	SELECt
		a.claim_no
	FROM
		t_qgws_sale_check a
	INNER JOIN t_swcas_bu_qgws_claim b ON b.claim_no = a.claim_no
	WHERe
		plan_code = ''
) 

使用表A与表B关联查询得出结果集C， 再用表A 查出 不在C （使用not in ）,结果如下图。

1：速度很快，2秒多就出结果了。
2：避免了使用了left join,当然也避免了 Block Nested Loop。

5：上面的SQL，精简一下写法

SELECt
	a.claim_no
FROM
	t_qgws_sale_check a
WHERe
	a.plan_code = ''
AND a.claim_no NOT IN (
	SELECt
		 claim_no
	FROM
		 t_swcas_bu_qgws_claim   
) 

数据又查不出来了。。

6：再次尝试

前面讲到的 not in 的写法。如以下语句 ,如果把 标色部分换成 a.claim_no ,结果又查不出来了。。那么感觉我已经能解释通了。。

EXPLAIN SELECt
a.claim_no
FROM
t_qgws_sale_check a
WHERe
a.plan_code = ‘’
AND a.claim_no NOT IN (
SELECt
a.claim_no
FROM
t_qgws_sale_check a
INNER JOIN t_swcas_bu_qgws_claim b ON b.claim_no = a.claim_no
WHERe
plan_code = ‘’
)

结论：神奇的My SQL

分晓，关键还是看执行计划与时间复杂度分析

第一种情况，数据出不来。

使用了嵌套查询，计算的复杂度超地100亿次，出不来理所当然。
时间复杂度
O=Row1 * Row2;

第二种情况，数据出不来

与第一种类似，虽然执行计划里没有BNL，但实际还是嵌套。
时间复杂度
O=Row1 * Row2;

第三种 使用not exist ,数据出不来。

与第一、二种无区别。
时间复杂度
O=Row1 * Row2;

第四种 使用临时表，claim_no为主键，not in 或者 not exists 都可以查出结果。

从以下截图可以看到，表b使用的是主键 ，rows的数量是1。
时间复杂度
1：主键的二分查询法凑效
2：用的是主键，没有回表查询。
以上大大减少了时间的复杂度。

第五种：借鉴第四种方法，使用 not in 和主键，可以查出结果。

时间复杂度
1：主键的二分查询法凑效
2：用的是主键，没有回表查询。
以上大大减少了时间的复杂度。

第六种 在第五种的基本出，把C表的结果换成使用b.claim_no，查不出结果。

EXPLAIN SELECt
a.claim_no
FROM
t_qgws_sale_check a
WHERe
a.plan_code = ‘’
AND a.claim_no NOT IN (
SELECt
b.claim_no
FROM
t_qgws_sale_check a
INNER JOIN t_swcas_bu_qgws_claim b ON b.claim_no = a.claim_no
WHERe
plan_code = ‘’
)

**我猜的：**时间复杂度又变回嵌套了。。细看，在extra里使用的的信息与种五种有点差异。。

总结与吐槽

总结

1: left join对主键有做特别的优化;或者 多次嵌套的时候，使用了二分查找法。 或者2：left join 对于一般的索引没有使用 二分查找法（简直就是个bug）。

吐槽

不管SQL的执行计划是怎么样，都可以结合结果猜出它是怎么样的一机制。
1:即便是这样,我觉得很被动，我需要记住这些mysql的尿性，然后去写sql避开这些坑。。
2:对于长年使用oracle的我来讲，感觉mysql需要改善的地方还挺多，希望高版本好用一点。。。

补充

试过了使用了 8.0的版本，left join 已经优化了。不再有以上问题 ，欧力给。。