MySql子查詢IN的執行和優化的實現方法

這篇文章主要講解了“MySql子查詢IN的執行和優化的實現方法”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“MySql子查詢IN的執行和優化的實現方法”吧！

目錄

• IN為什么慢？

• IN和EXISTS哪個快？

• 如何提高效率？

• MySQL5.6對子查詢的優化？

• SEMI JOIN策略

• Duplicate Weedout優化

• Materialization優化

• FirstMacth優化

• LooseScan優化

• SEMI JOIN變量

• 參考

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);

SQL語句的執行計劃：

先掃描的是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

感謝各位的閱讀，以上就是“MySql子查詢IN的執行和優化的實現方法”的內容了，經過本文的學習后，相信大家對MySql子查詢IN的執行和優化的實現方法這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！