MySql子查詢IN的執(zhí)行和優(yōu)化的實現(xiàn)

更新時間：2021年07月29日 10:40:01 作者：wuxiwei

本文主要介紹了MySql子查詢IN的執(zhí)行和優(yōu)化的實現(xiàn)，詳細的介紹了為什么IN這么慢以及如何優(yōu)化，具有一定的參考價值，感興趣的可以了解一下

IN為什么慢？

在應(yīng)用程序中使用子查詢后，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語句的執(zhí)行計劃：

可以看到上面的SQL語句變成了相關(guān)子查詢，通過EXPLAIN EXTENDED 和 SHOW WARNINGS命令，可以看到如下結(jié)果：

復(fù)制代碼代碼如下:

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優(yōu)化器直接把IN子句轉(zhuǎn)換成了EXISTS的相關(guān)子查詢。下面這條相關(guān)IN子查詢：

SELECT driver_id FROM driver where driver_id in (SELECT driver_id FROM user where user.uid = driver.driver_id);

查看SQL語句的執(zhí)行計劃：

就是相關(guān)子查詢，通過EXPLAIN EXTENDED 和 SHOW WARNINGS命令，看到如下結(jié)果：

復(fù)制代碼代碼如下:

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

可以看出無論是獨立子查詢還是相關(guān)子查詢，MySql 5.5之前的優(yōu)化器都是將IN轉(zhuǎn)換成EXISTS語句。如果子查詢和外部查詢分別返回M和N行，那么該子查詢被掃描為O(N+N*M)，而不是O(N+M)。這也就是為什么IN慢的原因。

IN和EXISTS哪個快？

網(wǎng)上百度到很多認為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列的索引。

總結(jié)上面的描述，個人認為其主要的原因在于對索引的使用。任何情況下，只要是大表的索引被使用，就可以使效率提高。

但是在編輯本文的時候，多次測試，卻沒能得到上面所總結(jié)的結(jié)果。下面是測試SQL語句，先是外表為大表，內(nèi)表為小表。（示例一）

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

執(zhí)行結(jié)果是：

再是外表是小表，內(nèi)表是大表。（示例二）

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

執(zhí)行結(jié)果是：

可以發(fā)現(xiàn)IN和EXISTS的執(zhí)行效率，在任何情況下都正好是相同的?；诖?，我們繼續(xù)查看示例一兩條SQL語句的執(zhí)行計劃，如下：

可以看到IN和EXISTS的執(zhí)行計劃是一樣的，對此得出的結(jié)論兩者的執(zhí)行效率應(yīng)該是一樣的。

《MySql技術(shù)內(nèi)幕：SQL編程》：書中描述的確實有很多DBA認為EXISTS比IN的執(zhí)行效率更高，可能是當時優(yōu)化器還不是很穩(wěn)定和足夠優(yōu)秀，但是目前絕大數(shù)的情況下，IN和EXISTS都具有相同的執(zhí)行計劃。

如何提高效率？

上面示例二中的SQL語句執(zhí)行時間約8秒，因為存在M*N的原因造成慢查詢，但是還是可以進行優(yōu)化，注意到慢的原因就是內(nèi)部每次與外部比較時，都需要遍歷一次表操作，可以采用另外一個方法，在嵌套一層子查詢，避免多次遍歷操作，語句如下：

SELECT count(driver_id) FROM driver where exists (SELECT uid FROM (SELECT uid from user) as b where b.uid = driver.driver_id);

執(zhí)行效果如圖：

可以發(fā)現(xiàn)優(yōu)化減少了6s多的執(zhí)行時間，下面是SQL的執(zhí)行計劃：

同樣的還是相關(guān)子查詢，但是減少了內(nèi)部遍歷查詢的操作。所以可以通過預(yù)查詢來減少遍歷操作，而提高效率。

其實在實際編程中，很多開發(fā)人員選擇不使用連接表查詢，而是自己先把數(shù)據(jù)從一張表中取出，再到另一張表中執(zhí)行WHEREIN操作，這原理和上面SQL語句實現(xiàn)的是一樣的。

MySQL5.6對子查詢的優(yōu)化？

SEMI JOIN策略

優(yōu)化器會識別出需要子查詢的IN語句以便從區(qū)域表返回每個區(qū)域鍵的一個實例。這就導(dǎo)致了MySQL會以半連接的方式執(zhí)行SELECT語句，所以全局表中每個區(qū)域只會有一個實例與記錄相匹配。

半連接和常規(guī)連接之間存在兩個非常重要的區(qū)別：

在半連接中，內(nèi)表不會導(dǎo)致重復(fù)的結(jié)果。
此操作不會有內(nèi)表中的字段添加到結(jié)果中去。

因此，半連接的結(jié)果常常是來自外表記錄的一個子集。從有效性上看，半連接的優(yōu)化在于有效的消除了來自內(nèi)表的重復(fù)項，MySQL應(yīng)用了四個不同的半連接執(zhí)行策略用來去重。

