MySQL中SELECT MAX()与GROUP BY不满足需求，求高性能解决方案

表结构说明 #

chapter表（50k+数据） #

id (auto_inc) | chapter_id | story_id | date (timestamp)
1             | 1          | 1        | 1715830560
...
10            | 10         | 1        | 1715830570 -- 故事1的最新章节
100           | 1          | 2        | 1715830560
...
111           | 11         | 2        | 1715830570 -- 故事2的最新章节
200           | 1          | 3        | 1715830560
...
211           | 21         | 3        | 1715830570 -- 故事3的最新章节

story表（10k+数据） #

id (auto_inc) | slug        | title     | date (timestamp)
1             | slug-1      | title 1   | 1715830560
...
100           | slug-100    | tit 100   | 1715830580 -- 最新创建的故事

原SQL问题 #

原SQL语句虽能获取每个故事的最新章节，但排序逻辑不符合需求：

SELECT C.id, MAX(C.chapter_id), C.story_id, C.title AS title_chapter, C.summary
FROM `chapter` C
GROUP by C.story_id
ORDER by C.id DESC
LIMIT 10;

该语句返回的是按故事id降序排列的故事对应的最新章节，而需求是按章节自增id（id字段）降序排列，展示每个故事的最新章节，同时关联story表的title和slug字段，且要求高性能低耗时。

高性能解决方案 #

第一步：添加索引优化查询速度 #

为避免全表扫描，需给关联字段和排序字段添加索引：

• 给chapter表添加复合索引：(story_id, chapter_id DESC, id)，快速定位每个故事的最新章节，同时支持排序。
• story表的自增id默认已有主键索引，确保关联查询时快速匹配。

第二步：使用窗口函数的SQL语句（推荐，MySQL 8.0+） #

窗口函数ROW_NUMBER()可高效标记每个故事的最新章节，筛选后排序并关联story表：

SELECT 
    c.id,
    c.chapter_id,
    c.story_id,
    c.date AS chapter_date,
    c.title AS title_chapter,
    c.summary,
    s.title AS story_title,
    s.slug
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY story_id ORDER BY chapter_id DESC) AS rn
    FROM `chapter`
) c
JOIN `story` s ON c.story_id = s.id
WHERE c.rn = 1 -- 筛选每个故事的最新章节
ORDER BY c.id DESC -- 按章节id降序排序
LIMIT 10;

备选方案：子查询关联（兼容旧版本MySQL） #

若你的MySQL版本低于8.0，可用子查询先获取每个故事的最大chapter_id，再关联原表和story表：

SELECT 
    c.id,
    c.chapter_id,
    c.story_id,
    c.date AS chapter_date,
    c.title AS title_chapter,
    c.summary,
    s.title AS story_title,
    s.slug
FROM `chapter` c
JOIN (
    SELECT story_id, MAX(chapter_id) AS max_chapter_id
    FROM `chapter`
    GROUP BY story_id
) latest ON c.story_id = latest.story_id AND c.chapter_id = latest.max_chapter_id
JOIN `story` s ON c.story_id = s.id
ORDER BY c.id DESC
LIMIT 10;

注意：该方案必须依赖之前创建的(story_id, chapter_id DESC)索引，否则性能会大幅下降。

结果说明 #

执行上述SQL后，会得到按章节id降序排列的结果，每个故事仅展示其最新章节，同时包含对应的故事标题和slug，符合预期：

id (↓)        | chapter_id (latest of each story) | story_id (unique) | chapter_date | story_title | slug
508           | 15                                | 75                | 1715830570   | ...         | ...
507           | 35                                | 95                | 1715830569   | ...         | ...
506           | 5                                 | 15                | 1715830568   | ...         | ...
505           | 15                                | 100               | 1715830568   | ...         | ...
504           | 35                                | 99                | 1715830568   | ...         | ...

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