如何高效查询指定用户与指定对象的各对话最新消息

我知道这类问题有先例，但需求有差异：需要从消息表中查询指定用户作为发送方或接收方时，与指定对象的每一组对话的最新消息。消息已加密，且sender和recipient均为哈希值而非真实用户ID。

消息表示例 #

期望结果（以用户abc为例） #

尝试过的无效SQL语句 #

语句1：

select messages.* 
from 
    messages
  join 
    (select sender, max(created_at) messages  
        from 
           (
             (select id, sender, recipient, created_at 
               from messages 
               where user=? ) 
           union 
             (select id, sender, recipient, created_at
               from messages
               where recipient IN (?,?,?))
            ) t1
       group by user) t2
 on ((sender=? and recipient=?) or 
     (sender=? and recipient=?)) and 
     (created_at = m)
order by created_at desc

语句2：

SELECT msg, iv FROM messages 
WHERE (sender=? AND reciepient IN (?,?,?,?)) 
OR (sender IN (?,?,?,?) AND reciepient=?) ORDER BY id desc LIMIT 1

正确的SQL查询写法 #

核心思路是先为指定用户和每个对话对象的组合找到最新的created_at时间，再关联原表获取完整消息数据，同时通过WHERE子句限定指定对象，避免全表扫描。

通用版本（支持多数关系型数据库）

假设要查询的指定用户哈希值为?，指定对话对象的哈希值列表为?, ?, ?（可根据实际数量调整）：

SELECT m.*
FROM messages m
JOIN (
    -- 先获取每个对话的最新时间戳
    SELECT 
        -- 确定对话的另一方：如果当前用户是sender，则另一方是recipient，反之亦然
        CASE 
            WHEN sender = ? THEN recipient 
            ELSE sender 
        END AS chat_partner,
        MAX(created_at) AS latest_time
    FROM messages
    -- 限定当前用户是发送方或接收方，且对话对象在指定列表中
    WHERE 
        (sender = ? AND recipient IN (?, ?, ?))
        OR (recipient = ? AND sender IN (?, ?, ?))
    GROUP BY chat_partner
) t ON (
    -- 匹配对话双方和最新时间
    ((m.sender = ? AND m.recipient = t.chat_partner) OR (m.recipient = ? AND m.sender = t.chat_partner))
    AND m.created_at = t.latest_time
)
ORDER BY m.created_at DESC;

窗口函数优化版本（MySQL 8.0+/PostgreSQL/SQL Server等）

如果你的数据库支持窗口函数，可以用更简洁高效的写法：

SELECT id, sender, recipient, message, created_at
FROM (
    SELECT 
        *,
        -- 按对话分组，取每组最新的一条
        ROW_NUMBER() OVER (
            PARTITION BY 
                CASE WHEN sender = ? THEN recipient ELSE sender END
            ORDER BY created_at DESC
        ) AS rn
    FROM messages
    WHERE 
        (sender = ? AND recipient IN (?, ?, ?))
        OR (recipient = ? AND sender IN (?, ?, ?))
) t
WHERE rn = 1
ORDER BY created_at DESC;

关键说明 #

• 两种写法都通过WHERE子句直接限定了指定用户和对话对象，避免全表扫描，保证性能
• 使用CASE语句统一识别对话的另一方，确保同一组双向对话（比如abc和bnn的消息）被归为一组
• 窗口函数版本逻辑更清晰，在支持的数据库中性能更优

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