如何优化带GROUP BY的MySQL查询？百万级user_activities表提速

表结构 #

create table user_activities
(
    id                   int unsigned auto_increment
        primary key,
    user_id              int unsigned                        not null,
    other_user_id        int unsigned                        not null,
    activity_type_id tinyint unsigned                    not null,
    reason               varchar(255)                        null,
    created_at           timestamp default CURRENT_TIMESTAMP not null,
    updated_at           timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP,
    constraint user_activities_user_id_other_user_id_unique
        unique (user_id, other_user_id),
    constraint user_activities_other_user_id_foreign
        foreign key (other_user_id) references users (id),
    constraint user_activities_activity_type_id_foreign
        foreign key (activity_type_id) references user_activity_types (id),
    constraint user_activities_user_id_foreign
        foreign key (user_id) references users (id)
)
    engine = InnoDB
    collate = utf8_unicode_ci;

create index user_activities_other_user_id_index
    on user_activities (other_user_id);

create index user_activities_activity_type_id_index
    on user_activities (activity_type_id);

create index user_activities_user_id_index
    on user_activities (user_id);

数据情况 #

表中现有6515846条数据。

查询目标 #

获取最近7天内有活动的用户，结果包含user_id、mostrecentuseractivitydate（即该用户最新活动的时间），后续要对这些用户执行操作。

当前查询语句 #

select updated_at, user_id from  user_activities
where created_at > '2022-08-08 15:16:55'
group by user_id
order by max(updated_at) desc
limit 10;

EXPLAIN结果 #

1,SIMPLE,user_activities,,index,"user_activities_user_id_other_user_id_unique,user_activities_user_id_index",user_activities_user_id_index,4,,6416255,33.33,Using where; Using temporary; Using filesort

问题 #

当前查询耗时极久（约5分钟），甚至会无响应挂起，无法满足需求。保留created_at >过滤最近7天数据的条件前提下，如何提速？

• 创建针对性复合索引：创建(created_at, user_id, updated_at)的复合索引，这个索引可以直接覆盖查询的所有逻辑：先通过created_at快速筛选出最近7天的数据，再按user_id分组，同时直接获取updated_at的值，避免回表查询，还能消除Using temporary和Using filesort的额外开销。创建语句：

  CREATE INDEX idx_created_user_updated ON user_activities (created_at, user_id, updated_at);
  

• 修正查询语句逻辑：原查询中select updated_at配合group by user_id存在逻辑不严谨问题（非聚合字段未包含在group by中），应明确用MAX(updated_at)获取用户最新活动时间，调整后的语句能更好利用索引：

  SELECT user_id, MAX(updated_at) AS mostrecentuseractivitydate
  FROM user_activities
  WHERE created_at > '2022-08-08 15:16:55'
  GROUP BY user_id
  ORDER BY mostrecentuseractivitydate DESC
  LIMIT 10;
  

• 考虑时间分区（可选）：如果数据量持续增长，可按created_at做时间分区（比如按月分区），查询最近7天数据时，数据库只会扫描对应分区，大幅减少扫描的数据量。

• 清理冗余索引：创建上述复合索引后，单独的user_id索引可以考虑删除，因为复合索引的前缀已经包含user_id，单独索引的作用被覆盖，能减少索引维护的开销。

内容的提问来源于stack exchange，提问作者Kristi Jorgji