如何优化含Using temporary/filesort的2秒耗时MariaDB查询

核心问题分析 #

原查询出现Using temporary; Using filesort，本质是先关联所有表再排序，导致处理的数据量过大，即便单表有索引也无法发挥作用。

具体优化步骤 #

1. 先取最新5条评论的核心数据 #

单独从comments表获取最新的5条记录，利用created_at的索引快速定位，避免全量关联后再排序：

SELECT c.id, c.article_id, c.user_id, c.content, c.created_at
FROM comments c
ORDER BY c.created_at DESC
LIMIT 5;

这一步直接走created_at的索引，不会产生临时表和文件排序。

2. 关联其他表获取附加信息 #

基于上面的5条评论ID，再关联articles和users表取标题、slug、用户名，因为仅处理5条数据，关联成本极低：

SELECT 
    c.id, c.content, c.created_at,
    a.title, a.slug,
    u.username
FROM (
    SELECT id, article_id, user_id, content, created_at
    FROM comments
    ORDER BY created_at DESC
    LIMIT 5
) c
JOIN articles a ON c.article_id = a.id
JOIN users u ON c.user_id = u.id
ORDER BY c.created_at DESC;

最后排序仅针对5条数据，几乎无性能开销。

3. 优化索引提升效率 #

• 给comments表的created_at建倒序索引，或者建包含必要字段的覆盖索引（避免回表查询）：

  -- 基础倒序索引
  CREATE INDEX idx_comments_created_at ON comments(created_at DESC);
  
  -- 覆盖索引（推荐，减少磁盘IO）
  CREATE INDEX idx_comments_created_at_include ON comments(created_at DESC) 
  INCLUDE (id, article_id, user_id, content);
  

• 确认articles.id和users.id是主键（默认自带索引，无需额外创建）。

4. 清理冗余内容 #

检查原查询是否有多余的SELECT字段或不必要的表关联，去掉这些冗余能减少数据传输和处理成本。

5. 验证优化效果 #

执行EXPLAIN查看优化后的执行计划，应该看到：

• 子查询部分使用idx_comments_created_at索引，无Using temporary和Using filesort
• 关联阶段是基于5条数据的嵌套循环，执行效率显著提升

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