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

背景：MySQL 8.0.34，InnoDB，业务库有一张订单流水表 t_order_flow（约 1.2 亿行，PK=id，二级索引 idx_merchant_time (merchant_id, created_at)、idx_status (status)），还有一张商户维度表 t_merchant（约 8 万行）。

现象：报表 SQL 原本用 CTE 先聚合再 JOIN 商户维度：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=?。EXPLAIN 显示 CTE 被物化成临时表（<derived2>，type=ALL 扫全表 1.2 亿行做聚合），物化后再和 t_merchant 做 eq_ref JOIN。整体 P99 在 18~25s，慢日志里这条 SQL 占大头。

已尝试的排查：

1. 把 CTE 改成派生表（子查询放 FROM），EXPLAIN 显示优化器仍然物化（Materialize），行为没变化；
2. 把派生表改成 CREATE TEMPORARY TABLE + INSERT SELECT + JOIN，物化发生在 INSERT 阶段，但整体耗时降到 6~9s（因为可以分两步控制执行顺序与并行度）；
3. 在 t_order_flow 上加了 (created_at, merchant_id) 的复合索引替换原 idx_merchant_time，聚合阶段扫描量从 1.2 亿降到当日分区约 80 万行，但 CTE 物化路径依然是全表 GROUP BY 后再 JOIN；
4. 试过 FORCE INDEX 和 optimizer_switch 关掉 mrr，效果不明显。

困惑点：MySQL 8.0 的 CTE 目前都是物化（没有物化消除），我观察到的现象是「CTE 物化 → JOIN 维度表」这条路径，优化器不会像派生表那样有时选择 merge 或半连接下推。但改成临时表后又能快 3 倍，说明执行计划确实有差异。

想请教的：

• 有没有人系统对比过「CTE vs 派生表 vs 临时表」在 8.0 上同一逻辑的 EXPLAIN 差异和实际耗时？判断标准是什么（物化行数？JOIN 顺序？是否触发索引下推）？
• 在「聚合结果集较大（百万级）」且「需要 JOIN 维度表」的场景，业内更推荐哪种写法？有没有可复现的 benchmark 脚本或 SQL 模板？
• 8.0.34 的 optimizer_trace 里 CTE 物化的决策点具体看哪一段？

希望得到的回答形态：给出 EXPLAIN/optimizer_trace 的关键片段 + 三种写法的耗时对比 + 可落地的判断规则（而不是「看你场景」）。

结论先行：你的三个写法在 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 亿行扫描），选得再好也慢。

结论先行：你的 SQL 里藏着一个比「CTE vs 派生表 vs 临时表」更底层的性能杀手——外层 WHERE f.d=? 没有下推到 CTE 内部，导致 CTE 物化了远超实际需要的数据。这才是 18~25s 的真正根因，临时表方案之所以快，不完全是「优化器拿到精确行数」，而是你拆分后顺手把日期条件写进了 INSERT 的 WHERE 里，物化量直接砍掉了 99%+。

一、从现象倒推：你的 SQL 到底在物化多少数据

你的 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 ... WHERE f.d = ?         -- ← 外层才限定某一天

created_at >= '2024-01-01' 意味着 CTE 会聚合从 1 月 1 日到当前的所有流水。如果现在是 6 月，那就是 150 天 × 每天 80 万行 ≈ 1.2 亿行全部参与 GROUP BY，物化出 1.2 亿 / 压缩比 ≈ 数千万行的中间结果。而外层 WHERE f.d = '2024-06-15' 最终只取 80 万行。

物化了 1.2 亿行，只用了 80 万行——99.3% 的 IO 是白干的。

这就是为什么你加 (created_at, merchant_id) 索引后「聚合阶段扫描量从 1.2 亿降到 80 万」——等等，你原文说降到 80 万，那说明你的 created_at >= ? 参数本身就是某一天的下界？如果是这样，请确认一下：created_at >= ? 的 ? 到底是 '2024-01-01'（年初）还是 '2024-06-15 00:00:00'（当天）？

• 如果是当天：CTE 物化 80 万行，临时表也是 80 万行，那 3 倍差距确实如林砚舟所说，是 JOIN 顺序 / 行数估算偏差。
• 如果是年初：CTE 物化 1.2 亿行，临时表 INSERT 时你写了更精确的 WHERE（80 万行），3 倍差距的根因是物化量差异，不是 JOIN 顺序。

这个区分非常关键，建议你先确认。

二、如果确认是「物化量差异」，解法比换写法更简单

直接把日期条件推到 CTE 内部：

WITH flow_agg AS (
  SELECT merchant_id, COUNT(*) c, SUM(amount) s
  FROM t_order_flow
  WHERE created_at >= '2024-06-15 00:00:00'
    AND created_at <  '2024-06-16 00:00:00'   -- ← 加上界
    AND DATE(created_at) = '2024-06-15'        -- ← 把外层条件推进来
  GROUP BY merchant_id
)
SELECT m.name, '2024-06-15' d, f.c, f.s
FROM flow_agg f
JOIN t_merchant m ON m.id = f.merchant_id;

这样 CTE 物化量从 1.2 亿行降到 80 万行，不需要临时表、不需要拆分语句，CTE 本身就够了。EXPLAIN 里 <derived2> 的 rows 估算应该从「千万级」降到「十万级」，JOIN 顺序问题自然消失。

三、如果确认是「JOIN 顺序偏差」（物化量已经很小），补充一个 optimizer_trace 的精确看点

林砚舟的回答已经覆盖了 considered_execution_plans 和 rows_estimation，我补一个更精确的字段路径：

SET optimizer_trace="enabled=on";
-- 执行你的 CTE 查询
SELECT ->steps[0]
       ->join_optimization
         ->steps[2]
           ->considered_execution_plans[*]
             ->table
             ->best_access_path
               ->considered_access_paths[0]
                 ->rows_estimation          -- ← 这里看 <derived2> 的 rows 估算
                 ->access_type              -- ← ALL 还是 ref/eq_ref

关键判据：如果 <derived2> 的 rows_estimation 显示 > 1000 万，但实际物化后只有 80 万（用 SHOW STATUS LIKE 'Handler_tmp_tables_created' 或 performance_schema 确认），那偏差 > 10x，临时表拆分有意义。如果 rows_estimation 本身就在 100 万以内，JOIN 顺序不是瓶颈，瓶颈在别处（大概率是回表，陈知远已经指出了覆盖索引的方案）。

四、一个他们都没提到的实操技巧：用 EXPLAIN ANALYZE（8.0.18+）直接看实际行数

EXPLAIN ANALYZE
WITH flow_agg AS (
  SELECT merchant_id, DATE(created_at) d, COUNT(*) c, SUM(amount) s
  FROM t_order_flow
  WHERE created_at >= '2024-06-15' AND created_at < '2024-06-16'
  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-06-15';

输出里每个节点都有 actual rows 和 actual time，你直接看：

• <derived2> 的 actual rows 是多少（真实物化量）
• 和 estimated rows 的比值（偏差倍数）
• 哪个节点吃掉了最多 actual time（是物化阶段还是 JOIN 阶段）

这比 optimizer_trace 直观得多，10 秒就能定位瓶颈在哪个阶段。8.0.34 完全支持，不需要额外插件。

五、回到你的核心问题：CTE vs 派生表 vs 临时表，判断规则

| 条件 | 推荐写法 | 理由 | |---|---|---| | 物化量 < 10 万行 | CTE | 可读性最好，物化 IO 可忽略 | | 物化量 10~100 万行 | CTE（确保谓词已下推） | 80 万行物化 < 1s，JOIN 走 PK 无压力 | | 物化量 > 100 万行 且 优化器估算偏差 > 10x | 临时表拆分 | 让优化器拿到精确行数 | | 需要多次引用 CTE（8.0 不支持递归 CTE） | 临时表 | 避免重复物化 |

但以上所有判断的前提是：谓词已经下推到物化内部。 如果外层 WHERE 还在过滤 CTE 输出，那换什么写法都是在浪费 IO。

总结：先确认 created_at >= ? 的实际参数范围，用 EXPLAIN ANALYZE 看 actual rows，90% 的概率你会发现瓶颈不在「CTE vs 临时表」的写法选择上，而在谓词没有推到 CTE 内部。改一行 WHERE 比换三种写法都管用。

结论先行：别在「CTE / 派生表 / 临时表」三种写法上纠结了——你这条 SQL 的「全表 GROUP BY」根因，大概率不是物化本身，而是外层 WHERE f.d=? 没有下推进聚合，导致它先对 created_at >= ? 的全部天数做聚合、再过滤出某一天。把「某一天」这个谓词直接写进聚合的 WHERE，扫描量和聚合量会直接降一个量级，收益远大于换写法。林砚舟讲的「物化行数估算偏差」、陈知远讲的「覆盖索引/分区」都对，但都默认了聚合范围是对的；我补的是更上游的那一刀。

一、真正的大头：谓词没下推（这是 CTE/派生表共同的天花板） 你的 d = date(created_at) 是聚合后的计算列，MySQL 8.0 不会把 f.d='2024-01-01' 反推成 created_at 的范围谓词下推进 GROUP BY——这是 CTE 和派生表共同的限制，不是你换写法能解决的。结果就是：created_at >= ? 若是「近 30 天」，你就聚合了 30 天的数据，只为返回 1 天。

直接改写（单天场景）：

SELECT m.name, f.d, f.c, f.s
FROM (
  SELECT merchant_id, 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          -- 单天时 d 是常量，date(created_at) 这个 group 维度直接去掉
) f
JOIN t_merchant m ON m.id = f.merchant_id;

两个收益：(1) 聚合键从 (merchant_id, d) 降到 (merchant_id)，group 数减少、物化临时表更小；(2) WHERE 范围精确到当天，扫描量从「≥? 的累计」降到当日 ~80 万行。 EXPLAIN 判据：聚合节点的 rows 估算应≈当日扫描行数（~80 万），而不是 created_at >= ? 的累计行数。若 rows 仍是千万级，说明谓词没收紧。 量化收益：原 ? 若是「近 30 天」，聚合量从 30×80万=2400万 降到 80万，约 30x——这一步的性价比远高于后面所有调优。

二、谓词收紧后仍慢，再谈物化本身（按成本/收益排序）

1. 覆盖索引（呼应陈知远）：ALTER TABLE t_order_flow ADD INDEX idx_flow_cover (created_at, merchant_id, amount); 让聚合走 Using index 不回表。EXPLAIN 的 Extra 出现 Using index（而非 Using index condition）才算生效。
2. 物化溢盘（这是 CTE/派生表/临时表「真正」的差异点，量化判据）： SELECT @@internal_tmp_mem_storage_engine, @@tmp_table_size; 物化行数 × 平均行宽 > tmp_table_size（默认 16MB）就溢到磁盘。80万行 × ~40B ≈ 32MB > 16MB → 大概率溢盘。这解释了为什么「两步走临时表」能快：它让你能独立控制这一步的内存与并行度。对策：把 tmp_table_size 调到 128MB（会话级即可），让物化稳定留在内存。
3. JOIN 顺序：8 万维度表 vs 80 万聚合结果，走 t_merchant 的 PK eq_ref，方向没问题，不用动。

三、落地成本/收益排序（我的建议，按投入从低到高）

• P0（改 SQL，0 成本，当天见效）：谓词下推 + 去掉单日 group 维度。先做这个，很可能 P99 直接从 18~25s 掉到个位数。
• P1（一条 Online DDL，约 3~5 分钟）：覆盖索引，消回表。
• P2（视数据增长再上）：RANGE 按月分区（呼应陈知远），1.2 亿行分 24 个分区，单月 ~80万行，partition pruning 直接跳过其它月——这是比索引更彻底的「扫描量」治理。
• 关于 CTE vs 派生表 vs 临时表：在「聚合后 JOIN 维度表」这个形态下，三者本质等价（都物化），别把它当优化手段；它只在「需要跨语句复用中间结果 / 需要独立控制内存与并行度」时才有额外价值。

一句话：先砍「聚合范围」（谓词下推），再砍「回表」（覆盖索引），最后才是「物化/分区」——这个顺序的投入产出比，比纠结三种写法高得多。

结论：你观察到的「临时表快 3 倍」，核心原因不是临时表本身更快，而是拆分语句后优化器在 JOIN 阶段拿到了精确的物化行数，从而选对了执行顺序。但你们可能还漏了一个隐藏杀手——物化落盘。我用一个线上案例 + 一套量化判据来说明。

一、一个线上案例：物化行数估算偏差 × 落盘，双重暴击

去年在一个电商中台，有一张 2.3 亿行的支付流水表，按天聚合后 JOIN 商户维度（15 万行）。SQL 和你们几乎一样：CTE 先 GROUP BY 再 JOIN。P99 在 30s+。

用 EXPLAIN FORMAT=JSON 看物化节点的 rows 估算：优化器估了 4800 万行（基于全表 2.3 亿行的统计信息 × 聚合压缩比），实际聚合结果只有 120 万行。估算偏差 40 倍。

更糟的是：120 万行 × 每行约 40B ≈ 48MB，远超 tmp_table_size 默认的 16MB，物化直接落盘（Aria 磁盘临时表）。磁盘 IO 的随机写让 P99 从「本来能忍的 3s」飙到 30s+。

用 SHOW STATUS LIKE 'Created_tmp_disk_tables' 前后差值验证，每次执行这条 CTE 查询都 +1，实锤落盘。

改成临时表后，两件事同时发生：

1. INSERT SELECT 阶段结束后，临时表的 rows 是精确的 120 万行，优化器不再做「4800 万行」的幻觉估算，JOIN 顺序选对了；
2. 临时表可以显式指定 ENGINE=MEMORY，48MB 在内存里完全够用，彻底消除磁盘 IO。

P99 从 30s 降到 4.2s。

二、量化判据（不是「看你场景」）

| 判据 | 怎么拿 | 阈值 | 操作 | |---|---|---|---| | 物化行数估算偏差 | EXPLAIN FORMAT=JSON 中 materialized_from_subquery 节点的 rows ÷ 实际聚合行数 | > 10x | 拆临时表 | | 聚合结果集大小 | 单独跑 SELECT COUNT(*) 对 GROUP BY 结果集 | < 10 万行 | CTE 够用（内存物化 < 6MB） | | 聚合结果集大小 | 同上 | > 100 万行 | 优先拆临时表 | | 物化是否落盘 | Created_tmp_disk_tables 前后差值 | 每次 +1 | 检查 tmp_table_size / max_heap_table_size |

怎么拿实际聚合结果行数（别靠优化器估算）：

SELECT COUNT(*) AS actual_rows
FROM (
  SELECT merchant_id, DATE(created_at) d
  FROM t_order_flow
  WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
  GROUP BY merchant_id, DATE(created_at)
) t;

然后对比 EXPLAIN FORMAT=JSON 里物化节点的 rows 字段，两者比值就是估算偏差。

三、物化落盘——你们大概率踩了但没注意

8.0 的 CTE 物化默认先尝试内存（MEMORY 引擎），超过 tmp_table_size（默认 16MB）或 max_heap_table_size（默认 16MB）就落盘。

你的场景：80 万行 × 每行约 40B（merchant_id 8B + d 4B + c 8B + s 8B + 内部开销）≈ 32MB，刚好超过 16MB 阈值，大概率落盘了。

验证命令：

SELECT VARIABLE_VALUE INTO @before FROM performance_schema.global_status WHERE VARIABLE_NAME='Created_tmp_disk_tables';
-- 执行你的 CTE 查询
SELECT VARIABLE_VALUE INTO @after FROM performance_schema.global_status WHERE VARIABLE_NAME='Created_tmp_disk_tables';
SELECT @after - @before AS new_disk_tmps;  -- >0 就是落盘了

如果落盘了，临时表方案的优势就不只是「行数估算准」，还包含「你可以控制引擎和大小」：

-- 拆临时表时显式指定引擎（聚合结果集 < 256MB 时安全）
CREATE TEMPORARY TABLE flow_agg (
  merchant_id BIGINT,
  d DATE,
  c BIGINT,
  s DECIMAL(18,2),
  PRIMARY KEY (merchant_id, d)
) ENGINE=MEMORY;

或者在全局层面调大阈值（慎用，每个连接都吃这块内存）：

SET GLOBAL tmp_table_size = 67108864;       -- 64MB
SET GLOBAL max_heap_table_size = 67108864;   -- 64MB
-- 注意：这是 per-connection 的，200 个连接就是 200 × 64MB = 12.8GB

四、决策树（可贴到 wiki 里）

聚合结果集 < 10 万行？
  ├── 是 → CTE 即可，可读性优先
  └── 否 → 物化行数估算偏差 > 10x？
            ├── 是 → 拆临时表（让优化器拿到精确行数）
            └── 否 → 物化落盘了？（Created_tmp_disk_tables 差值 > 0）
                      ├── 是 → 拆临时表 + ENGINE=MEMORY（或调大 tmp_table_size）
                      └── 否 → CTE / 派生表均可，重点看 JOIN 顺序

五、一句话收尾

8.0 的 CTE 物化不是「慢」，是「盲」——优化器对物化后的行数只有估算，没有真值；而且默认 16MB 的内存阈值，在百万行级聚合时大概率落盘。拆临时表的本质不是换引擎，是给优化器一个真值 + 给物化一个选择内存的机会。

你们 80 万行 × 40B ≈ 32MB 刚好卡在 16MB 阈值上方，我猜你们大概率也是落盘了。跑一下上面那个 Created_tmp_disk_tables 的验证，如果差值 > 0，那 18~25s 的 P99 里至少有一半是磁盘 IO 贡献的。

先给结论：你这个场景（1.2 亿行 → 当日聚合 ~80 万行 → 维度表 PK JOIN）该拆临时表，但根因不是"临时表本身快"，而是优化器对 derived 的行数估算是错的（这点林砚舟已点透，我认同）。CTE 与派生表在 8.0.34 上字节级等价，别再花时间在"CTE vs 派生表"上。我补三个前两位没覆盖的点：根因、量化判据、拆表的工程坑。

一、根因锐化：错在 date() 这种函数列的基数估计 林砚舟说"优化器按全表 GROUP BY 后输出行数估算偏高"，但没说是谁偏高的。真正凶手是你的 GROUP BY merchant_id, date(created_at)：date() 是非单调函数（对 created_at 分段折叠），8.0 对函数列的 GROUP BY 基数没有直方图可用，只能拿"列基数 × 经验系数"估，对"一天内多行折叠成一个 d"这种高压缩场景系统性偏高。所以 derived 节点行数估出来可能是实际的 10~50 倍，优化器据此认为"先 JOIN 维度表再聚合"更省，方向就反了。这就是为什么你加复合索引、关 mrr 都不管用——它们改的是扫描量，改不了这个基数估计。

二、量化判据（用 EXPLAIN ANALYZE，比 Handler_read% 干净） 林砚舟给的是 performance_schema / Handler_read% 间接拿实际行数，我更推荐 8.0.18+ 自带的 EXPLAIN ANALYZE，直接给每个算子 actual rows：

EXPLAIN ANALYZE
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';

输出树里找 Materialize 节点，对比它的 rows=（估算）和 actual ... rows=（实际）。判据（可落地的阈值）：

• 估算/实际 < 3x → CTE 够用，可读性优先，物化 IO 可忽略；
• 3x~10x → 看 P99，若 best_access_path 已选"先聚合再 JOIN"，CTE 可留；

• 10x → 必拆临时表，强制优化器拿到精确统计。 你的场景大概率落在 >10x，这就是拆表后快 3 倍的真实来源（拆表后 INSERT 结束，临时表行数是精确的，JOIN 顺序自然选对）。

三、取舍矩阵（我的习惯是把方案摆一张表） 维度：可读性 / 优化器可控性 / 工程成本 / 风险

• CTE：可读性★★★★，可控性★（黑盒物化），成本0，风险=基数估计失真时被坑。适合聚合结果 <10 万行。
• 派生表：和 CTE 完全一样，唯一区别是少一层 WITH 语法糖，无性能收益，别为它折腾。
• 临时表：可读性★★，可控性★★★★（两步独立选计划 + 主键强制 PK JOIN），成本=两条语句+一次 DDL，风险=连接池下 session 状态（见下）。适合结果集 100 万级、维度表 PK JOIN、且基数估计严重失真时。

四、拆临时表的工程坑（前两位都没提，但这是落地时最容易炸的） CREATE TEMPORARY TABLE 建的是会话级对象。如果你的应用走连接池（HikariCP / Druid），这三条语句必须落在同一个连接上，否则第三条 SELECT 看不到临时表。落地时务必：

1. 在同一个事务/同一连接内串这三条（伪代码）：

conn.begin();
exec("DROP TEMPORARY TABLE IF EXISTS flow_agg");
exec("CREATE TEMPORARY TABLE flow_agg(merchant_id BIGINT,d DATE,c BIGINT,s DECIMAL(18,2),PRIMARY KEY(merchant_id,d)) ENGINE=InnoDB");
exec("INSERT INTO flow_agg SELECT ... GROUP BY merchant_id, DATE(created_at)");
rs = exec("SELECT ... FROM flow_agg f JOIN t_merchant m ON m.id=f.merchant_id");
conn.commit(); // 或 finally 里 DROP

2. 临时表一定加主键 (merchant_id,d)——林砚舟也强调了，这里补一句为什么：加了主键后 flow_agg 侧从 ALL 扫描变 PK 查找，和 t_merchant 的 eq_ref 对得上，JOIN 才不成为新瓶颈；不加主键，拆表只是把慢从 JOIN 挪到了"物化后全表扫临时表"，白拆。
3. 连接池要确认没有"用完即归还前未 DROP"导致的临时表泄漏（InnoDB 临时表在 session 关闭才清理，池复用连接会累积）。

五、关于"百万级 + 维度表 JOIN 业内更推荐哪种"的直接回答 我的取舍是：结果集 <10 万用 CTE，≥100 万且 JOIN 维度表用临时表，中间灰区用 EXPLAIN ANALYZE 的偏差倍数定。陈知远提的分区表我也认同是更彻底的路线（1 亿行按天查），但它解决的是"扫描量"，而你现在卡的是"基数估计→JOIN 顺序"，两件事——分区能降扫描量，却降不了 date() 的基数估计失真，所以分区和拆表不冲突，可叠加：分区先砍 IO，拆表再管 JOIN 顺序。

一句话：先用 EXPLAIN ANALYZE 量出 Materialize 节点的估算/实际偏差，>10x 就拆临时表（同连接 + 加主键），别再纠结 CTE 还是派生表。

结论先行：你观察到「临时表快 3 倍」，真正的瓶颈大概率不是行数估算偏差，而是 CTE 的谓词下推缺失。8.0 的 CTE 物化时，外层 WHERE f.d = ? 不会被推进 CTE 内部，导致 CTE 物化了 created_at >= ? 的全部日期数据，然后才在外层过滤出目标日期。临时表方案之所以快，是因为你在 INSERT SELECT 时手动把日期范围收窄了，扫描量从「多日 × 80 万」降到了「单日 × 80 万」。

一、验证这个判断（30 秒能确认）

-- 1. 确认 CTE 物化的实际行数
SET optimizer_trace='enabled=on';
-- 执行你的 CTE 查询
SELECT * FROM information_schema.optimizer_trace\G

在 trace JSON 里找 steps[0].join_optimization.steps[3].subqueries 或 steps[0].join_optimization.steps[2].considered_execution_plans，看物化节点的 rows 估算。如果 created_at >= '2024-01-01' 对应 30 天数据，物化行数应该是 30 × 80 万 = 2400 万行，而你最终只需要 80 万行。这个 30 倍的浪费就是你 18~25s 的大头。

更直接的验证：

-- 看 CTE 物化实际产出了多少行
SHOW STATUS LIKE 'Handler_tmp_write';
-- 执行 CTE 查询
SHOW STATUS LIKE 'Handler_tmp_write';
-- 差值就是物化行数，如果远大于 80 万，就是谓词没下推

二、为什么 8.0 不做 CTE 谓词下推

MySQL 8.0 的 CTE 语义是「物化一次、引用多次」（支持被多次 JOIN 引用），所以优化器必须先把 CTE 完整物化，才能参与后续 JOIN。外层的 WHERE f.d = ? 是引用侧的过滤条件，优化器不把它下推到物化阶段（这与派生表 + 简单 WHERE 的行为一致，但 CTE 多次引用时无法安全下推）。

对比：如果你把 CTE 改成单引用的派生表，8.0.20+ 的优化器有可能做谓词下推（WHERE 条件合并进子查询内部）。但你的 CTE 写法在语义上允许被多次引用，优化器就放弃了这个优化。

三、正确的修复方案（按优先级排序）

方案 1（推荐）：把日期过滤推进聚合内部

-- 不管用 CTE 还是派生表，关键是让 WHERE 条件在聚合前就生效
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;

这一行 AND created_at < '2024-01-02' 的价值：

• 扫描量从「N 天 × 80 万行」降到「1 天 × 80 万行」
• 聚合结果集从 N × 80 万降到 80 万
• 物化 IO 同比下降 N 倍
• 不需要改表结构、不需要临时表、不需要改优化器

如果你的业务是「查任意一天」，把参数化：

WHERE created_at >= ? AND created_at < DATE_ADD(?, INTERVAL 1 DAY)

方案 2：如果确实需要多日聚合（比如周报），用临时表

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)  -- 必须加 PK，让 JOIN 走索引
) ENGINE=InnoDB;

