筆者最近遇到一則典型的因為sql中存在派生表造成的性能案例，通過改寫SQL改善了的性能，但當時并沒有弄清楚這其中的原因，派生表究竟是什么原因會導致性能上的副作用。
說來也巧，很快就無意中就看到下文中的提到的相關的派生表的介紹以及其特性之后，才發(fā)現(xiàn)個中緣由，本文基于此，用一個非常簡單的demo來演示該問題，同時警惕MySQL中派生表的使用。

開始之前，先看一下MySQL 5.7.20下面的奇葩的現(xiàn)象，感受一下MySQL對派生表的支持有多弱。
如下寫法，盡管c2字段上有索引的情況下，依舊會出現(xiàn)一個奇葩的索引遍歷（而不是預期的索引查找)
一個認為不太可能出問題的sql，他就是結結實實地出現(xiàn)了問題，而且還很嚴重，所以不服不行。

同樣的表結構，在sqlserver里面，按照預期的走了索引的seek

什么是派生表

關于派生表的定義，不贅述了，以下截圖來自于愛可生公司的公眾號中，說的非常清晰，連接地址為：https://mp.weixin.qq.com/s/CxagKla3Z6Q6RJ-x5kuUAA，侵刪，謝謝。

這里我們主要關注它在與父查詢join時的一些限制，如果派生表中存在distinct，group by union /union all,having，關聯(lián)子查詢，limit offset等，也即父查詢的參數(shù)無法傳遞到派生表的查詢中，導致一些性能上的問題。

測試場景

假設是在MySQL的關系數(shù)據中，試想有這個一個查詢：一個訂單表以及對應的物流信息表，關系為1：N，查詢訂單和其最新的1條物流信息，這個查詢該怎么寫（假設問題存在而不論證其是否合理）？
相信實現(xiàn)起來并不復雜，如果是查看單條訂單的物流信息，兩張表join 起來，按照時間倒序取第一條即可，如果要查詢多條訂單的信息，或者是某一段時間內所有的訂單的該信息呢？
如果是是商業(yè)數(shù)據庫或者是MySQL 8.0中有現(xiàn)成的分析函數(shù)可以用，如果是MySQL 5.7，沒有現(xiàn)成的分析函數(shù)，該怎么寫呢？
簡單demo一下，說明問題即可：加入t1表示訂單表，t2表示物流信息表，c1為訂單號（關聯(lián)鍵），t1和t2中的數(shù)據為1對多。

CREATE TABLE t1
(
    id INT AUTO_INCREMENT PRIMARY key,
    c1 INT,
    c2 VARCHAR(50),
    create_date datetime
);


CREATE TABLE t2
(
    id INT  AUTO_INCREMENT PRIMARY key,
    c1 INT,
    c2 VARCHAR(50),
    create_date datetime
);

CREATE INDEX idx_c1 ON t1(c1);
CREATE INDEX idx_c1 ON t2(c1);

按照1:10的比例往兩張表中寫入測試數(shù)據，也就是說一條訂單存在10條物流信息，其訂單的物流信息的創(chuàng)建時間隨機分布在一定的時間范圍。測試數(shù)據在百萬級就夠了。

CREATE DEFINER=`root`@`%` PROCEDURE `create_test_data`(
    IN `loop_count` INT
)
BEGIN
    SET @p_loop = 0;
    while @p_loop<loop_count do
    
        SET @p_date = DATE_ADD(NOW(),INTERVAL -RAND()*100 DAY);
        
        INSERT INTO t1 (c1,c2,create_date) VALUES (@p_loop,UUID(),@p_date);
        
        INSERT INTO t2 (c1,c2,create_date) VALUES (@p_loop,UUID(),DATE_ADD(@p_date,INTERVAL RAND()*10000 MINUTE));
        INSERT INTO t2 (c1,c2,create_date) VALUES (@p_loop,UUID(),DATE_ADD(@p_date,INTERVAL RAND()*10000 MINUTE));
        INSERT INTO t2 (c1,c2,create_date) VALUES (@p_loop,UUID(),DATE_ADD(@p_date,INTERVAL RAND()*10000 MINUTE));
        INSERT INTO t2 (c1,c2,create_date) VALUES (@p_loop,UUID(),DATE_ADD(@p_date,INTERVAL RAND()*10000 MINUTE));
        INSERT INTO t2 (c1,c2,create_date) VALUES (@p_loop,UUID(),DATE_ADD(@p_date,INTERVAL RAND()*10000 MINUTE));
        INSERT INTO t2 (c1,c2,create_date) VALUES (@p_loop,UUID(),DATE_ADD(@p_date,INTERVAL RAND()*10000 MINUTE));
        INSERT INTO t2 (c1,c2,create_date) VALUES (@p_loop,UUID(),DATE_ADD(@p_date,INTERVAL RAND()*10000 MINUTE));
        INSERT INTO t2 (c1,c2,create_date) VALUES (@p_loop,UUID(),DATE_ADD(@p_date,INTERVAL RAND()*10000 MINUTE));
        INSERT INTO t2 (c1,c2,create_date) VALUES (@p_loop,UUID(),DATE_ADD(@p_date,INTERVAL RAND()*10000 MINUTE));
        INSERT INTO t2 (c1,c2,create_date) VALUES (@p_loop,UUID(),DATE_ADD(@p_date,INTERVAL RAND()*10000 MINUTE));
        
        SET @p_loop = @p_loop+1;

    END while;
