一、問題 SQL
原 SQL 如下：
<code>select name,count(name) from bm_id a left JOIN (select TaskName from up_pro_accept_v3_bdc union all select TaskName from up_pro_accept_v3_hsjs union all select TaskName from up_pro_accept_v3_hszjj union all select TaskName from up_pro_accept_v3_hzl union all select TaskName from up_pro_accept_v3_kjyw union all select TaskName from up_pro_accept_v3_kpzzzxwx union all select TaskName from up_pro_accept_v3_qdzc union all select TaskName from up_pro_accept_v3_rsj union all select TaskName from up_pro_accept_v3_sjba union all select TaskName from up_pro_accept_v3_spk union all select TaskName from up_pro_accept_v3_test union all select TaskName from up_pro_accept_v3_wygl union all select TaskName from up_pro_accept_v3_yms union all select TaskName from up_pro_accept_v3_zjj union all select TaskName from up_pro_accept_v3w) t on a.zxi = t.TaskName group by name/<code>
這是一個統計類的 SQL，直接執行跑了好幾個小時都沒有結束，所以暫時不知道實際耗時，因為實在是太久了~
二、執行計劃
老步驟，我們先看下執行計劃，如下圖：
這裡 SQL 執行主要分為 2 個步驟：
1. 順序掃描每個 up_pro_accept 開頭的子表數據，最終組成 t 表(派生表)。
- 掃描 t 表(派生表) 相關的所有子表，可以看到這裡每張子表走的都是 全表掃描，有些表較大，有 100 多 w，檢索較慢。
2. a 表(bm_id) 與 t表(派生表) 進行關聯查詢，得到最後的結果。
- t 表 (派生表) 作為 被驅動表 大約 164W 行 左右，與 a 表做關聯查詢時走的是 全表掃描(ALL)， a 表(bm_id) 作為 驅動表 大約 1.3W 行 左右，也就是說，表關聯需要全表掃描 t 表(派生表) 1.3W 次，而每次都需要掃描 164W 行 數據，顯然 SQL 的絕大部分時間其實都花在這一步上。
那麼其實 SQL 優化也分為了 2 步，首先是多張子表的全表掃描，是否可以用索引掃描替換，加快數據檢索。
而後是主要的環節，這個派生表作為被驅動表時，是否可以走索引？如果不能走索引，有沒有其他方式減少 SQL 開銷？
三、派生表
既然這個 SQL 優化涉及到了派生表，那麼我們先看下何謂派生表，派生表有什麼特性？
Derivedtable(派生表) 實際上是一種特殊的 subquery(子查詢)，它位於 SQL 語句中 FROM 子句 裡面，可以看做是一個單獨的表。
MySQL 5.7 之前的處理都是對 Derived table(派生表) 進行 Materialize(物化)，生成一個 臨時表 用於保存 Derived table(派生表) 的結果，然後利用 臨時表 來協助完成其他父查詢的操作，比如 JOIN 等操作。
MySQL 5.7 中對 Derived table(派生表) 做了一個新特性，該特性允許將符合條件的 Derived table(派生表) 中的子表與父查詢的表合併進行直接 JOIN，類似於 Oracle 中的 子查詢展開，由優化器參數 optimizer_switch='derived_merge=ON' 來控制，默認為 打開。
但是 derived_merge 特性存在很多限制，當派生子查詢存在以下操作時，該特性無法生效。DISTINCT、 GROUP BY、 UNION/UNION ALL 、 HAVING、 關聯子查詢、 LIMIT/OFFSET 以及 聚合操作 等。
舉個簡單例子：
其中 a 表就是一個派生表
1. 如果走 derived_merge 特性，那麼可以走主鍵索引，速度非常快。
2. 如果關閉 derived_merge 特性，那麼就會走全表掃描，速度非常慢。
<code>select * from (select * from up_pro_accept_v3_bdc) awhere a.rowguid = '185c44aa-c23f-4e6f-bcd2-a38df16e2cc3'/<code>
四、SQL 優化
簡單介紹了下派生表，下面我們開始嘗試優化這個 SQL，步驟分 2 步：
1. 解決多張派生子表 union all 時全表掃描的問題。
2. 解決派生表在關聯過程中無法使用索引的問題。
我們先解決問題 1，這個問題比較簡單。
因為所有派生子表的查詢都是 select TaskName from up_pro_accept_v3_xxx 類似這樣，且外部關聯字段也是 taskname，所以我們只要在對應表上建立 taskname 的索引即可。
建好索引後，我們再看下執行計劃，所有的派生子表都走了 index 掃描，那麼問題 1 基本解決了，但是由於 t 表(派生表) 在關聯時還是走的全表掃描，並沒有用到 derived_merge 特性，所以 SQL 還是非常非常慢（上萬 s）。
接著我們來解決問題 2，這裡主要解決派生表無法走索引的問題。
從之前介紹派生表的內容來看，想要派生表走索引，就需要用到 derived_merge 特性，將外部條件推入進子查詢，但是這個特性的限制條件也很多，就比如我們這個 SQL，因為子查詢裡包括了 union all，那麼該 SQL 是無法利用到 derived_merge 特性的，因此無法直接走索引過濾。
既然無法在原有 SQL 的基礎上優化，那麼我們只能考慮改寫 SQL，通過 SQL 改寫來達到優化的目的。
這裡 SQL 其實是因為 驅動表 bm_id 最終是和派生表作表關聯，導致無法利用索引，我們可以嘗試將 驅動表 bm_id 也放到子查詢中，只要前後語義是一致的，那麼改寫就沒問題。這樣就可以在子查詢裡就走完表關聯，剩下的就是外部的分組排序，我們嘗試下。
<code>/* 改寫後 SQL */SELECT NAME ,count(NAME)FROM ( SELECT NAME FROM bm_id LEFT JOIN up_pro_accept_v3_bdc bdc ON bm_id.zxi = bdc.TaskName UNION ALL SELECT NAME FROM bm_id LEFT JOIN up_pro_accept_v3_hsjs hsjs ON bm_id.zxi = hsjs.TaskName UNION ALL SELECT NAME FROM bm_id LEFT JOIN up_pro_accept_v3_hszjj hszjj ON bm_id.zxi = hszjj.TaskName UNION ALL SELECT NAME FROM bm_id LEFT JOIN up_pro_accept_v3_hzl hzl ON bm_id.zxi = hzl.TaskName UNION ALL SELECT NAME FROM bm_id LEFT JOIN up_pro_accept_v3_kjyw kjyw ON bm_id.zxi = kjyw.TaskName UNION ALL SELECT NAME FROM bm_id LEFT JOIN up_pro_accept_v3_kpzzzxwx kp ON bm_id.zxi = kp.TaskName UNION ALL SELECT NAME FROM bm_id LEFT JOIN up_pro_accept_v3_qdzc qdzc ON bm_id.zxi = qdzc.TaskName UNION ALL SELECT NAME FROM bm_id LEFT JOIN up_pro_accept_v3_rsj rsj ON bm_id.zxi = rsj.TaskName UNION ALL SELECT NAME FROM bm_id LEFT JOIN up_pro_accept_v3_sjba sjba ON bm_id.zxi = sjba.TaskName UNION ALL SELECT NAME FROM bm_id LEFT JOIN up_pro_accept_v3_spk spk ON bm_id.zxi = spk.TaskName UNION ALL SELECT NAME FROM bm_id LEFT JOIN up_pro_accept_v3_test test ON bm_id.zxi = test.TaskName UNION ALL SELECT NAME FROM bm_id LEFT JOIN up_pro_accept_v3_wygl wygl ON bm_id.zxi = wygl.TaskName UNION ALL SELECT NAME FROM bm_id LEFT JOIN up_pro_accept_v3_yms yms ON bm_id.zxi = yms.TaskName UNION ALL SELECT NAME FROM bm_id LEFT JOIN up_pro_accept_v3_zjj zjj ON bm_id.zxi = zjj.TaskName UNION ALL SELECT NAME FROM bm_id LEFT JOIN up_pro_accept_v3w v3w ON bm_id.zxi = v3w.TaskName ) tGROUP BY t.name/<code>
再來看下改寫後的 SQL 執行計劃，發現確實如我們預想的，在子查詢中可以通過索引來進行表關聯（ 被驅動表 type 為 ref），然後 union all 匯聚數據，形成派生表，最後掃描派生表進行分組排序。
這裡分組排序時只需要 全表掃描一次派生表 就可以得到結果，效率比之前快太多了！
改寫後的 SQL 運行耗時為 13s 左右，速度快很多！
五、測試驗證
為了嚴謹性，我們需要驗證改寫後的 SQL 結果集是否與原始 SQL 一致，也就是證明下這樣改寫 SQL 是否會產生語義上的變化，如果為了優化 SQL，連結果集都不準了，那就沒意義了~
這裡因為原始 SQL 執行太久，沒法直接得到結果集對比，那麼我們只能通過手動創建臨時表來記錄子查詢結果集，然後再與 bm_id 表 關聯查詢，由於我們可以在臨時表上創建索引，就不會出現原始 SQL 那種全表掃描的問題啦。
具體操作如下：
1. 創建臨時表（帶索引）
<code>CREATE TABLE `tmp_up` ( `taskname` varchar(500) DEFAULT NULL, KEY `idx_taskname` (`taskname`));/<code>
2. 將子查詢結果插入至臨時表
<code>insert into tmp_upselect taskname from up_pro_accept_v3_bdcunion all select taskname up_pro_accept_v3_hsjs....../<code>
3. 使用臨時表代替子查詢
<code>select name,count(name) from bm_id a left JOIN (select TaskName from tmp_up )t on a.zxi = t.TaskName group by name/<code>
4. 對比下查詢結果是否一致
驚訝的發現改寫 SQL 的結果集會多出來很多？這裡可以確認走臨時表的結果集是肯定沒問題的，那麼問題肯定出在改寫 SQL 上！
回頭再仔細想一下，結合小測試，發現這樣改寫 SQL 確實會改變語義，問題主要是出在 LEFT JOIN，原本 bm_id 只做了 一次表關聯，而改寫 SQL 後，要做 多次表關聯，導致最後的結果集會多出來一部分因為 LEFT JOIN 而產生的重複數據。
如果是 INNER JOIN，其實就不會產生重複數據，我們也測試下，結果確實如所想，內聯是沒問題的~
六、個人總結
這次 SQL 優化案例個人感覺是比較有難度的，很多點自己一開始也沒有想到。就比如 SQL 改寫，一開始以為是沒有語義上的區別，直到做了測試才知道，所以啊，很多時候不能盲目自信啊。
針對這個 SQL 來說，想要直接通過改寫 SQL 優化還是比較難的，當然這裡說的是不改變語義的情況下，我暫時沒有想到好的改寫方式，也許是火候還不夠。
解決方式總結有 2 個：
1. 用 內聯 替代 左聯，然後使用上述的改寫 SQL，優點是 比較方便且查詢速度較快，但是 結果集會變化。
2. 通過 臨時表 代替 子查詢，缺點是 比較繁瑣，需要多個步驟實現，優點是 速度也較快 且 結果集不會變化。
附錄：
http://mysql.taobao.org/monthly/2017/03/05/
https://blog.csdn.net/sun_ashe/article/details/89522394
https://imysql.com/node/103
https://dev.mysql.com/doc/refman/5.7/en/derived-table-optimization.html
閱讀更多 愛可生 的文章