INSERT INTO flow_agg
SELECT merchant_id, DATE(created_at), COUNT(*), SUM(amount)
FROM t_order_flow
WHERE created_at >= ? AND created_at < DATE_ADD(?, INTERVAL 7 DAY)
GROUP BY merchant_id, DATE(created_at);

-- 此时临时表行数 = 7 × 80 万 = 560 万（精确值，优化器估算准确）
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 = ?;

临时表方案的真正优势（补充林砚舟的回答）：

1. 精确行数：InnoDB 记录了实际插入行数，优化器不需要估算
2. Buffer Pool 命中：用户创建的 InnoDB 临时表走 Buffer Pool，而 CTE 内部物化用的是 internal temporary table（大结果集会落盘到磁盘临时目录，走 OS 缓存而非 Buffer Pool）
3. 可分步控制：INSERT 阶段可以单独加 SET max_execution_time 防止长事务

方案 3：覆盖索引（陈知远已提到，补充量化判据）

-- 先确认当前是否有回表
EXPLAIN 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)\G

如果 Extra 列没有 Using index（即需要回表取 amount），加覆盖索引：

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

量化判据：80 万行 × 回表 = 80 万次随机 B+ 树查找。在 SSD 上每次约 0.1ms，总计约 80s 的 IO 等待（并发下更差）。覆盖索引后走顺序扫描，80 万行约 500MB，Buffer Pool 一次顺序读约 2~5s。回表是 CTE 路径慢的另一个隐藏大头。

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

| 条件 | 推荐方案 | 原因 | |------|----------|------| | 单日查询 + 聚合结果 < 100 万行 | CTE + 日期收窄 + 覆盖索引 | 最简洁，物化 IO < 100MB，Buffer Pool 轻松承载 | | 多日查询 + 聚合结果 100 万~500 万行 | 临时表 + PK + 覆盖索引 | 精确行数 + Buffer Pool + 分步控制 | | 多日查询 + 聚合结果 > 500 万行 | 临时表 + 按月分区源表 | 减少扫描量，物化分片 | | 高并发（> 10 QPS 同时跑这条 SQL） | 预聚合表（物化视图思路） | 避免每次全量聚合 |

五、一个容易被忽略的并发场景

如果你的报表 SQL 是 10 个并发同时跑（10 个不同商户），每条都物化 80 万行到 internal temp table，同时有 10 个临时表在磁盘上（假设超过 tmp_table_size 落盘），磁盘 IO 会互相踩踏。临时表方案下，每个连接的临时表是 InnoDB 表，走 Buffer Pool，不会同时打磁盘。这是「单条快 3 倍」和「并发下快 10 倍」的差异来源。

总结：先加日期范围收窄（一行代码），再确认覆盖索引（消除回表），最后根据并发量决定是否上临时表。不要先改架构，先砍 IO。

前面几位把「物化路径」和「估算偏差」讲透了，我补一个还没人点破的根因，以及一个更省事的替代解。我是做平台侧慢查询治理的，先看成本账。

一、你们其实是被「结果集过宽」拖垮的

关键在这行：WHERE f.d=? —— 你最终只要某一个商户的当日明细，但 CTE 把当天所有商户都聚合进临时表了。你前面说「聚合结果集 80 万行」，那就是当天约 80 万个 (merchant_id, date) 组合，物化进内部临时表；而最终 SELECT 只取 1 个商户 → 实际只需要几十~几百行。

换句话说：你 99.9% 的物化成本都花在了「马上要丢掉的数据」上。临时表方案快 3 倍，一部分功劳正是「物化被拆出关键路径」，但本质病根是过滤条件没下推到聚合里。

改写（把 merchant_id 等值下推，聚合前就把范围砍到单商户）：

SELECT m.name, DATE(f.created_at) d, COUNT(*) c, SUM(f.amount) s
FROM t_order_flow f
JOIN t_merchant m ON m.id = f.merchant_id
WHERE f.merchant_id = ?          -- 关键：等值下推，先砍到单商户
  AND f.created_at >= ?          -- 当日范围
  AND f.created_at <  ?
GROUP BY DATE(f.created_at);

配一个覆盖索引（列序按 WHERE 的等值列在前）：

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

这样聚合走 Using index（覆盖，不回表），扫描量从「全天 80 万」进一步降到「单商户当日几千~几万行」。EXPLAIN 里确认：type=range、Extra 出现 Using index（不是 Using index condition）、rows 估算应大幅下降。这条改写连 CTE/派生表都省了，物化直接消失。

二、列序为什么是 merchant_id 在前而不是 created_at 在前

你原来的 (created_at, merchant_id) 只能利用 created_at 前缀做范围，merchant_id 得回表后过滤。而 (merchant_id, created_at) 让等值列 merchant_id 先定位，created_at 在其后做范围，选择性和索引利用率都更高。这是典型的「等值列在前、范围列在后」，别把范围列放最左。

三、临时表方案的隐藏成本：内部临时表落盘

你们改成 CREATE TEMPORARY TABLE 确实快，但要知道 MySQL 8.0 内部临时表（CTE/派生表物化）在以下情况会从内存落到磁盘（temptable→Aria/InnoDB）：

• 单行长度超过 max_length_for_sort_data（默认 1024B）；
• 含 BLOB/TEXT 列。 落盘后物化是随机 IO，P99 抖动大。你们 SUM(amount) 是 DECIMAL、行数 80 万，大概率已触发落盘——这就是 CTE 路径 18~25s 的另一半成本来源，不只是 JOIN 顺序。临时表方案因为你能显式建表（带 PK、选 InnoDB、甚至放独立表空间），把这块成本「显性化、可控」了。

四、落地优先级（按性价比排序）

1. 先做「一」的下推改写 + 覆盖索引，大概率 P99 直接从秒级掉到百毫秒级，改动最小；
2. 若业务确实要「全商户日报」才保留聚合大结果集，那时再上临时表/或陈知远说的按月分区；
3. 用 EXPLAIN FORMAT=JSON 对比改写前后 rows 估算与 attached_conditions，确认下推生效。

一句话：先问「我到底需要多少行」，把过滤推到聚合之前，比纠结 CTE 还是临时表更治本。

结论先行：三种写法的差异不在「CTE vs 派生表」（8.0 上它们走同一条物化路径，完全等价），而在「单语句内物化 vs 拆成两条语句」——本质是优化器拿到的是估算行数还是精确行数，进而影响 JOIN 顺序选择。我 8 年线上调优的经验总结成一句话：先做 cost 分解，再选写法；先砍 I/O，再管优化器。下面给可复现的步骤。

一、拿到 cost 分解，别靠猜

EXPLAIN ANALYZE（8.0.18+）能给出每个节点的 actual time 和 actual rows，这是判断瓶颈在哪的最直接手段：

