MySQL批量插入數(shù)據(jù)的四種方案(性能測(cè)試對(duì)比)

本文記錄個(gè)人使用MySQL插入大數(shù)據(jù)總結(jié)較實(shí)用的方案，通過(guò)對(duì)常用插入大數(shù)據(jù)的4種方式進(jìn)行測(cè)試，即for循環(huán)單條、拼接SQL、批量插入saveBatch()、循環(huán) + 開(kāi)啟批處理模式，得出比較實(shí)用的方案心得。

一、前言

最近趁空閑之余，在對(duì)MySQL數(shù)據(jù)庫(kù)進(jìn)行插入數(shù)據(jù)測(cè)試，對(duì)于如何快速插入數(shù)據(jù)的操作無(wú)從下手，在僅1W數(shù)據(jù)量的情況下，竟花費(fèi)接近47s，實(shí)在不忍直視！在不斷摸索之后，整理出一些較實(shí)用的方案。

二、準(zhǔn)備工作

測(cè)試環(huán)境：SpringBoot項(xiàng)目、MyBatis-Plus框架、MySQL8.0.24、JDK13

前提：SpringBoot項(xiàng)目集成MyBatis-Plus上述文章有配置過(guò)程，同時(shí)實(shí)現(xiàn)IService接口用于進(jìn)行批量插入數(shù)據(jù)操作saveBatch()方法

1、Maven項(xiàng)目中pom.xml文件引入的相關(guān)依賴如下

 

org.springframework.boot
spring-boot-starter-web


 

com.baomidou
mybatis-plus-boot-starter
3.3.1


 

mysql
mysql-connector-java


 

org.projectlombok
lombok

2、application.yml配置屬性文件內(nèi)容（重點(diǎn)：開(kāi)啟批處理模式）

server:
#端口號(hào)
port:8080

#MySQL連接配置信息（以下僅簡(jiǎn)單配置，更多設(shè)置可自行查看）
spring:
datasource:
#連接地址（解決UTF-8中文亂碼問(wèn)題+時(shí)區(qū)校正）
#（rewriteBatchedStatements=true開(kāi)啟批處理模式）
url:jdbc//127.0.0.1:3306/bjpowernode?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
#用戶名
username:root
#密碼
password:xxx
#連接驅(qū)動(dòng)名稱
driver-class-name:com.mysql.cj.jdbc.Driver

3、Entity實(shí)體類(lèi)（測(cè)試）

/**
*Student測(cè)試實(shí)體類(lèi)
*
*@Data注解：引入Lombok依賴，可省略Setter、Getter方法
*@authorLBF
*@date2022/3/1816:06
*/
@Data
@TableName(value="student")
publicclassStudent{

/**主鍵type:自增*/
@TableId(type=IdType.AUTO)
privateintid;

/**名字*/
privateStringname;

/**年齡*/
privateintage;

/**地址*/
privateStringaddr;

/**地址號(hào)@TableField：與表字段映射*/
@TableField(value="addr_num")
privateStringaddrNum;

publicStudent(Stringname,intage,Stringaddr,StringaddrNum){
this.name=name;
this.age=age;
this.addr=addr;
this.addrNum=addrNum;
}
}

4、數(shù)據(jù)庫(kù)student表結(jié)構(gòu)（注意：無(wú)索引）

三、測(cè)試工作

簡(jiǎn)明：完成準(zhǔn)備工作后，即對(duì)for循環(huán)、拼接SQL語(yǔ)句、批量插入saveBatch()、循環(huán)插入+開(kāi)啟批處理模式，該4種插入數(shù)據(jù)的方式進(jìn)行測(cè)試性能。

注意：測(cè)試數(shù)據(jù)量為5W、單次測(cè)試完清空數(shù)據(jù)表（確保不受舊數(shù)據(jù)影響）

以下測(cè)試內(nèi)容可能受測(cè)試配置環(huán)境、測(cè)試規(guī)范和數(shù)據(jù)量等諸多因素影響，讀者可自行結(jié)合參考進(jìn)行測(cè)試

1、for循環(huán)插入（單條）（總耗時(shí)：177秒）

總結(jié)：測(cè)試平均時(shí)間約是177秒，實(shí)在是不忍直視（捂臉），因?yàn)槔胒or循環(huán)進(jìn)行單條插入時(shí)，每次都是在獲取連接(Connection)、釋放連接和資源關(guān)閉等操作上，（如果數(shù)據(jù)量大的情況下）極其消耗資源，導(dǎo)致時(shí)間長(zhǎng)。

