一則MySQL派生表優化案例-有解無憂

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

開始之前，先看一下MySQL 5.7.20下面的奇葩的現象，感受一下MySQL對派生表的支持有多弱，
如下寫法，盡管id為主鍵的情況下，依舊會出現一個奇葩的全表掃描,
一個認為不太可能出問題的sql，他就是結結實實地出現了問題，而且還很嚴重，所以不服不行，

什么是派生表

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

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

測驗場景

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

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

這是典型的一條資料示例（訂單和其物流資訊

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

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

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

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

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

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

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

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

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

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

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

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

MySQL 8.0的分析函式
其實之前的寫法都是為了實作row_number這個分析函式的功能，如果直接采用MySQL 8.0分析函式，SQL會極大地地得到簡化，性能也會飛起來，

總結

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

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;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/62702.html

標籤：MySQL

上一篇：求助Access報表中計算控制元件運算式的問題

下一篇：對"易購網上商城"專案開發的總結