你回想一下早几年写稍微复杂一点的分析SQL是不是每次都得先建临时表、再join几层子查询到了第三层嵌套自己都看不下去MySQL 8.0的通用表达式WITH出来后这个问题基本算是翻篇了。我最早接触CTE其实是在业务报表里当时一段长达两百行的嵌套子查询被逐步改写成多个WITH模块不但执行流程清爽了连后续加指标都省了大半力气。这篇文章想从实际使用的角度把MySQL 8.0里WITH子句的基本语法、普通CTE的用法、递归CTE的实现原理以及性能优化注意点系统梳理一遍重点侧重可复现的操作细节和踩坑记录适合正在学习MySQL进阶特性、或者准备把老项目迁移到8.0的同学参考。1. 从“能跑就行”到“优雅查询”为什么值得下功夫学 WITH1.1 一个改变我写SQL习惯的示例最开始用CTE是因为接手了一个报表模块那段核心SQL长这样一个大查询里套了两层临时子查询每个子查询又各自引用上一层的聚合结果结构几乎是三层嵌套。改动需求加个同比字段我在里面调了两小时改完都觉得只是碰巧能跑。后来我尝试用WITH重写每个中间结果集独立成一块再拼回主查询意外的收获是整体逻辑变得像搭积木每一块命名清楚后面维护的人不用再沿着括号往里钻。这里放个简化版对比。假设要统计每个分类下销量Top3的商品-- 旧方案嵌套子查询 SELECT t1.category_name, t1.goods_name, t1.sales_amount FROM ( SELECT c.name AS category_name, g.name AS goods_name, SUM(g.amount) AS sales_amount FROM goods_sales g JOIN category c ON g.category_id c.id GROUP BY c.id, g.id ) t1 WHERE ( SELECT COUNT(*) FROM ( SELECT c2.id, g2.id, SUM(g2.amount) AS s2 FROM goods_sales g2 JOIN category c2 ON g2.category_id c2.id GROUP BY c2.id, g2.id ) t2 WHERE t2.id t1.category_id AND t2.s2 t1.sales_amount LIMIT 1 ) 0;再换成CTE版本WITH sales_summary AS ( SELECT c.id AS category_id, c.name AS category_name, g.id AS goods_id, g.name AS goods_name, SUM(g.amount) AS sales_amount FROM goods_sales g JOIN category c ON g.category_id c.id GROUP BY c.id, g.id ) SELECT s.category_name, s.goods_name, s.sales_amount FROM sales_summary s LEFT JOIN sales_summary s2 ON s2.category_id s.category_id AND s2.sales_amount s.sales_amount WHERE s2.category_id IS NULL;是不是一眼就能看懂这就是CTE最大的价值它把SQL拆成逻辑清晰的模块命名即文档阅读顺序就是业务处理顺序大括号地狱从此不存在了。1.2 它解决的痛点和适用人群通用表达式本质上是给SQL增加了一个命名的临时结果集概念。但临时结果集并不只存在于执行期——WITH在生成执行计划时就已经被优化器识别可以被当作可复用的标准子结构进行优化。这一点和“直接创建临时表”完全不是一个思路。这个特性尤其适合几类场景复杂报表多指标需要基于同一份明细多次聚合用WITH定义一次底层数据后面所有分组都引用它。层级数据组织架构、菜单树、物料BOM清单这些天然带上下级关系的数据配合递归写法能一条SQL解决。数据清洗ETL过程中多步处理比如先过滤、再去重、再算指标每一步都用WITH表达逻辑全部留在最终SQL里审计起来也清楚。老SQL重构旧项目里大量嵌套子查询和临时表逐步替换为CTE后可维护性提升非常明显。2. WITH 子句的核心语法拆解照着写就行2.1 基础语法与执行特点MySQL 8.0的WITH语法官方描述为WITH cte_name [(column_name_list)] AS ( SELECT ... ) SELECT ...CTE可以单独存在也可以多个连续定义中间用逗号分隔。后面的CTE可以使用前面已经定义的CTE但是前面的CTE不能反向引用后面的CTE这是逻辑上的顺序依赖。一个容易忽略的点CTE列名可以在主体内显式指定别名并不总是必须写在SQL子句内部。比如WITH revenue_cte (dept_id, total_revenue) AS ( SELECT dept_id, SUM(revenue) FROM sales_data GROUP BY dept_id ) SELECT * FROM revenue_cte;这种做法在主体查询列名不清晰、或者聚合函数列名过长时特别好用还能顺便统一下游引用的字段名。多个CTE定义连写时可以这样组织WITH base_data AS ( SELECT id, product_id, sale_amount, sale_time FROM order_detail WHERE sale_time 2024-01-01 ), dept_summary AS ( SELECT d.manager_id, SUM(b.sale_amount) AS total FROM base_data b JOIN dept d ON b.dept_id d.id GROUP BY d.manager_id ) SELECT * FROM dept_summary;这里第二CTE直接引用了第一个CTE而且两个CTE模块的命名都表达了数据阶段执行时像流水线一样一层层传递数据整个链条很干净。2.2 派生表和 WITH 的差异别搞混派生表就是from后面的括号子查询它也能实现类似数据集的效果但二者有三个关键差别可读性派生表必须把结果内联在主查询括号里无法单独命名被多次引用CTE可以多次引用同一逻辑且模块可以重复出现在多个后续位置。可复用性CTE在同一个语句里可以引用多次。派生表如果被反复引用意味着那段子查询要重复写语句膨胀且容易出错。执行计划差异CTE在优化器中的处理与物化、合并策略相关MySQL8.0的优化器会评估CTE是否需要物化而派生表虽然也会被优化但代码层没法直接控制复用的粒度。我遇到过这样一个案例统计各部门销售额以及全公司销售额占比。用派生表写公司总额那个子查询要重复写两遍不仅冗长还必须保持两边同步。用CTE就不一样了WITH dept_total AS ( SELECT dept_id, SUM(sale_amount) AS total FROM sales_detail GROUP BY dept_id ), grand_total AS ( SELECT SUM(total) AS all_total FROM dept_total ) SELECT d.dept_id, d.total, ROUND(d.total / g.all_total * 100, 2) AS pct FROM dept_total d CROSS JOIN grand_total g ORDER BY pct DESC;这里dept_total被多次引用CTE做到了“一次定义多处使用”维护成本直线降低。提示如果CTE只在主查询里引用一次那它在语义上其实和派生表没太大差别。这时选择哪个主要就看阅读是否方便。追求极致性能时简单场景下不必为了“用特性而用特性”强上CTE。3. 递归 CTE 的进阶玩法让层级查询不再痛苦3.1 递归是怎么工作的三个关键部件MySQL 8.0支持WITH RECURSIVE也就是递归CTE。理解了它很多树形结构查询都能用一个SQL搞定。递归CTE的标准结构包含三个部分WITH RECURSIVE cte AS ( -- 初始查询anchor member产生递归起点 SELECT id, name, parent_id FROM organization WHERE parent_id IS NULL UNION ALL -- 递归查询recursive member逐层向下扩展 SELECT o.id, o.name, o.parent_id FROM cte JOIN organization o ON o.parent_id cte.id ) SELECT * FROM cte;初始查询先取根节点递归查询负责把当前层级的子节点找到然后不断重复直到JOIN不到任何新数据为止。整个递归过程类似“广度优先展开”。UNION和UNION ALL的选择要特别注意UNION会去重避免出现重复行逻辑上更安全UNION ALL性能更好但如果数据本身有环比如父节点指向孩子再指向祖先不加控制就会无限递归。实际业务数据里环并不常见但自引用表的历史数据很容易出现脏数据所以生产环境要权衡。3.2 实操案例组织架构与层级路径假设有员工表CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), manager_id INT NULL );现在需要输出每个员工的完整汇报链路比如“王总 李经理 张三”并且要求同时算出层级深度。递归CTE可以这样写WITH RECURSIVE emp_path AS ( SELECT id, name, manager_id, name AS path_str, 1 AS depth FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, CONCAT(ep.path_str, , e.name), ep.depth 1 FROM employees e JOIN emp_path ep ON e.manager_id ep.id ) SELECT id, name, depth, path_str FROM emp_path ORDER BY depth, id;运行结果会得到从顶层到每个节点的完整路径。上面这个SQL在生产项目里可以直接用于导出组织架构树、汇报线展示、甚至权限继承的模拟。如果你要反向查询某个节点的所有子节点也一样简单只要把递归方向调换从指定节点往下找即可。这里不放代码了核心思路就是把锚点换成指定id递归部分把父和子关系反过来。3.3 防止无限递归常见限制手段MySQL的递归CTE默认有个循环次数限制cte_max_recursion_depth默认值1000用于防止异常情况下无限执行。这个参数对很多表不够用比如深层级组织架构可能超过1000层那就需要按实际情况调高。调整方式SET SESSION cte_max_recursion_depth 5000;这个参数是会话级的不会立刻影响其他事务安全性较好。如果出现报错“Recursive query aborted after 1001 iterations”第一反应先看是不是真是层级深导致第二反应再看是否有环路数据。如果是脏数据形成环调大参数只能延长超时正确做法是修数据或加访止条件。注意递归中如果发现某条路径反复出现相同节点基本就是数据环了。可以在递归查询里加一个node_path字段用FIND_IN_SET判断当前节点是否已经出现过若出现则终止扩展。这是一个省事的防御性写法。4. 实操过程用 WITH 优化复杂查询的全流程记录4.1 典型场景商品类目汇总与环比分析竞赛环节里我们用一个模拟场景完整走一遍优化流程。业务需求统计每个一级类目下各子类目的销售额以及该子类目在上月同期的销售额最终只输出当月排名前三的子类目。原始SQL结构SELECT c1.id AS cat1_id, c1.name AS cat1_name, c2.id AS cat2_id, c2.name AS cat2_name, SUM(o.amount) AS cur_month_amount FROM orders o JOIN category c2 ON o.cat2_id c2.id JOIN category c1 ON c2.parent_id c1.id WHERE o.create_time BETWEEN 2025-03-01 AND 2025-03-31 GROUP BY c1.id, c2.id ORDER BY cur_month_amount DESC LIMIT 3;这是只算当月数据的情况。如果加上上月同期的销售额查找对应的上月订单明细势必造成大量重复扫描。我当时的改造方法是先用WITH把订单明细拆成“当月”和“上月”两个模块再分别聚合最后join。首先定义两个CTEWITH cur_month_orders AS ( SELECT cat2_id, SUM(amount) AS cur_amount FROM orders WHERE create_time 2025-03-01 AND create_time 2025-04-01 GROUP BY cat2_id ), last_month_orders AS ( SELECT cat2_id, SUM(amount) AS last_amount FROM orders WHERE create_time 2025-02-01 AND create_time 2025-03-01 GROUP BY cat2_id ) SELECT c1.id AS cat1_id, c1.name AS cat1_name, c2.id AS cat2_id, c2.name AS cat2_name, COALESCE(cur.cur_amount, 0) AS cur_month_amount, COALESCE(last.last_amount, 0) AS last_month_amount FROM category c2 JOIN category c1 ON c2.parent_id c1.id LEFT JOIN cur_month_orders cur ON cur.cat2_id c2.id LEFT JOIN last_month_orders last ON last.cat2_id c2.id ORDER BY cur_month_amount DESC LIMIT 3;这样重构后订单表被扫描的次数从原来可能需要多次扫描变成两次按不同时间段扫描聚合目的明确、逻辑清晰也给了优化器更明确的“二段式聚合”路径。4.2 用执行计划验证优化效果改造完成后我用EXPLAIN FORMATTREE看了一眼执行计划CTE部分被标记为具体的子节点能直观看到优化器是把它当作物化临时结果还是作为视图合并处理。MySQL 8.0的优化器对CTE有“合并”和“物化”两种策略。如果CTE简单优化器多半会把它合并到外层查询如果CTE结构复杂、被多次引用优化器则可能将其物化为内部临时表。实际测试中我发现一个坑如果CTE被多次引用且数据量很小时物化成本反而更高。比如一个只有十几行结果的CTE被JOIN了5次物化之后再扫描代价也不高但如果是几百MB数据量的CTE被引用两次物化带来的临时表IO开销就很明显。这种情况下我的处理方式是减少CTE引用次数或者提前在下层过滤条件做更彻底的裁剪。也就是说CTE内部能过滤就过滤别把全量明细带上来——这点和普通SQL优化思路完全一致。4.3 参数层面的可调空间递归CTE的迭代深度限制需要考虑普通CTE同样需要关注tmp_table_size和max_heap_table_size。CTE物化产生的临时表如果没有足够的内存空间就会落到磁盘性能断崖下降。我遇到过一张只有300万行的订单明细CTE物化后临时表落盘整个查询从0.3秒变成9秒。当时排查时看了状态变量Created_tmp_disk_tables发现数量猛增。后来优化方案是给SQL增加前置过滤条件让CTE内数据量从300万降到40万临时表内存问题迎刃而解。如果你在线上环境遇到CTE突然变慢先查这两个状态变量SHOW SESSION STATUS LIKE Created_tmp_tables; SHOW SESSION STATUS LIKE Created_tmp_disk_tables;根据落盘临时表数量的变化能快速判断是不是物化了。5. 常见问题与限制核对看完少走弯路5.1 频繁踩到的错误和排查思路错误场景报错信息或表现形式排查方向递归CTE超过深度限制Recursive query aborted after 1001 iterations检查cte_max_recursion_depth配置或确认是否存在环CTE名称冲突Every derived table must have its own alias确认CTE名称和表名、别名没有重复引用顺序错误Unknown column ... in ...检查是否引用了一个尚未定义的CTE递归成员引用非递归成员Invalid reference to recursion确认递归部分引用的表一定是CTE本身而不是外部表递归成员数据列不一致The column count of recursive query is not the same确认锚点和递归部分的SELECT列数一致类型兼容物化临时表落盘查询突然变慢临时表落盘状态变量增长优化CTE内部过滤范围或调整tmp_table_size我特别提一下“The column count of recursive query is not the same”这个错误。递归CTE要求锚点查询和递归查询的列数量严格一致同时列类型也要兼容。比如锚点是INT递归部分拼接出了一个字符串立刻报错。这是我在写组织架构路径时遇到的解决方案是让锚点也输出相同类型的路径字段保持结构一致。5.2 递归 CTE 的生产使用建议递归在数据量大的树状结构里执行效率并非总能如人意。每层迭代都需要进行一次表扫描加JOIN递归结果集当树的深度到几十层时即使每层数据量不大总的开销也不小。我之前测过一张约10万员工的BOM层级表递归CTE查单条物料链路大约耗时几十毫秒但一次性查询全量树的路径代价就很高。生产环境数据画像如下物料编码表约8万行父子关系约15万条。全树展开路径的递归SQL执行时间约4秒主要是迭代次数多且每层都需要扫描整张关系表。因此线上使用递归CTE时我有几个习惯先确认业务数据的真实深度并设置合理的cte_max_recursion_depth而不是盲目调大。锚点尽量先做条件过滤让递归的起点数据量尽量小这样每一层JOIN规模都会小很多。如果只是统计层级数量和深度考虑在应用层用程序遍历一次再批量写入一张冗余表比每次SQL递归要稳。涉及多级BOM的实时查询建议用物化路径方案替代递归别让数据库每条查询都做递归展开。5.3 WITH 子句在 MySQL 8.0 的适用边界通用表达式不是优化银弹它有明确的适用边界。首先MySQL 8.0中的WITH不是永久视图每条语句内的CTE生命周期只属于那条语句不能跨语句复用。其次CTE内部的索引使用逻辑和正常表一样如果CTE内部没有可用的索引外层JOIN时的性能依然不好。第三同一个查询里如果存在多个大CTE且它们之间没有相互依赖优化器可能会并行物化这在资源紧张时会放大IO压力。我还遇到过一个问题CTE内LIKE查询无法利用索引的情况。后来在CTE内部加全文索引的思路也被执行计划否决了。最后我换了个做法先建一张临时表添加索引再执行查询反而更快。这提醒我一点CTE清晰表达的是逻辑关系但物理优化层面临时表的显式索引在某些场景比CTE贪图方便更值得依赖。CTE适合用来替代嵌套子查询、减少重复计算、增强查询可读性而不适合无脑把所有业务都写成巨型WITH块让优化器承担巨大压力。6. 个人实操体会与一个小建议如果只让我说一条最重要经验那就是CTE不是用来炫技的它最大的价值是让复杂SQL具有可读性和可维护性。我见过有人把一个简单的两表JOIN也套上三个WITH反而比直接JOIN更难读。做事要权衡。另外一个非常值得推荐的小练习方式从你自己的业务库里找一条最痛的三层子查询把它改写成WITH版本然后对比改写前后的执行时间和表扫描次数。不用刻意去建复杂的树形数据普通报表数据就能体会它带来的结构便利。你之前觉得无从下手的SQL改着改着就会有一种“原来还能这么写”的通透感。MySQL 8.0的通用表达式确实给日常开发带来了不少便利但真正用好它还是要把注意力放在业务逻辑和数据特性上。SQL写得好不好归根结底是逻辑是否清晰、数据是否被合理利用工具特性只是辅助。希望这篇文章能帮你少踩一点坑把WITH子句用到真正该用的地方。