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语句问题

聚合函数谨慎用 最好不用，因为我们无法预算以后的数据量需要扫描多少行数据来计算

优化方案离不开统计表，都需要按一定的周期储存运算好的统计数据

到此这篇关于mysql聚合统计数据查询缓慢的文章就介绍到这了,更多相关mysql聚合统计数据查询缓慢内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL使用聚合函数进行单表查询

    聚合函数 作用于一组数据,对那组数据返回一个值 count :统计结果记录多少条数, max:统计最大值 min:统计最小值 sum:计算求和 avg: 计算平均值 注意,分组函数 group by.如果要对 分组后 的数据进行筛选,那么必须使用 having 关键字,条件写在 having 后面: where:先过滤已有的数据,在分组,在聚合函数计算: having:过滤分组之后的数据.插入我们需要练习的表city 表 CREATE TABLE `city` ( `id` INT AUTO_I

  • MySql 中聚合函数增加条件表达式的方法

    Mysql 与聚合函数在一起时候where条件和having条件的过滤时机 where 在聚合之前过滤 当一个查询包含了聚合函数及where条件,像这样的情况 select max(cid) from t where t.id<999 这时候会先进行过滤,然后再聚合.先过滤出ID<999的记录,再查找最大的cid返回. having 在聚合之后过滤 having在分组的时候会使用,对分组结果进行过滤,通常里面包含聚合函数. SELECT ip,MAX(id) FROM app GROUP BY

  • php+mysql开源XNA 聚合程序发布 下载

    php+mysql开源XNA 聚合程序发布 下载

    PHP+MYSQL (php5 +mysql 4.1) 的一个简单的开源XNA聚合,效果如:http://xna.spvrk.com 一个简单的聚合程序,用phpmyadmin 导入cms_xna.sql,修改config.inc.php中的数据库地址即可使用,后台为/admin,因时间关系,暂时没写用户名和密码验证,大家可免费使用本程序,也可在本程序的基础上二次开发,但不得用于任何商业用途,不提供技术支持,但有好的新版本也可回馈给我,本程序使用通用公共授权GPL3,想做成一个开源的项目,不知有

  • Mysql无法选取非聚合列的解决方法

    Mysql无法选取非聚合列的解决方法

    1. 前言 最近升级博客,给文章页面底部增加了两个按钮,可以直接跳转到上一篇和下一篇. 如下图所示: 实现这个功能的难点在于:数据库怎么选取出一条记录的前后两条相邻的记录? 2. 数据库设计 关于我文章数据库的设计如下图所示: 可以看到,每条记录的身份是索引Id.因为之前有很多文章记录被删除了,所以,Id并不是连续的. 如果当前文章的索引值是33,那么可以通过以下命令来得到前后相邻的 2 篇文章: select * from passage where id in (select case wh

  • MySQL常用聚合函数详解

    一.AVG AVG(col) 返回指定列的平均值 二.COUNT COUNT(col) 返回指定列中非NULL值的个数 三.MIN/MAX MIN(col):返回指定列的最小值 MAX(col):返回指定列的最大值 四.SUM SUM(col) 返回指定列的所有值之和 五.GROUP_CONCAT GROUP_CONCAT([DISTINCT] expr [,expr ...]              [ORDER BY {unsigned_integer | col_name | expr}

  • MySQL单表查询操作实例详解【语法、约束、分组、聚合、过滤、排序等】

    本文实例讲述了MySQL单表查询操作.分享给大家供大家参考,具体如下: 语法 一.单表查询的语法 SELECT 字段1,字段2... FROM 表名                   WHERE 条件                   GROUP BY field                   HAVING 筛选                   ORDER BY field                   LIMIT 限制条数 二.关键字的执行优先级(重点) 重点中的重点:关键

  • MySQL查询排序与查询聚合函数用法分析

    本文实例讲述了MySQL查询排序与查询聚合函数用法.分享给大家供大家参考,具体如下: 排序 为了方便查看数据,可以对数据进行排序 语法: select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...] 说明 将行数据按照列1进行排序,如果某些行列1的值相同时,则按照列2排序,以此类推 默认按照列值从小到大排列(asc) asc从小到大排列,即升序 desc从大到小排序,即降序 例1:查询未删除男生信息,按学号降序 select * from st

  • MySQL中聚合函数count的使用和性能优化技巧

    本文的环境是Windows 10,MySQL版本是5.7.12-log 一. 基本使用 count的基本作用是有两个: 统计某个列的数据的数量: 统计结果集的行数: 用来获取满足条件的数据的数量.但是其中有一些与使用中印象不同的情况,比如当count作用一列.多列.以及使用*来表达整行产生的效果是不同的. 示例表如下: CREATE TABLE `NewTable` ( `id` int(11) NULL DEFAULT NULL , `name` varchar(30) NULL DEFAUL

  • mysql连续聚合原理与用法实例分析

    mysql连续聚合原理与用法实例分析

    本文实例讲述了mysql连续聚合原理与用法.分享给大家供大家参考,具体如下: 连续聚合是按时间顺序对有序数据进行聚合的操作. 在下面的救示例中将使用EmpOrders表,该表用于存放每位员工每月发生的订购数量. 运行如下 代码创建EmpOrders表并填充示例数据. CREATE TABLE EmpOrders ( empid INT NOT NULL, ordermonth DATE NOT NULL, qty INT NOT NULL,test PRIMARY KEY (empid,orde

  • MySQL 分组查询和聚合函数

    概述 相信我们经常会遇到这样的场景:想要了解双十一天猫购买化妆品的人员中平均消费额度是多少(这可能有利于对商品价格区间的定位):或者不同年龄段的化妆品消费占比是多少(这可能有助于对商品备货量的预估). 这个时候就要用到分组查询,分组查询的目的是为了把数据分成多个逻辑组(购买化妆品的人员是一个组,不同年龄段购买化妆品的人员也是组),并对每个组进行聚合计算的过程:. 分组查询的语法格式如下: select cname, group_fun,... from tname [where conditio

随机推荐