在做系统开发的过程中,我们经常会遇到一些报表模块,或者配合前端的echarts组件进行一些统计查询。
在统计查询中,难免会使用到一些统计函数sum()… count() … max() 等等,而我们都知道,数据库中一旦使用了函数,就会导致索引失效,并且通常这些统计的查询并不仅仅是单一的统计条件查询,甚至会有非常多的子查询嵌套。。例如我下面这个例子,这是一个朋友的群里发出来的统计查询的sql。
可以看到这个sql非常的复杂,各种统计函数,各种子查询嵌套,join查询。
这个sql他发出来的时候声称需要至少30秒才能得出结果,领导已经责令优化。但他无处下手。
在我做系统开发过程中,我认为其实像这种报表查询,其实完全可以不在sql层面去做,可以通过一些简单的sql把数据查询出来,然后使用JDK8,的lambda的流式处理工具将我们想要的结果进行统计处理。
JDK8的lambda提供了:根据某个字段统计求和,分组过滤,只需要巧妙的使用流处理,就可以大大减少sql层面花费的时间,而在jvm内存中处理数据的时间,是远远短于我们通过tcp请求mysql去处理数据花费的时间,而mysql也可以在我们优化sql而简单查询后,使用到了索引从而大大减少数据查询的时间。
废话不多说了,基于上面两个例子,进行统计查询。
前提:
我们的数据存储在数据库,数据入库时间存的不是datetime类型,而是int的10位的年月日时分秒的时间戳,在service里进行日期转换后存储的,主要是为了方便进行范围统计查询。
1、查询今日和昨日的异常数据。(从0点-24点,各个整点的数量要计算)
sql语句很简单
通过service查询到数据库之后的sql语句为:
select id,create_time from table where ......
关键地方
List<Object> dataNum = new ArrayList<>();
// 搜索个时间段的数据,求和
IntStream.rangeClosed(1, 23).forEach(t -> {
long count = value.stream()
.filter(g -> g.getAddTime() >= startTm + t * 60 * 60 && g.getAddTime() <= startTm + ((t + 1) * 60 * 60))
.mapToInt(g -> g.getId())
.count();
dataNum.add(count);
});
这段代码主要思想是:
(1)先通过sql查询出昨天0点0分,到现在这个时间之内的所有数据。由于没有使用函数统计查询,因此使用到了索引之后可以很好的使用到索引 。(gps的报警数据表,一天可以有10几万条数据。)
(2)通过IntStream.rangeClosed(1, 23)获取一个1到23的一个闭区间(就是包含1和23)的一个int的数组。然后循环遍历:
从昨日0点0分的时间戳为基准,过滤出查询的数据时间在0点-1点的数据,然后指针往后移动一个小时,再过滤出1点-2点的数据。。依次类推。