早几年我还在用 MySQL 5.7 的时候最怕的不是写 SQL而是改别人的 SQL。尤其是一层套一层的嵌套子查询括号三层起步缩进全凭心情逻辑输出全靠“从最里面往外猜”。后来项目升到 MySQL 8.0我开始大规模把嵌套子查询改写成CTECommon Table Expression公共表表达式一年下来最大的感受就是四个字清爽、好改、不容易错。这篇文章就把我在真实业务里用 CTE 的经验完整摊开包括语法基础、典型场景改造、性能实测和踩过的坑写给那些每天跟 SQL 打交道、又被子查询嵌套折磨过的开发同学。1. 嵌套子查询到底让人多抓狂1.1 三层嵌套的“逆向阅读”体验先说一个几乎所有 RDBMS 都会遇到的现实问题嵌套子查询天生就是倒着读的。SQL 的执行顺序不是从第一行往下而是先看 FROM、再看 WHERE碰到子查询时还要跳进括号里一层一层从最内层开始算。人的阅读习惯是从上往下、从左往右这种“先读里面、再读外面”的顺序直接导致大家看复杂 SQL 时基本靠猜。我给你看一个很常见的例子销售表按月份存了销售记录现在要统计所有“高于当月该地区平均值”的销售员分布。SELECT region_id, COUNT(*) AS high_cnt FROM ( SELECT sales_id, region_id, total_amount FROM sales_record WHERE month 2025-03 ) sr JOIN ( SELECT region_id, AVG(total_amount) AS avg_amount FROM sales_record WHERE month 2025-03 GROUP BY region_id ) ra ON sr.region_id ra.region_id WHERE sr.total_amount ra.avg_amount GROUP BY sr.region_id;这段 SQL 的逻辑其实不复杂先圈出三月的销售记录再算各地区平均业绩最后把高于平均的人挑出来。但你看这段代码的时候得先盯住第 2 行的sr再跳到第 8 行的ra然后在脑子里手动完成两个派生表 JOIN 之后才知道sr.total_amount ra.avg_amount到底是在比什么。这还是两层如果业务再复杂一点比如加上“只统计达标团队里排名前 20 的人”括号直接飚到四层我敢打赌放三天之后再回来看你自己都解释不清当初为什么这么写。1.2 可读性差不是小事是会出 bug 的很多人觉得可读性差无非是“看着费劲”忍一忍就过去了。实际上可读性差的 SQL 有一连串连锁反应CR 评审形同虚设。评审的人根本看不出你嵌套里的WHERE到底是作用在哪个结果集上最后只能点个“通过”风险全留给线上。优化无从下手。查得慢的时候你连这条 SQL 在算什么都不知道更别提分析执行计划只能全表撸一遍。改需求等于重写。产品说“平均线改成中位数”你得把最内层的维度和外层引用全部重审稍微漏一个括号就改错。同一个业务口径被复制多份。比如month 2025-03这个条件在嵌套写法里出现了两次一旦下个月要切到2025-04漏改一处结果就悄悄错了。这些不是“风格问题”是实打实的维护成本和线上事故源。我见过不止一次线上报表数据对不上最后查下来就是嵌套子查询里某层过滤条件写重或者写漏了而那个文档早就没人看得懂。1.3 最容易被写烂的几类场景结合我自己的经验下面这几类 SQL 最容易长成“俄罗斯套娃”也是后来我重点改造的对象场景为什么容易嵌套变深多级聚合统计先明细聚合再在聚合结果上做二次聚合天然要多层 FROM同表多次 JOIN同一张表既要做平均值又要做最大值被迫写多个子查询Top N 分组排名先算排名窗口函数在外面再过滤排名传统写法就叠两层树形/层级数据组织架构、分类树、多级分销手写递归容易写到怀疑人生数据清洗转换去重、补齐、过滤前后衔接每一步都想包一层子查询这些场景的共同特点是每一层都有独立的业务含义。而嵌套子查询最大的问题恰恰是把这些独立含义“压缩”进了括号里让每一层都变得不可见、不可复用、不可单独调试。对症下药的办法就是把每一层拆成一个有名字的临时结果集这就是 CTE 的核心价值。2. CTE 是什么、怎么写才顺手2.1 基础语法WITH 一句顶十层括号CTE 的语法非常简单核心就是一个WITH关键字WITH cte_name AS ( SELECT ... ) SELECT ... FROM cte_name WHERE ...;WITH后面可以跟一个或多个临时结果集每个结果集都有名字后面的查询可以像查普通表一样引用它们。还是刚才那个销售例子用 CTE 重构之后是这样WITH month_sales AS ( SELECT sales_id, region_id, total_amount FROM sales_record WHERE month 2025-03 ), region_avg AS ( SELECT region_id, AVG(total_amount) AS avg_amount FROM month_sales GROUP BY region_id ) SELECT ms.region_id, COUNT(*) AS high_cnt FROM month_sales ms JOIN region_avg ra ON ms.region_id ra.region_id WHERE ms.total_amount ra.avg_amount GROUP BY ms.region_id;注意两个细节第一month_sales定义了三月销售明细第二region_avg直接在引用month_sales的基础上算平均值。整个 SQL 的阅读顺序从“从里往外猜”变成了“从上往下读”每一层在干什么名字写得清清楚楚。MySQL 的 CTE 是从8.0开始支持的8.0.14 之后又加了两个优化提示词后面性能部分细说。MariaDB 从 10.2 起也支持 CTE如果你的老项目在 MariaDB 上同样可以直接用。2.2 链式 CTE一层一层搭积木我实际写业务 SQL 时最常用的不是单层 CTE而是链式 CTE——后一个 CTE 引用前一个 CTE一层层往下搭。这比嵌套子查询舒服太多了因为每层都独立定义都有名字出 bug 时你可以单独把某一层摘出来跑一下看看结果对不对。WITH valid_users AS ( SELECT user_id, register_time FROM users WHERE status ACTIVE ), paid_orders AS ( SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders WHERE status PAID GROUP BY user_id ) SELECT vu.user_id, vu.register_time, po.order_cnt, po.total_amount FROM valid_users vu LEFT JOIN paid_orders po ON vu.user_id po.user_id ORDER BY po.total_amount DESC;这类写法在报表类需求里几乎是标准答案先圈定用户范围再算订单指标最后关联输出。每一层都是独立的一段业务逻辑评审的人看到名字就知道这层在干嘛调试时也可以把SELECT vu.user_id...改成SELECT * FROM valid_users单独验证。有个小习惯非常推荐CTE 列表之间用英文逗号分隔最后一个 CTE 后面不带逗号直接跟主查询。这个逗号位置是新手最容易报错的地方错误提示通常很笼统报错时第一个检查项就是这个。2.3 递归 CTE处理层级数据的终极大招CTE 还有个杀手级能力是嵌套子查询完全替代不了的递归。语法上就多一个RECURSIVE关键字但能解决组织架构、商品分类、树形菜单、多级分销这类“层级数据”的查询。WITH RECURSIVE emp_tree AS ( -- anchor递归起点通常是顶层节点 SELECT emp_id, emp_name, manager_id, 1 AS lvl FROM employee WHERE manager_id IS NULL UNION ALL -- 递归成员每次把下一层拼进来 SELECT e.emp_id, e.emp_name, e.manager_id, et.lvl 1 FROM employee e INNER JOIN emp_tree et ON e.manager_id et.emp_id ) SELECT emp_id, emp_name, manager_id, lvl FROM emp_tree;理解递归 CTE 只需要抓三个点anchor 查询先抓出第一批数据这里是总经理递归成员通过JOIN把员工表和已经产生的 CTE 结果连接找到每个上级的下属UNION ALL 控制每一轮递归的结果追加到前面直到某轮没有新数据产生递归自然终止。写递归 CTE 有几个硬性规则容易踩坑。一个是UNION ALL前面必须是 anchor后面必须是递归成员顺序不能反。另一个是递归成员里对 CTE 的引用有次数限制不能在一个递归成员里引用自己两次否则优化器会报错这种情况往往需要调整思路改成先筛出子集再递归。还有一个是 MySQL 对递归层数有默认上限下一节细聊防止死循环把数据库打爆。2.4 CTE 和派生表Derived Table到底差在哪很多人会问CTE 不就是给子查询起了个名字吗跟 FROM 后面的派生表有什么区别准确地说语法上 CTE 和派生表产生的结果差异不大但写法和使用体验差别非常大。区别主要在三点作用域可复用。派生表只能在它自己的 FROM 子句里用一次CTE 可以被同一语句里的多个地方引用甚至被多个后续 CTE 引用。定义与使用分离。派生表必须“现写现用”想用两次就得写两遍CTE 只需要定义一次后面主查询和别的 CTE 都能引用天然避免复制粘贴同一个条件导致的不一致。递归能力。派生表完全做不到递归这是 CTE 独有的。所以我的判断标准很简单只要一段子查询在我这条 SQL 里出现两次以上或者它有独立的业务含义值得起个名字我就用 CTE 把它抽出来。哪怕只用一次如果嵌套很深导致主查询读起来费劲我也会顺手抽成 CTE。3. 四个高频业务场景改造实录3.1 多级聚合统计先有明细再出结论报表需求里最经典的就是“先圈范围、再算汇总、最后对比”。拿员工薪资举例老板想知道每个部门里“薪资高于部门平均水平”的人占多少还要顺带看每个人的具体差幅。嵌套写法得写两遍AVG(salary)相关的子查询而 CTE 可以直接把部门均值定义一次主查询里反复用。WITH dept_stats AS ( SELECT dept_id, AVG(salary) AS avg_salary, MAX(salary) AS max_salary FROM employee GROUP BY dept_id ) SELECT e.emp_id, e.emp_name, e.dept_id, e.salary, ROUND(e.salary - ds.avg_salary, 2) AS diff_from_avg, ROUND(e.salary / ds.max_salary * 100, 2) AS pct_of_max, CASE WHEN e.salary ds.avg_salary THEN OVER ELSE BELOW END AS flag FROM employee e LEFT JOIN dept_stats ds ON e.dept_id ds.dept_id;这条 SQL 主管看到第一眼就知道要干嘛dept_stats是部门统计口径主查询做员工明细和部门统计的关联。如果以后要改口径比如平均值改成中位数、最大值改成最小值只需要改dept_stats这一处不用碰主查询。这在嵌套子查询时代是难以想象的因为同样的AVG(salary)你写了几个子查询就得原样改几个位置漏一个就是静默的脏数据。3.2 同表多次关联再也不怕复制粘贴另一个高频场景是一张表要和“它自己的聚合结果”比来比去。比如电商平台要按用户维度算“未支付订单数”然后找出未支付单量超过 5 单的用户以及他们在所有超标用户中的排名。WITH unpaid_orders AS ( SELECT user_id, COUNT(*) AS unpaid_cnt FROM orders WHERE status UNPAID GROUP BY user_id ) SELECT u.user_id, u.name, uo.unpaid_cnt, RANK() OVER (ORDER BY uo.unpaid_cnt DESC) AS unpaid_rank FROM users u JOIN unpaid_orders uo ON u.user_id uo.user_id WHERE uo.unpaid_cnt 5;如果不提前抽出unpaid_orders你很可能要把“过滤 UNPAID 状态并分组计数”这段逻辑写两遍一遍算 5的过滤条件一遍放在窗口函数的PARTITION BY外面做排序基数。这个场景里 CTE 的“定义一次、多处使用”优势体现得非常充分——同一业务口径不会被复制成两份也就没有复制之后改漏一处的隐患。群里的同学特别喜欢拿这类 CTE 和窗口函数配合。窗口函数本身就是“先算明细再在外面包一层”的逻辑把它放在 CTE 里外面再干净地过滤条件比传统子查询再包一层优雅太多。比如“每个部门薪资前两名”WITH ranked_emp AS ( SELECT emp_id, dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employee ) SELECT dept_id, emp_id, salary FROM ranked_emp WHERE rn 2;3.3 递归组织架构一套 SQL 查到底之前不少同学在 5.7 时代处理组织架构要么用程序代码分批查询再拼树要么借助化路径字段要么上存储过程循环。这些方案都存在性能开销或架构复杂度。有了递归 CTE直接一条 SQL 把整棵子树捞出来深度自动一层层算出来。我做一个具体案例把电商的分销层级查出来同时带上每个层级的深度方便前端按缩进渲染。WITH RECURSIVE distribute_tree AS ( SELECT id, parent_id, nickname, 1 AS lvl, CAST(id AS CHAR(200)) AS path FROM member WHERE parent_id IS NULL UNION ALL SELECT m.id, m.parent_id, m.nickname, dt.lvl 1, CONCAT(dt.path, ,, m.id) FROM member m JOIN distribute_tree dt ON m.parent_id dt.id ) SELECT lvl, id, nickname, path FROM distribute_tree ORDER BY path;这里的path字段我做了两件事一是把从根到当前节点的路径拼出来二是用ORDER BY path实现“深度优先”的树形排序。这个技巧在处理无限级分类时特别实用因为字符串路径排序天然保证了父子节点连续前端拿到结果直接循环渲染成树结构不用再二次加工。注意 MySQL 递归 CTE 里字符串拼接要用CAST统一类型否则报错比较隐晦这个坑我印象很深。3.4 配合 UPDATE / DELETE 做数据清理CTE 不只用于 SELECT。MySQL 8.0 里WITH也可以放在UPDATE和DELETE前面适合“先圈定要处理的行再批量修改”的场景。比如清理线上 90 天前的访问日志但又想先把规则定义清楚再删除避免误删。WITH old_logs AS ( SELECT id FROM access_log WHERE created_at NOW() - INTERVAL 90 DAY AND sample_flag 0 ) DELETE FROM access_log WHERE id IN (SELECT id FROM old_logs);这段 SQL 的价值在于你要删除的范围、删除的附加条件全部集中在old_logs这个定义里。万一产品说“90 天改成 180 天”只改一个地方。如果是老版本 MySQL只能写成DELETE ... WHERE id IN (SELECT ...)条件一多就又是套娃。这里有个重要的实操注意点先用 SELECT 验证 CTE 圈出的行数确认无误后再改成 DELETE。任何 DML 前先验证结果集是我给自己定下的铁律尤其是涉及按时间批量清理这类高危险操作时。4. 性能CTE 真的比嵌套子查询慢吗4.1 优化器的合并Merge与物化Materialize很多同学不敢用 CTE最大的顾虑就是“会不会多生成一张临时表、性能变差”。这个担心可以理解但对 MySQL 8.0 来说需要先理解优化器对待 CTE 的两种方式合并Merge如果 CTE 只被引用一次优化器大概率会把它直接“展开”进主查询本质上跟写子查询是一样的执行计划不存在额外的临时表开销。物化Materialize如果 CTE 被引用多次或者递归查询必须物化优化器会把它先算成一张内部临时表后续引用直接读这张表。换句话说CTE 不是“一定会物化”大多数单引用的 CTE 实际执行计划和等价子查询几乎没有差别。真正需要关心的是被引用多次的 CTE它很可能物化成一张没有索引的临时表如果这个临时表行数非常大和主表 JOIN 时可能造成性能回退。MySQL 8.0.14 之后给了我们两个手工干预的武器-- 强制合并告诉优化器别物化直接展开 WITH cte AS NOT MATERIALIZED ( SELECT ... ) SELECT ... FROM cte ...; -- 强制物化明确要求先生成临时表 WITH cte AS MATERIALIZED ( SELECT ... ) SELECT ... FROM cte ...;我实际工作中用NOT MATERIALIZED的场景偏多比如 CTE 本身很小、引用一次、希望优化器直接展开而MATERIALIZED更常用于“这个 CTE 计算很重但我后面要引用两次不希望算两遍”的情况。不过这两条只是提示词优化器在某些情况下有最终决定权不能当绝对开关用。4.2 同一个 CTE 被引用两次会不会执行两遍这是群里问得最多的问题。答案是同一语句里同一个 CTE 被引用多次时MySQL 通常会物化一次然后多次读取。所以不存在“定义一次用两次就白算一遍”的问题这正是 CTE 相对子查询最大的性能优势——嵌套子查询如果同一个子查询写两遍优化器可能无法识别出它们完全相同极大概率会算两遍。举例来说你要同时算“每个部门平均薪资”和“低于部门平均薪资的人数比例”这两个指标都依赖同一个部门平均值。用 CTE 定义dept_stats一次主查询里多个地方引用MySQL 大概率只物化一次dept_stats比嵌套写法里复制两遍AVG子查询划算得多。这一点我在实际压测里也验证过数据量一大差异接近两倍的例子不是没遇到。4.3 EXPLAIN 怎么判断 CTE 的执行计划判断 CTE 到底走合并还是物化最直接的方式是看执行计划。MySQL 8.0 也支持EXPLAIN ANALYZE可以直接拿到实际执行耗时和行数统计。EXPLAIN ANALYZE WITH dept_stats AS ( SELECT dept_id, AVG(salary) AS avg_salary FROM employee GROUP BY dept_id ) SELECT e.emp_id, e.salary, ds.avg_salary FROM employee e LEFT JOIN dept_stats ds ON e.dept_id ds.dept_id;在实际输出里如果出现Materialize相关的操作节点说明 CTE 走了物化如果执行计划里看不到这个节点说明优化器把它合并进主查询了。看到物化时建议顺手检查物化结果集大小和后续 JOIN 字段是否有索引支持。我自己调优的决策顺序是数据量小、CTE 结果几百行以内不管合并还是物化都不敏感优先保证可读性数据量大、CTE 结果几十万行以上就要认真看执行计划必要时MATERIALIZED或NOT MATERIALIZED两个提示都试一遍对比耗时。记住一个原则不要为了“看起来优化”而引入复杂度先用真实数据和执行计划说话。4.4 CTE、临时表、视图三兄弟怎么选被问多了之后我干脆整理了一张表遇到“要不要用 CTE”的问题直接查表对比维度CTE临时表 TEMPORARY TABLE视图 VIEW生命周期单条 SQL 执行期间当前会话期间持久化对象能否显式建索引不能可以依赖底层表索引适用场景单条复杂 SQL 内复用/递归/分层多步处理、跨语句复用、结果集很大固定业务口径、多语句长期复用典型痛点数据量太大时物化表无索引需要建表、清理、容易遗忘权限和口径管理稍重我的经验是同一个复杂报表里能一步做完的单条 SQL 优先用 CTE一旦这条 SQL 拆成好几条、每一条的结果要被下一条继续加工那就老实落临时表并加上必要索引如果某个业务口径要长期固定给多个系统共用才考虑视图。不要一上来就建临时表更不要动不动建视图CTE 在很多场景里是性价比最高、代码最干净的选择。5. 常见问题与调试技巧实录5.1 递归 CTE 死循环和深度上限递归 CTE 最常见的故障就是死循环或误以为死循环。死循环的根源通常是数据本身存在环比如员工 A 的上级是 BB 的上级又是 A这种脏数据会让递归永远切不断。所以我在递归 CTE 上线前都会做两个动作一是检查业务表有没有环状脏数据二是给递归层数设限不让一个错误把数据库资源拖死。MySQL 默认的递归上限是cte_max_recursion_depth默认值 1000。如果层级确实很深或者测试数据故意很大可以按会话临时调高SET SESSION cte_max_recursion_depth 5000;但我要提醒一句调高上限只是兜底真正要防的是死循环。更稳妥的做法是在递归里做一个环路检测比如保留path字段在递归成员里加条件FIND_IN_SET(m.id, dt.path) 0保证同一个节点不会被重复访问。这招在处理分销、组织架构这类强环风险的数据时几乎是必备。5.2 报错“找不到表”或者“递归只允许引用一次”新手写 CTE 经常遇到几个报错我把它们提前列出来省得你们踩Table xxx doesnt exist多半是 CTE 名字拼错或者引用了后面才定义的 CTE。CTE 只能引用前面已经定义过的兄弟 CTE不能“向前引用”。递归 CTE 报Recursive query相关错误先检查是不是忘了写RECURSIVE关键字。很多人写WITH emp_tree AS没有WITH RECURSIVE emp_tree AS自然会报错。递归成员里引用 CTE 两次报错业务上如果非要引用两次通常是递归逻辑设计有问题需要换个思路比如先把要重复关联的数据拆成另一个 CTE再在递归里只引一次。逗号位置错误多个 CTE 之间用英文逗号最后一个 CTE 后面直接跟主查询多一个逗号都会报解析错误。这些报错信息都比较晦涩我的习惯是报错后先复读一遍语法定义再看是不是名字冲突最后才怀疑业务逻辑。90% 以上的 CTE 报错都集中在语法层面千万不要一上来就去翻数据。5.3 老项目还在 MySQL 5.7 怎么办如果你还在 5.7 时代很遗憾 CTE 用不了。但我不会劝你因为一个查询特性就急着升库迁移成本和风险都太大。替代方案也是成熟的派生表照用单个复杂查询还可以用 FROM 子查询注意命名容易读。临时表多步处理的报表先CREATE TEMPORARY TABLE落中间结果再往下加工效果等价于手工物化 CTE。视图固定业务口径提前定义好。从 5.7 过渡到 8.0 时我建议不要把老 SQL 一次性全部重写而是挑几类典型场景多级聚合、同表多关联、组织架构逐条改造。CTE 和原写法的执行计划很可能完全相同但代码可读性和后续维护成本完全是两个世界。我记得 5.7 里一段 200 行的三层嵌套报表改成 CTE 后不到 80 行逻辑一眼到底那次改造之后团队的信心才真正建立起来。5.4 团队落地时的几个小规矩个人用 CTE 很简单团队要推广就得定规矩否则每个人写出来风格各不相同效果反而打折。我们团队沉淀下来的几条 CTE 规范分享给你参考CTE 命名用业务名词不要用t1、t2、a、b这种无意义代号。dept_stats、paid_orders、old_logs这种名字本身就是注释。一个 WITH 块控制的 CTE 数量建议在 5 个以内。超过 5 个就意味着这段 SQL 太重了考虑拆临时表或分步处理。每个 CTE 尽量只做一件事。要么是圈定范围要么是算一个指标不要在一个 CTE 里既过滤又聚合又关联那又把逻辑压回套娃了。上线前单独验证每个 CTE。把主查询注释掉逐个SELECT * FROM 某个_cte确认每层结果都符合预期再整条跑。敏感 DML 必须先 SELECT 验证。用 CTE 定义删除范围时一定先把等价 SELECT 跑一遍确认行数没问题再执行 DELETE/UPDATE。这些规矩看起来琐碎但真的能救命的。一次线上 DELETE 误删往往就藏在“我以为 CTE 圈出的范围是对的”这种错觉里。5.5 一个让调试效率翻倍的小技巧最后分享一个我自己用得很顺手的小技巧把 CTE 当作调试沙箱。遇到一个复杂的统计逻辑不要一口气把完整 SQL 写出来而是先在客户端里按层写WITH step1 AS ( -- 第一步圈出本月有效销售 SELECT ... ), step2 AS ( -- 第二步基于 step1 做区域聚合 SELECT ... ) SELECT * FROM step2;每一次改动只需要改一层跑一次看一层的结果。数据对不上时能精确锁定是哪一层出的问题而不是在一坨括号里上下翻飞。我在项目里改过上线的库存报表慢查询就是用这种方式一层层查下去最后发现是第二步聚合时扫了一个没有索引的大表而不是 CTE 本身的问题。MySQL 8.0 的 CTE 并不能让一条糟糕 SQL 自动变快但它能让你把一个复杂问题拆成看得懂、验得了、改得动的若干小步骤。而我实际工作中的体会是大多数慢 SQL 之所以难优化不是数据库不行是人都看不懂它在算什么。CTE 至少让“看懂”这件事不再成为瓶颈。下次看到三四个嵌套子查询叠在一起时不妨先抽出 CTE 试试你会发现 SQL 这个老伙计其实也可以写得很清爽。