mysql - 如何在仍然使用索引的同时按 MySQL 中的日期/时间段对结果进行分组？

Question

在 MySQL 中，您可以创建要在查询中使用的索引，以防止全表扫描。只能使用一个索引。

此外，为了使用索引，索引的字段不能通过函数运行(即 DATE()、MONTH()、YEAR())，因为这样查询优化器不知道结果会是什么，所以不能使用索引，而是回退到完整(或部分)表扫描。

假设您想要运行一个按日/月/季度/年(GROUP BY date(created_at))对条目进行分组的报告，您如何设计一个查询来执行此操作，同时仍然使用索引？

示例表:

CREATE TABLE `datesort` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `value` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `c_v` (`created_at`,`value`)
) ENGINE=InnoDB;

-- Problem Query
EXPLAIN SELECT COUNT(*), `value`, created_at
FROM datesort
WHERE created_at > NOW() - INTERVAL 1 DAY
GROUP BY date(created_at), value;

-- Using where; Using index; Using temporary; Using filesort

vs

EXPLAIN SELECT COUNT(*), `value`, created_at
FROM datesort
WHERE created_at > NOW() - INTERVAL 1 DAY
GROUP BY created_at, value;

-- Using where; Using index 
-- (notice no DATE() in GROUP BY)

注意第一个查询必须导致部分表扫描(Using temporary; Using filesort)，因为它不能使用 c_v 索引，因为 日期(创建时间)。

第二个查询不按日期排序(它按秒排序)但可以单独使用索引而不会导致读取记录数据。

由于按时间段分组在报告中很常见，我如何仅使用索引按日/月/季度/年对记录进行分组？

Answer 1

扩展 WOUNDEDStevenJones 的有用评论和 Rick James : 您可以创建一个生成的列来存储每条记录的日期部分(不包括时间部分)并为其编制索引。

alter table datesort
    add column date_created_at date
    generated always as (date(created_at)) stored
;

create index myidx on datesort(date_created_at, value);

现在您可以再次尝试您的查询。为了获得索引的全部好处，理想情况下您需要更改 where 子句，以便它使用生成的日期列而不是原始日期时间列(希望这仍然适合您的用例):

select count(*) cnt, value,  date_created_at
from datesort
where date_created_at > current_date - interval 1 day
group by date_created_at, value;

这会产生预期的 explain:

id | select_type | table    | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                   -: | :---------- | :------- | :--------- | :---- | :------------ | :---- | :------ | :--- | ---: | -------: | :----------------------- 1 | SIMPLE      | datesort | null       | index | myidx         | myidx | 8       | null |    1 |   100.00 | Using where; Using index

Demo on DB Fiddle