MySQL 函數效能優化實戰：從 13 秒降至 1 秒的索引重寫之路

MySQL 函數效能優化實戰：從 13 秒降至 1 秒的索引重寫之路

某舊系統存儲函數 F_getBuluDates 單次調用耗時 20~30 秒，經分析發現核心問題在於 FIND_IN_SET 導致全表掃描。本文完整記錄優化過程：定位瓶頸、重寫邏輯、建立組合索引、驗證效果，最終效能提升 11 倍。

一、原始函數瓶頸分析

REPEAT
  SELECT MAX(a2104) INTO maxRiZhiDate
  FROM a21
  WHERE issubmit='y' AND a2104 < tmpdate AND FIND_IN_SET(a2104, bldate)=0;
UNTIL tmpdate <= nowdate END REPEAT;

問題：

• FIND_IN_SET(col, str) 無法使用索引，導致每次迴圈全表掃描 a21（十幾萬筆）。
• 迴圈內多次呼叫自訂函數 F_isWorkDay、F_ifInSet、F_getid1value2、F_removeFromSet，進一步放大開銷。

二、優化策略：游標 + 索引友好條件

1. 替換迴圈為游標：一次查詢取得符合條件的日期集合，避免重複掃描。
2. 移除 FIND_IN_SET：改用 NOT IN (SELECT ...) 或應用層集合判斷，使 WHERE 條件可走索引。
3. 建立組合索引：ADD INDEX idx_issubmit_a2104 (issubmit, a2104); 覆蓋查詢欄位。

三、重寫後的核心邏輯

CREATE FUNCTION F_getBuluDates_inner(PuserId INT, tmpdate VARCHAR(32), nowdate VARCHAR(32))
RETURNS VARCHAR(3000) CHARSET gbk
BEGIN
  DECLARE no_more_data INT DEFAULT 0;
  DECLARE maxRiZhiDate VARCHAR(255);
  DECLARE bldate VARCHAR(3000) DEFAULT '';
  -- 只掃描一次，按日期倒序
  DECLARE my_cursor CURSOR FOR
    SELECT DISTINCT a2104
    FROM a21
    WHERE issubmit='y' AND a2104 < tmpdate AND a2104 >= nowdate
    ORDER BY a2104 DESC;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_data = 1;

  -- 其餘業務邏輯保持不變...
  OPEN my_cursor;
  FETCH my_cursor INTO maxRiZhiDate;
  REPEAT
    -- 業務判斷邏輯
    FETCH my_cursor INTO maxRiZhiDate;
  UNTIL no_more_data = 1 END REPEAT;
  CLOSE my_cursor;
  RETURN bldate;
END;

四、對外調用入口函數

CREATE FUNCTION F_getBuluDates_new(PuserId INT) RETURNS VARCHAR(3000) CHARSET gbk
BEGIN
  DECLARE bldate VARCHAR(3000) DEFAULT '';
  DECLARE tmpdate, nowdate VARCHAR(32);
  DECLARE yxblts INT;
  SELECT a5204 INTO yxblts FROM a52 WHERE a5218='gzrz' LIMIT 1;
  SET tmpdate = CURDATE();
  SET nowdate = DATE_SUB(tmpdate, INTERVAL yxblts DAY);
  SET bldate = F_getBuluDates_inner(PuserId, tmpdate, nowdate);
  RETURN bldate;
END;

五、驗證結果

mysql> SELECT F_getBuluDates_new(10687);
-- 1.10 sec，回傳正確日期清單

mysql> SELECT F_getBuluDates(10687);   -- 原函數
-- 13.10 sec，結果一致

mysql> EXPLAIN SELECT DISTINCT a2104 FROM a21
  WHERE issubmit='y' AND a2104 < '2016-08-16' AND a2104 >= '2016-08-10'
  ORDER BY a2104 DESC;
+----+-------------+-------+-------+----------------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys        | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------------+----------------+---------+------+------+--------------------------+
| 1  | SIMPLE      | a21   | range | a2104,issubmit_a2104 | issubmit_a2104 | 516     | NULL | 834  | Using where; Using index |
+----+-------------+-------+-------+----------------------+----------------+---------+------+------+--------------------------+

• 執行計畫顯示 type=range、key=issubmit_a2104、Using index（覆蓋索引），預估掃描 834 行，遠低於全表掃描。
• 效能提升 11.9 倍（13.1s → 1.1s），客戶滿意驗收。

六、核心經驗總結

1. 函數內避免在 WHERE 條件對欄位做運算/函數呼叫，否則索引失效。
2. 大數據量迴圈查詢改用游標或單次集合查詢，減少重複掃描。
3. 組合索引順序遵循「等值優先、範圍在後、覆蓋查詢」 原則。
4. EXPLAIN 是優化利器，重點關注 type、key、rows、Extra。
5. 業務邏輯下推至應用層：複雜字串拼接、集合運算在 SQL 中處理效率低，建議查回原始資料後由應用層完成。

結語：這次優化未修改表結構、未增加硬體資源，僅透過 SQL 重寫與索引調整即獲數量級提升。說明「寫對 SQL、建對索引」往往是性價比最高的優化手段。