MySQL 批量操作,一次插入多少行數(shù)據(jù)效率最高?
點擊關(guān)注公眾號，Java干貨
及時送達
推薦閱讀：
學(xué)習(xí)?Spring Cloud 微服務(wù)的最佳姿勢！
一、前言
我們在操作大型數(shù)據(jù)表或者日志文件的時候經(jīng)常會需要寫入數(shù)據(jù)到數(shù)據(jù)庫，那么最合適的方案就是數(shù)據(jù)庫的批量插入。只是我們在執(zhí)行批量操作的時候，一次插入多少數(shù)據(jù)才合適呢？假如需要插入的數(shù)據(jù)有百萬條，那么一次批量插入多少條的時候，效率會高一些呢？這里博主和大家一起探討下這個問題，應(yīng)用環(huán)境為批量插入數(shù)據(jù)到臨時表。二、批量插入前準備
博主本地原本是循環(huán)查出來的數(shù)據(jù)，然后每1000條插入一次，直至完成插入操作。但是為什么要設(shè)置1000條呢，實不相瞞，這是因為項目里的其他批量插入都是一次插1000條。。汗，博主不服，所以想要測試下。 首先是查看當(dāng)前數(shù)據(jù)庫的版本，畢竟各個版本之間存在差異，脫離版本講數(shù)據(jù)庫就是耍流氓（以前沒少耍?。?/span>
mysql>?select?version();
+------------+
|?version()??|
+------------+
|?5.6.34-log?|
+------------+
1?row?in?set?(0.00?sec)
1、插入到數(shù)據(jù)表的字段
對于手動創(chuàng)建的臨時表來說，字段當(dāng)然是越少越好，而且字段占用的空間要盡量小一些，這樣臨時表不至于太大，影響表操作的性能。這里需要插入的字段是：
字段1?int(10)
字段2?int(10)
字段3?int(10)
字段4?varchar(10)
我們一共插入四個字段，分別是3個int類型的，一個varchar類型的，整體來說這些字段都比較小，占用的內(nèi)存空間會小一些。
2、計算一行字段占用的空間
對于innodb引擎來說，int類型可以存儲4個字節(jié)，里面的
Int(M)
并不會影響存儲字節(jié)的大小，這個M只是數(shù)據(jù)的展示位數(shù)，和mysql的ZEROFILL屬性有關(guān)，即在數(shù)字長度不夠的數(shù)據(jù)前面填充0，以達到設(shè)定的長度。此處不多說，想要了解的朋友可以百度一下，還是很有意思的。
varchar（10）代表可以存儲10個字符，不管是英文還是中文，最多都是10個，這部分假設(shè)存儲的是中文，在utf-8mb4下，10個中文占用10*4 = 40個字節(jié)那么一行數(shù)據(jù)最多占用：4+4+4+40 = 52字節(jié)3、在數(shù)據(jù)里做插入操作的時候，整體時間的分配
鏈接耗時?（30%）
發(fā)送query到服務(wù)器?（20%）
解析query?（20%）
插入操作?（10%?*?詞條數(shù)目）
插入index?（10%?*?Index的數(shù)目）
關(guān)閉鏈接?（10%）
從這里可以看出來，真正耗時的不是操作，而是鏈接，解析的過程。單條sql的話，會在鏈接，解析部分耗費大量的時間，因此速度會很慢，所以我們一般都是采用批量插入的操作，爭取在一次鏈接里面寫入盡可能多的數(shù)據(jù)，以此來提升插入的速度。但是這個盡可能多的數(shù)據(jù)是多少呢？一次到底插入多少才合適呢？三、批量插入數(shù)據(jù)測試
開始測試，但是一開始插入多少是合適的呢，是否有上限？查詢mysql手冊，我們知道sql語句是有大小限制的。最新 MySQL?面試題整理好了，大家可以在Java面試庫小程序在線刷題。1、SQL語句的大小限制
my.ini 里有 max_allowed_packet 這個參數(shù)控制通信的 packet 大小。mysql默認的sql語句的最大限制是1M（mysql5.7的客戶端默認是16M，服務(wù)端默認是4M），可以根據(jù)設(shè)置查看。官方解釋是適當(dāng)增大 max_allowed_packet 參數(shù)可以使client端到server端傳遞大數(shù)據(jù)時，系統(tǒng)能夠分配更多的擴展內(nèi)存來處理。官方手冊：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
2、查看服務(wù)器上的參數(shù)：
mysql>?show?variables?like?'%max_allowed_packet%';
+--------------------------+------------+
|?Variable_name????????????|?Value??????|
+--------------------------+------------+
|?max_allowed_packet???????|?33554432???|
|?slave_max_allowed_packet?|?1073741824?|
+--------------------------+------------+
2?rows?in?set?(0.00?sec)
33554432字節(jié) = 32M ，也就是規(guī)定大小不能超過32M。3、計算一次能插入的最大行記錄
1M計算的話，(1024*1024)/52 ≈ 20165 ，為了防止溢出，最大可一次性插入20000條（根據(jù)自己的配置和sql語句大小計算）。那么32M的話就是：20000 *32 = 640000 也就是64W條。4、測試插入數(shù)據(jù)比對
（1）插入11W條數(shù)據(jù)，按照每次10,600,1000,20000,80000來測試：
+---------------+
|?count(c1.uin)?|
+---------------+
|?????????110000?|
+---------------+
有個博客說一次插入10條最快，，我覺得一次插的有點少，咱們試試這個博主測試后，認為一次插10條是性能最快的，他的每條記錄是3kb，相當(dāng)于我的59行數(shù)據(jù)，取個整數(shù)60，那么對于這個博主是插入10條，對我來說插入：600，這幾個值都試試。耗時：
11W的數(shù)據(jù)，每次插入10條。耗時：2.361s
11W的數(shù)據(jù)，每次插入600條。耗時：0.523s
11W的數(shù)據(jù)，每次插入1000條。耗時：0.429s
11W的數(shù)據(jù)，每次插入20000條。耗時：0.426s
11W的數(shù)據(jù)，每次插入80000條。耗時：0.352s
從這部分看，隨著批量插入的增加，速度略有提升，最起碼一次插10條應(yīng)該不是最佳的。插入數(shù)據(jù)量多，減少了循環(huán)的次數(shù)，也就是在數(shù)據(jù)庫鏈接部分的耗時有所減少，只是這個8W并不是極限數(shù)據(jù)，具體一次插入多少條，還有待參考。最新 MySQL?面試題整理好了，大家可以在Java面試庫小程序在線刷題。（2）加大數(shù)據(jù)量到24w
+---------------+
|?count(c1.uin)?|
+---------------+
|????????241397?|
+---------------+
耗時：
24W的數(shù)據(jù)，每次插入10條。耗時：4.445s
24W的數(shù)據(jù)，每次插入600條。耗時：1.187s
24W的數(shù)據(jù)，每次插入1000條。耗時：1.13s
24W的數(shù)據(jù)，每次插入20000條。耗時：0.933s
24W的數(shù)據(jù)，每次插入80000條。耗時：0.753s
一次插入24W反而性能最佳，這么代表我們的測試數(shù)據(jù)量依然不夠。（3）加大測試量到42W
+---------------+
|?count(c1.uin)?|
+---------------+
|????????418859?|
耗時：
42W的數(shù)據(jù)，每次插入1000條。耗時：2.216s
42W的數(shù)據(jù)，每次插入80000條。耗時：1.777s
42W的數(shù)據(jù)，每次插入16W條。耗時：1.523s
42W的數(shù)據(jù)，每次插入20W條。耗時：1.432s
42W的數(shù)據(jù)，每次插入30W條。耗時：1.362s
42W的數(shù)據(jù)，每次插入40W條。耗時：1.764s
隨著插入量的增加，批量插入條數(shù)多了之后，性能是有所提升的。但是在達到30W以上之后，效率反而有所下降。這部分我的理解是mysql是要分配一定的內(nèi)存給傳過來的數(shù)據(jù)包使用，當(dāng)批量插入的數(shù)據(jù)量到達一定程度之后，一次插入操作的開銷就很耗費內(nèi)存了。
個人感覺，最佳大小是max_allowed_packet的一半，也就是極限能插入64W，選用32W也許性能會更好一些，同時也不會對mysql的其他操作產(chǎn)生太大的影響。
5、如果插入的值就是sql語句限制的最大值，那么性能真的好嗎？
博主瘋狂谷歌百度，都沒有找到有人來具體的說一下這個問題，不過在高性能mysql里面發(fā)現(xiàn)一句話：客戶端用一個單獨的數(shù)據(jù)包將查詢請求發(fā)送給服務(wù)器，所以當(dāng)查詢語句很長的時候，需要設(shè)置后面通過各種百度，博主覺得最大只是代表傳輸數(shù)據(jù)包的最大長度，但性能是不是最佳就要從各個方面來分析了。比如下面列出的插入緩沖，以及插入索引時對于緩沖區(qū)的剩余空間需求，以及事務(wù)占有的內(nèi)存等，都會影響批量插入的性能。 點擊關(guān)注公眾號，Java干貨 及時送達max_allowed_packet參數(shù)。但是需要注意的是，如果查詢實在是太大，服務(wù)端會拒絕接收更多數(shù)據(jù)并拋出異常。與之相反的是，服務(wù)器響應(yīng)給用戶的數(shù)據(jù)通常會很多，由多個數(shù)據(jù)包組成。但是當(dāng)服務(wù)器響應(yīng)客戶端請求時，客戶端必須完整的接收整個返回結(jié)果，而不能簡單的只取前面幾條結(jié)果，然后讓服務(wù)器停止發(fā)送。因而在實際開發(fā)中，盡量保持查詢簡單且只返回必需的數(shù)據(jù)，減小通信間數(shù)據(jù)包的大小和數(shù)量是一個非常好的習(xí)慣，這也是查詢中盡量避免使用SELECT *以及加上LIMIT限制的原因之一。
四、其他影響插入性能的因素
1、首先是插入的時候，要注意緩沖區(qū)的大小使用情況
在分析源碼的過程中，有一句話：如果buffer pool余量不足25%，插入失敗，返回DB_LOCK_TABLE_FULL。這個錯誤并不是直接報錯：max_allowed_packet 不夠大之類的，這個錯誤是因為對于innodb引擎來說，一次插入是涉及到事務(wù)和鎖的，在插入索引的時候，要判斷緩沖區(qū)的剩余情況，所以插入并不能僅僅只考慮max_allowed_packet的問題，也要考慮到緩沖區(qū)的大小。另外，最新最全的 Java 面試題整理好了，微信搜索Java面試庫小程序在線刷題。2、插入緩存
另外對于innodb引擎來說，因為存在插入緩存（Insert Buffer）這個概念，所以在插入的時候也是要耗費一定的緩沖池內(nèi)存的。當(dāng)寫密集的情況下，插入緩沖會占用過多的緩沖池內(nèi)存，默認最大可以占用到1/2的緩沖池內(nèi)存，當(dāng)插入緩沖占用太多緩沖池內(nèi)存的情況下，會影響到其他的操作。也就是說，插入緩沖受到緩沖池大小的影響，緩沖池大小為：
mysql>?show?variables?like?'innodb_buffer_pool_size';
+-------------------------+-----------+
|?Variable_name???????????|?Value?????|
+-------------------------+-----------+
|?innodb_buffer_pool_size?|?134217728?|
+-------------------------+-----------+
換算后的結(jié)果為：128M，也就是說，插入緩存最多可以占用64M的緩沖區(qū)大小。這個大小要超過咱們設(shè)置的sql語句大小，所以可以忽略不計。詳細解釋：我們都知道，在InnoDB引擎上進行插入操作時，一般需要按照主鍵順序進行插入，這樣才能獲得較高的插入性能。當(dāng)一張表中存在非聚簇的且不唯一的索引時，在插入時，數(shù)據(jù)頁的存放還是按照主鍵進行順序存放，但是對于非聚簇索引葉節(jié)點的插入不再是順序的了，這時就需要離散的訪問非聚簇索引頁，由于隨機讀取的存在導(dǎo)致插入操作性能下降。InnoDB為此設(shè)計了Insert Buffer來進行插入優(yōu)化。對于非聚簇索引的插入或者更新操作，不是每一次都直接插入到索引頁中，而是先判斷插入的非聚集索引是否在緩沖池中，若在，則直接插入；若不在，則先放入到一個
Insert Buffer中。看似數(shù)據(jù)庫這個非聚集的索引已經(jīng)查到葉節(jié)點，而實際沒有，這時存放在另外一個位置。然后再以一定的頻率和情況進行Insert Buffer和非聚簇索引頁子節(jié)點的合并操作。這時通常能夠?qū)⒍鄠€插入合并到一個操作中，這樣就大大提高了對于非聚簇索引的插入性能。最新面試題整理：https://www.javastack.cn/mst/3、使用事務(wù)提升效率
還有一種說法，使用事務(wù)可以提高數(shù)據(jù)的插入效率，這是因為進行一個INSERT操作時，MySQL內(nèi)部會建立一個事務(wù)，在事務(wù)內(nèi)才進行真正插入處理操作。通過使用事務(wù)可以減少創(chuàng)建事務(wù)的消耗，所有插入都在執(zhí)行后才進行提交操作。大概如下：
START?TRANSACTION;
INSERT?INTO?`insert_table`?(`datetime`,?`uid`,?`content`,?`type`)?
????VALUES?('0',?'userid_0',?'content_0',?0);
INSERT?INTO?`insert_table`?(`datetime`,?`uid`,?`content`,?`type`)?
????VALUES?('1',?'userid_1',?'content_1',?1);
...
COMMIT;
事務(wù)需要控制大小，事務(wù)太大可能會影響執(zhí)行的效率。MySQL有innodb_log_buffer_size配置項，超過這個值會把innodb的數(shù)據(jù)刷到磁盤中，這時，效率會有所下降。所以比較好的做法是，在數(shù)據(jù)達到這個這個值前進行事務(wù)提交。查看：show variables like '%innodb_log_buffer_size%';
+------------------------+----------+
|?Variable_name??????????|?Value????|
+------------------------+----------+
|?innodb_log_buffer_size?|?67108864?|
+------------------------+----------+
大概是：64M這種寫法和批量寫入的效果差不多，只不過sql語句還是單句的，然后統(tǒng)一提交。一個瓶頸是SQL語句的大小，一個瓶頸是事務(wù)的大小。當(dāng)我們在提交sql的時候，首先是受到sql大小的限制，其次是受到事務(wù)大小的限制。在開啟事務(wù)的情況下使用批量插入，會節(jié)省不少事務(wù)的開銷，如果要追求極致的速度的話，建議是開著事務(wù)插入的。不過需要注意一下，內(nèi)存是有限且共享的，如果批量插入占用太多的事務(wù)內(nèi)存，那么勢必會對其他的業(yè)務(wù)操作等有一定的影響。
4、通過配置提升讀寫性能
也可以通過增大innodb_buffer_pool_size 緩沖區(qū)來提升讀寫性能，只是緩沖區(qū)是要占用內(nèi)存空間的，內(nèi)存很珍貴，所以這個方案在內(nèi)存富裕，而性能瓶頸的時候，可以考慮下。5、索引影響插入性能
如果表中存在多個字段索引，當(dāng)對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也要動態(tài)的維護。這樣就降低了數(shù)據(jù)的插入速度。對于普通的數(shù)據(jù)表，主鍵索引是肯定要有的，想要加快性能的話，就是要有序插入，每次插入記錄都在索引的最后面，索引的定位效率很高，并且對索引調(diào)整較小。如果插入的記錄在索引中間，需要B+tree進行分裂合并等處理，會消耗比較多計算資源，并且插入記錄的索引定位效率會下降，數(shù)據(jù)量較大時會有頻繁的磁盤操作。五、總結(jié)
博主經(jīng)過測試+谷歌，最終是選用的一次批量插入數(shù)據(jù)量為max_allowed_packet大小的一半。只是在不斷的搜索中，發(fā)現(xiàn)影響插入性能的地方挺多的，如果僅僅是拿max_allowed_packet這個參數(shù)作為分析，其實是沒有意義的，這個參數(shù)只是設(shè)置最大值，但并不是最佳性能。不過需要注意，由于sql語句比較大，所以才執(zhí)行完插入操作之后，一定要釋放變量，不要造成無謂的內(nèi)存損耗，影響程序性能。對于我們的mysql來說也是一樣的，mysql的最佳性能是建立在各個參數(shù)的合理設(shè)置上，這樣協(xié)同干活兒的效果最佳。如果其他設(shè)置不到位的話，就像是木桶原理一樣，哪怕內(nèi)存緩沖區(qū)設(shè)置的很大，但是性能取決的反而是設(shè)置最差的那個配置。關(guān)于mysql的配置調(diào)優(yōu)，我們都在路上，加油！
版權(quán)聲明：本文為CSDN博主「鐵柱同學(xué)」的原創(chuàng)文章，遵循CC 4.0 BY-SA版權(quán)協(xié)議，轉(zhuǎn)載請附上原文出處鏈接及本聲明。原文鏈接：https://blog.csdn.net/LJFPHP/article/details/99708888參考：
- 參考：mysql技術(shù)內(nèi)幕 Innodb篇
- https://www.cnblogs.com/aicro/p/3851434.html
- https://my.oschina.net/songhongxu/blog/163063
- https://my.oschina.net/anuodog/blog/3002941
- http://mysql.taobao.org/monthly/2017/09/10/
- https://cloud.tencent.com/developer/article/1200824
End
Spring Cloud Alibaba 最新重磅發(fā)布！
Spring Cloud 微服務(wù)最新課程！
評論
圖片
表情