场景描述:商品信息表中,用户、商品、仓库和日期唯一确定一条记录,将用户、商品、仓库当做一个sku,每个sku前一天的期末值与下一天的期初值一致,现在需对时间范围内的数据进行汇总,大部分数据直接求和,期初取日期最早记录的值,期末取最晚日期的值。
数据量:单表数据量在两千万左右
解决思路:
1、 取得满足条件的记录集,并分组求和,非求和字段取值是随机的
2、 循环取得sku的期初值,并替换期初数据
3、 循环取得sku的期末值,并替换期末数据
解决方案:
方案一、 通过使用子查询和连接查询,一次DB操作,直接获取到结果
思路:a. 对表中所有数据进行倒叙排序(目的是group by时,非sum的字段可以获得真实的期末值)
b. 对a求得的数据,进行sum,得到期末及sum值
c. 将b 所得的结果与全表进行左连接,正序取第一条得到期初的值,并进行替换
问题:执行效率太慢
方案二、 由于方案一中,步骤c的连接操作耗时长,所以拆分成步来执行,但若每次查询20条记录,查询DB次数较方案一增加了20次
思路:a. 将方案一中的a、b操作当做一次查询,得出求和及期末的值
b. 将a中的结果,循环查询DB,得出单个sku的期初,并替换
sql大概如下:
a.
经测试,表数据量为600多万时,需要3秒多,2000多万数据量时,需要13秒左右
但是第一步就没法忍受
以上sql查询计划如下:
方案三:老老实实分三步来做,但DB查询次数又要增加20次,意味着调用一次该接口需要查询41次DB
思路:a. 对sku区间内的记录进行分组求和,得到sum值
b. 遍历a中记录求出它们的期初值并替换
c. 遍历a中记录求出它们的期末值并替换
其中b.c使用了唯一索引,单次查询时间为30ms左右,a大量使用sum函数,耗时200ms左右,故总耗时1秒多,虽然还是慢,但相比方案一、二,要快很多
总结:应该以响应时间作为衡量标准,不一定DB查询次数越少越好。