一项简单的数据库小技巧如何将电子邮件处理速度提升 18 倍

电子邮件同步问题

假设你正在开发一款将文件中的电子邮件地址同步到数据库的应用。你的任务是：将文件中的所有电子邮件标记为已激活，停用数据库中存在但文件中不再包含的任何电子邮件，并记录每一次更改以备审计。这听起来很简单——但当你处理 100,000 个地址时，这种简单直接的方法就成了瓶颈。

开发者 Yaroslav Podorvanov 最近分享了解决这同一问题最初耗时 90 秒的经历——直到他使用一项 PostgreSQL 技术对其进行了重写，将耗时缩短至仅 5 秒。在 2026 年 7 月 8 日，这种方法非常值得深入了解，因为它揭示了数据库工作原理中的一个重要事实：真正的成本通常不在于繁重的数据处理——而在于你的代码与数据库之间的通信对话。

慢速版本的工作原理

最初的解决方案将这项工作拆分为多个独立的步骤：

1. 插入或更新来自文件的新电子邮件地址
2. 停用不在文件中的任何电子邮件地址
3. 在历史记录表中记录每次更改以备审计

每个步骤都是独立的数据库查询。这意味着代码必须：

• 将电子邮件列表发送给数据库
• 等待响应
• 获取已发生更改的 ID
• 将这些 ID 发回给数据库
• 再次等待
• 重复执行停用操作
• 再次重复执行历史记录操作

在处理 100,000 个地址时，所有这些等待累积起来：总共耗时 90 秒。

CTE 解决方案

通用表表达式（CTE）是 PostgreSQL 允许你将复杂查询分解为有名称的、可重用的块的一种方式——这一切都在单个数据库操作中完成。Podorvanov 没有向数据库发起五次独立的往返请求，而是将所有内容移入了单个查询中：

WITH
  data (email) AS (
    SELECT UNNEST(@emails::VARCHAR[])
  ),
  deactivated (id) AS (
    UPDATE rtt_emails
    SET active = FALSE, updated_at = @now::TIMESTAMP
    WHERE email NOT IN (SELECT email FROM data)
    AND active = TRUE
    RETURNING id
  ),
  activated (id) AS (
    INSERT INTO rtt_emails (email, active, created_at, updated_at)
    SELECT email, TRUE, @now::TIMESTAMP, @now::TIMESTAMP
    FROM data
    ON CONFLICT (email) DO UPDATE
    SET active = EXCLUDED.active, updated_at = EXCLUDED.updated_at
    WHERE rtt_emails.active IS DISTINCT FROM EXCLUDED.active
    RETURNING id
  ),
  deactivated_history AS (
    INSERT INTO rtt_email_history (email_id, active, file_id, created_at)
    SELECT id, FALSE, @file_id::BIGINT, @now::TIMESTAMP
    FROM deactivated
  ),
  activated_history AS (
    INSERT INTO rtt_email_history (email_id, active, file_id, created_at)
    SELECT id, TRUE, @file_id::BIGINT, @now::TIMESTAMP
    FROM activated
  )
SELECT
  (SELECT COUNT(*) FROM activated) AS activated_count,
  (SELECT COUNT(*) FROM deactivated) AS deactivated_count;

看似复杂的内容实际上是在执行独立查询所做的操作——但全部是在与数据库的同一次对话中完成的。CTE 将各个部分串联在一起：传入的数据输入到激活步骤，激活步骤接着输入到历史记录步骤，依此类推。

速度提升

结果不言自明：

• 10,000 个地址： ~5 秒（慢速版本）→ ~3 秒（CTE）
• 100,000 个地址： ~90 秒（慢速版本）→ ~5 秒（CTE）
• 100 万个地址： ~30 秒（CTE）

对于 10 万个地址的情况，速度提升了 18 倍，这纯粹是通过重构数据流向来实现的——并不是让数据库承担更繁重的工作，而是让它更智能地工作。

为什么这很重要

这里的经验教训有些违背直觉：在数据库中，往返成本（数据来回发送所花费的时间）往往远大于实际的计算成本。通过将五次往返合并为一次，Podorvanov 不仅节省了时间，还减轻了服务器负载，并使该操作更具原子性（要么全部成功，要么全部失败，中间没有任何部分完成的状态）。

这不是魔法；这是一个适用于任何地方的原则：尽量减少不必要的反复往返，系统就会变快。这种技术适用于电子邮件同步、批量导入，或者任何将多个读写操作链在一起的操作。

结论

当数据库操作感觉缓慢时，罪魁祸首往往不是查询本身，而是你正在运行多少个独立的查询。CTE 允许你将多个操作合并为一个原子步骤，大幅削减往返开销并显著提升吞吐量。对于诸如电子邮件同步或批量上传之类的工作负载，这种优化可以是 90 秒操作与 5 秒操作之间的区别。

优点

• 显著减少数据库往返次数，降低延迟
• 保持数据一致性——所有更改都在一个事务中原子地发生
• 简化应用代码（减少独立的函数调用）
• 呈线性扩展；同一个查询适用于 1 万或 100 万个地址
• 充分利用数据库优势，而不是与其相背而行

缺点

• CTE 语法更为复杂，可能更难调试
• 需要熟悉高级 SQL——对于刚接触数据库的团队来说不够理想
• 数据库日志粒度变小（更难精确查看是哪一步变慢了）
• 并非所有数据库都对 CTE 提供同样出色的支持
• 对于开销并不重要的小型数据集来说可能大材小用

注意事项

本文具有教育意义，取材于真实案例。在实现类似的优化时，请将任何占位符值（如 @file_id 或 @now）替换为应用中的实际变量，使用你自己的数据量进行彻底测试，并在生产环境中依赖它们之前对照原始出处验证相关断言。数据库性能极度依赖于上下文环境；这里有效的方法可能需要针对你具体的模式、索引和硬件进行调优。

常见问题解答

• PostgreSQL 中的 CTE 是什么？它如何提高查询性能？
• CTE 能否用于电子邮件同步之外的其他操作？
• 当出现问题时，你如何调试 CTE 查询？
• CTE 和存储过程有什么区别？
• 所有 SQL 数据库支持 CTE 的方式都一样吗？
• 什么时候 CTE 是正确的工具，而什么时候应该使用独立的查询？
• CTE 如何影响索引使用和查询计划？
• 如果 CTE 操作中途失败会发生什么？

标签

#postgres #database #performance #cte #sql #optimization #batch-processing #postgresql