农业-大量数据在数据库中做AVG如何优化

Tsbug

已于 2024-04-25 19:22:05 修改

阅读量1.3k

点赞数 20

文章标签：数据库

于 2024-04-25 11:07:19 首次发布

本文链接：https://blog.csdn.net/Tsbug/article/details/138169921

版权

如果是直接查询呢？

设备每个分区3s回传一次数据

一个设备有三个分区

一分钟需要回传 3 * 20 = 60次

一个小时回传 60*60 = 3600次

一天回传 3600 * 24 = 86400次

我如果想计算以天为单位的气温数据，需要聚合8w条数据

进行优化

一分钟60次，记录60分钟的数据

一小时对 60分钟的数据进行统计，一共60个

一天对24小时的数据进行统计，一共24个

sql测试

在一天的数据量8640条数据中按每小时进行聚合统计，是一个很大的开销，但是可以接受

SELECT
    DATE_FORMAT(created_at, "%Y-%m-%d %H : %i") AS time_minute,
    AVG(temperature) AS avg_temperature,
    AVG(humidity) AS avg_humidity
FROM
    agriculture_cybe.device_hearts
WHERE
    device_id = 315
GROUP BY
     DATE_FORMAT(created_at, "%Y-%m-%d %H :%i") 
ORDER BY
    time_minute DESC;

执行耗时3s

查看操作行数

那么如果是月和年呢？
执行时间19s

查看操作行数

需要检索68w条这是相当大的数据

最关键的是，这个只是检索一个设备的气温开销，而我们将来可能会有300+以上的设备，这更是一笔很大的开销

如何优化呢？

分支思想

解决上面这种求平均数的方法可以使用分治的思想

如

1 2 3 4 5 6 7 8 9 求平均值

3/2 7/2 11/2 15/2 9

第一次计算

5/2 13/2 9

第三次计算

9/2 9

接下来需要加权计算

9/2*8/9 + 9*1/9

3.96+1 = 4.96

误差只有0.04完全可以接受

但是项目中没有使用加权计算这种方式，这种方式运算太复杂了

需要求出总数进行加权

我们使用的方法是

和前一个数据一起做统计，这样可以解决数据尖刺问题

数据尖刺

假设2：01的温度突然升高

此时统计的数据就是增高后的数据，并不是平均值

那么会出现下面的情况

小时图，突然飙高，但是10分钟之后又回复了正常

那是因为此时正好进行小时级别的平均值的运算

分钟表中就这一条异常数据，所以对这一条异常数据算平均数那平均数就是异常数据本身

如何解决呢？

1. 加权

2. 和前面的数据一起计算平均值

计算分钟平均值的时候，不止计算当前分钟的平均值，而是计算一个区间

1：58到2：01 这三分钟内的平均值，这样就可以解决尖刺问题了

索引

所以需要做优化，首先给时间字段加上索引（实测不一定走索引,交给执行引擎自己选择索引）

走时间索引之后的时间

避免大量数据做聚合统计

一开始是直接统计2023-2024年的数据

现在我是先统计月度数据，再将月度数据统计为年度数据

将where 的范围减少，降低数据库做聚合的压力


SELECT
    DATE_FORMAT(created_at, "%Y-%m-%d %H:%i") AS time_minute,
    AVG(temperature) AS avg_temperature,
    AVG(humidity) AS avg_humidity
FROM
    agriculture_cybe.device_hearts
WHERE
    device_id = 9 AND
    created_at >= '2024-04-25 08:56' AND
    created_at <= '2024-04-25 10:26'
GROUP BY
    time_minute
ORDER BY
    time_minute ASC;

执行时间缩短为0s