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

IN為什么慢？

在應(yīng)用程序中使用子查詢后，SQL語(yǔ)句的查詢性能變得非常糟糕。例如：

SELECT driver_id FROM driver where driver_id in (SELECT driver_id FROM driver where _create_date > '2016-07-25 00:00:00');

獨(dú)立子查詢返回了符合條件的driver_id，這個(gè)問(wèn)題是解決了，但是所用的時(shí)間需要6秒，可以通過(guò)EXPLAIN查看SQL語(yǔ)句的執(zhí)行計(jì)劃：

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

可以看出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語(yǔ)句的執(zhí)行計(jì)劃：

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

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

IN和EXISTS哪個(gè)快？

網(wǎng)上百度到很多認(rèn)為IN和EXISTS效率一樣是錯(cuò)誤的文章。

如果查詢的兩個(gè)表大小相當(dāng)，那么用in和exists差別不大。
如果兩個(gè)表中一個(gè)較小，一個(gè)是大表，則子查詢表大的用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é)上面的描述，個(gè)人認(rèn)為其主要的原因在于對(duì)索引的使用。任何情況下，只要是大表的索引被使用，就可以使效率提高。

但是在編輯本文的時(shí)候，多次測(cè)試，卻沒(méi)能得到上面所總結(jié)的結(jié)果。下面是測(cè)試SQL語(yǔ)句，先是外表為大表，內(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語(yǔ)句的執(zhí)行計(jì)劃，如下：

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

《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ì)劃。

如何提高效率？

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

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í)行時(shí)間，下面是SQL的執(zhí)行計(jì)劃：

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

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

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

SEMI JOIN策略

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

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

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

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

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)橐粋€(gè)連接，或是利用table pullout并將查詢作為子查詢表和外表之間的一個(gè)內(nèi)連接來(lái)執(zhí)行。Table pullout會(huì)為外部查詢從子查詢抽取出一個(gè)表。

有些時(shí)候，一個(gè)子查詢可以被重寫為JOIN，例如：

SELECT OrderID FROM Orders where EmployeeID IN (select EmployeeID from Employees where EmployeeID > 3);

如果知道OrderID是唯一的，即主鍵或者唯一索引，那么SQL語(yǔ)句會(huì)被重寫為Join形式。

SELECT OrderID FROM Orders join Employees where Orders.EmployeeID = Employees.EmployeeID and Employees.EmployeeID > 3;

Table pullout的作用就是根據(jù)唯一索引將子查詢重寫為JOIN語(yǔ)句，在MySql 5.5中，上述的SQL語(yǔ)句執(zhí)行計(jì)劃：

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

正是上面說(shuō)的in為什么慢？

在MySql 5.6中，優(yōu)化器會(huì)對(duì)SQL語(yǔ)句重寫，得到的執(zhí)行計(jì)劃：

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

很顯然，優(yōu)化器將上述子查詢重寫為JOIN語(yǔ)句，這就是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í)行半連接，就如同它是一個(gè)連接并利用臨時(shí)表移除了重復(fù)的記錄。

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

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

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

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

與Table Pullout優(yōu)化不同的是，顯示的是semi join而不是join，其中原因在于多了一些去重的工作，對(duì)于上述的執(zhí)行計(jì)劃，其掃描成本約為830+830*1=1660次。
而在MySql 5.5中的執(zhí)行計(jì)劃為：

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

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

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)子查詢，如果子查詢是獨(dú)立子查詢，則優(yōu)化器可以選擇將獨(dú)立子查詢產(chǎn)生的結(jié)果填充到單獨(dú)一張物化臨時(shí)表中，如圖：

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

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

下面的子查詢可以利用Materialization來(lái)進(jìn)行優(yōu)化：

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

SQL語(yǔ)句的執(zhí)行計(jì)劃：

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

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

SQL語(yǔ)句的執(zhí)行計(jì)劃：

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

半連接的最先匹配(FirstMatch)策略執(zhí)行子查詢的方式與MySQL稍早版本中的IN-TO-EXISTS是非常相似的。對(duì)于外表中的每條匹配記錄，MySQL都會(huì)在內(nèi)表中進(jìn)行匹配檢查。當(dāng)發(fā)現(xiàn)存在匹配時(shí)，它會(huì)從外表返回記錄。只有在未發(fā)現(xiàn)匹配的情況下，引擎才會(huì)回退去掃描整個(gè)內(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.利用索引來(lái)掃描一個(gè)子查詢表可以從每個(gè)子查詢的值群組中選出一個(gè)單一的值。

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之外的每個(gè)策略可以用變量控制開關(guān)，semijoin控制semi-joins優(yōu)化是否開啟，如果設(shè)置開啟，其他的策略也有獨(dú)立的變量控制。所有的變量在5.6默認(rèn)是打開的。

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)化的具體效果沒(méi)有很好的例子去顯示出來(lái)。有機(jī)會(huì)可以交流學(xué)習(xí)。

參考

《MySql技術(shù)內(nèi)幕：SQL編程》

http://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html

http://tech.it168.com/a2013/0506/1479/000001479749.shtml

到此這篇關(guān)于MySql子查詢IN的執(zhí)行和優(yōu)化的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MySql子查詢IN 內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！