給你 2 萬條數(shù)據(jù)，怎么快速導(dǎo)入到 MySQL？寫得太好了！
來源：blog.csdn.net/kisscatforever/
article/details/79817039
一、前言
前兩天做了一個(gè)導(dǎo)入的功能，導(dǎo)入開始的時(shí)候非常慢，導(dǎo)入2w條數(shù)據(jù)要1分多鐘，后來一點(diǎn)一點(diǎn)的優(yōu)化，從直接把list懟進(jìn)Mysql中，到分配把list導(dǎo)入Mysql中，到多線程把list導(dǎo)入Mysql中。時(shí)間是一點(diǎn)一點(diǎn)的變少了。非常的爽，最后變成了10s以內(nè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
二、直接把list懟進(jìn)Mysql
使用mybatis的批量導(dǎo)入操作：
??@Transactional(rollbackFor?=?Exception.class)
????public?int?addFreshStudentsNew2(List<FreshStudentAndStudentModel>?list,?String?schoolNo)?{
????????if?(list?==?null?||?list.isEmpty())?{
????????????return?0;
????????}
????????List?studentEntityList?=?new?LinkedList<>();
????????List?enrollStudentEntityList?=?new?LinkedList<>();
????????List?allusersEntityList?=?new?LinkedList<>();
????????for?(FreshStudentAndStudentModel?freshStudentAndStudentModel?:?list)?{
????????????EnrollStudentEntity?enrollStudentEntity?=?new?EnrollStudentEntity();
????????????StudentEntity?studentEntity?=?new?StudentEntity();
????????????BeanUtils.copyProperties(freshStudentAndStudentModel,?studentEntity);
????????????BeanUtils.copyProperties(freshStudentAndStudentModel,?enrollStudentEntity);
????????????String?operator?=?TenancyContext.UserID.get();
????????????String?studentId?=?BaseUuidUtils.base58Uuid();
????????????enrollStudentEntity.setId(BaseUuidUtils.base58Uuid());
????????????enrollStudentEntity.setStudentId(studentId);
????????????enrollStudentEntity.setIdentityCardId(freshStudentAndStudentModel.getIdCard());
????????????enrollStudentEntity.setOperator(operator);
????????????studentEntity.setId(studentId);
????????????studentEntity.setIdentityCardId(freshStudentAndStudentModel.getIdCard());
????????????studentEntity.setOperator(operator);
????????????studentEntityList.add(studentEntity);
????????????enrollStudentEntityList.add(enrollStudentEntity);
????????????AllusersEntity?allusersEntity?=?new?AllusersEntity();
????????????allusersEntity.setId(enrollStudentEntity.getId());
????????????allusersEntity.setUserCode(enrollStudentEntity.getNemtCode());
????????????allusersEntity.setUserName(enrollStudentEntity.getName());
????????????allusersEntity.setSchoolNo(schoolNo);
????????????allusersEntity.setTelNum(enrollStudentEntity.getTelNum());
????????????allusersEntity.setPassword(enrollStudentEntity.getNemtCode());??//密碼設(shè)置為考生號(hào)
????????????allusersEntityList.add(allusersEntity);
????????}
????????????enResult?=?enrollStudentDao.insertAll(enrollStudentEntityList);
????????????stuResult?=?studentDao.insertAll(studentEntityList);
????????????allResult?=?allusersFacade.insertUserList(allusersEntityList);
????????if?(enResult?>?0?&&?stuResult?>?0?&&?allResult)?{
????????????return?10;
????????}
????????return?-10;
????}
Mapper.xml
??"insertAll"?parameterType="com.dmsdbj.itoo.basicInfo.entity.EnrollStudentEntity">
????????insert?into?tb_enroll_student
????????"("?suffix=")"?suffixOverrides=",">
????????????????id,??
????????????????remark,??
????????????????nEMT_aspiration,??
????????????????nEMT_code,??
????????????????nEMT_score,??
????????????????student_id,??
????????????????identity_card_id,??
????????????????level,??
????????????????major,??
????????????????name,??
????????????????nation,??
????????????????secondary_college,??
????????????????operator,??
????????????????sex,??
????????????????is_delete,??
????????????????account_address,??
????????????????native_place,??
????????????????original_place,??
????????????????used_name,??
????????????????pictrue,??
????????????????join_party_date,??
????????????????political_status,??
????????????????tel_num,??
????????????????is_registry,??
????????????????graduate_school,??
????????????????create_time,??
????????????????update_time????????????????
????????values
????????"list"?item="item"?index="index"?separator=",">
????????(
????????????????#{item.id,jdbcType=VARCHAR},
????????????????#{item.remark,jdbcType=VARCHAR},
????????????????#{item.nemtAspiration,jdbcType=VARCHAR},
????????????????#{item.nemtCode,jdbcType=VARCHAR},
????????????????#{item.nemtScore,jdbcType=VARCHAR},
????????????????#{item.studentId,jdbcType=VARCHAR},
????????????????#{item.identityCardId,jdbcType=VARCHAR},
????????????????#{item.level,jdbcType=VARCHAR},
????????????????#{item.major,jdbcType=VARCHAR},
????????????????#{item.name,jdbcType=VARCHAR},
????????????????#{item.nation,jdbcType=VARCHAR},
????????????????#{item.secondaryCollege,jdbcType=VARCHAR},
????????????????#{item.operator,jdbcType=VARCHAR},
????????????????#{item.sex,jdbcType=VARCHAR},
????????????????0,
????????????????#{item.accountAddress,jdbcType=VARCHAR},
????????????????#{item.nativePlace,jdbcType=VARCHAR},
????????????????#{item.originalPlace,jdbcType=VARCHAR},
????????????????#{item.usedName,jdbcType=VARCHAR},
????????????????#{item.pictrue,jdbcType=VARCHAR},
????????????????#{item.joinPartyDate,jdbcType=VARCHAR},
????????????????#{item.politicalStatus,jdbcType=VARCHAR},
????????????????#{item.telNum,jdbcType=VARCHAR},
????????????????#{item.isRegistry,jdbcType=TINYINT},
????????????????#{item.graduateSchool,jdbcType=VARCHAR},
????????????????now(),
????????????????now()????????
????????)???
????????????????????????
???
代碼說明：
底層的mapper是通過逆向工程來生成的，批量插入如下，是拼接成類似：insert into tb_enroll_student（）values （），（）…….（）;
這樣的缺點(diǎn)是，數(shù)據(jù)庫一般有一個(gè)默認(rèn)的設(shè)置，就是每次sql操作的數(shù)據(jù)不能超過4M。這樣插入，數(shù)據(jù)多的時(shí)候，數(shù)據(jù)庫會(huì)報(bào)錯(cuò)Packet for query is too large (6071393 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.，雖然我們可以通過
類似 修改 my.ini 加上?max_allowed_packet =67108864，67108864=64M，默認(rèn)大小4194304 也就是4M
修改完成之后要重啟mysql服務(wù)，如果通過命令行修改就不用重啟mysql服務(wù)。
完成本次操作，但是我們不能保證項(xiàng)目單次最大的大小是多少，這樣是有弊端的。所以可以考慮進(jìn)行分組導(dǎo)入。
基于微服務(wù)的思想，構(gòu)建在 B2C 電商場(chǎng)景下的項(xiàng)目實(shí)戰(zhàn)。核心技術(shù)棧，是 Spring Boot + Dubbo 。未來，會(huì)重構(gòu)成 Spring Cloud Alibaba 。
項(xiàng)目地址：https://github.com/YunaiV/onemall
三、分組把list導(dǎo)入Mysql中
同樣適用mybatis批量插入，區(qū)別是對(duì)每次的導(dǎo)入進(jìn)行分組計(jì)算，然后分多次進(jìn)行導(dǎo)入：
?@Transactional(rollbackFor?=?Exception.class)
????public?int?addFreshStudentsNew2(List<FreshStudentAndStudentModel>?list,?String?schoolNo)?{
????????if?(list?==?null?||?list.isEmpty())?{
????????????return?0;
????????}
????????List?studentEntityList?=?new?LinkedList<>();
????????List?enrollStudentEntityList?=?new?LinkedList<>();
????????List?allusersEntityList?=?new?LinkedList<>();
????????for?(FreshStudentAndStudentModel?freshStudentAndStudentModel?:?list)?{
????????????EnrollStudentEntity?enrollStudentEntity?=?new?EnrollStudentEntity();
????????????StudentEntity?studentEntity?=?new?StudentEntity();
????????????BeanUtils.copyProperties(freshStudentAndStudentModel,?studentEntity);
????????????BeanUtils.copyProperties(freshStudentAndStudentModel,?enrollStudentEntity);
????????????String?operator?=?TenancyContext.UserID.get();
????????????String?studentId?=?BaseUuidUtils.base58Uuid();
????????????enrollStudentEntity.setId(BaseUuidUtils.base58Uuid());
????????????enrollStudentEntity.setStudentId(studentId);
????????????enrollStudentEntity.setIdentityCardId(freshStudentAndStudentModel.getIdCard());
????????????enrollStudentEntity.setOperator(operator);
????????????studentEntity.setId(studentId);
????????????studentEntity.setIdentityCardId(freshStudentAndStudentModel.getIdCard());
????????????studentEntity.setOperator(operator);
????????????studentEntityList.add(studentEntity);
????????????enrollStudentEntityList.add(enrollStudentEntity);
????????????AllusersEntity?allusersEntity?=?new?AllusersEntity();
????????????allusersEntity.setId(enrollStudentEntity.getId());
????????????allusersEntity.setUserCode(enrollStudentEntity.getNemtCode());
????????????allusersEntity.setUserName(enrollStudentEntity.getName());
????????????allusersEntity.setSchoolNo(schoolNo);
????????????allusersEntity.setTelNum(enrollStudentEntity.getTelNum());
????????????allusersEntity.setPassword(enrollStudentEntity.getNemtCode());??//密碼設(shè)置為考生號(hào)
????????????allusersEntityList.add(allusersEntity);
????????}
????????int?c?=?100;
????????int?b?=?enrollStudentEntityList.size()?/?c;
????????int?d?=?enrollStudentEntityList.size()?%?c;
????????int?enResult?=?0;
????????int?stuResult?=?0;
????????boolean?allResult?=?false;
????????for?(int?e?=?c;?e?<=?c?*?b;?e?=?e?+?c)?{
????????????enResult?=?enrollStudentDao.insertAll(enrollStudentEntityList.subList(e?-?c,?e));
????????????stuResult?=?studentDao.insertAll(studentEntityList.subList(e?-?c,?e));
????????????allResult?=?allusersFacade.insertUserList(allusersEntityList.subList(e?-?c,?e));
????????}
????????if?(d?!=?0)?{
????????????enResult?=?enrollStudentDao.insertAll(enrollStudentEntityList.subList(c?*?b,?enrollStudentEntityList.size()));
????????????stuResult?=?studentDao.insertAll(studentEntityList.subList(c?*?b,?studentEntityList.size()));
????????????allResult?=?allusersFacade.insertUserList(allusersEntityList.subList(c?*?b,?allusersEntityList.size()));
????????}
????????if?(enResult?>?0?&&?stuResult?>?0?&&?allResult)?{
????????????return?10;
????????}
????????return?-10;
????}
代碼說明：
這樣操作，可以避免上面的錯(cuò)誤，但是分多次插入，無形中就增加了操作實(shí)踐，很容易超時(shí)。所以這種方法還是不值得提倡的。
再次改進(jìn)，使用多線程分批導(dǎo)入。
四、多線程分批導(dǎo)入Mysql
依然使用mybatis的批量導(dǎo)入，不同的是，根據(jù)線程數(shù)目進(jìn)行分組，然后再建立多線程池，進(jìn)行導(dǎo)入。
??@Transactional(rollbackFor?=?Exception.class)
????public?int?addFreshStudentsNew(List<FreshStudentAndStudentModel>?list,?String?schoolNo)?{
????????if?(list?==?null?||?list.isEmpty())?{
????????????return?0;
????????}
????????List?studentEntityList?=?new?LinkedList<>();
????????List?enrollStudentEntityList?=?new?LinkedList<>();
????????List?allusersEntityList?=?new?LinkedList<>();
????????list.forEach(freshStudentAndStudentModel?->?{
????????????EnrollStudentEntity?enrollStudentEntity?=?new?EnrollStudentEntity();
????????????StudentEntity?studentEntity?=?new?StudentEntity();
????????????BeanUtils.copyProperties(freshStudentAndStudentModel,?studentEntity);
????????????BeanUtils.copyProperties(freshStudentAndStudentModel,?enrollStudentEntity);
????????????String?operator?=?TenancyContext.UserID.get();
????????????String?studentId?=?BaseUuidUtils.base58Uuid();
????????????enrollStudentEntity.setId(BaseUuidUtils.base58Uuid());
????????????enrollStudentEntity.setStudentId(studentId);
????????????enrollStudentEntity.setIdentityCardId(freshStudentAndStudentModel.getIdCard());
????????????enrollStudentEntity.setOperator(operator);
????????????studentEntity.setId(studentId);
????????????studentEntity.setIdentityCardId(freshStudentAndStudentModel.getIdCard());
????????????studentEntity.setOperator(operator);
????????????studentEntityList.add(studentEntity);
????????????enrollStudentEntityList.add(enrollStudentEntity);
????????????AllusersEntity?allusersEntity?=?new?AllusersEntity();
????????????allusersEntity.setId(enrollStudentEntity.getId());
????????????allusersEntity.setUserCode(enrollStudentEntity.getNemtCode());
????????????allusersEntity.setUserName(enrollStudentEntity.getName());
????????????allusersEntity.setSchoolNo(schoolNo);
????????????allusersEntity.setTelNum(enrollStudentEntity.getTelNum());
????????????allusersEntity.setPassword(enrollStudentEntity.getNemtCode());??//密碼設(shè)置為考生號(hào)
????????????allusersEntityList.add(allusersEntity);
????????});
????????int?nThreads?=?50;
????????int?size?=?enrollStudentEntityList.size();
????????ExecutorService?executorService?=?Executors.newFixedThreadPool(nThreads);
????????List>?futures?=?new?ArrayList>(nThreads);
????????for?(int?i?=?0;?i?????????????final?List?EnrollStudentEntityImputList?=?enrollStudentEntityList.subList(size?/?nThreads?*?i,?size?/?nThreads?*?(i?+?1));
????????????final?List?studentEntityImportList?=?studentEntityList.subList(size?/?nThreads?*?i,?size?/?nThreads?*?(i?+?1));
????????????final?List?allusersEntityImportList?=?allusersEntityList.subList(size?/?nThreads?*?i,?size?/?nThreads?*?(i?+?1));
???????????Callable?task1?=?()?->?{
??????????studentSave.saveStudent(EnrollStudentEntityImputList,studentEntityImportList,allusersEntityImportList);
???????????????return?1;
????????????};
??????????futures.add(executorService.submit(task1));
????????}
????????executorService.shutdown();
????????if?(!futures.isEmpty()?&&?futures?!=?null)?{
????????????return?10;
????????}
????????return?-10;
????}
代碼說明：
上面是通過應(yīng)用ExecutorService 建立了固定的線程數(shù)，然后根據(jù)線程數(shù)目進(jìn)行分組，批量依次導(dǎo)入。一方面可以緩解數(shù)據(jù)庫的壓力，另一個(gè)面線程數(shù)目多了，一定程度會(huì)提高程序運(yùn)行的時(shí)間。缺點(diǎn)就是要看服務(wù)器的配置，如果配置好的話就可以開多點(diǎn)線程，配置差的話就開小點(diǎn)。
五、小結(jié)
通過使用這個(gè)操作真是不斷的提高了，項(xiàng)目使用技巧也是不錯(cuò)。加油~~ 多線程哦~~
完
往期推薦
TCP 就沒什么缺陷嗎？
自作孽！知網(wǎng)終于被查了，涉嫌壟斷，背后到底是何方神圣？
一款自動(dòng)生成單元測(cè)試的 IDEA 插件
有道無術(shù)，術(shù)可成；有術(shù)無道，止于術(shù)
歡迎大家關(guān)注Java之道公眾號(hào)
好文章，我在看??
評(píng)論
圖片
表情