MySQL ORDER BY慢查询优化：邮件业务取前40条记录耗时15秒问题

针对当前查询耗时接近15秒的问题，可从索引补全、SQL逻辑改写、执行效率优化几个维度落地改进，具体如下：

• 补全适配查询逻辑的联合覆盖索引，消除全表扫描和回表开销
  现有单键索引无法支撑子查询的过滤、分组、取最大值逻辑，50万行的Messages表每次查询都需要读取全量行计算每个SubjectID下的最新MessageID，是耗时的核心来源。可在Messages表创建联合索引idx_messages_received_subject (IsReceived, SubjectID, MessageID, IsRead, MessageDate)，这个索引可以完全覆盖子查询的所有计算需求：
  • 最左列IsReceived可以直接过滤收到的邮件，跳过所有发件记录
  • 第二列SubjectID可以直接支撑GROUP BY分组，触发松散索引扫描，无需遍历全表行，直接在索引结构上跳读每个SubjectID分组的最大MessageID
  • 索引叶子节点直接存储了后续JOIN需要的IsRead、MessageDate字段，关联Messages取字段值时完全不需要回表读聚簇索引的整行数据
    该索引建好后，原来需要扫描50万行的子查询，会变为和SubjectID数量线性相关的毫秒级扫描，总耗时可直接降低一个数量级。
    同时可在Subjects表创建覆盖索引idx_subjects_contact (ContactID, SubjectID, Subject)，支撑和Contacts表的关联逻辑，访问Subject表时也不需要回表取字段。
• 改写冗余的窗口函数写法，下推LIMIT截断逻辑
  现有写法通过两层嵌套：先对全量符合JOIN条件的结果集计算ROW_NUMBER生成行号，再在外层筛选1-40的范围。这种写法会强制优化器把所有符合关联条件的记录全部物化到临时表，即使最终只需要返回40条数据，也需要完成全量结果的存储、排序，额外产生大量磁盘IO和内存开销。
  实际对比验证，取连续1-40的行和直接按相同排序规则加LIMIT 40的返回结果完全一致，可直接去掉ROW_NUMBER的嵌套逻辑，将排序规则写在最外层，让优化器在排序阶段一旦凑够40条符合规则的记录就停止扫描，无需处理全量数据。改写后的参考SQL：

  SELECT 
    c.ContactName, s.subject, s.SubjectID, d.MessageDate, d.isRead
  FROM Contacts c                    
  INNER JOIN Subjects s ON s.ContactID = c.ContactID
  INNER JOIN (
    SELECT MAX(MessageID) AS dtl_id, SubjectID 
    FROM Messages 
    WHERE IsReceived = 1    
    GROUP BY SubjectID
  ) d_max ON d_max.SubjectID = s.SubjectID
  INNER JOIN Messages d ON d.MessageID = d_max.dtl_id
  ORDER BY d.isRead ASC, d.MessageDate DESC
  LIMIT 40;
  

• 辅助优化排序执行效率
  因为最终排序只涉及40条记录，可适当调整会话级的内存临时表参数，让排序过程完全在内存中完成，避免因为内存不足把临时数据写入磁盘产生额外开销：

  SET tmp_table_size = 67108864; -- 64MB
  SET max_heap_table_size = 67108864;
  

• 可选的冗余字段优化方案
  如果日常查询频率远高于写入频率，可在Subjects表新增LatestMessageID字段，每次Messages表有新消息写入时，同步更新对应SubjectID的LatestMessageID值，查询时直接关联这个字段拿到对应消息，完全去掉子查询的分组计算逻辑，查询性能还能进一步提升。

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