- 本文300W数据量导致导入导出同步请求超时;针对
请求超时问题
本小节暂不进行讨论。- 文章可能对
如何提高导入导出效率
这个问题有所帮助,百万数据量
只是想将问题暴露而已;并不见得能真正地、彻底地解决百万数据量的导入导出。- 如果
充分使用多线程
接口请求超时问题依旧存在,请考虑使用异步方式。异步成功后如何及时通知用户后续再进行讨论。- 文中批量操作的数量,请自行测试进行调整
https://gitee.com/antirust/idooy-stable/tree/master/idooy-EasyExcel
开发中,导入导出
功能对于后台管理这样的系统来说太常用了,除了实现该功能外导入导出的性能也需要开发人员进行充分的考虑。一般情况下,针对导入导出功能的设计会面临如下几个问题:
- 如果同步导数据,系统的承载的数据量会不会导致接口超时。
- 如果把所有数据一次性装载到内存,很容易引起OOM。
- 数据量太大sql语句必定很慢。
- 如果走异步,如何通知用户导出结果?(文中无解)
- 如果excel文件太大,目标用户打不开怎么办?(文中无解)
个别系统导入导出业务复杂,出现问题的地方就不局限于上面的这几个点,开发中如果是优化的话,那也无法抛开复杂的业务场景而单纯的去讨论导入导出的功能。曾经就亲身经历过业务员导入7W条数据跑好几个小时,同步导出10W条数据接口超时。如果没有业务逻辑从中作梗;单纯的导入和导出10W条数据还是很轻松的。
总之,导出导入功能如果追求效率,就需要往多线程上靠;必要的话,还需要进行异步操作。
本小节基于EasyExcel使用多线程进行高效的导入导出操作
百万数据准备
300W的数据大概97M大小
存储过程
create procedure insert_emp(IN num int)
begin
declare i int default 0;
set autocommit = 0;
repeat
insert into employee(last_name, age, sex, salary, job_id)
values (concat('emp', lpad(i, 8, '0')), floor(rand() * 100), if(rand() > 0.5, '男', '女'),
round(rand() * 10000), floor(rand() * 10));
set i = i + 1;
until i = num end repeat;
commit;
end;
表结构创建语句
create table if not exists employee
(
employee_id int auto_increment primary key,
last_name varchar(100) null,
age int null,
sex varchar(1) null,
salary decimal null,
job_id int null
);
插入300W数据
call insert_emp(3000000);
EasyExcel导出
导出分两步:
- select查询数据(多线程分页查询)
- 数据write写入文件中(因为EasyExcel不支持并发写,即不管是多线程写入单个sheet,还是多线程写入多个sheet都是不允许的)
故EasyExcel高效率导出,就是要合理的使用多线程进行分页数据的查询,(当然还要考虑SQL有没有优化的空间,这里不进行讨论)
EasyExcel不支持并发写
EasyExcel版本3.3.3
,并发写的时候程序异常;查看官方文档,文档明确指出‘不支持并发写’
具体解决办法参看GitHub-issues#3020
导出功能的代码片段
Long count = baseMapper.selectCount(queryWrapper);
Long sheetNum = count % pageSize == 0 ? count / pageSize:count / pageSize + 1;
// 多线程去读
// 1.初始化map容量 防止扩容带来的效率损耗
Map<Integer, Page<T>> pageMap = new ConcurrentHashMap<>(Math.toIntExact(3));
CountDownLatch countDownLatch = new CountDownLatch(Math.toIntExact(3));
// 注意 easyexcel 暂时不支持多线程并发写入!!! 详情请看github上issues
for (int i = 0 ;i< sheetNum;i++){
int finali = i;
threadPoolTaskExecutor.submit(()->{
Page<T> page = new Page<>();
page.setCurrent(finali + 1);
page.setSize(pageSize);
// 获取数据存放到map中
Page<T> selectPage = baseMapper.selectPage(page,queryWrapper);
pageMap.put(finali,selectPage);
// 消耗掉一个
countDownLatch.countDown();
});
}
try {
countDownLatch.await();
} catch (InterruptedException e) {
e.printStackTrace();
}
// 写入
try (ExcelWriter excelWriter = EasyExcel.write(out, pojoClass).build()) {
pageMap.forEach((k,v)->{
log.info("正在写入{}条数据",pageSize);
WriteSheet writeSheet = EasyExcel.writerSheet(k, "第"+(k+1)+"批数据").build();
excelWriter.write(v.getRecords(), writeSheet);
pageMap.remove(k);
});
excelWriter.finish();
}
EasyExcel导入
MySQL单次执行SQL的大小限制问题
在MySQL中,可以通过设置参数max_allowed_packet
来限制一次性执行SQL语句的最大大小。该参数表示客户端与服务器之间传输的最大包大小(单位为字节)。默认情况下,这个值被设定为4MB。
要查看当前的max_allowed_packet
值,可以使用如下命令:SHOW VARIABLES LIKE 'max_allowed_packet';
若需修改
max_allowed_packet
的值,可以使用如下命令进行更新:SET GLOBAL max_allowed_packet = <new_value>;
其中,
<new_value>
应替换为所期望的新值。注意,此操作将会影响全局范围内的配置,因此需要有适当的权限才能执行此操作。
EasyExcel导入思路如下:
- 解析一行插入一行(速度太慢,不可取)
- 逐行解析到达指定行数(EasyExcel提供PageReadListener类);数据库批量插入
// PageReadListener类基础使用片段
EasyExcel.read(resource.getInputStream(), Employee.class, new PageReadListener<Employee>((empList) -> {
// 参数empList的size = EasyExcel已经解析的行数batchSize
// 这里进行业务操作,批量insert
}, batchSize)).sheet().doRead();
- 如果是多个sheet页
- 每个线程处理一个sheet;
- 解析指定的行数以后单线程批量插入。
- 解析指定的行数以后多线程批量插入。
- 每个线程处理一个sheet;
//一个sheet开启一个线程,多线程进行批量插入
@Override
public void importEmployeeSingleSheet() throws IOException {
int batchSize = 100000;
Resource resource = resourceLoader.getResource("classpath:/repeatedWrite_100W.xlsx");
// 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
EasyExcel.read(resource.getInputStream(), Employee.class, new PageReadListener<Employee>((empList) -> {
// MybatisPlus默认一次批量插入的数据量为1000
// mybatisPlus默认的批量插入saveBatch其实就是同一个连接下单行插入,太慢了
// 方式一:伪批量,单行插入
// saveBatch(empList,batchSize);
// 方式二:使用SQL注入器,实现真正的批量插入;每次批量插入10W数据,100W一共用时56s
//employeeMapper.insertBatchSomeColumn(empList);
// 方式三:多线程批量插入,每次批量插入10W数据,100W数据一共用时12s
threadPool.execute(() -> employeeMapper.insertBatchSomeColumn(empList));
log.info("成功插入一次{}量的数据", batchSize);
}, batchSize)).sheet().doRead();
}
MybatisPlus默认提供saveBatch进行批量插入 :其实是伪批量,仅仅只是在一个Connection的生命周期中循环逐行insert而已;
- 解决方案一:自己在Mapper.xml中写批量插入的SQL,映射给Mapper接口
- 解决方案二:使用MybatisPlus的SQL注入器,可根据词条
insertBatchSomeColumn
探索MybatisPlus通过SQL注入器insertBatchSomeColumn完成批量插入