OB_MYSQL UPDATE 最佳化案例

小至尖尖發表於2024-06-18
MySql

在工單系統上看到有一條SQL問題還沒解決，直接聯絡這位同學看看是否需要幫忙。

慢SQL：

UPDATE  A
SET CORPORATION_NAME = (
    SELECT DISTINCT CORPORATION_NAME
    FROM (
        SELECT CONTRACT_NO, 
               COOP_SERVICE_TYPE, 
               CORPORATION_NAME, 
               PROJECT_NAME, 
               ROW_NUMBER() OVER (PARTITION BY CONTRACT_NO, COOP_SERVICE_TYPE ) AS SEQ
        FROM O_PLIS_PROC B
        WHERE B.BDHA_TX_DATE='2024-06-10' AND A.LM_CT1_NO = B.CONTRACT_NO
    ) B
    WHERE B.COOP_SERVICE_TYPE='01' AND B.SEQ = 1
)
WHERE LM_CT1_NO IN (
    SELECT  CONTRACT_NO
    FROM O_PLIS_PROC C
    WHERE C.CONTRACT_NO=A.LM_CT1_NO
    AND C.COOP_SERVICE_TYPE='01'
    AND C.BDHA_TX_DATE='2024-06-10'
);

執行計劃：

上面sql 跑超時都跑不出結果，估計要執行非常長時間。

這條sql in 後面關聯返回107911行資料，update set ... 可以理解成標量子查詢，返回1107911資料相當於 update set 標量子查詢也要執行107911次。

標量子查詢最重要的是要走對索引，然而這個sql計劃根本沒走索引，這位同學的問題也是如何透過改寫來消除標量子查詢，很明顯這個思路是錯誤的。

新增合適的索引：

CREATE INDEX TEST ON O_PLIS_PROC(
　　BDHA_TX_DATE,
　　COOP_SERVICE_TYPE,
　　CONTRACT_NO,
　　COOP_SERVICE_TYPE,
　　CORPORATION_NAME);

很明顯，建立索引以後計劃顯示能用上索引，sql整體5秒能執行完成。

再提供個相同邏輯的等價改寫方案：

WITH O_PLIS_PROC as (
    SELECT 
           CONTRACT_NO,
           COOP_SERVICE_TYPE,
           CORPORATION_NAME
     FROM O_PLIS_PROC 
     WHERE BDHA_TX_DATE='2024-06-10' AND COOP_SERVICE_TYPE='01'
)
UPDATE A
SET CORPORATION_NAME = (
    SELECT  CORPORATION_NAME 
        FROM O_PLIS_PROC B 
    WHERE A.LM_CT1_NO = B.CONTRACT_NO  GROUP BY CONTRACT_NO, COOP_SERVICE_TYPE LIMIT 1 ) 
WHERE LM_CT1_NO IN (
    SELECT  CONTRACT_NO
    FROM O_PLIS_PROC C
    WHERE C.CONTRACT_NO=A.LM_CT1_NO
);

改寫後的sql 5秒能跑出結果，和原來邏輯一樣，提升不大。

遇到效能慢的sql語句，不要一上來就想著等價改寫，先透過索引進行最佳化，合理的索引能解決90%的效能問題。

如果索引都解決不了的情況下，才去嘗試使用等價改寫來進行最佳化sql，一般來說等價改寫能解決剩下5%的問題。

如果連等價改寫都解決不了剩下的5%的效能問題話，就要嘗試改業務，或者改資料庫技術棧來解決問題了，這種通常來說成本會非常高。

相關文章