Siam博客

mysql聚合统计数据查询缓慢优化方案

2019-08-23

写在前面

在我们日常操作数据库的时候,比如订单表、访问记录表、商品表的时候。 经常会处理计算数据列总和、数据行数等统计问题。 随着业务发展,这些表会越来越大,如果处理不当,查询统计的速度也会越来越慢,直到业务无法再容忍。 所以,我们需要先了解、思考这些场景知识点,在设计之初,便预留一些优化空间支撑业务发展。

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语句问题
  • 聚合函数谨慎用 最好不用,因为我们无法预算以后的数据量需要扫描多少行数据来计算
  • 优化方案离不开统计表,都需要按一定的周期储存运算好的统计数据
本文链接:
版权声明: 本文由 Siam原创发布,转载请遵循《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》许可协议授权

扫描二维码,分享此文章