在我們的工作中可能會遇到這樣的情形：
我們需要查詢a表裡面的資料，但是要以b表作為約束。
舉個例子，比如我們需要查詢訂單表中的資料，但是要以使用者表為約束，也就是查詢出來的訂單的user_id要在使用者表裡面存在才返回。
表結構和表資料如下：
table1 usertb;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
+----+-----------+
| id | name |
+----+-----------+
| 1 | panchao |
| 2 | tangping |
| 3 | yinkaiyue |
+----+-----------+
table2 ordertb;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | YES | | NULL | |
| order_name | varchar(50) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
+----+---------+-------------------+
| id | user_id | order_name |
+----+---------+-------------------+
| 1 | 1 | tangping's order |
| 2 | 2 | yinkaiyue's order |
| 3 | 0 | zhangtian's order |
+----+---------+-------------------+
看過表過後，大家在腦海中可能已經想出了很多方法了，對吧。
主要三種方法：left join、in、exists。
我們分別來看看。他們的查詢結果和explain的結果。
1、left join：
MariaDB [test]> select * from ordertb a left join usertb b on a.user_id = b.id;
+----+---------+-------------------+------+----------+
| id | user_id | order_name | id | name |
+----+---------+-------------------+------+----------+
| 1 | 1 | tangping's order | 1 | panchao |
| 2 | 2 | yinkaiyue's order | 2 | tangping |
| 3 | 0 | zhangtian's order | NULL | NULL |
+----+---------+-------------------+------+----------+
MariaDB [test]> explain select * from ordertb a left join usertb b on a.user_id= b.id;
+------+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 3 | |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | test.a.user_id | 1 | Using where |
+------+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+
2、in：
MariaDB [test]> select * from ordertb where ordertb.user_id in (select id from usertb);
+----+---------+-------------------+
| id | user_id | order_name |
+----+---------+-------------------+
| 1 | 1 | tangping's order |
| 2 | 2 | yinkaiyue's order |
+----+---------+-------------------+
MariaDB [test]> explain select * from ordertb where ordertb.user_id in (select id from usertb);
+------+-------------+---------+--------+---------------+---------+---------+----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+--------+---------------+---------+---------+----------------------+------+-------------+
| 1 | PRIMARY | ordertb | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
| 1 | PRIMARY | usertb | eq_ref | PRIMARY | PRIMARY | 4 | test.ordertb.user_id | 1 | Using index |
+------+-------------+---------+--------+---------------+---------+---------+----------------------+------+-------------+
3、exists：
MariaDB [test]> select * from ordertb where exists(select 1 from usertb where usertb.id = ordertb.user_id);
+----+---------+-------------------+
| id | user_id | order_name |
+----+---------+-------------------+
| 1 | 1 | tangping's order |
| 2 | 2 | yinkaiyue's order |
+----+---------+-------------------+
MariaDB [test]> explain select * from ordertb where exists(select 1 from usertbwhere usertb.id = ordertb.user_id);
+------+-------------+---------+--------+---------------+---------+---------+----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+--------+---------------+---------+---------+----------------------+------+-------------+
| 1 | PRIMARY | ordertb | ALL | NULL | NULL | NULL | NULL | 3 | Using where |
| 1 | PRIMARY | usertb | eq_ref | PRIMARY | PRIMARY | 4 | test.ordertb.user_id | 1 | Using index |
+------+-------------+---------+--------+---------------+---------+---------+----------------------+------+-------------+
我們可以看到，這三種查詢的explain結果大致相同，唯一不同的是left join中的Extra沒有用到Useing Where。說明left join相比於其他兩個查詢效率要低一些，並且left join中有冗餘資料。
我們再來看 in 和 exists ，從表面上來看好像xiaolv一樣。其實不然。我們來深入分析一下這兩個語句。
1、in。
其中usertb我們用B來代替，ordertb我們用A來代替。
in()只執行一次,它查出B表中的所有id欄位並快取起來.之後,檢查A表的user_id是否與B表中的id相等,如果相等則將A表的記錄加入結果集中,直到遍歷完A表的所有記錄. 它的查詢過程類似於以下過程
List resultSet=[]; Array A=(select * from A); Array B=(select id from B);
for(int i=0;i<A.length;i++) { for(int j=0;j<B.length;j++) { if(A[i].id==B[j].id) { resultSet.add(A[i]); break; } } } return resultSet;
可以看出,當B表資料較大時不適合使用in(),因為它會B表資料全部遍歷一次. 如:A表有10000條記錄,B表有1000000條記錄,那麼最多有可能遍歷10000*1000000次,效率很差. 再如:A表有10000條記錄,B表有100條記錄,那麼最多有可能遍歷10000*100次,遍歷次數大大減少,效率大大提升.
2、exists。
exists()會執行A.length次,它並不快取exists()結果集,因為exists()結果集的內容並不重要,重要的是結果集中是否有記錄,如果有則返回true,沒有則返回false. 它的查詢過程類似於以下過程
List resultSet=[]; Array A=(select * from A)
for(int i=0;i<A.length;i++) { if(exists(A[i].id) { //執行select 1 from B b where b.id=a.id是否有記錄返回 resultSet.add(A[i]); } } return resultSet;
當B表比A表資料大時適合使用exists(),因為它沒有那麼遍歷操作,只需要再執行一次查詢就行. 如:A表有10000條記錄,B表有1000000條記錄,那麼exists()會執行10000次去判斷A表中的id是否與B表中的id相等. 如:A表有10000條記錄,B表有100000000條記錄,那麼exists()還是執行10000次,因為它只執行A.length次,可見B表資料越多,越適合exists()發揮效果. 再如:A表有10000條記錄,B表有100條記錄,那麼exists()還是執行10000次,還不如使用in()遍歷10000*100次,因為in()是在記憶體裡遍歷比較,而exists()需要查詢資料庫,我們都知道查詢資料庫所消耗的效能更高,而記憶體比較很快.
結論:exists()適合B表比A表資料大的情況
當A表資料與B表資料一樣大時,in與exists效率差不多,可任選一個使用.
區別及應用場景
in 和 exists的區別:
如果子查詢得出的結果集記錄較少，主查詢中的表較大且又有索引時應該用in, 反之如果外層的主查詢記錄較少，子查詢中的表大，又有索引時使用exists。其實我們區分in和exists主要是造成了驅動順序的改變(這是效能變化的關鍵)，如果是exists，那麼以外層表為驅動表，先被訪問，如果是IN，那麼先執行子查詢，所以我們會以驅動表的快速返回為目標，那麼就會考慮到索引及結果集的關係了 ，另外IN時不對NULL進行處理。
in 是把外表和內表作hash 連線，而exists是對外表作loop迴圈，每次loop迴圈再對內表進行查詢。一直以來認為exists比in效率高的說法是不準確的。
更多細節，可以參考以下部落格（SQL語句中exists和in的區別），因為我也是看了這個部落格寫的文章。