MySQL左连接查询加ORDER BY卡顿，如何优化该带排序的关联查询？

1. 修正连接类型 #

你的原查询中使用LEFT JOIN，但WHERE子句里过滤了stories.host和complete_free，这会把左连接自动转为内连接（不匹配的stories行字段为NULL，无法满足过滤条件）。直接改为INNER JOIN可以减少数据库不必要的计算：

SELECT chapters.* 
FROM giangthe.chapters
JOIN giangthe.stories
  ON chapters.story_id = stories.id
WHERE stories.host = 'faloo' AND stories.complete_free = 1
ORDER BY chapters.id ASC;

2. 添加针对性索引 #

排序卡顿的核心原因通常是缺少支持过滤、连接和排序的索引，导致数据库需要执行耗时的文件排序（filesort）：

• 给stories表创建联合索引，覆盖过滤条件和连接字段，避免回表查询：

  CREATE INDEX idx_stories_host_free_id ON giangthe.stories(host, complete_free, id);
  

• 确保chapters.id是主键（主键默认自带唯一有序索引），如果不是，单独创建索引：

  CREATE INDEX idx_chapters_id ON giangthe.chapters(id);
  

• chapters.story_id作为外键，建议创建索引加速连接过程：

  CREATE INDEX idx_chapters_story_id ON giangthe.chapters(story_id);
  

3. 优化分批获取逻辑 #

如果用LIMIT OFFSET分页，当OFFSET值很大时性能会急剧下降。改用基于上一页最后一条id的连续分页，利用索引直接定位起始位置：

-- 第一页
SELECT c.* 
FROM giangthe.chapters c
JOIN giangthe.stories s ON c.story_id = s.id
WHERE s.host = 'faloo' AND s.complete_free = 1
ORDER BY c.id ASC
LIMIT 100;

-- 后续页面，替换[上一页最大id]为实际值
SELECT c.* 
FROM giangthe.chapters c
JOIN giangthe.stories s ON c.story_id = s.id
WHERE s.host = 'faloo' AND s.complete_free = 1
AND c.id > [上一页最大id]
ORDER BY c.id ASC
LIMIT 100;

4. 验证执行计划 #

用EXPLAIN分析查询，确认索引是否生效：

EXPLAIN SELECT chapters.* 
FROM giangthe.chapters
JOIN giangthe.stories
  ON chapters.story_id = stories.id
WHERE stories.host = 'faloo' AND stories.complete_free = 1
ORDER BY chapters.id ASC;

查看Extra列，若无Using filesort说明排序已利用索引优化；type列显示ref或range，说明过滤和连接的索引生效。

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