代码之家 › 专栏 › 技术社区 › Daniel McLaury

优化查询以获取整个行,其中一个字段是组的最大值

myisam greatest-n-per-group query-optimization mariadb

0

Daniel McLaury · 技术社区 · 6 年前

我有一个表,它的模式是,

EventTime   DATETIME(6),
EventType   VARCHAR(20),
Number1     INT,
Number2     INT,
Number3     INT,
...

这个表中有难以想象的大量行,但是为了这个查询,我只对其中的几千行感兴趣,比如说,它们在两个给定的 EventTime . 上面有索引如果我只是做些

SELECT * FROM table WHERE EventTime >= time1 and EventTime <= time2;

然后,它能够在几乎瞬间返回相关行。

在这个时间窗口的行中,我想精确地提取那些 Number1 是任何一排中最大的 EventType

SELECT * FROM
  (SELECT EventType, MAX(Number1) as max_Number1
   FROM table
   WHERE EventTime >= time1 AND EventTime <= time2
   GROUP BY EventType) AS a
  LEFT JOIN
  (SELECT * FROM table
   WHERE EventTime >= time1 AND EventTime <= time2) AS b
  ON a.EventType = b.EventType AND a.max_Number1 = b.Number1)

这看起来应该工作得很好——我可以运行每个子查询,即

SELECT EventType, MAX(Number1) as max_Number1
FROM table
WHERE EventTime >= time1 AND EventTime <= time2
GROUP BY EventType;

和

SELECT * FROM table
WHERE EventTime >= time1 AND EventTime <= time2;

事件类型 然后把东西搭配起来。

永远 . 我不知道要花多长时间,因为我从来没有让它完成过,但是手动拉取两个查询的结果并在其他地方进行合并所花的时间要比我所花的时间长。

为什么要花这么长时间?数据库引擎在做什么?
有没有一种方法来编写这样一个查询,使它能够合理地执行?
如果不是,我可以把它写成一个存储过程吗?

1 回复 | 直到 6 年前

1

Willem Renzema 6 年前

实际上,您已经非常接近一个好的查询。您的主要缺点可能是在从中选择all时使用左连接 table 在时间范围内。请尝试以下操作:

SELECT * FROM
table b
INNER JOIN (
    SELECT EventType, MAX(Number1) as max_Number1
    FROM table
    WHERE EventTime >= time1 AND EventTime <= time2
    GROUP BY EventType
) AS a
ON a.EventType = b.EventType
AND a.max_Number1 = b.Number1
WHERE b.EventTime >= time1 AND b.EventTime <= time2

(EventType,EventTime) . 请提供 SHOW CREATE TABLE table 在您的问题中,我们可以看到您当前有哪些索引。我们可以调整现有的索引,或者帮助您删除不需要的索引,以允许添加此新索引。