mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录

NOT IN、JOIN、IS NULL、NOT EXISTS效率对比

语句一：select count(*) from A where A.a not in (select a from B)

语句二：select count(*) from A left join B on A.a = B.a where B.a is null

语句三：select count(*) from A where not exists (select a from B where A.a = B.a)

知道以上三条语句的实际效果是相同的已经很久了，但是一直没有深究其间的效率对比。一直感觉上语句二是最快的。
今天工作上因为要对一个数千万行数据的库进行数据清除，需要删掉两千多万行数据。大量的用到了以上三条语句所要实现的功能。本来用的是语句一，但是结果是执行速度1个小时32分，日志文件占用21GB。时间上虽然可以接受，但是对硬盘空间的占用确是个问题。因此将所有的语句一都换成语句二。本以为会更快。没想到执行40多分钟后，第一批50000行都没有删掉，反而让SQL SERVER崩溃掉了，结果令人诧异。试了试单独执行这条语句，查询近一千万行的表，语句一用了4秒，语句二却用了18秒，差距很大。语句三的效率与语句一接近。

第二种写法是大忌，应该尽量避免。第一种和第三种写法本质上几乎一样。

假设buffer pool足够大，写法二相对于写法一来说存在以下几点不足：
（1）left join本身更耗资源（需要更多资源来处理产生的中间结果集）
（2）left join的中间结果集的规模不会比表A小
（3）写法二还需要对left join产生的中间结果做is null的条件筛选，而写法一则在两个集合join的同时完成了筛选，这部分开销是额外的

这三点综合起来，在处理海量数据时就会产生比较明显的区别（主要是内存和CPU上的开销）。我怀疑楼主在测试时buffer pool可能已经处于饱和状态，这样的话，写法二的那些额外开销不得不借助磁盘上的虚拟内存，在SQL Server做换页时，由于涉及到较慢的I/O操作因此这种差距会更加明显。

关于日志文件过大，这也是正常的，因为删除的记录多嘛。可以根据数据库的用途考虑将恢复模型设为simple，或者在删除结束后将日志truncate掉并把文件shrink下来。

因为以前曾经作过一个对这个库进行无条件删除的脚本，就是要删除数据量较大的表中的所有数据，但是因为客户要求，不能使用truncate table，怕破坏已有的库结构。所以只能用delete删，当时也遇到了日志文件过大的问题，当时采用的方法是分批删除，在SQL2K中用set rowcount @chunk，在SQL2K5中用delete top @chunk。这样的操作不仅使删除时间大大减少，而且让日志量大大减少，只增长了1G左右。
但是这次清除数据的工作需要加上条件，就是delete A from A where ....后面有条件的。再次使用分批删除的方法，却已经没效果了。
不知您知不知道这是为什么。

mysql not in 和 left join 效率问题记录

首先说明该条sql的功能是查询集合a不在集合b的数据。
not in的写法

复制代码代码如下:

 
select add_tb.RUID 
from (select distinct RUID 
from UserMsg 
where SubjectID =12 
and CreateTime>'2009-8-14 15:30:00' 
and CreateTime<='2009-8-17 16:00:00' 
) add_tb 
where add_tb.RUID 
not in (select distinct RUID 
from UserMsg 
where SubjectID =12 
and CreateTime<'2009-8-14 15:30:00' 
) 

复制代码代码如下:

 
select a.ruid,b.ruid 
from(select distinct RUID 
from UserMsg 
where SubjectID =12 
and CreateTime >= '2009-8-14 15:30:00' 
and CreateTime<='2009-8-17 16:00:00' 
) a left join ( 
select distinct RUID 
from UserMsg 
where SubjectID =12 and CreateTime< '2009-8-14 15:30:00' 
) b on a.ruid = b.ruid 
where b.ruid is null 

复制代码代码如下:

 
select distinct a.RUID 
from UserMsg a 
left join UserMsg b 
on a.ruid = b.ruid 
and b.subjectID =12 and b.createTime < '2009-8-14 15:30:00' 
where a.subjectID =12 
and a.createTime >= '2009-8-14 15:30:00' 
and a.createtime <='2009-8-17 16:00:00' 
and b.ruid is null; 

复制代码代码如下:

 
select distinct a.ruid 
from UserMsg a 
where a.subjectID =12 
and a.createTime >= '2009-8-14 15:30:00' 
and a.createTime <='2009-8-17 16:00:00' 
and not exists ( 
select distinct RUID 
from UserMsg 
where subjectID =12 and createTime < '2009-8-14 15:30:00' 
and ruid=a.ruid 
) 

复制代码代码如下:

 
select a.ruid,b.ruid 
from( select distinct RUID 
from UserMsg 
where CreateTime >= '2009-8-14 15:30:00' 
and CreateTime<='2009-8-17 16:00:00' 
) a left join UserMsg b 
on a.ruid = b.ruid 
and b.createTime < '2009-8-14 15:30:00' 
where b.ruid is null; 