@GetMapping("/for")
publicvoidforSingle(){
//開(kāi)始時(shí)間
longstartTime=System.currentTimeMillis();
for(inti=0;i

	

	（1）第一次測(cè)試結(jié)果：190155 約等于 190秒

	

	（2）第二次測(cè)試結(jié)果：175926 約等于 176秒（服務(wù)未重啟）

	

	（3）第三次測(cè)試結(jié)果：174726 約等于 174秒（服務(wù)重啟）

	

	2、拼接SQL語(yǔ)句（總耗時(shí)：2.9秒）

	簡(jiǎn)明：拼接格式：insert into student(xxxx) value(xxxx),(xxxx),(xxxxx).......

	總結(jié)：拼接結(jié)果就是將所有的數(shù)據(jù)集成在一條SQL語(yǔ)句的value值上，其由于提交到服務(wù)器上的insert語(yǔ)句少了，網(wǎng)絡(luò)負(fù)載少了，性能也就提上去。

	但是當(dāng)數(shù)據(jù)量上去后，可能會(huì)出現(xiàn)內(nèi)存溢出、解析SQL語(yǔ)句耗時(shí)等情況，但與第一點(diǎn)相比，提高了極大的性能。

	
@GetMapping("/sql")
publicvoidsql(){
ArrayListarrayList=newArrayList<>();
longstartTime=System.currentTimeMillis();
for(inti=0;i
//使用@Insert注解插入：此處為簡(jiǎn)便，不寫(xiě)Mapper.xml文件
@Insert("")
intinsertSplice(@Param("studentList")ListstudentList);


	

	（1）第一次測(cè)試結(jié)果：3218 約等于 3.2秒

	

	（2）第二次測(cè)試結(jié)果：2592 約等于 2.6秒（服務(wù)未重啟）

	

	（3）第三次測(cè)試結(jié)果：3082 約等于 3.1秒（服務(wù)重啟）

	

	3、批量插入saveBatch（總耗時(shí)：2.7秒）

	簡(jiǎn)明：使用MyBatis-Plus實(shí)現(xiàn)IService接口中批處理saveBatch()方法，對(duì)底層源碼進(jìn)行查看時(shí)，可發(fā)現(xiàn)其實(shí)是for循環(huán)插入，但是與第一點(diǎn)相比，為什么性能上提高了呢？因?yàn)槔梅制幚恚╞atchSize = 1000） + 分批提交事務(wù)的操作，從而提高性能，并非在Connection上消耗性能。

	
@GetMapping("/saveBatch1")
publicvoidsaveBatch1(){
ArrayListarrayList=newArrayList<>();
longstartTime=System.currentTimeMillis();
//模擬數(shù)據(jù)
for(inti=0;i

	

	（1）第一次測(cè)試結(jié)果：2864 約等于 2.9秒

	

	（2）第二次測(cè)試結(jié)果：2302 約等于 2.3秒（服務(wù)未重啟）

	

	（3）第三次測(cè)試結(jié)果：2893 約等于 2.9秒（服務(wù)重啟）

	

	重點(diǎn)注意：MySQL JDBC驅(qū)動(dòng)默認(rèn)情況下忽略saveBatch()方法中的executeBatch()語(yǔ)句，將需要批量處理的一組SQL語(yǔ)句進(jìn)行拆散，執(zhí)行時(shí)一條一條給MySQL數(shù)據(jù)庫(kù)，造成實(shí)際上是分片插入，即與單條插入方式相比，有提高，但是性能未能得到實(shí)質(zhì)性的提高。

	測(cè)試：數(shù)據(jù)庫(kù)連接URL地址缺少 rewriteBatchedStatements = true 參數(shù)情況

	
#MySQL連接配置信息
spring:
datasource:
#連接地址（未開(kāi)啟批處理模式）
url:jdbc//127.0.0.1:3306/bjpowernode?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
#用戶名
username:root
#密碼
password:xxx
#連接驅(qū)動(dòng)名稱
driver-class-name:com.mysql.cj.jdbc.Driver


	

	測(cè)試結(jié)果：10541 約等于 10.5秒（未開(kāi)啟批處理模式）

	

	4、循環(huán)插入 + 開(kāi)啟批處理模式（總耗時(shí)：1.7秒）（重點(diǎn)：一次性提交）

	簡(jiǎn)明：開(kāi)啟批處理，關(guān)閉自動(dòng)提交事務(wù)，共用同一個(gè)SqlSession之后，for循環(huán)單條插入的性能得到實(shí)質(zhì)性的提高；由于同一個(gè)SqlSession省去對(duì)資源相關(guān)操作的耗能、減少對(duì)事務(wù)處理的時(shí)間等，從而極大程度上提高執(zhí)行效率。（目前個(gè)人覺(jué)得最優(yōu)方案）

	
@GetMapping("/forSaveBatch")
publicvoidforSaveBatch(){
//開(kāi)啟批量處理模式BATCH、關(guān)閉自動(dòng)提交事務(wù)false
SqlSessionsqlSession=sqlSessionFactory.openSession(ExecutorType.BATCH,false);
//反射獲取，獲取Mapper
StudentMapperstudentMapper=sqlSession.getMapper(StudentMapper.class);
longstartTime=System.currentTimeMillis();
for(inti=0;i

	

	（1）第一次測(cè)試結(jié)果：1831 約等于 1.8秒

	

	（2）第二次測(cè)試結(jié)果：1382 約等于 1.4秒（服務(wù)未重啟）

	

	（3）第三次測(cè)試結(jié)果：1883 約等于 1.9秒（服務(wù)重啟）

	

	四、總結(jié)

	本文記錄個(gè)人學(xué)習(xí)MySQL插入大數(shù)據(jù)一些方案心得，可得知主要是在獲取連接、關(guān)閉連接、釋放資源和提交事務(wù)等方面較耗能，其中最需要注意是開(kāi)啟批處理模式，即URL地址的參數(shù)：rewriteBatchedStatements = true，否則也無(wú)法發(fā)揮作用。

	對(duì)于測(cè)試方案的設(shè)定、對(duì)考慮不周、理解和編寫(xiě)錯(cuò)誤的地方等情況，請(qǐng)多指出，共同學(xué)習(xí)！

	審核編輯：湯梓紅