MariaDB GROUP BY查询过慢求助：百万级消息表分组优化

我需要从包含200多万行数据的msg表中，获取指定用户（msg_to=23）的最新消息，并按parent（会话线程ID）分组。但GROUP BY操作导致查询耗时约1秒，比不使用GROUP BY时慢1000倍。

表结构 #

CREATE TABLE `msg` (
`msg_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`msg_to` int(10) unsigned NOT NULL,
`msg_from` int(10) unsigned NOT NULL,
`msg` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
`date` timestamp NOT NULL DEFAULT current_timestamp(),
`parent` int(10) unsigned NOT NULL,
 PRIMARY KEY (`msg_id`),
 KEY `msg_toIX` (`msg_to`) USING BTREE,
 KEY `msg_fromIX` (`msg_from`) USING BTREE,
 KEY `parentIX` (`parent`) USING BTREE ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

当前查询语句 #

SELECT a.msg_id, a.msg_from, a.msg FROM msg a 
JOIN(SELECT MAX(msg_id) maxid FROM msg WHERE msg_to = 23 GROUP BY parent ORDER BY msg_id DESC LIMIT 10) b
ON a.msg_id IN (b.maxid)
ORDER BY a.msg_id DESC LIMIT 10

已查看执行计划，想咨询此查询是否已达最优性能？或是我的实现方式有误？毕竟无GROUP BY时，带条件提取1万行仅需0.001秒。

更新：感谢各位贡献，复合索引是解决问题的关键。

核心优化方案：添加复合索引 #

问题根源在于现有索引无法高效支撑msg_to过滤+parent分组+msg_id取最大值的操作。单独的msg_toIX、parentIX索引，在执行GROUP BY时需要做大量数据聚合计算，导致性能骤降。

创建覆盖查询需求的复合索引：

CREATE INDEX idx_msg_to_parent_msgid ON msg (msg_to, parent, msg_id);

索引生效逻辑

• msg_to作为首列：快速过滤出指定用户的所有消息，避免全表扫描。
• parent作为次列：将同一会话线程的消息聚合在一起，GROUP BY操作无需额外排序或聚合，直接按分组读取数据。
• msg_id作为第三列：因为msg_id是自增主键，同一分组内最大的msg_id就是最新消息，索引中直接包含该字段，无需回表查询，实现覆盖索引效果，进一步提升速度。

优化后的查询语句（可选微调） #

原查询子查询中的ORDER BY msg_id DESC LIMIT 10属于冗余操作，因为我们只需要每个分组的最大msg_id，优化后语句：

SELECT a.msg_id, a.msg_from, a.msg 
FROM msg a 
JOIN (
    SELECT MAX(msg_id) maxid 
    FROM msg 
    WHERE msg_to = 23 
    GROUP BY parent
) b ON a.msg_id = b.maxid
ORDER BY a.msg_id DESC 
LIMIT 10;

效果验证 #

添加复合索引后，执行计划会显示使用新创建的索引，GROUP BY操作的耗时会大幅降低，性能接近无GROUP BY时的查询速度。

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