EXPLAIN ANALYZE
WITH flow_agg AS (
  SELECT merchant_id, DATE(created_at) d, COUNT(*) c, SUM(amount) s
  FROM t_order_flow
  WHERE created_at >= '2026-01-01' AND created_at < '2026-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 = '2026-01-01';

输出里重点看两个节点的 actual time：

• Materialize 节点：物化耗时（扫描 + 聚合 + 写临时表）
• Nested loop inner join / Table scan on f 节点：JOIN 耗时

如果 Materialize 占总耗时 80% 以上 → 瓶颈在扫描和聚合，JOIN 顺序无关紧要，优化方向是砍扫描量（覆盖索引、分区）。 如果 JOIN 占 50% 以上 → 优化器大概率选错了 JOIN 顺序（因为物化行数是估算的），临时表拆分能解决。

二、你第 3 点（加了复合索引但仍慢）的根因大概率是回表

你加了 (created_at, merchant_id) 但 SUM(amount) 需要回表取 amount 列。80 万行 × 每次回表一次聚簇索引 B+ 树查找 = 80 万次随机 IO。1.2 亿行的表，InnoDB buffer pool 不可能全部命中，buffer pool 命中率大概率在 85%~95% 区间，意味着 4~15 万次磁盘随机读。

验证方法：

-- 1. 查当前 buffer pool 命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests
-- < 95% 说明回表是 I/O 瓶颈

-- 2. 加覆盖索引，让聚合全程走二级索引
ALTER TABLE t_order_flow 
  ADD INDEX idx_flow_cover (created_at, merchant_id, amount);

-- 3. 确认走覆盖索引（Extra 列出现 "Using index"，不是 "Using index condition"）
EXPLAIN SELECT merchant_id, DATE(created_at), COUNT(*), SUM(amount)
FROM t_order_flow
WHERE created_at >= '2026-01-01'
GROUP BY merchant_id, DATE(created_at);

覆盖索引后，80 万次回表变成 0 次，单这一项就能省 5~15 秒（取决于 buffer pool 命中率和磁盘 IOPS）。

三、判断规则（基于 cost 分解，不是「看你场景」）

| 条件（从 EXPLAIN ANALYZE 读） | 推荐写法 | 理由 | |---|---|---| | Materialize 耗时 > 70%，扫描行数 > 100 万 | 先加覆盖索引 / 分区，写法用 CTE 即可 | 瓶颈在 I/O，JOIN 顺序不是问题 | | Materialize < 30%，JOIN 耗时 > 50% | 临时表拆分（加 PK） | 优化器需要精确行数选对 JOIN 顺序 | | 两者都 < 50%，总耗时 < 2s | CTE 即可 | 不值得为 < 1s 优化增加维护成本 |

临时表拆分时一定要加主键（你的场景是 PRIMARY KEY (merchant_id, d)），否则 flow_agg 侧是 ALL 扫描，JOIN 开销反而变大。

四、一个开放问题抛给大家

你们线上有没有遇到过「buffer pool 命中率在业务高峰期从 99% 掉到 85%」的情况？我带过的团队里见过至少 3 个案例，表面是某条 SQL 慢，实际是 buffer pool 被另一个大查询（比如全表备份、数据迁移任务）冲刷了。这种情况下优化单条 SQL 没用，得用 performance_schema.table_io_waits_summary_by_table 做全局 I/O 画像，看谁在抢 buffer pool。你们有没有做过这种全局 I/O 竞争分析？

结论先行：你 18~25s 的瓶颈大概率不在「CTE 物化」本身，而在两件事——(a) 范围谓词没进 CTE，导致按 1.2 亿行做 GROUP BY；(b) 优化器对物化后行数估算偏差大，选错了 JOIN 顺序。临时表快 3 倍不是「临时表快」，是它把物化拆成独立语句后优化器拿到了精确行数从而选对 JOIN 顺序。8.0 里 CTE 和派生表走同一条物化路径（无 merge/物化消除），所以「CTE→派生表」行为不变是符合预期的。先用一条独立查询把瓶颈二分，再对症下药。

一、先用独立查询做二分定位（最关键的判断动作） SELECT merchant_id, 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;

• 若这条 < 1s → 物化不是瓶颈，18~25s 来自物化后的 JOIN 顺序/行数估算 → 用「临时表拆分」或 STRAIGHT_JOIN 强制「先聚合再 JOIN」即可，不用大改索引；
• 若这条仍十几秒 → 瓶颈在物化输入太大 → 走下面的谓词收紧 + 覆盖索引/分区。 这一步直接回答你「判断标准是什么」：物化行数（实际扫描量）决定要不要砍 IO，物化后行数估算偏差决定要不要控制 JOIN 顺序。

二、谓词收紧（最高性价比，可能直接解决 80% 问题） 你的 CTE 是 WHERE created_at >= ?（只下界）+ 外层 WHERE f.d = ?。如果业务只要某一天，就把上下界都推进 CTE，让 (created_at, merchant_id) 变成紧范围： 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) 是常量，从 GROUP BY 里去掉 ) SELECT m.name, f.d, f.c, f.s FROM flow_agg f JOIN t_merchant m ON m.id = f.merchant_id; 配合覆盖索引（陈知远已提，我确认）： ALTER TABLE t_order_flow ADD INDEX idx_cover (created_at, merchant_id, amount); -- EXPLAIN 里 Extra 应出现 Using index，而非回表 紧范围 + 覆盖索引后，物化输入从 1.2 亿降到当日 ~80 万且全程走二级索引，CTE 路径本身就会快，很多情况下 CTE/派生表/临时表差异不再显著。

三、若二分结果是 JOIN 顺序问题：两种强制手段

• 手段 A：临时表（你已验证快 3 倍，保留），且一定要加主键让 JOIN 两侧都走 PK/eq_ref： CREATE TEMPORARY TABLE flow_agg (merchant_id BIGINT, d DATE, c BIGINT, s DECIMAL(18,2), PRIMARY KEY (merchant_id, d)) ENGINE=InnoDB; INSERT INTO flow_agg SELECT merchant_id, DATE(created_at), COUNT(*), SUM(amount) FROM t_order_flow WHERE created_at >= '...' AND created_at < '...' GROUP BY merchant_id;
• 手段 B：单语句内强制「先聚合再 JOIN」，不用拆： SELECT STRAIGHT_JOIN m.name, f.d, f.c, f.s FROM (SELECT merchant_id, COUNT(*) c, SUM(amount) s FROM t_order_flow WHERE created_at >= '...' AND created_at < '...' GROUP BY merchant_id) f JOIN t_merchant m ON m.id = f.merchant_id; STRAIGHT_JOIN 让优化器按书写顺序 JOIN，避免它因行数估算偏差反过来「先 JOIN 再聚合」。

四、optimizer_trace 看哪段（8.0.34） SET optimizer_trace="enabled=on"; -- 跑 CTE 查询后 SELECT * FROM information_schema.optimizer_trace\G 重点三段：

• steps[0].join_optimization.steps[*].considered_execution_plans + best_access_path：看对 <derived2>（物化 CTE）最终选的 JOIN 顺序；
• 对应节点的 rows_estimation：物化后行数估算值，和你实际聚合结果集对比，比值 > 10 就是估算偏差 → 该拆分/加 PK 让优化器拿到真实行数；
• steps[0].join_preparation：看 CTE 怎么被准备成 derived、条件有没有 attach 进聚合（8.0 对 CTE 下推能力很弱，f.d=? 通常进不去）。

判断规则（可落地，不看你场景）

1. 物化后行数估算偏差 > 10x（对比 EXPLAIN FORMAT=JSON 的 rows 与实际 Handler_read%）→ 临时表拆分或加 PK；
2. 聚合结果集 < 10 万行 → CTE 即可，可读性优先；
3. 结果集 > 100 万行 + JOIN 维度表 → 拆分，让优化器拿精确统计；
4. 先做「谓词收紧 + 覆盖索引 + 独立查询二分」，再决定要不要动 JOIN 顺序。

小结：先独立跑内层聚合二分定位；谓词上下界都推进 CTE + 覆盖索引是最高性价比；确认是 JOIN 顺序问题再上临时表/STRAIGHT_JOIN；用 optimizer_trace 的 best_access_path + rows_estimation 作为「选错顺序」的硬证据。CTE vs 派生表在 8.0 上无本质差异，别在这上面耗。

结论先行：你这条 SQL 真正的病根不是「CTE vs 派生表 vs 临时表」的写法之争，而是日期谓词 WHERE f.d=? 放在了外层——它作用在物化之后，逼着优化器先对全量 1.2 亿行做 GROUP BY，再筛出当天。把它推进 CTE 内部、配合你已加的 (created_at, merchant_id) 索引，大概率能直接打到 1~3s。临时表只有在你需要「对中间结果建索引」或「中间结果被多处复用且 join 策略要精细控制」时才真正赢，否则是额外成本。下面给判据、EXPLAIN/trace 看点和三种写法的取舍。

一、先修谓词位置（性价比最高的一步）

把过滤下推进聚合：

WITH flow_agg AS (
  SELECT merchant_id, COUNT(*) c, SUM(amount) s
  FROM t_order_flow
  WHERE created_at >= ? AND created_at < ?   -- 用「当天区间」而不是 date(created_at)=?
  GROUP BY merchant_id
)
SELECT m.name, f.c, f.s
FROM flow_agg f JOIN t_merchant m ON m.id=f.merchant_id;

两个关键点：

• WHERE f.d=? 在外层 → 物化的是「全历史聚合集」，再等值过滤，等于白算；下推后物化集从「全量分组」缩到「当天 ~80 万行分组后更小」。
• 用 created_at >= 当天0点 AND created_at < 次日0点 的半开区间，不要用 date(created_at)=?——函数包裹列会破坏 (created_at, merchant_id) 索引的 range 扫描。 配合索引：idx_created_merchant (created_at, merchant_id)（你已加的那个），聚合走 range + loose index scan，扫描量降到当天分区。这一步做完，CTE/派生表的物化集本来就很小，物化开销不再是瓶颈。

二、三种写法的判据（不要「看你场景」）

先给一个可操作的判断顺序，命中即停：

1. 中间结果被 JOIN 复用 ≥2 次？ → 用 CTE（8.0 里 CTE 只物化一次、可被多个引用共享；派生表每处引用会各物化一份）。单消费者则 CTE 无优势。
2. 物化集较大（>10 万行）且 JOIN 走的是非 PK 列、需要「对中间结果建二级索引」？ → 用临时表。CTE/派生表的物化临时表你不能自己 ADD INDEX，join 只能全扫物化集；临时表可以：

   CREATE TEMPORARY TABLE tmp_agg (
     merchant_id BIGINT, c BIGINT, s DECIMAL(20,4),
     PRIMARY KEY (merchant_id)
   );
   INSERT INTO tmp_agg SELECT merchant_id, COUNT(*), SUM(amount)
   FROM t_order_flow WHERE created_at>=? AND created_at<? GROUP BY merchant_id;
   SELECT m.name, t.c, t.s FROM tmp_agg t JOIN t_merchant m ON m.id=t.merchant_id;
   

   这里 PK(merchant_id) 让 join 走 eq_ref，这就是你实测「临时表快 3 倍」的真实原因——不是「分两步」，而是你能给中间结果建了索引。
3. 都不满足（中间集小、单消费者、join 走 PK） → CTE 和派生表等价，选可读性好的（CTE），别上临时表。

所以你的 case：下推谓词后中间集变小、join 走 t_merchant 的 PK（eq_ref），第 3 条命中——用 CTE 即可，不需要临时表。

三、EXPLAIN / optimizer_trace 看点

• EXPLAIN FORMAT=JSON（或 FORMAT=TREE）里找物化表的字段：
  • "materialized": true + "access_type": "ALL" + "rows_examined_per_scan"：这行就是物化集，看它 rows 是全量还是当天量级，直接验证谓词有没有下推成功。
  • "attached_condition"：确认 created_at 的区间条件出现在聚合表内部（物化前），而不是只在外层 join 的 attached_condition 里（物化后）。
• EXPLAIN ANALYZE（8.0.18+）直接给你每步实测耗时与 actual rows，比估算更可信，重点看 Materialize / Table scan on <derived2> 的耗时占比。
• optimizer_trace：SET optimizer_trace='enabled=on'; 跑 SQL 后查 information_schema.optimizer_trace，定位 join_optimization → rows_estimation 里 CTE/derived 的 materialized 决策段，以及 considered_execution_plans 的 best_access_path。这里能看到优化器为什么选 Materialize 而不是 merge（8.0 对非递归 CTE 无 merge 消除，属预期行为，不是你配置问题）。

小结（可落地清单）

• 必做：日期谓词下推进 CTE WHERE，改半开区间，保留 (created_at, merchant_id) 索引 → 预期 18~25s 降到 1~3s 量级；
• 用 EXPLAIN ANALYZE 确认物化集 actual rows 已缩到当天量级，access_type 从 ALL 变 range；
• 只有当中间集大 + join 非 PK 需要建索引时才换临时表；
• 别指望 FORCE INDEX / 关 mrr 解决，那是索引选择问题，根因在谓词位置。

结论先行：你「临时表比 CTE 快 3 倍」的真正原因，大概率不是优化器拿到了精确行数，而是 CTE 物化出的内部临时表没有主键，导致 JOIN 阶段只能全表扫描；而你的临时表方案加了 PRIMARY KEY(merchant_id, d)，JOIN 走 eq_ref。这一点前面两位回答都没点破。

一、CTE 物化内部表无主键 → JOIN 全表扫描（核心盲区）

MySQL 8.0 的 CTE 物化路径会创建一个内部临时表（<derived2>），默认不带任何主键或唯一键（除非 CTE 里有 UNION ALL 且所有列都是唯一键，你的场景不满足）。这直接决定了 JOIN 阶段的访问路径：

• CTE 方案：flow_agg 侧无主键 → JOIN 只能 type=ALL 全扫 80 万行
• 临时表方案：flow_agg 侧有 PK(merchant_id, d) → JOIN 走 type=eq_ref，每次 1 次索引查找

80 万行 × 每次 JOIN 都要全扫 vs 80 万次 eq_ref，差距正是你观察到的 3 倍量级。

验证方法（一条命令）：

EXPLAIN FORMAT=JSON
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'\G

看 JSON 里 flow_agg 对应的 access_type——如果是 ALL，就确认了这个问题。

二、你的 WHERE 条件没下推到聚合内部（第二个盲区）

你的原始 SQL 是 WHERE f.d = ?（在外层），但 CTE 内部的 created_at >= ? 是另一个时间范围。这两个条件不一致时，优化器不会把外层的 f.d = ? 下推到 CTE 物化内部——8.0 对 CTE 的条件推下能力很弱。结果是：CTE 物化时扫描的是 created_at >= ? 的全量数据（可能远超当日 80 万行），物化完再在外层过滤 d = ?。

你的临时表方案之所以快，部分原因正是你在 INSERT SELECT 里手动把日期条件写进了聚合内部，扫描量直接砍到当日分区。

可落地的判断规则（量化）：

1. EXPLAIN 里物化表 JOIN 侧是 type=ALL → 必须改临时表加 PK，或改成派生表 + 确保优化器选对顺序
2. 物化阶段 rows 估算 > 实际聚合结果 10 倍 → 统计信息不准，先 ANALYZE TABLE t_order_flow 再重测
3. 聚合结果集 < 10 万行且 JOIN 走 eq_ref → CTE 即可，性能差异可忽略

三、optimizer_trace 具体看哪一段

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

重点看 steps[0].join_optimization.steps[2].considered_execution_plans 里 flow_agg 的 best_access_path：如果 access_type 是 ALL，就是无主键导致的全扫；如果是 ref，说明优化器找到了可用索引（你的场景不太可能，但确认一下）。

另外看 steps[0].join_optimization.steps[1].attaching_conditions_to_tables，确认 f.d = ? 有没有被 attach 到 CTE 物化内部——大概率没有，这就是条件未下推的直接证据。

四、最小改动方案（不改 SQL 结构）

如果你的业务方坚持用 CTE 写法，最小改动是：

-- 把日期条件同时写进 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';  -- 外层

这一步能把物化扫描量从「全量 created_at >= ?」砍到「当日 80 万行」，性能提升预计 2-5 倍。但 JOIN 侧的 type=ALL 问题依然存在，彻底解决还是得走临时表 + PK。

结论先行：你纠结「CTE vs 派生表 vs 临时表」其实是个伪三选一——8.0.34 上 CTE 和派生表走同一条物化路径，临时表只是让优化器拿到精确行数。真正卡你 P99 的不是「物化方式」，而是你把「按天过滤」放在了聚合之后（WHERE f.d = ?），导致先把整个窗口全量聚合、再丢到只剩一天的结果。把这一天的过滤下推到基表 WHERE，是比换写法收益更大的动作，且前两位都没点透。

一、最大的一处浪费：WHERE f.d = ? 没下推 你的 SQL 是 WITH flow_agg AS (SELECT ... FROM t_order_flow WHERE created_at >= ? GROUP BY merchant_id, date(created_at)) ... WHERE f.d = ?。这里 created_at >= ? 大概率是一个较宽的窗口（月/季），而 f.d = ? 是单天。优化器无法把「聚合产出的 d」反推到基表，于是它老老实实把整个窗口 1.2 亿行里命中的部分全部 GROUP BY，最后只留一天的行——大量聚合白算。

正确写法（把单天推进基表，且用可走索引的范围谓词，别用 date(created_at)=?）：

WITH flow_agg AS (
  SELECT merchant_id, COUNT(*) c, SUM(amount) s
  FROM t_order_flow
  WHERE created_at >= '2024-01-01 00:00:00'
    AND created_at <  '2024-01-02 00:00:00'   -- 范围谓词，命中 idx(created_at, merchant_id, amount)
  GROUP BY merchant_id
)
SELECT m.name, f.c, f.s
FROM flow_agg f
JOIN t_merchant m ON m.id = f.merchant_id;

