如何避免MySQL查询出现Using temporary; Using filesort问题

问题根源 #

你当前的SQL执行逻辑是先完成所有表的关联，生成全量结果集后再做排序，符合条件的cam_tags记录有7500万行，关联后生成的结果集体量极大，排序时需要创建临时表、甚至触发磁盘文件排序，所以性能极差。去掉ORDER BY后不需要排序步骤，自然速度恢复正常。

优化方案 #

1. 改写SQL逻辑：先分页排序，再关联取字段

只把排序分页需要的最小字段集放到子查询处理，拿到符合分页要求的24条记录后再关联其他表取扩展字段，大幅降低排序的数据量：

SELECT cams.name, models.gender, TIMESTAMPDIFF(YEAR, models.birthdate, CURRENT_DATE) AS age, sorted_cams.viewers
FROM (
    -- 子查询仅处理过滤、排序、分页逻辑，所有字段均可通过覆盖索引直接获取
    SELECT ct.cam_id, lcs.viewers
    FROM cam_tags ct
    INNER JOIN tags t ON ct.tag_id = t.id
    LEFT JOIN latest_cam_stats lcs ON ct.cam_id = lcs.cam_id
    WHERE t.name = '?'
    ORDER BY lcs.time_stamp_id DESC, lcs.viewers DESC
    LIMIT 24 OFFSET 96
) AS sorted_cams
LEFT JOIN cams ON sorted_cams.cam_id = cams.id
LEFT JOIN model_cams ON sorted_cams.cam_id = model_cams.cam_id
LEFT JOIN models ON model_cams.model_id = models.id;

2. 新增覆盖索引消除回表

新增两个联合索引，让子查询的所有操作都可以直接通过索引完成，不需要回表读取数据：

• 给cam_tags表新增联合索引：ALTER TABLE cam_tags ADD INDEX idx_tag_cam (tag_id, cam_id);
  这个索引可以直接通过tag名匹配到的tag_id，拿到所有关联的cam_id，不需要扫描cam_tags全表数据。
• 调整latest_cam_stats表的联合索引为：ALTER TABLE latest_cam_stats ADD INDEX idx_cam_ts_viewers (cam_id, time_stamp_id DESC, viewers DESC);
  这个索引可以直接通过cam_id拿到排序需要的time_stamp_id和viewers字段，不需要回表。

优化效果 #

调整后子查询排序的数据量仅为原逻辑的1/10不到，Using temporary; Using filesort的开销会降到可忽略的程度，查询性能会有数量级的提升。如果后续offset值变得非常大（比如超过1万），可以进一步改用游标分页代替offset偏移，消除大偏移量的扫描开销。

内容的提问来源于stack exchange，提问作者tobi.g