datetime - 在 MariaDB 中，如何选择每天最后一个事件并为其创建索引？

Question

我有一个会议表，每个会议都有一个开始和结束时间，表示为一个整数 Unix 时间戳，以及参加这次会议的一群人。一旦他们当天的所有会议结束，我需要向每一组人发送通知。为此，我需要找到（天，组）的每个组合的最后一次会议。我已经根据我的情况调整了这个答案，它有效：

SELECT MAX(`starts_at`), `id`, `group_id`, DATE(FROM_UNIXTIME(`starts_at`)) `day`
FROM `meeting`
GROUP BY `day`, `group_id`;

但是，尽管尝试了不同的索引组合，但我似乎找不到一个可以使该查询不执行全表扫描的组合。结果EXPLAIN总是如下：

+------+-------------+---------+------+---------------+------+---------+------+------+---------------------------------+
| id   | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+------+-------------+---------+------+---------------+------+---------+------+------+---------------------------------+
|    1 | SIMPLE      | meeting | ALL  | NULL          | NULL | NULL    | NULL |   16 | Using temporary; Using filesort |
+------+-------------+---------+------+---------------+------+---------+------+------+---------------------------------+

我的表是这样定义的：

CREATE TABLE `meeting` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL,
  `starts_at` int(11) NOT NULL,
  `ends_at` int(11) NOT NULL,
  ... other fields ..., 
  PRIMARY KEY (`id`),
  CONSTRAINT `meeting_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `group` (`id`),
)

我在这里需要什么索引组合和什么查询？我看到的一个解决方案是创建一个索引列来存储事件的日期，可能是某种序数，虽然这是一个选项，但我希望尽可能避免它，以免在单个中包含冗余值排。

标签： datetimemariadbquery-optimizationgreatest-n-per-groupmariadb-10.3

Answer 1

如果您正在运行 MySQL 8.0.13 或更高版本，您可以尝试对表达式进行索引：

create index idx_meeting on meeting(
    group_id, 
    (date(from_unixtime(starts_at))), 
    starts_at desc
);

这首先放置子句中出现的列/表达式group by，然后是聚合的列；由于我们将要查找 ，因此max()我们希望在索引中按降序对该列进行排序。

在这个 DB Fiddle中，看起来索引是由数据库拾取的（但请注意，我没有可使用的数据 - 结果在您的环境中可能会有所不同：

ID	选择类型	桌子	分区	类型	可能的键	钥匙	key_len	参考	行	过滤	额外的
1	简单的	会议	空值	指数	idx_meeting	idx_meeting	12	空值	1	100.00	使用索引；使用临时

---

另一个选择是稍微简化查询。我们可以算术而不是时间戳转换。如果您的版本不支持表达式索引，我们可以轻松地为此添加索引 - 或者使用计算列：

alter table meeting 
    add starts_date date as (floor(starts_at / 60 / 60 / 24))
    stored;

create index idx_meeting2 on meeting(group_id, starts_date, starts_at desc);

然后我们按如下方式运行查询：

SELECT MAX(starts_at), group_id, DATE(MAX(starts_at)) day
FROM meeting
GROUP BY group_id, starts_day;

在 Maria DB 10.3 中解释：

ID	选择类型	桌子	类型	可能的键	钥匙	key_len	参考	行	额外的
1	简单的	会议	指数	空值	idx_meeting2	12 空	1	使用索引

---

编辑：您想要每组和每天的最新事件。聚合不合适。相反，我们需要过滤。

考虑以下设置：

alter table meeting 
    add starts_day int(11) as (floor(starts_at / 60 / 60 / 24) * 60 * 60 * 24)
    stored;
create index idx_meeting on meeting(group_id, starts_day, starts_at desc);

现在我们可以使用以下查询：

select starts_at, group_id, date(from_unixtime(starts_day)) day
from meeting m
where starts_at = (
    select max(m1.starts_at)
    from meeting m1
    where m1.group_id = m.group_id and m1.starts_day = m.starts_day
)

子查询利用索引，该索引也可以部分用于外部查询。

DB Fiddle 上的演示