MySQL多表关联带条件查询时，如何对titles结果而非关联结果分页？

要实现筛选含特定流派的标题并按标题数量分页，核心思路是先筛选出符合条件的标题并完成分页，再关联获取对应的流派信息，避免LIMIT作用在关联后的多条结果上。以下是两种高效实现方式：

方法一：先分页获取标题，再关联流派 #

先通过子查询筛选出符合流派要求的标题，去重后分页，再关联中间表和流派表获取完整信息：

SELECT paginated_titles.name AS title_name, genres.name AS genre_name
FROM (
    -- 内层子查询：筛选符合流派的标题，去重后分页
    SELECT titles.id, titles.name
    FROM titles
    INNER JOIN title_genre_relationships tgr ON titles.id = tgr.title_id
    INNER JOIN genres ON tgr.genre_id = genres.id
    WHERE genres.name IN ('one', 'two')
    GROUP BY titles.id, titles.name  -- 确保每个标题只出现一次
    ORDER BY titles.name ASC
    LIMIT 100, 20  -- 这里的LIMIT作用在标题数量上
) AS paginated_titles
-- 外层关联获取该分页标题对应的所有流派
INNER JOIN title_genre_relationships tgr ON paginated_titles.id = tgr.title_id
INNER JOIN genres ON tgr.genre_id = genres.id
ORDER BY paginated_titles.name ASC;

方法二：用DISTINCT替代GROUP BY的简化写法 #

如果标题的id是主键，也可以用DISTINCT代替GROUP BY实现去重，逻辑更简洁：

SELECT paginated_titles.name AS title_name, genres.name AS genre_name
FROM (
    SELECT DISTINCT titles.id, titles.name
    FROM titles
    INNER JOIN title_genre_relationships tgr ON titles.id = tgr.title_id
    INNER JOIN genres ON tgr.genre_id = genres.id
    WHERE genres.name IN ('one', 'two')
    ORDER BY titles.name ASC
    LIMIT 100, 20
) AS paginated_titles
INNER JOIN title_genre_relationships tgr ON paginated_titles.id = tgr.title_id
INNER JOIN genres ON tgr.genre_id = genres.id
ORDER BY paginated_titles.name ASC;

性能优化关键：建立合适的索引 #

只要索引配置正确，即使匹配的标题数量庞大，上述方案依然高效。建议创建以下索引：

• genres表：(name, id) 联合索引，快速通过流派名称定位到流派ID
• title_genre_relationships表：(genre_id, title_id) 联合索引，快速通过流派ID找到关联的标题ID；同时创建(title_id, genre_id)索引，用于外层关联时快速查找标题对应的流派
• titles表：(id, name) 联合索引，覆盖子查询中需要的ID和名称字段，避免回表查询

关于IN子查询方案的性能说明 #

你担心的「先获取所有符合条件的标题ID再用IN筛选」的方案，只要配合上述索引，性能同样有保障。但相比上面的方案，它需要先查询所有符合条件的ID，再分页，当匹配数量极大时，内存占用会更高。而先分页再关联的方案，内层子查询只返回分页后的20条标题ID，内存开销更小，整体效率更优。

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