MySQL在執(zhí)行批量操作的時(shí)候一次插入多少數(shù)據(jù)才合適呢？

一、前言

我們?cè)诓僮鞔笮蛿?shù)據(jù)表或者日志文件的時(shí)候經(jīng)常會(huì)需要寫(xiě)入數(shù)據(jù)到數(shù)據(jù)庫(kù)，那么最合適的方案就是數(shù)據(jù)庫(kù)的批量插入。只是我們?cè)趫?zhí)行批量操作的時(shí)候，一次插入多少數(shù)據(jù)才合適呢？

假如需要插入的數(shù)據(jù)有百萬(wàn)條，那么一次批量插入多少條的時(shí)候，效率會(huì)高一些呢？這里博主和大家一起探討下這個(gè)問(wèn)題，應(yīng)用環(huán)境為批量插入數(shù)據(jù)到臨時(shí)表。

基于 Spring Boot + MyBatis Plus + Vue & Element 實(shí)現(xiàn)的后臺(tái)管理系統(tǒng) + 用戶小程序，支持 RBAC 動(dòng)態(tài)權(quán)限、多租戶、數(shù)據(jù)權(quán)限、工作流、三方登錄、支付、短信、商城等功能

二、批量插入前準(zhǔn)備

博主本地原本是循環(huán)查出來(lái)的數(shù)據(jù)，然后每1000條插入一次，直至完成插入操作。但是為什么要設(shè)置1000條呢，實(shí)不相瞞，這是因?yàn)轫?xiàng)目里的其他批量插入都是一次插1000條。。汗，博主不服，所以想要測(cè)試下。

首先是查看當(dāng)前數(shù)據(jù)庫(kù)的版本，畢竟各個(gè)版本之間存在差異，脫離版本講數(shù)據(jù)庫(kù)就是耍流氓（以前沒(méi)少耍?。?/p>

mysql>selectversion();
+------------+
|version()|
+------------+
|5.6.34-log|
+------------+
1rowinset(0.00sec)

1、插入到數(shù)據(jù)表的字段

對(duì)于手動(dòng)創(chuàng)建的臨時(shí)表來(lái)說(shuō)，字段當(dāng)然是越少越好，而且字段占用的空間要盡量小一些，這樣臨時(shí)表不至于太大，影響表操作的性能。這里需要插入的字段是：

字段1int(10)
字段2int(10)
字段3int(10)
字段4varchar(10)

我們一共插入四個(gè)字段，分別是3個(gè)int類型的，一個(gè)varchar類型的，整體來(lái)說(shuō)這些字段都比較小，占用的內(nèi)存空間會(huì)小一些。

2、計(jì)算一行字段占用的空間

對(duì)于innodb引擎來(lái)說(shuō)，int類型可以存儲(chǔ)4個(gè)字節(jié)，里面的Int(M)并不會(huì)影響存儲(chǔ)字節(jié)的大小，這個(gè)M只是數(shù)據(jù)的展示位數(shù)，和mysql的ZEROFILL屬性有關(guān)，即在數(shù)字長(zhǎng)度不夠的數(shù)據(jù)前面填充0，以達(dá)到設(shè)定的長(zhǎng)度。此處不多說(shuō)，想要了解的朋友可以百度一下，還是很有意思的。

varchar（10）代表可以存儲(chǔ)10個(gè)字符，不管是英文還是中文，最多都是10個(gè)，這部分假設(shè)存儲(chǔ)的是中文，在utf-8mb4下，10個(gè)中文占用10*4 = 40個(gè)字節(jié)那么一行數(shù)據(jù)最多占用：4+4+4+40 = 52字節(jié)

3、在數(shù)據(jù)里做插入操作的時(shí)候，整體時(shí)間的分配

鏈接耗時(shí)（30%）
發(fā)送query到服務(wù)器（20%）
解析query（20%）
插入操作（10%*詞條數(shù)目）
插入index（10%*Index的數(shù)目）
關(guān)閉鏈接（10%）