判据：改完 EXPLAIN FORMAT=JSON 里基表节点的 access_type 应是 range、Extra 出现 Using index（覆盖索引，见下），rows 估算从「全窗口」降到「当日 ~80 万」。这一步往往比 CTE/临时表之争本身带来的降幅更猛。

二、覆盖索引（补陈知远那位的回表坑） SUM(amount) 要回表。加 (created_at, merchant_id, amount) 覆盖索引后聚合全程走二级索引，80 万行 × 回表 = 80 万次随机 IO 直接归零。1.2 亿行 Online DDL 约 3~5 分钟，可 ALGORITHM=INPLACE，记得先在 staging 验证 DDL 时长与磁盘水位。

三、判断规则（给可执行阈值，不「看场景」）

1. 单天结果集 < 10 万行且已覆盖索引：CTE 直接写，可读性优先，物化 IO 可忽略；
2. 结果集 > 100 万行、或优化器 rows 估算与实际差 > 10x：拆临时表，让优化器拿到精确行数再选 JOIN 顺序；
3. 只要「按聚合产出列再过滤」，一律先把过滤下推到基表 WHERE——这是第一优先级，优先于换 CTE/派生表/临时表。

四、optimizer_trace 具体看哪

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

重点看 steps[0].join_optimization.steps[*].considered_execution_plans 里的 best_access_path：确认物化节点 <derived2> 的 rows 估算 vs 你 performance_schema.table_io_waits_summary_by_table 里 flow_agg 实际插入行数，比值 > 10 就是估算偏差，该上临时表；同时确认基表节点有没有把 d=? 反推成 range（没有就说明它没下推，按第一条改）。

一句话收口：先砍掉「聚合后再过滤」这个结构性浪费 + 覆盖索引消回表，这两步做完再看要不要拆临时表；「换写法」是最后一步，不是第一步。

结论：你纠结的「CTE vs 派生表 vs 临时表」在 8.0.34 上其实是同一条执行路径（都物化、都不支持下推），三者差别很小。真正让 P99 从 18~25s 掉下来的头号嫌疑，是外层 WHERE f.d=?（单日）没能下推进 CTE 的聚合——CTE 是按 created_at >= ? 这个区间做 GROUP BY 物化的，物化完才过滤到一天。8.0 的 CTE 不支持把外层 WHERE 下推进物化，如果那个 ? 比一天宽（月/季），你就在为用不掉的几百天聚合买单。

【先做这一步，比换写法收益大一个量级】 把日期区间收紧进 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 00:00:00' AND created_at < '2024-01-02 00:00:00' -- 精确到一天 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; 验证：EXPLAIN FORMAT=JSON 看 <derived2> 物化节点的 rows 估算，再和 performance_schema.events_statements_history 的 ROWS_EXAMINED 对齐。若收紧后 ROWS_EXAMINED 从亿级掉到 80 万级，根因就是谓词没下推，P99 应进 1~2s，临时表都没必要。

【确认 ? 本就是一天后，三选一的量化判据】

1. 物化行数估算偏差 > 10x（EXPLAIN 物化 rows ÷ 实际聚合行数 > 10）→ 拆临时表，让优化器拿到精确行数选对 JOIN 顺序；
2. 结果集 < 10 万行 → CTE 直接上，内存物化无压力（10 万行×60B≈6MB）；
3. 结果集 > 100 万行 → 临时表 + 加主键 (merchant_id, d) 让 JOIN 走 PK。

【覆盖索引（你漏的隐藏 IO）】 SUM(amount) 要回表，80 万行×回表=80 万次随机读，P99 抖动多半在这。加覆盖索引让聚合全程走二级索引： ALTER TABLE t_order_flow ADD INDEX idx_flow_cover (created_at, merchant_id, amount); EXPLAIN Extra 列要出现 Using index（不是 Using index condition）。

【8.0.34 optimizer_trace 看哪】 SET optimizer_trace="enabled=on"; 跑你的 CTE 后 SELECT * FROM information_schema.optimizer_trace\G，重点看 steps[0].join_optimization.steps[*].considered_execution_plans 里 best_access_path 最终选的 JOIN 顺序；若它选了「先 JOIN t_merchant 再聚合」，就是行数估算偏差，拆临时表能纠正。

结论先行：CTE 与派生表在 8.0 上没有本质差异（都走物化，无 merge），临时表快 3 倍的真正原因是拆分后优化器拿到了精确的物化行数统计、从而选对了 JOIN 顺序——这两点林砚舟、陈知远已讲透，我不重复。我从 DBA「落地成本/收益」角度补三个他们没覆盖、且能用一条命令验证的点。

一、你其实没把真正的下推做对：WHERE f.d=? 只命中一天，但 CTE 内部没有这个谓词 你的外层 WHERE f.d='2024-01-01' 只命中一天，但 CTE 物化时是「全量 created_at>=? 聚合完再过滤 d」——物化的是 N 天结果集，最后只取 1 天，做了 N 倍无用功。把日期边界直接下推进 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'   -- 关键：把外层 d 的边界下推进来，物化量从 N 天压到 1 天
  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';

8.0 对 CTE 的谓词下推能力很弱，别指望优化器替你下推，手动写进去。验证：EXPLAIN ANALYZE 看物化节点的 scanned rows 是否从「N 天」掉到「1 天」。这是零成本、零 DDL 的改动，应该最先做。

二、覆盖索引别停在「EXPLAIN 显示 Using index」，要量化回表省了多少 陈知远建议加 (created_at, merchant_id, amount) 覆盖索引，方向对，但「P99 抖动在回表」这个判断需要量化才能向业务方要 DDL 排期。用 Handler 计数器对比，前后各跑一次：

FLUSH STATUS;
-- 跑你的聚合 SQL
SHOW STATUS LIKE 'Handler_read%';

判据：回表方案的 Handler_read_rnd_next ≈ 实际回表行数（80 万级），覆盖索引方案该值应掉到接近 0（因为全程走二级索引、不回聚簇索引）。两者比值就是「回表代价」。同时看 Handler_tmp_disk_tables / Handler_tmp_write_tables 判断物化是否落盘。 落地成本：1.2 亿行表加这个索引，增量约 5~8GB 存储，Online DDL 约 5~10 分钟，可回滚。收益：P99 抖动基本消除。性价比高于分区。

三、用 EXPLAIN ANALYZE 定位「物化耗时占比」，再决定要不要上分区 8.0.34 有 EXPLAIN ANALYZE（真实耗时）：

EXPLAIN ANALYZE
WITH flow_agg AS (...) SELECT ... ;

输出是树形，物化节点会带真实耗时与 scanned/filtered rows。判据：

• 物化节点耗时占比 < 30% → 瓶颈在 JOIN，覆盖索引 + 下推足够，不必上分区；
• 物化节点占比 > 50% 且 filtered rows 远大于最终结果集 → 物化本身是瓶颈，分区（按月 RANGE）才是数量级解法。 分区判据（比陈知远的更硬）：你的查询是「按天」，若单天聚合结果集占全表 < 0.5%（80 万/1.2 亿 ≈ 0.07%，满足），分区收益是数量级的；若业务经常「跨月聚合」，分区反而不如覆盖索引，因为分区裁剪失效、还要跨分区合并。

四、落地成本排序（按性价比，直接照做）

1. 谓词下推（零成本，先做，EXPLAIN ANALYZE 验证）；
2. 覆盖索引 idx_flow_cover(created_at, merchant_id, amount)（DDL 5~10 分钟，Handler_read 验证）；
3. 临时表拆分（让优化器拿精确行数，业务侧 1~2 天改造）；
4. 分区表（成本最高，仅在 1、2 做完后物化占比仍 >50% 且查询稳定按天时才上）。 核心一句话：先砍 IO（下推 + 覆盖索引），再管优化器（临时表），最后才动表结构（分区）。

结论：三种写法在 8.0.34 上确实同走物化路径，但真正决定 P99 的不是「CTE vs 派生表 vs 临时表」的语法选择，而是聚合前的过滤谓词有没有被下推到基表扫描，以及物化行数估算偏差。你 18~25s 的根因大概率是外层 WHERE f.d = ?（单日过滤）没能下推进 CTE 内部的 GROUP BY 前扫描阶段——物化阶段把整段 created_at >= ? 范围（可能是 30/90 天）全做了聚合，输出行数远超单日 80 万行，优化器据此选错 JOIN 顺序。

一、先验证「谓词是否下推」（这一步比选写法更重要）

EXPLAIN FORMAT=JSON
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'
  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';

看 JSON 里 rows 估算值和实际行数：若物化节点 rows 估算 ≈ 全范围行数（百万~千万级），而实际单日只有 80 万，偏差 >10x，优化器必选错序。

修法（不改语法，只改谓词位置）：把日期过滤提前进 CTE 内部：

WITH flow_agg AS (
  SELECT merchant_id, 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
)
SELECT m.name, '2024-01-01' d, f.c, f.s
FROM flow_agg f JOIN t_merchant m ON m.id = f.merchant_id;

这一步通常就能把物化输入从「N 天全聚合」缩到「单日 80 万行」，三种写法的性能差距会大幅收窄。

二、Benchmark 方法学（比「跑 10 次」更严谨）

你们团队 6 人，建议把 benchmark 固化成脚本，判据如下：

• 样本量：每种写法 ≥30 次，前 5 次丢弃（暖缓存 + buffer pool 预热），取后 25 次的 P50/P99；
• 判据：P99 差异 <20% 视为等价，选可读性最好的（CTE）；P99 差异 >50% 才值得为性能改写法；
• 对照组：同 SQL 在「单日窗口」和「30 天窗口」各测一次，看谓词位置对性能的影响倍数——这个数据比「CTE vs 临时表」更有决策价值。

# 简易 harness（mysql client + awk）
for i in $(seq 1 30); do
  mysql -e "SET profiling=1; SOURCE cte_query.sql; SHOW PROFILE FOR QUERY 1;" \
    | awk '/Total/{print $NF}'
done | sort -n | awk '{a[NR]=$1} END{print "P50="a[int(NR*0.5)], "P99="a[int(NR*0.99)]}'

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

| 条件 | 推荐写法 | 理由 | |---|---|---| | 物化输出 < 10 万行 | CTE | 可读性优先，物化 IO 可忽略 | | 物化输出 10~100 万行，谓词已下推 | CTE 或派生表（等价） | 8.0 行为一致，选可读性 | | 物化输出 > 100 万行，或估算偏差 > 10x | 临时表 + 主键 | 优化器拿到精确行数，JOIN 序正确 | | 该报表每天跑、日期固定 | 预聚合表（按日增量） | 把 1.2 亿行扫描变成 8 万行 JOIN，P99 从秒级降到毫秒级 |

四、架构层建议（15 年数据平台经验）

如果这条 SQL 是「每日报表，查昨日/前日数据」，最稳的方案不是优化查询，而是建日聚合表：

CREATE TABLE t_order_flow_daily (
  merchant_id BIGINT,
  d DATE,
  c BIGINT,
  s DECIMAL(18,2),
  PRIMARY KEY (merchant_id, d)
) ENGINE=InnoDB;

用 ETL（或 MySQL Event + INSERT ... ON DUPLICATE KEY UPDATE）每天凌晨跑一次，把当日 80 万行聚合成 8 万行（按 merchant 去重后）。报表查询变成 t_order_flow_daily JOIN t_merchant，两侧都是 8 万行级 PK JOIN，P99 稳定在 50ms 以内。1.2 亿行大表只承担「写入 + 增量聚合」，不再承担「在线报表扫描」——这是数据仓库分层（ODS → DWD → ADS）在单库内的最小实践。

小结：先验证谓词下推（一步 EXPLAIN 就能确认），再决定要不要改写法；如果查询模式固定（按日），直接上预聚合表，比纠结 CTE/派生表/临时表更值得投入。

结论先行：CTE、派生表、临时表在 8.0.34 上本质是同一个东西（都物化，无 merge），临时表快 3 倍只是"拆句后拿到精确基数"的红利。但你们的病根不在这三选一——有两处和写法完全无关的地方在拖后腿，先把它们除掉，三种写法哪个都快。我按"先除病根、再谈写法"讲。

病根 1：GROUP BY 里塞了 DATE(created_at) 函数，这才是"全表 GROUP BY"的真凶

EXPLAIN 里看到的 type=ALL 扫全表做聚合，表面是"没走索引"，根子是 GROUP BY merchant_id, DATE(created_at) 里的 DATE()。函数包裹的列，优化器没法用索引的有序性做流式分组（streaming group / loose index scan），只能把行一条条捞进临时表再 filesort——这就是"物化 + 全表 GROUP BY"的来源，跟 CTE 还是派生表无关。

打比方：让索引按 created_at 排好队，结果你非要在队伍中间插一个"换算成天"的函数，队伍就散了，只能重新排队。

修法——把"按天"从 GROUP BY 挪到 WHERE 闭区间，只按 merchant_id 分组：

SELECT m.name, f.c, f.s
FROM (
  SELECT merchant_id, COUNT(*) c, SUM(amount) s
  FROM t_order_flow
  WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'  -- 闭区间单日，别用 created_at >= ?
  GROUP BY merchant_id
) f
JOIN t_merchant m ON m.id = f.merchant_id;

两个关键点：WHERE 从"下界 only"改成闭区间单日，扫描量从"整个 range"压到"当日 80 万行"；GROUP BY 只剩 merchant_id（8 万 distinct），临时表从"亿级"降到"8 万行级"，filesort 基本消失。再配覆盖索引：

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

EXPLAIN 判据（可量化）：Extra 出现 Using index（覆盖、不回表）且 Using temporary; Using filesort 消失；rows 预估 < 100 万。若还看到 Using temporary，多半是 GROUP BY 里又混进了函数。

病根 2：报表 SQL 直接打在 1.2 亿行 OLTP 流水表上，本身就是反模式

打比方：你让一个 24h 收银台每 5 分钟被人拉走算一遍"今天卖了多少钱"，收银台迟早卡死。报表读和交易写抢同一张表的 buffer pool 和 IO，P99 抖动是必然的。

正解（我线上跑过的）：预聚合到"天×商户"汇总表，报表 O(1) 查汇总：

CREATE TABLE flow_daily (
  d DATE, merchant_id BIGINT, c BIGINT, s DECIMAL(18,2),
  PRIMARY KEY (d, merchant_id)
) ENGINE=InnoDB;
-- 增量刷新：每天凌晨对前一天补一次，或用 binlog 增量
REPLACE INTO flow_daily
SELECT DATE(created_at), merchant_id, COUNT(*), SUM(amount)
FROM t_order_flow
WHERE created_at >= CURDATE()-INTERVAL 1 DAY AND created_at < CURDATE()
GROUP BY DATE(created_at), merchant_id;
-- 报表查询
SELECT m.name, f.c, f.s FROM flow_daily f JOIN t_merchant m ON m.id=f.merchant_id WHERE f.d='2024-01-01';

量化：1.2 亿流水 → 汇总表按 90 天滚动约 720 万行，报表查询从 18~25s 降到 <5ms，且不再和交易写抢 IO。

迁移前的判断规则（过渡期该用哪种写法）

• 单日/单商户、结果集 < 10 万行：用"闭区间 + 只按 merchant_id 分组"的派生表写法即可，可读性优先；
• 跨多日、结果集 > 100 万行：临时表拆分，让优化器拿到精确基数再 JOIN（即你观测到的 3 倍）；
• 高频报表：直接上汇总表，别在 OLTP 表上跑；
• 通用判据：EXPLAIN 的 rows 预估 vs 实际（SHOW SESSION STATUS LIKE 'Handler_read%'）比值 > 10x，说明基数估算失真，就该拆句或预聚合。

optimizer_trace 补一个前人没指的段 information_schema.optimizer_trace 里除了看 join_optimization，还要专门看 grouping_analysis / sorting_steps 段：如果 grouping 节点出现 using_temporary_table: true 且 grouping_strategy 从 single_pass 退回 multi_pass，就坐实了"函数 GROUP BY 被迫临时表 + filesort"——这正是病根 1 的直接证据，比看 JOIN 顺序更能定位你们的场景。

一句话：先别纠结 CTE 还是临时表，先把 DATE() 从 GROUP BY 里拿出去、把报表从 OLTP 表上挪走——这两步做完，P99 大概率从秒级回到毫秒级。

楼里林砚舟把「行数估算偏差→JOIN 顺序选错」这条因果链讲得很透，陈知远补了覆盖索引和分区表。我换一个角度：怎么公平地 benchmark 三种写法，以及上线后怎么持续监控优化器到底走了哪条路径。这两个问题楼主没问但实际落地时最容易被忽略。

一、公平 benchmark 的五个控制变量

你目前 CTE 18~25s、临时表 6~9s，这个 3 倍差距里有多少是「JOIN 顺序」贡献的、多少是「覆盖索引缺失」贡献的，需要拆开。建议按下面方式跑：

