写在前面
在我们日常操作数据库的时候,比如订单表、访问记录表、商品表的时候。 经常会处理计算数据列总和、数据行数
等统计问题。 随着业务发展,这些表会越来越大,如果处理不当,查询统计的速度也会越来越慢,直到业务无法再容忍。 所以,我们需要先了解、思考这些场景知识点,在设计之初,便预留一些优化空间支撑业务发展。
sql聚合函数
在mysql等数据中,都会支持聚合函数,方便我们计算数据。 常见的有以下方法
取平均值 AVG() 求和 SUM() 最大值 MAX() 最小值 MIN() 行数 COUNT()
演示几个简单使用的sql语句: 查询u_id为100的订单总数
select count(id) from orders where u_id = 100;
查询u_id为100的订单消费总和
select sum(order_amount) from orders where u_id = 100;
查询销量最高的商品
select max(sell_num) from goods
统计7月份的订单数量、金额总和
select count(id) as count, sum(order_amount) as total_amount from orders where order_date between 20190701 and 20190731 and is_pay = 1
如果此时,订单表的总数是1亿条。并且此条sql运行很慢,我们应该如何排查优化? 有的同学会说了:行数多,在日期字段上加索引
,这样子筛选就很快了。 总数1亿条,假设7月份的订单有1000万条,加了索引的时候,筛选速度自然会提升不少。但是此时我们的问题真的解决了吗? 在这种聚合函数中,结果需要遍历每一条
数据来计算,比如我们统计订单总和,就需要每一行都读取订单金额,然后加起来。 也就是说在这条统计sql中,需要先从1亿数据中筛选1000万条数据,然后再遍历这些数据来计算。 此时就会非常慢了。
增加索引并不能解决聚合函数统计慢的问题
优化聚合统计的方案
提前预算
建立统计数据表
,以日期区分,如:20190801一天,销售了多少订单、金额等等数据。 当订单产生(支付完成后 可统计数据)时,便在统计数据表中对应的日期增加金额、数量。
需要注意的是,如果有退款等场景会影响减少数据,记得也相应地做操作处理
当我们需要统计8月份的数据时候,则只需要遍历计算这一个月的三十来行数据。
定时落地
我们可以使用easyswoole、计划任务等。来定时(比如每20分钟一次)计算总和,然后更新到统计数据表
中。 优点:做的处理比较少,也无需改动退款操作等api,只需要依赖原订单表
的数据,定时统计、刷新统计数据。
需要注意的是,根据不同的订单热度,来设置不同的落地频率,比如 一周内的数据变化几率比较大,可能20分钟落地。而一年前的数据则变化几率很小,可以选择某天同步一次,甚至确保不会变动时,则不再刷新。
总结
- 索引并不能解决统计聚合数据慢的sql语句问题
- 聚合函数谨慎用 最好不用,因为我们无法预算以后的数据量需要扫描多少行数据来计算
- 优化方案离不开统计表,都需要按一定的周期储存运算好的统计数据