SQL查询优化咨询：查找与讲师Milmore授课相同的其余讲师信息

原SQL存在的问题 #

• 重复执行相同子查询：代码中两次查询instructor表获取姓氏为Milmore的讲师ID，造成不必要的IO开销
• 多层IN嵌套逻辑冗余：三层IN嵌套的执行效率在数据量大的场景下会明显下降，同时NOT IN如果遇到子查询返回NULL的场景会出现结果为空的逻辑错误
• 输出字段不符合需求：原查询仅返回dictado表的课程、年份、讲师ID字段，没有关联instructor表获取要求的讲师姓名、出生日期信息

优化后的查询语句 #

方案1：用JOIN代替IN嵌套（兼容性最好，性能更稳定） #

SELECT DISTINCT i.apellido, i.nombre, i.fecha_nacimiento
FROM instructor i
-- 关联授课表拿到该讲师的所有授课记录
JOIN dictado d ON i.id_profesor = d.id_profesor
-- 关联Milmore讲师的所有授课记录做匹配
JOIN (
    SELECT DISTINCT d2.cod_curso, d2.anio
    FROM dictado d2
    JOIN instructor i2 ON d2.id_profesor = i2.id_profesor
    WHERE i2.apellido = 'Milmore'
) milmore_cursos ON d.cod_curso = milmore_cursos.cod_curso AND d.anio = milmore_cursos.anio
-- 排除Milmore本人
WHERE i.apellido != 'Milmore'

方案2：用EXISTS改写（适合dictado表数据量极大的场景，提前终止匹配） #

SELECT DISTINCT i.apellido, i.nombre, i.fecha_nacimiento
FROM instructor i
WHERE i.apellido != 'Milmore'
AND EXISTS (
    SELECT 1
    FROM dictado d
    JOIN dictado d_milmore ON d.cod_curso = d_milmore.cod_curso AND d.anio = d_milmore.anio
    JOIN instructor i_milmore ON d_milmore.id_profesor = i_milmore.id_profesor
    WHERE d.id_profesor = i.id_profesor
    AND i_milmore.apellido = 'Milmore'
)

额外性能提升建议 #

• 建议给instructor表的apellido字段加普通索引，加速姓氏匹配
• 建议给dictado表的id_profesor、cod_curso、anio字段加联合索引，避免授课表的全表扫描
• 如果确定没有姓氏模糊匹配的需求，把LIKE 'Milmore'改成= 'Milmore'，可以用上索引，执行效率更高

内容的提问来源于stack exchange，提问作者Maite Iara Leiva