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

解释显示type: index，这是一个索引扫描.也就是说，它使用了索引，但它迭代了索引中的every个条目，就像表扫描对表中的行所做的那样.rows: 2861816支持这一点，它告诉您优化器对它将判断的索引项数量的估计(这是一个粗略的数字).这比只判断符合条件的行要昂贵得多，而这正是我们希望从索引中获得的好处.

那么为什么会这样呢？

在搜索中对索引列使用任何函数时，如下所示:

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

它 destruct 了索引减少判断行数的好处.

MySQL的优化器不知道函数的结果，因此它不能推断返回值的顺序与索引的顺序相同.因此，它不能利用索引已排序这一事实来缩小搜索范围.您和我都知道，DATE(created_at)与created_at的顺序相同是很自然的，但查询优化器并不知道这一点.还有像MONTH(created_at)这样的其他函数，在这些函数中，结果按排序顺序肯定是not，并且MySQL的优化器不会try 知道哪个函数的结果是可靠排序的.

要修复您的查询，可以try 以下两种方法之一:

使用表达式索引.这是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，则无论如何都应进行升级.MySQL5.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'会更简单.

对此的解释表明，它使用了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.