MyBatis實現(xiàn)插入大量數(shù)據(jù)方法詳解
1、前言
在開發(fā)過程中，有時我們會碰到將大批量的數(shù)據(jù)入庫的場景，那么我們一般有下面三種方式入庫：
- ExecutorType.BATCH批處理方式插入
- foreach循環(huán)標簽插入
- MyBatisPlus自帶的saveBatch批量新增方法
下面我們用一個案例來測試一下，看下三種方式哪種效率最好
2、案例說明
現(xiàn)在我數(shù)據(jù)庫新建一張表t_user，建表語句如下：
DROP TABLE IF EXISTS `t_user`; CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID', `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名', `age` int(11) NULL DEFAULT NULL COMMENT '年齡', `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '手機號', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用戶表' ROW_FORMAT = DYNAMIC;
我這里MySQL數(shù)據(jù)庫版本是5.5.28
然后我用上面三種方式分別向t_user表中存5萬條數(shù)據(jù)，每次存數(shù)據(jù)前，先清空一下表數(shù)據(jù)，使用下面語句快速清除表數(shù)據(jù)：
truncate table t_user;
每種方式我都測試5次，然后每種方式我都計算一個耗時平均值，看哪種方式耗時最小
3、編碼
這是我的基礎項目：https://gitee.com/colinWu_java/spring-boot-base.git
我會在此基礎項目上做測試
3.1、ExecutorType.BATCH批處理方式插入
ExecutorType.BATCH介紹：
- Mybatis內置的ExecutorType有3種，SIMPLE、REUSE、BATCH，默認的是simple，該模式下它為每個語句的執(zhí)行創(chuàng)建一個新的預處理語句，單條提交sql
- 而batch模式重復使用已經(jīng)預處理的語句，并且批量執(zhí)行所有更新語句，顯然batch性能將更優(yōu)；但batch模式也有自己的問題，比如在Insert操作時，在事務沒有提交之前，是沒有辦法獲取到自增的id，這在某型情形下是不符合業(yè)務要求的
- 通過批處理的方式，我們就可以在 JDBC 客戶端緩存多條 SQL 語句，然后在 flush 或緩存滿的時候，將多條 SQL 語句打包發(fā)送到數(shù)據(jù)庫執(zhí)行，這樣就可以有效地降低上述兩方面的損耗，從而提高系統(tǒng)性能
下面開始編碼
在UserController新增下面接口
/**
* 測試大批量數(shù)據(jù)插入數(shù)據(jù)庫
* 方式1：用一個 for 循環(huán)，把數(shù)據(jù)一條一條的插入
* @return
*/
@GetMapping("/insertUser1/{count}")
public JSONResult insertUser1(@PathVariable("count") Integer count){
return userService.insertUser1(count);
}
userService代碼：
public JSONResult insertUser1(Integer count) {
//如果自動提交設置為true，將無法控制提交的條數(shù)。所以我這里設置為false，，改為統(tǒng)一提交
SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
UserMapper uMapper = session.getMapper(UserMapper.class);
//獲取測試用戶集合數(shù)據(jù)
List<User> userList = Tools.getUserList(count);
int commitCount = 5000;//每次提交的數(shù)量條數(shù)
long startTime = System.currentTimeMillis();
for(int i=0; i<userList.size(); i++){
uMapper.addUserOne(userList.get(i));
if (i != 0 && i % commitCount == 0) {
session.commit();
}
}
session.commit();
long endTime = System.currentTimeMillis();
log.info("方式1耗時：{}", (endTime - startTime));
return JSONResult.success();
}
在UserMapper中新增addUserOne方法：
Integer addUserOne(User user);
對應xml代碼：
<insert id="addUserOne">
insert into t_user (name, age, phone) values (#{name}, #{age}, #{phone})
</insert>
getUserList方法代碼如下，就是獲取指定數(shù)量的測試用戶數(shù)據(jù)而已：
/**
* 獲取指定數(shù)量的用戶測試對象
* @param count 數(shù)量
* @return
*/
public static List<User> getUserList(int count){
List<User> userList = new ArrayList<>();
User user = null;
for(int i=1; i<=count; i++){
user = new User();
user.setName("王天霸" + i + "號");
user.setAge(i);
user.setAge(i);
user.setPhone("18022222222");
userList.add(user);
}
return userList;
}
注意事項：
如果單次提交給MySQL數(shù)據(jù)庫的數(shù)據(jù)量太大的話，可能會報如下錯誤：
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (204444558 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
出現(xiàn)這個錯誤的原因是：
大數(shù)據(jù)量的插入或更新會被 max_allowed_packet 參數(shù)限制，導致失敗，因為MySQL根據(jù)配置文件會限制server允許接收的數(shù)據(jù)包大小
解決方案：
修改MySQL的配置參數(shù)
打開Mysql命令客戶端：
然后輸入密碼之后輸入下面命令：
show VARIABLES like '%max_allowed_packet%';
結果：
用這個值除以1024*1024等于200M，那我這里就是設置的200M，根據(jù)實際情況設置該值，執(zhí)行如下命令即可：
set global max_allowed_packet = 200*1024*1024;
設置完成之后，需要關閉MySQL服務，然后再重啟（注意，這里最大設置為：1G）
重啟成功后，需要關掉上面的MySQL命令客戶端，再重新打開一次，查看是否設置成功
注意：
- MySQL的JDBC連接的url中要加rewriteBatchedStatements參數(shù)，并保證5.1.13以上版本的驅動，才能實現(xiàn)高性能的批量插入
- MySQL JDBC驅動在默認情況下會無視executeBatch()語句，把我們期望批量執(zhí)行的一組SQL語句拆散，一條一條地發(fā)給MySQL數(shù)據(jù)庫，批量插入實際上是單條插入，直接造成較低的性能
- 只有把rewriteBatchedStatements參數(shù)置為true，驅動才會幫你批量執(zhí)行SQL，另外這個選項對INSERT/UPDATE/DELETE都有效
url: jdbc:mysql://127.0.0.1:3306/test1?allowMultiQueries=true&rewriteBatchedStatements=true
//allowMultiQueries=true，允許一次性執(zhí)行多條SQL,批量插入時必須在連接地址后面加allowMultiQueries=true這個參數(shù)
//rewriteBatchedStatements=true，批量將數(shù)據(jù)傳給MySQL，數(shù)據(jù)庫會更高性能的執(zhí)行批量處理，MySQL數(shù)據(jù)庫版本在5.1.13以上，才能實現(xiàn)高性能的批量插入
3.2、foreach循環(huán)標簽插入
UserController新增方法：
/**
* 測試大批量數(shù)據(jù)插入數(shù)據(jù)庫
* 方式2：foreach標簽批量插入
* @return
*/
@GetMapping("/insertUser2/{count}")
public JSONResult insertUser2(@PathVariable("count") Integer count){
return userService.insertUser2(count);
}insertUser2代碼：
public JSONResult insertUser2(Integer count) {
//獲取測試用戶集合數(shù)據(jù)
List<User> userList = Tools.getUserList(count);
int countItem = 5000;//每次提交的記錄條數(shù)
int userSize = userList.size();
List<User> userListTemp = new ArrayList<>();
long startTime = System.currentTimeMillis();
for (int i = 0, n=userSize; i < n; i++) {
User user= userList.get(i);
userListTemp.add(user);
if ((i>0 && i % countItem == 0) || i == userSize - 1) {
//每5000條記錄提交一次
userMapper.insertUserBatch(userListTemp);
userListTemp.clear();
}
}
long endTime = System.currentTimeMillis();
log.info("方式2耗時：{}", (endTime - startTime));
return JSONResult.success();
}UserMapper的insertUserBatch方法：
void insertUserBatch(@Param("userList") List<User> userList);對應xml代碼：
<insert id="insertUserBatch">
insert into t_user (name, age, phone) values
<foreach collection="userList" item="user" separator=",">
(#{user.name}, #{user.age}, #{user.phone})
</foreach>
</insert>
3.3、MyBatisPlus自帶的saveBatch批量新增方法
UserController新增方法：
@Autowired
public UserService userService;
/**
* 測試大批量數(shù)據(jù)插入數(shù)據(jù)庫
* 方式3：mybatisplus自帶的saveBatch批量新增方法
* @return
*/
@GetMapping("/insertUser3/{count}")
public JSONResult insertUser3(@PathVariable("count") Integer count){
//獲取測試用戶集合數(shù)據(jù)
List<User> userList = Tools.getUserList(count);
long startTime = System.currentTimeMillis();
userService.saveBatch(userList, 5000);//每次往數(shù)據(jù)庫提交5000條數(shù)據(jù)
long endTime = System.currentTimeMillis();
log.info("方式3耗時：{}", (endTime - startTime));
return JSONResult.success();
}
4、測試
在瀏覽器中訪問地址：http://localhost:8001/user/insertUser1/50000
這里的50000是表示插入5萬條數(shù)據(jù)到t_user表
每次訪問后，都記錄一下耗時，然后清掉t_user表數(shù)據(jù)，再訪問，反復測試5次
然后再訪問http://localhost:8001/user/insertUser2/50000和http://localhost:8001/user/insertUser2/50000
步驟和上面一致，最終我統(tǒng)計出來的結果如下：
很明顯，方式一效率最高
注意：以上結果僅是我本地測試情況，大家機器上可能會不同
5、總結
經(jīng)過今天的測試，我們知道批量插入數(shù)據(jù)最快的方式就是ExecutorType.BATCH批處理方式插入有些小的注意點記得在實際開發(fā)中謹慎處理，比如url參數(shù)配置，還有提交數(shù)量不要太大
到此這篇關于MyBatis實現(xiàn)插入大量數(shù)據(jù)方法詳解的文章就介紹到這了,更多相關MyBatis插入大量數(shù)據(jù)內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
- MyBatis批量插入幾千條數(shù)據(jù)為何慎用foreach
- SpringBoot Mybatis批量插入Oracle數(shù)據(jù)庫數(shù)據(jù)
- Mybatis?Plus插入數(shù)據(jù)后獲取新數(shù)據(jù)id值的踩坑記錄
- 如何用注解的方式實現(xiàn)Mybatis插入數(shù)據(jù)時返回自增的主鍵Id
- mybatis插入數(shù)據(jù)后如何返回新增數(shù)據(jù)的id值
- MyBatis?實現(xiàn)多對多中間表插入數(shù)據(jù)
- MyBatis實現(xiàn)批量插入數(shù)據(jù),多重forEach循環(huán)
- Mybatis如何獲取最新插入數(shù)據(jù)的id
相關文章
構造函數(shù),是一種特殊的方法,主要用來在創(chuàng)建對象時初始化對象,即為對象成員變量賦初始值,總與new運算符一起使用在創(chuàng)建對象的語句中,這篇文章主要給大家介紹了關于JAVA構造函數(shù)(方法)的相關資料,需要的朋友可以參考下2021-11-11
這篇文章主要為大家詳細介紹了Java實現(xiàn)排球比賽計分系統(tǒng)，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-06-06
Java中4種校驗注解詳解(值校驗、范圍校驗、長度校驗、格式校驗)
這篇文章主要給大家介紹了關于Java中4種校驗注解詳解的相關資料,分別包括值校驗、范圍校驗、長度校驗、格式校驗等,Java注解(Annotation)是一種元數(shù)據(jù),它可以被添加到Java代碼中,并可以提供額外的信息和指令,需要的朋友可以參考下2023-08-08
Tornadofx學習筆記之IconTextFx開源庫整合5000+個字體圖標
這篇文章主要介紹了Tornadofx學習筆記之IconTextFx開源庫整合5000+個字體圖標的相關知識,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-12-12
今天介紹一下Spring事物不生效的場景,事物是我們在項目中經(jīng)常使用的,如果是Java的話,基本上都使用Spring的事物,不過Spring的事物如果使用不當,那么就會導致事物失效或者不回滾,最終導致數(shù)據(jù)不一致,下面我們意義列舉不生效的場景,并給出解決方法2023-09-09
使用Java實現(xiàn)百萬Excel數(shù)據(jù)導出
這篇文章主要為大家詳細介紹了如何使用Java實現(xiàn)百萬Excel數(shù)據(jù)導出,文中的示例代碼講解詳細,具有一定的借鑒價值,有需要的小伙伴可以參考一下2024-03-03
這篇文章主要介紹了IDEA設置背景為自定義照片,本文通過圖文并茂的形式給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-12-12
動態(tài)?SQL?是?MyBatis?的強大特性之一。如果你使用過?JDBC?或其它類似的框架，你應該能理解根據(jù)不同條件拼接?SQL?語句有多痛苦，例如拼接時要確保不能忘記添加必要的空格，還要注意去掉列表最后一個列名的逗號。利用動態(tài)?SQL，可以徹底擺脫這種痛苦2022-04-04
這篇文章主要介紹了基于java構造方法中對Vector刪除元素的源碼分析，有需要的朋友可以借鑒參考下，希望可以有所幫助，祝大家早日升職加薪2021-09-09
這篇文章主要介紹了springboot添加AOP日志配置詳解,日志是一種在軟件開發(fā)中常用的技術,用于記錄和跟蹤應用程序的運行過程,通過AOP日志,開發(fā)人員可以實時監(jiān)控應用程序的行為,包括方法的調用、參數(shù)的傳遞和返回值的獲取等,需要的朋友可以參考下2023-10-10