MySQL关联查询优化：如何简化语句并提升查询效率？

需求回顾 #

需要查询classroom=1的所有用户（包含未完成paperId=25作业的用户）该作业的最高得分记录，原查询语句存在多次表关联，效率有优化空间。

原语句问题分析 #

原语句通过三层嵌套关联scores表：先聚合每个用户的最高得分和最大firstTry值，再关联原scores表匹配对应记录，额外多了一次不必要的表关联，增加了查询开销。

优化方案 #

方案1：使用窗口函数（MySQL 8.0+ 推荐）

窗口函数可以直接按用户分组，筛选出每个用户的最高得分记录，逻辑简洁且效率更高：

SELECT u.*, s.score, s.firstTry, s.id
FROM users u
LEFT JOIN (
    SELECT 
        uid, score, firstTry, id,
        -- 按用户分组，优先取得分最高的，得分相同则取firstTry大的，最后按id升序
        ROW_NUMBER() OVER (PARTITION BY uid ORDER BY score DESC, firstTry DESC, id ASC) AS rn
    FROM scores
    WHERE paperId = 25
) s ON u.uid = s.uid AND s.rn = 1
WHERE u.classroom = 1
ORDER BY s.score DESC, s.id ASC;

优势：仅需两次表关联，窗口函数的计算效率远高于多次聚合关联，同时能精准控制同分情况下的记录筛选逻辑。

方案2：兼容MySQL 5.x版本（无窗口函数）

通过关联子查询直接筛选每个用户的目标记录，避免额外的表关联：

SELECT u.*, s.score, s.firstTry, s.id
FROM users u
LEFT JOIN scores s 
    ON u.uid = s.uid 
    AND s.paperId = 25
    AND (s.score, s.firstTry) = (
        SELECT MAX(score), MAX(firstTry)
        FROM scores
        WHERE uid = s.uid AND paperId = 25
    )
WHERE u.classroom = 1
ORDER BY s.score DESC, s.id ASC;

优势：去掉了原语句中多余的子查询关联，逻辑更直观，适合低版本MySQL环境。

索引优化建议 #

为进一步提升查询效率，需添加以下复合索引：

• 给scores表创建索引：CREATE INDEX idx_scores_paper_uid_score ON scores(paperId, uid, score, firstTry, id);
• 给users表创建索引：CREATE INDEX idx_users_classroom_uid ON users(classroom, uid);
  这些索引可以让数据库直接通过索引定位数据，避免全表扫描。

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