從這里可以看出來(lái)，真正耗時(shí)的不是操作，而是鏈接，解析的過(guò)程。單條sql的話，會(huì)在鏈接，解析部分耗費(fèi)大量的時(shí)間，因此速度會(huì)很慢，所以我們一般都是采用批量插入的操作，爭(zhēng)取在一次鏈接里面寫(xiě)入盡可能多的數(shù)據(jù)，以此來(lái)提升插入的速度。但是這個(gè)盡可能多的數(shù)據(jù)是多少呢？一次到底插入多少才合適呢？

基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 實(shí)現(xiàn)的后臺(tái)管理系統(tǒng) + 用戶小程序，支持 RBAC 動(dòng)態(tài)權(quán)限、多租戶、數(shù)據(jù)權(quán)限、工作流、三方登錄、支付、短信、商城等功能

三、批量插入數(shù)據(jù)測(cè)試

開(kāi)始測(cè)試，但是一開(kāi)始插入多少是合適的呢，是否有上限？查詢mysql手冊(cè)，我們知道sql語(yǔ)句是有大小限制的。

1、SQL語(yǔ)句的大小限制

my.ini 里有 max_allowed_packet 這個(gè)參數(shù)控制通信的 packet 大小。mysql默認(rèn)的sql語(yǔ)句的最大限制是1M（mysql5.7的客戶端默認(rèn)是16M，服務(wù)端默認(rèn)是4M），可以根據(jù)設(shè)置查看。官方解釋是適當(dāng)增大 max_allowed_packet 參數(shù)可以使client端到server端傳遞大數(shù)據(jù)時(shí)，系統(tǒng)能夠分配更多的擴(kuò)展內(nèi)存來(lái)處理。

2、查看服務(wù)器上的參數(shù)：

mysql>showvariableslike'%max_allowed_packet%';
+--------------------------+------------+
|Variable_name|Value|
+--------------------------+------------+
|max_allowed_packet|33554432|
|slave_max_allowed_packet|1073741824|
+--------------------------+------------+
2rowsinset(0.00sec)

33554432字節(jié) = 32M ，也就是規(guī)定大小不能超過(guò)32M。

3、計(jì)算一次能插入的最大行記錄

1M計(jì)算的話，(1024*1024)/52 ≈ 20165 ，為了防止溢出，最大可一次性插入20000條（根據(jù)自己的配置和sql語(yǔ)句大小計(jì)算）。那么32M的話就是：20000 *32 = 640000 也就是64W條。

4、測(cè)試插入數(shù)據(jù)比對(duì)

（1）插入11W條數(shù)據(jù)，按照每次10,600,1000,20000,80000來(lái)測(cè)試：

+---------------+
|count(c1.uin)|
+---------------+
|110000|
+---------------+

有個(gè)博客說(shuō)一次插入10條最快，，我覺(jué)得一次插的有點(diǎn)少，咱們?cè)囋?/p>

這個(gè)博主測(cè)試后，認(rèn)為一次插10條是性能最快的，他的每條記錄是3kb，相當(dāng)于我的59行數(shù)據(jù)，取個(gè)整數(shù)60，那么對(duì)于這個(gè)博主是插入10條，對(duì)我來(lái)說(shuō)插入：600，這幾個(gè)值都試試。

耗時(shí)：

11W的數(shù)據(jù)，每次插入10條。耗時(shí)：2.361s
11W的數(shù)據(jù)，每次插入600條。耗時(shí)：0.523s
11W的數(shù)據(jù)，每次插入1000條。耗時(shí)：0.429s
11W的數(shù)據(jù)，每次插入20000條。耗時(shí)：0.426s
11W的數(shù)據(jù)，每次插入80000條。耗時(shí)：0.352s

從這部分看，隨著批量插入的增加，速度略有提升，最起碼一次插10條應(yīng)該不是最佳的。插入數(shù)據(jù)量多，減少了循環(huán)的次數(shù)，也就是在數(shù)據(jù)庫(kù)鏈接部分的耗時(shí)有所減少，只是這個(gè)8W并不是極限數(shù)據(jù)，具體一次插入多少條，還有待參考。

（2）加大數(shù)據(jù)量到24w

