13 秒插入 30 萬條數據，果真高手！

前言

大家好，這裏是頂尖架構師棧！點選上方關註，添加「 星標 」，切勿錯過每日幹貨分享，一起學習大廠前沿架構！

本文主要講述透過MyBatis、JDBC等做大數據量數據插入的案例和結果。

30萬條數據插入插入資料庫驗證

驗證的資料庫表結構如下：

CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '使用者id',
`username` varchar(64) DEFAULT NULL COMMENT '使用者名稱稱',
`age` int(4) DEFAULT NULL COMMENT '年齡',
PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='使用者資訊表';


話不多說，開整！

實體類、mapper和配置檔定義

User實體

/**
 * <p>使用者實體</p>
 *
 * @Author zjq
 */
@Data
public classUser{
privateint id;
private String username;
privateint age;
}


mapper介面

publicinterfaceUserMapper{
/**
* 批次插入使用者
* @param userList
*/
voidbatchInsertUser(@Param("list") List<User> userList);

}


mapper.xml檔

<!-- 批次插入使用者資訊 -->
<insert id="batchInsertUser" parameterType="java.util.List">
insert into t_user(username,age) values
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.username},
#{item.age}
)
</foreach>
</insert>


jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=root
jdbc.password=root


sqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configurationPUBLIC"-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--透過properties標簽載入外部properties檔-->
<propertiesresource="jdbc.properties"></properties>

<!--自訂別名-->
<typeAliases>
<typeAliastype="com.zjq.domain.User"alias="user"></typeAlias>
</typeAliases>

<!--資料來源環境-->
<environmentsdefault="developement">
<environmentid="developement">
<transactionManagertype="JDBC"></transactionManager>
<dataSourcetype="POOLED">
<propertyname="driver"value="${jdbc.driver}"/>
<propertyname="url"value="${jdbc.url}"/>
<propertyname="username"value="${jdbc.username}"/>
<propertyname="password"value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>

<!--載入對映檔-->
<mappers>
<mapperresource="com/zjq/mapper/UserMapper.xml"></mapper>
</mappers>

</configuration>






不分批次直接梭哈

MyBatis直接一次性批次插入30萬條，程式碼如下：

@Test
publicvoidtestBatchInsertUser()throws IOException {
InputStream resourceAsStream =
Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession session = sqlSessionFactory.openSession();
System.out.println("===== 開始插入數據 =====");
long startTime = System.currentTimeMillis();
try {
List<User> userList = new ArrayList<>();
for (int i = 1; i <= 300000; i++) {
User user = new User();
user.setId(i);
user.setUsername("共飲一杯無 " + i);
user.setAge((int) (Math.random() * 100));
userList.add(user);
}
session.insert("batchInsertUser", userList); // 最後插入剩余的數據
session.commit();
long spendTime = System.currentTimeMillis()-startTime;
System.out.println("成功插入 30 萬條數據,耗時："+spendTime+"毫秒");
} finally {
session.close();
}
}

可以看到控制台輸出：

Cause: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (27759038 >yun 4194304). You can change this value on the server by setting the max_allowed_packet’ variable.

超出最大封包限制了，可以透過調整 max_allowed_packet 限制來提高可以傳輸的內容，不過由於30萬條數據超出太多，這個不可取，梭哈看來是不行了 😅😅😅

既然梭哈不行那我們就一條一條迴圈著插入行不行呢

迴圈逐條插入

mapper介面和mapper檔中新增單個使用者新增的內容如下:

/**
 * 新增單個使用者
 * @param user
 */
void insertUser(User user);
<!-- 新增使用者資訊 -->
<insertid="insertUser" parameterType="user">
insertinto t_user(username,age) values
(
#{username},
#{age}
)
</insert>


調整執行程式碼如下：

