设置timeZone时查询性能差

Question

我正在使用一个查询来汇总一天内的数据，使用简单的查询:
澄清语法，它在存储过程

中

SET TIME_ZONE = 'Asia/Dubai'; 
SELECT 
r.id, 
r.name, 
SUM(dd.activeEnergy) as activeEnergy, 
FROM rooms r
LEFT JOIN devicesData dd  ON dd.roomId = r.id 
WHERE dd.createdAt >= UNIX_TIMESTAMP(CURDATE() - INTERVAL 1 DAY)
AND dd.createdAt < UNIX_TIMESTAMP(CURDATE())
GROUP BY r.id    

有趣的是，对于较大的查询(90000行)，查询会挂起并永远循环。如果我去掉

SET TIME_ZONE = 'Asia/Dubai'; 

查询将在短响应时间(2s)下正常工作

作为解决方案，我设置了变量:

DECLARE start INT UNSIGNED; 
DECLARE end INT unsigned ; 
SET start   =   unix_timestamp(CONVERT_TZ(CURDATE()-INTERVAL 1 DAY ,'Asia/Dubai','GMT'));  
SET end     =   unix_timestamp(CONVERT_TZ(CURDATE() ,'Asia/Dubai','GMT'));  
..
WHERE dd.createdAt >= start
AND dd.createdAt < end

现在查询工作正常，即使在更大的卷。

有一个关于tzset()在这个问题上创建查询瓶颈的提示:如何将一个国家的时间戳转换为另一个国家的时间戳，而不使用tzset()并在C中正确处理DST ?关于这篇文章:https://alibaba-cloud.medium.com/mysql-practices-how-to-handle-slow-sql-execution-due-to-time-zone-setting-8c11ad29356a

这似乎表明设置时区会对性能产生影响。它似乎符合上述描述。

在查询之前不应该设置时区，这真的很奇怪吗?

Answer 1

将LEFT JOIN改为JOIN。对于LEFT，它会收集所有"缺失"的行，并不必要地将得到的null求和。

更好:

SET TIME_ZONE = 'Asia/Dubai';
SELECT  r.id, r.name, 
( SELECT SUM(dd.activeEnergy)
FROM devicesData dd
WHERE  dd.roomId = r.id
AND  dd.createdAt >= UNIX_TIMESTAMP(CURDATE() - INTERVAL 1 DAY)
AND  dd.createdAt  < UNIX_TIMESTAMP(CURDATE())
) as activeEnergy,
FROM  rooms r

这也应该避免GROUP BY。

如果这还不够好，我可能还有另一个技巧。