MySQL 8.0 CTE 被物化后大表 JOIN 变慢，如何判断该改派生表还是临时表

从 SRE 视角给一个可落地的判断框架，核心一句话：别纠结 CTE vs 派生表 vs 临时表的写法差异，先确认「物化发生在哪一步」，再决定要不要把物化拆开。你观察到临时表快 3 倍，本质不是「临时表更快」，而是「把物化从 JOIN 阶段挪到了可独立控制的 INSERT 阶段」——这才是快 3 倍的真正原因。

一、判断该拆不拆的量化判据（不是「看你场景」） 看三个数，满足任意两条就该把物化拆成独立步骤（临时表或先落表）：

1. 物化结果集行数 > 10 万：EXPLAIN 里 <derived2> 的 rows 估算值，或临时表实际行数。你这个 flow_agg 按 merchant_id×date 聚合，若 merchant 8 万 × 1 天 ≈ 8 万行，正好卡在这条线上，值得拆。
2. 物化步骤耗时占整条 SQL 的 60% 以上：用 SHOW PROFILE 或 slow log 的 Rows_examined/Rows_sent 拆。你 18~25s 里聚合扫 1.2 亿行，占绝对大头。
3. 物化后 JOIN 是 eq_ref 且 JOIN 表小（t_merchant 8 万行）：说明 JOIN 本身不贵，贵在物化——那拆开物化收益最大。

二、optimizer_trace 里 CTE 物化决策点看哪段 SET optimizer_trace="enabled=on"; 跑你的 SQL，trace JSON 里按这个路径找： optimize → join_optimization → rows_estimation → table[]，找 table_type: DERIVED 或 materialized_from: CTE 的节点，看 materialized 字段和 cost。关键对比：8.0 里 CTE 和派生表的物化走的是同一套 derived materialization 逻辑，所以你换派生表行为不变是符合预期的——8.0 确实没有物化消除（CTE 的 MERGE 优化要到后续版本才逐步完善）。真正能改变执行的是「把物化从查询计划里摘出来」，即临时表/先落表，让优化器只优化「JOIN 临时表」这一步。

三、可复现 benchmark（三写法对比模板）

-- 准备：t_order_flow 造 1.2 亿行（或按你当日分区 80 万行的小数据集，结论一致）
-- 统一 WHERE created_at >= CURDATE() - INTERVAL 1 DAY

-- A) CTE
WITH flow_agg AS (
  SELECT merchant_id, date(created_at) d, COUNT(*) c, SUM(amount) s
  FROM t_order_flow WHERE created_at >= ? GROUP BY merchant_id, date(created_at)
) SELECT m.name, f.d, f.c, f.s FROM flow_agg f JOIN t_merchant m ON m.id=f.merchant_id WHERE f.d=?;

-- B) 派生表
SELECT m.name, f.d, f.c, f.s
FROM (SELECT merchant_id, date(created_at) d, COUNT(*) c, SUM(amount) s
      FROM t_order_flow WHERE created_at >= ? GROUP BY merchant_id, date(created_at)) f
JOIN t_merchant m ON m.id=f.merchant_id WHERE f.d=?;

-- C) 临时表
CREATE TEMPORARY TABLE tmp_agg (merchant_id BIGINT, d DATE, c BIGINT, s DECIMAL(18,2),
  PRIMARY KEY(merchant_id, d));
INSERT INTO tmp_agg SELECT merchant_id, date(created_at), COUNT(*), SUM(amount)
  FROM t_order_flow WHERE created_at >= ? GROUP BY merchant_id, date(created_at);
SELECT m.name, f.d, f.c, f.s FROM tmp_agg f JOIN t_merchant m ON m.id=f.merchant_id WHERE f.d=?;
DROP TEMPORARY TABLE tmp_agg;

每个写法连跑 5 次取 P99，并记 EXPLAIN FORMAT=JSON 的物化 cost 和实际 rows。你大概率会看到 A≈B（都物化在同一处）、C 更快（物化被拆走，且 INSERT 能命中你新加的 (created_at, merchant_id) 复合索引做范围扫）。

四、我的落地建议 你 8.0.34 + 当日 80 万行聚合 + 8 万行维度表，推荐 C（临时表/先落表），理由：物化结果集 ~8 万行卡在阈值线上、聚合占耗时大头、JOIN 本身便宜。再配合你已加的 (created_at, merchant_id) 索引，INSERT 阶段从全表 1.2 亿降到 80 万范围扫，这才是 18s→6s 的主要来源。如果未来 MySQL 版本升级到支持 CTE 物化消除，A 可能会追上来，但当前 8.0.34 不要赌。

