MySQL事務程式設計效能與問題分析 [開發必看]

沒有一句廢話，滿滿的乾貨，直接進入分析：

1.在循環中提交的問題

很多開發人員非常喜歡在循環中進行事務提交，下面演示一個他們經常寫的一個存儲過程示例，如下圖：

DROP PROCEDURE IF EXISTS load1;
CREATE PROCEDURE load1(count INT UNSIGNED)BEGIN   
DECLARE s INT UNSIGNED DEFAULT 1;   
DECLARE c CHAR(80) DEFAULT REPEAT('a',80);   
WHILE s <= count DO      
INSERT INTO t1 select NULL,c;      
COMMIT;      
SET s=s+1;   
END WHILE;
END;

在上面的範例中，是否加上commit指令並不是關鍵。由於MySQL innodb的儲存引擎預設為自動提交，因此去掉預存程序中的commit結果是一樣的。如下圖所示，以下也是另一個容易被開發人員忽略的問題：

DROP PROCEDURE IF EXISTS load2;
 CREATE PROCEDURE load2(count INT UNSIGNED)BEGIN  
  DECLARE s INT UNSIGNED DEFAULT 1;   
  DECLARE c CHAR(80) DEFAULT REPEAT('a',80);   
  WHILE s <= count DO      
  INSERT INTO t1 select NULL,c;      
  SET s=s+1;   
  END WHILE;
  END;

不論上面哪個預存過程，當發生錯誤時，資料庫會停留在一個未知的位置。例如我們要插入10000條數據，但是在插入5000條時發生了錯誤，然而這5000條已經存放在了數據庫中，我們如何處理？另外一個是效能問題，上面的兩個預存程序都不會比下面的這個預存程序快，因為下面這個是將insert放在了一個事務中：

DROP PROCEDURE IF EXISTS load3; 
CREATE PROCEDURE load3(count INT UNSIGNED)BEGIN   
DECLARE s INT UNSIGNED DEFAULT 1;   
DECLARE c CHAR(80) DEFAULT REPEAT('a',80);   
START TRANSACTION;   
WHILE s <= count DO      
INSERT INTO t1 select NULL,c;      
SET s=s+1;   
END WHILE;   
COMMIT;
END;

對於上面三個預存過程，我們分別插入100萬數據來比較執行時間，如下所示，顯然可以看到第三種方法快很多，這是因為每次提及都要寫一次重做日誌，所以load1和load2實際寫了100萬次重做紀錄.對於預存程序load3,我們只寫了1次重做日誌。

先準備一個測試表

CREATE TABLE `t1` (`id` int NOT NULL AUTO_INCREMENT ,`name` varchar(500) NULL ,PRIMARY KEY (`id`)) ;

執行測試

09:50:44 test> call load1(1000000);
Query OK, 0 rows affected (1 min 4.90 sec)09:54:23 test> truncate table t1;
Query OK, 0 rows affected (0.05 sec)09:54:25 test> call load2(1000000);
Query OK, 1 row affected (1 min 3.38 sec)09:55:32 test> truncate table t1;
Query OK, 0 rows affected (0.20 sec)09:55:58 test> call load3(1000000);
Query OK, 0 rows affected (33.90 sec)

對於第二個預存程序load2,我們也可以人為的開啟下事務，同樣可以達到存儲過程load3的效果，執行時間如下所示：

09:57:42 test> begin;
Query OK, 0 rows affected (0.00 sec)09:57:46 test> call load2(1000000);
Query OK, 1 row affected (34.08 sec)09:58:26 test> commit;
Query OK, 0 rows affected (0.76 sec)

2.關於使用自動提交

在一些特殊場景下，有時候自動提交不一定是個好的事情， 如我們上面講到的循環提交的問題，MySQL資料庫預設是自動提交（autocommit)。可以透過以下方式來改變MySQL的提交方式：

10:35:34 test> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

也可以使用START TRANSATION或BEGIN顯示的開啟一個事務。 MySQL會自動執行

SET AUTOCOMMIT=0，並在COMMIT或ROLLBACK結束一個事務後執行SET AUTOCOMMIT=1 。

3.使用自動回滾處理異常

當儲存過程發生異常的時候怎麼辦，Innodb儲存引擎支援透過一個HANDLER來進行交易的自動回滾操作。如在預存程序中發生錯誤會自動進行回滾操作。如下面一個範例：

CREATE PROCEDURE sp_auto_rollback_demo()
BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
   START TRANSACTION;
   INSERT INTO b select 1;
   INSERT INTO b select 2;
   INSERT INTO b select 1;
   INSERT INTO b select 3;
   COMMIT;

END;

測試表如下

CREATE TABLE `b` (  
 `a` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

執行上面的預存程序，因此會在插入第二個記錄1時發生錯誤，但是因為啟用了自動回滾操作，這個預存程序執行結果如下：

10:09:46 test> call sp_auto_rollback_demo;
Query OK, 0 rows affected (0.01 sec)
10:10:04 test> select * from b;Empty set (0.00 sec)

看起來沒有問題，運作比較正常，但是在執行sp_auto_rollback_demo的時候是執行成功了還是失敗了？對此，我們可以進行如下處理，示例如下：

DROP PROCEDURE IF EXISTS sp_auto_rollback_demo;
CREATE PROCEDURE sp_auto_rollback_demo()BEGIN   
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT -1; 
END;   
START TRANSACTION;   
INSERT INTO b select 1;   
INSERT INTO b select 2;   
INSERT INTO b select 1;   
INSERT INTO b select 3;   
COMMIT;   
SELECT 1;
END;

當發生錯誤時，先回滾然後返回-1,表示運行發生了錯誤。返回1表示運作正常。運行結果如下：

10:16:19 test> call sp_auto_rollback_demo\G*************************** 1. row ***************************-1: -1
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
10:16:35 test> select * from b;
Empty set (0.00 sec)

以上就是MySQL事務程式設計效能與問題分析 [開發必看]的內容，更多相關內容請關注PHP中文網（www.php.cn）！