时间戳

首页 > 解决方案 > 非常相似的 MySQL 查询导致查询持续时间显着不同（在时间跨度上的位置）

mysql - 非常相似的 MySQL 查询导致查询持续时间显着不同（在时间跨度上的位置）

问题描述

我有一个 MySQL 表，其中包含大约 600 K 行（引擎：InnoDB）。MySQL 在装有 Ubuntu 16.04 LTS 的虚拟机上运行。MySQL 服务器版本是 5.7.23，如果相关的话。

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

我取（体积）总和的列是双倍的。

此查询立即返回（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 子句并不重要。但是，如果我想获得一列的总和，当我对两列使用 WHERE 子句时，查询会变得非常慢。但是，如果我使用单列，它会立即再次返回。

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

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

标签： mysqlperformance

解决方案

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')

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

推荐阅读