优化400万+行MariaDB聊天会话查询性能

问题背景 #

开发聊天应用，基于MariaDB的chatmessages表（400万+数据）实现类似iMessage/Messenger/WhatsApp的最近会话功能：获取每个访客的最后一条消息及关联信息。当前两种实现的性能未达预期：

• 窗口函数（PARTITION BY OVER）方案：耗时1.5秒
• 分组关联方案：耗时1.7秒
  目标将查询耗时优化至0.2秒以内。

现有实现性能瓶颈分析 #

窗口函数方案 #

常规写法依赖ROW_NUMBER() OVER (PARTITION BY visitor_id ORDER BY created_at DESC)取首条记录，若未创建匹配的索引，数据库会执行全表扫描+内存/磁盘排序，这是1.5秒耗时的核心原因。

分组关联方案 #

通过GROUP BY visitor_id取MAX(created_at)，再关联原表获取消息详情，瓶颈在于：

1. 分组时需对visitor_id排序，生成临时表（Using temporary）
2. 关联原表时，若未通过索引定位到对应消息，会触发二次全表扫描或范围查找

核心优化方案 #

1. 创建覆盖索引（优先级最高） #

直接消除回表和排序开销，是实现亚秒级查询的关键。创建包含visitor_id、排序字段created_at及查询所需所有业务字段的复合索引：

CREATE INDEX idx_visitor_last_msg ON chatmessages(visitor_id, created_at DESC, id, content, sender_id, sender_name, is_read);

• visitor_id作为前缀，保证同访客的消息被聚合在一起
• created_at DESC确保同访客的消息按时间倒序排列，窗口函数无需额外排序
• 后续字段为查询需要返回的所有字段，实现覆盖索引扫描，数据库无需访问主表数据

2. 优化窗口函数查询（利用覆盖索引） #

调整查询语句，确保数据库能直接利用上述索引：

SELECT id, visitor_id, content, sender_id, sender_name, created_at, is_read
FROM (
    SELECT 
        id, visitor_id, content, sender_id, sender_name, created_at, is_read,
        ROW_NUMBER() OVER (PARTITION BY visitor_id ORDER BY created_at DESC) AS rn
    FROM chatmessages
) AS msg_with_rn
WHERE rn = 1;

此时执行计划应显示Using index（覆盖索引扫描），无Using filesort或Using temporary。

3. 物化视图（适合非强实时场景） #

若业务允许1~5秒的数据延迟，可创建物化视图定时刷新每个访客的最后一条消息：

CREATE MATERIALIZED VIEW mv_last_visitor_msg
AS
SELECT m.*
FROM (
    SELECT visitor_id, MAX(created_at) AS last_created
    FROM chatmessages
    GROUP BY visitor_id
) AS g
JOIN chatmessages m ON m.visitor_id = g.visitor_id AND m.created_at = g.last_created;

-- 定时刷新（比如每分钟一次）
REFRESH MATERIALIZED VIEW mv_last_visitor_msg;

查询时直接从物化视图读取，耗时可控制在0.1秒以内。

4. 缓存层优化（极致性能） #

对于强实时且性能要求极高的场景，用Redis缓存每个访客的最后一条消息：

• 发送新消息时，同步更新Redis中对应访客的缓存（存储消息JSON或关键字段）
• 查询最近会话时直接从Redis读取，性能可达毫秒级
• 需通过定时任务或binlog同步机制处理缓存与数据库的一致性问题

验证步骤 #

1. 执行EXPLAIN查看优化后的查询计划，确认：
   • 类型为range或ref，而非ALL（全表扫描）
   • Extra列显示Using index，无Using filesort/Using temporary
2. 实际执行查询，验证耗时是否降至0.2秒以内
3. 若存在同访客同时间多条消息的场景，需在索引和排序中加入id DESC（主键）确保唯一性：

   CREATE INDEX idx_visitor_last_msg ON chatmessages(visitor_id, created_at DESC, id DESC, content, sender_id);
   

内容的提问来源于stack exchange，提问作者Yehia A.Salam