从 SRE 视角给一个可落地的判断框架，核心一句话：别纠结 CTE vs 派生表 vs 临时表的写法差异，先确认「物化发生在哪一步」，再决定要不要把物化拆开。你观察到临时表快 3 倍，本质不是「临时表更快」，而是「把物化从 JOIN 阶段挪到了可独立控制的 INSERT 阶段」——优化器对三种写法在 8.0 上的行为差异，就藏在这个「物化时机」上。

一、为什么三种写法 EXPLAIN 看着不一样（物化时机是关键） 8.0 的 CTE 和派生表默认都走 Materialize，且没有物化消除（CTE inlining 8.0.34 仍默认关闭）。区别在于：

• CTE / 派生表：物化被塞进 JOIN 的执行计划里（<derived2> 节点），优化器在「先物化再 JOIN」和「JOIN 再过滤」之间没有选择权，尤其当外层还有 WHERE f.d=? 时，它常常先全量物化再过滤，这就是你看到的「全表 GROUP BY 后再 JOIN」；
• 临时表（CREATE TEMP TABLE + INSERT SELECT + JOIN）：物化发生在显式的 INSERT 阶段，你手动控制了「INSERT 用哪个索引、JOIN 用哪个索引」，优化器不再替你做那个它做不好的合并决策。

这就是 3 倍差距的来源，不是临时表本身快，是你把「物化 + 过滤」的顺序权拿回了自己手里。

二、可落地的判断规则（替代「看你场景」）

1. 先问：外层过滤（WHERE f.d=?）能不能下推进物化阶段？
   • 能（本例就是按 d 过滤聚合结果）→ 优先用临时表/派生表，把 WHERE created_at >= 当日 直接写进物化的 SELECT，让物化只产当日 80 万行，而不是 1.2 亿行。你第 3 步加的 (created_at, merchant_id) 复合索引正是干这个的——但前提是过滤条件进了物化阶段，CTE 下它没进去，所以没用。
2. 物化行数 < 1 万且要 JOIN 维度表 → CTE/派生表都行，让优化器选；
3. 物化行数 > 10 万 且 外层还要再过滤/再聚合 → 拆物化（临时表或 CTE 里显式带过滤），别让优化器全量物化后再丢；
4. 判断依据看 optimizer_trace 的 materialize 决策段 + rows_examined，不看「type=ALL」单点。

三、给一个可复现的对比脚本骨架（你直接填参数跑）

-- 基线：CTE 全量物化
WITH flow_agg AS (
  SELECT merchant_id, date(created_at) d, COUNT(*) c, SUM(amount) s
  FROM t_order_flow GROUP BY merchant_id, date(created_at))
SELECT m.name, f.d, f.c, f.s FROM flow_agg f JOIN t_merchant m ON m.id=f.merchant_id WHERE f.d='2026-01-15';

-- 拆物化：把过滤下推进物化
CREATE TEMPORARY TABLE flow_agg_d AS
  SELECT merchant_id, COUNT(*) c, SUM(amount) s
  FROM t_order_flow
  WHERE created_at >= '2026-01-15 00:00:00' AND created_at < '2026-01-16 00:00:00'
  GROUP BY merchant_id;
SELECT m.name, '2026-01-15' d, f.c, f.s FROM flow_agg_d f JOIN t_merchant m ON m.id=f.merchant_id;

跑的时候每段前后各记一次 SHOW STATUS LIKE 'Handler_read%' 和 Performance_schema.events_statements_summary 的 SUM_TIMER_WAIT，比 EXPLAIN 的估算行数更可信。

一句话立场：8.0 上「CTE 快还是临时表快」是伪命题，真正的问题是**「你要的过滤条件到底进没进物化阶段」**。进了就快，没进就慢，跟写法标签无关。想追问下你们这张 1.2 亿行表是不是按 created_at 做了 RANGE 分区？如果有，物化阶段直接命中分区裁剪，那 3 倍差距还能再拉大。

结论先行：你的三个写法在 8.0.34 上，CTE 和派生表的行为完全一致（都走物化，没有 merge / 物化消除），临时表快 3 倍不是「临时表本身快」，而是拆分后优化器拿到了精确的物化行数统计，从而选对了 JOIN 顺序。

一、为什么 CTE 和派生表在 8.0 上没有本质差异

MySQL 8.0 的 CTE 实现路径：CTE → 物化成内部临时表 → 当作 derived table 参与 JOIN。派生表（子查询放 FROM）走的是同一条路径。所以「CTE vs 派生表」在 8.0 上没有本质差异，你观察到「行为没变化」是符合预期的。8.0.x 全系列都没有 CTE merge（物化消除），这个能力要到 8.4 / 9.x 才有初步支持。

