最近被一條SQL語句弄的有點(diǎn)興奮，具體情況是這樣的...
我這邊有兩個(gè)表需要關(guān)聯(lián)查詢价涝，表的情況如下：
# 2759174行數(shù)據(jù)
SELECT COUNT(*) FROM tb_data t1;
# 7262行數(shù)據(jù)
SELECT COUNT(*) FROM tb_task t2;
# 執(zhí)行時(shí)間為44.88s
SELECT SQL_NO_CACHE t1.id FROM tb_data t1 WHERE t1.task_id IN (SELECT t2.id FROM tb_task t2);
# 執(zhí)行時(shí)間為28.93s
SELECT SQL_NO_CACHE t1.id FROM tb_data t1 WHERE EXISTS (SELECT * FROM tb_task t2 WHERE t1.task_id = t2.id);
這里涉及到IN和EXISTS的區(qū)別掀抹。
如果你試圖在網(wǎng)上找出答案蹬竖，你會(huì)發(fā)現(xiàn)所有的博客都是寫著：
如果兩個(gè)表中一個(gè)表大，另一個(gè)是表小丈冬，那么IN適合于外表大而子查詢表小的情況嘱函；EXISTS適合于外表小而子查詢表大的情況。
但是埂蕊，這個(gè)說法正確嗎往弓？繼續(xù)往下看！Ｐ钛酢函似！
按照我上面測(cè)試的情況。
t1表有兩百多萬行數(shù)據(jù)喉童，t2表只有7千行數(shù)據(jù)撇寞。它們關(guān)聯(lián)關(guān)系為t1.task_id = t2.id，我在使用IN時(shí)堂氯，t2表是子查詢表蔑担，并且是小表，按理來說在這種情況下使用IN應(yīng)該是更加合理的方式咽白。
然后實(shí)際情況是使用IN需要44.88s啤握，使用EXISTS需要28.93s，這個(gè)是怎么回事晶框？
一排抬、EXISTS和IN的介紹
我們先對(duì)EXISTS和IN做一個(gè)簡(jiǎn)單的介紹懂从。
1.1、exists
exists對(duì)外表用loop逐條查詢畜埋，每次查詢都會(huì)查看exists的條件語句莫绣，當(dāng)exists里的條件語句能夠返回記錄行時(shí)（無論記錄行是的多少，只要能返回）悠鞍，條件就為真，返回當(dāng)前l(fā)oop到的這條記錄模燥；反之咖祭，如果exists里的條件語句不能返回記錄行，則當(dāng)前l(fā)oop到的這條記錄被丟棄蔫骂，exists的條件就像一個(gè)bool條件么翰，當(dāng)能返回結(jié)果集則為true，不能返回結(jié)果集則為false
如下：
select * from user where exists (select 1);
對(duì)user表的記錄逐條取出辽旋，由于子條件中的select 1永遠(yuǎn)能返回記錄行浩嫌，那么user表的所有記錄都將被加入結(jié)果集，所以與select * from user;是一樣的补胚。
又如下：
select * from user where exists (select * from user where user_id = 0);
可以知道對(duì)user表進(jìn)行l(wèi)oop時(shí)码耐，檢查條件語句(select * from user where user_id = 0),由于user_id永遠(yuǎn)不為0，所以條件語句永遠(yuǎn)返回空集溶其，條件永遠(yuǎn)為false骚腥，那么user表的所有記錄都將被丟棄。
總結(jié)：如果A表有n條記錄瓶逃，那么exists查詢就是將這n條記錄逐條取出束铭，然后判斷n遍exists條件。
1.2厢绝、in
in查詢相當(dāng)于多個(gè)or條件的疊加契沫，這個(gè)比較好理解，比如下面的查詢：
select * from user where user_id in (1, 2, 3);
等效于
select * from user where user_id = 1 or user_id = 2 or user_id = 3;
總結(jié)：in查詢就是先將子查詢條件的記錄全都查出來昔汉，假設(shè)結(jié)果集為B懈万，共有m條記錄，然后再將子查詢條件的結(jié)果集分解成m個(gè)挤庇，再進(jìn)行m次查詢钞速。
1.3、使用上的區(qū)別
in查詢的子條件返回結(jié)果必須只有一個(gè)字段嫡秕，例如
select * from user where user_id in (select id from B);
不能是
select * from user where user_id in (select id, age from B);
而exists就沒有這個(gè)限制渴语。
二、EXISTS和IN的性能分析
為了便于分析昆咽，我把實(shí)際上的例子簡(jiǎn)化一下驾凶。
實(shí)際：
SELECT t1.id FROM tb_data t1 WHERE t1.task_id IN (SELECT t2.id FROM tb_task t2);
SELECT t1.id FROM tb_data t1 WHERE EXISTS (SELECT * FROM tb_task t2 WHERE t1.task_id = t2.id);
簡(jiǎn)化后：
查詢1牙甫、SELECT * FROM A WHERE A.id IN (SELECT id FROM B);
查詢2、SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);
2.1调违、in
假設(shè)B表的所有id為(1,2,3)窟哺，查詢1可以轉(zhuǎn)換為：
SELECT * FROM A WHERE A.id = 1 OR A.id = 2 OR A.id = 3;
這里主要是用到了A的索引，B表如何對(duì)查詢影響不大技肩。
2.2且轨、exists
查詢2可以轉(zhuǎn)化以下偽代碼：
for (i = 0; i < count(A); i++) {
a = get_record(A, i); #從A表逐條獲取記錄
if (B.id = a[id]) { #如果子條件成立
result[] = a;
}
}
return result;
這里主要用到了B表的索引，A表如何對(duì)查詢的效率影響不大虚婿。
2.3旋奢、實(shí)際情況
1）SELECT t1.id FROM tb_data t1 WHERE t1.task_id IN (SELECT t2.id FROM tb_task t2);
它使用的索引情況如下：
2）SELECT t1.id FROM tb_data t1 WHERE EXISTS (SELECT * FROM tb_task t2 WHERE t1.task_id = t2.id);
三、結(jié)論
MySQL中的in語句是把外表和內(nèi)表作join連接然痊，而exists語句是對(duì)外表作nest loop循環(huán)至朗，每次loop循環(huán)再對(duì)內(nèi)表進(jìn)行查詢。
通過以上分析剧浸，很容易得出下面的結(jié)論：
1锹引、如果查詢的兩個(gè)表大小相當(dāng)，那么用in和exists差別不大唆香。
2嫌变、如果兩個(gè)表中一個(gè)表大，另一個(gè)是表小袋马，那么IN適合于外表大而子查詢表小的情況初澎。
3、如果兩個(gè)表中一個(gè)表大虑凛，另一個(gè)是表小碑宴，EXISTS適合于外表小而子查詢表大的情況。
在一般情況下確實(shí)如此桑谍，這里需要考慮到索引原因延柠。但特殊情況是什么情況呢？我目前也不知道锣披。
最后贞间，我們來看看《高性能MySQL》這邊經(jīng)典的MySQL書籍對(duì)in和exists有什么見解。
書上說雹仿，MySQL會(huì)把in的查詢語句改成exists再去執(zhí)行（實(shí)際上我們?cè)跊]有索引情況下增热，他們的執(zhí)行過程確實(shí)是一致的）
在《MySQL技術(shù)內(nèi)幕:SQL編程》這本書中說：確實(shí)有很多DBA認(rèn)為EXISTS比IN的執(zhí)行效率更高，可能是當(dāng)時(shí)優(yōu)化器還不是很穩(wěn)定和足夠優(yōu)秀胧辽，但是目前絕大數(shù)的情況下峻仇，IN和EXISTS都具有相同的執(zhí)行計(jì)劃。
四邑商、處理
說實(shí)話摄咆，我想盡了我所知道的辦法凡蚜，都沒有找出這到底是怎么回事。我想我是應(yīng)該要去看看《MySQL技術(shù)內(nèi)幕:SQL編程》啦吭从。
不過對(duì)于下次處理類似的問題時(shí)朝蜘，我已經(jīng)有對(duì)應(yīng)的處理方案：
根據(jù)具體的業(yè)務(wù)情況，分析出它對(duì)應(yīng)可用的經(jīng)驗(yàn)法則涩金，再進(jìn)行不斷的測(cè)試和調(diào)優(yōu)谱醇，選出最優(yōu)的SQL。
而不僅僅是選擇相信經(jīng)驗(yàn)法則步做。
當(dāng)然枣抱，也有一些文章對(duì)這個(gè)進(jìn)行了一個(gè)深入的講解，但是我判斷不出對(duì)錯(cuò)辆床，在此提供給大家參考吧。
深入理解MySql子查詢IN的執(zhí)行和優(yōu)化
這個(gè)是第1篇桅狠，第2篇等我看一會(huì)書先～
