VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
optimizer_switch 设置,所以您的数字会有所不同。可以沿用的是每个计划的形状。
规则:可合并或不可合并
CTE 要么被折叠进外层查询,要么先被构建为临时表,而您得到哪一种取决于 CTE 包含什么,而不是您引用它多少次。 主体是带WHERE 和 JOIN 的普通 SELECT 的 CTE 会被合并,并从计划中完全消失:
GROUP BY、DISTINCT、LIMIT、UNION 或窗口函数的 CTE 无法合并,因为外层查询的条件在这些操作之前应用时并不等价。这样的 CTE 会被物化。
下面的计划展示第二种情况。请把它们理解为关乎 CTE 的内容,而不是关乎 WITH 这个词。
检查您得到了哪一种
EXPLAIN FORMAT=TREE 会直接写出 Materialize 这个词。默认的 EXPLAIN 则不会:对本页的查询,它的 Extra 列不带这个词。
Materialize CTE counts,尽管这个 CTE 只被引用了一次。是其中的 GROUP BY 强制了这一点,服务器把行写入临时表再读回来。
包含相同 GROUP BY 的派生表得到相同的计划。 写成 FROM (SELECT rating, COUNT(*) ... GROUP BY rating) AS counts,除了节点名称之外,树完全相同,成本也相同。所以在单次引用的查询上,CTE 并不比派生表强:这 2 种形式只在可读性上不同,优化器对它们一视同仁。
两次引用同一个 CTE,措辞就会变成 if needed,第二次引用复用同一个物化结果:
物化让您付出什么代价
物化的 CTE 是一张临时表,这意味着 2 件值得了解的事。 它放弃了基表的索引。一旦行位于临时表中,外层查询扫描的就是那张表。服务器可能会即时构建一个auto_key,就像上面的联接那样,但那不是您在真实表上的索引。对比本页顶部的合并计划,那里外层条件到达了 PRIMARY。
下推缓解了这一点。derived_condition_pushdown 默认开启,它把能移动的条件移到内部,这就是为什么上面的 Filter: (count(0) > 200) 出现在 Materialize 节点内部而不是外部。它无法移动所有条件,所以当计划显示 CTE 产生的行数远多于结果保留的行数时,请自己把限制条件写进 CTE 内部。
CTE 何时不合适
- 单个简短的步骤。 派生表把它留在使用它的地方。给一个只有 1 行代码的子查询命名,只会给读者增加一次跳转。
- 优化器没有下推的过滤条件。 如果计划显示 CTE 物化了每一行而外层查询丢弃了其中大部分,请把过滤条件写进内部。
- 针对索引的逐行工作。 相关子查询或联接可以在每次查找时使用索引。物化的 CTE 已经放弃了这一点。
SELECT 来单独运行,这能在您查看第二阶段之前告诉您第一阶段是否正确。把派生表从 FROM 子句中间抽出来手动运行,是每次都要手工做一遍的同样工作。
真实层级结构上的递归 CTE
Sakila 没有层级结构,所以本节使用自己的一张表。在示例数据旁边创建它,用完后运行DROP TABLE employees;:
depth 列既是答案也是守卫。
WHERE oc.depth < 100 就是防止这种情况的东西。它把无界的遍历变成有界的,而且无论数据是否干净都有效。
错误消息建议提高 cte_max_recursion_depth,这只有在递归确实会终止、只是需要比默认的 1,000 更多轮次时才正确。一个合法的 1,500 步序列会遇到同样的错误,此时 SET SESSION cte_max_recursion_depth = 5000 是正确的答案。对于有环的数据,这是错误的答案:查询仍然永远不会结束,只是失败得更晚。
可用性与术语
CTE 在 MySQL 8.0 中引入,并存在于此后的每个版本中,包括 8.4 和 9.x。对WITH 子句回应 ERROR 1064 的服务器早于 CTE 的引入;请用 SELECT VERSION() 检查。
标准把递归 CTE 的第一部分称为锚成员,第二部分称为递归成员。教程把第一部分称为种子,两者是同一回事,而 MySQL 自己的消息使用标准术语。
CTE 可用于 INSERT、UPDATE 和 DELETE
故障排除
另请参阅
- MySQL 中的子查询与 JOIN — 与之竞争的另一种形式
- MySQL GROUP BY 性能 — CTE 可以帮您避免的临时表
- MySQL 视图 — 改为把命名查询持久化为模式对象
- 公共表表达式 — 教程,如果您想先学语法