MySQL:优化日期范围内记录的查询

Question

我有一个表 (logs)，其中包含以下列(还有其他列，但这些是重要的列):

• id(PK，整数)
• 时间戳(日期时间)(索引)
• 持续时间(整数)

基本上这是一次开始一次结束的事件的记录。该表目前有几十万行。我预计它会增长到数百万。为了加快查询速度，我添加了另一列和预先计算的值:

• 结束时间(日期时间)(索引)

为了计算 EndTime，我将 Duration 中的秒数添加到 Timestamp 字段。

现在我想做的是运行一个查询，结果计算开始时间 (Timestamp) 和结束时间 (EndTime) 超出的行数某个时间点。然后我想在一个大的时间跨度(比如一年)内每秒运行一次这个查询。我还想计算从特定时间点开始到特定时间点结束的行数。

我创建了以下查询:

SELECT 
    `dates`.`date`, 
    COUNT(*) AS `total`, 
    SUM(IF(`dates`.`date`=`logs`.`Timestamp`, 1, 0)) AS `new`,
    SUM(IF(`dates`.`date`=`logs`.`EndTime`, 1, 0)) AS `dropped` 
FROM 
    `logs`,
    (SELECT 
        DATE_ADD("2010-04-13 09:45:00", INTERVAL `number` SECOND) AS `date` 
        FROM numbers LIMIT 120) AS dates
WHERE dates.`date` BETWEEN `logs`.`Timestamp` AND `logs`.`EndTime` 
GROUP BY `dates`.`date`;

请注意，数字表严格用于轻松枚举日期范围。它是一个只有一列 number 的表格，包含值 1、2、3、4、5 等...

这正是我要找的东西......一个有 4 列的表格:

• 日期
• 总计(在当前时间点之外开始和结束的总行数)
• 新(从这个时间点开始的行)
• 丢弃(在这个时间点结束的行)

麻烦的是，这个查询可能需要很长时间才能执行。要经过 120 秒(如查询中所示)，大约需要 10 秒。我怀疑这和我将要获得的速度一样快，但我想我会在这里询问是否有人对提高此查询的性能有任何想法。

任何建议都会很有帮助。感谢您的宝贵时间。

编辑:我在 Timestamp 和 EndTime 上有索引。

EXPLAIN 对我的查询的输出:

"id";"select_type";"table";"type";"possible_keys";"key";"key_len";"ref";"rows";"Extra"
"1";"PRIMARY";"<derived2>";"ALL";NULL;NULL;NULL;NULL;"120";"Using temporary; Using filesort"
"1";"PRIMARY";"logs";"ALL";"Timestamp,EndTime";NULL;NULL;NULL;"296159";"Range checked for each record (index map: 0x6)"
"2";"DERIVED";"numbers";"index";NULL;"PRIMARY";"4";NULL;"35546940";"Using index"

当我在我的日志表上运行分析时，它显示状态正常。

Answer 1

注释在EXPLAIN输出 logs 的连接类型table 为“ALL”且 key 为 NULL，这意味着计划进行全表扫描。 “为每条记录检查范围”消息意味着 MySQL 使用 range access method在 logs在检查结果中其他地方的列值之后。我认为这意味着一旦dates已创建，MySQL 可以对 logs 执行范围连接使用第二个和第三个索引(可能是 Timestamp 和 EndTime 上的索引)而不是执行全表扫描。如果您只有 Timestamp 上的索引和 EndTime分别尝试在两者上添加索引，这可能会导致更有效的连接类型(例如 index_merge 而不是 range ):

CREATE INDEX `start_end` ON `logs` (`Timestamp`, `EndTime`);

我相信(尽管很可能是错误的)查询计划中的其他项目要么不是真正的问题，要么无法消除。 filesort，作为后者的一个例子，可能是由于 GROUP BY .换句话说，这可能是您可以使用此特定查询执行的操作的范围，尽管解决表存储格式的完全不同的查询或方法仍然可能更有效。