MyBatis、JDBC等做大數(shù)據(jù)量數(shù)據(jù)插入的案例和結(jié)果

30萬(wàn)條數(shù)據(jù)插入插入數(shù)據(jù)庫(kù)驗(yàn)證

實(shí)體類、mapper和配置文件定義

不分批次直接梭哈

循環(huán)逐條插入

MyBatis實(shí)現(xiàn)插入30萬(wàn)條數(shù)據(jù)

JDBC實(shí)現(xiàn)插入30萬(wàn)條數(shù)據(jù)

總結(jié)

本文主要講述通過(guò)MyBatis、JDBC等做大數(shù)據(jù)量數(shù)據(jù)插入的案例和結(jié)果。

30萬(wàn)條數(shù)據(jù)插入插入數(shù)據(jù)庫(kù)驗(yàn)證

實(shí)體類、mapper和配置文件定義

User實(shí)體

mapper接口

mapper.xml文件

jdbc.properties

sqlMapConfig.xml

不分批次直接梭哈

循環(huán)逐條插入

MyBatis實(shí)現(xiàn)插入30萬(wàn)條數(shù)據(jù)

JDBC實(shí)現(xiàn)插入30萬(wàn)條數(shù)據(jù)

總結(jié)

驗(yàn)證的數(shù)據(jù)庫(kù)表結(jié)構(gòu)如下：

CREATETABLE`t_user`(
`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'用戶id',
`username`varchar(64)DEFAULTNULLCOMMENT'用戶名稱',
`age`int(4)DEFAULTNULLCOMMENT'年齡',
PRIMARYKEY(`id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='用戶信息表';

話不多說(shuō)，開(kāi)整！

基于 Spring Boot + MyBatis Plus + Vue & Element 實(shí)現(xiàn)的后臺(tái)管理系統(tǒng) + 用戶小程序，支持 RBAC 動(dòng)態(tài)權(quán)限、多租戶、數(shù)據(jù)權(quán)限、工作流、三方登錄、支付、短信、商城等功能

項(xiàng)目地址：https://github.com/YunaiV/ruoyi-vue-pro

視頻教程：https://doc.iocoder.cn/video/

實(shí)體類、mapper和配置文件定義

User實(shí)體

/**
*
用戶實(shí)體
*
*@Authorzjq
*/
@Data
publicclassUser{

privateintid;
privateStringusername;
privateintage;

}

mapper接口

publicinterfaceUserMapper{

/**
*批量插入用戶
*@paramuserList
*/
voidbatchInsertUser(@Param("list")ListuserList);


}

mapper.xml文件

 

insertintot_user(username,age)values

(
#{item.username},
#{item.age}
)

jdbc.properties

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

sqlMapConfig.xml

基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 實(shí)現(xiàn)的后臺(tái)管理系統(tǒng) + 用戶小程序，支持 RBAC 動(dòng)態(tài)權(quán)限、多租戶、數(shù)據(jù)權(quán)限、工作流、三方登錄、支付、短信、商城等功能

項(xiàng)目地址：https://github.com/YunaiV/yudao-cloud

視頻教程：https://doc.iocoder.cn/video/

不分批次直接梭哈

MyBatis直接一次性批量插入30萬(wàn)條，代碼如下：

@Test
publicvoidtestBatchInsertUser()throwsIOException{
InputStreamresourceAsStream=
Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactorysqlSessionFactory=newSqlSessionFactoryBuilder().build(resourceAsStream);
SqlSessionsession=sqlSessionFactory.openSession();
System.out.println("=====開(kāi)始插入數(shù)據(jù)=====");
longstartTime=System.currentTimeMillis();
try{
ListuserList=newArrayList<>();
for(inti=1;i<=?300000;?i++)?{
????????????User?user?=?new?User();
????????????user.setId(i);
????????????user.setUsername("共飲一杯無(wú)?"?+?i);
????????????user.setAge((int)?(Math.random()?*?100));
????????????userList.add(user);
????????}
????????session.insert("batchInsertUser",?userList);?//?最后插入剩余的數(shù)據(jù)
????????session.commit();

????????long?spendTime?=?System.currentTimeMillis()-startTime;
????????System.out.println("成功插入?30?萬(wàn)條數(shù)據(jù),耗時(shí)："+spendTime+"毫秒");
????}?finally?{
????????session.close();
????}
}

可以看到控制臺(tái)輸出：

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.

超出最大數(shù)據(jù)包限制了，可以通過(guò)調(diào)整max_allowed_packet限制來(lái)提高可以傳輸?shù)膬?nèi)容，不過(guò)由于30萬(wàn)條數(shù)據(jù)超出太多，這個(gè)不可取，梭哈看來(lái)是不行了

