EasyExcel导出大数据文件（100万条）_easyexcel导出百万数据(1)

​
       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基本没有明显变化。


![](https://img-blog.csdnimg.cn/01e63684b6ff4496baa8d5ca031c3220.png)




### 分成十个文件，每个文件分十次写入


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 queryWrapper = new QueryWrapper<>();
               queryWrapper.last("limit " + (20 * i + j) * 5000 + “,5000”);
               List 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%以下。


![](https://img-blog.csdnimg.cn/c5fdada0fabd40df80ec87aa04082739.png)



### 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 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 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查询的原因。


![](https://img-blog.csdnimg.cn/d3037c15faa940cd8459a3db2a2b1329.png)



### 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 queryWrapper = new QueryWrapper<>();
               int begin = (20 * i + j) * 5000;
               queryWrapper.last(“where id >=” +begin + " and id < " + (begin+5000));
               List memberInfos = examItemService.list(queryWrapper);
//               List 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 |


可以看到耗时又来到一个新低。但是这种情况过于理想了


![](https://img-blog.csdnimg.cn/0390068e6a2e48e8ac73d9a680d5ab83.png)



#### 三种sql语句的解释对比


1. 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 |
±—±------------±----------±-----------±-----±--------------±-----±--------±-----±-------±---------±------+

2. limit优化

 

select *from exam_item,
(select id from exam_item limit 100000 ,5000) as d
where exam_item.id = d.id;

±—±------------±-----------±-----------±-------±--------------±--------±--------±-----±-------±---------±------------+
| id | select_type | table     | partitions | type   | possible_keys | key     | key_len | ref | rows   | filtered | Extra       |
±—±------------±-----------±-----------±-------±--------------±--------±--------±-----±-------±---------±------------+
| 1 | PRIMARY     | | NULL       | ALL   | NULL         | NULL   | NULL   | NULL | 105000 |   100.00 | NULL       |
| 1 | PRIMARY     | exam_item | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | d.id |     1 |   100.00 | NULL       |
| 2 | DERIVED     | exam_item | NULL       | index | NULL         | PRIMARY | 4       | NULL | 990950 |   100.00 | Using index |
±—±------------±-----------±-----------±-------±--------------±--------±--------±-----±-------±---------±------------+

3. 直接范围查询

 

select * from exam_item where id>=0 and id <5000;

±—±------------±----------±-----------±------±--------------±--------±--------±-----±-----±---------±------------+
| id | select_type | table     | partitions | type | possible_keys | key     | key_len | ref | rows | filtered | Extra       |
±—±------------±----------±-----------±------±--------------±--------±--------±-----±-----±---------±------------+
| 1 | SIMPLE     | exam_item | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 9898 |   100.00 | Using where |
±—±------------±----------±-----------±------±--------------±--------±--------±-----±-----±---------±------------+

我们可以在sql的explain中对比他们之间的type列。看出谁的性能最高


type列的值**按照性能高到低排列** `system` > `const` > `eq_ref` > `ref` > `ref_or_null` > `index_merge` > `range` > `index` > `ALL`




| 值 | 含义 |
| --- | --- |
| system | 这是const连接类型的特例，当查询的表只有一行时使用 |
| const | 表中有且只有一个匹配的行时使用，如对主键或唯一索引的查询，这是效率最高的链接方式 |
| eq\_ref | 唯一索引或主键查询，对应每个索引建，表中只有一条记录与之匹配 【A表扫描每一行B表只有一行匹配满足】 |
| ref | 非唯一索引查找，返回匹配某个单独值的所有行 |
| ref\_or\_null | 类似于ref类型的查询，但是附加了对NULL值列的查询 |
| index\_merge | 该链接类型表示使用了索引合并优化方法 |
| range | 索引范围扫描，常见于between、>、< 这样的查询条件 |
| index | FULL index Scan全索引扫描，同ALL的区别是，遍历的是索引树 |
| ALL | FULL TABLE Scan全表扫描 ，这是效率最差的链接方式 |




![img](https://img-blog.csdnimg.cn/img_convert/5f175ba480e0c6443db81acd8b1190cf.png)
![img](https://img-blog.csdnimg.cn/img_convert/47b65290a8252b45f834fb2626b76a9f.png)
![img](https://img-blog.csdnimg.cn/img_convert/8eed5d5d547f79810a1e26d5856991f1.png)

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618545628)**

O91UVq-1714401886537)]
[外链图片转存中...(img-hYLS7Vd5-1714401886538)]
[外链图片转存中...(img-m7WXE7de-1714401886538)]

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618545628)**