+---------------+
|count(c1.uin)|
+---------------+
|241397|
+---------------+

耗時(shí)：

24W的數(shù)據(jù)，每次插入10條。耗時(shí)：4.445s
24W的數(shù)據(jù)，每次插入600條。耗時(shí)：1.187s
24W的數(shù)據(jù)，每次插入1000條。耗時(shí)：1.13s
24W的數(shù)據(jù)，每次插入20000條。耗時(shí)：0.933s
24W的數(shù)據(jù)，每次插入80000條。耗時(shí)：0.753s

一次插入24W反而性能最佳，這么代表我們的測(cè)試數(shù)據(jù)量依然不夠。

（3）加大測(cè)試量到42W

+---------------+
|count(c1.uin)|
+---------------+
|418859|

耗時(shí)：

42W的數(shù)據(jù)，每次插入1000條。耗時(shí)：2.216s
42W的數(shù)據(jù)，每次插入80000條。耗時(shí)：1.777s
42W的數(shù)據(jù)，每次插入16W條。耗時(shí)：1.523s
42W的數(shù)據(jù)，每次插入20W條。耗時(shí)：1.432s
42W的數(shù)據(jù)，每次插入30W條。耗時(shí)：1.362s
42W的數(shù)據(jù)，每次插入40W條。耗時(shí)：1.764s

隨著插入量的增加，批量插入條數(shù)多了之后，性能是有所提升的。但是在達(dá)到30W以上之后，效率反而有所下降。這部分我的理解是mysql是要分配一定的內(nèi)存給傳過(guò)來(lái)的數(shù)據(jù)包使用，當(dāng)批量插入的數(shù)據(jù)量到達(dá)一定程度之后，一次插入操作的開(kāi)銷就很耗費(fèi)內(nèi)存了。

個(gè)人感覺(jué)，最佳大小是max_allowed_packet的一半，也就是極限能插入64W，選用32W也許性能會(huì)更好一些，同時(shí)也不會(huì)對(duì)mysql的其他操作產(chǎn)生太大的影響。

5、如果插入的值就是sql語(yǔ)句限制的最大值，那么性能真的好嗎？

博主瘋狂谷歌百度，都沒(méi)有找到有人來(lái)具體的說(shuō)一下這個(gè)問(wèn)題，不過(guò)在高性能mysql里面發(fā)現(xiàn)一句話：

客戶端用一個(gè)單獨(dú)的數(shù)據(jù)包將查詢請(qǐng)求發(fā)送給服務(wù)器，所以當(dāng)查詢語(yǔ)句很長(zhǎng)的時(shí)候，需要設(shè)置max_allowed_packet參數(shù)。但是需要注意的是，如果查詢實(shí)在是太大，服務(wù)端會(huì)拒絕接收更多數(shù)據(jù)并拋出異常。與之相反的是，服務(wù)器響應(yīng)給用戶的數(shù)據(jù)通常會(huì)很多，由多個(gè)數(shù)據(jù)包組成。

但是當(dāng)服務(wù)器響應(yīng)客戶端請(qǐng)求時(shí)，客戶端必須完整的接收整個(gè)返回結(jié)果，而不能簡(jiǎn)單的只取前面幾條結(jié)果，然后讓服務(wù)器停止發(fā)送。因而在實(shí)際開(kāi)發(fā)中，盡量保持查詢簡(jiǎn)單且只返回必需的數(shù)據(jù)，減小通信間數(shù)據(jù)包的大小和數(shù)量是一個(gè)非常好的習(xí)慣，這也是查詢中盡量避免使用SELECT *以及加上LIMIT限制的原因之一。

后面通過(guò)各種百度，博主覺(jué)得最大只是代表傳輸數(shù)據(jù)包的最大長(zhǎng)度，但性能是不是最佳就要從各個(gè)方面來(lái)分析了。比如下面列出的插入緩沖，以及插入索引時(shí)對(duì)于緩沖區(qū)的剩余空間需求，以及事務(wù)占有的內(nèi)存等，都會(huì)影響批量插入的性能。

四、其他影響插入性能的因素

