在我做項目的時候遇到一個需求，要将存在于表ta而不存在于表tb中的資料查詢出來。

記錄使用的方法和探讨效率。

資料準備

建立表ta，并且使用存儲過程插入13000條資料，在我的機器上運作時間: 346.719s。如果覺得插入的速度比較慢,可以直接導入我建好的表，百度雲位址 http://pan.baidu.com/s/1dFtovg1 ，裡面已經有資料了，直接導入sql執行即可，這樣比用存儲過程要快很多。

DROP TABLE IF EXISTS ta;

CREATE TABLE `ta` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13000 DEFAULT CHARSET=utf8;

DROP PROCEDURE IF EXISTS ta_insert;

DELIMITER $$
CREATE PROCEDURE ta_insert()
 MODIFIES SQL DATA
 BEGIN
 SET @i=1;
 SET @max=13000;
 WHILE @i<@max DO
 INSERT INTO `ta` VALUES ();
 SET @i = @i + 1;
END WHILE;
end $$

CALL ta_insert();

建立表tb，并且使用存儲過程插入10000條資料，在我的機器上運作時間: 224.102s。

DROP TABLE IF EXISTS tb;

CREATE TABLE `tb` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8000 DEFAULT CHARSET=utf8;

DROP PROCEDURE IF EXISTS tb_insert;

DELIMITER $$
CREATE PROCEDURE tb_insert()
 MODIFIES SQL DATA
 BEGIN
 SET @i=1;
 SET @max=8000;
 WHILE @i<@max DO
 INSERT INTO `tb` VALUES ();
 SET @i = @i + 1;
END WHILE;
end $$

CALL tb_insert();

子查詢

使用

NOT IN

，ta表中的每一個id值都要去與tb表中的id比對，比對到就停止，也就是說，存在于ta表而不存在于tb表的id值需要與所有tb表中的id值進行比對。

執行子查詢時，MYSQL需要建立臨時表，查詢完畢後再删除這些臨時表，是以，子查詢的速度會受到一定的影響，這裡多了一個建立和銷毀臨時表的過程。

平均時間為 0.04s。

SELECT ta.id FROM ta WHERE ta.id IN (SELECT id FROM tb)

左連接配接

LEFT JOIN

，ta表左連接配接tb表，而存在于ta表不存在于tb表中的字段為

NULL

，于是我們可以通過判斷

WHERE tb.id IS NULL

來找到存在于ta表而不存在于tb表的id值。

平均時間是 0.06s。

SELECT ta.id FROM ta LEFT JOIN tb ON ta.id = tb.id WHERE tb.id IS NULL

效率之謎

版本問題？

按理來說，連接配接應該比子查詢要快，但是在我進行試驗的時候發現卻不是這樣的，子查詢居然還比連接配接要快。

搜尋了解到

對于類似NOT IN這樣的子查詢，也能受益于subquery materialize，将子查詢的結果集cache到臨時表裡，使用hashindex來進行檢索；物化的子查詢可以看到select_type字段為SUBQUERY，而在MySQL5.5裡為DEPENDENT SUBQUERY

可能是版本原因，我用的是mysql5.7，可能做了優化。

于是使用mysql5.5再次測試。

發現子查詢和左連接配接的查詢時間都在0.12s附近，還是不能說明連接配接比子查詢高效。進一步猜測，我的資料組織格式是否出現了問題，于是使用上面百度雲盤連接配接中的mm_member表和mm_log表(這是 mysql（4）—— 表連接配接查詢與where後使用子查詢的性能分析。提供的資料) 。

猜測驗證

SELECT mm_member.id FROM mm_member WHERE mm_member.id NOT IN (SELECT DISTINCT mm_log.member_id FROM mm_log)

SELECT mm_member.id FROM mm_member LEFT JOIN (SELECT DISTINCT mm_log.member_id FROM mm_log ) AS mm
ON mm.member_id = mm_member.id WHERE mm.member_id IS NULL

mysql5.7

子查詢為1.1s左右，左連接配接為1.55s，子查詢依然速度較快。

mysql5.5

子查詢為48s左右，左連接配接為1.4s，将近34倍的差距，由此印證上面引用的那部分，mysql5.7确實已經多子查詢做了優化，使其達到了逼近左連接配接的效率。

那為什麼我自己所建立的表無法展現版本的這種性能差别？

猜測應該是資料類型的原因，可能int類型的查詢效率已經都優化好了。

網傳最高效

不太清楚其中的原理，并且在我的測試中性能跟連接配接差不多。

SELECT id FROM ta WHERE (SELECT COUNT(1) AS num FROM tb WHERE ta.id = tb.id) = 0

而且網上流傳的版本((資料庫篇) SQL查詢~ 存在一個表而不在另一個表中的資料)為

select * from B where (select count(1) as num from A where A.ID = B.ID) = 0

應該是

select * from A where (select count(1) as num from B where A.ID = B.ID) = 0

大表在前，小表在後。

注意：

存儲過程循環插入比普通方式插入資料慢很多倍。
索引可以有效提高搜尋效率。
不是所有的子查詢都比連接配接慢的。

參考文檔：

MySQL 5.6的優化器改進
mysql（4）—— 表連接配接查詢與where後使用子查詢的性能分析。

查詢在一張表不在另外一張表的記錄及效率探究