先放一段代碼

for(int i=0;i<1000;i++){
 for(int j=0;j<5;j++){
 System.out.println("hello");
 }
}

 for(int i=0;i<5;i++){
 for(int j=0;j<1000;j++){
 System.out.println("hello");
 }
}

分析以上代碼可以看到兩行代碼除了循環(huán)的次序不一致意外，其他并無(wú)區(qū)別，在實(shí)際執(zhí)行時(shí)兩者所消耗的時(shí)間和空間應(yīng)該也是一致的。但是這僅僅是在Java中，現(xiàn)在我們轉(zhuǎn)化一下情景，最外層循環(huán)是數(shù)據(jù)庫(kù)中的連接操作，內(nèi)層循環(huán)為查找操作，那么現(xiàn)在兩次的結(jié)果將相差巨大。

之所以出現(xiàn)這樣的原因是數(shù)據(jù)庫(kù)的特點(diǎn)決定的，數(shù)據(jù)庫(kù)中相比較于查詢(xún)操作而言，建立連接是更消耗資源的。第一段代碼建立了1000次連接，每一次連接卻只做了5次查詢(xún)，顯然是很浪費(fèi)的。

因此在我們對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作時(shí)需要遵循的操作應(yīng)當(dāng)是小表驅(qū)動(dòng)大表(小的數(shù)據(jù)集驅(qū)動(dòng)大的數(shù)據(jù)集)。

in與exists

表結(jié)構(gòu)

tbl_emp為員工表，deptld為部門(mén)id。tbl_dept為部門(mén)表。員工表中含有客人，其deptld字段為-1

mysql> desc tbl_emp;
+--------+-------------+------+-----+---------+----------------+
| Field | Type  | Null | Key | Default | Extra   |
+--------+-------------+------+-----+---------+----------------+
| id  | int(11)  | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES |  | NULL |    |
| deptld | int(11)  | YES | MUL | NULL |    |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc tbl_dept;
+----------+-------------+------+-----+---------+----------------+
| Field | Type  | Null | Key | Default | Extra   |
+----------+-------------+------+-----+---------+----------------+
| id  | int(11)  | NO | PRI | NULL | auto_increment |
| deptName | varchar(30) | YES | MUL | NULL |    |
| locAdd | varchar(40) | YES |  | NULL |    |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

我們知道對(duì)于一個(gè)公司而言相對(duì)于部門(mén)來(lái)說(shuō)員工數(shù)要多得多?，F(xiàn)在我們有這樣一個(gè)需求：查詢(xún)屬于這個(gè)公司的員工（不含來(lái)訪(fǎng)客人），我們可以用以下代碼解決

利用in

# 先查詢(xún)部門(mén)表中所有的id，然后拿員工表中的deptld字段與之對(duì)比，若含有則保留。

mysql> select * from tbl_emp a where a.deptld in (select id from tbl_dept);

in關(guān)鍵字就像or的拼接，例如上述sql雨中子查詢(xún)查出的結(jié)果為1，2，3。則sql語(yǔ)句等價(jià)于以下形式

mysql> select * from tbl_emp a where a.deptld=1 or a.deptld=2 or a.deptld=3

總的來(lái)說(shuō)in關(guān)鍵字就是將子查詢(xún)中的所有結(jié)果都查出來(lái)，假設(shè)結(jié)果集為B，共有m條記錄，然后在將子查詢(xún)條件的結(jié)果集分解成m個(gè)，再進(jìn)行m次查詢(xún)?？梢钥吹竭@里主要是用到了A的索引，B表如何對(duì)查詢(xún)影響不大

利用exists

mysql> select * from tbl_emp a where exists (select 1 from tbl_dept b where a.deptld = b.id );

exits：將主查詢(xún)的數(shù)據(jù)放到子查詢(xún)中做條件驗(yàn)證，根據(jù)驗(yàn)證結(jié)果（True或False）來(lái)判斷是否保留主查詢(xún)中的記錄。

for (i = 0; i < count(A); i++) { //遍歷A的總記錄數(shù)
　　a = get_record(A, i); //從A表逐條獲取記錄
　　if (B.id = a[id]) //如果子條件成立
　　　　result[] = a;
}
return result;

可以看到：exists主要是用到了B表的索引，A表如何對(duì)查詢(xún)的效率影響不大

結(jié)論

mysql> select * from tbl_emp a where a.deptld in (select id from tbl_dept);

若tbl_dept的記錄數(shù)少余tbl_emp則使用in效率比較高

mysql> select * from tbl_emp a where exists (select 1 from tbl_dept b where a.deptld = b.id );

若tbl_dept的記錄數(shù)多余tbl_emp則使用in效率比較高

下面給大家介紹IN與EXISTS的區(qū)別

1、IN查詢(xún)分析

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

等價(jià)于：1、SELECT id FROM B ----->先執(zhí)行in中的查詢(xún)

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

以上in()中的查詢(xún)只執(zhí)行一次，它查詢(xún)出B中的所有的id并緩存起來(lái)，然后檢查A表中查詢(xún)出的id在緩存中是否存在，如果存在則將A的查詢(xún)數(shù)據(jù)加入到結(jié)果集中，直到遍歷完A表中所有的結(jié)果集為止。

以下用遍歷結(jié)果集的方式來(lái)分析IN查詢(xún)

通過(guò)以上程序可以看出，當(dāng)B表的數(shù)據(jù)較大時(shí)不適合使用in()查詢(xún)，因?yàn)樗鼤?huì)將B表中的數(shù)據(jù)全部遍歷一次

例如：

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

2、A表中有1000條記錄，B表中有100條記錄，那么最多可遍歷1000*100此，內(nèi)循環(huán)次數(shù)減少，效率大大提升

結(jié)論：IN()查詢(xún)適合B表數(shù)據(jù)比A表數(shù)據(jù)小的情況，IN()查詢(xún)是從緩存中取數(shù)據(jù)

2、EXISTS查詢(xún)分析

語(yǔ)法：SELECT  字段 FROM  table WHERE EXISTS（subquery）;

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

以上查詢(xún)等價(jià)于：

SELECT * FROM A;
SELECT I FROM B WHERE B.id = A.id;

EXISTS()查詢(xún)會(huì)執(zhí)行SELECT * FROM A查詢(xún)，執(zhí)行A.length次，并不會(huì)將EXISTS()查詢(xún)結(jié)果結(jié)果進(jìn)行緩存，因?yàn)镋XISTS()查詢(xún)返回一個(gè)布爾值true或flase，它只在乎EXISTS()的查詢(xún)中是否有記錄，與具體的結(jié)果集無(wú)關(guān)。

EXISTS()查詢(xún)是將主查詢(xún)的結(jié)果集放到子查詢(xún)中做驗(yàn)證，根據(jù)驗(yàn)證結(jié)果是true或false來(lái)決定主查詢(xún)數(shù)據(jù)結(jié)果是否得以保存。

總結(jié)

以上所述是小編給大家介紹的MySQL中in與exists的使用及區(qū)別介紹，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！

最新評(píng)論