专栏首页宣言(Siam)博客mysql聚合统计数据查询缓慢优化方案

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

写在前面

在我们日常操作数据库的时候,比如订单表、访问记录表、商品表的时候。

经常会处理计算数据列总和、数据行数等统计问题。

随着业务发展,这些表会越来越大,如果处理不当,查询统计的速度也会越来越慢,直到业务无法再容忍。

所以,我们需要先了解、思考这些场景知识点,在设计之初,便预留一些优化空间支撑业务发展。

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语句问题
  • 聚合函数谨慎用 最好不用,因为我们无法预算以后的数据量需要扫描多少行数据来计算
  • 优化方案离不开统计表,都需要按一定的周期储存运算好的统计数据

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 无限级子商户的查询优化方法

    A 有 2 个直接下级B、C,    B有2个直接下级D、E,    C有2个直接下级F、G

    宣言言言
  • [入门] Docker容器数据储存和转移

    容器和镜像之间的主要区别是顶部的可写层。所有对容器添加新的或修改现有数据的内容都存储在该可写层中。当容器被删除时,可写层也被删除。底层镜像保持不变。

    宣言言言
  • PHP生成不重复的订单号

    使用date()函数,获取当前日期的数字,再配合rand()函数,生成几位随机数。便是一个简单的12位订单号了

    宣言言言
  • centos6下ActiveMQ+Zookeeper消息中间件集群部署记录

    由于最近一个项目并发请求压力比较大,所以考虑改进架构,引入消息中间件集群作为一个缓冲消息队列,具体需求: 1)将大量的WebService请求报文发送到mq集群...

    洗尽了浮华
  • 【华为张宝峰】AI 吓尿指数与终端智慧化未来的三大痛点(PPT)

    【新智元导读】华为在人工智能相关领域的发展路径选择,也许将改变中国 AI+ 时代的产业格局和江湖面貌。新智元特别邀请到华为 CBG 软件工程部 VP、终端智慧工...

    新智元
  • Unity导入3D模型的过程与方法

    Zoctopus
  • linux系统运维企业常见面试题集合(一)

    本文所有内容均来自于个人整理而成,其中解答均属个人观点,如有不正之处,烦请给予指正,谢谢!!!

    民工哥
  • 采购反欺诈解决方案时应当问供应商的11个问题

    现在市场上不缺欺骗防御类产品,本文介绍能够对不同的欺骗防御方案作出初步评估和筛选的11个问题,避免用户在眼花缭乱的营销手段下,花大价钱买回了效果微乎其微的东西。...

    FB客服
  • 如何在Ubuntu上加密你的信息:Vault入门教程

    Vault是一个开源工具,提供安全,可靠的方式来存储分发API密钥,访问令牌和密码等加密信息。在部署需要使用加密或敏感数据的应用程序时,您就应该试试Vault。

    苏子晨
  • Puppeteer自动化的性能优化与执行速度提升

    最近随着复杂的自动化任务的增加,robot 项目出现了很多问题,经常要人工智能,在上次清远漂流的时候,就是经常报警,而且基本都是我人工智能解决的。

    夜尽天明

扫码关注云+社区

领取腾讯云代金券