MySQL查询优化？

Question

我有一个包含约5百万行数据的数据库，并尝试优化其加载时间。我已经尽可能优化了列和索引，因此我认为问题出在查询语句上。这个查询在一个包含约5百万行数据的表上运行，并返回其中的40行，但需要101秒才能完成。

如果我删除时区转换，同样的查询只需0.0015秒即可完成，但由于时区差异，结果略有不同。如何优化以获得正确的结果并提高速度？

SELECT *, date(CONVERT_TZ(eventDate, "US/Eastern", "America/New_York")) as `timezoneDate`
FROM `transactions`
WHERE `isValid` = X
AND `storeID` = X
AND date(CONVERT_TZ(eventDate, "US/Eastern", "America/New_York")) >= '2014-11-19'
AND date(CONVERT_TZ(eventDate, "US/Eastern", "America/New_York")) <= '2014-11-25'
ORDER BY `eventDate` 

如果之前不清楚的话，我正在转换的第二个时区会根据用户而变化，所以现在是美国/纽约，与US/Eastern相同，但它会改变。

- CMOS

---

抱歉，你无法这样做。因为你正在使用派生值，这意味着没有索引可用。例如，where foo=1 是可以的，并且可以使用任何适用的索引。而 where somefunc(foo)=1 则不行，因为 somefunc 的结果是无法被索引的。 - Marc B

如果去掉时区限制，以+和-一天的方式获取结果，然后运行PHP循环来计算并删除多余的结果，会更快吗？我认为对10-12个额外行进行时区转换并删除一些行比100秒更快。 - CMOS

如果返回的数字足够小，可以尝试在没有调用CONVERT_TZ函数的情况下运行它，并且使用稍微宽松一些的时间范围（例如2014年11月18日至2014年11月26日），然后再缩小范围。哦，我在打字的时候想到了这个，抱歉。 - TZHX

查询在时区转换时只有选择操作变慢了吗？0.0015秒内返回了多少行？ - JRD

反转逻辑。在您的固定值上进行时区/时间转换，例如 eventdate = date(convert_tz('2014-11-19')) 这样的操作。然后，您可以使用索引。因为索引字段正在被“原样”使用。 - Marc B

Answer 1

我最终采用了JRD、TZHX和MarcB的建议。我选择了没有时区的查询，但将我的选择半径增加了正负一天。这将我的约500万行数据减少到了大约50行。然而，由于结果略多于我想要的数量，我随后对第一个查询返回的50个结果运行了原始查询。这导致所有完全相同的数据只需0.11秒即可返回。非常感谢你们！

- CMOS

Answer 2

关于推迟日期筛选，您的意思是什么？

select * from (     
SELECT *, date(CONVERT_TZ(eventDate, "US/Eastern", "America/New_York")) as `timezoneDate`
    FROM `transactions`
    WHERE `isValid` = X
    AND `storeID` = X
)
where timezoneDate between '2014-11-19' and '2014-11-25'
ORDER BY timezoneDate

- JRD

---

无法按照拼写方式工作，因为WHERE需要表达式而不是别名。 - Rick James

什么？我不明白。select alias from (select 'text' as alias) q where alias = 'text'; 是完全有效的。 - JRD

Answer 3

不知道你的数据，但是你应该确定在transactions(storeId, isvalid, eventdate)上有一个索引。

如果这不能提高性能，那么你的选择就更少了。一种方法是为纽约定义一个“规范”的事件时间。这需要一个触发器来进行转换。

第二个选项类似，但它涉及将时间差作为单独的列保留。

我建议对常量进行时区转换，但两个时区应该相同。我不知道为什么它们会返回不同的结果。

- Gordon Linoff