Mysql:加速包含子查询的查询
Mysql:加速包含子查询的查询
提问于 2018-10-03 09:34:00
表：
id | date | id_device | total | others 15/20 columns
----------------------------------------------------- 包含数以百万计的记录
- 该表具有、id_device、和date列的索引。
- 它是一个表，每分钟X能源设备节省它的消耗(总列。它是一个不断增加的值)。但也可能有空的时间间隔，如下表所示。
我需要计算给定设备在特定间隔时间的某一天的每小时消耗。
要做到这一点，我有一个查询，，它可以工作。例子: 2018-10-03，间隔时间00-01.此间隔意味着(以及所有其他记录)从00之前的最后一个记录开始，然后结束到00的最后一个记录。因此，与上面的示例一样，00间隔的总数为300-120，300 (最后一个记录为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所以我不明白为什么原来的查询这么慢。
回答 3
Stack Overflow用户
发布于 2018-10-03 09:46:26
我认为，如果您可以在逻辑上为日期设置一个较低的范围(例如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能在合理的时间内得到响应
Stack Overflow用户
发布于 2018-10-03 09:54:17
反转查询的层次结构(将外部查询转换为子查询)，或者更好地使用联接。尝试(这是船长显而易见的)尝试使用索引字段，如果可能时，加入和过滤。在变量中设置where的日期，并使用变量而不是日期格式。否则，实际上将对每一行进行计算，这会大大降低查询的速度。
Stack Overflow用户
发布于 2018-10-03 10:34:46
首先，将子查询移到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简化逻辑。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52623993
复制相关文章
相似问题
腾讯云开发者
