場景
由于壓力測試，您需要在數據庫中檢索大量數據，但數據庫中沒有太多數據。于是為了測試，就得往數據庫里快速插入大量的臨時數據。
有兩種方法可以快速插入大量數據：
- 一種是使用JAVA代碼實現；
- 另一種是使用數據庫存儲過程。
優化方向
直接使用foreach的方式，一條一條的直接插入數據到MySQL中，效率十分低下。大概10w數據量需要18秒左右，100w數據大概需要10多分鐘甚至直接卡死了。因此，我們可以對數據插入過程進行優化，分為下面兩個方面：
- 數據提交方面：批量提交
- 數據庫引擎方面：MyisAM
因為批量提交是分批次提交數據，因此一次創建少量的數據再分批次提交到數據庫，這樣既保證了數據傳遞的效率又不會一次占滿內存；
另外因為InnoDB的鎖級別為行鎖并且是事務性的，而MyisAM為表鎖且無事務，因此MyisAM引擎對于頻繁數據更新和插入的效率遠大于InnoDB引擎。
下面我們來進行代碼實踐：
快速實踐
1. 創建數據表
首先，你必須有一個數據表，注意數據表的引擎，在構建表時使用MyISAM引擎，MyISAM插入比InnoDB快得多，因為InnoDB的事務支持要好得多，并且在大多數情況下是default使用InnoDB，因此您可以在插入數據后將引擎從修改的MyISAM更換回為InnoDB。
CREATE TABLE `tb_data` (
`id` int(11) DEFAULT NULL,
`user_name` varchar(100) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`random` double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
復制代碼
2. 編寫數據插入類
創建100w數據插入到MySQL的測試代碼：
package com.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
?
public class InsertDataDemo {
static Connection conn = null;
?
public static void initConn() throws ClassNotFoundException, SQLException {
?
String url = "jdbc:mysql://localhost:3306/testdb?"
+ "user=root&password=root&useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=UTC";
?
try {
// 動態加載mysql驅動
Class.forName("com.mysql.jdbc.Driver");
System.out.println("成功加載MySQL驅動程序");
conn = DriverManager.getConnection(url);
} catch (Exception e) {
e.printStackTrace();
}
}
?
?
public static String randomStr(int size) {
//定義一個空字符串
String result = "";
for (int i = 0; i < size; ++i) {
//生成一個97~122之間的int類型整數
int intVal = (int) (Math.random() * 26 + 97);
//強制轉換（char）intVal 將對應的數值轉換為對應的字符，并將字符進行拼接
result = result + (char) intVal;
}
//輸出字符串
return result;
}
?
?
public static void insert(int insertNum) {
// 開時時間
Long begin = System.currentTimeMillis();
System.out.println("開始插入數據...");
// sql前綴
String prefix = "INSERT INTO tb_data (id, user_name, create_time, random) VALUES ";
?
try {
// 保存sql后綴
StringBuffer suffix = new StringBuffer();
// 設置事務為非自動提交
conn.setAutoCommit(false);
//為繼承了Statement對象所有功能的預編譯對象，性能和防SQL注入優于Statement對象，常用于重復執行的批處理命令
PreparedStatement pst = conn.prepareStatement("");
for (int i = 1; i <= insertNum; i++) {
// 構建sql后綴(并一次生成8條數據)
suffix.Append("(" + i +",'"+ randomStr(8) + "', SYSDATE(), " + i * Math.random() + "),");
}
// 構建完整sql
String sql = prefix + suffix.substring(0, suffix.length() - 1);
// 添加執行sql
pst.addBatch(sql);
// 執行操作(批處理)
pst.executeBatch();
// 提交事務
conn.commit();
// 關閉連接
pst.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
// 結束時間
Long end = System.currentTimeMillis();
System.out.println("插入"+insertNum+"條數據數據完成！");
System.out.println("耗時 : " + (end - begin) / 1000 + " 秒");
}
?
?
public static void main(String[] args) throws SQLException, ClassNotFoundException {
?
initConn();
insert(1000000);
?
}
}
復制代碼
3. 測試數據插入
注意，這里有兩個坑：1. MySQL連接器版本；2. MySQL最大內存值限制。
第一個問題：MySQL連接器版本過高或過低，需要固定依賴版本
執行后，會出現下面的錯誤：
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Successfully loaded MySQL driver
Start Inserting Data...
java.sql.SQLException: SQL String cannot be empty
復制代碼
解決方式就是：將下面mysql連接器依賴版本替換為5.1.47
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
<scope>runtime</scope>
</dependency>
復制代碼
第二個問題：MySQL最大限制內存過小
修改MySQL驅動版本為5.1+版本后，發現控制臺報OOM異常：
java.sql.BatchUpdateException: Packet for query is too large (50235460 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
復制代碼
仔細分析下，其實批處理插入數據的方式也是基于內存的，在批量提交的時候也會有一定內存的占用率。因此，應該是超過了MySQL最大內存限制導致的。
查看my.ini配置文件，發現MySQL數據庫引擎內存最大值為1M(5.7版本默認是1M)，得到驗證。
查看內存大小：
mysql> show VARIABLES like '%max_allowed_packet%';
復制代碼
控制臺修改內存大?。ㄒ部芍苯有薷膍y.ini配置文件）
mysql> mysql --max_allowed_packet=500M
或
set global max_allowed_packet = 4*1024*1024*10
復制代碼
保存，重啟MySQL服務
最后，控制臺輸出：
Successfully loaded MySQL driver
Start Inserting Data...
insert1000000 data data is completed!
Time-consuming : 7seconds
?
Process finished with exit code 0
復制代碼
數據庫顯示
到這里已經實現了快速插入100w數據到MySQL數據庫，測試成功！
注意事項
適當增加mysql的max_allowed_packet參數值允許系統在客戶端到服務器端傳遞大數據時分配更多擴展內存以進行處理。 修改mysql配置文件（不能直接通過命令行進行修改）：
[mysqld]
# 沒有不需要添加
net_buffer_length=512k
?
max_allowed_packet=500M
復制代碼
-- 更改引擎的語句 ALTER TABLE 表名 ENGINE=MyISAM;
-- 更改引擎的語句 ALTER TABLE 表明 ENGINE=InnoDB
總結
相比較于for循環直接插入而言，使用批處理提交的方式進行百萬級別的數據插入，效率的確得到了極大地提升！
如果數據量再提升一個或幾個量級，那么就需要考慮多線程和批量提交相結合的方式了，并且可以使用異步批處理的方式進行進一步優化，這里就不進行深入探究了。
歡迎點贊關注評論，感謝觀看ヾ(?°∇°?)??
鏈接：
https://juejin.cn/post/7135101795646308365
