背景

有人問mysqldump出來的insert語句，是否可以按每 10 row 一條insert語句的形式組織。

思考1：引數--extended-insert回憶過去所學：

我只知道有一對引數

--extended-insert(預設值)

表示使用長 INSERT ，多 row 在合併一起批次 INSERT，提高匯入效率

--skip-extended-insert一行一個的短INSERT

均不滿足群友需求，無法控制按每 10 row 一條 insert 語句的形式組織。

思考2：“避免大事務”之前一直沒有考慮過這個問題。這個問題的提出，相信主要是為了“避免大事務”。所以滿足 insert 均為小事務即可。下面，我們來探討一下以下問題：1. 什麼是大事務？

2. 那麼 mysqldump 出來的 insert 語句可能是大事務嗎？

什麼是大事務？

定義：執行時間比較長，操作的資料比較多的事務我們稱之為大事務。

大事務風險：

∘ 鎖定太多的資料，造成大量的阻塞和鎖超時，回滾所需要的時間比較長。

∘ 執行時間長，容易造成主從延遲。

∘ undo log膨脹

避免大事務：我這裡按公司實際場景，規定了，每次操作/獲取資料量應該少於5000條，結果集應該小於2Mmysqldump出來的SQL檔案有大事務嗎？

前提，MySQL 預設是自提交的，所以如果沒有明確地開啟事務，一條 SQL 語句就是一條事務。在 mysqldump 裡，就是一條 SQL 語句為一條事務。

按照我的“避免大事務”自定義規定，答案是沒有的。原來，mysqldump 會按照引數--net-buffer-length，來自動切分 SQL 語句。預設值是 1M。按照我們前面定義的標準，沒有達到我們的 2M 的大事務標準。--net-buffer-length 最大可設定為 16777216，人手設定大於這個值，會自動調整為 16777216，即 16M。設定 16M，可以提升匯出匯入效能。如果為了避免大事務，那就不建議調整這個引數，使用預設值即可。[root@192-168-199-198 ~]# mysqldump --net-buffer-length=104652800 -uroot -proot -P3306 -h192.168.199.198 test t >16M.sqlmysqldump: [Warning] option "net_buffer_length": unsigned value 104652800 adjusted to 16777216#設定大於16M，引數被自動調整為16M

注意，指的是 mysqldump 的引數，而不是 mysqld 的引數。官方文件提到: If you increase this variable, ensure that the MySQL server net_buffer_length system variable has a value at least this large.

意思是 mysqldump 增大這個值，mysqld 也得增大這個值，測試結論是不需要的。懷疑官方文件有誤。

不過，在匯入的時候，受到伺服器引數 max_allowed_packet 影響，它控制了伺服器能接受的資料包的最大大小，預設值是 4194304，即 4M。所以匯入資料庫時需要調整引數 max_allowed_packet 的值。set global max_allowed_packet=16*1024*1024*1024;不調整的話，會出現以下報錯：[root@192-168-199-198 ~]# mysql -uroot -proot -P3306 -h192.168.199.198 test <16M.sqlmysql: [Warning] Using a password on the command line interface can be insecure.ERROR 2006 (HY000) at line 46: MySQL server has gone away相關測試最後，我放出我的相關測試步驟mysql> select version();+------------+| version() |+------------+| 5.7.26-log |+------------+1 row in set (0.00 sec)造100萬行資料

create database test;

use test;

CREATE TABLE `t` (

`a` int(11) DEFAULT NULL,

`b` int(11) DEFAULT NULL,

`c` varchar(255) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into t values (1,1,"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyztuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz");

insert into t select * from t; #重複執行20次

# 直到出現Records: 524288 Duplicates: 0 Warnings: 0

# 說明資料量達到100多萬條了。

mysql> select count(*) from t;

+----------+

| count(*) |

+----------+

| 1048576 |

+----------+

1 row in set (1.04 sec)

資料大小如下，有 284MB[root@192-168-199-198 test]# pwd/data/mysql/mysql3306/data/test[root@192-168-199-198 test]# du -sh t.ibd284M t.ibd--net-buffer-length=1M[root@192-168-199-198 ~]# mysqldump -uroot -proot -S /tmp/mysql3306.sock test t >1M.sql[root@192-168-199-198 ~]# du -sh 1M.sql225M 1M.sql[root@192-168-199-198 ~]# cat 1M.sql |grep -i insert |wc -l226預設 --net-buffer-length=1M 的情況下，225M 的SQL檔案裡有 226 條 insert ，平均下來確實就是每條 insert 的 SQL 大小為 1M。

--net-buffer-length=16M

[root@192-168-199-198 ~]# mysqldump --net-buffer-length=16M -uroot -proot -S /tmp/mysql3306.sock test t >16M.sql[root@192-168-199-198 ~]# du -sh 16M.sql225M 16M.sql[root@192-168-199-198 ~]# cat 16M.sql |grep -i insert |wc -l15預設--net-buffer-length=16M 的情況下，225M 的 SQL 檔案裡有 15 條 insert，平均下來確實就是每條 insert 的 SQL 大小為 16M。所以，這裡證明了 --net-buffer-length 確實可用於拆分 mysqldump 備份檔案的SQL大小的。效能測試insert 次數越多，互動次數就越多，效能越低。 但鑑於上面例子的 insert 數量差距不大，只有 16 倍，效能差距不會很大(實際測試也是如此)。我們直接對比 --net-buffer-length=16K 和 --net-buffer-length=16M 的情況，他們insert次數相差了 1024 倍。

[root@192-168-199-198 ~]# time mysql -uroot -proot -S /tmp/mysql3306.sock test <16K.sql

mysql: [Warning] Using a password on the command line interface can be insecure.

real 0m10.911s #11秒

user 0m1.273s

sys 0m0.677s

[root@192-168-199-198 ~]# mysql -uroot -proot -S /tmp/mysql3306.sock -e "reset master";

mysql: [Warning] Using a password on the command line interface can be insecure.

[root@192-168-199-198 ~]# time mysql -uroot -proot -S /tmp/mysql3306.sock test <16M.sql

mysql: [Warning] Using a password on the command line interface can be insecure.

real 0m8.083s #8秒

user 0m1.669s

sys 0m0.066s

結果明顯。--net-buffer-length 設定越大，客戶端與資料庫互動次數越少，匯入越快。結論mysqldump 預設設定下匯出的備份檔案，符合匯入需求，不會造成大事務。效能方面也符合要求，不需要調整引數。