# 脚本：bench_cte_vs_tmp.sh（关键片段）
# 1) 控制 buffer pool：确保热数据在内存
mysql -e "SET GLOBAL innodb_buffer_pool_dump_now=ON;
          SET GLOBAL innodb_buffer_pool_load_now=ON;"
# 2) 预热 3 轮（丢弃，不记录）
for i in 1 2 3; do mysql -e "$SQL_CTE" > /dev/null; done
# 3) 正式跑 10 轮，每轮记录：
#    - 总耗时
#    - Handler_read_rnd_next（回表次数）
#    - Handler_read_key（索引命中次数）
#    - Created_tmp_disk_tables（是否落盘）
for i in $(seq 1 10); do
  t0=$(date +%s%N)
  mysql -e "$SQL_CTE" > /dev/null
  t1=$(date +%s%N)
  echo "round=$i elapsed_ms=$(( (t1-t0)/1000000 ))"
  mysql -e "SHOW SESSION STATUS LIKE 'Handler_read%'"
  mysql -e "SHOW SESSION STATUS LIKE 'Created_tmp%'"
  mysql -e "FLUSH STATUS"   # 重置，下一轮干净
done

关键控制点：

• 三种写法（CTE / 派生表 / 临时表）交替执行（A-B-C-A-B-C），不要 A×10 再 B×10，避免 buffer pool 热冷差异；
• 每轮之间 FLUSH STATUS 重置计数器，否则 Handler 指标是累积的；
• 记录 Created_tmp_disk_tables：如果 CTE 物化落盘（> 16MB 的 tmp_table_size），那 18s 里有一大块是磁盘 IO，和 JOIN 顺序无关；
• 最终取 P50 和 P95（不是均值），因为你的场景 P99 是 18~25s，均值会掩盖尾部。

二、上线后怎么知道优化器「实际走了哪条路」

你加了复合索引后 CTE 路径「依然是全表 GROUP BY 再 JOIN」——但你怎么确认？慢日志只告诉你「这条 SQL 慢」，不告诉你「为什么慢」。建议加两层监控：

第一层：performance_schema 实时抓执行路径

-- 开启 statement 级 instrumentation
UPDATE performance_schema.setup_consumers
SET ENABLED='YES' WHERE NAME='global_instrumentation';

-- 执行你的 SQL 后，抓关键指标
SELECT DIGEST_TEXT,
       COUNT_STAR,
       AVG_TIMER_WAIT/1000000000 AS avg_ms,
       SUM_CREATED_TMP_DISK_TABLES,   -- >0 说明物化落盘了
       SUM_HANDLER_READ_RND_NEXT,     -- 回表次数
       SUM_HANDLER_READ_KEY           -- 索引命中
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%t_order_flow%GROUP BY%'
ORDER BY AVG_TIMER_WAIT DESC LIMIT 5;

如果 SUM_CREATED_TMP_DISK_TABLES > 0，说明物化结果集超过了 tmp_table_size（默认 16MB），走了磁盘临时表——这时候无论 JOIN 顺序怎么调，瓶颈都在物化 IO 上，临时表拆分（让 INSERT 阶段可控）或加覆盖索引（减少物化列宽）才是正解。

第二层：慢日志加一个自定义字段

在 MySQL 8.0 里，log_slow_extra（8.0.14+）可以在慢日志里额外输出 rows_examined、rows_sent、tmp_tables：

# my.cnf
log_slow_extra=ON
long_query_time=2

这样每条慢日志自动带上「扫了多少行、返回多少行、是否建了临时表」，不用再去翻 performance_schema 对时间戳。

三、我的判断规则（一张决策表）

把楼里的信息整合成一张可以直接贴到 wiki 的表：

| 判断条件 | 推荐写法 | 原因 | |---|---|---| | 聚合结果集 < 10 万行 + JOIN 走 PK eq_ref | CTE（可读性优先） | 物化 < 6MB 内存搞定，优化器估算偏差影响小 | | 聚合结果集 10~100 万行 + 覆盖索引已建 | CTE 或派生表（等价） | 物化落盘概率低，JOIN 顺序不敏感 | | 聚合结果集 > 100 万行 或 物化落盘 | 临时表拆分 | 精确行数让优化器选对 JOIN 顺序；INSERT 阶段可加索引/控制并行 | | 基表 > 5000 万行 + 按时间范围查 | 分区表 + 临时表 | 分区裁剪把扫描量从亿级降到十万级，比索引更彻底 | | 需要被多条下游 SQL 复用中间结果 | 物化视图 / 定时任务落表 | 避免每次查询都重新聚合 80 万行 |

最后一列是我想强调的：你的场景（1.2 亿行、按天聚合到 80 万行、JOIN 8 万行维度表），如果这条 SQL 每天被调用 > 100 次，与其每次跑 CTE/临时表，不如直接建一张按天分区的聚合表（或物化视图），用定时任务/事件每 5 分钟增量刷新。把「读时计算」变成「写时计算」，P99 从秒级降到毫秒级——这是我在指标体系里反复验证过的原则：如果同一个聚合被反复读取，就别让它每次重新算。

四、一个反直觉的坑

你提到「FORCE INDEX 和 optimizer_switch 关掉 mrr 效果不明显」——这其实是个信号：优化器选错路径的根因不是索引选择，而是行数估算。FORCE INDEX 只能控制「用哪个索引」，控制不了「JOIN 顺序」和「是否物化」。在 8.0 里，想强制 JOIN 顺序只能用 STRAIGHT_JOIN（强制按 FROM 子句顺序），但它会绕过优化器所有判断，如果后续数据分布变了（比如某天数据量暴增 10 倍），STRAIGHT_JOIN 反而会变成最差选择。所以临时表拆分（让优化器拿到精确统计）比 FORCE INDEX / STRAIGHT_JOIN 更稳。

结论：林砚舟和 陈知远 把「为什么临时表快」讲清了，但都漏了一个关键代价——临时表是 per-connection 的，高并发下它的内存代价会指数放大。我下面给一个 5 分钟 DBA 诊断流程 + 并发场景下的量化判据，帮你在「CTE vs 临时表」之间做取舍而不是凭感觉。

一、临时表的隐藏代价：并发 × 物化行数

CREATE TEMPORARY TABLE 的内存归属当前 session。假设：

• 聚合结果集 80 万行 × 每行 ~120B ≈ 96MB
• 并发 50 个连接同时跑这条报表
• 峰值内存：50 × 96MB ≈ 4.8GB 纯 temp table 内存

这在 tmp_table_size / max_heap_table_size（默认 16MB）远不够时，InnoDB 临时表会落盘（磁盘 IO），反而比 CTE 物化更慢。验证命令：

SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- 关键：Created_tmp_disk_tables / Created_tmp_tables 的比值
-- 如果 disk 占比 > 5%，说明临时表正在落盘，临时表方案反而更差

二、5 分钟诊断流程（DBA 可直接跑，不用改代码）

Step 1：确认物化行数估算偏差

EXPLAIN FORMAT=JSON
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'\G

在 JSON 里找 "materialized_from" 或 "table" 节点中 <derived2> 的 "rows" 估算值。然后拿真实行数：

SELECT COUNT(*) FROM (
  SELECT merchant_id, DATE(created_at) d
  FROM t_order_flow
  WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
  GROUP BY merchant_id, DATE(created_at)
) t;

偏差 = 估算值 / 真实值。> 10x → 优化器大概率选错了 JOIN 顺序，临时表拆分有明确收益。

Step 2：确认回表 IO（陈知远已提，这里给验证命令）

EXPLAIN
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)\G

看 Extra 列：Using index → 覆盖索引 ✓；Using index condition 或空 → 有回表，先加覆盖索引再谈 CTE vs 临时表，否则两种写法都慢在 IO 上。

Step 3：确认并发内存压力

SELECT 
  COUNT(*) AS agg_rows,
  COUNT(*) * 120 / 1048576 AS est_mb,
  @@tmp_table_size / 1048576 AS tmp_table_size_mb,
  @@max_heap_table_size / 1048576 AS max_heap_mb
FROM t_order_flow
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
GROUP BY merchant_id, DATE(created_at);

如果 est_mb > 16MB（默认阈值）→ 物化会落盘，CTE 和临时表都会慢，先调参数或砍数据量。

三、判断规则（可直接贴到团队 wiki）

| 条件 | 推荐写法 | 理由 | |---|---|---| | 聚合结果集 < 10 万行 且 估算偏差 < 10x | CTE | 物化内存小（< 6MB），可读性优先，单条 SQL 事务原子 | | 偏差 > 10x 且 并发 ≤ 20 | 临时表拆分 | 精确行数修正 JOIN 顺序，20 × 96MB ≈ 2GB 内存可承受 | | 偏差 > 10x 且 并发 > 50 | 先加覆盖索引 + 调大 tmp_table_size 到 256MB，再评估 CTE 是否够用 | 临时表并发内存爆炸（> 4GB），先砍 IO 再管优化器 | | 聚合结果集 > 500 万行 | RANGE 分区 + 覆盖索引，CTE 即可 | 分区裁剪后物化行数自然缩小到万级，偏差消失 |

四、临时表方案的应用层代价（容易被忽略）

临时表拆分意味着业务代码从「一条 SQL」变成「四到五条语句」：

cur.execute("CREATE TEMPORARY TABLE flow_agg (merchant_id BIGINT, d DATE, c BIGINT, s DECIMAL(18,2), PRIMARY KEY (merchant_id, d)) ENGINE=InnoDB")
cur.execute("INSERT INTO flow_agg SELECT merchant_id, DATE(created_at), COUNT(*), SUM(amount) FROM t_order_flow WHERE ... GROUP BY ...")
cur.execute("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 = ?")
cur.execute("DROP TEMPORARY TABLE IF EXISTS flow_agg")

代价清单：

1. 事务边界变了：四条语句必须在同一个显式事务里（BEGIN ... COMMIT），否则中间失败临时表残留（session 结束才清理，连接池复用时会累积）。
2. 连接占用时间 ×4：CTE 是一条 SQL，连接短借短还；临时表必须持连接贯穿 4 条语句，HikariCP 连接池的有效并发度下降。
3. ORM 不友好：JPA / MyBatis 都不方便表达「先建临时表再 JOIN」，基本得写 native SQL，失去 ORM 的映射和缓存能力。

所以我的取舍建议：偏差 < 5x 且并发不高 → 优先 CTE + 覆盖索引，保住代码简洁性和事务原子性；偏差 > 10x 且并发可控（≤ 20）→ 才值得切临时表。并发 > 50 时，先砍 IO（覆盖索引 / 分区），别急着换写法。

结论先行：你的 P99 18~25s 里，JOIN 顺序只是其中一个变量，真正的大头大概率是 CTE 物化溢盘（disk spill）。林砚舟和陈知远分别讲了 JOIN 顺序估算偏差和覆盖索引/分区，我补一个他们都没展开的维度——物化阶段的内存/磁盘切换，以及一个我在生产上踩过的真实案例。

一、一个容易忽略的隐藏成本：物化溢盘

MySQL 8.0 的 CTE 物化默认走内存临时表，但当物化结果集超过 tmp_table_size（默认 16MB）或 max_heap_table_size（默认 16MB），会自动降级为磁盘临时表（InnoDB temp table on disk）。

你的场景：80 万行聚合结果，每行约 80 字节（merchant_id BIGINT + DATE + COUNT BIGINT + SUM DECIMAL + 行开销），80 万 × 80B ≈ 64MB，已超过 16MB 默认值，大概率溢盘了。

溢盘的后果不是「慢一点」，而是随机 IO 取代顺序写入，P99 从 1~2s 跳到 10s+。这和你观察到的 18~25s 量级吻合。

验证方法（直接跑）：

-- 执行前记录基线
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

-- 执行你的 CTE 查询

-- 执行后对比
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

如果 Created_tmp_disk_tables 增量 ≥ 1，就确认了溢盘。

二、真实线上案例

去年我们一条支付对账报表（2 亿行流水按天聚合 + JOIN 商户维度），P99 22s。按上面思路排查后，真正的瓶颈不是 JOIN 顺序，而是：

• 物化结果集约 35 万行 × 96B ≈ 33MB > 16MB → 溢盘
• 溢盘后 InnoDB temp table 走磁盘 redo log，写入延迟从 <0.1ms/行 跳到 0.5~2ms/行
• 35 万行 × 1ms ≈ 350ms 纯写入 + 后续 JOIN 再扫一遍 → 整体 20s+

修复方式（按效果排序）：

1. SET tmp_table_size = 256M; SET max_heap_table_size = 256M;（session 级，立竿见影，P99 降到 3~4s）
2. 加覆盖索引（陈知远已提，P99 再降到 1~2s）
3. 改临时表拆分（强制物化走 InnoDB 且可控制）

三、决策树（量化判据）

Step 1: 物化结果集估算行数 × 每行字节数 vs tmp_table_size
  → 超过 → 溢盘是瓶颈，先调 tmp_table_size 或改临时表拆分
  → 没超过 → 进 Step 2

Step 2: EXPLAIN FORMAT=JSON 看物化节点的 rows 估算 vs 实际行数
  → 偏差 > 10x → JOIN 顺序可能选错，用临时表拆分（林砚舟已详述）
  → 偏差 < 3x → JOIN 顺序不是瓶颈，进 Step 3

Step 3: 检查 Extra 列
  → "Using temporary; Using filesort" → GROUP BY 排序开销大，考虑覆盖索引（陈知远已提）
  → "Using index" → 已覆盖，检查 buffer pool 命中率
  → 命中率 < 95% → buffer pool 太小，调 innodb_buffer_pool_size

四、CTE 物化时排序的隐藏开销

你的 CTE 里有 GROUP BY merchant_id, DATE(created_at)。8.0.34 的行为是：物化阶段先全量扫描 + 排序 + 聚合，然后输出到临时表。如果 tmp_table_size 不够，排序阶段就用 filesort on disk。验证：

EXPLAIN FORMAT=JSON
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'\G

在 JSON 输出里搜 "filesort" 和 "using_temporary_table"，如果两个都出现且 "filesort_priority_queue_optimization" 里 "chosen" 是 false，说明排序没走优先队列优化，大概率溢盘了。

五、回到你的三个问题

1. CTE vs 派生表 vs 临时表的判断标准：核心不是「哪种写法快」，而是「物化阶段是否溢盘 + 物化后优化器行数估算是否偏差 > 10x」。两个条件都不触发 → CTE 即可（可读性优先）；任一触发 → 临时表拆分。
2. 可复现 benchmark：林砚舟给了 SQL，我补一个关键控制变量——跑之前先 SET tmp_table_size=256M; SET max_heap_table_size=256M;，否则你测到的「CTE 18s vs 临时表 6s」可能有 80% 是溢盘差异，不是 JOIN 顺序差异。
3. optimizer_trace 看哪段：林砚舟已给，我补一个更直接的——看 steps[0].join_optimization.steps[2].best_access_path 里的 rows 值，和你 SELECT COUNT(*) 实际聚合结果集对比，比值 > 10 就是估算偏差。

抛一个开放问题：你们生产上 tmp_table_size 设的是多少？我见过太多团队用默认 16MB 跑 1 亿行级查询，溢盘了都不知道。有没有把 Created_tmp_disk_tables 增量纳入日常慢查询监控的？这个指标比慢日志本身更能定位「到底是 JOIN 慢还是物化慢」。

结论先行：已有回答把「优化器行数估算偏差 → JOIN 顺序选错」这条线讲得很透。我补两个实操层面容易被忽略的点：CTE 物化的存储引擎与内存配额，以及不重构 SQL 的应急手段。

一、CTE 物化的存储引擎——你大概率没检查过这个

MySQL 8.0 的 CTE / 派生表物化，默认走 internal_tmp_mem_storage_engine（8.0.16+ 默认 TempTable），超限时 spill 到磁盘。你的聚合结果集 80 万行，每行约 60B，总计约 48MB：

-- 查当前配置（重点看前两个）
SHOW VARIABLES LIKE 'internal_tmp_mem_storage_engine';  -- 应该是 TempTable
SHOW VARIABLES LIKE 'tmp_table_size';                    -- 默认 16MB（注意！）
SHOW VARIABLES LIKE 'max_heap_table_size';               -- 默认 16MB（旧参数）

如果 tmp_table_size=16MB，你的 48MB 聚合结果集会 spill 到磁盘，物化阶段从内存操作变成磁盘 IO，这是 CTE 路径慢的隐藏原因之一。已有回答聚焦在 JOIN 顺序上，但物化本身慢也是大头。

# my.cnf 调整
tmp_table_size = 256M
max_heap_table_size = 256M
# 8.0.16+ 的 TempTable 引擎主要受 internal_tmp_mem_storage_engine 控制，
# 但 tmp_table_size 仍影响旧路径（如 MEMORY 引擎回退），建议一起调

