百万级大表慢SQL查询优化思路验证与方案咨询

问题背景 #

• 待优化的SQL涉及单表百万行规模的多表关联查询，因数据未存储在本地无法构造测试验证，仅持有建表语句与原始SQL，需要确认现有优化思路的正确性，补充遗漏的优化点。

待优化原始SQL #

SELECT
    tutor.tutor_id, 
    tutor.subject_id,
    course.title,
    SUBSTR(score.date, 4, 2) AS month,
    AVG(score.score)
FROM
    tutor
    LEFT JOIN course ON tutor.course_id = course.course_id
    LEFT JOIN score ON tutor.course_id = score.course_id
WHERE
    course.course_id IS NOT NULL
    AND score.date > '01-MAR-2020' 
    AND score.student_id in (SELECT student_id FROM students)
GROUP BY 
    tutor.tutor_id, 
    tutor.subject_id,
    course.title,
    SUBSTR(score.date, 4, 2);

初步梳理的优化方向 #

• 判断WHERE子句中嵌套的IN (SELECT student_id FROM students)子查询是性能瓶颈之一，计划第一步替换为INNER JOIN实现
• 确认当前查询仅选取业务必需字段，后续计划通过LIMIT采样查询结果、执行EXPLAIN PLAN分析执行计划定位延迟根因、检查JOIN关联字段的索引配置等方式提升性能

关联表建表语句 #

CREATE TABLE Score (
    score_id integer,
    student_id integer,
    course_id integer,
    date date,
    score integer,
    PRIMARY KEY(score_id),
    FOREIGN KEY(student_id) REFERENCES Students(student_id),
    FOREIGN KEY(course_id) REFERENCES Course(course_id)
);

CREATE TABLE Student (
    student_id integer,
    first_name varchar,
    last_name varchar,
    group_id integer,
    PRIMARY KEY(student_id),
    FOREIGN KEY(group_id) REFERENCES Groups(group_id)
);

CREATE TABLE Groups (
    group_id integer,
    name varchar,
    PRIMARY KEY(group_id)
);

CREATE TABLE Course (
    course_id integer,
    title varchar,
    PRIMARY KEY(course_id)
);

CREATE TABLE Tutor (
    course_id integer,
    tutor_id integer,
    group_id integer,
    PRIMARY KEY(tutor_id),
    FOREIGN KEY(course_id) REFERENCES Course(course_id),
    FOREIGN KEY(group_id) REFERENCES Groups(group_id)
);

优化思路校验与补充 #

现有思路的正确性说明 #

• 将IN嵌套子查询替换为JOIN的方向是对的，但这里存在一个更优的处理方式：Score表的student_id字段本身就是指向Student表主键的外键，只要外键约束未被禁用，所有Score表中student_id非空的记录必然对应Student表中存在的学生ID，这个IN判断是完全冗余的，直接删除即可，不需要额外做JOIN关联，能省掉一次大表关联的开销。
• 通过EXPLAIN分析执行计划、检查关联字段索引的思路是正确的，这是慢SQL优化的标准前置动作。
• 注意：LIMIT仅适合调试阶段采样验证结果正确性，不能作为生产环境的性能优化手段，若业务需要分页，必须配合过滤条件缩小扫描范围，避免大偏移量分页的性能问题。

遗漏的核心优化点 #

1. 修正JOIN类型，减少无效计算
   当前SQL写了LEFT JOIN后又在WHERE子句中对关联表字段加非空、过滤条件，本质已经是内连接逻辑：
   • 对Course表的LEFT JOIN加了course.course_id IS NOT NULL条件，直接改成INNER JOIN即可，避免左连接生成空匹配记录后再被过滤的无效开销
   • 对Score表的LEFT JOIN加了score.date > '01-MAR-2020'条件，所有关联不到Score的记录都会被这个条件过滤掉，也应该直接改成INNER JOIN
2. 避免函数导致的索引失效，修正日期取值逻辑
   你用SUBSTR(score.date, 4, 2)提取月份存在两个问题：一是Score表的date字段是DATE类型，用字符串截取函数会导致数据库无法使用date字段上的索引，二是截取结果依赖数据库会话的默认日期格式，很容易出现取值错误。建议改用数据库原生的日期提取函数，比如MONTH(score.date)或EXTRACT(MONTH FROM score.date)，在保证结果正确性的同时，避免函数造成的索引失效。
3. 针对性建立覆盖索引，避免回表开销
   不能只笼统检查关联字段是否有索引，要针对这条查询的过滤、关联、分组、计算逻辑建联合覆盖索引，减少回表：
   • 针对Score表（百万级核心大表）建议建联合索引(course_id, date, student_id, score)：可以直接通过索引完成和Tutor表的course_id关联、date条件过滤，AVG计算需要的score字段也在索引中，全程不需要回表查询主键数据
   • 针对Tutor表建议建联合索引(course_id, tutor_id, subject_id)：关联时可以直接从索引取出查询需要的tutor_id、subject_id字段，不需要回表
   • Course表的course_id是主键，默认已有聚簇索引，不需要额外加索引
4. 提前聚合缩小中间结果集
   不要等三表全量关联完成后再做GROUP BY聚合，可以先在Score表层面按course_id + 月份维度提前聚合计算出平均分数，把百万级的Score记录压缩成小体量的聚合结果后，再和Tutor、Course表做关联，能大幅降低关联阶段的计算量，性能提升会非常明显。
5. 字段校验
   你提供的Tutor表建表语句中不存在查询用到的subject_id字段，执行前需要先确认字段是否存在，避免执行报错。

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