JOIN前子查询过滤与JOIN后WHERE过滤的性能对比咨询

问题描述 #

我有一个关联8张表的大型查询，部分表存在一对多关系，现咨询两个问题：

1. 在JOIN操作前通过子查询过滤表数据，相比在所有JOIN完成后使用WHERE子句进行过滤，哪种方式的查询效率更高？
2. 在版本2中，因一对多关系产生course表的重复记录时，REGEXP正则表达式是否会对每条重复记录执行，还是会被数据库优化？

版本1（子查询过滤） #

SELECT *
FROM (SELECT * FROM mdl_course WHERE idnumber REGEXP 'some regex') AS course
JOIN mdl_enrol AS enrollment ON enrollment.courseid = course.id
JOIN mdl_user_enrolments AS userEnrollment ON enrollment.Id = userEnrollment.enrolid
JOIN mdl_user AS user ON user.Id = userEnrollment.UserId
JOIN mdl_course_modules AS courseModule ON course.id = courseModule.course
JOIN mdl_modules AS module ON courseModule.module = module.id AND module.Name = 'resource'
JOIN mdl_context AS context ON context.instanceid = courseModule.id AND context.contextlevel = 70
LEFT JOIN (SELECT * FROM mdl_logstore_standard_log WHERE component = 'mod_resource' AND action = 'viewed') AS logEntry
    ON logEntry.contextid = context.id AND logEntry.userid = user.id

版本2（WHERE子句过滤） #

SELECT *
FROM mdl_course AS course
JOIN mdl_enrol AS enrollment ON enrollment.courseid = course.id
JOIN mdl_user_enrolments AS userEnrollment ON enrollment.Id = userEnrollment.enrolid
JOIN mdl_user AS user ON user.Id = userEnrollment.UserId
JOIN mdl_course_modules AS courseModule ON course.id = courseModule.course
JOIN mdl_modules AS module ON courseModule.module = module.id AND module.Name = 'resource'
JOIN mdl_context AS context ON context.instanceid = courseModule.id AND context.contextlevel = 70
LEFT JOIN mdl_logstore_standard_log AS logEntry
    ON logEntry.contextid = context.id AND logEntry.userid = user.id
WHERE
    course.idnumber REGEXP 'some regex' AND
    (logEntry.component = 'mod_resource' OR logEntry.component IS NULL) AND
    (logEntry.action = 'viewed' OR logEntry.action IS NULL)

问题解答 #

1. 子查询过滤与JOIN后WHERE过滤的效率对比 #

现代主流数据库（如MySQL、PostgreSQL）的查询优化器具备查询重写能力，多数情况下会自动调整执行顺序，因此两种写法的效率不一定有绝对差距，但存在以下关键差异：

• 结果一致性差异：版本2中对logEntry的过滤条件写在WHERE子句里，会把原本的LEFT JOIN逻辑变相转为INNER JOIN（只有当logEntry符合条件或为NULL时才会保留，但如果原mdl_logstore_standard_log表中存在不符合条件的记录，会被直接过滤），而版本1是先筛选出符合条件的日志再做LEFT JOIN，两者返回的结果集可能不同，这点需优先注意。
• 效率优化场景：如果过滤条件能大幅缩减数据集（比如mdl_course经REGEXP过滤后仅保留少量记录），版本1的子查询过滤更稳妥：提前过滤掉无关数据，能减少后续JOIN操作的关联次数，尤其是当过滤字段（如mdl_course.idnumber）有索引时，这种优势更明显。
• 优化器自动调整：对于course.idnumber REGEXP这类针对左表的过滤条件，优化器通常会自动将其推送到JOIN之前执行，避免先关联大量数据再过滤，此时版本2的效率与版本1接近。但如果涉及复杂子查询或特殊场景，优化器可能无法正确推导，版本1的显式过滤会更可靠。

2. REGEXP正则表达式的执行次数问题 #

不会对每条重复记录执行。数据库优化器会识别出course.idnumber REGEXP 'some regex'是针对mdl_course表的过滤条件，会优先对mdl_course表执行一次正则匹配，筛选出符合条件的课程集合后，再进行后续的JOIN操作。后续因一对多关系产生的重复course记录，是JOIN后的结果，此时正则匹配已经完成，不会重复计算。即正则表达式只会对mdl_course表中的每条原始记录执行一次，与最终结果集的重复次数无关。

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