一、前言
我們在操作大型資料表或者日志檔案的時候經常會需要寫入資料到資料庫，那么最合適的方案就是資料庫的批量插入，只是我們在執行批量操作的時候，一次插入多少資料才合適呢？
假如需要插入的資料有百萬條，那么一次批量插入多少條的時候，效率會高一些呢？這里博主和大家一起探討下這個問題，應用環境為批量插入資料到臨時表，
二、批量插入前準備
博主本地原本是回圈查出來的資料，然后每1000條插入一次，直至完成插入操作，但是為什么要設定1000條呢，實不相瞞，這是因為專案里的其他批量插入都是一次插1000條，，汗，博主不服，所以想要測驗下，
首先是查看當前資料庫的版本，畢竟各個版本之間存在差異，脫離版本講資料庫就是耍流氓（以前沒少耍啊）：
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.34-log |
+------------+
1 row in set (0.00 sec)
1、插入到資料表的欄位
對于手動創建的臨時表來說，欄位當然是越少越好，而且欄位占用的空間要盡量小一些，這樣臨時表不至于太大，影響表操作的性能，這里需要插入的欄位是：
欄位1 int(10)
欄位2 int(10)
欄位3 int(10)
欄位4 varchar(10)
我們一共插入四個欄位，分別是3個int型別的，一個varchar型別的，整體來說這些欄位都比較小，占用的記憶體空間會小一些，
2、計算一行欄位占用的空間
對于innodb引擎來說，int型別可以存盤4個位元組，里面的Int(M)并不會影響存盤位元組的大小，這個M只是資料的展示位數，和mysql的ZEROFILL屬性有關，即在數字長度不夠的資料前面填充0，以達到設定的長度，此處不多說，想要了解的朋友可以百度一下，還是很有意思的，
varchar（10）代表可以存盤10個字符，不管是英文還是中文，最多都是10個，這部分假設存盤的是中文，在utf-8mb4下，10個中文占用10*4 = 40個位元組那么一行資料最多占用：4+4+4+40 = 52位元組
3、在資料里做插入操作的時候，整體時間的分配
鏈接耗時 （30%）
發送query到服務器 （20%）
決議query （20%）
插入操作 （10% * 詞條數目）
插入index （10% * Index的數目）
關閉鏈接 （10%）
從這里可以看出來，真正耗時的不是操作，而是鏈接，決議的程序，單條sql的話，會在鏈接，決議部分耗費大量的時間，因此速度會很慢，所以我們一般都是采用批量插入的操作，爭取在一次鏈接里面寫入盡可能多的資料，以此來提升插入的速度，但是這個盡可能多的資料是多少呢？一次到底插入多少才合適呢？
三、批量插入資料測驗
開始測驗，但是一開始插入多少是合適的呢，是否有上限？查詢mysql手冊，我們知道sql陳述句是有大小限制的，
1、SQL陳述句的大小限制
my.ini 里有 max_allowed_packet 這個引數控制通信的 packet 大小，mysql默認的sql陳述句的最大限制是1M（mysql5.7的客戶端默認是16M，服務端默認是4M），可以根據設定查看，官方解釋是適當增大 max_allowed_packet 引數可以使client端到server端傳遞大資料時，系統能夠分配更多的擴展記憶體來處理，
官方手冊：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
2、查看服務器上的引數：
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位元組 = 32M ，也就是規定大小不能超過32M，
3、計算一次能插入的最大行記錄
1M計算的話，(1024*1024)/52 ≈ 20165 ，為了防止溢位，最大可一次性插入20000條（根據自己的配置和sql陳述句大小計算），那么32M的話就是：20000 *32 = 640000 也就是64W條，
4、測驗插入資料比對
（1）插入11W條資料，按照每次10,600,1000,20000,80000來測驗：
+---------------+
| count(c1.uin) |
+---------------+
| 110000 |
+---------------+
有個博客說一次插入10條最快，，我覺得一次插的有點少，咱們試試
這個博主測驗后，認為一次插10條是性能最快的，他的每條記錄是3kb，相當于我的59行資料，取個整數60，那么對于這個博主是插入10條，對我來說插入：600，這幾個值都試試，
耗時：
11W的資料，每次插入10條，耗時：2.361s
11W的資料，每次插入600條，耗時：0.523s
11W的資料，每次插入1000條，耗時：0.429s
11W的資料，每次插入20000條，耗時：0.426s
11W的資料，每次插入80000條，耗時：0.352s
從這部分看，隨著批量插入的增加，速度略有提升，最起碼一次插10條應該不是最佳的，插入資料量多，減少了回圈的次數，也就是在資料庫鏈接部分的耗時有所減少，只是這個8W并不是極限資料，具體一次插入多少條，還有待參考，
（2）加大資料量到24w
+---------------+
| count(c1.uin) |
+---------------+
| 241397 |
+---------------+
耗時：
24W的資料，每次插入10條，耗時：4.445s
24W的資料，每次插入600條，耗時：1.187s
24W的資料，每次插入1000條，耗時：1.13s
24W的資料，每次插入20000條，耗時：0.933s
24W的資料，每次插入80000條，耗時：0.753s
一次插入24W反而性能最佳，這么代表我們的測驗資料量依然不夠，
（3）加大測驗量到42W
+---------------+
| count(c1.uin) |
+---------------+
| 418859 |
耗時：
42W的資料，每次插入1000條，耗時：2.216s
42W的資料，每次插入80000條，耗時：1.777s
42W的資料，每次插入16W條，耗時：1.523s
42W的資料，每次插入20W條，耗時：1.432s
42W的資料，每次插入30W條，耗時：1.362s
42W的資料，每次插入40W條，耗時：1.764s
隨著插入量的增加，批量插入條數多了之后，性能是有所提升的，但是在達到30W以上之后，效率反而有所下降，這部分我的理解是mysql是要分配一定的記憶體給傳過來的資料包使用，當批量插入的資料量到達一定程度之后，一次插入操作的開銷就很耗費記憶體了，
個人感覺，最佳大小是
max_allowed_packet的一半，也就是極限能插入64W，選用32W也許性能會更好一些，同時也不會對mysql的其他操作產生太大的影響，
5、如果插入的值就是sql陳述句限制的最大值，那么性能真的好嗎？
博主瘋狂谷歌百度，都沒有找到有人來具體的說一下這個問題，不過在高性能mysql里面發現一句話：
客戶端用一個單獨的資料包將查詢請求發送給服務器，所以當查詢陳述句很長的時候，需要設定
max_allowed_packet引數，但是需要注意的是，如果查詢實在是太大，服務端會拒絕接收更多資料并拋出例外，與之相反的是，服務器回應給用戶的資料通常會很多，由多個資料包組成，但是當服務器回應客戶端請求時，客戶端必須完整的接收整個回傳結果，而不能簡單的只取前面幾條結果，然后讓服務器停止發送，因而在實際開發中，盡量保持查詢簡單且只回傳必需的資料，減小通信間資料包的大小和數量是一個非常好的習慣，這也是查詢中盡量避免使用SELECT *以及加上LIMIT限制的原因之一，
后面通過各種百度，博主覺得最大只是代表傳輸資料包的最大長度，但性能是不是最佳就要從各個方面來分析了，比如下面列出的插入緩沖，以及插入索引時對于緩沖區的剩余空間需求，以及事務占有的記憶體等，都會影響批量插入的性能，
四、其他影響插入性能的因素
1、首先是插入的時候，要注意緩沖區的大小使用情況
在分析原始碼的程序中，有一句話：如果buffer pool余量不足25%，插入失敗，回傳DB_LOCK_TABLE_FULL，這個錯誤并不是直接報錯：max_allowed_packet 不夠大之類的，這個錯誤是因為對于innodb引擎來說，一次插入是涉及到事務和鎖的，在插入索引的時候，要判斷緩沖區的剩余情況，所以插入并不能僅僅只考慮max_allowed_packet的問題，也要考慮到緩沖區的大小，
2、插入快取
另外對于innodb引擎來說，因為存在插入快取（Insert Buffer）這個概念，所以在插入的時候也是要耗費一定的緩沖池記憶體的，當寫密集的情況下，插入緩沖會占用過多的緩沖池記憶體，默認最大可以占用到1/2的緩沖池記憶體，當插入緩沖占用太多緩沖池記憶體的情況下，會影響到其他的操作，
也就是說，插入緩沖受到緩沖池大小的影響，緩沖池大小為：
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
換算后的結果為：128M，也就是說，插入快取最多可以占用64M的緩沖區大小，這個大小要超過咱們設定的sql陳述句大小，所以可以忽略不計，
詳細解釋：
我們都知道，在InnoDB引擎上進行插入操作時，一般需要按照主鍵順序進行插入，這樣才能獲得較高的插入性能，當一張表中存在非聚簇的且不唯一的索引時，在插入時，資料頁的存放還是按照主鍵進行順序存放，但是對于非聚簇索引葉節點的插入不再是順序的了，這時就需要離散的訪問非聚簇索引頁，由于隨機讀取的存在導致插入操作性能下降，
InnoDB為此設計了Insert Buffer來進行插入優化，對于非聚簇索引的插入或者更新操作，不是每一次都直接插入到索引頁中，而是先判斷插入的非聚集索引是否在緩沖池中，若在，則直接插入；若不在，則先放入到一個Insert Buffer中，
看似資料庫這個非聚集的索引已經查到葉節點，而實際沒有，這時存放在另外一個位置，然后再以一定的頻率和情況進行Insert Buffer和非聚簇索引頁子節點的合并操作，這時通常能夠將多個插入合并到一個操作中，這樣就大大提高了對于非聚簇索引的插入性能，
3、使用事務提升效率
還有一種說法，使用事務可以提高資料的插入效率，這是因為進行一個INSERT操作時，MySQL內部會建立一個事務，在事務內才進行真正插入處理操作，通過使用事務可以減少創建事務的消耗，所有插入都在執行后才進行提交操作，大概如下：
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;
事務需要控制大小，事務太大可能會影響執行的效率，MySQL有innodb_log_buffer_size配置項，超過這個值會把innodb的資料刷到磁盤中，這時，效率會有所下降，所以比較好的做法是，在資料達到這個這個值前進行事務提交，
查看：show variables like '%innodb_log_buffer_size%';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| innodb_log_buffer_size | 67108864 |
+------------------------+----------+
大概是：64M
這種寫法和批量寫入的效果差不多，只不過sql陳述句還是單句的，然后統一提交，一個瓶頸是SQL陳述句的大小，一個瓶頸是事務的大小，當我們在提交sql的時候，首先是受到sql大小的限制，其次是受到事務大小的限制，在開啟事務的情況下使用批量插入，會節省不少事務的開銷，如果要追求極致的速度的話，建議是開著事務插入的，
不過需要注意一下，記憶體是有限且共享的，如果批量插入占用太多的事務記憶體，那么勢必會對其他的業務操作等有一定的影響，
4、通過配置提升讀寫性能
也可以通過增大innodb_buffer_pool_size 緩沖區來提升讀寫性能，只是緩沖區是要占用記憶體空間的，記憶體很珍貴，所以這個方案在記憶體富裕，而性能瓶頸的時候，可以考慮下，
5、索引影響插入性能
如果表中存在多個欄位索引，當對表中的資料進行增加、洗掉和修改的時候，索引也要動態的維護，這樣就降低了資料的插入速度，對于普通的資料表，主鍵索引是肯定要有的，想要加快性能的話，就是要有序插入，每次插入記錄都在索引的最后面，索引的定位效率很高，并且對索引調整較小，如果插入的記錄在索引中間，需要B+tree進行分裂合并等處理，會消耗比較多計算資源，并且插入記錄的索引定位效率會下降，資料量較大時會有頻繁的磁盤操作，
五、總結
博主經過測驗+谷歌，最終是選用的一次批量插入資料量為max_allowed_packet大小的一半，只是在不斷的搜索中，發現影響插入性能的地方挺多的，如果僅僅是拿max_allowed_packet這個引數作為分析，其實是沒有意義的，這個引數只是設定最大值，但并不是最佳性能，
不過需要注意，由于sql陳述句比較大，所以才執行完插入操作之后，一定要釋放變數，不要造成無謂的記憶體損耗，影響程式性能，
對于我們的mysql來說也是一樣的，mysql的最佳性能是建立在各個引數的合理設定上，這樣協同干活兒的效果最佳，如果其他設定不到位的話，就像是木桶原理一樣，哪怕記憶體緩沖區設定的很大，但是性能取決的反而是設定最差的那個配置，關于mysql的配置調優，我們都在路上，加油！
著作權宣告：本文為CSDN博主「鐵柱同學」的原創文章，遵循CC 4.0 BY-SA著作權協議，轉載請附上原文出處鏈接及本宣告，原文鏈接：https://blog.csdn.net/LJFPHP/article/details/99708888
參考：
- 參考：mysql技術內幕 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
近期熱文推薦：
1.1,000+ 道 Java面試題及答案整理(2022最新版)
2.勁爆！Java 協程要來了，，，
3.Spring Boot 2.x 教程，太全了！
4.別再寫滿屏的爆爆爆炸類了，試試裝飾器模式，這才是優雅的方式！！
5.《Java開發手冊（嵩山版）》最新發布，速速下載！
覺得不錯，別忘了隨手點贊+轉發哦！
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/518461.html
標籤：Java
上一篇：FlatMap和map的區別