END

這是典型的一條數(shù)據示例（訂單和其物流信息

派生表的性能問題
按照最通用的寫法，就是實現(xiàn)一個類似于商業(yè)數(shù)據庫中的row_number()功能，按照訂單號分組，然后求給每個訂單號的物流信息排序，最后取第一條物流信息即可。
為了簡單起見，這個SQL只查詢一個訂單的最新的物流信息。
于是就這么寫了一個語句，其中通過一個派生表，用到了典型的row_number()分析函數(shù)等價實現(xiàn)，這個語句執(zhí)行起來邏輯上當然沒有什么問題，結果也完全符合預期，但是執(zhí)行時間會接近3秒鐘，這個是遠遠超出過預期的。

這里插一句：很多人包括面試都會問，SQL優(yōu)化有哪些技巧？
不排除一部分人的言外之意就是要你列舉出來一些”固定的套路”，比如where條件怎么樣了，索引怎么建了，什么亂七八糟的，列舉出來一大堆，這么多年過去了，這中套路式的列舉依然是我最最最討厭的套路。
實際情況千變萬化，固定的套路可能會好使，但是更多的時候，需要根據是情況做具體分析，而不是死套套路，如果真的有一個（系列）規(guī)則可以套，那么執(zhí)行計劃是不是又回到最原始的RBO模式了？
面對一個需要優(yōu)化的SQL，弄清楚這個sql的邏輯之后：先不管它實際上是怎么執(zhí)行的，首先自己心中要有一個執(zhí)行計劃，要有一個預期的執(zhí)行方式，理論上是相對較好的一種執(zhí)行方式(計劃)。
1，如果按照預期的方式執(zhí)行，但是性能并沒有達到預期，需要反思是什么因素造成的？
2，如果沒有按照預期的方式執(zhí)行，同樣需要反思了，為什么沒有按照預期的方式執(zhí)行，可能會是什么原因造成的？

對于這個SQL，我個人傾向于先通過派生表對子表做一個清晰的排序實現(xiàn)，然后父查詢進行過濾（篩選最新的一條數(shù)據），
我個人臆測的執(zhí)行計劃如下：
因為join條件是t.c1 = a.c1，where條件是a.c1 = 99999，按道理來說，是比較清晰的邏輯，既然a.c1 = 99999又t.c1 = a.c1，這個篩選條件會直接推進到子查詢（派生表內部），篩選一下就完事了
這個性能表面，實際的執(zhí)行計劃很可能不是這么走的，其實卻是出乎意料的。

可以看到，派生表內部是一個全表掃描，也就是說跟t2做做一個全表掃描，然后對每個訂單的物流信息排序，然后再根據外層的查詢進行訂單號的篩選（where a.c1 = 99999）
這個可以說是完全出乎意料的，一開始并不知道外層的查詢條件，是無法能推進到派生表內部來做過濾的。

這里涉及到一個derived_merge相關的實現(xiàn)，
指的是一種查詢優(yōu)化技術，作用就是把派生表合并到外部的查詢中，提高數(shù)據檢索的效率。這個特性在MySQL5.7版本中被引入（參考https://blog.csdn.net/sun_ashe/article/details/89522394）。
舉一個實際的例子，比如對于select * from （select * from table_name）t where id= 100;
用土話說就是，外層查詢的條件會推進到派生表的子查詢中，實際的執(zhí)行過程就變?yōu)椋簊elect * from （select * from table_name where id =100）t where id= 100，
在商業(yè)數(shù)據庫中，這一切都是非常的自然的一個過程，在MySQL中是不一定的，所以不得不承認MySQL的優(yōu)化器太弱了。

基于此重新改寫了一下SQL，如下，主表和子表先join起來，同時對子表進行排序，然后再外層篩選最新的一條信息（t.sort_num = 1），
改寫之后，這個查詢只需要0.125秒，大概有20倍+的提升，這是沒有任何外界條件的變化的情況下。

