MySQL 存儲過程游標循環極慢優化指南：從逐行處理到批量集合操作

性能瓶頸根因

原存儲過程使用 CURSOR 逐行讀取 t_person_plan（關聯 500 萬行 t_person_plan_record），並在循環內對每行執行多條 SELECT COUNT(*)、INSERT/UPDATE。游標屬於逐行處理（RBAR，Row-By-Agonizing-Row），上下文切換、鎖競爭、索引查找開銷巨大，導致 2 小時僅處理 10 萬行。

優化核心思路：集合化批量操作

將「循環讀取 → 逐條判斷 → 逐條寫入」重構為 「單條 SQL 完成全量數據讀取、分組、寫入」。MySQL 8.0+ 支持 CTE（Common Table Expressions）與窗口函數，配合 INSERT ... ON DUPLICATE KEY UPDATE 可一次性完成「不存在則插入，存在則更新」。

重構步驟示例

1. 先用 CTE 彙總所需中間數據

WITH plan_base AS (
  SELECT p1.id AS person_plan_id,
         p1.start_learn_time,
         p1.last_date,
         p1.sum_study_time,
         p1.target_study_time
  FROM t_person_plan p1
  WHERE p1.license_type = 7
    AND p1.last_date <= CURRENT_DATE()
),
stage_times AS (
  SELECT person_plan_id,
         MAX(CASE WHEN chapter_id = 29611 THEN add_time END) AS first_end_time,
         MAX(CASE WHEN chapter_id = 29625 THEN add_time END) AS sec_end_time,
         MAX(CASE WHEN chapter_id = 29734 THEN add_time END) AS third_end_time
  FROM t_person_plan_record
  WHERE person_plan_id IN (SELECT person_plan_id FROM plan_base)
  GROUP BY person_plan_id
),
stage_counts AS (
  SELECT person_plan_id,
         SUM(stage = 1) AS id1_count,
         SUM(stage = 2) AS id2_count,
         SUM(stage = 3) AS id3_count
  FROM t_stage_info
  WHERE person_plan_id IN (SELECT person_plan_id FROM plan_base)
  GROUP BY person_plan_id
)

2. 單條 INSERT ... ON DUPLICATE KEY UPDATE 寫入三階段

INSERT INTO t_stage_info (person_plan_id, start_time, end_time, sum_study_time, target_study_time, stage)
SELECT pb.person_plan_id,
       CASE s.stage
         WHEN 1 THEN pb.start_learn_time
         WHEN 2 THEN COALESCE(st.first_end_time, pb.last_date)
         WHEN 3 THEN COALESCE(st.sec_end_time, pb.last_date)
       END AS start_time,
       CASE s.stage
         WHEN 1 THEN COALESCE(st.first_end_time, pb.last_date)
         WHEN 2 THEN COALESCE(st.sec_end_time, pb.last_date)
         WHEN 3 THEN COALESCE(st.third_end_time, pb.last_date)
       END AS end_time,
       pb.sum_study_time,
       pb.target_study_time,
       s.stage
FROM plan_base pb
JOIN stage_times st ON pb.person_plan_id = st.person_plan_id
JOIN stage_counts sc ON pb.person_plan_id = sc.person_plan_id
JOIN (SELECT 1 AS stage UNION SELECT 2 UNION SELECT 3) s
  ON (s.stage = 1 AND pb.sum_study_time <= 12)
  OR (s.stage = 2 AND pb.sum_study_time > 12 AND pb.sum_study_time <= 14)
  OR (s.stage = 3 AND pb.sum_study_time > 14)
ON DUPLICATE KEY UPDATE
  end_time = VALUES(end_time),
  sum_study_time = VALUES(sum_study_time),
  target_study_time = VALUES(target_study_time);

前提：t_stage_info 需在 (person_plan_id, stage) 上建立唯一索引（或主鍵），使 ON DUPLICATE KEY UPDATE 生效。

關鍵索引優化

預期效果

• 從 O(N) 輪次交互 降為 3~4 條 SQL，執行時間通常從小時級降至秒級或分鐘級。
• 減少鎖持有時間，降低主從複製延遲。
• 代碼可維護性大幅提升，邏輯集中在 SQL 層，便於 EXPLAIN 分析。

避坑提醒

• 大事務風險：若數據量極大（百萬級以上），單條 SQL 可能產生巨大 Undo Log，導致鎖升級或複製延遲。可按 person_plan_id 分批（如每 5 萬行）循環執行上述 SQL。
• NULL 值處理：COALESCE 確保階段結束時間不為 NULL 時回退到 last_date。
• 版本兼容：MySQL 5.7 無 CTE，可改用臨時表或子查詢嵌套實現相同邏輯。

核心原則：能用 SQL 集合操作解決的，絕不寫游標循環。這是關係型數據庫性能優化的黃金法則。