请求优化MySQL查询：获取用户所有好友及双方最后一条消息

我来帮你完善这个查询，添加双方之间的最后一条消息。这里有两种可行的方案，你可以根据自己的数据库环境和性能需求选择：

方案一：关联子查询（简洁直观） #

这种写法逻辑清晰，适合大多数中小数据量的场景：

SELECT 
    os_friends.*, 
    os_users.username, 
    os_users.user_id AS friend_id,
    -- 子查询获取当前用户与好友的最后一条消息
    (
        SELECT message
        FROM os_messages
        WHERE 
            -- 匹配双向消息（不管是用户发给好友还是好友发给用户）
            (user_id = 24 AND target_id = os_users.user_id) 
            OR (user_id = os_users.user_id AND target_id = 24)
        ORDER BY time DESC
        LIMIT 1 -- 只取最新的一条
    ) AS lastmessage
FROM os_friends 
LEFT JOIN os_users 
    -- 优化关联条件，明确匹配好友ID（排除当前用户24）
    ON (os_friends.user_id = os_users.user_id AND os_users.user_id != 24) 
    OR (os_friends.target_id = os_users.user_id AND os_users.user_id != 24)
WHERE 
    -- 筛选当前用户的好友关系
    (os_friends.user_id = 24 OR os_friends.target_id = 24)
ORDER BY os_friends.lastaction DESC

• 子查询会针对每个好友，查找用户24和该好友之间的所有消息，按时间倒序后取第一条作为lastmessage；
• 如果双方没有任何消息，lastmessage会返回NULL，符合预期；
• 优化了原关联条件，避免不必要的匹配，让逻辑更清晰。

方案二：CTE+窗口函数（性能更优） #

如果你的消息表数据量较大，这种方式只需要扫描一次消息表，性能会更好：

WITH ranked_messages AS (
    SELECT 
        message,
        user_id,
        target_id,
        -- 为每一组双向对话的消息按时间倒序排名
        ROW_NUMBER() OVER (
            PARTITION BY 
                -- 用用户ID的组合作为对话唯一标识（统一双向对话的分组）
                LEAST(user_id, target_id), 
                GREATEST(user_id, target_id)
            ORDER BY time DESC
        ) AS rn
    FROM os_messages
)
SELECT 
    os_friends.*, 
    os_users.username, 
    os_users.user_id AS friend_id,
    rm.message AS lastmessage
FROM os_friends 
LEFT JOIN os_users 
    ON (os_friends.user_id = os_users.user_id AND os_users.user_id != 24) 
    OR (os_friends.target_id = os_users.user_id AND os_users.user_id != 24)
-- 关联预处理好的最新消息
LEFT JOIN ranked_messages rm
    ON (rm.user_id = 24 AND rm.target_id = os_users.user_id) 
    OR (rm.user_id = os_users.user_id AND rm.target_id = 24)
    AND rm.rn = 1 -- 只取每组对话的第一条（最新）消息
WHERE 
    (os_friends.user_id = 24 OR os_friends.target_id = 24)
ORDER BY os_friends.lastaction DESC

• 先用CTEranked_messages给所有消息按对话分组（LEAST和GREATEST确保双向对话被归为同一组），然后按时间倒序排名，rn=1就是每组的最新消息；
• 主查询通过用户24和好友ID关联这个CTE，直接获取最新消息，避免了多次扫描消息表。

注意事项 #

• 建议给os_messages表的time字段添加索引，这样排序和查找最新消息的速度会大幅提升；
• 如果存在同一时间多条消息的情况，LIMIT 1或rn=1会随机返回其中一条，若需要特定规则（比如按消息ID倒序），可以在ORDER BY里添加message_id DESC（如果你的表有这个字段）。

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