MySQL带ORDER BY的JOIN关联查询执行过慢问题求助

• 执行计划出现的Using temporary和Using filesort是性能低下的核心诱因：当前查询执行逻辑为，先从books_genres表匹配genre_id in的条件，得到近20万条book_id，再关联books表过滤状态条件，之后需要对所有符合条件的结果完成去重、按livelib_read_num和id排序的操作，大数量级的排序直接拉高了查询耗时。
• 去掉ORDER BY后不需要执行排序逻辑，匹配到前25条符合条件的结果就直接返回，因此速度有量级提升。
• 现有索引未覆盖排序字段，MySQL无法利用索引顺序直接返回结果，只能在内存/磁盘中生成临时数据集完成排序，符合条件的数据集越大，排序耗时越高。
• 查询中的DISTINCT去重操作也会触发临时表创建，叠加后续排序操作进一步放大了性能开销。

方案1：新增覆盖索引，利用索引排序避免临时表和文件排序（优先级最高） #

给books表新增联合索引，覆盖过滤条件+排序条件，让MySQL可以从books表按排序顺序遍历，每拿到一条book_id就去books_genres表校验是否符合genre_id条件，匹配到25条就直接返回，全程不需要额外排序和临时表。
索引创建语句：

ALTER TABLE `books` ADD INDEX `idx_status_master_readnum_id` (`is_status`, `master_book`, `livelib_read_num` DESC, `id` DESC);

该方案优化后查询耗时通常可降到毫秒级，改造成本最低。

方案2：改写SQL逻辑，缩小排序数据集 #

先从books表按排序条件拿到符合状态要求的ID列表，再和books_genres的分类条件做关联匹配，减少需要处理的数据量：

SELECT b.id
FROM (
    SELECT id 
    FROM `books` 
    WHERE `is_status` = 'active' AND `master_book` = 'true'
    ORDER BY `livelib_read_num` DESC, `id` DESC
) b
INNER JOIN `books_genres` bg ON bg.book_id = b.id
WHERE bg.genre_id IN (380,381,384,385,1359)
GROUP BY b.id
LIMIT 0,25;

方案3：字段冗余（适合读多写少的业务场景） #

如果这类分类+排序的查询频次很高，可以把livelib_read_num字段冗余到books_genres表，给books_genres新增联合索引genre_id, livelib_read_num DESC, book_id DESC，这样直接遍历索引就能拿到符合分类条件且按要求排序的book_id，再关联books表做简单校验即可，完全避免排序操作。

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