怎样分析MySQL 8.0公用表表达式CTE在底层的执行机制-mysql教程-PHP中文网

mysql 8.0的cte是优化器可重写的查询块，非独立执行单元；默认不物化，常被内联，多次引用可能重复计算；递归cte强制迭代执行并用临时表；验证方式为explain format=tree。

怎样分析mysql 8.0公用表表达式cte在底层的执行机制

MySQL 8.0 的 CTE 不是视图，也不生成物化临时表（默认情况下）

CTE 在 MySQL 8.0 中本质是语法糖 + 优化器可重写的查询块，不是独立的执行单元。它不会像 PostgreSQL 那样自动物化（除非显式加 /*+ MATERIALIZE */ 提示），也不会像早期 MySQL 视图那样被简单地文本展开——优化器会根据成本模型决定是否内联、是否物化、是否递归展开。

这意味着：写一个 WITH t AS (SELECT ...)，不等于“先算出 t 再用”，而更可能是“把 t 的定义直接塞进外层 WHERE/HAVING/JOIN 条件里一起优化”。

若 CTE 只被引用一次，且无递归、无副作用，MySQL 几乎总是选择内联（EXPLAIN 显示为 DERIVED 或直接消失）
若 CTE 被多次引用，又没加 MATERIALIZE，MySQL 仍可能重复计算（即“多次执行子查询”），而不是缓存结果
递归 CTE（WITH RECURSIVE）强制走迭代执行路径，底层用内部临时表暂存每轮结果，有 cte_max_recursion_depth 限制

如何验证 CTE 实际是否被内联或物化？看 `EXPLAIN FORMAT=TREE`

这是最直接的方式。MySQL 8.0+ 的 FORMAT=TREE 会清晰展示 CTE 的处理策略：

出现 > Materialize with deduplication 或 > Materialize —— 表示该 CTE 被物化（用了临时表）
出现 > Table scan on <derived></derived> 或直接嵌入到主查询树中 —— 表示已内联，未物化
递归 CTE 会显示 > Recursive loop on <cte_name></cte_name> 及迭代层级结构

例如：

EXPLAIN FORMAT=TREE
WITH cte1 AS (SELECT id FROM orders WHERE status = 'shipped')
SELECT * FROM cte1 JOIN users USING(id);

如果 orders 有合适索引，你大概率看到的是单层 join 树，cte1 消失不见——说明被内联了。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

`MATERIALIZE` 提示不是万能的，且受变量和版本限制

从 MySQL 8.0.22 起支持优化器提示 /*+ MATERIALIZE(cte_name) */，但它只在满足一定条件时才生效：

CTE 必须是 non-recursive；
不能含用户变量、存储函数、OUTFILE 等不可重入操作；
cte_max_recursion_depth 对它无影响，但 tmp_table_size 和 max_heap_table_size 会影响物化能否成功（超限会退回到磁盘临时表）；
即使加了提示，优化器仍可能忽略——比如发现内联代价更低，或物化后无法使用索引下推。

所以别盲目加 MATERIALIZE，先用 EXPLAIN FORMAT=TREE 看现状，再对比加提示后的执行树变化。

递归 CTE 的执行开销集中在迭代与临时表 I/O

递归 CTE 底层靠一个“工作表”（working table）循环存取数据：首轮执行 anchor member 得初始集，之后每轮用上一轮结果驱动 recursive member 执行，并将新行插入工作表，直到无新行产生或达到 cte_max_recursion_depth（默认 1000）。

每次迭代都是一次独立的查询执行，涉及临时表读写、去重（若用 UNION）、索引查找；
若 recursive member 缺少有效过滤条件（如没关联上 anchor 结果），极易产生笛卡尔爆炸；
工作表默认在内存（MEMORY 引擎），但行数多或字段大时会转成磁盘 InnoDB 临时表，性能断崖下跌。

典型陷阱：WITH RECURSIVE t(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM t) 没终止条件 → 直接触发 ERROR 3636 (HY000): Recursive query aborted after 1000 iterations。

真正要调优递归 CTE，重点不在“怎么写语法”，而在控制每轮输出规模、确保 recursive member 能走索引、预估最大迭代深度并设合理上限。物化、提示、索引覆盖这些常规手段，在这里都得让位于迭代逻辑本身的设计。