mysql - 非常相似的 MySQL 查询结果显着不同的查询持续时间(WHERE on timespans)

Question

我有一个 MySQL 表，其中包含大约 60 万行(引擎:InnoDB)。MySQL 在装有 Ubuntu 16.04 LTS 的 virtualbox 机器上运行。 MySQL 服务器版本是 5.7.23，如果相关的话。

WHERE 子句中的列(open_time 和close_time)都被索引并且它们都是 DATETIME 列。

我计算总和的列(体积)是 double 的。

此查询立即返回(0.000 秒):

SELECT *
FROM klines
WHERE (open_time between '2018-01-01 00:00:00' AND '2018-01-01 12:00:00')
;

解释输出:

而这个需要将近一秒的时间来获取(在 10 次尝试之间变化在 0.640 到 0.703 秒之间):

SELECT SUM(volume)
FROM klines
WHERE open_time >= '2018-01-01 00:00:00' AND close_time <= '2018-01-01 12:00:00'
;

解释输出:

请注意，这两个查询返回的行大致相同(第一个查询返回 720 行，第二个查询返回 721 行。第二个查询返回与第一个查询返回的相同的 720 行，再加上另一个)。

因此，如果我只想获取行，则对两列或一列使用 WHERE 子句都没有关系。但是，如果我想获取列的 SUM，当我对两列使用 WHERE 子句时，查询速度会大大降低。但是，如果我使用单个列，它会再次立即返回。

虽然我完全可以使用在两个 open_time 标准之间查询表的查询，但我真的很好奇发生了什么。

那么，这背后的原因是什么？

Answer 1

open_time between '2018-01-01 00:00:00'
              AND '2018-01-01 12:00:00'

可以很容易地使用 INDEX(open_time) 来仅触摸感兴趣的行。但是不可能有一个突然停止的索引:

     open_time >= '2018-01-01 00:00:00'
AND close_time <= '2018-01-01 12:00:00'

INDEX(open_time) 可以使用，但表的后半部分将被扫描。 INDEX(close_time) 同样会扫描表的前半部分。现在有办法做到这两点。

您可能有一个无处可见的额外约束:

• [open..close] 时间范围不重叠？
• 打开总是<关闭?

这些不能在标准 SQL 中指定，也没有任何索引公式可以利用这两个约束。

这里有两行会搞乱任何优化尝试:

INSERT INTO klines (open_time,             close_time)
            VALUES ('2018-01-01 06:00:00', '2037-12-31'),
                   ('1971-01-01',          '2018-01-01 06:00:00')
                   ('2037-01-01',          '1971-01-01')

有修复，但它们需要假设不重叠，然后使用查询是严格的方法；或玩水桶。