一个WITH子句如何让亿级数据查询直接起飞？

那是一个周四的下午，我正为一个金融科技客户的性能报告而焦头烂额。他们的用户群在第一季度经历了爆炸式增长，这意味着我们那些曾经响应迅速的仪表盘，现在慢得像老掉牙的拨号上网。

我的任务是优化一个为他们客户消费分析功能提供数据的核心查询。这个查询本身并不算太复杂——仅仅是拉取上个季度按客户细分群体统计的总交易金额。

但坑爹的是：transactions (交易) 表有超过 1.5 亿行数据！

而我的那个查询呢？它跑一次要超过 2 分钟，有时候甚至直接超时。

---

原始查询 (又名“这玩意儿咋就这么慢呢？”)

我一开始用的查询是这样的：

SELECT
    c.customer_id,
    c.segment,
    SUM(t.amount) AS total_spent
FROM
    customers c
JOIN
    transactions t ON c.customer_id = t.customer_id
WHERE
    t.transaction_date >= '2024-01-01' -- 只筛选特定日期之后的交易
GROUP BY
    c.customer_id, c.segment;

乍一看，这 SQL 似乎还挺干净的：基于已索引的外键进行连接，用 WHERE 子句过滤近期的交易，再用 GROUP BY 按客户统计花费。

那么问题到底出在哪儿呢？

结果发现，即使我对 transaction_date 进行了过滤，数据库执行 JOIN (连接) 操作实际上是在执行日期过滤之前。
这意味着数据库引擎在缩小数据范围之前，对那 1.5 亿行交易数据进行了全表扫描！🤯

我尝试了添加更多索引、重写 JOIN 的方式、加上 LIMIT 子句等等，但都没能带来我需要的性能提升。

---

改变局势的“大杀器”：WITH 子句 (公用表表达式 - CTE)

在近乎绝望之下，我用 CTE (Common Table Expression) 重写了查询——主要目的是为了让调试更容易一些。
结果，这一改，一切都不同了。

-- 使用 CTE 先筛选出近期的交易数据
WITH recent_transactions AS (
    SELECT
        customer_id, -- 确保 customer_id 被选出用于后续 JOIN
        amount,
        transaction_date -- 虽然这里不直接用，但筛选条件基于它
    FROM
        transactions
    WHERE
        transaction_date >= '2024-01-01'
)
-- 然后再将筛选后的结果与 customers 表连接
SELECT
    c.customer_id,
    c.segment,
    SUM(r.amount) AS total_spent -- r 代表 recent_transactions
FROM
    customers c
JOIN
    recent_transactions r ON c.customer_id = r.customer_id
GROUP BY
    c.customer_id, c.segment;

现在，查询不再是先扫描 1.5 亿行交易数据然后再进行日期过滤，而是首先从 transactions 表中过滤出相关的（比如）2000 万行数据到 recent_transactions 这个临时的结果集中，然后再将这个小得多的结果集与 customers 表进行连接。

🚀 执行时间从 127 秒 骤降到了 11.4 秒！

没有其他任何改动，同样的数据，仅仅是优化了查询的结构。

---

让我们来分析一下为什么这样改动就有效了

1. 尽早过滤 = 更少的数据参与连接
当参与连接的数据集已经被大幅缩减后，连接操作自然就快得多了。你可以把它想象成在把一堆衣服扔进洗衣机之前，先做好了分类筛选，挑出了真正需要洗的那些。

2. 更优的执行计划 (Cleaner execution plan)
现代的查询优化器（比如 PostgreSQL 12+、Google BigQuery 和 SQL Server 中的优化器）能够将 CTE 视为内联视图（inline views）或有效地进行处理——这有助于它们在优化查询时做出更明智的决策，优先执行 CTE 部分以缩小数据集。

3. 模块化的逻辑 = 更容易调试
将你的复杂查询逻辑拆分成更小、更专注的 CTE，可以让你更容易发现低效的部分，并且可以单独测试查询的每个组成部分是否符合预期。

---

额外优化：在 CTE 内部直接进行聚合

想让性能更上一层楼吗？下面这个版本，我直接在 CTE 内部就完成了按 customer_id 的聚合操作：

-- 在 CTE 中就完成按客户ID的聚合
WITH recent_spend AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent -- 直接在 CTE 里计算每个客户的总花费
    FROM
        transactions
    WHERE
        transaction_date >= '2024-01-01'
    GROUP BY
        customer_id -- 按 customer_id 分组
)
-- 然后将聚合后的结果与 customers 表连接
SELECT
    c.customer_id,
    c.segment,
    r.total_spent -- r 代表 recent_spend
FROM
    customers c
JOIN
    recent_spend r ON c.customer_id = r.customer_id;
-- 注意：这里的 GROUP BY customers.segment 如果还需要，则应加在最外层查询
-- 如果 customer_id 已经是唯一的，且 segment 与 customer_id 一一对应或不需要再次聚合，则可省略

现在，我是在一个已经聚合好的结果集（recent_spend）上进行连接，这同时减少了需要连接的行数和后续的处理时间。在我的实际案例中，这个版本的查询执行时间被压缩到了 6 秒以内！

---

最终思考

写了这么多年 SQL，我学到了一点：一个好查询和一个真正牛逼的查询之间的区别，不在于语法有多花哨，而在于其结构。

使用 WITH 子句（CTE）就像给你的复杂逻辑加上了清晰的注释。它能阐明你的意图、分离不同的逻辑关注点，并且在很多情况下，能够极大地提升查询性能。

但这里也有几点需要注意（The catch）：

• • 在一些老版本的 MySQL 中，CTE 可能会被“物化”（materialized），即数据库会为 CTE 创建实际的临时表，这反而可能降低性能（新版本 MySQL 对 CTE 的优化已大大改善）。

• • 在 PostgreSQL 中，建议使用 12 或更新版本，以便充分利用其对 CTE 的内联优化（inlining）能力。

• • 在 Google BigQuery 中，通常推荐使用 CTE，这对于成本控制和查询性能的可见性都有好处。

要点总结 (Key Points)

• • 我那个处理 1.5 亿行数据的查询，仅仅通过使用 CTE 就获得了将近 10 倍的速度提升。

• • 使用 WITH 子句尽早过滤数据，以减轻后续 JOIN 操作的负载。

• • 优化前后一定要测试性能——你的 SQL 数据库引擎及其版本很重要。

• • 整洁的 SQL = 更快的 SQL = 更快乐的数据分析师（和开发者）！