项目小结（1）——报表中对于60w的数据量，怎么进行sql查询优化

问题描述

近期要做一个项目是展示一个报表，要对后端传来的数据，在前端进行展示并进行相同单元格的合并显示同时在打印页面也要进行单元格合并，框架使用的是bootstraptable，接下来我分成前端显示的单元格合并，和打印时的单元格合并，其中设计到的思路我会写下来，实现功能和逻辑其实不算很难，重点是sql中的优化和各种合并单元格的问题困扰了近一个礼拜。

一、最初的sql

要做的报表，一开始没有想那么多，直接多表联合查询，

1. 然后要对部门进行分组直接group by ‘dept’ 然后把要的部门展示出来GROUP_CONCAT(dept_name)
2. 分别对每个出入库记录进行计次count()对价格进行求和sum()。

结果写完测试，好家伙查询时间有30多秒，但是当时觉得写的一点问题都没有啊，主要是还是没有接触过这么多数据，当时在学校的项目也都是假数据，没有那么多条，不管怎么写，因为数据少，查询结果都很快。

放在的实际工作的项目中可就不一样了，当时向师傅进行请教，给我进行了优化，优化如下：

1. 首先对子查询进行测试看一个具体是哪一个sql块查询的慢
2. 减少不必要的分组和where条件
3. 对LEFT JOIN的右表的条件加上索引

同时又教给我一个新的方法对于大的数据量——定时任务

1. 可以建立一个专门供展示的报表，每次只查询它
2. 数据通过定时任务，每有更新操作等，就把操作的数据添加到展示的表中

在优化后果然快了很多，但是放到具体的代码中，又慢了，分析原因，在后端的框架中有自动分页的方法，而分页是对总的sql代码进行count()，然后再limit0，10，而有的右表中是不影响总数的，把右表的sql加上只会增加查询次数。
所以后来自己又重写的分页，没有用框架本身的，速度又快了好多。
本以为这样可以了的时候，新的需求又增加了，要增加新的筛选条件和新的求和，求count的统计，当时出于着急也没有过多的分析，直接本可以在后端完成的求和计数的统计，全都在数据库中做了，由于筛选条件要在右表中进行，自己的分页也失去了效果。
此时的我彻底懵了，一个查询要近1min，这边项目还着急要发版。

二、sql优化

所以在项目进行中，一方面要有提前量，同时也要考虑好规划，再开始去做。不然之前写的代码，做的工作都是白白浪费了时间。

最后机缘巧合，我所在的项目突然有比这个项目还要着急的事，就暂缓了测试，发版，真的是天无绝人之路啊，老天救了我。重新整理好心态，稳住。复盘，好好看一下自己写的。

重新写了sql语句，把能在后台完成的工作交给后台，对于求和和计数都要进行分组，这个时候就要用到java8中的特性了（我也是通过项目才了解到）

`Map<String, List<类>> deptMap = orderAndSupplyReportList.stream()
                .filter(map->map.getDeptName()!=null)  //过滤为空的
                .collect(Collectors.groupingBy(类::get要分组的方法));`

e
再对得到的map进行循环

for (Map.Entry<String, List<类>> entry : deptMap.entrySet()) {
//进行分组求和计数
}

sql去掉了计算果然快了好多，但这还远远不够，通过查资料，说60w的数据也不是很大，完全可以通过索引进行优化

于是乎又开始学起了优化索引
通过explain看看哪些数据比较大，哪些走了索引？

1. 一方面要对右表中on所连接的条件添加索引
2. 根据实际项目，用户登录后会通过当前用户进行筛选并不会看到所有数据，所以对用户的id，也就是where后的条件添加索引进行查询果然又快了很多，然后根据使用情况，用户总是通过某个筛选条件，比如部门，设备来查看报表，又对条件添加了组合索引。
   最终sql优化到了查询在1~2秒之内。

对于刚工作的小白，很多事情并不是理想中的那么容易，只有通过时间，慢慢努力，去完善自己，写出更加优美的代码，更少的bug，我知道我的方法可能并不是那么完美，希望看到最后的前辈们，能够说一下一般遇到这种问题，大家是怎么做的。给我这个小白一些工作经验