MySQL 8.0.36嵌套WHERE IN子句的优化器提示指定问题

可能的原因及排查步骤 #

1. 子查询被判定为相关子查询 #

如果中间层的session_elements子查询引用了外层rubric_session_elements的任何列（哪怕是隐式引用），MySQL会将其视为相关子查询，而SEMIJOIN物化仅支持不相关子查询。

• 排查方式：检查子查询是否包含rse.xxx这类外层表的引用，若有则无法使用物化。

2. 统计信息过时导致优化器判断偏差 #

MySQL优化器依赖表的统计信息选择执行计划，若rubric_session_elements、session_elements或sessions的统计信息过时，可能导致优化器认为weedout的开销更低，忽略提示。

• 解决方式：执行以下命令更新统计信息后重新测试：

ANALYZE TABLE rubric_session_elements, session_elements, sessions;

3. QB_NAME的定位不准确 #

三层嵌套场景中，SEMIJOIN提示需要精准绑定到对应的子查询节点，若QB_NAME的位置或引用错误，优化器会忽略提示。

• 正确用法示例：

/*+ SEMIJOIN(@subq1 MATERIALIZATION) */
SELECT * FROM rubric_session_elements rse
WHERE rse.id IN (
    /*+ QB_NAME(subq1) */
    SELECT se.id FROM session_elements se
    WHERE se.session_id IN (
        SELECT s.id FROM sessions s WHERE s.status = 'active'
    )
);

或者将提示直接加在目标子查询上：

SELECT * FROM rubric_session_elements rse
WHERE rse.id IN (
    /*+ QB_NAME(subq1) SEMIJOIN(@subq1 MATERIALIZATION) */
    SELECT se.id FROM session_elements se
    WHERE se.session_id IN (
        SELECT s.id FROM sessions s WHERE s.status = 'active'
    )
);

4. 执行计划重写导致SEMIJOIN结构变化 #

MySQL可能将三层嵌套IN重写成多表JOIN链，原本的SEMIJOIN结构被拆解，导致物化提示无法生效。

• 排查方式：执行EXPLAIN FORMAT=JSON查看执行计划，重点查看semijoin_strategy字段，确认是否存在物化节点，以及子查询是否被重写为JOIN。

5. 版本特定bug #

MySQL 8.0.36可能存在多层嵌套SEMIJOIN物化的逻辑bug，导致优化器强制使用weedout。

• 排查方式：查看MySQL官方bug库确认是否有相同场景的报告；尝试升级到更高版本（如8.0.38）测试是否解决问题。

6. optimizer_switch参数组合问题 #

即使关闭了duplicateweedout=off，若materialization=off或derived_merge=on等参数影响了子查询结构，也会导致物化无法触发。

• 确认方式：执行以下命令查看参数状态：

SHOW SESSION VARIABLES LIKE 'optimizer_switch';

确保materialization=on，若需要可临时关闭derived_merge测试：

SET SESSION optimizer_switch = 'derived_merge=off';

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