@Test
publicvoidtestCirculateInsertUser()throws IOException {
InputStream resourceAsStream =
Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession session = sqlSessionFactory.openSession();
System.out.println("===== 開始插入數據 =====");
long startTime = System.currentTimeMillis();
try {
for (int i = 1; i <= 300000; i++) {
User user = new User();
user.setId(i);
user.setUsername("共飲一杯無 " + i);
user.setAge((int) (Math.random() * 100));
// 一條一條新增
session.insert("insertUser", user);
session.commit();
}
long spendTime = System.currentTimeMillis()-startTime;
System.out.println("成功插入 30 萬條數據,耗時："+spendTime+"毫秒");
} finally {
session.close();
}
}

執行後可以發現磁盤IO占比飆升，一直處於高位。

等啊等等啊等，好久還沒執行完

先不管他了太慢了先搞其他的，等會再來看看結果吧。

two thousand year later …

控制台輸出如下：

總共執行了14909367毫秒，換算出來是4小時八分鐘。太慢了。。

還是最佳化下之前的批次處理方案吧

MyBatis實作插入30萬條數據

先清理表數據，然後最佳化批次處理執行插入：

-- 清空使用者表
TRUNCATE table t_user;


以下是透過 MyBatis 實作 30 萬條數據插入程式碼實作：

/**
 * 分批次批次插入
 * @throws IOException
 */
@Test
publicvoidtestBatchInsertUser()throws IOException {
InputStream resourceAsStream =
Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession session = sqlSessionFactory.openSession();
System.out.println("===== 開始插入數據 =====");
long startTime = System.currentTimeMillis();
int waitTime = 10;
try {
List<User> userList = new ArrayList<>();
for (int i = 1; i <= 300000; i++) {
User user = new User();
user.setId(i);
user.setUsername("共飲一杯無 " + i);
user.setAge((int) (Math.random() * 100));
userList.add(user);
if (i % 1000 == 0) {
session.insert("batchInsertUser", userList);
// 每 1000 條數據送出一次事務
session.commit();
userList.clear();
// 等待一段時間
Thread.sleep(waitTime * 1000);
}
}
// 最後插入剩余的數據
if(!CollectionUtils.isEmpty(userList)) {
session.insert("batchInsertUser", userList);
session.commit();
}
long spendTime = System.currentTimeMillis()-startTime;
System.out.println("成功插入 30 萬條數據,耗時："+spendTime+"毫秒");
} catch (Exception e) {
e.printStackTrace();
} finally {
session.close();
}
}


使用了 MyBatis 的批次處理操作，將每 1000 條數據放在一個批次中插入，能夠較為有效地提高插入速度。同時請註意在迴圈插入時要帶有合適的等待時間和批次處理大小，以防止出現記憶體占用過高等問題。此外，還需要在配置檔中設定合理的連線池和資料庫的參數，以獲得更好的效能。

在上面的範例中，我們每插入1000行數據就進行一次批次處理送出，並等待10秒鐘。這有助於控制記憶體占用，並確保插入操作平穩進行。

五十分鐘執行完畢，時間主要用在了等待上。

如果低谷時期執行，CPU和磁盤效能又足夠的情況下，直接批次處理不等待執行：

/**
 * 分批次批次插入
 * @throws IOException
 */
@Test
publicvoidtestBatchInsertUser()throws IOException {
InputStream resourceAsStream =
Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession session = sqlSessionFactory.openSession();
System.out.println("===== 開始插入數據 =====");
long startTime = System.currentTimeMillis();
int waitTime = 10;
try {
List<User> userList = new ArrayList<>();
for (int i = 1; i <= 300000; i++) {
User user = new User();
user.setId(i);
user.setUsername("共飲一杯無 " + i);
user.setAge((int) (Math.random() * 100));
userList.add(user);
if (i % 1000 == 0) {
session.insert("batchInsertUser", userList);
// 每 1000 條數據送出一次事務
session.commit();
userList.clear();
}
}
// 最後插入剩余的數據
if(!CollectionUtils.isEmpty(userList)) {
session.insert("batchInsertUser", userList);
session.commit();
}
long spendTime = System.currentTimeMillis()-startTime;
System.out.println("成功插入 30 萬條數據,耗時："+spendTime+"毫秒");
} catch (Exception e) {
e.printStackTrace();
} finally {
session.close();
}
}

