沒有一句廢話，滿滿的乾貨，直接進入分析：
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中文網（m.sbmmt.com）！
