MySQL：优化查询以获取日期范围内的记录

Question

MySQL：优化查询以获取日期范围内的记录

mysql

3

我有一张表（logs），它包含以下列（还有其他列，但这些是重要的）：

id（PK，int）
Timestamp（datetime）（索引）
Duration（int）

基本上，这是一个在某个时间开始并在某个时间结束的事件记录。该表目前有几十万行数据。我希望它能够增长到数百万行。为了加快查询速度，我添加了另一列并预先计算了值：

EndTime（datetime）（索引）

为了计算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 秒钟。我怀疑这已经是最快的速度了，但我想在这里问一下是否有人有任何提高此查询性能的想法。

任何建议都将非常有帮助。谢谢您的时间。

编辑：我在时间戳和结束时间上建立了索引。

查询 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"

当我在日志表上运行分析时，它显示状态为“OK”。

- Brad

你已经建立了什么索引？执行查询时的EXPLAIN输出是什么？你是否已经对logs表进行了分析？ - outis

@outis，感谢您的提问。我已将信息添加到我的帖子底部。 - Brad

分析更多地是关于它对查询优化器如何利用索引的影响。现在我很好奇你的查询的扩展查询计划是什么，特别是想看看查询是否被重写了。请在 EXPLAIN EXTENDED 查询后发布 SHOW WARNINGS 的结果。 - outis

2个回答

2

您可以查看合并表来加速处理。使用合并表，由于表被分割，索引较小，因此获取速度更快。此外，如果您有多个处理器，则可以并行搜索以提高性能。

- Rasika

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- outis · Accepted Answer

请注意，在EXPLAIN输出中，logs表的连接类型为“ALL”，键值为NULL，这意味着将进行全表扫描。 “Range checked for each record”消息表示MySQL在从结果中的其他位置检查列值后，对logs使用范围访问方法。我认为这意味着一旦创建了dates，MySQL可以使用第二个和第三个索引（可能是Timestamp和EndTime上的索引）对logs执行范围联接，而不是执行全表扫描。如果您仅在Timestamp和EndTime上拥有索引，请尝试在两者上添加索引，这可能会导致更有效的联接类型（例如index_merge而不是range）。

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

我相信（虽然可能错了），查询计划中的其他项目要么不是真正的问题，要么无法消除。例如，文件排序（filesort）很可能是由于GROUP BY引起的。换句话说，这可能是您可以在此特定查询中做的全部，尽管处理表存储格式的完全不同的查询或方法仍然可能更有效。