則24秒可以完成數據插入操作：

可以看到短時CPU和磁盤占用會飆高。

把批次處理的量再調大一些調到5000，在執行：

13秒插入成功30萬條，直接蕪湖起飛🛫🛫🛫

JDBC實作插入30萬條數據

JDBC迴圈插入的話跟上面的mybatis逐條插入類似，不再贅述。

以下是 Java 使用 JDBC 批次處理實作 30 萬條數據插入的範例程式碼。請註意，該程式碼僅提供思路，具體實作需根據實際情況進行修改。

/**
 * JDBC分批次批次插入
 * @throws IOException
 */
@Test
publicvoidtestJDBCBatchInsertUser()throws IOException {
Connection connection = null;
PreparedStatement preparedStatement = null;
String databaseURL = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "root";
try {
connection = DriverManager.getConnection(databaseURL, user, password);
// 關閉自動送出事務，改為手動送出
connection.setAutoCommit(false);
System.out.println("===== 開始插入數據 =====");
long startTime = System.currentTimeMillis();
String sqlInsert = "INSERT INTO t_user ( username, age) VALUES ( ?, ?)";
preparedStatement = connection.prepareStatement(sqlInsert);
Random random = new Random();
for (int i = 1; i <= 300000; i++) {
preparedStatement.setString(1, "共飲一杯無 " + i);
preparedStatement.setInt(2, random.nextInt(100));
// 添加到批次處理中
preparedStatement.addBatch();
if (i % 1000 == 0) {
// 每1000條數據送出一次
preparedStatement.executeBatch();
connection.commit();
System.out.println("成功插入第 "+ i+" 條數據");
}
}
// 處理剩余的數據
preparedStatement.executeBatch();
connection.commit();
long spendTime = System.currentTimeMillis()-startTime;
System.out.println("成功插入 30 萬條數據,耗時："+spendTime+"毫秒");
} catch (SQLException e) {
System.out.println("Error: " + e.getMessage());
} finally {
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}






上述範例程式碼中，我們透過 JDBC 連線 MySQL 資料庫，並執行批次處理操作插入數據。具體實作步驟如下：

使用 setAutoCommit(false) 來禁止自動送出事務，然後在每次批次插入之後手動送出事務。每次插入數據時都新建一個 PreparedStatement 物件以避免狀態不一致問題。在插入數據的迴圈中，每 10000 條數據就執行一次 executeBatch() 插入數據。

另外，需要根據實際情況最佳化連線池和資料庫的相關配置，以防止連線超時等問題。

總結

實作高效的大量數據插入需要結合以下最佳化策略（建議綜合使用）：

1.批次處理： 批次送出SQL語句可以降低網路傳輸和處理開銷，減少與資料庫互動的次數。在Java中可以使用Statement或者 PreparedStatement 的 addBatch() 方法來添加多個SQL語句，然後一次性執行 executeBatch() 方法送出批次處理的SQL語句。

2.索引: 在大量數據插入前暫時去掉索引，最後再打上，這樣可以大大減少寫入時候的更新索引的時間。

3.資料庫連線池： 使用資料庫連線池可以減少資料庫連線建立和關閉的開銷，提高效能。在沒有使用資料庫連線池的情況，記得在finally中關閉相關連線。

資料庫參數調整：增加MySQL資料庫緩沖區大小、配置高效能的磁盤和I/O等。

— END —

IT一線從業者抱團群

致力於幫助廣大開發者提供高效合適的工具，讓大家能夠騰出手做更多創造性的工作，也歡迎大家分享自己公司的內推資訊，相互幫助，一起進步！

組建了程式設計師，架構師，IT從業者交流群，以 交流技術 、 職位內推 、 行業探討 為主