聊天应用消息历史加载查询优化问询（5000万行大表场景）

现有查询缺陷 #

• JOIN条件中的OR逻辑会导致索引失效，5000万行大表全表扫描效率极低
• 非标准GROUP BY写法无法保证取到对应联系人的最新消息时间，结果准确性不可靠
• MyISAM引擎表级锁会导致查询期间阻塞消息写入，影响线上业务可用性

具体优化措施 #

1. 新增覆盖索引 #

为messages表新增两个联合覆盖索引，避免回表查询，让收发两种场景的查询都能直接命中索引：

ALTER TABLE `messages` 
ADD INDEX `idx_sender_receiver_date` (`sender_id`, `receiver_id`, `date` DESC),
ADD INDEX `idx_receiver_sender_date` (`receiver_id`, `sender_id`, `date` DESC);

2. 重写查询逻辑 #

拆分收发消息两个场景分别查询，避免OR逻辑导致的索引失效，同时保证取到每个联系人的最新交流时间：

SELECT u.username, t.contact_id, t.latest_msg_time
FROM (
    -- 聚合所有用户108收到的消息，取每个发送者的最新消息时间
    SELECT sender_id AS contact_id, MAX(`date`) AS latest_msg_time
    FROM messages
    WHERE receiver_id = 108
    GROUP BY sender_id
    UNION ALL
    -- 聚合所有用户108发出的消息，取每个接收者的最新消息时间
    SELECT receiver_id AS contact_id, MAX(`date`) AS latest_msg_time
    FROM messages
    WHERE sender_id = 108
    GROUP BY receiver_id
) AS t
-- 合并同一联系人的收发记录，取最晚的交流时间
GROUP BY t.contact_id
-- 关联用户表获取用户名
JOIN users u ON u.id = t.contact_id
-- 按最新交流时间倒序排列
ORDER BY t.latest_msg_time DESC;

如果你的MySQL版本在8.0及以上，也可以用窗口函数实现，逻辑更简洁，同样可以命中上述索引：

WITH user_msg_list AS (
    SELECT
        IF(sender_id = 108, receiver_id, sender_id) AS contact_id,
        `message`, `date`,
        ROW_NUMBER() OVER (PARTITION BY IF(sender_id = 108, receiver_id, sender_id) ORDER BY `date` DESC) AS rn
    FROM messages
    WHERE sender_id = 108 OR receiver_id = 108
)
SELECT u.username, ml.contact_id, ml.`message`, ml.`date`
FROM user_msg_list ml
JOIN users u ON u.id = ml.contact_id
WHERE ml.rn = 1
ORDER BY ml.`date` DESC;

3. 长期优化建议 #

• 将messages表的存储引擎从MyISAM更换为InnoDB，支持行级锁、事务和崩溃安全，并发性能提升明显
• 新增user_conversations会话中间表，每条记录对应两个用户的唯一会话，每次发送消息时同步更新该会话的最新消息时间。查询联系人列表时直接读取该表，数据量远小于messages表，查询性能可提升一个数量级。

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