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

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

同樣的表結(jié)構(gòu)，在sqlserver里面，按照預(yù)期的走了索引的seek

什么是派生表

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

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

測試場景

假設(shè)是在MySQL的關(guān)系數(shù)據(jù)中，試想有這個一個查詢：一個訂單表以及對應(yīng)的物流信息表，關(guān)系為1：N，查詢訂單和其最新的1條物流信息，這個查詢該怎么寫（假設(shè)問題存在而不論證其是否合理）？
相信實(shí)現(xiàn)起來并不復(fù)雜，如果是查看單條訂單的物流信息，兩張表join 起來，按照時間倒序取第一條即可，如果要查詢多條訂單的信息，或者是某一段時間內(nèi)所有的訂單的該信息呢？
如果是是商業(yè)數(shù)據(jù)庫或者是MySQL 8.0中有現(xiàn)成的分析函數(shù)可以用，如果是MySQL 5.7，沒有現(xiàn)成的分析函數(shù)，該怎么寫呢？
簡單demo一下，說明問題即可：加入t1表示訂單表，t2表示物流信息表，c1為訂單號（關(guān)聯(lián)鍵），t1和t2中的數(shù)據(jù)為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ù)據(jù)，也就是說一條訂單存在10條物流信息，其訂單的物流信息的創(chuàng)建時間隨機(jī)分布在一定的時間范圍。測試數(shù)據(jù)在百萬級就夠了。

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ù)據(jù)示例（訂單和其物流信息

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

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

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

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

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

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

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

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

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

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

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

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

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

總結(jié)

以上通過一個簡單的案例，來說了了derived_merge的限制，可能這些在其他數(shù)據(jù)庫上不是問題的問題，在MySQL上都是問題，實(shí)際上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;