MySQL CTE 拆分复杂查询 怎样处理

复杂查询越写越长时，MySQL CTE（Common Table Expression，公用表表达式）适合先把“输入筛选”“中间汇总”和“最终关联”拆成几个有名字的结果集，再由最后一个 SELECT 组合。它解决的是阅读、复查和复用问题，不等于手工创建了一张永久临时表，也不保证查询一定更快。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/with.html

处理这类查询时，先按数据职责拆 CTE，再用 EXPLAIN 判断优化器是合并还是物化。只要中间结果的行数、连接键和过滤时机都能说清，拆分就是有效的；如果只是把一条简单查询切成许多层，反而会增加排查成本。

先判断哪些部分值得拆出来

我通常先把原查询标成三类：第一类是尽早缩小范围的基础数据，例如时间区间、状态和租户；第二类是对缩小后的数据做聚合的结果，例如每个用户的订单数和金额；第三类是把汇总结果接回用户、部门或标签表的展示查询。只有能用一句话说明职责的部分，才值得成为一个 CTE。

下面的场景中，CTE 的边界比较自然：

• 同一批过滤后的订单要被多个后续逻辑引用；
• 聚合表达式已经长到影响主查询阅读；
• 需要把“先过滤再汇总”的关系明确写给审核者。

反过来，只有一层简单 WHERE 条件，或者拆分后每个 CTE 只有一两个字段，却没有复用和审查价值时，保留派生表或原查询更直接。

用一个 WITH 子句按依赖关系拆分

MySQL 允许在同一个 WITH 子句中声明多个 CTE，名称之间用逗号分隔。后面的 CTE 可以引用前面已经声明的 CTE，但不能反过来引用后声明的名字。下面用一个报表场景示范：先筛选近 30 天的已支付订单，再按用户汇总，最后补充用户名称。

-- 先缩小订单范围，避免把无关状态带入后续汇总
WITH recent_paid_orders AS (
    SELECT user_id, amount
    FROM orders
    WHERE paid_at >= CURRENT_DATE - INTERVAL 30 DAY
      AND status = 'paid'
),
-- 聚合层只关心用户维度，便于单独阅读和复查
user_totals AS (
    SELECT user_id,
           COUNT(*) AS order_count,
           SUM(amount) AS total_amount
    FROM recent_paid_orders
    GROUP BY user_id
)
-- 展示层负责补充维度信息，不重复书写聚合条件
SELECT u.id,
       u.display_name,
       t.order_count,
       t.total_amount
FROM user_totals AS t
JOIN users AS u ON u.id = t.user_id
ORDER BY t.total_amount DESC;

这个拆法的关键不是 CTE 数量，而是每层只回答一个问题：哪些订单进入统计、每个用户统计出什么、结果如何展示。字段别名也应在 CTE 内明确写出，避免最后一层出现多个同名列。

不要把 CTE 当成强制物化的临时表

MySQL 8.4 文档说明，派生表、视图引用和 CTE 都可能采用两种策略：合并到外层查询，或物化到内部临时表。优化器会尽量避免不必要的物化，并可能把外层条件下推到内部。因此，“拆成 CTE 后一定多一次落盘”是不准确的判断。

真正需要警惕的是中间结果过大、过滤条件放得太晚，以及为了可读性重复计算同一份大集合。上线前可以先查看计划：

-- EXPLAIN 只查看优化器计划，不执行这条报表查询
EXPLAIN
WITH recent_paid_orders AS (
    SELECT user_id, amount
    FROM orders
    WHERE paid_at >= CURRENT_DATE - INTERVAL 30 DAY
      AND status = 'paid'
),
user_totals AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
    FROM recent_paid_orders
    GROUP BY user_id
)
SELECT u.id, u.display_name, t.order_count, t.total_amount
FROM user_totals AS t
JOIN users AS u ON u.id = t.user_id;

如果计划显示的访问路径没有按预期使用订单过滤条件，就回到基础表上的索引和过滤表达式检查，而不是立刻增加更多 CTE。聚合、DISTINCT、GROUP BY、LIMIT、UNION 等构造可能阻止合并；是否物化仍应以实际执行计划和数据规模为准。

变慢时的回滚和复查顺序

CTE 改写后出现变慢，我会按下面顺序处理：

1. 保留改写前的 SQL 和改写后的 SQL，先确认结果列、重复行和 NULL 语义没有变化；
2. 分别检查每个 CTE 的预计行数，优先处理最早产生大量中间数据的层；
3. 确认时间、状态和租户条件是否尽量靠近基础表，连接键的数据类型是否一致；
4. 用 EXPLAIN 对比访问类型、扫描行数和连接顺序；
5. 如果只是为了可读性而拆分、但计划明显恶化，先恢复原查询，再保留较少的 CTE 边界。

这里的回滚是 SQL 文本回滚，不是删除数据库对象。CTE 只在当前语句范围内生效，改写失败不会留下需要清理的临时表。

常见问题

一个 WITH 里能写多个 CTE 吗？可以，使用逗号分隔，并保证每个名称唯一；同一层不能连续写两个 WITH。

CTE 一定比子查询快吗？不一定。CTE 首先改善结构，优化器可能把它合并，也可能因聚合、DISTINCT 等条件物化，最终要看执行计划。

什么时候适合递归 CTE？层级树遍历、日期序列等需要反复引用自身的任务才适合。递归查询必须有终止条件，并关注递归深度和中间结果规模。

小结

MySQL CTE 拆分复杂查询的正确边界是“按职责命名中间结果”：先筛选，再聚合，最后关联。写完后用 EXPLAIN 复查过滤是否提前、结果集是否膨胀，以及优化器选择了合并还是物化。把 CTE 当成可读的查询组织工具，再根据计划决定是否保留，通常比把它当成性能开关更稳妥。