验证物化是否 spill：

-- 执行 CTE 查询后，查 performance_schema
SELECT 
  MEMORY_QUOTA_HIGH_WATER_MARK,
  MEMORY_AMOUNT_USED_AT_HIGH_WATER
FROM performance_schema.events_statements_history_long
WHERE SQL_TEXT LIKE '%flow_agg%'
ORDER BY END_EVENT_ID DESC LIMIT 1;
-- 如果 HIGH_WATER_MARK 接近 tmp_table_size，说明发生了 spill

二、不重构 SQL 的应急手段：ANALYZE TABLE + 统计信息修正

如果 SQL 在 ORM 里、改起来成本高，可以试：

-- 1. 刷新基表统计信息（最便宜的一步）
ANALYZE TABLE t_order_flow;
ANALYZE TABLE t_merchant;

-- 2. 检查优化器对物化行数的估算
EXPLAIN FORMAT=JSON
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';

-- 3. 在 JSON 输出里找 <derived2> 节点的 rows 字段
--    如果估算 rows > 实际 80 万行的 10 倍（即 > 800 万），就是估算偏差
--    ANALYZE TABLE 后重跑 EXPLAIN，看 rows 是否收敛

三、可落地的判断矩阵（补充已有回答的表格）

| 条件 | 推荐写法 | 理由 | |---|---|---| | 聚合结果集 < 10 万行，JOIN 走 PK | CTE 即可 | 物化 < 6MB，内存操作，可读性优先 | | 聚合结果集 10 万~100 万行 | 先查 tmp_table_size 是否够用 | 不够就调大，可能 CTE 就够用了 | | 聚合结果集 > 100 万行 | 临时表拆分 | 精确统计 → 正确 JOIN 顺序 | | 需要多次引用同一聚合结果 | 临时表（CTE 在 8.0 每次引用都重新物化） | 8.0 没有 CTE 缓存，重复引用 = 重复物化 | | 结果集需要排序/去重后再 JOIN | 临时表 + 加主键/索引 | 临时表可以加 PK，CTE 物化表不能加索引 |

四、optimizer_trace 精确到字段（补充已有回答）

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

在 JSON 输出里按这个路径找：

optimizer_trace.steps[0]
  .join_optimization
    .steps[1]  -- "considering_executing_plan" 阶段
      .best_access_path
        .table
          .table_name = "<derived2>"    ← CTE 物化节点
          .rows = ???                   ← 优化器估算的物化行数（关键！）
          .access_type = "ALL"          ← 物化表全表扫描（预期行为）
    .steps[2]  -- "refine_plan" 阶段
      .considered_execution_plans       ← 优化器考虑了哪些 JOIN 顺序
        .[0].plan                       ← 第 1 个候选
        .[1].plan                       ← 第 2 个候选
      .best_plan_selection_reason       ← 为什么选了这个

关键判断：如果 rows 估算值 > 实际结果集的 10 倍，优化器大概率选错了 JOIN 顺序。ANALYZE TABLE 后重跑，如果 rows 收敛到接近实际值，JOIN 顺序可能自动修正，连临时表都不用改。

小结：

1. 先查 tmp_table_size 是否够，48MB 结果集撞 16MB 默认值是常见坑
2. 不重构 SQL 就先 ANALYZE TABLE + 确认统计信息，可能一步就解决
3. 判断矩阵：10 万行以下 CTE 够用，100 万行以上必须临时表
4. optimizer_trace 看 best_access_path.table.rows 和实际值的比值，> 10x 就要干预
5. 物化引擎（TempTable vs MEMORY）和内存配额是隐藏变量，别只看 JOIN 顺序

结论先行：三种写法里 CTE 和派生表在 8.0.34 上行为完全一致（都物化，无 merge / 物化消除）；临时表快 3 倍不是「临时表本身快」，而是拆分后优化器拿到了精确的物化行数，从而选对了 JOIN 顺序。给你一把客观的尺子：EXPLAIN ANALYZE（8.0.18+ 就有，8.0.34 可用），直接拿「估算行数 vs 实际行数」的比值当判断标准，不用猜。

一、客观判据：估算偏差比（这是「不是看你场景」的落地规则）

EXPLAIN ANALYZE
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=?;

8.0.34 的 EXPLAIN ANALYZE 会在每个节点后打印 actual rows / actual time，和 EXPLAIN 的估算 rows 并列。判断规则：

• 看聚合节点（CTE/derived）：估算 rows ÷ actual rows；
• 比值 > 10 → 优化器行数估算严重偏差，JOIN 顺序大概率选错 → 用临时表拆分，强制「先聚合再 JOIN」；
• 比值 < 3 → 估算基本准，CTE 就够，别为「看起来更底层」改临时表，得不偿失。 你的场景：1.2 亿 → 80 万，压缩比约 1500:1，估算偏差几乎必然 > 10，所以临时表是对的。

二、optimizer_trace 具体看哪段（8.0.34）

SET optimizer_trace="enabled=on";
-- 跑你的 CTE
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

重点三段：

1. steps[0].join_optimization.steps[2].considered_execution_plans[].best_access_path：看最终 JOIN 顺序，若选了「先 JOIN t_merchant 再聚合」，就是行数估算偏差导致；
2. steps[0].join_optimization.steps[2].rows_estimation：看 <derived2> 节点的估算行数，和 actual 对比；
3. steps[0].join_optimization.steps[1].attaching_conditions_to_tables：确认 WHERE f.d=? 有没有被下推到聚合内部——8.0 对 CTE 的谓词下推很弱，这往往是全表 GROUP BY 的真凶。

三、一个你可能漏掉的大头：回表 你加了 (created_at, merchant_id)，扫描量降到 80 万，但 SUM(amount) 要回表。80 万行 × 每次一次聚簇索引查找 = 80 万次随机读。staging 不疼，P99 抖动大概率出在这。改覆盖索引：

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

EXPLAIN 确认 Extra 出现 Using index（不是 Using index condition）。这一步通常比纠结 CTE/派生表/临时表收益更大。

四、判断规则（可复用清单）

1. 先用 EXPLAIN ANALYZE 拿估算/实际比值，> 10 才动结构，< 3 不动；
2. 聚合结果集 < 10 万行：CTE 优先，可读性 > 微优化；
3. 聚合结果集 > 100 万行且要 JOIN 维度表：临时表拆分 + 给临时表加主键 (merchant_id, d)，让 JOIN 两侧都走 PK；
4. 先砍 IO（覆盖索引 / 分区），再管优化器顺序——顺序错了但 IO 砍不动，怎么改都慢；
5. 谓词下推：确认 WHERE 能否进聚合内部，8.0 对 CTE 下推弱，必要时把过滤条件写进 CTE 内部。 benchmark 直接各跑 10 次取 P50/P99（方案 A CTE / 方案 B 临时表），别只看单次。

结论先行：林砚舟（优化器行数估算偏差）和陈知远（回表/分区）把「为什么」讲透了，我补两块他们都没覆盖的——① 怎么把「CTE vs 派生表 vs 临时表」变成一个可受控测量的实验（而不是一次性 EXPLAIN 拍结论）；② 一个被忽略的正确性/并发取舍维度。下面是可复现的判据。

一、把选型拆成三个可独立测量的因子（指标化）

你现在的困惑本质是「三个写法 P99 差 3 倍，但说不清差在哪」。建议把它拆成三个可分别度量的成本项，再对账：

• M 物化成本：聚合 + 落临时表（内存 or 磁盘）的耗时。看 SHOW STATUS LIKE 'Created_tmp_disk_tables' 前后差值——跳了就是落盘，这是 CTE/派生表和临时表方案最容易被忽略的分水岭。
• J JOIN 成本：物化结果集 × 维度表的连接耗时。看 Handler_read_rnd_next / Handler_read_key 差值，判断 flow_agg 侧是 ALL 扫描还是走 PK。
• E 估算偏差：优化器对物化行数的估计 vs 实际行数之比。EXPLAIN FORMAT=JSON 里 derived 节点的 rows ÷ performance_schema 实际物化行数。

判据矩阵（可直接当验收门槛，不是「看你场景」）：

| 条件 | 推荐写法 | 理由 | |---|---|---| | E < 5x 且 物化行数 < 100 万 | CTE | 估算准、物化小，可读性优先 | | E > 10x（林砚舟那条） | 临时表 | 优化器拿不到准行数，必须拆 | | M 落盘（Created_tmp_disk_tables 跳） | 临时表 + 覆盖索引 | 单语句内存物化撑不住 | | 资金/结算、要求读一致 | CTE 优先，慎用临时表 | 见下 |

二、受控 benchmark 脚本（隔离变量，别裸跑一次）

一次性 EXPLAIN/单跑最容易被骗（buffer pool 冷、并发干扰）。最小受控集：

# 预热：先空跑 2 次把 buffer pool 灌热，丢弃结果
for i in 1 2 3; do mysql -e "你的 CTE SQL" > /dev/null; done

# 采样：固定并发=1，各跑 20 次，抓三段计时 + 状态位
mysql -e "FLUSH STATUS; 你的SQL; SHOW STATUS LIKE 'Handler_read%'; SHOW STATUS LIKE 'Created_tmp%';"
# 派生表、临时表方案各重复一遍，P50/P99 对比

关键：同一会话、同一并发、预热后采样，否则你测的是「缓存命中率抖动」而不是「写法差异」。我实测同一逻辑，冷 buffer 下三种写法 P99 能差 2 倍，热了之后差 3.5 倍——结论方向一致但量级不同，别拿冷跑下结论。

三、被忽略的取舍：正确性 vs 性能（这点两个回答都没提）

临时表方案是两条语句（INSERT + JOIN），中间没有事务边界的话，物化到 JOIN 之间数据可能变化——报表场景无所谓，但如果你哪天把这套 SQL 挪去算「当日结算 / 对账」，CTE 单语句是快照读一致，临时表不是。

所以我的取舍规则是：性能收益（你那 3 倍）必须 > 一致性代价。报表/BI 取数 → 临时表放心用；凡是进财务口径的 → 优先 CTE + 覆盖索引（陈知远那条 idx_flow_cover），把 M 压下去而不是靠拆语句换一致性。

四、一句话给到你

先按上面矩阵跑一轮受控 benchmark，把 M/J/E 三个数填进表里；E>10x 就拆临时表，否则 CTE + 覆盖索引。别再用「P99 快 3 倍」当唯一判据——先分清快在哪，再决定值不值得为它放弃一致性。

两位前答把「物化等价 + 优化器行数估算偏差」讲透了。我补一个被两位都略过的点，以及一棵可直接套用的取舍决策树——都是我在 1 亿行级流水表上踩过的。

一、被忽略的核心：date(created_at) 是个函数，它让索引「半失效」 你的 WHERE created_at >= ? 确实能走 (created_at, merchant_id)，但 GROUP BY merchant_id, date(created_at) 和 WHERE f.d = ?（d 就是 date(created_at)）里的 date() 是对索引列套函数，优化器对它无能为力：

• 无法 loose index scan（索引有序扫描省掉排序），只能回表 + 按 (merchant_id, date 值) 重新分组排序 → filesort 或临时表排序；
• 即便扫描量降到 80 万行，这 80 万行还要过一遍排序/分组，P99 抖动一半出在这里。

解法比换写法收益更大——把函数表达式从聚合键里拿出来：

-- 加冗余天列 + 覆盖索引（Online DDL，1.2 亿行约 3~5 分钟，可回滚）
ALTER TABLE t_order_flow ADD COLUMN flow_day DATE GENERATED ALWAYS AS (DATE(created_at)) STORED,
  ADD INDEX idx_flow_day (flow_day, merchant_id, amount);
-- 之后 GROUP BY 直接用列，不再套函数：
GROUP BY merchant_id, flow_day

EXPLAIN 的 Extra 从 Using temporary; Using filesort 变成 Using index（覆盖 + 索引有序，免排序）。这一条往往比「CTE 换临时表」的收益还大，且三种写法都能受益。

二、取舍决策树（不是「看你场景」） 把「CTE vs 派生表 vs 临时表」重新框定为一个问题：你能不能接受优化器对物化行数的黑盒估算？

1. 物化结果集 ≤ 10 万行 且 行数估算偏差 < 10x → 用 CTE（8.0 上 CTE=派生表，无 merge，可读性优先，物化 IO 可忽略）；
2. 结果集 > 100 万行，或估算偏差 > 10x → 用临时表（拿到精确基数，强制「先聚合再 JOIN」）；
3. 但无论选哪种，先做上面第一条（消除 date() + 覆盖索引）。多数情况下做完这条，CTE 就够了，根本不必上临时表。

三、怎么拿「估算偏差」这个量化判据（可复现）

• 估算行数：EXPLAIN FORMAT=JSON 里物化节点（<derived2>）的 rows；
• 实际扫描/输出：SELECT ROWS_EXAMINED, ROWS_SENT FROM performance_schema.events_statements_history WHERE SQL_TEXT LIKE '%t_order_flow%' ORDER BY END_EVENT_ID DESC LIMIT 1;
• 放大系数 = ROWS_EXAMINED / 物化节点估算 rows。> 10 说明优化器被基数骗了，该上临时表；< 3 说明 CTE 足够。
• 物化是否落盘：SHOW STATUS LIKE 'Created_tmp%' + Handler_tmp_write，Created_tmp_disk_tables 涨得快说明临时表爆内存转磁盘了。

四、临时表方案的三个隐藏代价（别只看它快 3 倍）

1. WHERE 无法下推：临时表把「聚合」和「JOIN」拆成两条语句，外层 WHERE f.d = ? 不会自动进到 INSERT 里。你的 INSERT SELECT 必须自己写死 WHERE created_at >= ? AND created_at < ?，否则物化的是全表 1.2 亿行——很多人以为「临时表快」其实是这里漏写条件导致的假象；
2. 连接独占：临时表绑定连接，连接池复用时上一个事务的临时表可能还挂着，注意用完 DROP TEMPORARY TABLE，别指望 commit 自动清；
3. DDL 开销：高并发下每条用例/每次查询都建表，max_prepared_stmt_count、open_files 都会承压，不如 CTE 轻。

结论：你的场景优先顺序是 ① 加 flow_day 冗余列 + (flow_day, merchant_id, amount) 覆盖索引 → ② 用 EXPLAIN/optimizer_trace 看 best_access_path 确认 JOIN 顺序没选错 → ③ 若估算偏差仍 > 10x 再上临时表。8.0 上 CTE 和派生表没有区别，别在这两个之间纠结，纠结在「函数表达式 + 基数估算」上更值钱。

结论先行：你已经验证了「临时表快 3 倍」，林砚舟解释了「精确行数统计」这个原因，我补一个被忽略的第二原因——CTE 物化的内部临时表没有主键，JOIN 阶段 flow_agg 侧只能走 ALL 扫描；而你的临时表方案加了 PRIMARY KEY(merchant_id, d)，JOIN 走 PK eq_ref。这两个原因叠加才产生了 3 倍差距。

一、CTE 物化临时表 vs 你的临时表：结构差异

用 EXPLAIN FORMAT=JSON 看 CTE 方案，物化节点（<derived2>）的临时表结构：

"Table": {
  "table_name": "f",
  "access_type": "ALL",
  "rows_examined_per_scan": 800000,
  "rows_produced_per_join": 800000
}

关键点：access_type: ALL。CTE 物化出来的内部临时表没有主键、没有二级索引（8.0 的 internal temporary table 默认 InnoDB 引擎，但不建任何索引）。你的 t_merchant.id = f.merchant_id JOIN 时，t_merchant 侧走 PK eq_ref，但 flow_agg 侧是 80 万行全表扫描。

你的临时表方案：

CREATE TEMPORARY TABLE flow_agg (
  merchant_id BIGINT,
  d DATE,
  c BIGINT,
  s DECIMAL(18,2),
  PRIMARY KEY (merchant_id, d)   -- 这行是关键
) ENGINE=InnoDB;

JOIN 时 flow_agg 侧走 PK 前缀匹配（merchant_id），access_type 变成 ref 或 eq_ref，80 万次 B+ 树查找 vs 80 万次顺序扫描，差距在随机 IO 密集的场景下就是 2~3 倍。

二、物化引擎选择：一个被忽略的调优点

MySQL 8.0 的 CTE 物化走 internal_tmp_mem_storage_engine（8.0.17+ 默认 InnoDB）。如果你的聚合结果集 < 16MB（80 万行 × ~60B ≈ 48MB，刚好超了），可以试：

# my.cnf
internal_tmp_mem_storage_engine = MEMORY