1、首先是插入的時(shí)候，要注意緩沖區(qū)的大小使用情況

在分析源碼的過(guò)程中，有一句話：如果buffer pool余量不足25%，插入失敗，返回DB_LOCK_TABLE_FULL。這個(gè)錯(cuò)誤并不是直接報(bào)錯(cuò)：max_allowed_packet 不夠大之類的，這個(gè)錯(cuò)誤是因?yàn)閷?duì)于innodb引擎來(lái)說(shuō)，一次插入是涉及到事務(wù)和鎖的，在插入索引的時(shí)候，要判斷緩沖區(qū)的剩余情況，所以插入并不能僅僅只考慮max_allowed_packet的問(wèn)題，也要考慮到緩沖區(qū)的大小。

2、插入緩存

另外對(duì)于innodb引擎來(lái)說(shuō)，因?yàn)榇嬖诓迦刖彺妫↖nsert Buffer）這個(gè)概念，所以在插入的時(shí)候也是要耗費(fèi)一定的緩沖池內(nèi)存的。當(dāng)寫(xiě)密集的情況下，插入緩沖會(huì)占用過(guò)多的緩沖池內(nèi)存，默認(rèn)最大可以占用到1/2的緩沖池內(nèi)存，當(dāng)插入緩沖占用太多緩沖池內(nèi)存的情況下，會(huì)影響到其他的操作。

也就是說(shuō)，插入緩沖受到緩沖池大小的影響，緩沖池大小為：

mysql>showvariableslike'innodb_buffer_pool_size';
+-------------------------+-----------+
|Variable_name|Value|
+-------------------------+-----------+
|innodb_buffer_pool_size|134217728|
+-------------------------+-----------+

換算后的結(jié)果為：128M，也就是說(shuō)，插入緩存最多可以占用64M的緩沖區(qū)大小。這個(gè)大小要超過(guò)咱們?cè)O(shè)置的sql語(yǔ)句大小，所以可以忽略不計(jì)。

詳細(xì)解釋：

我們都知道，在InnoDB引擎上進(jìn)行插入操作時(shí)，一般需要按照主鍵順序進(jìn)行插入，這樣才能獲得較高的插入性能。當(dāng)一張表中存在非聚簇的且不唯一的索引時(shí)，在插入時(shí)，數(shù)據(jù)頁(yè)的存放還是按照主鍵進(jìn)行順序存放，但是對(duì)于非聚簇索引葉節(jié)點(diǎn)的插入不再是順序的了，這時(shí)就需要離散的訪問(wèn)非聚簇索引頁(yè)，由于隨機(jī)讀取的存在導(dǎo)致插入操作性能下降。

InnoDB為此設(shè)計(jì)了Insert Buffer來(lái)進(jìn)行插入優(yōu)化。對(duì)于非聚簇索引的插入或者更新操作，不是每一次都直接插入到索引頁(yè)中，而是先判斷插入的非聚集索引是否在緩沖池中，若在，則直接插入；若不在，則先放入到一個(gè)Insert Buffer中。

看似數(shù)據(jù)庫(kù)這個(gè)非聚集的索引已經(jīng)查到葉節(jié)點(diǎn)，而實(shí)際沒(méi)有，這時(shí)存放在另外一個(gè)位置。然后再以一定的頻率和情況進(jìn)行Insert Buffer和非聚簇索引頁(yè)子節(jié)點(diǎn)的合并操作。這時(shí)通常能夠?qū)⒍鄠€(gè)插入合并到一個(gè)操作中，這樣就大大提高了對(duì)于非聚簇索引的插入性能。

3、使用事務(wù)提升效率

還有一種說(shuō)法，使用事務(wù)可以提高數(shù)據(jù)的插入效率，這是因?yàn)檫M(jìn)行一個(gè)INSERT操作時(shí)，MySQL內(nèi)部會(huì)建立一個(gè)事務(wù)，在事務(wù)內(nèi)才進(jìn)行真正插入處理操作。通過(guò)使用事務(wù)可以減少創(chuàng)建事務(wù)的消耗，所有插入都在執(zhí)行后才進(jìn)行提交操作。大概如下：

