Mysql

autocommit=off 如何影響使用 innodb 的 mysql 中的批量插入性能?

  • October 24, 2021

我知道關閉自動送出可以根據以下內容大大提高批量插入性能:

使用AUTOCOMMIT = 0更好嗎

我已經做了實驗來證實這個結論。但我想知道的是,為什麼關閉自動送出可以改善批量插入?送出事務時,inno db 內部會發生什麼?

有 3 種方式進行交易。 以下所有3 項都涉及交易。

SET autocommit = ON;

INSERT ... VALUES (1,2), (2,3), ...;  -- This is one transaction
another INSERT/UPDATE/etc;  -- This is another transaction

或者…

SET autocommit = OFF;

INSERT ... VALUES (1,2), (2,3), ...;
another INSERT/UPDATE/etc;  
COMMIT;     -- Terminate the ONE transaction (with 2 statements)

或者

BEGIN;   -- aka START TRANSACTION;
INSERT ... VALUES (1,2), (2,3), ...;
another INSERT/UPDATE/etc;
COMMIT;     -- Terminate the ONE transaction (with 2 statements)

如果您正在做一個 bulk INSERT,您可能希望它本身就是一個事務,因為您已經將許多 1 行插入組合成一個“批量”插入。

速度:

  • 事務有一些成本。
  • 語句有一些成本。

所以:

  • 最快:批量INSERT(一筆交易中的一條語句)
  • 中:和之間的一堆單行INSERT語句BEGIN``COMMIT
  • 最慢: 1-row INSERTs,每個都是單獨的事務。

提示: 請勿使用autocommit=0,因為您可能會忘記發出COMMIT.

如果關閉了自動送出,您可以隨時執行 aROLLBACK以恢復數據庫在您開始執行某項操作之前所處的狀態。 COMMIT確保對數據庫的所有更改都被永久寫入,每次執行此INSERT操作都比多次執行此操作花費更多時間*,* INSERT因為不僅需要插入記錄,還需要更新索引。對大量記錄執行最後一項比一次對 1 條記錄執行此操作更有效。

引用自:https://dba.stackexchange.com/questions/252445