作者：胡呈清
愛可生 DBA 團(tuán)隊成員，擅長故障分析、性能優(yōu)化，個人博客：
https://www.jianshu.com/u/a95ec11f67a8，歡迎討論。本文來源：原創(chuàng)投稿
*愛可生開源社區(qū)出品，原創(chuàng)內(nèi)容未經(jīng)授權(quán)不得隨意使用，轉(zhuǎn)載請聯(lián)系小編并注明來源。
有這么一個 SQL，外查詢 where 子句的 bizCustomerIncoming_id 字段，和子查詢 where 字句的 cid 字段都有高效索引，為什么這個 SQL 執(zhí)行的非常慢，需要全表掃描？
delete FROM biz_customer_incoming_path WHERE bizCustomerIncoming_id IN
(SELECT id FROM biz_customer_incoming WHERE cid='315upfdv34umngfrxxxxxx');
我們從這么一個問題來引入接下來的內(nèi)容，如果你知道答案就不用繼續(xù)看下去了。
子查詢優(yōu)化策略
對于不同類型的子查詢，優(yōu)化器會選擇不同的策略。
1. 對于 IN、=ANY 子查詢，優(yōu)化器有如下策略選擇：
- semijoin
- Materialization
- exists
2. 對于 NOT IN、<>ALL 子查詢，優(yōu)化器有如下策略選擇：
- Materialization
- exists
3. 對于 derived 派生表，優(yōu)化器有如下策略選擇：
- derived_merge，將派生表合并到外部查詢中（5.7 引入 ）；
- 將派生表物化為內(nèi)部臨時表，再用于外部查詢。
注意：update 和 delete 語句中子查詢不能使用 semijoin、materialization 優(yōu)化策略
優(yōu)化思路
那么這些策略分別是什么意思？為什么會有這些優(yōu)化策略？
為方便分析，先建兩張表：
CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB;
drop procedure idata;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into t2 values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
create table t1 like t2;
insert into t1 (select * from t2 where id<=100)
有以下子查詢示例：
SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id < 10);
你肯定認(rèn)為這個 SQL 會這樣執(zhí)行：
SELECT t2.b FROM t2 WHERE id < 10;
結(jié)果:1,2,3,4,5,6,7,8,9
select * from t1 where t1.a in(1,2,3,4,5,6,7,8,9);
但實際上 MySQL 并不是這樣做的。MySQL 會將相關(guān)的外層表壓到子查詢中，優(yōu)化器認(rèn)為這樣效率更高。也就是說，優(yōu)化器會將上面的 SQL 改寫成這樣：
select * from t1 where exists(select b from t2 where id < 10 and t1.a=t2.b);
執(zhí)行計劃為：
+----+--------------------+-------+-------+---------+------+----------+-------------+
| id | select_type | table | type | key | rows | filtered | Extra |
+----+--------------------+-------+-------+---------+------+----------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | 100 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | range | PRIMARY | 9 | 10.00 | Using where |
+----+--------------------+-------+-------+---------+------+----------+-------------+
不相關(guān)子查詢變成了關(guān)聯(lián)子查詢（select_type:DEPENDENT SUBQUERY），子查詢需要根據(jù) b 來關(guān)聯(lián)外表 t1，因為需要外表的 t1 字段，所以子查詢是沒法先執(zhí)行的。執(zhí)行流程為：
1. 掃描 t1，從 t1 取出一行數(shù)據(jù) R；
2. 從數(shù)據(jù)行 R 中，取出字段 a 執(zhí)行子查詢，如果得到結(jié)果為 TRUE，則把這行數(shù)據(jù) R 放到結(jié)果集；
3. 重復(fù) 1、2 直到結(jié)束。
總的掃描行數(shù)為 100+100*9=1000（這是理論值，實際值為 964，怎么來的一直沒想明白，看規(guī)律是子查詢結(jié)果集每多一行，總掃描行數(shù)就會少幾行）。
Semi-join
這樣會有個問題，如果外層表是一個非常大的表，對于外層查詢的每一行，子查詢都得執(zhí)行一次，這個查詢的性能會非常差。我們很容易想到將其改寫成 join 來提升效率：
select t1.* from t1 join t2 on t1.a=t2.b and t2.id<10;
這樣優(yōu)化可以讓 t2 表做驅(qū)動表，t1 表關(guān)聯(lián)字段有索引，查找效率非常高。
但這里會有個問題，join 是有可能得到重復(fù)結(jié)果的，而 in(select ...) 子查詢語義則不會得到重復(fù)值。而 semijoin 正是解決重復(fù)值問題的一種特殊聯(lián)接。在子查詢中，優(yōu)化器可以識別出 in 子句中每組只需要返回一個值，在這種情況下，可以使用 semijoin 來優(yōu)化子查詢，提升查詢效率。這是 MySQL 5.6 加入的新特性，MySQL 5.6 以前優(yōu)化器只有 exists 一種策略來“優(yōu)化”子查詢。經(jīng)過 semijoin 優(yōu)化后的 SQL 和執(zhí)行計劃分為：
select
`t1`.`id`,`t1`.`a`,`t1`.`b`
from `t1` semi join `t2`
where
((`t1`.`a` = `<subquery2>`.`b`)
and (`t2`.`id` < 10));
##注意這是優(yōu)化器改寫的SQL，客戶端上是不能用 semi join 語法的
+----+--------------+-------------+-------+---------+---------------+------+-------------+
| id | select_type | table | type | key | ref | rows | Extra |
+----+--------------+-------------+-------+---------+---------------+------+-------------+
| 1 | SIMPLE | <subquery2> | ALL | NULL | NULL | NULL | Using where |
| 1 | SIMPLE | t1 | ref | a | <subquery2>.b | 1 | NULL |
| 2 | MATERIALIZED | t2 | range | PRIMARY | NULL | 9 | Using where |
+----+--------------+-------------+-------+---------+---------------+------+-------------+
semijoin 優(yōu)化實現(xiàn)比較復(fù)雜，其中又分 FirstMatch、Materialize 等策略，上面的執(zhí)行計劃中 select_type=MATERIALIZED 就是代表使用了 Materialize 策略來實現(xiàn)的 semijoin，后面有專門的文章介紹 semijoin，這里不展開。這里 semijoin 優(yōu)化后的執(zhí)行流程為：
1. 先執(zhí)行子查詢，把結(jié)果保存到一個臨時表中，這個臨時表有個主鍵用來去重；
2. 從臨時表中取出一行數(shù)據(jù) R；
3. 從數(shù)據(jù)行 R 中，取出字段 b 到被驅(qū)動表 t1 中去查找，滿足條件則放到結(jié)果集；
4. 重復(fù)執(zhí)行 2、3，直到結(jié)束。
這樣一來，子查詢結(jié)果有 9 行，即臨時表也有 9 行（這里沒有重復(fù)值），總的掃描行數(shù)為 9+9+9*1=27 行，比原來的 1000 行少了很多。
Materialization
MySQL 5.6 版本中加入的另一種優(yōu)化特性 materialization，就是把子查詢結(jié)果物化成臨時表，然后代入到外查詢中進(jìn)行查找，來加快查詢的執(zhí)行速度。內(nèi)存臨時表包含主鍵（hash 索引），消除重復(fù)行，使表更小。如果子查詢結(jié)果太大，超過 tmp_table_size 大小，會退化成磁盤臨時表。這跟前面提到的“我們誤以為的”過程相似，這樣子查詢只需要執(zhí)行一次，而不是對于外層查詢的每一行都得執(zhí)行一遍。不過要注意的是，這樣外查詢依舊無法通過索引快速查找到符合條件的數(shù)據(jù)，只能通過全表掃描或者全索引掃描，materialization 優(yōu)化后的執(zhí)行計劃為：
+----+-------------+-------+-------+---------+------+------+-------------+
| id | select_type | table | type | key | ref | rows | Extra |
+----+-------------+-------+-------+---------+------+------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | 100 | Using where |
| 2 | SUBQUERY | t2 | range | PRIMARY | NULL | 9 | Using where |
+----+-------------+-------+-------+---------+------+------+-------------+
總掃描行數(shù)為 100+9=109。
semijoin 和 materialization 的開啟是通過 optimizer_switch 參數(shù)中的 semijoin={on|off}、materialization={on|off} 標(biāo)志來控制的。上文中不同的執(zhí)行計劃就是對 semijoin 和 materialization 進(jìn)行開/關(guān)產(chǎn)生的。特意考古找了下 MySQL 5.5 的官方手冊，優(yōu)化策略相當(dāng)稀少：
總的來說對于子查詢，先檢查是否滿足各種優(yōu)化策略的條件（比如子查詢中有 union 則無法使用 semijoin 優(yōu)化），然后優(yōu)化器會按成本進(jìn)行選擇，實在沒得選就會用 exists 策略來“優(yōu)化”子查詢，exists 策略是沒有參數(shù)來開啟或者關(guān)閉的。
小結(jié)
回到開篇的問題，答案是：delete 無法使用 semijoin、materialization 優(yōu)化策略，會以 exists 方式執(zhí)行，外查詢即 delete
biz_customer_incoming_path 表時必須要進(jìn)行全表掃描。優(yōu)化的方法也很簡單，改成 join 即可（這里是 delete，不用擔(dān)心重復(fù)行問題）：
delete
biz_customer_incoming_path
FROM biz_customer_incoming_path a join biz_customer_incoming b
WHERE
a.bizCustomerIncoming_id=b.id
and b.cid='7Ex46Dz22Fqq6iuPCLPlzQ';
參考資料
1. https://dev.mysql.com/doc/refman/5.7/en/subquery-optimization.html
2. 《高性能 MySQL》第 6.5.1 章節(jié)
