![MySQL事務(wù)編程性能和問題分析 [開發(fā)必看]-小浪學(xué)習(xí)網(wǎng)](http://m.babyishan.com/wp-content/themes/zibll/img/icon.png){kind=icon}
.jpg)
沒有一句廢話，滿滿的干貨，直接進(jìn)入分析：
1.在循環(huán)中提交的問題
很多開發(fā)人員非常喜歡在循環(huán)中進(jìn)行事務(wù)提交，下面演示一個(gè)他們經(jīng)常寫的一個(gè)存儲(chǔ)過程示例，如下所示：
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?<p>在上面的例子中，是否加上commit命令并不是關(guān)鍵。由于MySQL innodb的存儲(chǔ)引擎默認(rèn)為自動(dòng)提交，因此去掉存儲(chǔ)過程中的commit結(jié)果是一樣的。如下所示，下面也是另一個(gè)容易被開發(fā)人員忽視的問題：</p><pre class="brush:php;toolbar:false">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?<p>不論上面哪個(gè)存儲(chǔ)過程，當(dāng)發(fā)生錯(cuò)誤時(shí)，數(shù)據(jù)庫會(huì)停留在一個(gè)未知的位置。例如我們要插入10000條數(shù)據(jù)，但是在插入5000條時(shí)發(fā)生了錯(cuò)誤，然而這5000條已經(jīng)存放在了數(shù)據(jù)庫中，我們?nèi)绾翁幚恚苛硗庖粋€(gè)是性能問題，上面的兩個(gè)存儲(chǔ)過程都不會(huì)比下面的這個(gè)存儲(chǔ)過程快，因?yàn)橄旅孢@個(gè)是將insert放在了一個(gè)事務(wù)中：</p><pre class="brush:php;toolbar:false">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?<p>對于上面三個(gè)存儲(chǔ)過程，我們分別插入100萬數(shù)據(jù)來比較執(zhí)行時(shí)間，如下所示，顯然可以看到第三種方法要快很多，這是因?yàn)槊看翁峒岸家獙懸淮沃刈鋈罩荆詌oad1和load2實(shí)際寫了100萬次重做日志。對于存儲(chǔ)過程load3,我們只寫了1次重做日志。</p><p>先準(zhǔn)備一個(gè)測試表</p><pre class="brush:php;toolbar:false">CREATE?TABLE?`t1`?(`id`?int?NOT?NULL?AUTO_INCREMENT?,`name`?varchar(500)?NULL?,PRIMARY?KEY?(`id`))?;
執(zhí)行測試
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)
對于第二個(gè)存儲(chǔ)過程load2,我們也可以人為的開啟下事務(wù)，同樣可以達(dá)到存儲(chǔ)過程load3的效果，執(zhí)行時(shí)間如下所示：
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.關(guān)于使用自動(dòng)提交
在一些特殊場景下，有時(shí)候自動(dòng)提交不一定是個(gè)好的事情， 如我們上面講到的循環(huán)提交的問題，MySQL數(shù)據(jù)庫默認(rèn)是自動(dòng)提交（autocommit)。可以通過如下方式來改變MySQL的提交方式：
10:35:34?test>?SET?AUTOCOMMIT=0; Query?OK,?0?rows?affected?(0.00?sec)
也可以使用START TRANSATION或者BEGIN顯示的開啟一個(gè)事務(wù)。MySQL會(huì)自動(dòng)執(zhí)行
SET AUTOCOMMIT=0，并在COMMIT或ROLLBACK結(jié)束一個(gè)事務(wù)后執(zhí)行SET AUTOCOMMIT=1 。
3.使用自動(dòng)回滾處理異常
當(dāng)存儲(chǔ)過程發(fā)生異常的時(shí)候怎么辦，Innodb存儲(chǔ)引擎支持通過一個(gè)HANDLER來進(jìn)行事務(wù)的自動(dòng)回滾操作。如在存儲(chǔ)過程中發(fā)生錯(cuò)誤會(huì)自動(dòng)進(jìn)行回滾操作。如下面一個(gè)示例：
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;
執(zhí)行上面的存儲(chǔ)過程，因此會(huì)在插入第二個(gè)記錄1時(shí)發(fā)生錯(cuò)誤，但是因?yàn)閱⒂昧俗詣?dòng)回滾操作，這個(gè)存儲(chǔ)過程執(zhí)行結(jié)果如下：
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)
看起來沒有問題，運(yùn)行比較正常，但是在執(zhí)行sp_auto_rollback_demo的時(shí)候是執(zhí)行成功了還是失敗了？對此，我們可以進(jìn)行如下處理，示例如下：
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;
當(dāng)發(fā)生錯(cuò)誤時(shí)，先回滾然后返回-1,表示運(yùn)行發(fā)生了錯(cuò)誤。返回1表示運(yùn)行正常。運(yùn)行結(jié)果如下：
10:16:19?test>?call?sp_auto_rollback_demoG***************************?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事務(wù)編程性能和問題分析 [開發(fā)必看]的內(nèi)容，更多相關(guān)內(nèi)容請關(guān)注PHP中文網(wǎng)（www.php.cn）！
.png)
