取得一張表的資料不在另一張表中的最優秀方法(JOIN與EXISTS的效率研究)，張表中exists

取得一張表的資料不在另一張表中的最優秀方法(JOIN與EXISTS的效率研究)，張表中exists
業務需求：

公司有個CRM經紀人管理系統，前天遇到的比較棘手的問題是，要查詢一張表中不在另一張表中的所有使用者並分頁顯示，但問題是外表的資料量很大，如果用not in(select ..)，not exists(select ...)之類的子查詢 的話需要對子查詢表進行所有資訊的調用過濾才行，一單子查詢資料量過大效率問題就來了，於是就私下裡查了一點資料，並根據資料中提供的例子建立了對應的測試表做了一些測試，隨後將解決問題的方法移交給了技術部的鄒鴻同事去解決問題，因為是前天的事情了，本來是事情忙沒時間寫日誌的，就是這個事情沒有做個筆記已經兩天沒睡好覺了，於是乎今天來了還是做一下筆記，分享給大家的好，如果大家有更好的辦法 或者 說有bug疑問，請隨時Email聯絡 摘取天上星 happy.yin@qq.com

下面是一組測試的JOIN 、EXISTS 效率對比

先將profiling開啟,用來一會查看sql執行時間

set profiling=1;

 exists 子查詢與 join聯結效率的對比

EXPLAIN SELECT film_id, language_id FROM sakila.filmWHERE NOT EXISTS(    SELECT * FROM sakila.film_actor    WHERE film_actor.film_id = film.film_id);

 

EXPLAIN SELECT film_id, language_id FROM sakila.film    LEFT JOIN sakila.film_actor USING(film_id)WHERE film_actor.actor_id IS NULL; 

他們只有一點細微的區別,一個select_type,還有就是Extra裡後者用了Not exists,也就是提前終止演算法,當遇到第一個actor_id不是null的時候,就捨棄這個篩選.

最後來看看二者的效率

可知使用子查詢Exists的效率要低一些.

SELECT DISTINCT film_id FROM sakila.film    JOIN sakila.film_actor USING(film_id);SELECT film_id FROM sakila.film    WHERE EXISTS (    SELECT * FROM sakila.film_actor    WHERE film.film_id = film_actor.film_id);SHOW PROFILES;

可以看出.依然EXISTS效率要低
PS:“摘取天上星” 的小插曲，有人說在使用JOIN方法對 1000萬條資料的表進行操作時速度會變得很慢，其實不是SQL語句本身的問題，單表1000萬條資料慢的問題是磁碟IO帶來的硬傷，是任何最佳化無法避免的，使用任何軟體層最佳化都無法避免磁碟IO問題對資料庫帶來的硬傷。。

總結：判斷一個表的資料不在另一個表中最優秀方法如下可選(前提條件:a表的aid和b表的aid必須有索引,b.aid不能有NULL值)：
SELECT aid FROM a LEFT JOIN b ON a.aid = b.aid WHERE b.aid IS NULL LIMIT 0,100;

SELECT  aid FROM a LEFT JOIN b USING(aid) WHERE b.aid IS NULL  LIMIT 0,100;(此文法實際效果同上，是上面SQL語句的簡寫形式,USING文法參見本文末尾↓)

實現原理其實很簡單，left join在進行 鏈表時，如果外表資料不存在 的情況下 返回資料為NULL，
而 SELECT aid FROM a LEFT JOIN ON a.aid=b.aid WHERE b.aid IS NULL 恰恰利用了 a表aid和b表aid相等同時b表的aid又為NULL的情況下才返回資料，而滿足這個條件的資料只有一種情況，就是b表中沒有這個資料的情況下才滿足b.aid即為NULL，同時又和a.aid相等，也就是我們要找的在a表中但不在b表中的資料。
 
USING用法以及使用效果如下：

用於表串連時給定串連條件（可以理解為簡寫形式）

SELECT * FROM a JOIN b ON a.id = b.id;

使用 USING 可以寫為

SELECT * FROM a JOIN b USING(id);