利用多線程批量拆分 List 導(dǎo)入數(shù)據(jù)庫，效率杠杠的！

一、前言

前兩天做了一個(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)。

下面就展示一下過程。

二、直接把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

<insert?id="insertAll"?parameterType="com.dmsdbj.itoo.basicInfo.entity.EnrollStudentEntity">??
????insert?into?tb_enroll_student??
????<trim?prefix="("?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????????trim>??????????
????values??
????<foreach?collection="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()??????????
????)?????
????foreach>??????????????????
insert>???

代碼說明：

底層的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)入。

三、分組把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ò)。

加油~~ 多線程哦~~

來源：blog.csdn.net/kisscatforever/article/

details/79817039