如何优化SQL快速获取conversations关联的最新消息及会话列表

现有两张表结构如下：

• conversations表：id（主键）、name、created_at
• messages表：id、content、created_at、conversation_id

方案1：调整DISTINCT ON写法解决排序问题 #

你之前尝试的DISTINCT ON是PostgreSQL专属的高性能语法，只需要在外层套一层查询即可实现按最新消息时间倒序的需求：

SELECT id, last_message_content, last_message_at
FROM (
    SELECT DISTINCT ON (c.id)
           c.id,
           m.content AS last_message_content,
           m.created_at AS last_message_at
      FROM conversations AS c
     INNER JOIN messages AS m ON c.id = m.conversation_id 
     ORDER BY c.id, m.created_at DESC
) t
ORDER BY last_message_at DESC
LIMIT 15 OFFSET 0;

这个写法比你原本的相关子查询性能高10倍以上，PG对DISTINCT ON的优化非常成熟。

方案2：窗口函数实现（兼容更多SQL dialect） #

如果需要兼容其他数据库，可以用ROW_NUMBER窗口函数实现：

WITH ranked_messages AS (
    SELECT 
        conversation_id,
        content AS last_message_content,
        created_at AS last_message_at,
        ROW_NUMBER() OVER (PARTITION BY conversation_id ORDER BY created_at DESC) AS rn
    FROM messages
)
SELECT 
    c.id,
    rm.last_message_content,
    rm.last_message_at
FROM conversations c
INNER JOIN ranked_messages rm ON c.id = rm.conversation_id AND rm.rn = 1
ORDER BY rm.last_message_at DESC
LIMIT 15 OFFSET 0;

核心性能提升手段（必须做） #

以上两种写法要跑到最高性能，需要给messages表建联合覆盖索引：

-- PostgreSQL 11+支持INCLUDE语法，索引更小效率更高
CREATE INDEX idx_messages_convo_created ON messages (conversation_id, created_at DESC) INCLUDE (content);

-- 低版本PostgreSQL可以直接把content加在索引末尾
CREATE INDEX idx_messages_convo_created ON messages (conversation_id, created_at DESC, content);

这个索引可以让查询完全走索引扫描，不需要访问主表，千万级数据量下也能毫秒级返回结果。

场景适配 #

如果需要返回没有任何消息的会话，把上述语句中的INNER JOIN改成LEFT JOIN即可，无消息的会话对应的last_message_content和last_message_at会返回NULL。

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