为什么这个查询需要超过5秒才能运行？

Question

为什么这个查询需要超过5秒才能运行？

4

我有一个包含约2百万行的MySQL表。我试图运行以下查询每次需要超过5秒才能得到结果。我在created_at列上建立了索引。以下是EXPLAIN的输出。

这是预期的吗？

提前感谢。

SELECT
  DATE(created_at) AS grouped_date,
  HOUR(created_at) AS grouped_hour,
  count(*) AS requests
FROM
  `advert_requests`
WHERE
  DATE(created_at) BETWEEN '2022-09-09' AND '2022-09-12'
GROUP BY
  grouped_date,
  grouped_hour

- James Mills

1

有280万行符合WHERE条件。将其拆分为日期和小时进行分组是昂贵的。 - Barmar

1

如果在created_at上有索引，DATE函数将不使用它。 - Ergest Basha

@ErgestBasha 解释输出显示它正在使用该键。 - Barmar

尝试将 DATE(created_at) BETWEEN '2022-09-09' AND '2022-09-12' 替换为 created_at BETWEEN '2022-09-08 23:59:59' AND '2022-09-12 23:59:59'，并检查是否会产生差异。 - Ergest Basha

3个回答

0

如果我正确理解了EXPLAIN，它能够使用索引来实现WHERE过滤。但是这会返回280万行数据，然后需要按日期和小时进行分组，这是一个缓慢的过程。

您可以通过为日期和小时创建虚拟列，并对其进行索引来改善它。

ALTER TABLE advert_requests
ADD COLUMN created_date DATE AS (DATE(created_at)), ADD column created_hour INT AS (HOUR(created_at)), ADD INDEX (created_date, created_hour);

- Barmar

它缺少数据类型，我认为虚拟列的定义应该用一对括号括起来。 - blabla_bingo

0

使用 explain analysis 并检查是否为 Index range scan。如果不是，请跟随此链接： https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html （请注意，如果表中的大部分时间戳属于所选日期范围，则有时完整表扫描可能更好。我知道在这种情况下优化并不简单）

- YJR

它将具有更好的性能，但它只会匹配发生在00:00:00时间的“created_at”值。因此，它将无法包括发生在不同时间的所有其他条目。 - Bill Karwin

同意你的观点。 - YJR

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

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

- Bill Karwin · Accepted Answer

EXPLAIN 显示的是 type: index，这是一种索引扫描。也就是说，它正在使用索引，但是像表扫描一样迭代索引中的每个条目，而不仅仅是与条件匹配的行。这得到了 rows: 2861816 的支持，它告诉您优化器估计将要检查的索引条目数量（这是一个粗略的数字）。这比仅检查与条件匹配的行要昂贵得多，而这也是我们从索引中寻求的好处。

那么为什么会这样呢？

当您在搜索中使用索引列上的任何函数时，就会出现这种情况：

WHERE
  DATE(created_at) BETWEEN '2022-09-09' AND '2022-09-12'

这破坏了索引减少检查行数的好处。

MySQL 的优化器对函数结果没有任何智能提示，因此无法推断返回值的顺序是否与索引相同。因此，它不能利用索引排序来缩小搜索范围。你和我都知道 DATE(created_at) 应该与created_at 相同顺序，但查询优化器不知道这一点。还有其他像 MONTH(created_at) 这样的函数，其结果明显不是按排序顺序的，MySQL 优化器不尝试知道哪个函数的结果可靠地排序。

要修复查询，可以尝试以下两种方法之一：

使用表达式索引。这是 MySQL 8.0 中的新特性：

ALTER TABLE `advert_requests` ADD INDEX ((DATE(created_at)))

注意多余的一对括号。定义表达式索引时需要这些括号。索引条目是该函数或表达式的结果，而不是列的原始值。

如果在查询中使用同样的表达式，则优化器会识别并使用该索引。

mysql> explain SELECT   DATE(created_at) AS grouped_date,   HOUR(created_at) AS grouped_hour,   count(*) AS requests FROM   `advert_requests` WHERE   DATE(created_at) BETWEEN '2022-09-09' AND '2022-09-12' GROUP BY   grouped_date,   grouped_hour\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: advert_requests
   partitions: NULL
         type: range          <-- much better than 'index'
possible_keys: functional_index
          key: functional_index
      key_len: 4
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using temporary

如果您使用的是MySQL 5.7版本，则无法直接使用表达式索引，但是您可以使用虚拟列并在虚拟列上定义索引：

ALTER TABLE advert_requests
  ADD COLUMN created_at_date DATE AS (DATE(created_at)),
  ADD INDEX (created_at_date);

优化器识别表达式的技巧仍然有效。

如果您使用的MySQL版本早于5.7，无论如何都应该升级。MySQL 5.6及更早版本已经过了其生命周期，是安全风险。

第二件事是重构查询，使created_at列不在函数内部。

WHERE
  created_at >= '2022-09-09' AND created_at < '2022-09-13'

当将日期时间与日期值进行比较时，日期值默认为00:00:00.000时间。要包含每一秒的时间戳，直到2022-09-12 23:59:59.999，更简单的方法是只使用< '2022-09-13'。

这个语句的EXPLAIN显示它使用了现有的created_at索引。

mysql> explain SELECT   DATE(created_at) AS grouped_date,   HOUR(created_at) AS grouped_hour,   count(*) AS requests FROM   `advert_requests` WHERE   created_at >= '2022-09-09' AND created_at < '2022-09-13' GROUP BY   grouped_date,   grouped_hour\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: advert_requests
   partitions: NULL
         type: range        <-- not 'index'
possible_keys: created_at
          key: created_at
      key_len: 6
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition; Using temporary

这个解决方法适用于旧版本的MySQL以及5.7和8.0版本。