mysql：加速包含子查询的查询

ao218c7q 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(282)

表格：

id | date | id_device | total | others 15/20 columns
----------------------------------------------------

can包含数百万条记录
该表有一个id\u device和date列的索引。
它是一个每分钟x能量设备节省其消耗的表（总计列）。它是一个不断增加的值）。但也可能有空的时间间隔，如下表所示。
我需要计算给定设备在特定时间间隔的某一天的每小时消耗量。
要做到这一点，我有这个查询，这是有效的。示例：2018-10-03，间隔时间00-01。此间隔意味着（以及所有其他间隔）从00之前的最后一个记录开始，到00的最后一个记录结束。因此，像上面的例子一样，00间隔的总数是300-120300（00的最后一条记录）120（00之前的最后一条记录）。减法是用php完成的。

id | date                | id_device | total | others 15/20 columns
----------------------------------------------------
1  | 2018-10-02 23:50:20 | 1         | 100   | ....
2  | 2018-10-02 23:55:20 | 1         | 120   | ....
3  | 2018-10-03 00:01:20 | 1         | 150   | ....
.. | 2018-10-03 00:59:20 | 1         | 300   | ....
.. | 2018-10-03 01:00:20 | 1         | 350   | ....

SELECT `total` AS `total` FROM `mytable` AS `A`, 
    (
        SELECT MIN(`date`) AS `firstValue`, MAX(`date`) AS `lastValue`
        FROM `mytable`
        WHERE `date` BETWEEN 
        COALESCE((SELECT `date` FROM `mytable` WHERE `date` < '2018-10-03 00:00:00' AND `id_device` = 1 ORDER BY `date` DESC LIMIT 1), '2018-10-03 00:00:00'
        AND '2018-10-03 00:59:59'
        AND `id_device` = 1
    ) AS `B`

    WHERE `A`.`date` IN (`B`.`firstValue`,`B`.`lastValue`) AND `id_device` = 1
    ORDER BY `A`.`date`

使用此查询，执行时间约为0.9/1.5秒。而且它太慢了（我必须在一个循环中为每个设备计算x次这个查询）。
删除子查询时，执行时间实际上是0。执行时间是完美的，但这样的查询显然不适合我。

SELECT `total` AS `total` FROM `mytable` AS `A`, 
    (
        SELECT MIN(`date`) AS `firstValue`, MAX(`date`) AS `lastValue`
        FROM `mytable`
        WHERE `date` BETWEEN 
        '2018-10-03 00:00:00'
        AND '2018-10-03 00:59:59'
        AND `id_device` = 1
    ) AS `B`

    WHERE `A`.`date` IN (`B`.`firstValue`,`B`.`lastValue`) AND `id_device` = 1
    ORDER BY `A`.`date`

我单独测试了子查询，执行时间实际上是0。

SELECT `date` FROM `mytable` WHERE `date` < '2018-10-03 00:00:00' AND `id_device` = 1 ORDER BY `date` DESC LIMIT 1

所以我不明白为什么原来的查询这么慢。

sql mysql

来源：https://stackoverflow.com/questions/52623993/mysql-speed-up-query-that-contain-a-sub-query

3条答案

按热度按时间

vwkv1x7d1#

我首先将子查询移动到 FROM 条款：

SELECT `total` AS `total`
FROM `mytable` AS `A`CROSS JOIN 
     (SELECT MIN(t2.`date`) AS `firstValue`, MAX(t2.`date`) AS `lastValue`
      FROM `mytable` t2 CROSS JOIN
           (SELECT t3.`date`
            FROM `mytable` t3
            WHERE t3.`date` < '2018-10-03' AND t3.`id_device` = 1
            ORDER BY t3.`date` DESC
            LIMIT 1
           ) d
      WHERE t2.date >= COALESCE(d.date, '2018-10-03') AND
            t2.date < '2018-10-04' AND
            t2.id_device = 1
    ) B
WHERE `A`.`date` IN (B.firstValue, B.lastValue) AND
      A.`id_device` = 1
ORDER BY `A`.`date`;

对于这个查询，我将从 mytable(id_device, date) .
我也建议使用索引，但您指出基本查询运行得很快。因此，不需要额外的索引。
您还可以使用 union all .

赞(0）回复(0）举报 2021-06-21

ruarlubt2#

反转查询的层次结构（将外部查询转换为子查询），或者最好仍然使用联接。在连接和筛选时，如果可能的话，尝试使用索引字段。设定日期 where 并使用变量而不是日期格式。否则，它实际上是为每一行计算的，这会大大降低查询速度。

赞(0）回复(0）举报 2021-06-21

yfjy0ee73#

我想如果你能在逻辑上设置一个较低的日期范围（例如5天前或30天前，取决于你的问题）

SELECT `date` FROM `mytable` WHERE `date` < DATE_FORMAT('2018-10-03 00:00:00', '%Y-%m-%d %H:%i:%s') AND `id_device` = 1 ORDER BY `date` DESC LIMIT 1

能在合理的时间内得到回应

赞(0）回复(0）举报 2021-06-20

我来回答

mysql：加速包含子查询的查询

3条答案

相关问题

热门标签

最新问答