既然梭哈不行那我們就一條一條循環(huán)著插入行不行呢

循環(huán)逐條插入

mapper接口和mapper文件中新增單個(gè)用戶新增的內(nèi)容如下:

/**
*新增單個(gè)用戶
*@paramuser
*/
voidinsertUser(Useruser);


insertintot_user(username,age)values
(
#{username},
#{age}
)

調(diào)整執(zhí)行代碼如下：

@Test
publicvoidtestCirculateInsertUser()throwsIOException{
InputStreamresourceAsStream=
Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactorysqlSessionFactory=newSqlSessionFactoryBuilder().build(resourceAsStream);
SqlSessionsession=sqlSessionFactory.openSession();
System.out.println("=====開(kāi)始插入數(shù)據(jù)=====");
longstartTime=System.currentTimeMillis();
try{
for(inti=1;i<=?300000;?i++)?{
????????????User?user?=?new?User();
????????????user.setId(i);
????????????user.setUsername("共飲一杯無(wú)?"?+?i);
????????????user.setAge((int)?(Math.random()?*?100));
????????????//?一條一條新增
????????????session.insert("insertUser",?user);
????????????session.commit();
????????}

????????long?spendTime?=?System.currentTimeMillis()-startTime;
????????System.out.println("成功插入?30?萬(wàn)條數(shù)據(jù),耗時(shí)："+spendTime+"毫秒");
????}?finally?{
????????session.close();
????}
}

執(zhí)行后可以發(fā)現(xiàn)磁盤IO占比飆升，一直處于高位。

等啊等等啊等，好久還沒(méi)執(zhí)行完

先不管他了太慢了先搞其他的，等會(huì)再來(lái)看看結(jié)果吧。

two thousand year later …

控制臺(tái)輸出如下：

總共執(zhí)行了14909367毫秒，換算出來(lái)是4小時(shí)八分鐘。太慢了。。

還是優(yōu)化下之前的批處理方案吧

MyBatis實(shí)現(xiàn)插入30萬(wàn)條數(shù)據(jù)

先清理表數(shù)據(jù)，然后優(yōu)化批處理執(zhí)行插入：

--清空用戶表
TRUNCATEtablet_user;

以下是通過(guò) MyBatis 實(shí)現(xiàn) 30 萬(wàn)條數(shù)據(jù)插入代碼實(shí)現(xiàn)：

/**
*分批次批量插入
*@throwsIOException
*/
@Test
publicvoidtestBatchInsertUser()throwsIOException{
InputStreamresourceAsStream=
Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactorysqlSessionFactory=newSqlSessionFactoryBuilder().build(resourceAsStream);
SqlSessionsession=sqlSessionFactory.openSession();
System.out.println("=====開(kāi)始插入數(shù)據(jù)=====");
longstartTime=System.currentTimeMillis();
intwaitTime=10;
try{
ListuserList=newArrayList<>();
for(inti=1;i<=?300000;?i++)?{
????????????User?user?=?new?User();
????????????user.setId(i);
????????????user.setUsername("共飲一杯無(wú)?"?+?i);
????????????user.setAge((int)?(Math.random()?*?100));
????????????userList.add(user);
????????????if?(i?%?1000?==?0)?{
????????????????session.insert("batchInsertUser",?userList);
????????????????//?每?1000?條數(shù)據(jù)提交一次事務(wù)
????????????????session.commit();
????????????????userList.clear();

????????????????//?等待一段時(shí)間
????????????????Thread.sleep(waitTime?*?1000);
????????????}
????????}
????????//?最后插入剩余的數(shù)據(jù)
????????if(!CollectionUtils.isEmpty(userList))?{
????????????session.insert("batchInsertUser",?userList);
????????????session.commit();
????????}

????????long?spendTime?=?System.currentTimeMillis()-startTime;
????????System.out.println("成功插入?30?萬(wàn)條數(shù)據(jù),耗時(shí)："+spendTime+"毫秒");
????}?catch?(Exception?e)?{
????????e.printStackTrace();
????}?finally?{
????????session.close();
????}
}

使用了 MyBatis 的批處理操作，將每 1000 條數(shù)據(jù)放在一個(gè)批次中插入，能夠較為有效地提高插入速度。同時(shí)請(qǐng)注意在循環(huán)插入時(shí)要帶有合適的等待時(shí)間和批處理大小，以防止出現(xiàn)內(nèi)存占用過(guò)高等問(wèn)題。此外，還需要在配置文件中設(shè)置合理的連接池和數(shù)據(jù)庫(kù)的參數(shù)，以獲得更好的性能。

