时间戳

首页 > 解决方案 > 如何按速度优化mysql的日期组？

mysql - 如何按速度优化mysql的日期组？

问题描述

CREATE TABLE `device_m1000` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`sensor_id` MEDIUMINT(9) NOT NULL,
`ctime` DATETIME NOT NULL,
`now_data` FLOAT(12) NOT NULL,
`total_data` FLOAT(12) NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `sensor_id` (`sensor_id`) USING BTREE,
INDEX `ctime` (`ctime`) USING BTREE) COLLATE='utf8_unicode_ci' ENGINE=InnoDB AUTO_INCREMENT=296533;

在此处输入图像描述

上表有 300,000 条数据。total_data 列始终以累积使用量计。（一个总是增加的结构）如果我想知道“今天”的用法，我会根据今天的日期取最小值和最大值并减去它。

试试 sql

SELECT ROUND((max.v - min.v), 2) total
FROM   (SELECT
               DATE(ctime) `date`,
               sum(total_data)  v
        FROM   device_m1000
        WHERE  ( sensor_id, ctime ) IN (SELECT sensor_id,
                                               MAX(ctime) AS dt
                                        FROM   device_m1000
                                        WHERE  ctime >= '2020-11-23 06:30' and ctime < '2020-11-24 06:30'
                                        GROUP  BY sensor_id
                                        ORDER  BY null)
        group by `date`) max
       INNER JOIN (SELECT
                          DATE(ctime) `date`,
                          sum(total_data)  v
                   FROM   device_m1000
                   WHERE  ( sensor_id, ctime ) IN (SELECT sensor_id,
                                                          MIN(ctime) AS dt
                                                   FROM   device_m1000
                                                   WHERE  ctime >= '2020-11-23 06:30' and ctime < '2020-11-24 06:30'
                                                   GROUP  BY sensor_id
                                                   ORDER  BY null)
                   group by `date`) min
               ON min.`date` = max.`date`;

使用以下查询获取数据。但是，它只是在 max(ctime) 的代码中延迟了 10 秒以上。我该如何优化它？

标签： mysqlsqlquery-optimizationaggregate-functionswhere-clause

解决方案

  • “行构造函数”表现不佳。( WHERE (a,b) IN ...)
  • FLOAT(12)没有给你 12 个有效数字，你只得到大约 7 个。
  • id有用吗？还是(sensor_id, ctime)独一无二的？如果它是唯一的，则使其成为PRIMARY KEY并摆脱id.
  • 如果你不能使它成为PK，有INDEX(sensor_id, ctime)。

回到你的问题。放弃将小计放入每一行的尝试。几乎任何方法每秒都将执行超过 10 亿次操作——因为每天必须为每个传感器汇总大约 30K 项。

而是制作一个单独的“汇总表”，其中包含日期、传感器和当天的总计。然后，当您INSERT进入详细信息表（如您所拥有的）时，也执行

INSERT INTO daily_summary (date, sensor, tot)
    VALUES (?, ?, ?)
    ON DUPLICATE KEY UPDATE
        tot = VALUES(tot) + ?

并DOUBLE用于tot. 并且有PRIMARY KEY(sensor, date)

这避免了你的减法并提供了很多效率。

更多关于汇总表：http: //mysql.rjweb.org/doc.php/summarytables

如果您需要每小时小计，则将其设为每小时表而不是每天。您可以通过从每小时汇总表中添加 24 行来有效地获取每日金额。

推荐阅读