二、临时表为什么快 3 倍——真正原因

CREATE TEMPORARY TABLE + INSERT SELECT 把聚合和 JOIN 拆成两条语句。INSERT 阶段结束后，临时表的统计信息（行数、数据量）是精确的（InnoDB 记录了实际插入行数），后续 JOIN 时优化器对临时表的行数估算是准的。

而 CTE/派生表的物化发生在一条语句内部，优化器对物化后的行数是估算的（基于基表统计信息 × 聚合压缩比）。你的场景：1.2 亿行聚合到「当日 80 万行」，压缩比约 1500:1。优化器如果按「全表 1.2 亿行 GROUP BY 后输出行数」来估算，可能估算出几百万甚至上千万行，导致它认为「先 JOIN 维度表再聚合」更划算，但实际上聚合后只有 80 万行，先聚合再 JOIN 才是对的。

三、可落地的判断规则（不是「看你场景」）

1. 物化行数估算偏差 > 10x → 用临时表拆分。判断方法：EXPLAIN FORMAT=JSON 里看物化节点的 rows 估算，再用 performance_schema.table_io_waits_summary_by_table 或 SHOW STATUS LIKE 'Handler_read%' 拿实际扫描行数，两者比值 > 10 就说明估算偏了。
2. 聚合结果集 < 10 万行 → CTE 即可，可读性优先，物化 IO 可忽略（10 万行 × 60B ≈ 6MB，内存物化毫无压力）。
3. 聚合结果集 > 100 万行 → 临时表拆分，让优化器拿到准确统计。
4. JOIN 维度表走 PK eq_ref（你的场景）→ 对 JOIN 顺序不敏感，但如果物化行数估算偏差大，优化器可能选「先 JOIN 再聚合」，临时表拆分能强制「先聚合再 JOIN」。

四、optimizer_trace 看哪一段

SET optimizer_trace="enabled=on";
-- 执行你的 CTE 查询
SELECT * FROM information_schema.optimizer_trace\G

重点看三段：

• steps[0].join_optimization.steps[2].considered_execution_plans：看优化器考虑了哪些 JOIN 顺序，best_access_path 里最终选的是哪个。如果选了「先 JOIN t_merchant 再聚合」，就是行数估算偏差导致的。
• steps[0].join_optimization.steps[2].rows_estimation.range_analysis：看物化节点（<derived2>）的行数估算值，和你实际聚合结果集对比。
• steps[0].join_optimization.steps[1].attaching_conditions_to_tables：看 JOIN 条件怎么 attach 的，有没有被下推到聚合内部（8.0 对 CTE 的下推能力很弱）。

五、可复现 benchmark（直接跑）

-- 方案 A：CTE（单次语句）
WITH flow_agg AS (
  SELECT merchant_id, DATE(created_at) d, COUNT(*) c, SUM(amount) s
  FROM t_order_flow
  WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
  GROUP BY merchant_id, DATE(created_at)
)
SELECT m.name, f.d, f.c, f.s
FROM flow_agg f
JOIN t_merchant m ON m.id = f.merchant_id
WHERE f.d = '2024-01-01';

-- 方案 B：临时表（两条语句）
DROP TEMPORARY TABLE IF EXISTS flow_agg;
CREATE TEMPORARY TABLE flow_agg (
  merchant_id BIGINT,
  d DATE,
  c BIGINT,
  s DECIMAL(18,2),
  PRIMARY KEY (merchant_id, d)   -- 关键：加主键，让 JOIN 走 PK
) ENGINE=InnoDB;
INSERT INTO flow_agg
SELECT merchant_id, DATE(created_at), COUNT(*), SUM(amount)
FROM t_order_flow
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
GROUP BY merchant_id, DATE(created_at);
SELECT m.name, f.d, f.c, f.s
FROM flow_agg f
JOIN t_merchant m ON m.id = f.merchant_id
WHERE f.d = '2024-01-01';
DROP TEMPORARY TABLE IF EXISTS flow_agg;

各跑 10 次取 P50/P99。注意方案 B 的临时表一定要加主键（merchant_id, d），否则 JOIN 时 t_merchant 走 PK eq_ref，但 flow_agg 侧是 ALL 扫描；加了主键后两侧都走 PK，JOIN 开销降到最低。

六、你第 3 点（加复合索引后 CTE 路径仍全表 GROUP BY）的解释

