被嵌套子查询“折磨”的日常
你是否遇到过类似这样的统计需求「找出平均销售额高于公司整体平均水平的产品类别」。
用传统嵌套子查询，写法是这样的：
-- 嵌套子查询写法（绕圈又难读）
SELECT * FROM (
-- 内层子查询：按类别分组，计算每个类别的平均销售额
SELECT category_id, AVG(sales_amount) as avg_sales
FROM products GROUP BY category_id
) cat_avg -- 派生表别名，记起来都费劲
-- 外层查询：筛选出高于整体平均销售额的类别
WHERE avg_sales > (
-- 另一个子查询：计算公司所有产品的整体平均销售额
SELECT AVG(sales_amount) FROM products
);
两个子查询嵌套，逻辑绕来绕去，很不好理解，而且要是后续想加字段、改筛选条件，得一层层找对应的子查询，特别麻烦。
而用 CTE 来写，瞬间清爽很多，优化后的写法：
-- CTE 写法（逻辑清晰，读起来丝滑）
WITH
global_avg AS (
-- 第一个CTE：计算公司整体平均销售额
SELECT AVG(sales_amount) as avg_all FROM products
),
category_avg AS (
-- 第二个CTE：按类别分组，计算每个类别的平均销售额
SELECT category_id, AVG(sales_amount) as avg_sales FROM products GROUP BY category_id
)
-- 主查询：直接关联两个CTE，筛选结果
SELECT * FROM category_avg, global_avg WHERE avg_sales > avg_all;
是不是一下子就清晰了？不用嵌套，从上到下顺着读，每个模块分工明确，调试和修改也省心。这就是 CTE 的核心价值——把复杂的嵌套逻辑“拉平”，让 SQL 更易读、更易维护。接下来，咱们就正式认识一下 CTE ～
一. CTE 介绍
通用表表达式（Common Table Expressions，简称 CTE），说白了就是 MySQL 8.0 新增的一种“临时结果集”技术。它用 WITH 子句来定义，作用域特别“专一”——只在当前执行的这一条 SQL 里有效，执行完就自动“消失”，不会像临时表那样，占着会话资源不放手～
1.1 基本语法
CTE 的语法很简单，就两部分：用 WITH 子句定义 CTE，再用主查询引用它。而且支持一次定义多个 CTE，用逗号隔开就行。
-- 单个CTE定义
WITH cte_name (column_list) AS (
-- CTE内部查询（随便写合法SQL，SELECT、JOIN、WHERE都能来）
SELECT 列1, 列2, ... FROM 表名 WHERE 条件
)
-- 主查询：引用CTE，想筛选、关联、聚合都可以
SELECT * FROM cte_name WHERE 条件;
-- 多个CTE定义（用逗号分隔）
WITH
cte1 (col1, col2) AS (SELECT ...), -- 第一个CTE
cte2 (col3, col4) AS (SELECT ...) -- 第二个CTE
SELECT * FROM cte1 JOIN cte2 ON cte1.col1 = cte2.col3; -- 主查询关联多个CTE
1.2 核心价值
CTE 最牛的地方，就是 “逻辑扁平化” ！把复杂的嵌套子查询，拆成一个个命名清晰的“小模块”，每个模块干一件事，主查询只要按需引用这些模块就行。这样一来，SQL 读起来就像“流水账”，从上到下一目了然，调试和维护也省了不少劲。
补充一句：CTE 本身不存数据，只在查询执行的时候临时生成结果，执行完就立马释放，不会有持久化的存储开销（除非特殊情况，后面讲性能的时候会说到）。
了解了 CTE 的基本概念、语法和核心价值后，接下来咱们就结合实际业务场景，看看 CTE 具体能解决哪些问题，如何落地使用。
二. CTE 应用场景
CTE 分两种：普通 CTE（非递归）和递归 CTE。简单说，普通 CTE 用来简化复杂查询，递归 CTE 专门搞定树形、层级数据，或者生成序列，咱们结合具体场景，一步步看～
2.1 普通 CTE：简化复杂查询
就像开篇咱们举的例子，只要你的 SQL 里嵌套了好几个子查询，而且每个子查询的逻辑都不一样，用普通 CTE 把它们拆成独立模块，读起来、改起来都会轻松很多。
场景说明
还是用 products 产品表，需求升级：找出平均销售额高于公司整体平均水平的“优秀”产品类别，顺便看看它们比整体平均高多少。
CTE 实现代码
-- 定义两个CTE，分工明确，一个算整体平均，一个算类别平均
WITH
global_avg AS (
-- 第一个CTE：算全公司所有产品的平均销售额，起个小别名avg_all
SELECT AVG(sales_amount) as avg_all FROM products
),
category_avg AS (
-- 第二个CTE：按类别分组，算每个类别的平均销售额
SELECT
category_id,
AVG(sales_amount) as avg_sales -- 类别平均销售额
FROM products
GROUP BY category_id -- 按类别分组
)
-- 主查询：把两个CTE关联起来，筛选出“优秀”类别
SELECT
ca.category_id, -- 产品类别ID
ca.avg_sales, -- 类别平均销售额
ca.avg_sales - ga.avg_all AS diff -- 比整体平均高多少
FROM category_avg ca, global_avg ga -- 用别名简化书写，不用写那么长
WHERE ca.avg_sales > ga.avg_all; -- 筛选条件：类别平均 > 整体平均
代码说明
global_avg：专门算公司整体平均销售额，逻辑独立，想单独调试它也很方便；category_avg：专门算每个类别的平均销售额，和第一个 CTE 互不干扰；- 主查询就负责“牵线搭桥”，把两个 CTE 的结果关联起来，筛选出符合条件的记录。后续想改筛选条件、加字段，只改对应模块就行，不用动整体逻辑。
2.2 递归 CTE：处理树形/层级数据
递归 CTE 是 CTE 的“王牌功能”！它用“非递归部分（起点）+ 递归部分（循环逻辑）”的组合，能轻松搞定树形结构数据（比如公司组织架构、商品分类目录），再也不用写一堆嵌套子查询绕来绕去了。
记住一个小要点：递归 CTE 必须加 RECURSIVE 关键字，而且里面得用 UNION ALL 连接非递归和递归部分，用 UNION 会去重，会影响递归效果哦～
场景说明
假设有个 employees 员工表，里面有 id（员工ID）、name（员工姓名）、manager_id（上级ID）。咱们要查 ID 为 1 的总经理，所有的下属——不管是直接下属（部门经理），还是间接下属（员工），还要显示每个下属的层级（直接下属是1级，间接下属逐级加1）。
测试数据
| id | name | manager_id |
|---|---|---|
| 1 | 张三（总经理） | NULL |
| 2 | 李四（部门经理） | 1 |
| 3 | 王五（员工） | 2 |
| 4 | 赵六（员工） | 2 |
| 5 | 孙七（部门经理） | 1 |
递归 CTE 实现代码
WITH RECURSIVE emp_tree AS (
-- 1. 非递归部分（递归起点）：先找到总经理（id=1），层级设为1
SELECT
id,
name,
manager_id,
1 as level -- level字段：标记层级，起点就是1
FROM employees
WHERE id = 1 -- 递归起点：咱们的总经理
UNION ALL -- 连接起点和循环逻辑，必须用UNION ALL（去重会搞乱递归）
-- 2. 递归部分（循环逻辑）：找当前层级员工的直接下属
SELECT
e.id,
e.name,
e.manager_id,
et.level + 1 -- 下属层级比上级高1，简单吧～
FROM employees e -- 员工表别名e，省得写全称
INNER JOIN emp_tree et -- 关联递归CTE本身（emp_tree），别名et
ON e.manager_id = et.id -- 关联条件：员工的上级ID = 上一层级员工的ID
)
-- 主查询：把所有递归结果查出来，就是总经理的所有下属
SELECT * FROM emp_tree; -- 想按层级排序，加个ORDER BY level就行
递归 CTE 运行结果
运行递归CTE代码后，查询结果如下（包含层级level字段）：
| id | name | manager_id | level |
|---|---|---|---|
| 1 | 张三（总经理） | NULL | 1 |
| 2 | 李四（部门经理） | 1 | 2 |
| 5 | 孙七（部门经理） | 1 | 2 |
| 3 | 王五（员工） | 2 | 3 |
| 4 | 赵六（员工） | 2 | 3 |
递归逻辑说明
- 非递归部分：只执行一次，找到递归的起点——总经理（id=1），作为第一层数据；
- 递归部分：循环执行，每次都关联上一次递归的结果（emp_tree），找出上一层员工的直接下属，层级加1；
- 什么时候停呢？当递归部分查不到数据（没有更多下属）的时候，循环就自动终止，把所有层级的结果返回给我们。
2.3 递归 CTE：生成序列
除了处理树形数据，递归 CTE 还有个实用功能——生成连续序列，比如日期序列、数字序列，不用依赖额外的表，简单又高效。
场景说明
需求很简单：生成 2024 年 1 月到 12 月的每月第一天，做成一个日期序列，后续用来统计每个月的数据，省得手动输入。
实现代码
WITH RECURSIVE months AS (
-- 非递归部分：序列起点（2024年1月1日）
SELECT '2024-01-01' AS month_start
UNION ALL
-- 递归部分：每次在当前日期基础上加1个月
SELECT DATE_ADD(month_start, INTERVAL 1 MONTH)
FROM months
WHERE month_start < '2024-12-01' -- 终止条件：不超过2024年12月1日
)
-- 主查询：把生成的日期序列查出来，直接用就行
SELECT month_start FROM months;
运行结果
| month_start |
|---|
| 2024-01-01 |
| 2024-02-01 |
| 2024-03-01 |
| 2024-04-01 |
| 2024-05-01 |
| 2024-06-01 |
| 2024-07-01 |
| 2024-08-01 |
| 2024-09-01 |
| 2024-10-01 |
| 2024-11-01 |
| 2024-12-01 |
补充说明：生成的日期序列严格遵循代码逻辑，从2024-01-01开始，每月递增1个月，直至2024-12-01终止，可直接用于后续月度数据统计。
掌握了 CTE 的各类应用场景后，大家难免会关心其性能表现——CTE 会不会拖慢查询速度？会带来怎样的内存消耗？下一节我们就聚焦 CTE 的性能与内存占用，解答这些疑问。
三. CTE 的性能与内存占用
很多小伙伴可能会误以为 CTE 有“性能buff”，其实不然！CTE 本身没有特殊的性能优化，它的性能好不好，主要看 MySQL 优化器怎么处理（合并还是物化），还有数据量和查询逻辑。
3.1 优化器行为：合并 vs 物化
MySQL 8.0 优化器处理 CTE 有两种方式，会根据查询成本自动选，简单理解一下：
-
合并（Merge）：默认操作，就是把 CTE 的查询逻辑，直接“合并”到主查询里，相当于把 CTE 换成了对应的子查询。这时候 CTE 只起到“拆分逻辑”的作用，性能和子查询差不多。
-
物化（Materialize）：把 CTE 的结果存到内部临时表，主查询直接从临时表里读数据。适合 CTE 被多次引用、或者 CTE 逻辑很复杂（比如有聚合、关联）的情况，能避免重复执行 CTE 逻辑，提升速度。
注意点：CTE 多次引用的性能陷阱
给大家提个醒：如果 CTE 被多次引用（比如用 UNION ALL、JOIN 引用好几次），而且优化器选了“合并”方式，那么 CTE 的查询逻辑会被执行多次，相当于做了重复工作，性能会下降。举个例子：
-- 风险示例：CTE被引用两次，可能会执行两次
WITH cte AS (
SELECT * FROM big_table WHERE complex_condition -- 复杂查询，执行起来费时间
)
SELECT * FROM cte -- 第一次引用CTE
UNION ALL
SELECT * FROM cte; -- 第二次引用，没物化的话，会再执行一次CTE逻辑
3.2 物化优化：手动强制物化
如果 CTE 被多次引用，或者逻辑很复杂，可以手动“强制物化”——用 /*+ MATERIALIZED */ 这个优化器提示，让 MySQL 把 CTE 结果存到临时表，避免重复执行，提升性能。
-- 强制物化CTE，解决多次引用的性能问题
WITH /*+ MATERIALIZED */ cte AS (
SELECT * FROM big_table WHERE complex_condition
)
SELECT * FROM cte
UNION ALL
SELECT * FROM cte; -- 此时CTE只执行一次，两次引用都读临时表
3.3 物化的内存消耗
如果 CTE 被物化了，会用 MySQL 的临时表存结果，内存消耗有几个小规则，大家了解一下：
- 优先用 内存临时表，数据量小的时候，存在内存里，访问速度快；
- 如果数据量超过
tmp_table_size（默认16M）或者max_heap_table_size（默认16M），就会自动转到 磁盘临时表，这时候性能会下降（磁盘读写比内存慢）； - 可以监控
Created_tmp_disk_tables这个指标，看看磁盘临时表建了多少，要是太多，就调整一下tmp_table_size参数。
清楚了 CTE 的性能和内存相关要点后，我们不妨将其与传统的临时结果集实现方式（子查询、临时表、视图）做个对比，看看 CTE 到底有哪些独特优势，下一节我们详细拆解对比逻辑。
四. CTE 对比传统实现方式的优势
在 CTE 出现之前，咱们写 SQL 想实现临时结果集，只能用子查询（派生表）、临时表、视图。CTE 相当于“集大成者”，结合了三者的优点，还弥补了它们的不足，咱们从几个维度，简单对比一下～
4.1 对比子查询（派生表）
子查询（派生表）就是把一个查询的结果，当成另一个查询的数据源，嵌套在主查询里。嵌套层数一多，读起来就像“套娃”，头晕眼花，而 CTE 能把这些嵌套逻辑“拉平”，清爽多了。
| 对比维度 | CTE | 子查询（派生表） |
|---|---|---|
| 可读性 | ✅ 清清爽爽，自上而下，每个模块都有名字 | ❌ 嵌套深了就像“绕迷宫”，难读又难懂 |
| 复用性 | ✅ 同一 SQL 里，同一个 CTE 能多次引用 | ❌ 每次引用都要重新执行一次，效率拉胯 |
| 代码维护 | ✅ 模块独立，改一个地方不影响其他部分 | ❌ 逻辑缠在一起，改一处要动好多地方 |
4.2 对比临时表
临时表是用 CREATE TEMPORARY TABLE 创建的，作用域是整个会话，用完还得手动删除，不然会占资源。而 CTE 只在当前 SQL 里有效，执行完自动释放，不用咱们多操心。
| 对比维度 | CTE | 临时表 |
|---|---|---|
| 作用域 | 只在当前 SQL 里有效，执行完自动释放 | 整个会话都有效，会话结束才释放（或手动删） |
| 存储开销 | 默认不存数据，只临时生成结果，几乎无开销 | 显式占内存/磁盘，数据多了压力就大 |
| 索引支持 | ❌ 没法直接给 CTE 结果建索引 | ✅ 可以手动建索引，优化查询速度 |
| 维护成本 | ✅ 自动释放，不用手动操作 | ❌ 得手动执行 DROP TEMPORARY TABLE，不然占资源 |
| 适用场景 | 单次复杂查询，不用跨 SQL 复用结果 | 跨多条 SQL 复用结果，需要索引优化 |
4.3 对比视图
视图是用 CREATE VIEW 创建的“永久虚拟表”，存在数据库里，多个会话都能共用。而 CTE 是临时的，只在当前查询里能用，不持久化。
| 对比维度 | CTE | 视图 |
|---|---|---|
| 持久性 | 临时的，SQL 执行完就消失，不存数据库 | 永久的，创建后存数据库，除非手动删 |
| 作用域 | 只当前查询能用，跨会话、跨 SQL 不行 | 多会话共享，有权限的用户都能引用 |
| 适用场景 | 临时的、一次性的复杂查询 | 频繁复用的查询，需要多会话共享 |
通过对比我们能看出 CTE 的诸多优势，但它并非万能工具，存在一些不适用的场景。下一节我们就来梳理哪些情况不建议使用 CTE，帮助大家避开使用误区。
五. CTE 不适用的场景
CTE 虽然好用，但不是万能的！以下几种情况，不建议用 CTE，不然会越用越卡，还满足不了需求。
5.1 超大数据量全量处理
如果数据量特别大（比如亿级、十亿级），还需要全量处理，CTE 会生成一个超大的临时结果集，占满内存和磁盘，导致查询卡顿、内存溢出。这时候不如用分页查询、分批处理，或者直接优化查询逻辑，别全量加载数据。
-- ❌ 不适用：10亿行数据直接用CTE全量查，纯属找卡
WITH all_data AS (SELECT * FROM billion_rows) -- 全量加载10亿行，内存直接爆
SELECT complex_analysis FROM all_data; -- 复杂分析雪上加霜
-- ✅ 推荐：分页查询，分批处理
SELECT complex_analysis FROM billion_rows LIMIT 10000 OFFSET 0; -- 一次查1万条，不卡
5.2 跨会话复用中间结果
CTE 只能在当前这一条 SQL 里用，跨会话、跨 SQL 都不行。如果需要在多个 SQL、多个会话里复用中间结果，别用 CTE，选临时表或者视图更合适。
5.3 递归深度过大
递归 CTE 有个深度限制，默认是 cte_max_recursion_depth = 1000，超过这个限制就会报错“Recursion depth exceeded”。就算没超过，深度太大也会让性能急剧下降，还可能内存溢出。
适合用的场景：递归深度小的树形结构（比如三级组织架构）；不适合：深度特别大的（比如多级分类目录，深度超过1000）。
5.4 需要索引优化的后续查询
CTE 的结果集没法直接建索引，如果后续查询需要对 CTE 结果做多次复杂过滤、排序、关联，没有索引会很慢。这时候不如用临时表（可以建索引），比 CTE 好用。
六. 总结
总的来说，CTE 是 MySQL 8.0 特别实用的临时结果集技术，核心价值就是简化复杂查询逻辑，让 SQL 好读、好改、好调试，主要分普通 CTE（简化查询）和递归 CTE（处理树形数据、生成序列）两种。
结合开篇的例子和咱们讲的所有内容，最后给大家划几个重点，用 CTE 的时候记好：
- 遇到嵌套子查询绕圈的情况，优先用 CTE 重构，逻辑会清爽很多；
- CTE 没有性能优势，性能看优化器的合并/物化操作，多次引用记得强制物化；
- 超大数据量、递归深度太大、跨会话复用、需要索引优化的场景，别用 CTE；
- CTE、子查询、临时表、视图各有优缺点，按需选就好：单次复杂查询用 CTE，跨会话复用用临时表/视图。
