MySQL查询ORDER BY message_creation_time DESC过慢排查优化

• 核心性能瓶颈：message_creation_time是逐行关联子查询生成的派生字段，没有索引可支撑排序。去掉ORDER BY子句时，MySQL凑够LIMIT 20条结果即可提前终止查询，不需要执行全量符合条件行的子查询；加入排序后，必须先遍历所有满足WHERE条件的会话记录，逐行执行全部关联子查询拿到所有行的message_creation_time值，再做内存/磁盘filesort，最后才能截取前20条结果，额外开销陡增。
• N+1查询问题严重：原SQL对主表每一行结果，要执行8次独立关联子查询（查最新消息ID、最新消息3个字段、assign表2个字段、未读计数、对方用户名），假设符合条件的会话有1000条，就要多产生8000次随机IO查询，开销极高。
• 冗余逻辑过多：已经LEFT JOIN了assign_support_conversations表，仍用子查询查该表同channel_id的字段；拿到最新消息ID后分3次查同一条消息的不同字段；HAVING条件unread_messages >=0完全无效（COUNT统计结果不可能为负）；GROUP BY写法不规范，SELECT大量非聚合、非分组键字段，既存在结果不确定的隐患，也额外增加了分组开销。
• 索引不匹配查询逻辑：未读数查询的过滤条件是uid_to + channel_id + seen，没有对应联合索引；查同channel下status=1的最大消息ID时，现有联合索引未包含主键id，需要回表查询；主表过滤条件role_type + is_closed没有对应联合索引，过滤效率低。
• 字段设计不合理：created_at用varchar类型存储时间，排序时按字符串规则比较，不仅可能出现排序结果错误，排序性能也远低于整数类型时间戳。

1. SQL重写，彻底消除N+1子查询 #

用预聚合的派生表关联代替逐行子查询，改写示例：

SELECT 
    msg_stat.unique_max_id,
    sck.id,
    sck.`from`,
    sck.`to`,
    sck.channel,
    sck.channel_id,
    sck.role_type,
    sck.is_closed,
    sck.closed_at,
    sck.closed_by,
    sm.has_attachment,
    sm.message AS last_message,
    sm.created_at AS message_creation_time,
    `asc`.is_pinned,
    `asc`.id AS assign_support_id,
    IFNULL(msg_stat.unread_messages,0) AS unread_messages,
    u.username AS to_username
FROM support_contacts_keys AS sck
INNER JOIN `assign_support_conversations` AS `asc` ON `asc`.channel_id = sck.channel_id
-- 一次聚合算出每个channel的最新消息ID、未读数，替代逐行子查询
LEFT JOIN (
    SELECT 
        channel_id,
        MAX(CASE WHEN STATUS = 1 THEN id END) AS unique_max_id,
        SUM(CASE WHEN uid_to = 3 AND seen IN (0,1) THEN 1 ELSE 0 END) AS unread_messages
    FROM support_messages
    GROUP BY channel_id
) AS msg_stat ON msg_stat.channel_id = sck.channel_id
-- 单次关联拿最新消息全量字段，不用分三次子查询
LEFT JOIN support_messages sm ON sm.id = msg_stat.unique_max_id
-- 单次关联拿对方用户名，不用CASE WHEN套子查询
LEFT JOIN `user` u ON u.id = IF(sck.`from` = 3, sck.`to`, sck.`from`)
WHERE sck.role_type = 'support'  
  AND `asc`.is_pinned IN (0, 1)     
  AND sck.is_closed = 0
-- 如果sck表同channel_id存在重复记录，打开下一行注释即可
-- GROUP BY sck.channel_id
-- 用整数时间戳排序，替代字符串类型的created_at
ORDER BY sm.created_unix DESC 
LIMIT 20 OFFSET 0

说明：原SQL用LEFT JOIN assign表但WHERE中写了asc.is_pinned过滤条件，实际等价于INNER JOIN，直接改为INNER JOIN可减少无效数据匹配。

2. 索引优化 #

• 给support_messages表新增联合索引，覆盖最新消息查询、未读数统计场景：(channel_id, status, id, uid_to, seen)，如果要进一步消除回表，可以把has_attachment、message、created_unix、created_at加入索引做覆盖索引，可根据实际数据量调整。
• 给support_contacts_keys表新增过滤用联合索引：(role_type, is_closed, channel_id)，可以直接通过索引过滤不符合条件的记录，减少回表次数。
• 废弃created_at字段的排序逻辑，统一用BIGINT类型的created_unix字段排序，避免字符串排序的性能问题和结果错误。

3. 长期架构优化 #

聊天会话列表场景不要每次实时从消息表聚合最新消息、未读数，建议在会话表（或单独的会话元数据表）冗余last_message_id、last_message_content、last_message_time、unread_count字段，发送消息、已读事件触发时同步更新冗余字段，查询会话列表时直接查会话表即可，不需要关联消息表做聚合，性能可以提升1-2个数量级，可支撑百万级消息量的业务场景。

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