SQL中NOT IN和NOT EXISTS性能上的差异

前几周我们遇到个bug，在通话记录很多的情况下，查看通话记录会很慢。用具体数字来说就是当通话记录达到1000条后，UI需要10多秒才能响应外界输入。经调试发现瓶颈在数据库上，原来都是NOT IN惹的祸。

为讲解方便，我在此将问题简化后描述一下，主要涉及下面几个表。

-- 通话号码，可以包括电话号码，Email地址，IM地址等。
CREATE TABLE call_number (
    id int PRIMARY KEY AUTO_INCREMENT,
    number varchar(30)
    -- 其它属性
);

-- 通话记录。
CREATE TABLE call_log (
    id int PRIMARY KEY AUTO_INCREMENT,
    number_id int NOT NULL REFERENCES call_number ON DELETE CASCADE
    -- 其它属性
);

-- 联系人的电话号码，属于通话号码的一种。
CREATE TABLE contact_phonenumber (
    id int PRIMARY KEY AUTO_INCREMENT,
    number_id int NOT NULL REFERENCES call_number ON DELETE CASCADE
    -- 其它属性
);

这样划分表格不见得很合理，因为尽管其符合3级范式的要求，但很多查询都需要做多表连接，性能不高。这些不属于本文的讨论范围，我们暂且不去考虑这些问题。

罪魁祸首是一条很不起眼的SQL语句，
DELETE FROM contact_phonenumber
WHERE number_id NOT IN (
    SELECT number_id FROM call_log
);

这条语句的作用是删除contact_phonenumber中number_id不在call_log中的记录，防止无效数据过多导致性能下降。实验证明，这条语句效率极低，在两个表记录都接近1000条后，执行起来需要10秒多。

经过研究，改用如下方案后，可以在1秒左右就达到同样的效果。
DELETE FROM contact_phonenumber AS cp
WHERE NOT EXISTS (
    SELECT * FROM call_log WHERE number_id = cp.number_id);
);

由于contact_phonenumber的number_id不允许为NULL，所以两条语句的功能是完全一样的。参见另一篇文章“SQL中NOT IN和NOT EXISTS功能上的差异”(http://hi.baidu.com/sdshancheng/blog/item/fe6bf03e8fedf00cbaa16789.html)。

为什么NOT EXISTS会比NOT IN快这么多呢？我从网上搜了一下，谈到这个问题的帖子还真多。主要原因就是：
如果查询语句使用了NOT IN，那么内外表都进行全表扫描，没有用到索引；
而NOT EXISTS的子查询依然能用到表上的索引。