MySQL 批量操作，一次插入多少行數(shù)據(jù)效率最高？
閱讀本文大概需要 12 分鐘。
來自：blog.csdn.net/LJFPHP/article/details/99708888
一、前言
二、批量插入前準備
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.34-log |
+------------+
1 row in set (0.00 sec)
1、插入到數(shù)據(jù)表的字段
字段1 int(10)
字段2 int(10)
字段3 int(10)
字段4 varchar(10)
2、計算一行字段占用的空間
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%）
三、批量插入數(shù)據(jù)測試
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)存來處理。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、計算一次能插入的最大行記錄
(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 |
+---------------+
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
（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
（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
max_allowed_packet的一半，也就是極限能插入64W，選用32W也許性能會更好一些，同時也不會對mysql的其他操作產(chǎn)生太大的影響。5、如果插入的值就是sql語句限制的最大值，那么性能真的好嗎？
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ū)的大小。2、插入緩存
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
Insert Buffer中。Insert Buffer和非聚簇索引頁子節(jié)點的合并操作。這時通常能夠?qū)⒍鄠€插入合并到一個操作中，這樣就大大提高了對于非聚簇索引的插入性能。3、使用事務(wù)提升效率
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;
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 |
+------------------------+----------+
4、通過配置提升讀寫性能
innodb_buffer_pool_size 緩沖區(qū)來提升讀寫性能，只是緩沖區(qū)是要占用內(nèi)存空間的，內(nèi)存很珍貴，所以這個方案在內(nèi)存富裕，而性能瓶頸的時候，可以考慮下。5、索引影響插入性能
五、總結(jié)
max_allowed_packet大小的一半。只是在不斷的搜索中，發(fā)現(xiàn)影響插入性能的地方挺多的，如果僅僅是拿max_allowed_packet這個參數(shù)作為分析，其實是沒有意義的，這個參數(shù)只是設(shè)置最大值，但并不是最佳性能。推薦閱讀：
首次力壓 MacOS，Linux 桌面版在2022年殺瘋了！
領(lǐng)域驅(qū)動設(shè)計（DDD）的幾種典型架構(gòu)介紹
互聯(lián)網(wǎng)初中高級大廠面試題(9個G) 內(nèi)容包含Java基礎(chǔ)、JavaWeb、MySQL性能優(yōu)化、JVM、鎖、百萬并發(fā)、消息隊列、高性能緩存、反射、Spring全家桶原理、微服務(wù)、Zookeeper......等技術(shù)棧！
?戳閱讀原文領(lǐng)?。?/span> 朕已閱
