IN為什麼慢？
在應用程式中使用子查詢後，SQL語句的查詢性能變得非常糟糕。例如：
SELECT driver_id FROM driver where driver_id in (SELECT driver_id FROM driver where _create_date > '2016-07-25 00:00:00');
獨立子查詢傳回了符合條件的driver_id，這個問題是解決了，但是所用的時間需要6秒，可以通過EXPLAIN檢視SQL語句的執行計劃：
可以看到上面的SQL語句變成了相關子查詢，通過EXPLAIN EXTENDED 和 SHOW WARNINGS指令，可以看到如下結果：
select `northwind`.`driver`.`driver_id` AS `driver_id` from `northwind`.`driver` where <in_optimizer>(`northwind`.`driver`.`driver_id`,<exists>(select 1 from `northwind`.`driver` where ((`northwind`.`driver`.`_create_date` > '2016-07-25 00:00:00') and (<cache>(`northwind`.`driver`.`driver_id`) = `northwind`.`driver`.`driver_id`))))
可以看出MySql優化器直接把IN子句轉換成了EXISTS的相關子查詢。下面這條相關IN子查詢：
SELECT driver_id FROM driver where driver_id in (SELECT driver_id FROM user where user.uid = driver.driver_id);
檢視SQL語句的執行計劃：
就是相關子查詢，通過EXPLAIN EXTENDED 和 SHOW WARNINGS指令，看到如下結果：
select `northwind`.`driver`.`driver_id` AS `driver_id` from `northwind`.`driver` where <in_optimizer>(`northwind`.`driver`.`driver_id`,<exists>(select 1 from `northwind`.`user` where ((`northwind`.`user`.`uid` = `northwind`.`driver`.`driver_id`) and (<cache>(`northwind`.`driver`.`driver_id`) = `northwind`.`driver`.`driver_id`))))
可以看出無論是獨立子查詢還是相關子查詢，MySql 5.5之前的優化器都是将IN轉換成EXISTS語句。如果子查詢和外部查詢分别傳回M和N行，那麼該子查詢被掃描為O(N+N*M)，而不是O(N+M)。這也就是為什麼IN慢的原因。
IN和EXISTS哪個快？
網上百度到很多認為IN和EXISTS效率一樣是錯誤的文章。
如果查詢的兩個表大小相當，那麼用in和exists差别不大。
如果兩個表中一個較小，一個是大表，則子查詢表大的用exists，子查詢表小的用in：
例如：表A（小表），表B（大表）
1：
select * from A where cc in (select cc from B) 效率低，用到了A表上cc列的索引；
select * from A where exists(select cc from B where cc=A.cc) 效率高，用到了B表上cc列的索引。
相反的
2：
select * from B where cc in (select cc from A) 效率高，用到了B表上cc列的索引；
select * from B where exists(select cc from A where cc=B.cc) 效率低，用到了A表上cc列的索引。
總結上面的描述，個人認為其主要的原因在于對索引的使用。任何情況下，隻要是大表的索引被使用，就可以使效率提高。
但是在編輯本文的時候，多次測試，卻沒能得到上面所總結的結果。下面是測試SQL語句，先是外表為大表，内表為小表。（示例一）
SELECT count(driver_id) FROM driver where driver_id in (SELECT uid FROM user);
SELECT count(driver_id) FROM driver where exists (SELECT 1 FROM user where uid = driver.driver_id);
執行結果是：
再是外表是小表，内表是大表。（示例二）
select count(uid) from user where uid in (SELECT driver_id FROM driver);
select count(uid) from user where exists (SELECT 1 FROM driver where driver.driver_id = user.uid);
可以發現IN和EXISTS的執行效率，在任何情況下都正好是相同的。基于此，我們繼續檢視示例一兩條SQL語句的執行計劃，如下：
可以看到IN和EXISTS的執行計劃是一樣的，對此得出的結論兩者的執行效率應該是一樣的。
《MySql技術内幕：SQL程式設計》：書中描述的确實有很多DBA認為EXISTS比IN的執行效率更高，可能是當時優化器還不是很穩定和足夠優秀，但是目前絕大數的情況下，IN和EXISTS都具有相同的執行計劃。
如何提高效率？
上面示例二中的SQL語句執行時間約8秒，因為存在M*N的原因造成慢查詢，但是還是可以進行優化，注意到慢的原因就是内部每次與外部比較時，都需要周遊一次表操作，可以采用另外一個方法，在嵌套一層子查詢，避免多次周遊操作，語句如下：
SELECT count(driver_id) FROM driver where exists (SELECT uid FROM (SELECT uid from user) as b where b.uid = driver.driver_id);
執行效果如圖：
可以發現優化減少了6s多的執行時間，下面是SQL的執行計劃：
同樣的還是相關子查詢，但是減少了内部周遊查詢的操作。是以可以通過預查詢來減少周遊操作，而提高效率。
其實在實際程式設計中，很多開發人員選擇不使用連接配接表查詢，而是自己先把資料從一張表中取出，再到另一張表中執行WHEREIN操作，這原理和上面SQL語句實作的是一樣的。
MySQL5.6對子查詢的優化？
SEMI JOIN政策
優化器會識别出需要子查詢的IN語句以便從區域表傳回每個區域鍵的一個執行個體。這就導緻了MySQL會以半連接配接的方式執行SELECT語句，是以全局表中每個區域隻會有一個執行個體與記錄相比對。
半連接配接和正常連接配接之間存在兩個非常重要的差別：
- 在半連接配接中，内表不會導緻重複的結果。
- 此操作不會有内表中的字段添加到結果中去。
是以，半連接配接的結果常常是來自外表記錄的一個子集。從有效性上看，半連接配接的優化在于有效的消除了來自内表的重複項，MySQL應用了四個不同的半連接配接執行政策用來去重。
Table Pullout優化
Convert the subquery to a join, or use table pullout and run the query as an inner join between subquery tables and outer tables. Table pullout pulls a table out from the subquery to the outer query.将子查詢轉變為一個連接配接，或是利用table pullout并将查詢作為子查詢表和外表之間的一個内連接配接來執行。Table pullout會為外部查詢從子查詢抽取出一個表。
有些時候，一個子查詢可以被重寫為JOIN，例如：
SELECT OrderID FROM Orders where EmployeeID IN (select EmployeeID from Employees where EmployeeID > 3);
如果知道OrderID是唯一的，即主鍵或者唯一索引，那麼SQL語句會被重寫為Join形式。
SELECT OrderID FROM Orders join Employees where Orders.EmployeeID = Employees.EmployeeID and Employees.EmployeeID > 3;
Table pullout的作用就是根據唯一索引将子查詢重寫為JOIN語句，在MySql 5.5中，上述的SQL語句執行計劃：
如果通過EXPLAIN EXTENDED 和 SHOW WARNINGS指令，可以看到如下結果：
select `northwind`.`Orders`.`OrderID` AS `OrderID` from `northwind`.`Orders` where <in_optimizer>(`northwind`.`Orders`.`EmployeeID`,<exists>(<primary_index_lookup>(<cache>(`northwind`.`Orders`.`EmployeeID`) in Employees on PRIMARY where ((`northwind`.`Employees`.`EmployeeID` > 3) and (<cache>(`northwind`.`Orders`.`EmployeeID`) = `northwind`.`Employees`.`EmployeeID`)))))
正是上面說的in為什麼慢？
在MySql 5.6中，優化器會對SQL語句重寫，得到的執行計劃：
在MySql 5.6中，優化器沒有将獨立子查詢重寫為相關子查詢，通過EXPLAIN EXTENDED 和 SHOW WARNINGS指令，得到優化器的執行方式為：
/* select#1 */ select `northwind`.`orders`.`OrderID` AS `OrderID` from `northwind`.`employees` join `northwind`.`orders` where ((`northwind`.`orders`.`EmployeeID` = `northwind`.`employees`.`EmployeeID`) and (`northwind`.`employees`.`EmployeeID` > 3))
很顯然，優化器将上述子查詢重寫為JOIN語句，這就是Table Pullout優化。
Duplicate Weedout優化
Run the semi-join as if it was a join and remove duplicate records using a temporary table.執行半連接配接，就如同它是一個連接配接并利用臨時表移除了重複的記錄。
上面内部表查出的列是唯一的，是以優化器會将子查詢重寫為JOIN語句，以提高SQL執行的效率。Duplicate Weedout優化是指外部查詢條件是列是唯一的，MySql優化器會先将子查詢查出的結果進行去重。比如下面這條SQL語句：
SELECT ContactName FROM Customers where CustomerID in (select CustomerID from Orders where OrderID > 10000 and Customers.Country = Orders.ShipCountry);
因為CustomerID是主鍵，是以應該對子查詢得到的結果進行去重。在MySql 5.6中的執行計劃：
Extra選項提示的Start temporary表示建立一張去重的臨時表，End temporary表示删除該臨時表。而通過EXPLAIN EXTENDED 和 SHOW WARNINGS指令，得到優化器的執行方式為：
/* select#1 */ select `northwind`.`customers`.`ContactName` AS `ContactName` from `northwind`.`customers` semi join (`northwind`.`orders`) where ((`northwind`.`customers`.`CustomerID` = `northwind`.`orders`.`CustomerID`) and (`northwind`.`customers`.`Country` = `northwind`.`orders`.`ShipCountry`) and (`northwind`.`orders`.`OrderID` > 10000))
與Table Pullout優化不同的是，顯示的是semi join而不是join，其中原因在于多了一些去重的工作，對于上述的執行計劃，其掃描成本約為830+830*1=1660次。
而在MySql 5.5中的執行計劃為：
可以看到，在MySql 5.5中還是将語句轉化為相關子查詢，掃描成本約為93+93*9=930次。
我們可以看到MySql 5.6優化以後比5.5的掃描成本反而大，其實這隻是在兩張表較小的的情況下的結果，如果表很大，優化的效果會非常明顯。
Materialization優化
Materialize the subquery into a temporary table with an index and use the temporary table to perform a join. The index is used to remove duplicates. The index might also be used later for lookups when joining the temporary table with the outer tables; if not, the table is scanned.
上面的子查詢是相關子查詢，如果子查詢是獨立子查詢，則優化器可以選擇将獨立子查詢産生的結果填充到單獨一張物化臨時表中，如圖：
根據JOIN的順序，Materialization優化可分為：
- Materialization scan：JOIN是将物化臨時表和表進行關聯。
- Materialization lookup：JOIN是将表和物化臨時表進行關聯。
下面的子查詢可以利用Materialization來進行優化：
SELECT OrderID FROM Orders where OrderID in (select OrderID from `Order Details` where UnitPrice < 50 );
SQL語句的執行計劃：
可以看到，在進行JOIN時（也就是id為1的步驟），先掃描的表是Orders，然後是subquery2，是以這是Materialization lookup的優化。對于下面的SQL：
select * FROM driver where driver_id in (select uid from user);
先掃描的是subquery2，再是driver表，這就是Materialization scan的優化。
FirstMacth優化
When scanning the inner tables for row combinations and there are multiple instances of a given value group, choose one rather than returning them all. This "shortcuts" scanning and eliminates production of unnecessary rows.為了對記錄進行合并而在掃描内表，并且對于給定值群組有多個執行個體時，選擇其一而不是将它們全部傳回。這為表掃描提供了一個早期退出機制而且還消除了不必要記錄的産生。
半連接配接的最先比對(FirstMatch)政策執行子查詢的方式與MySQL稍早版本中的IN-TO-EXISTS是非常相似的。對于外表中的每條比對記錄，MySQL都會在内表中進行比對檢查。當發現存在比對時，它會從外表傳回記錄。隻有在未發現比對的情況下，引擎才會回退去掃描整個内表。
LooseScan優化
Scan a subquery table using an index that enables a single value to be chosen from each subquery's value group.利用索引來掃描一個子查詢表可以從每個子查詢的值群組中選出一個單一的值。
SEMI JOIN變量
Each of these strategies except Duplicate Weedout can be enabled or disabled using the optimizer_switch system variable. The semijoin flag controls whether semi-joins are used. If it is set to on, the firstmatch, loosescan, and materialization flags enable finer control over the permitted semi-join strategies. These flags are on by default.除Duplicate Weedout之外的每個政策可以用變量控制開關，semijoin控制semi-joins優化是否開啟，如果設定開啟，其他的政策也有獨立的變量控制。所有的變量在5.6預設是打開的。
mysql> SELECT @@optimizer_switch\G;
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)
EXPLAIN檢視政策
- Semi-joined tables show up in the outer select. EXPLAIN EXTENDED plus SHOW WARNINGS shows the rewritten query, which displays the semi-join structure. From this you can get an idea about which tables were pulled out of the semi-join. If a subquery was converted to a semi-join, you will see that the subquery predicate is gone and its tables and WHERE clause were merged into the outer query join list and WHERE clause.
- Temporary table use for Duplicate Weedout is indicated by Start temporary and End temporary in the Extra column. Tables that were not pulled out and are in the range of EXPLAIN output rows covered by Start temporary and End temporary will have their rowid in the temporary table.
- FirstMatch(tbl_name) in the Extra column(列) indicates join shortcutting.
- LooseScan(m..n) in the Extra column indicates use of the LooseScan strategy. m and n are key part numbers.
- As of MySQL 5.6.7, temporary table use for materialization is indicated by rows with a select_type value of MATERIALIZED and rows with a table value of .
- Before MySQL 5.6.7, temporary table use for materialization is indicated in the Extra column by Materialize if a single table is used, or by Start materialize and End materialize if multiple tables are used. If Scan is present, no temporary table index is used for table reads. Otherwise, an index lookup is used.
上面介紹中FirstMacth優化、LooseScan優化的具體效果沒有很好的例子去顯示出來。有機會可以交流學習。
參考
有不對的地方希望大家多交流，謝謝。
《MySql技術内幕：SQL程式設計》
http://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html
http://tech.it168.com/a2013/0506/1479/000001479749.shtml
轉載請注明出處。
作者：wuxiwei
出處：http://www.cnblogs.com/wxw16/p/6105624.html