GROUP BY与ORDER BY查询过慢，寻求合适的索引优化方案

问题分析 #

你的查询核心是获取INBOX文件夹下每个工单（ticket_number）最新的邮件记录（通过LEFT JOIN排除存在更大id的同工单邮件），再按created_at倒序取前20条。当前查询慢的原因有两个：

1. 多余的GROUP BY：m2.id IS NULL已经确保每个ticket_number仅返回1条记录，GROUP BY会触发临时表创建，额外消耗性能。
2. ORDER BY created_at DESC无法利用现有索引，导致对4万+条记录全量排序（filesort），这是性能瓶颈的核心。

优化方案 #

1. 修正查询语句，移除冗余GROUP BY

SELECT
    m1.*,
    m1.created_at AS receivedAt  -- 原语句中`mail_at`为笔误，表结构无此字段，替换为`created_at`
FROM
    mail AS m1
WHERE
    m1.folder_path = 'INBOX'
    AND NOT EXISTS (
        SELECT 1 
        FROM mail AS m2 
        WHERE m2.ticket_number = m1.ticket_number 
          AND m2.folder_path = m1.folder_path 
          AND m2.id > m1.id
    )
ORDER BY
    m1.created_at DESC
LIMIT 20 OFFSET 0;

2. 创建针对性复合索引

创建覆盖筛选、排序、关联条件的索引，彻底消除filesort和临时表：

CREATE INDEX idx_folder_created_ticket_id ON mail (folder_path, created_at DESC, ticket_number, id);

这个索引的作用：

• folder_path作为前缀，快速过滤出INBOX文件夹的所有记录；
• created_at DESC让索引内的记录天然按排序要求排列，直接满足ORDER BY需求，无需额外排序；
• ticket_number和id用来快速完成NOT EXISTS子查询的条件校验，无需回表查询额外字段。

原表中已有的folder_path (folder_path, ticket_number, id, created_at)索引可以支持子查询中m2的快速查找，无需额外创建新索引。

效果验证 #

优化后的查询执行计划会显示：

• m1表使用idx_folder_created_ticket_id索引，Extra字段无Using temporary和Using filesort；
• m2表使用现有folder_path索引，快速验证是否存在更大id的同工单记录。

查询执行时间会降至接近移除ORDER BY后的0.004秒水平。

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