在上面的示例中，我們每插入1000行數(shù)據(jù)就進(jìn)行一次批處理提交，并等待10秒鐘。這有助于控制內(nèi)存占用，并確保插入操作平穩(wěn)進(jìn)行。

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

如果低谷時(shí)期執(zhí)行，CPU和磁盤性能又足夠的情況下，直接批處理不等待執(zhí)行：

/**
*分批次批量插入
*@throwsIOException
*/
@Test
publicvoidtestBatchInsertUser()throwsIOException{
InputStreamresourceAsStream=
Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactorysqlSessionFactory=newSqlSessionFactoryBuilder().build(resourceAsStream);
SqlSessionsession=sqlSessionFactory.openSession();
System.out.println("=====開(kāi)始插入數(shù)據(jù)=====");
longstartTime=System.currentTimeMillis();
intwaitTime=10;
try{
ListuserList=newArrayList<>();
for(inti=1;i<=?300000;?i++)?{
????????????User?user?=?new?User();
????????????user.setId(i);
????????????user.setUsername("共飲一杯無(wú)?"?+?i);
????????????user.setAge((int)?(Math.random()?*?100));
????????????userList.add(user);
????????????if?(i?%?1000?==?0)?{
????????????????session.insert("batchInsertUser",?userList);
????????????????//?每?1000?條數(shù)據(jù)提交一次事務(wù)
????????????????session.commit();
????????????????userList.clear();
????????????}
????????}
????????//?最后插入剩余的數(shù)據(jù)
????????if(!CollectionUtils.isEmpty(userList))?{
????????????session.insert("batchInsertUser",?userList);
????????????session.commit();
????????}

????????long?spendTime?=?System.currentTimeMillis()-startTime;
????????System.out.println("成功插入?30?萬(wàn)條數(shù)據(jù),耗時(shí)："+spendTime+"毫秒");
????}?catch?(Exception?e)?{
????????e.printStackTrace();
????}?finally?{
????????session.close();
????}
}

則24秒可以完成數(shù)據(jù)插入操作：

可以看到短時(shí)CPU和磁盤占用會(huì)飆高。

把批處理的量再調(diào)大一些調(diào)到5000，在執(zhí)行：

13秒插入成功30萬(wàn)條，直接蕪湖起飛

JDBC實(shí)現(xiàn)插入30萬(wàn)條數(shù)據(jù)

JDBC循環(huán)插入的話跟上面的mybatis逐條插入類似，不再贅述。

以下是 Java 使用 JDBC 批處理實(shí)現(xiàn) 30 萬(wàn)條數(shù)據(jù)插入的示例代碼。請(qǐng)注意，該代碼僅提供思路，具體實(shí)現(xiàn)需根據(jù)實(shí)際情況進(jìn)行修改。

/**
*JDBC分批次批量插入
*@throwsIOException
*/
@Test
publicvoidtestJDBCBatchInsertUser()throwsIOException{
Connectionconnection=null;
PreparedStatementpreparedStatement=null;

StringdatabaseURL="jdbc//localhost:3306/test";
Stringuser="root";
Stringpassword="root";

try{
connection=DriverManager.getConnection(databaseURL,user,password);
//關(guān)閉自動(dòng)提交事務(wù)，改為手動(dòng)提交
connection.setAutoCommit(false);
System.out.println("=====開(kāi)始插入數(shù)據(jù)=====");
longstartTime=System.currentTimeMillis();
StringsqlInsert="INSERTINTOt_user(username,age)VALUES(?,?)";
preparedStatement=connection.prepareStatement(sqlInsert);

Randomrandom=newRandom();
for(inti=1;i<=?300000;?i++)?{
????????????preparedStatement.setString(1,?"共飲一杯無(wú)?"?+?i);
????????????preparedStatement.setInt(2,?random.nextInt(100));
????????????//?添加到批處理中
????????????preparedStatement.addBatch();

????????????if?(i?%?1000?==?0)?{
????????????????//?每1000條數(shù)據(jù)提交一次
????????????????preparedStatement.executeBatch();
????????????????connection.commit();
????????????????System.out.println("成功插入第?"+?i+"?條數(shù)據(jù)");
????????????}

????????}
????????//?處理剩余的數(shù)據(jù)
????????preparedStatement.executeBatch();
????????connection.commit();
????????long?spendTime?=?System.currentTimeMillis()-startTime;
????????System.out.println("成功插入?30?萬(wàn)條數(shù)據(jù),耗時(shí)："+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();
????????????}
????????}
????}
}

