含50万行messages表的MySQL查询耗时超15秒，求优化方案

原查询耗时高核心原因 #

• 嵌套相关子查询逐行执行：外层每返回1条role_users符合条件的数据，就要对50万行的messages表执行2次独立查询，外层如果返回N条数据，总共要执行2*N次messages表扫描，IO开销极高
• 缺少必要的覆盖索引，关联过滤字段无索引时每次查询都会触发全表扫描
• 多余的DISTINCT关键字：如果role_users表的id是主键/唯一键，DISTINCT完全多余，反而会增加排序去重的额外开销

第一步：新增覆盖索引（必做） #

直接在对应表添加联合索引，让查询可以直接走索引无需回表读数据：

-- 覆盖最新消息时间查询逻辑，匹配OR条件两端的关联字段+排序字段
CREATE INDEX idx_messages_from_to_created ON messages(from_role_user_id, to_role_user_id, created_at DESC);
-- 覆盖未读消息计数查询逻辑，包含所有过滤条件字段
CREATE INDEX idx_messages_unread_count ON messages(from_role_user_id, to_role_user_id, is_read);
-- 覆盖外层角色用户表的过滤条件
CREATE INDEX idx_role_users_filter ON role_users(is_deleted, chat_status, user_name, role_id);
-- 覆盖角色表关联和名称过滤
CREATE INDEX idx_roles_name ON roles(name, id);

第二步：改写SQL逻辑，替换相关子查询为预聚合JOIN #

将原本逐行执行的子查询改为提前一次性聚合messages表数据，仅扫描2次messages表即可完成所有计算：

SELECT 
    ur.id, ur.user_name AS name, ur.online, ur.chat_status,
    ur.updated_at, ur.profile_image, ur.role_id,
    msg_agg.last_msg_at AS message_at,
    COALESCE(unread_agg.unread_count, 0) AS count,
    r.name AS role
FROM role_users ur
LEFT JOIN roles r ON r.id = ur.role_id
-- 预聚合所有用户的最新消息时间
LEFT JOIN (
    SELECT 
        user_id,
        MAX(created_at) AS last_msg_at
    FROM (
        SELECT from_role_user_id AS user_id, created_at FROM messages
        UNION ALL
        SELECT to_role_user_id AS user_id, created_at FROM messages
    ) t
    GROUP BY user_id
) msg_agg ON msg_agg.user_id = ur.id
-- 预聚合所有发给用户1的未读消息计数
LEFT JOIN (
    SELECT 
        from_role_user_id AS user_id,
        COUNT(*) AS unread_count
    FROM messages
    WHERE to_role_user_id = 1 AND is_read = 0
    GROUP BY from_role_user_id
) unread_agg ON unread_agg.user_id = ur.id
WHERE 
    ur.id != 1
    AND r.name IN ('superadmin', 'candidate', 'admin', 'manager', 'business_unit','client')
    AND ur.chat_status != 'offline'
    AND ur.is_deleted = 0
    AND ur.user_name IS NOT NULL
ORDER BY message_at DESC
LIMIT 10;

优化效果说明 #

优化后messages表的扫描次数从2*N次降低到固定2次，配合覆盖索引可以完全避免回表，查询耗时通常可以降到1秒以内，逻辑和原查询完全一致。

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