MYSQL中IN與EXISTS的區別詳解

在MYSQL的連表查詢中,最好是遵循‘小表驅動大表的原則’

一、IN與EXISTS的區別

1、IN查詢分析

SELECT * FROM A WHERE id IN (SELECT id FROM B);

等價於:1、SELECT id FROM B —–>先執行in中的查詢

2、SELECT * FROM A WHERE A.id = B.id

以上in()中的查詢隻執行一次,它查詢出B中的所有的id並緩存起來,然後檢查A表中查詢出的id在緩存中是否存在,如果存在則將A的查詢數據加入到結果集中,直到遍歷完A表中所有的結果集為止。

以下用遍歷結果集的方式來分析IN查詢

通過以上程式可以看出,當B表的數據較大時不適合使用in()查詢,因為它會將B表中的數據全部遍歷一次

例如:

1、A表中有100條記錄,B表中有1000條記錄,那麼最多可能遍歷100*1000次,效率很差

2、A表中有1000條記錄,B表中有100條記錄,那麼最多可遍歷1000*100此,內循環次數減少,效率大大提升

結論:IN()查詢適合B表數據比A表數據小的情況,IN()查詢是從緩存中取數據

2、EXISTS查詢分析

語法:SELECT 字段 FROM table WHERE EXISTS(subquery);

SELECT * FROM a WHERE EXISTS(SELECT 1 FROM b WHERE B.id = A.id);

以上查詢等價於:

1、SELECT * FROM A;

2、SELECT I FROM B WHERE B.id = A.id;

EXISTS()查詢會執行SELECT * FROM A查詢,執行A.length次,並不會將EXISTS()查詢結果結果進行緩存,因為EXISTS()查詢返回一個佈爾值true或flase,它隻在乎EXISTS()的查詢中是否有記錄,與具體的結果集無關。

EXISTS()查詢是將主查詢的結果集放到子查詢中做驗證,根據驗證結果是true或false來決定主查詢數據結果是否得以保存。

以下用遍歷結果集的方式來分析EXISTS查詢

從以上程式可以看出:

當B表的數據比A表的數據大時適合使用EXISTS()查詢,因為它不用遍歷B操作,隻執行一次查詢就OK瞭

例如:

1、A表有100條記錄,B表有1000條記錄,那麼EXISTS()會執行100次去判斷A表中的id是否與B表中的id相等.因為它隻執行A.length次,可見B表數據越多,越適合EXISTS()發揮效果.

2、A表有10000條記錄,B表有100條記錄,那麼EXISTS()還是執行10000次,此時不如使用in()遍歷10000*100次,因為IN()是在內存裡遍歷數據進行比較,而EXISTS()需要查詢資料庫,我們都知道查詢資料庫所消耗的性能更高,而內存比較很快.

結論:

exists()適合B表比A表數據大的情況

當A表數據與B表數據一樣大時,in與exists效率差不多,可任選一個使用

發佈留言