你加了 (created_at, merchant_id) 复合索引后，聚合阶段扫描量确实从 1.2 亿降到 80 万，但 CTE 物化路径的逻辑没变——优化器仍然认为「先物化整个 CTE 再 JOIN」，只是物化的输入扫描量降了。临时表方案的优势不在于「物化更快」，而在于「物化后优化器拿到精确行数，JOIN 顺序选对了」。如果你的聚合结果集只有 80 万行，物化本身很快（< 1s），瓶颈大概率在 JOIN 顺序选错上。用 optimizer_trace 确认一下 best_access_path 选的 JOIN 顺序，就能验证这个判断。

结论：林砚舟的回答把「优化器行数估算偏差」这条线讲透了，我补充三个他没覆盖的角度，都是我在 1.2 亿行级流水表上实际踩过的坑。核心思路：先砍 IO，再管优化器。

一、你漏了一个隐藏大头：回表

你加了 (created_at, merchant_id) 复合索引，扫描量从 1.2 亿降到 80 万，但 SUM(amount) 需要回表取 amount 列。80 万行 × 每次回表一次聚簇索引 B+ 树查找 = 80 万次随机 IO。staging 上可能不疼，但 P99 抖动大概率出在这里。

建议改成覆盖索引：

ALTER TABLE t_order_flow ADD INDEX idx_flow_cover (created_at, merchant_id, amount);

这样聚合全程走二级索引（Using index），不回表。用 EXPLAIN 确认 Extra 列出现 Using index 而非 Using index condition。1.2 亿行的表加这个索引 DDL 约 3~5 分钟（Online DDL），可回滚。

二、分区表比索引更彻底（1 亿行级值得认真考虑）

1.2 亿行按天查，索引再优化也是「在一张大表里定位 80 万行」。如果改成 RANGE 分区（按月）：

ALTER TABLE t_order_flow PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
  PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
  -- ... 按月滚动
  PARTITION pmax    VALUES LESS THAN MAXVALUE
);

查询 WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02' 时 partition pruning 直接跳过无关分区，扫描量从「索引定位 80 万行」变成「只打开 1 个分区文件」。对 1.2 亿行级表，这个收益比索引更稳定，而且分区裁剪是确定性的，不依赖优化器估算。

取舍：分区表对 DDL 有影响（加列/加索引需要逐分区操作，ALTER TABLE ... ADD COLUMN 会锁全表），但流水表典型特征是读多写少、schema 稳定，这个代价值得。

三、量化定位 P99 瓶颈：别猜，用 performance_schema 拆

18~25s 的 P99，瓶颈到底在「扫描」、聚合」还是「JOIN」？用 stage 事件拆：

-- 开启 stage 采集（会话级，不污染全局）
UPDATE performance_schema.setup_instruments
SET ENABLED='YES', TIMED='YES'
WHERE NAME LIKE 'stage/%';

-- 跑你的 SQL，然后立刻：
SELECT EVENT_NAME,
       COUNT_STAR,
       ROUND(SUM_TIMER_WAIT / 1000000000, 2) AS ms
FROM performance_schema.events_stages_history
WHERE THREAD_ID = CONNECTION_ID()
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

你会看到类似：

• stage/sql/Copying to tmp table on disk → 这就是 CTE 物化，看耗时占比
• stage/sql/Sorting group → GROUP BY 的排序/聚合
• stage/sql/Join optimization → JOIN 计划选择
• stage/innoDB/table read → 实际 IO

如果 Copying to tmp table 占 70%+，瓶颈在物化（扫描+写临时表），覆盖索引或分区表直接砍这块。如果 Join optimization 或 Join reading 占比高，那就是林砚舟说的行数估算偏差，临时表拆分是对的。

四、我的默认取舍顺序（1 亿行级流水表 + 按天聚合 + JOIN 维度表）

1. 先加覆盖索引（成本最低，5 分钟 Online DDL，可回滚）→ 看 P99 是否降到 < 3s
2. 不行再上分区表（DDL 重，但一劳永逸，分区裁剪是确定性的）
3. CTE vs 临时表：聚合结果集 < 50 万行用 CTE（可读性优先）；> 50 万行拆临时表（让优化器拿到精确统计）。这个阈值取决于 buffer pool 大小——buffer pool 256GB 可以放宽到 500 万行，因为物化结果能全放内存
4. 临时表一定要加主键（merchant_id, d），否则 JOIN 时 flow_agg 侧走 ALL 扫描

这套顺序的核心逻辑：先砍 IO（覆盖索引/分区），再管优化器（临时表拆分），而不是反过来。优化器是「帮你选路」的，但如果路本身太长（1.2 亿行扫描），选得再好也慢。