注：excel2007版本，即后缀名为.xlsx的excel文件，一个文件最多能存储104万行数据，所以100万条数据已经比较接近excel的极限值了，所以打开加载可能要稍等下。
下图为jconsole工具在进程运行期间的参数展示，可以看出内存几乎和CPU占有率都挺高的
把所有数据导出到十个文件中
多次查询，多个文件，单次写入,100万条数据，分成10个文件，每个文件的10万条数据都是单次写入。即每次都查出十万条数据导出到一个excel中。
public void export4() {
long t1 = System.currentTimeMillis();
long query = 0;
long write = 0;
for (int i = 0; i < 10; i++) {
long t3 = System.currentTimeMillis();
String fileName = "写你自己准备存excel的文件夹路径" + "单次写入" + i + ".xlsx";
ExcelWriter excelWriter = EasyExcel.write(fileName, ExamItem.class).build();
WriteSheet writeSheet = EasyExcel.writerSheet("单次写入").build();
QueryWrapper<ExamItem> queryWrapper = new QueryWrapper<>();
// select * from exam_item limit i*100000,100000(从i*100000开始，查100000条数据）
queryWrapper.last("limit " + i * 100000 + ",100000");
List<ExamItem> memberInfos = examItemService.list(queryWrapper);
long t4 = System.currentTimeMillis();
excelWriter.write(memberInfos, writeSheet);
excelWriter.finish();
long t5 = System.currentTimeMillis();
System.out.println("第"+i+"个文件的查询时间：" + (t4-t3) / 1000D);
System.out.println("第"+i+"个文件的写入时间：" + (t5-t4) / 1000D);
query += (t4-t3);
write += (t5-t4);
}
long t2 = System.currentTimeMillis();
System.out.println("耗时：" + (t2 - t1) / 1000D);
System.out.println("总查询耗时：" + query / 1000D);
System.out.println("总写耗时：" + write / 1000D);
}
| 耗时：33.145 |
|---|
| 总查询耗时：7.037 |
| 总写耗时：26.108 |
其实和一次性全部导出的区别就在于用做了十次循环，每次循环单独做数据查询、文件导出。
观察内存和CPU的变化，可以发现堆内存下降到了1G左右，CPU占有率显著下降。
分成十个文件，每个文件分十次写入
多次查询，多个文件，多次写入。100万条数据，分成10个文件，每个文件的10万条数据分10次写入，每次写入1万条数据
public void export5() {
long t1 = System.currentTimeMillis();
long query = 0;
long write = 0;
for (int i = 0; i < 10; i++) {
String fileName = "写你自己准备存excel的文件夹路径" + "分页导出" + i + ".xlsx";
ExcelWriter excelWriter = EasyExcel.write(fileName, ExamItem.class).build();
WriteSheet writeSheet = EasyExcel.writerSheet("分页导出").build();
for (int j = 0; j < 10; j++) {
long t3 = System.currentTimeMillis();
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
QueryWrapper<ExamItem> queryWrapper = new QueryWrapper<>();
queryWrapper.last("limit " + (10 * i + j) * 10000 + ",10000");
List<ExamItem> memberInfos = examItemService.list(queryWrapper);
long t4 = System.currentTimeMillis();
excelWriter.write(memberInfos, writeSheet);
long t5 = System.currentTimeMillis();
System.out.println("一万条数据第"+i+"个文件的第"+j+"次查询时间：" + (t4-t3) / 1000D);
System.out.println("一万条数据第"+i+"个文件的第"+j+"次写入时间：" + (t5-t4) / 1000D);
query += (t4-t3);
write += (t5-t4);
}
excelWriter.finish();
}
long t2 = System.currentTimeMillis();
System.out.println("总耗时：" + (t2 - t1) / 1000D);
System.out.println("总查询耗时：" + query / 1000D);
System.out.println("总写耗时：" + write / 1000D);
}
改动就是改成了两层循环，外层循环负责写入十次文件，内层循环负责每个文件写十次，每次写入1万条数据。
| 总耗时：55.701 |
|---|
| 总查询耗时：29.27 |
| 总写耗时：16.3 |
写入时间进一步减少，但是查询时间再增加，总时间反而变的更多了。
而堆内存的使用量进一步降低，CPU基本没有明显变化。
分成十个文件，每个文件分十次写入
easyexcel的官网给的简单写代码里有这么一行注释。
//在数据量不大的情况下可以使用（5000以内，具体也要看实际情况），数据量大参照 重复多次写入
于是有了思考，上面的方法虽然CPU占用很平稳，也不高，但是总时长却变长了。是不是因为easyexcel的单次写入性能性价比最高极限是5000条数据，所以，我们尝试把每次写入1万条数据再次拆分，变成每次写入5000条。
多次查询，多个文件，多次写入。100万条数据，分成10个文件，每个文件的10万条数据分20次写入，每次写入5千条数据
public void export6() {
long t1 = System.currentTimeMillis();
long query = 0;
long write = 0;
for (int i = 0; i < 10; i++) {
String fileName = "写你自己准备存excel的文件夹路径" + "分页导出-5000-" + i + ".xlsx";
ExcelWriter excelWriter = EasyExcel.write(fileName, ExamItem.class).build();
WriteSheet writeSheet = EasyExcel.writerSheet("分页导出").build();
for (int j = 0; j < 20; j++) {
long t3 = System.currentTimeMillis();
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
QueryWrapper<ExamItem> queryWrapper = new QueryWrapper<>();
queryWrapper.last("limit " + (20 * i + j) * 5000 + ",5000");
List<ExamItem> memberInfos = examItemService.list(queryWrapper);
long t4 = System.currentTimeMillis();
excelWriter.write(memberInfos, writeSheet);
long t5 = System.currentTimeMillis();
System.out.println("一万条数据第"+i+"个文件的第"+j+"次查询时间：" + (t4-t3) / 1000D);
System.out.println("一万条数据第"+i+"个文件的第"+j+"次写入时间：" + (t5-t4) / 1000D);
query += (t4-t3);
write += (t5-t4);
}
excelWriter.finish();
}
long t2 = System.currentTimeMillis();
System.out.println("总耗时：" + (t2 - t1) / 1000D);
System.out.println("总查询耗时：" + query / 1000D);
System.out.println("总写耗时：" + write / 1000D);
}
| 总耗时：82.946 |
|---|
| 总查询耗时：55.168 |
| 总写耗时：17.593 |
看到结果的时候我直接傻了，为啥更慢了。相比于上一次，内存使用量雀氏更小了，但是这耗时也增加太多了把。反向优化了属于是。于是我回去看每5000条数据后打印的耗时日志。发现查询时间越到后面越慢，刚开始每查5000条数据只用了0.03秒左右，到最后每次查询基本在0.7秒左右。
堆内存的使用量降到了900M以下，CPU的占有率降到了6%以下。
SQL优化
我开始思考，是不是因为索引失效了，但是我是写的有主键的，按照MySQL的默认引擎InnoDB的索引方法，每次查询应该会走索引的，即使表中没有主键，也没有不重复的列，InnoDB也会自己用隐藏列中的rowid做唯一索引的。不应该出现慢sql的情况的。
于是推测，可能是主键唯一索引失效了，所以我强制用id进行查询,比如查询前5000条数据
select *from exam_item,
(select id from exam_item limit 0 ,5000) as d
where exam_item.id = d.id
可能有的同学会问，为什么不直接用where限定id范围，然后查询，反而做这种查询，感觉有点脱了裤子放屁的感觉。
是这样的，如果主键连续递增，这么写sql雀氏非常完美，但是实际项目中，主键一般都是用雪花算法生成的，上一条的主键和下一条的主键之间一般不会是简单的加一减一的关系，所以我们取前5000条数据在这种情况下，就不能简单的用select * from exam_item where id>=0 and id <5000;来写了。
public void export7() {
long t1 = System.currentTimeMillis();
long query = 0;
long write = 0;
for (int i = 0; i < 10; i++) {
String fileName = "写你自己准备存excel的文件夹路径" + "分页导出-SQL优化" + i + ".xlsx";
ExcelWriter excelWriter = EasyExcel.write(fileName, ExamItem.class).build();
WriteSheet writeSheet = EasyExcel.writerSheet("分页导出").build();
for (int j = 0; j < 20; j++) {
long t3 = System.currentTimeMillis();
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
QueryWrapper<ExamItem> queryWrapper = new QueryWrapper<>();
queryWrapper.last(",(select exam_item.id as innerid from exam_item limit " + (20 * i + j) * 5000 + ",5000) as d where exam_item.id = innerid");//这里给id起别名innerid是为了防止数据库分不出来这两个id哪个是哪个
List<ExamItem> memberInfos = examItemService.list(queryWrapper);
long t4 = System.currentTimeMillis();
excelWriter.write(memberInfos, writeSheet);
long t5 = System.currentTimeMillis();
System.out.println("一万条数据第"+i+"个文件的第"+j+"次查询时间：" + (t4-t3) / 1000D);
System.out.println("一万条数据第"+i+"个文件的第"+j+"次写入时间：" + (t5-t4) / 1000D);
query += (t4-t3);
write += (t5-t4);
}
excelWriter.finish();
}
long t2 = System.currentTimeMillis();
System.out.println("总耗时：" + (t2 - t1) / 1000D);
System.out.println("总查询耗时：" + query / 1000D);
System.out.println("总写耗时：" + write / 1000D);
}
| 总耗时：55.384 |
|---|
| 总查询耗时：28.18 |
| 总写耗时：17.199 |
可以看到总查询时间明显减少。刚开始的查询时间也是0.03s左右，但是到最后的查询时间就减少为0.3s左右，比上面没有做优化的减少了很多。
堆内存小幅度降低，但是CPU占有率峰值小幅提升，应该是使用了复杂SQL查询的原因。
SQL优化2
我们来试一下理想情况下，主键全部为每次递增1的数，可以直接用where实现分页功能。
public void export8() {
long t1 = System.currentTimeMillis();
long query = 0;
long write = 0;
for (int i = 0; i < 10; i++) {
String fileName = "写你自己准备存excel的文件夹路径" + "分页导出-SQL优化2-" + i + ".xlsx";
ExcelWriter excelWriter = EasyExcel.write(fileName, ExamItem.class).build();
WriteSheet writeSheet = EasyExcel.writerSheet("分页导出").build();
for (int j = 0; j < 20; j++) {
long t3 = System.currentTimeMillis();
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
QueryWrapper<ExamItem> queryWrapper = new QueryWrapper<>();
int begin = (20 * i + j) * 5000;
queryWrapper.last("where id >=" +begin + " and id < " + (begin+5000));
List<ExamItem> memberInfos = examItemService.list(queryWrapper);
// List<ExamItem> memberInfos = examItemMapper.getPage(i,j);
long t4 = System.currentTimeMillis();
excelWriter.write(memberInfos, writeSheet);
long t5 = System.currentTimeMillis();
System.out.println("一万条数据第"+i+"个文件的第"+j+"次查询时间：" + (t4-t3) / 1000D);
System.out.println("一万条数据第"+i+"个文件的第"+j+"次写入时间：" + (t5-t4) / 1000D);
query += (t4-t3);
write += (t5-t4);
}
excelWriter.finish();
}
long t2 = System.currentTimeMillis();
System.out.println("总耗时：" + (t2 - t1) / 1000D);
System.out.println("总查询耗时：" + query / 1000D);
System.out.println("总写耗时：" + write / 1000D);
}
| 总耗时：31.315 |
|---|
| 总查询耗时：4.192 |
| 总写耗时：17.035 |
可以看到耗时又来到一个新低。但是这种情况过于理想了
三种sql语句的解释对比
-
limit分页
SELECT * FROM exam_item LIMIT 100000,5000
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| 1 | SIMPLE | exam_item | NULL | ALL | NULL | NULL | NULL | NULL | 990950 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
-
limit优化
select *from exam_item,
**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！**
**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**
**[需要这份系统化资料的朋友，可以戳这里获取](https://gitee.com/vip204888)**
