前言：最近在看 《高性能mysql第三版》 這本書，讀到子查詢優化那章，書中說mysql會将in子查詢改寫成exists查詢（書中基于的mysql版本是5.1.50和5.5），于是乎我又上網找了下資料，發現網上說法幾乎都是：
in 子查詢是把外表和内表hash關聯，先查詢内表，再把内表結果與外表比對，對外表使用索引（外表效率高，可用大表），而内表多大都需要查詢，不可避免，故外表大的使用in，可加快效率。
exists 是對外表做loop循環，每次loop循環再對内表（子查詢）進行查詢，那麼因為對内表的查詢使用的索引（内表效率高，故可用大表），而外表有多大都需要周遊，不可避免（盡量用小表），故内表大的使用exists，可加快效率；
發現竟然和書上說的不一樣，因為按照書上說所，in 和 exists 應該是一樣的（會重寫 in 查詢為 exists ) ，于是想在本地mysql測試一下，本地裝的是5.7版本，資料庫中有兩個表 userinfo 和 syslog 表：
這裡使用兩個表的主鍵id進行關聯，sql 和分析計劃表如下：
explain select * from syslog where id in(select id from userinfo);
show WARNINGS;
可以看到優先執行userinfo表，再去和syslog進行比對，看起來似乎和網上說的一樣，接着我們将順序颠倒：
explain select * from userinfo where id in(select id from syslog);
show WARNINGS;
結果竟然和上面執行計劃運作順序是一緻的？？？我們打開結果二那一欄檢視mysql優化器将sql重寫後的結果，分别如下：
原SQL：
select * from syslog where id in(select id from userinfo);
優化後的SQL：
/* select#1 */
SELECT
`test`.`syslog`.`id` AS `id`,
`test`.`syslog`.`user_name` AS `user_name`,
`test`.`syslog`.`type` AS `type`,
`test`.`syslog`.`operation` AS `operation`,
`test`.`syslog`.`method` AS `method`,
`test`.`syslog`.`params` AS `params`,
`test`.`syslog`.`ip` AS `ip`,
`test`.`syslog`.`operation_time` AS `operation_time`
FROM
`test`.`userinfo`
JOIN `test`.`syslog`
WHERE
(
`test`.`syslog`.`id` = `test`.`userinfo`.`id`
)
原SQL：
select * from userinfo where id in(select id from syslog);
優化後的SQL：
/* select#1 */
SELECT
`test`.`userinfo`.`id` AS `id`,
`test`.`userinfo`.`user_id` AS `user_id`,
`test`.`userinfo`.`user_name` AS `user_name`,
`test`.`userinfo`.`age` AS `age`,
`test`.`userinfo`.`gender` AS `gender`,
`test`.`userinfo`.`address` AS `address`,
`test`.`userinfo`.`user_pass` AS `user_pass`
FROM
`test`.`syslog`
JOIN `test`.`userinfo`
WHERE
(
`test`.`userinfo`.`id` = `test`.`syslog`.`id`
)
可以看到 mysql将兩個in子查詢全都改寫成了内連接配接查詢 ，這也就可以解釋兩個分析計劃表為什麼總是先查找userinfo表了，因為在内連接配接的情況下mysql優化器始終會先通路資料量小的那張表，這樣可以減少不必要的IO。
第一個sql改寫後首先運作的是 select id from userinfo 使用了主鍵覆寫索引（Extra 為 using index），将查詢的結果和 syslog 進行比對，是以syslog表使用了主鍵索引+where條件過濾（Extra 為 using Where）。
第二個sql改寫後首先運作的的是 select * from userinfo ，是以計劃中顯示的是全表掃描，然後将查詢後的結果和 syslog 進行比對，這裡因為查找的是select id from syslog where id = ? ，是以計劃中顯示是覆寫索引+where條件過濾（Extra 為 using where + using index)。
我們再來看一下 exists 的分析情況：
explain select * from syslog where exists(select * from userinfo where userinfo.id = syslog.id);
explain select * from userinfo where exists(select * from syslog where syslog.id = userinfo.id);
可以看到exists查詢是一個相關子查詢，内部的查詢需要依賴外部的查詢結果，是以兩個sql的分析計劃都是先将外部的表進行全表掃描再和子查詢表進行比對，如果外部的表資料量小的話性能可能不會太差，資料量大的情況下性能會非常糟糕。
結論：1、mysql5.5以前會将 in 子查詢改寫成 exists 查詢，如果外部表資料量大的情況下性能會非常糟糕。
2、mysql5.7（5.6沒有測過，感興趣的同學可以測測）對 in 子查詢進行了優化，會将sql改寫成 join 連接配接，這樣優化器就可以始終優先通路資料量小的表格，減少IO，性能和直接寫連接配接查詢幾乎是一樣的（這點和網上書上說的是有出入的）。
3、exists查詢會被分解成一個外部查詢和相關子查詢（DEPENDENT SUBQUERY），這樣子查詢會依賴于外部查詢的結果，是以始終會對外部表進行全表掃描，外部表資料量大的時候要尤其注意。