子查询中使用MAX引发索引扫描问题求助

你遇到的核心问题是：单独查询单chat_id的最新消息时间时，MySQL能利用chat_id_created_at索引的有序性直接返回最大值（优化为"Select tables optimized away"），但把这个查询作为相关子查询时，MySQL会对每个chat_id单独执行一次查询——虽然每次都用了索引，但如果用户所属聊天数量多，或者部分聊天消息量极大，多次索引查找的开销会累加导致整体变慢。

为什么子查询没有被优化？ #

相关子查询依赖外层chat_users的每一行数据执行，MySQL在这里没有将多个子查询合并为一次批量聚合查询，而是逐个处理每个chat_id，所以无法像单独查询那样做到极致优化。

优化方案：使用JOIN+预聚合替代子查询 #

换用先聚合所有chat_id的最新消息时间，再和chat_users关联的方式，这样只需要扫描一次messages的索引就能完成所有chat的最大值计算，效率会显著提升：

SELECT 
    cu.chat_id,
    COALESCE(m.max_created_at, 0) AS latest_message_at, -- 处理无消息的聊天
    cu.active
FROM chat_users cu
LEFT JOIN (
    SELECT chat_id, MAX(created_at) AS max_created_at
    FROM messages
    GROUP BY chat_id
) m ON cu.chat_id = m.chat_id
WHERE cu.user_id = '???';

为什么这个方案更快？

• 子查询(SELECT chat_id, MAX(created_at) FROM messages GROUP BY chat_id)会利用chat_id_created_at索引的有序性，高效完成分组聚合：因为索引是按chat_id排序、同chat_id下按created_at DESC排序的，MySQL只需要顺序扫描索引，遇到新的chat_id就记录前一个的最大值，不需要扫描全表。
• 关联chat_users时，直接用用户所属的chat_id和预聚合结果匹配，避免了多次子查询的开销。

进阶优化：维护实时的聊天最新时间 #

如果你的系统中消息写入频繁，且这个查询的调用量很大，可以考虑新增一个chats表（或者在chat_users中扩展字段），专门存储每个chat的last_message_at：

-- 假设新增chats表
CREATE TABLE chats (
    chat_id VARCHAR(128) NOT NULL PRIMARY KEY,
    last_message_at BIGINT SIGNED NOT NULL DEFAULT 0,
    -- 其他聊天字段...
);

-- 写入消息时触发更新
DELIMITER //
CREATE TRIGGER update_chat_last_message
AFTER INSERT ON messages
FOR EACH ROW
BEGIN
    UPDATE chats 
    SET last_message_at = NEW.created_at 
    WHERE chat_id = NEW.chat_id;
END //
DELIMITER ;

之后查询就可以直接关联chats表，速度会更快：

SELECT 
    cu.chat_id,
    c.last_message_at,
    cu.active
FROM chat_users cu
JOIN chats c ON cu.chat_id = c.chat_id
WHERE cu.user_id = '???';

验证优化效果 #

执行EXPLAIN FORMAT=json查看新查询的执行计划，你会看到预聚合的子查询会使用chat_id_created_at索引，且是一次性扫描，而不是多次依赖查询。

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