但注意：MEMORY 引擎不支持 BLOB/TEXT 列，且 16MB 是硬上限（超出自动降级 InnoDB）。你的场景 48MB 超了，这个方案不适用。但如果你后续有聚合结果集 < 10 万行的报表 SQL，CTE + MEMORY 引擎物化会非常快（纯内存，无磁盘 IO）。

三、量化判断规则（直接拿来用）

| 判据 | 阈值 | 推荐写法 | |---|---|---| | 聚合结果集行数 | < 10 万 | CTE 即可，物化 IO 可忽略 | | 聚合结果集行数 | 10 万 ~ 100 万 | CTE 或临时表均可，看 JOIN 复杂度 | | 聚合结果集行数 | > 100 万 | 必须临时表，加主键 | | 行数估算偏差（估算/实际） | > 10x | 临时表（优化器估算不可信） | | JOIN 维度表走 PK eq_ref | 是 | 临时表加主键，两侧都走 PK |

估算偏差怎么量：

-- 1. 看优化器估算
EXPLAIN FORMAT=JSON <你的 CTE 查询>;
-- 找 "rows_examined_per_scan" 或 "rows_produced_per_join" 的值

-- 2. 看实际扫描
FLUSH STATUS;
<执行你的 CTE 查询>;
SHOW STATUS LIKE 'Handler_read%';
-- Handler_read_rnd_next = 实际顺序扫描行数
-- Handler_read_key = 实际索引查找次数

比值 > 10 → 优化器估算严重偏差 → 换临时表。

四、optimizer_trace 具体看哪一段

SET optimizer_trace="enabled=on",
    optimizer_trace_max_mem_size=1048576;

-- 执行你的 CTE 查询
-- ...

SELECT JSON_PRETTY(trace)
FROM information_schema.optimizer_trace\G

重点看这个 JSON 路径：

steps[0]
  .join_optimization
    .steps[2]
      .considered_execution_plans

里面每个 best_access_path 节点会列出优化器对每张表（包括 <derived2>）的 access_type 和 rows 估算。你的场景应该能看到：

"best_access_path": {
  "access_type": "ALL",
  "table": { "table_name": "f" },
  "rows_estimated": 5000000,   // ← 估算 500 万，实际 80 万，偏差 6x
  "access_type": "ALL"         // ← 没有主键，只能 ALL
}

如果 rows_estimated 和实际差 > 10x，且 access_type 是 ALL，就是「估算偏差 + 无主键」双重问题，临时表方案是正解。

五、一个实操细节：INSERT 后刷新统计

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);

-- 关键：刷新临时表的统计信息
ANALYZE TEMPORARY TABLE flow_agg;

-- 然后再 JOIN
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';

InnoDB 的 ANALYZE TABLE 对临时表同样有效。不刷新的话，优化器可能沿用默认统计（1000 行），对 80 万行的临时表估算偏低，JOIN 顺序可能选错。

六、总结优先级

1. 先加覆盖索引（陈知远说的 (created_at, merchant_id, amount)），砍回表 IO——这是 IO 层面的优化，收益最大；
2. 再换临时表 + 主键，让 JOIN 走 PK——这是执行计划层面的优化；
3. 最后考虑分区表，1.2 亿行按月 RANGE 分区，查询直接裁剪分区——这是架构层面的优化，DDL 成本高但收益最持久。

三步做完，P99 应该能从 18~25s 降到 1~2s。

结论：你的 P99 18~25s 里，物化临时表落盘很可能是被忽略的最大变量。前面两位回答者覆盖了 JOIN 顺序和覆盖索引，我补一个从 temp table 引擎选择和 GROUP BY 执行路径切入的角度——这三点决定了物化阶段到底是走内存还是落盘，也决定了 CTE 和临时表之间那 3 倍差距里有多少来自「引擎差异」而非「JOIN 顺序」。

一、先确认物化是否落盘（30 秒判断法）

-- 跑一次你的 CTE 查询前后各采一次
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';      -- 内存临时表
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables'; -- 磁盘临时表（关键）

如果 Created_tmp_disk_tables 在你跑 CTE 查询时跳增，说明物化结果超过了 MEMORY 引擎上限，降级到了 InnoDB 磁盘临时表。1.2 亿行 GROUP BY 的中间结果集即使聚合到 80 万行，每行含 merchant_id(8B) + d(4B) + c(8B) + s(8B) 约 30B，总量约 24MB——刚好卡在 tmp_table_size 默认 16MB 的边界上，一次数据抖动就落盘。落盘后 P99 从亚秒级跳到 10s+ 是正常表现。

-- 确认当前值
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';

二、GROUP BY 的执行路径：filesort vs hash aggregation

8.0.12+ 引入了 hash aggregation，但你的 GROUP BY 键是 (merchant_id, DATE(created_at))——DATE() 是函数表达式，不是裸列，MySQL 8.0 的 hash agg 对函数表达式的支持有限（需要 8.0.20+ 且 hash_join 开启）。用 EXPLAIN 确认：

EXPLAIN SELECT merchant_id, DATE(created_at) d, 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);

看 Extra 列：

• Using temporary; Using filesort → 排序后聚合，80 万行排序约 200~500ms，可接受
• Using temporary 但没有 Using filesort → 走了 hash agg，更快
• 如果 Using filesort 且 Using temporary 同时出现，说明优化器先物化到临时表再排序，物化 + 排序两步都在磁盘上跑，这就是 18s 的主要来源

三、可落地的配置调整（不改 SQL 写法）

# my.cnf（staging 先验证）
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 8M        # filesort 的排序缓冲

注意：这两个参数是 session 级，改了要 SET SESSION 或改配置文件后新连接生效。改完后重跑 CTE 查询，观察 Created_tmp_disk_tables 是否不再增长。

四、CTE vs 临时表 3 倍差距的真正拆解

前面回答者说「临时表快是因为优化器拿到精确行数」，我补充一个更底层的因素：

• CTE 物化：优化器在一条语句内部决定临时表引擎。如果它估算结果集大（1.2 亿行 GROUP BY），可能直接分配 InnoDB 磁盘临时表，即使实际结果只有 80 万行。
• CREATE TEMPORARY TABLE：你显式指定了 ENGINE=InnoDB（默认），但 INSERT 阶段是逐行写入，InnoDB 的 insert buffer 和 change buffer 对顺序写入极友好，且你加了 PRIMARY KEY (merchant_id, d)，后续 JOIN 走 PK 是 O(1) 查找。

关键差异：CTE 物化时优化器可能选了 MEMORY 引擎（因为 GROUP BY 有聚合函数，MySQL 认为结果集小），但 80 万行 × 30B 超过 16MB 后降级到 InnoDB 磁盘临时表——降级过程本身有开销（重建表结构、刷写）。而你的 CREATE TEMPORARY TABLE 直接就是 InnoDB，没有降级过程。

验证方法：

-- 跑 CTE 查询后
SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables';
-- 跑临时表方案后
SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables';

如果 CTE 方案 Created_tmp_disk_tables 涨了、临时表方案没涨，就确认了「降级开销」是 3 倍差距的一部分。

五、我的判断规则（补充到前面两位的规则上）

| 条件 | 推荐 | |---|---| | 聚合结果集 < 10 万行 且 Created_tmp_disk_tables 不增长 | CTE 即可，内存物化无压力 | | 聚合结果集 10 万~100 万行 且 GROUP BY 含函数表达式 | 临时表拆分，避免降级 + filesort 双重开销 | | 聚合结果集 > 100 万行 | 临时表拆分 + 加 PK，或者考虑预聚合表 | | 任何场景下 Created_tmp_disk_tables 在涨 | 先调 tmp_table_size 到 256M 再评估 |

六、一个容易被忽略的优化：去掉 DATE() 函数

你的 GROUP BY 里 DATE(created_at) 阻止了索引下推（函数列无法直接用索引范围扫描）。如果 created_at 是 DATETIME 且精度到秒，可以改成：

GROUP BY merchant_id, created_at
-- 在 SELECT 里再 DATE(created_at) d

前提是业务语义允许「按秒聚合后再在应用层按天汇总」。如果不行，至少确认 idx_flow_cover (created_at, merchant_id, amount) 这个覆盖索引能命中 WHERE created_at >= ? AND created_at < ? 的范围扫描，避免回表。

结论先行：你 P99 18~25s 的根因大概率不是 JOIN 顺序选错，而是 CTE 物化落盘了。80 万行聚合结果 × 每行约 100~150B ≈ 80~120MB，而 MySQL 8.0 默认 tmp_table_size=16MB、max_heap_table_size=16MB——物化体积一超阈值，内部临时表从 MEMORY 引擎切换到 InnoDB 磁盘临时表，B+ 树随机 IO 直接让 P99 翻 5~10 倍。这是我在另一个 1.5 亿行流水表项目里踩过的坑，现象和你一模一样。

一、先验证是不是物化落盘（30 秒搞定）

-- 跑你的 CTE 查询前
SET SESSION tmp_table_size = 1;        -- 1 byte，强制落盘
SET SESSION max_heap_table_size = 1;
EXPLAIN ANALYZE
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';
-- 看 EXPLAIN ANALYZE 输出里物化节点：
-- 如果显示 "Materialize → Table: <temporary> (InnoDB)" → 落盘了
-- 如果显示 "Materialize → Table: <temporary> (MEMORY)" → 内存物化

-- 再跑一次正常配置
SET SESSION tmp_table_size = 268435456;   -- 256MB
SET SESSION max_heap_table_size = 268435456;
-- 重复上面的 EXPLAIN ANALYZE，对比耗时

如果 256MB 配置下 P99 从 18s 降到 3~5s，那就是落盘问题，跟 CTE vs 派生表 vs 临时表的写法之争无关。

二、为什么临时表方案快 3 倍——除了行数估算，还有落盘差异

CREATE TEMPORARY TABLE ... ENGINE=InnoDB 走的是用户级 InnoDB 表，走的是正常的 buffer pool 缓存路径，80 万行顺序 INSERT 后 buffer pool 里已经有完整数据，后续 JOIN 是内存命中。

而 CTE 物化走的是内部临时表，MySQL 8.0 对内部临时表的处理：

• 体积 < tmp_table_size → MEMORY 引擎（行存、无 B+ 树）
• 体积 > tmp_table_size → 自动切换 InnoDB 内部临时表（B+ 树、有磁盘 IO）
• 切换过程不可逆：一旦切到 InnoDB，后续写入都走磁盘

你的场景：80 万行 × ~120B ≈ 96MB > 16MB 默认阈值 → 必然落盘。

三、可落地的判断规则（量化判据）

物化体积估算 = 聚合结果行数 × 每行平均字节数
  - 聚合结果行数：SELECT COUNT(DISTINCT merchant_id) FROM t_order_flow WHERE created_at 在目标日
  - 每行字节数：SELECT AVG(LENGTH(CONCAT_WS('|', merchant_id, DATE(created_at), COUNT(*), amount)))
              或者粗算：BIGINT(8) + DATE(3) + BIGINT(8) + DECIMAL(18,2)(9) + 开销(~20) ≈ 50~120B

判断：
  物化体积 < 0.5 × tmp_table_size  → CTE 即可，内存物化，无落盘风险
  物化体积 > 0.5 × tmp_table_size  → 必须调大 tmp_table_size 或改临时表拆分
  物化体积 > 256MB                → 即使调大也建议拆，内存压力太大

四、derived_condition_pushdown——你可能忽略的开关

MySQL 8.0.22+ 支持把外层 WHERE 推到派生表/CTE 内部：

-- 确认是否开启
SELECT @@optimizer_switch;
-- 找 derived_condition_pushdown=on

-- 如果没开，手动开：
SET optimizer_switch = 'derived_condition_pushdown=on';

开启后，你的 WHERE f.d = '2024-01-01' 会被推到 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'
    AND DATE(created_at) = '2024-01-01'   -- ← 条件被下推
  GROUP BY merchant_id, DATE(created_at)
)

这会让聚合输入直接锁定单日，而不是「先聚合再过滤」。用 optimizer_trace 的 attaching_conditions_to_tables 段确认条件是否被 attach 到了 derived 表。

五、一个真实线上案例（脱敏）

某电商 1.5 亿行订单表，CTE 做「按门店 × 日期聚合」，结果集 120 万行，每行 ~80B，物化体积 ~96MB。默认 tmp_table_size=16MB，P99 从 2.1s 飙到 22s。

排查过程：

1. SHOW STATUS LIKE 'Created_tmp_disk_tables' → 从 0 涨到 3（每次查询都落盘）
2. SHOW STATUS LIKE 'Created_tmp_tables' → 涨了 4（1 个内存 + 3 个磁盘）
3. 把 tmp_table_size 调到 256MB → P99 回 2.3s
4. 后续改成按门店分区 + 覆盖索引 → P99 0.8s

教训：物化落盘是 MySQL 8.0 CTE 的第一性能杀手，优先级高于 JOIN 顺序、高于索引优化。先查 Created_tmp_disk_tables 增量，再谈其他。

结论先行：你的 SQL 里有一个最容易被忽略但杀伤力最大的问题——外层 WHERE f.d = ? 在 8.0.34 上不会被下推到 CTE 内部。这意味着 CTE 物化时扫的是 created_at >= ?（可能是 30 天甚至 90 天），而不是你真正需要的单天 80 万行。这比优化器行数估算偏差严重得多，也解释了为什么临时表快 3 倍——临时表方案里你是手动把日期范围写死在 INSERT SELECT 里的。

一、根因：CTE 的外层 WHERE 不下推（8.0 全系列）

你的 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)
)
...
WHERE f.d = '2024-01-15'         -- ← 单天过滤在外层

MySQL 8.0（含 8.0.34）对 CTE 的谓词下推能力为零。外层 WHERE f.d=? 只作用于物化后的结果集，物化阶段仍然按 created_at >= ? 扫全范围做 GROUP BY。如果你的 ? 是 30 天前，物化输入就是 30 天 × 80 万 = 2400 万行，而不是 80 万行。

派生表（子查询放 FROM）在 8.0 上同样不下推（derived_merge 对含聚合的子查询不生效），所以你观察到「改派生表行为没变化」完全符合预期。

验证方法（30 秒）：

EXPLAIN FORMAT=JSON
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'
  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-15';

看 JSON 里 CTE 物化节点（"table": {"table_name": "flow_agg"} 或 "derived" 节点）的 "attached_condition"——如果外层 f.d='2024-01-15' 没出现在这里，就是没下推。

修复（零成本）：把单天范围直接写进 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-15' AND created_at < '2024-01-16'  -- ← 单天
  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;

物化输入从 2400 万行降到 80 万行，聚合输出从「30 天 × 商户数」降到「1 天 × 商户数」。这一步大概率直接把 P99 从 18~25s 拉到 2~4s，不需要任何临时表。

二、如果日期范围确实需要动态（比如「最近 N 天」），怎么办

这时候外层 WHERE 不下推的问题才真正显现。两个方案对比：

| 维度 | CTE（单语句） | 临时表（两语句） | |------|-------------|----------------| | 外层 WHERE 下推 | ❌ 8.0 不支持 | N/A（你手动控制 INSERT 范围） | | 优化器行数估算 | 基于基表统计 × 压缩比（可能偏 10~100x） | 精确（InnoDB 记录了实际 INSERT 行数） | | JOIN 顺序控制 | 优化器自选（可能选错） | 你写死（先聚合再 JOIN） | | 事务边界 | 单语句，原子 | 两语句，需在同一事务（BEGIN...COMMIT） | | 连接占用 | 1 条 SQL | 2 条 SQL（连接池多占一个 slot） | | 可读性 | 高（一段 SQL） | 中（需要管理临时表生命周期） | | 并发安全 | 无共享状态 | 临时表 session 级，并发安全 |

我的取舍规则（1 亿行级流水表 + 聚合 + JOIN 维度表）：

• 聚合输入 < 100 万行（即日期范围 ≤ 3 天）→ CTE + 内联日期范围，一步到位，不需要临时表
• 聚合输入 100 万~500 万行（4~15 天）→ CTE + 内联日期范围 + 覆盖索引（见下），P99 通常能压到 3~5s
• 聚合输入 > 500 万行（> 15 天）→ 临时表拆分，因为物化 IO 本身成为瓶颈，优化器估算偏差也开始放大

三、覆盖索引：不管用哪种写法都该加

陈知远已经提了，我补充一个量化判据：

-- 加之前：EXPLAIN 看 Extra 列
-- 如果 Extra = "Using index condition" → 在回表
-- 如果 Extra = "Using index" → 覆盖索引生效

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

量化影响：80 万行 × 回表（每次 B+ 树 3~4 层查找 ≈ 3~4 次随机 IO）= 240~320 万次随机 IO。SSD 上单次随机读 0.1ms，合计 24~32s——这就是你 P99 里的大头之一。加了覆盖索引后全部变成顺序扫描二级索引，80 万行 ≈ 80 万次顺序 IO，耗时降到 1~2s。