其實這個執(zhí)行計劃，才是上面提到的“預期的”執(zhí)行計劃，篩選條件同時應用到了兩張表中，進過篩選之后再做邏輯上的排序計算。
至于為什么第一次沒有用到這些寫法，其實寫SQL每個人都有自己的習慣，個人的思路就是首先可以做到不牽涉任何join的時候，先對目標對象進行排序計算等等，完成份內的事之后，然后再join主表取數(shù)據。
個人認為這是一種寫法的邏輯看上去更加清晰易懂，尤其是在較多的表join的時候，每一步先完成自己份內的事，然后再跟其他表join（當然這也是一個見仁見智的問題，個人思路都可能不一樣，這里有點跑偏了）
如果上上述第一種寫法，在SqlServer或者其他關系數(shù)據庫中，是完全等價于第二種寫法的，所以一開始是沒有預料到這種巨大的性能差異的。

其實這里就可以不回歸到本文一開始提到的派生表的限制了，這個截圖來自于這里：https://blog.csdn.net/sun_ashe/article/details/89522394，侵刪。
任何走到continue中的邏輯，都是無法實現(xiàn)外層查詢篩選條件推進到派生表的子查詢的。
也就是說派生表中存在distinct，group by union /union all,having，關聯(lián)子查詢，limit offset，變量等情況下，無法進行derived_merge。

可以認為，任何一個走向continue的分支的情況，都是無法使用derived_merge的。

其實本文中的示例SQL繼續(xù)簡化一下，就非常明顯了，這里不去join任何表，僅對t2表做一個分析查詢，然后刻意基于派生表實現(xiàn)篩選，其執(zhí)行計劃并不是理想中的索引查找
也就是說，select * from （select * from table_name）t where id= 100在某些條件下是無法轉換為select * from （select * from table_name where id =100）t where id= 100的，外層查詢條件無法推進到內層查詢中。

上文中的查詢，與join的參與并無關系，其實就派生表中有用戶變量造成的，這里看到執(zhí)行計劃走的是一個全表掃描

如果不使用派生表的方式，其執(zhí)行計劃就是索引查找

MySQL 8.0的分析函數(shù)
其實之前的寫法都是為了實現(xiàn)row_number這個分析函數(shù)的功能，如果直接采用MySQL 8.0分析函數(shù)，SQL會極大地地得到簡化，性能也會飛起來。

總結

以上通過一個簡單的案例，來說了了derived_merge的限制，可能這些在其他數(shù)據庫上不是問題的問題，在MySQL上都是問題，實際上MySQL優(yōu)化器還是需要提升的。
如果一旦有類似派生表的情況，可能會遇到有性能問題，還是需要值得注意的。

demo的sql

SET @sort_num=0;
SET @group_category=NULL;
SELECT 
a.c1,a.c2 AS order_info,a.create_date AS order_date,t.c2 AS express_log,t.create_date AS express_log_date
FROM t1 a INNER JOIN 
(
    SELECT 
            IF(@group_category=b.c1, @sort_num:=@sort_num+1, @sort_num:=1) sort_num,
            IF(@group_category=b.c1, @group_category, @group_category:=b.c1) group_category,
            b.*
    FROM t2 b 
    ORDER BY b.c1 DESC , b.create_date DESC 
)t ON t.c1 = a.c1
WHERE a.c1 = 99999 AND t.sort_num = 1;



SET @sort_num=0;
SET @group_category=NULL;
SELECT 
*
FROM 
(
    SELECT 
            IF(@group_category=b.c1, @sort_num:=@sort_num+1, @sort_num:=1) sort_num,
            IF(@group_category=b.c1, @group_category, @group_category:=b.c1) group_category,
            a.c1,a.c2 AS order_info,
            a.create_date AS order_date,
            b.c2 AS express_log,
            b.create_date AS express_log_date
    FROM t1 a inner join t2 b ON a.c1 = b.c1
    WHERE  a.c1 = 99999 
    ORDER BY b.c1 DESC , b.create_date DESC 
)t
WHERE t.sort_num = 1;


SELECT 
*
FROM 
(
    SELECT 
    row_number()over(PARTITION BY a.c1 ORDER BY b.create_date desc) as sort_num,
    a.c1,
    a.c2 AS order_info,
    a.create_date AS order_date,
    b.c2 AS express_log,
    b.create_date AS express_log_date
    FROM t1 a inner join t2 b ON a.c1 = b.c1
    WHERE b.c1 = 99999 
)t
WHERE t.sort_num = 1;