Table Pullout優(yōu)化

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.將子查詢轉(zhuǎn)變?yōu)橐粋€連接，或是利用table pullout并將查詢作為子查詢表和外表之間的一個內(nèi)連接來執(zhí)行。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的作用就是根據(jù)唯一索引將子查詢重寫為JOIN語句，在MySql 5.5中，上述的SQL語句執(zhí)行計劃：

如果通過EXPLAIN EXTENDED 和 SHOW WARNINGS命令，可以看到如下結(jié)果：

復(fù)制代碼代碼如下:

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中，優(yōu)化器會對SQL語句重寫，得到的執(zhí)行計劃：

在MySql 5.6中，優(yōu)化器沒有將獨立子查詢重寫為相關(guān)子查詢，通過EXPLAIN EXTENDED 和 SHOW WARNINGS命令，得到優(yōu)化器的執(zhí)行方式為：

復(fù)制代碼代碼如下:

/* 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))

很顯然，優(yōu)化器將上述子查詢重寫為JOIN語句，這就是Table Pullout優(yōu)化。

Duplicate Weedout優(yōu)化

Run the semi-join as if it was a join and remove duplicate records using a temporary table.執(zhí)行半連接，就如同它是一個連接并利用臨時表移除了重復(fù)的記錄。

上面內(nèi)部表查出的列是唯一的，因此優(yōu)化器會將子查詢重寫為JOIN語句，以提高SQL執(zhí)行的效率。Duplicate Weedout優(yōu)化是指外部查詢條件是列是唯一的，MySql優(yōu)化器會先將子查詢查出的結(jié)果進行去重。比如下面這條SQL語句：

SELECT ContactName FROM Customers where CustomerID in (select CustomerID from Orders where OrderID > 10000 and Customers.Country = Orders.ShipCountry);

因為CustomerID是主鍵，所以應(yīng)該對子查詢得到的結(jié)果進行去重。在MySql 5.6中的執(zhí)行計劃：

Extra選項提示的Start temporary表示創(chuàng)建一張去重的臨時表，End temporary表示刪除該臨時表。而通過EXPLAIN EXTENDED 和 SHOW WARNINGS命令，得到優(yōu)化器的執(zhí)行方式為：

復(fù)制代碼代碼如下:

/* 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優(yōu)化不同的是，顯示的是semi join而不是join，其中原因在于多了一些去重的工作，對于上述的執(zhí)行計劃，其掃描成本約為830+830*1=1660次。
而在MySql 5.5中的執(zhí)行計劃為：

可以看到，在MySql 5.5中還是將語句轉(zhuǎn)化為相關(guān)子查詢，掃描成本約為93+93*9=930次。

我們可以看到MySql 5.6優(yōu)化以后比5.5的掃描成本反而大，其實這只是在兩張表較小的的情況下的結(jié)果，如果表很大，優(yōu)化的效果會非常明顯。

Materialization優(yōu)化

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.

上面的子查詢是相關(guān)子查詢，如果子查詢是獨立子查詢，則優(yōu)化器可以選擇將獨立子查詢產(chǎn)生的結(jié)果填充到單獨一張物化臨時表中，如圖：

根據(jù)JOIN的順序，Materialization優(yōu)化可分為：

Materialization scan：JOIN是將物化臨時表和表進行關(guān)聯(lián)。
Materialization lookup：JOIN是將表和物化臨時表進行關(guān)聯(lián)。

下面的子查詢可以利用Materialization來進行優(yōu)化：

SELECT OrderID FROM Orders where OrderID in (select OrderID from `Order Details` where UnitPrice < 50 );

SQL語句的執(zhí)行計劃：

可以看到，在進行JOIN時（也就是id為1的步驟），先掃描的表是Orders，然后是subquery2，因此這是Materialization lookup的優(yōu)化。對于下面的SQL：

select * FROM driver where driver_id in (select uid from user);

SQL語句的執(zhí)行計劃：

先掃描的是subquery2，再是driver表，這就是Materialization scan的優(yōu)化。

FirstMacth優(yōu)化

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.為了對記錄進行合并而在掃描內(nèi)表，并且對于給定值群組有多個實例時，選擇其一而不是將它們?nèi)糠祷?。這為表掃描提供了一個早期退出機制而且還消除了不必要記錄的產(chǎn)生。

半連接的最先匹配(FirstMatch)策略執(zhí)行子查詢的方式與MySQL稍早版本中的IN-TO-EXISTS是非常相似的。對于外表中的每條匹配記錄，MySQL都會在內(nèi)表中進行匹配檢查。當發(fā)現(xiàn)存在匹配時，它會從外表返回記錄。只有在未發(fā)現(xiàn)匹配的情況下，引擎才會回退去掃描整個內(nèi)表。

LooseScan優(yōu)化

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之外的每個策略可以用變量控制開關(guān)，semijoin控制semi-joins優(yōu)化是否開啟，如果設(shè)置開啟，其他的策略也有獨立的變量控制。所有的變量在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優(yōu)化、LooseScan優(yōu)化的具體效果沒有很好的例子去顯示出來。有機會可以交流學習。