上述示例代碼中，我們通過(guò) JDBC 連接 MySQL 數(shù)據(jù)庫(kù)，并執(zhí)行批處理操作插入數(shù)據(jù)。具體實(shí)現(xiàn)步驟如下：

獲取數(shù)據(jù)庫(kù)連接。

創(chuàng)建 Statement 對(duì)象。

定義 SQL 語(yǔ)句，使用 PreparedStatement 對(duì)象預(yù)編譯 SQL 語(yǔ)句并設(shè)置參數(shù)。

執(zhí)行批處理操作。

處理剩余的數(shù)據(jù)。

關(guān)閉 Statement 和 Connection 對(duì)象。

使用setAutoCommit(false) 來(lái)禁止自動(dòng)提交事務(wù)，然后在每次批量插入之后手動(dòng)提交事務(wù)。每次插入數(shù)據(jù)時(shí)都新建一個(gè) PreparedStatement 對(duì)象以避免狀態(tài)不一致問(wèn)題。在插入數(shù)據(jù)的循環(huán)中，每 10000 條數(shù)據(jù)就執(zhí)行一次 executeBatch() 插入數(shù)據(jù)。

另外，需要根據(jù)實(shí)際情況優(yōu)化連接池和數(shù)據(jù)庫(kù)的相關(guān)配置，以防止連接超時(shí)等問(wèn)題。

總結(jié)

實(shí)現(xiàn)高效的大量數(shù)據(jù)插入需要結(jié)合以下優(yōu)化策略（建議綜合使用）：

1.批處理： 批量提交SQL語(yǔ)句可以降低網(wǎng)絡(luò)傳輸和處理開(kāi)銷，減少與數(shù)據(jù)庫(kù)交互的次數(shù)。在Java中可以使用Statement或者PreparedStatement的addBatch()方法來(lái)添加多個(gè)SQL語(yǔ)句，然后一次性執(zhí)行executeBatch()方法提交批處理的SQL語(yǔ)句。

在循環(huán)插入時(shí)帶有適當(dāng)?shù)牡却龝r(shí)間和批處理大小，從而避免內(nèi)存占用過(guò)高等問(wèn)題：

設(shè)置適當(dāng)?shù)呐幚泶笮。号幚泶笮≈冈谝淮尾迦氩僮髦胁迦攵嗌傩袛?shù)據(jù)。如果批處理大小太小，插入操作的頻率將很高，而如果批處理大小太大，可能會(huì)導(dǎo)致內(nèi)存占用過(guò)高。通常，建議將批處理大小設(shè)置為1000-5000行，這將減少插入操作的頻率并降低內(nèi)存占用。

采用適當(dāng)?shù)牡却龝r(shí)間：等待時(shí)間指在批處理操作之間等待的時(shí)間量。等待時(shí)間過(guò)短可能會(huì)導(dǎo)致內(nèi)存占用過(guò)高，而等待時(shí)間過(guò)長(zhǎng)則可能會(huì)延遲插入操作的速度。通常，建議將等待時(shí)間設(shè)置為幾秒鐘到幾十秒鐘之間，這將使操作變得平滑且避免出現(xiàn)內(nèi)存占用過(guò)高等問(wèn)題。

可以考慮使用一些內(nèi)存優(yōu)化的技巧，例如使用內(nèi)存數(shù)據(jù)庫(kù)或使用游標(biāo)方式插入數(shù)據(jù)，以減少內(nèi)存占用。

總的來(lái)說(shuō)，選擇適當(dāng)?shù)呐幚泶笮『偷却龝r(shí)間可以幫助您平穩(wěn)地進(jìn)行插入操作，避免出現(xiàn)內(nèi)存占用過(guò)高等問(wèn)題。

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

3.數(shù)據(jù)庫(kù)連接池： 使用數(shù)據(jù)庫(kù)連接池可以減少數(shù)據(jù)庫(kù)連接建立和關(guān)閉的開(kāi)銷，提高性能。在沒(méi)有使用數(shù)據(jù)庫(kù)連接池的情況，記得在finally中關(guān)閉相關(guān)連接。

數(shù)據(jù)庫(kù)參數(shù)調(diào)整：增加MySQL數(shù)據(jù)庫(kù)緩沖區(qū)大小、配置高性能的磁盤和I/O等。