Q1与Q2查询效率对比场景分析：数据工程师面试问题

表结构说明 #

• Course：CId（主键）、Name、Type
• Student：SId（主键）、Name
• Takes：CId（主键，外键关联Course.CId）、SId（主键，外键关联Student.SId）、Semester、Grade

注：学生可重复修读课程，因此同一SId在Takes中可能对应多条记录

两个查询语句 #

Q1：基于存在性检查的查询 #

SELECT SId, Name
FROM Student s
WHERE (SELECT COUNT(*) FROM Takes t WHERE t.SId=s.SId) > 0

注：实际执行中，数据库优化器通常会将COUNT(*) > 0转换为EXISTS存在性检查，找到匹配记录即停止遍历，无需统计总数。

Q2：基于JOIN+去重的查询 #

SELECT DISTINCT SId, Name
FROM Student NATURAL JOIN Takes

Q1比Q2更高效的场景 #

• Takes表中单个学生的重复选课记录极多：比如某学生反复修读同一门课程数十次，Q2会先通过JOIN生成大量重复的Student记录，再执行DISTINCT去重，这两步会消耗大量内存和CPU资源。而Q1的存在性检查只需找到该学生任意一条选课记录就停止查询，无需遍历所有匹配项，开销显著更低。
• Student表中大部分学生无选课记录：此时Q1可利用Student.SId和Takes.SId的索引，快速排除无匹配的学生。而Q2需要先执行JOIN筛选出有选课的学生，再去重；当Student表规模大且匹配率极低时，JOIN的整体开销会高于Q1的逐行存在性检查。
• Takes.SId有高效独立索引，JOIN无法利用最优索引：如果Takes表有单独的SId索引，Q1的子查询可直接通过该索引快速判断存在性；而Q2的NATURAL JOIN若因索引使用不当（如选择哈希JOIN且哈希表构建开销大），会导致执行效率低于Q1。

Q2比Q1更高效的场景 #

• Takes表中每个学生的选课记录极少（平均1-2条）：此时Q2的JOIN生成的重复记录极少，DISTINCT的开销可忽略不计。而Q1若为相关子查询，需对每个Student记录单独发起一次查询；当Student表规模较大时，多次子查询的累加开销会超过Q2的批量JOIN处理。
• 数据库优化器将Q2转换为半连接（Semi-Join）：部分数据库的优化器会识别到Q2的DISTINCT JOIN等价于半连接逻辑，采用更高效的执行计划（如合并JOIN）——先对Student和Takes按SId排序，再一次遍历完成匹配和去重，避免了Q1中逐行子查询的额外开销。
• Student与Takes的匹配率极高：当大部分学生都有选课记录时，Q2的JOIN可一次性完成所有匹配，后续的DISTINCT操作开销极小；而Q1的逐行存在性检查需要多次访问Takes表，总开销反而更高。

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