如何优化MySQL中LiveMovie表的指定分组COUNT查询速度？

问题根因分析 #

该查询的本质是统计指定roomID下的去重pitchID总数，现有索引和查询写法存在明显缺陷：

• 用到的live_roomID_time索引仅包含roomID、start_time两个字段，定位到符合roomID=?的记录后，需要回表读取pitchID字段，产生额外IO开销
• GROUP BY pitchID操作需要创建临时表、对pitchID排序，对应执行计划里的Using temporary; Using filesort，数据量较大时耗时大幅上升

优化方案 #

方案1：新增覆盖联合索引（性价比最高，优先选择） #

创建联合索引(roomID, pitchID)，建索引语句：

CREATE INDEX live_roomID_pitchID ON LiveMovie (roomID, pitchID);

同时可以把原查询简化为更高效的写法，不需要嵌套子查询：

SELECT COUNT(DISTINCT pitchID) FROM LiveMovie WHERE roomID = ?;

优化原理：

• 索引前缀roomID可以快速过滤出所有符合条件的记录
• 索引已经包含需要的pitchID字段，不需要回表查询，属于覆盖索引
• 同一roomID下的pitchID在索引中是有序存储的，GROUP BY/DISTINCT操作不需要额外排序和临时表，执行效率可以提升几十倍，500万数据下耗时通常可以降到毫秒级

方案2：预聚合表优化（适合高频查询场景） #

如果该查询是业务高频访问接口，可以新增一张预聚合统计表存储结果：

CREATE TABLE RoomPitchCount (
    roomID INT PRIMARY KEY,
    pitch_count INT NOT NULL DEFAULT 0
);

后续可以通过以下方式维护数据：

• 业务侧新增/删除LiveMovie记录时同步更新对应roomID的计数
• 或者通过数据库触发器自动更新计数
• 或者用定时任务每天低峰期全量校准一次数据
  查询时直接访问该表，响应速度可以稳定在1ms以内。

方案3：分库分表（适合后续数据持续增长的场景） #

如果后续数据量继续上涨到千万甚至亿级别，可以按roomID做哈希分表，将不同room的记录分散到不同分片，进一步分散查询压力。

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