您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 存儲過程優化實戰：從亂碼到高性能的五步重構

編輯：MySQL 綜合教學

原始存儲過程（存在五大問題）

DROP PROCEDURE IF EXISTS pr_dealtestnum;
DELIMITER //
CREATE PROCEDURE pr_dealtestnum (IN p_boxnumber VARCHAR(30))
pr_dealtestnum_label:BEGIN
  INSERT INTO tb_testnum SELECT boxnumber, usertype FROM tb_testnum_tmp WHERE boxnumber = p_boxnumber;
  LEAVE pr_dealtestnum_label;
END; //
DELIMITER ;

表結構：

-- 目標表
CREATE TABLE tb_testnum (
  boxnumber VARCHAR(30) NOT NULL,
  usertype INT NOT NULL,
  UNIQUE KEY idx1_tb_testnum (boxnumber)
) ENGINE=InnoDB;

-- 源表
CREATE TABLE tb_testnum_tmp (
  boxnumber VARCHAR(30) NOT NULL,
  usertype INT NOT NULL,
  UNIQUE KEY idx1_tb_testnum_tmp (boxnumber)
) ENGINE=InnoDB;

五輪重構優化

優化一：拆分 INSERT ... SELECT，提升可讀性與調試性

DECLARE p_usertype INT;
SELECT usertype INTO p_usertype FROM tb_testnum_tmp WHERE boxnumber = p_boxnumber;
INSERT INTO tb_testnum VALUES (p_boxnumber, p_usertype);

收益：SQL 與業務邏輯分離，便於單獨驗證查詢結果，異常時精確定位是查詢還是插入失敗。

優化二：雙重存在性檢查，避免主鍵衝突與空指針

DECLARE p_datacount INT;

-- 源表存在性
SELECT COUNT(*) INTO p_datacount FROM tb_testnum_tmp WHERE boxnumber = p_boxnumber;
IF p_datacount = 0 THEN LEAVE pr_dealtestnum_label; END IF;

SELECT usertype INTO p_usertype FROM tb_testnum_tmp WHERE boxnumber = p_boxnumber;

-- 目標表唯一性
SELECT COUNT(*) INTO p_datacount FROM tb_testnum WHERE boxnumber = p_boxnumber;
IF p_datacount > 0 THEN LEAVE pr_dealtestnum_label; END IF;

INSERT INTO tb_testnum VALUES (p_boxnumber, p_usertype);

收益：業務邏輯層面保證冪等性，不依賴數據庫報錯回滾，性能更優（COUNT 走索引極快）。

優化三：引入輸出參數標識執行結果

CREATE PROCEDURE pr_dealtestnum (
  IN p_boxnumber VARCHAR(30),
  OUT p_result INT  -- 0 成功，1 源表無數據，2 目標表已存在
)
-- 內部各分支 SET p_result = 對應碼; LEAVE ...

收益：調用方無需捕獲異常即可知曉業務狀態，便於上層重試/告警邏輯。

優化四：顯式列出 INSERT 列名，防範 Schema 變更風險

INSERT INTO tb_testnum (boxnumber, usertype) VALUES (p_boxnumber, p_usertype);

收益：未來給 tb_testnum 新增列（如 create_time）時，存儲過程不會報錯，實現向後兼容。

優化五：檢查 `@error_count` 確認寫入真正成功

INSERT INTO tb_testnum (boxnumber, usertype) VALUES (p_boxnumber, p_usertype);
IF @error_count <> 0 THEN
  SET p_result = 3;  -- 寫入失敗（如磁盤滿、鎖等待超時）
ELSE
  SET p_result = 0;
END IF;

收益：捕獲運行時異常（非邏輯錯誤），區分業務失敗與系統故障。

最終優化版本

DROP PROCEDURE IF EXISTS pr_dealtestnum;
DELIMITER //
CREATE PROCEDURE pr_dealtestnum (
  IN p_boxnumber VARCHAR(30),
  OUT p_result INT  -- 0=成功 1=源無數據 2=目標已存在 3=寫入異常
)
pr_dealtestnum_label:BEGIN
  DECLARE p_usertype INT;
  DECLARE p_datacount INT;

  -- 源表校驗
  SELECT COUNT(*) INTO p_datacount FROM tb_testnum_tmp WHERE boxnumber = p_boxnumber;
  IF p_datacount = 0 THEN SET p_result = 1; LEAVE pr_dealtestnum_label; END IF;

  SELECT usertype INTO p_usertype FROM tb_testnum_tmp WHERE boxnumber = p_boxnumber;

  -- 目標表唯一性校驗
  SELECT COUNT(*) INTO p_datacount FROM tb_testnum WHERE boxnumber = p_boxnumber;
  IF p_datacount > 0 THEN SET p_result = 2; LEAVE pr_dealtestnum_label; END IF;

  -- 執行插入並檢查運行時錯誤
  INSERT INTO tb_testnum (boxnumber, usertype) VALUES (p_boxnumber, p_usertype);
  IF @error_count <> 0 THEN SET p_result = 3; ELSE SET p_result = 0; END IF;

  LEAVE pr_dealtestnum_label;
END; //
DELIMITER ;