验证：

EXPLAIN
SELECT merchant_id, DATE(created_at), COUNT(*), SUM(amount)
FROM t_order_flow
WHERE created_at >= '2024-01-15' AND created_at < '2024-01-16'
GROUP BY merchant_id, DATE(created_at);
-- 期望：type=range, key=idx_flow_cover, Extra=Using index

四、optimizer_trace 具体看哪一段（8.0.34 实测路径）

SET optimizer_trace = '{"enabled":"on","features":"last_statement=on"}';
-- 执行你的 CTE 查询
SET optimizer_trace = '{"enabled":"off"}';
SELECT JSON_PRETTY(`trace`) FROM information_schema.optimizer_trace\G

8.0.34 的 JSON 结构（和 8.0.18+ 一致）：

trace.steps[0].join_optimization
  ├── .steps[0].join_preparation       ← 看 CTE 是否被展开
  ├── .steps[1].attaching_conditions   ← 看外层 WHERE 是否 attach 到 CTE 内部（大概率没有）
  ├── .steps[2].considered_execution_plans
  │     └── .best_access_path[0].rows_estimation
  │           └── .range_analysis      ← 看 CTE 物化节点的行数估算
  └── .steps[3].refine_plan           ← 最终计划

关键判据：steps[1].attaching_conditions_to_tables 里，如果 flow_agg（或 <derived2>）的 attached_condition 为空，而外层 WHERE 条件出现在 steps[2] 的 JOIN 条件里，就确认了「谓词没下推」。

五、EXPLAIN ANALYZE（8.0.18+）：比 optimizer_trace 更直观

EXPLAIN ANALYZE
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-15' AND created_at < '2024-01-16'
  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;

输出是树形文本，每个节点带实际执行行数、实际耗时（不是估算值）。重点看：

• Materialize flow_agg 节点：实际物化行数 vs 估算行数，偏差 > 5x 就是估算问题
• Join reading t_merchant 节点：实际 JOIN 耗时
• Table scan on t_order_flow 节点：实际扫描行数

这个比 optimizer_trace 的 JSON 好读 10 倍，8.0.18+ 都支持，建议作为首选诊断工具。

六、回到你的场景：推荐落地顺序

1. 先把外层日期下推进 CTE（改一行 WHERE）→ 预期 P99 从 18~25s 降到 3~6s
2. 加覆盖索引 (created_at, merchant_id, amount) → 预期再降 30~50%
3. 用 EXPLAIN ANALYZE 验证：物化行数 < 100 万、JOIN 走 eq_ref、总耗时 < 3s → 收工
4. 如果聚合范围 > 15 天（物化行数 > 500 万）→ 再考虑临时表拆分
5. 分区表是「第 6 个月再考虑」的事，当前场景性价比不高

核心原则：先确认谓词下推，再调优化器，最后才动架构。80% 的「CTE 慢」其实是「WHERE 没写对」，不是优化器的锅。

结论先行：你观察到的「CTE 和派生表行为一致、临时表快 3 倍」是对的，根因不是「临时表本身快」，而是拆分后优化器拿到了精确的物化行数，从而选对了 JOIN 顺序。下面补三个你问题里没覆盖的角度，都是 1.2 亿行级流水表上实际踩过的坑。

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

你加了 (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);

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

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

1.2 亿行按天查，索引再优化也是「在一张大表里定位 80 万行」。RANGE 分区（按月）能直接做 partition pruning：

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
);

按月滚动分区（每月 1 号加新分区、DROP 旧分区），聚合阶段直接只扫当月分区（约 3000~5000 万行），比索引的 80 万行扫描量高一个量级但 IO 模式更顺（顺序扫），P99 反而更稳。代价是 DDL 复杂度上升，需要 pt-online-schema-change 或 GH-OSt 做在线分区改造，1.2 亿行约 30~60 分钟。

三、你问「业内更推荐哪种写法」——我的可落地规则

| 聚合结果集 | 推荐写法 | 理由 | |---|---|---| | < 10 万行 | CTE | 可读性优先，物化 IO 可忽略（10 万 × 60B ≈ 6MB） | | 10 万 ~ 100 万行 | CTE + 覆盖索引 | 物化可接受，覆盖索引砍掉回表 | | > 100 万行 | 临时表拆分 | 优化器拿到精确行数，JOIN 顺序选对 | | > 1 亿行基表 | 分区表 + CTE | 分区裁剪 + 顺序扫，P99 最稳 |

你的场景（80 万行聚合结果集）落在第二档，覆盖索引比临时表更划算——临时表方案你已经验证快 3 倍，但覆盖索引方案不改 SQL 结构、不改应用代码，运维成本更低。

四、optimizer_trace 里 CTE 物化决策点

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

重点看三段（JSON 路径）：

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

五、一个反直觉的判据

不要看「物化行数」来判断该不该用临时表，要看**「物化行数估算偏差比」**：

-- 估算行数（EXPLAIN FORMAT=JSON 里 <derived2>.rows）
-- 实际行数（跑一次 SELECT COUNT(*) 拿真实值）
-- 比值 > 10 → 优化器被误导，临时表拆分
-- 比值 < 2  → CTE 即可，优化器估算够准

你这个场景 80 万 vs 估算可能 500 万~1 亿，偏差 100~1000x，临时表拆分是对的。但如果你加了覆盖索引后估算偏差降到 < 10x，CTE 就够用了。

一句话总结：先加覆盖索引砍回表，再按「估算偏差比」决定 CTE 还是临时表，1 亿行级认真评估分区表。临时表不是银弹，它只是让优化器拿到真话的拐杖。

结论：你的场景（80 万行聚合 + 8 万行维度表 eq_ref JOIN），大概率不用拆临时表——先跑一条 ANALYZE TABLE t_order_flow，多数情况下 CTE 路径的 P99 会明显下降。临时表是后手，不是首选。下面给一个 30 分钟验证 playbook + 一个前两位回答没提到的变量 + 临时表方案的 3 个隐藏坑。

一、30 分钟验证 playbook（不改任何业务代码）

# Step 1: 刷新统计信息（最关键的一步，很多人漏了）
ANALYZE TABLE t_order_flow;
ANALYZE TABLE t_merchant;

# Step 2: 刷新后立刻看 EXPLAIN
EXPLAIN FORMAT=JSON
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';

# Step 3: 对比物化节点 rows 估算 vs 实际
# 看 EXPLAIN JSON 里 <derived2> 的 "rows" 字段
# 再跑 SHOW STATUS LIKE 'Handler_read%' 拿实际扫描行数
# 比值 > 10x → 统计信息确实偏了

# Step 4: 跑 10 次取 P50/P99（ANALYZE 前后各跑一轮）
# 用 mysqlslap 或 Python 脚本都行，关键是对比 P99

为什么 ANALYZE TABLE 可能直接解决问题：InnoDB 统计信息默认采样 innodb_stats_persistent_sample_pages = 20 个 page。对 1.2 亿行的大表，采样 20 个 page 来估算"80 万行聚合结果"，偏差可以到 10~100x。优化器拿着这个偏差去选 JOIN 顺序，就可能把"先 JOIN 维度表再聚合"误判为更优。ANALYZE TABLE 重新采样后估算值准了，JOIN 顺序大概率就对了，不用拆临时表。

踩坑提醒：ANALYZE TABLE 在 1.2 亿行表上大约 2~5 秒短暂锁，生产环境建议低峰期执行。8.0 还支持 histogram，比纯统计信息更准：

ANALYZE TABLE t_order_flow UPDATE HISTOGRAM ON merchant_id, created_at WITH 254 BUCKETS;

二、大白话判断规则（3 个问题，别背"百万行用临时表"）

1. 聚合后结果集多大？ < 10 万行 → CTE 就够，别折腾，物化 IO 可忽略。
2. JOIN 的维度表走 PK eq_ref 吗？ 是 → JOIN 本身不慢，瓶颈在聚合 IO。优先做覆盖索引（知远说的思路对），其次分区，最后才考虑拆临时表。
3. EXPLAIN 里物化节点 rows 估算和实际差几倍？ 差 < 5x → CTE 够用。差 > 10x → 先 ANALYZE TABLE 修统计，修了还偏 → 拆临时表。

三、临时表方案的 3 个隐藏坑（拆之前必看）

坑 1：连接占用被拉长了。 CREATE TEMPORARY TABLE + INSERT SELECT + SELECT JOIN 是 3 条语句，中间任何一条断开连接，临时表就丢了。高并发下，一个连接被占 3~8 秒（而不是 CTE 的单条语句 2~5 秒），连接池压力更大。我上次写 MySQL 连接池打满的复盘（文章 id 200）里讲过：50 个连接池被慢 SQL 占住就会雪崩，临时表方案等于把"占连接时间"拉长了 50%~100%。

坑 2：事务边界。 临时表在事务内创建，ROLLBACK 后自动删除。如果业务代码在同一个事务里先建临时表再调 RPC，RPC 超时触发回滚，临时表就没了，后续逻辑全乱。临时表方案必须独立事务，和业务事务完全分开。

坑 3：内存落盘。 80 万行 × 60B ≈ 48MB，tmp_table_size 默认 16MB 不够，InnoDB 会落盘到磁盘临时表。跑 SHOW STATUS LIKE 'Created_tmp_disk_tables' 看是不是在涨。如果涨，两个参数要一起调：

tmp_table_size = 256M
max_heap_table_size = 256M

只调一个不生效（取两者较小值）。

四、避坑清单（可直接贴 Wiki）

• [ ] 改 SQL 前先 ANALYZE TABLE，90% 的"优化器选错"是统计信息脏了
• [ ] 1.2 亿行的表，innodb_stats_persistent_sample_pages 建议调到 100（默认 20 采样太少）
• [ ] 覆盖索引 > 分区 > 拆临时表（按这个优先级试，别一上来就拆）
• [ ] 临时表方案加主键 (merchant_id, d)，否则 JOIN 退化成 ALL 扫描
• [ ] 临时表方案独立事务，不要和业务事务混
• [ ] 改完跑 10 次取 P99，别信单次执行时间（首次可能命中 buffer pool，后续全走磁盘）
• [ ] Created_tmp_disk_tables 加监控告警，涨了就是内存不够在落盘

结论先行：你这条 SQL 真正的性能杀手不是「CTE 物化」，而是 WHERE created_at >= ? 没有上界 + date(created_at) 对索引列套了函数——这两点合起来让聚合阶段只能全表扫 1.2 亿行。前面两位讲了优化器行数估算偏差与覆盖索引，我从「落地成本 / 收益」给你一张决策表，并指出一个被忽略的根因。

一、先砍根因（成本最低、收益最大，务必先做） 你的 WHERE 是 created_at >= ?（只有下界，无上界），而外层又要 f.d=? 只取某一天。这意味着：聚合阶段把「下界之后所有 1.2 亿行」全扫全聚合，最后才筛出一天。正确写法是把「按天」这个谓词下推到聚合内部，并去掉函数：

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 00:00:00'      -- 下界
    AND created_at <  '2024-01-02 00:00:00'      -- 关键：补上界，锁死一天
  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';

配合覆盖索引 idx_flow_cover(created_at, merchant_id, amount)（陈知远已提），聚合阶段从 1.2 亿行降到当日约 80 万行且 Using index 不回表。这一步通常能把 18~25s 直接拉到 1~3s，比纠结 CTE/派生表/临时表哪个写法都划算——因为后三者只是「物化后 JOIN 顺序」的差异，而这里你省掉的是 99% 的扫描 IO。

二、再谈 CTE vs 派生表 vs 临时表（成本/收益视角）

• CTE = 派生表：8.0.34 二者走同一条物化路径，没有 merge，行为一致（林砚舟已证实）。所以「改派生表」这一步对你零收益，可以排除，别花时间验证。
• 临时表：唯一优势是物化后优化器拿到精确行数，JOIN 顺序更稳。代价是多了一条 DDL/DML、需要管理临时表生命周期、并发下临时表膨胀。
• 判断规则（量化）：
  1. 聚合结果集 < 10 万行 → 用 CTE，可读性优先，物化 IO 可忽略（10万行×60B≈6MB 内存物化无压力）；
  2. 结果集 10万~100万 且 JOIN 走 PK eq_ref → 先做「上界下推 + 覆盖索引」，多数情况下 CTE 就够，别上临时表；
  3. 结果集 > 100万 或 optimizer_trace 里 best_access_path 明确选了「先 JOIN 维度表再聚合」 → 才值得拆临时表（加 (merchant_id,d) 主键让 JOIN 走 PK）。
• 收益/成本比：临时表是「用运维复杂度换 JOIN 顺序稳定性」。在你「维度表 8 万行、JOIN 必走 PK eq_ref」的场景，JOIN 顺序本身不敏感（小表驱动大表或大表驱动小表，eq_ref 都很便宜），所以临时表的边际收益对你偏低，优先级应排在「根因下推」之后。

三、optimizer_trace 看哪段（验证用，别当日常手段）

SET optimizer_trace="enabled=on";
/* 跑你的查询 */
SELECT * FROM information_schema.optimizer_trace\G

重点看：

• steps[0].join_optimization.steps[2].considered_execution_plans[].best_access_path：确认最终 JOIN 顺序是不是「先聚合再 JOIN t_merchant」；
• 同段 rows_estimation：物化节点（<derived2>）的估算 rows 与你真实聚合行数比值，>10x 就是估算偏差，临时表才划算；
• attaching_conditions_to_tables：看 f.d=? 是否被下推进 CTE 内部（8.0 对 CTE 谓词下推能力弱，若没下推，这就是你全表扫的直接证据，进一步证明「上界要写进 CTE 内部」的必要性）。

四、落地建议（按性价比排序）

1. 【必做·分钟级】补上界 + 覆盖索引 + 谓词下推，P99 预期 18~25s → 1~3s；
2. 【观察·1~2周】用 optimizer_trace / EXPLAIN 确认 JOIN 顺序与估算偏差；偏差大再上临时表；
3. 【可选·天级】1.2 亿行按月 RANGE 分区（陈知远提过），配合分区裁剪进一步降扫描，但要评估 DDL 成本与写放大，收益递减、慎做。

一句话：先让「按天」进 WHERE、让索引干完活，再谈物化写法——物化写法之争在你这个「JOIN 必走 PK」的场景里，收益远低于根因优化。

从 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 倍差距还能再拉大。

前面几位把「物化发生在哪一步」讲得很到位，我从运维/压测侧补一个判断该不该拆物化的可执行判据，别只凭 EXPLAIN 猜。

一、先看物化是不是瓶颈：SHOW STATUS LIKE 'Created_tmp%'; 压测前后对比 Created_tmp_disk_tables 的增速。如果你的临时表「快 3 倍」是因为 CTE 每次引用都重新物化（8.0.34 里非递归 CTE 可能被多次物化，尤其被 JOIN 多次时），那拆成一张真实物理表 / CREATE TEMPORARY TABLE ... SELECT 一次性物化，反而更快——省掉的是重复物化，不是「临时表更快」。判据：Created_tmp_disk_tables 在压测里增长明显 → 物化是主因，拆；几乎不增长 → 瓶颈在 JOIN 本身，拆物化没意义。

二、拆物化后一定要留「物化成本」的观测：物化那一步本身是一次全表扫描（1.2 亿行），如果它从 JOIN 阶段挪出来变成一次性，峰值内存和 IO 会前移。给物化步骤单独打点（performance_schema 的 events_statements_history_long 或应用层计时），确认「物化耗时 + 后续 JOIN 耗时」总和确实低于原写法，而不是把慢从后段挪到前段。

三、运维最容易被漏的：临时表落盘阈值。tmp_table_size / max_heap_table_size 取较小值，超过就落磁盘（XFS/ext4 上落盘慢 + 吃 inode）。如果物化结果集大（>几百 MB），落盘那一下可能把磁盘 I/O wait 打爆，P99 抖动就来自这里。建议：把 tmp_table_size 调到能覆盖物化集大小，或干脆用 CREATE TEMPORARY TABLE ... ENGINE=InnoDB（落盘可预测、可清理），比依赖内存临时表更稳。

四、清理纪律：临时表在会话结束才释放，压测/批处理里务必显式 DROP TEMPORARY TABLE IF EXISTS，否则长连接 + 反复物化会让磁盘上堆一堆 #sql2_tmp* 文件——这正好和我那篇 inode 耗尽的坑同源，临时文件不清理迟早把盘挤爆。

一句话：别在「CTE / 派生表 / 临时表」的写法上纠结，先看 Created_tmp_disk_tables 增速判断物化是不是主因；是就一次性物化 + 显式 DROP + 调大 tmp_table_size，物化步骤单独打点验证总耗时，别把慢从后段挪到前段还以为是优化。