STARTTRANSACTION;
INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)
VALUES('0','userid_0','content_0',0);
INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)
VALUES('1','userid_1','content_1',1);
...
COMMIT;

參考：https://my.oschina.net/songhongxu/blog/163063

事務(wù)需要控制大小，事務(wù)太大可能會(huì)影響執(zhí)行的效率。MySQL有innodb_log_buffer_size配置項(xiàng)，超過(guò)這個(gè)值會(huì)把innodb的數(shù)據(jù)刷到磁盤(pán)中，這時(shí)，效率會(huì)有所下降。所以比較好的做法是，在數(shù)據(jù)達(dá)到這個(gè)這個(gè)值前進(jìn)行事務(wù)提交。

查看：show variables like '%innodb_log_buffer_size%';

+------------------------+----------+
|Variable_name|Value|
+------------------------+----------+
|innodb_log_buffer_size|67108864|
+------------------------+----------+

大概是：64M

這種寫(xiě)法和批量寫(xiě)入的效果差不多，只不過(guò)sql語(yǔ)句還是單句的，然后統(tǒng)一提交。一個(gè)瓶頸是SQL語(yǔ)句的大小，一個(gè)瓶頸是事務(wù)的大小。當(dāng)我們?cè)谔峤籹ql的時(shí)候，首先是受到sql大小的限制，其次是受到事務(wù)大小的限制。在開(kāi)啟事務(wù)的情況下使用批量插入，會(huì)節(jié)省不少事務(wù)的開(kāi)銷，如果要追求極致的速度的話，建議是開(kāi)著事務(wù)插入的。

不過(guò)需要注意一下，內(nèi)存是有限且共享的，如果批量插入占用太多的事務(wù)內(nèi)存，那么勢(shì)必會(huì)對(duì)其他的業(yè)務(wù)操作等有一定的影響。

4、通過(guò)配置提升讀寫(xiě)性能

也可以通過(guò)增大innodb_buffer_pool_size 緩沖區(qū)來(lái)提升讀寫(xiě)性能，只是緩沖區(qū)是要占用內(nèi)存空間的，內(nèi)存很珍貴，所以這個(gè)方案在內(nèi)存富裕，而性能瓶頸的時(shí)候，可以考慮下。

5、索引影響插入性能

如果表中存在多個(gè)字段索引，當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)。這樣就降低了數(shù)據(jù)的插入速度。對(duì)于普通的數(shù)據(jù)表，主鍵索引是肯定要有的，想要加快性能的話，就是要有序插入，每次插入記錄都在索引的最后面，索引的定位效率很高，并且對(duì)索引調(diào)整較小。如果插入的記錄在索引中間，需要B+tree進(jìn)行分裂合并等處理，會(huì)消耗比較多計(jì)算資源，并且插入記錄的索引定位效率會(huì)下降，數(shù)據(jù)量較大時(shí)會(huì)有頻繁的磁盤(pán)操作。

五、總結(jié)

博主經(jīng)過(guò)測(cè)試+谷歌，最終是選用的一次批量插入數(shù)據(jù)量為max_allowed_packet大小的一半。只是在不斷的搜索中，發(fā)現(xiàn)影響插入性能的地方挺多的，如果僅僅是拿max_allowed_packet這個(gè)參數(shù)作為分析，其實(shí)是沒(méi)有意義的，這個(gè)參數(shù)只是設(shè)置最大值，但并不是最佳性能。

不過(guò)需要注意，由于sql語(yǔ)句比較大，所以才執(zhí)行完插入操作之后，一定要釋放變量，不要造成無(wú)謂的內(nèi)存損耗，影響程序性能。

對(duì)于我們的mysql來(lái)說(shuō)也是一樣的，mysql的最佳性能是建立在各個(gè)參數(shù)的合理設(shè)置上，這樣協(xié)同干活兒的效果最佳。如果其他設(shè)置不到位的話，就像是木桶原理一樣，哪怕內(nèi)存緩沖區(qū)設(shè)置的很大，但是性能取決的反而是設(shè)置最差的那個(gè)配置。

審核編輯：劉清