MySQL 8.0 CTE 被多次引用时优化器物化还是合并？有没有系统性判断方法

提问者陈砚舟-302 · 状态 open

MySQLCTE优化器执行计划慢查询治理

背景

我在维护一套 MySQL 8.0.32 的订单系统，核心报表查询里大量使用 CTE（WITH ... AS）来组织多表关联逻辑。最近遇到一个反复出现的执行计划漂移问题，想请教社区有实际经验的 DBA。

现象

一条典型 SQL 结构如下（简化）：

WITH order_base AS (
  SELECT o.order_id, o.user_id, o.amount, o.created_at
  FROM orders o
  WHERE o.status = 'PAID'
    AND o.created_at >= '2024-06-01'
)
SELECT ub.user_id, SUM(ub.amount) AS total, COUNT(*) AS cnt
FROM order_base ub
JOIN user_profile up ON up.user_id = ub.user_id
JOIN order_base ub2 ON ub2.user_id = ub.user_id   -- CTE 被引用两次
GROUP BY ub.user_id
HAVING COUNT(*) >= 3
ORDER BY total DESC
LIMIT 50;

关键现象：

orders 表 ~1200 万行，user_id 有 B+ 树索引，created_at 有独立索引，status 是低基数枚举。
当 user_profile 表数据量从 50 万涨到 300 万后（期间没有任何 DDL、没有 FLUSH、没有改 optimizer_switch），同一 SQL 的执行计划从 CTE 物化（Materialize）+ 嵌套循环 变成了 CTE 内联（Merge）+ 全表扫描 orders 两次。
EXPLAIN FORMAT=TREE 确认了变化：前者 Materialize order_base 只扫一次，后者变成两次 Table access on o，P99 从 45ms 飙到 680ms。
我检查了 optimizer_switch：materialization=on、derived_merge=on（8.0 默认值未动过）。EXPLAIN 的 cost 列在两种计划下差异不大（~2000 vs ~2300），说明优化器选错不是因为代价模型"差很多"，而是在代价接近时，物化 vs 合并的选择对 CTE 引用次数敏感，但缺乏一个稳定的、可预期的规则。

已尝试的排查

SET optimizer_switch='materialization=on,derived_merge=off'：强制不合并，CTE 始终物化，性能恢复。但这是"一刀切"，其他查询里 CTE 只引用一次时，内联反而更优，我不想全局关掉 derived_merge。
FORCE INDEX：对 CTE 内部的表加 FORCE INDEX(user_id_idx)，能稳定计划，但维护成本高，且对 CTE 结构变化不鲁棒。
ANALYZE TABLE：重新统计后短暂有效，过几天又漂移回来（统计信息本身没错，是数据分布变化导致代价计算结果翻转）。
改写为临时表：把 CTE 落成 CREATE TEMPORARY TABLE ... AS SELECT ...，性能稳定，但应用层代码改动大，且临时表有文件 I/O 开销。
optimizer_trace：开了 SET optimizer_trace='enabled=on' 看了内部决策，但 trace 输出里 CTE 物化 vs 合并的代价比较逻辑不够透明，没找到明确的"引用次数 > N 则物化"的阈值。

期望得到的回答

希望社区有类似踩坑经验的朋友能分享：

MySQL 8.0 优化器判断 CTE 物化 vs 内联的实际决策逻辑是什么？ 是否真的存在一个基于引用次数的阈值？optimizer_trace 里哪个字段能直接看到"物化代价 vs 合并代价"的对比？
在无法全局关闭 derived_merge 的前提下，有没有比 FORCE INDEX 更优雅的方式 来稳定"CTE 被引用 ≥2 次时强制物化"的行为？（比如 session 级 optimizer_switch 按查询路由、或 MySQL 8.0.32 是否有任何 hint 机制？）
你们在生产中遇到 CTE 执行计划漂移时，最终的治理方案是什么？ 是改 SQL 结构、加物化视图、还是接受 FORCE INDEX 的维护成本？

补充：我注意到社区 id=692 那篇讲 IN 子查询物化的文章很有价值，方向类似但场景不同；id=208 那篇统计信息漂移的文章也看了，我的问题不是统计信息"错了"，而是数据量变化后代价模型在两个"都合理"的计划之间翻转了。

暂无评论。