跳转到主要内容

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →
CTE 和派生表返回相同的行,所以两者之间的选择通常只关乎可读性。当优化器物化其中一个时,它就不再只关乎可读性了,而优化器这样做的频率比大多数文章所说的要高。本指南讲的是如何判断您得到了哪一种,以及 CTE 不适合的那些情况。 教程讲授语法本身:公共表表达式、递归 CTE 和派生表。 本页的每个执行计划都来自 1 台针对 Sakila 示例数据库运行的服务器。成本和行数估计来自 InnoDB 的抽样统计信息和您的 optimizer_switch 设置,所以您的数字会有所不同。可以沿用的是每个计划的形状。

规则:可合并或不可合并

CTE 要么被折叠进外层查询,要么先被构建为临时表,而您得到哪一种取决于 CTE 包含什么,而不是您引用它多少次。 主体是带 WHERE 和 JOIN 的普通 SELECT 的 CTE 会被合并,并从计划中完全消失:
没有 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,第二次引用复用同一个物化结果:
这才是这 2 种形式真正分道扬镳的地方。开销大的内层查询只运行一次,两处引用都读取其结果。写成派生表时,同一个查询会两次写出该子查询,并计算两次。

物化让您付出什么代价

物化的 CTE 是一张临时表,这意味着 2 件值得了解的事。 它放弃了基表的索引。一旦行位于临时表中,外层查询扫描的就是那张表。服务器可能会即时构建一个 auto_key,就像上面的联接那样,但那不是您在真实表上的索引。对比本页顶部的合并计划,那里外层条件到达了 PRIMARY。 下推缓解了这一点。derived_condition_pushdown 默认开启,它把能移动的条件移到内部,这就是为什么上面的 Filter: (count(0) > 200) 出现在 Materialize 节点内部而不是外部。它无法移动所有条件,所以当计划显示 CTE 产生的行数远多于结果保留的行数时,请自己把限制条件写进 CTE 内部。

CTE 何时不合适

  • 单个简短的步骤。 派生表把它留在使用它的地方。给一个只有 1 行代码的子查询命名,只会给读者增加一次跳转。
  • 优化器没有下推的过滤条件。 如果计划显示 CTE 物化了每一行而外层查询丢弃了其中大部分,请把过滤条件写进内部。
  • 针对索引的逐行工作。 相关子查询或联接可以在每次查找时使用索引。物化的 CTE 已经放弃了这一点。
当查询有 2 个或更多命名阶段、同一结果需要使用两次,或者替代方案是嵌套深到没人能看清它在哪里结束的子查询时,CTE 是合适的形式。 它也是这 2 种形式中更容易调试的一种。CTE 定义可以通过对它执行 SELECT 来单独运行,这能在您查看第二阶段之前告诉您第一阶段是否正确。把派生表从 FROM 子句中间抽出来手动运行,是每次都要手工做一遍的同样工作。

真实层级结构上的递归 CTE

Sakila 没有层级结构,所以本节使用自己的一张表。在示例数据旁边创建它,用完后运行 DROP TABLE employees;:
种子部分选出根节点,递归成员把表与已产生的行联接起来。depth 列既是答案也是守卫。
环是失败模式。 如果 A 向 B 汇报而 B 向 A 汇报,查询中没有任何东西会停下来,它会一直运行直到触及深度限制:
上面查询中的 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

CTE 先被求值,所以它指定的行在语句更改任何内容之前就已确定。

故障排除

另请参阅