PostgreSQL中实现等效嵌套删除的高性能SQL方案

请参考如下Order、Item与Article实体对应的ERD图：

业务需求为：删除满足复杂筛选条件的Orders记录，随后删除关联到这些订单的Items记录，最终删除关联到上述订单项的Articles记录。

已知约束：

• Order是Item的父表，Orders到Items的级联删除可正常配置生效
• Item并非Article的父表，无法配置从Item到Article的级联删除规则
• 触发器方案不可行：常规业务场景下删除Item时无需同步删除关联的Article，该联动删除逻辑仅在本次查询场景下生效
• 数据库为PostgreSQL，支持DELETE ... RETURNING语法，但不支持多层嵌套的DELETE子查询写法：

DELETE FROM articles WHERE id IN
  (DELETE FROM items WHERE order_id IN
    (DELETE FROM orders WHERE complex_condition RETURNING id)
  RETURNING article_id)

• 三张表单表数据量均达数千万级，complex_condition是执行耗时最高的部分，要求该条件仅执行一次，避免重复计算的性能开销
• 临时表方案虽然可行，但代码过于繁琐，追求更简洁的实现方式

直接使用PostgreSQL原生支持的**可写CTE（数据修改型公用表表达式）**实现，无需临时表，单条SQL即可完成所有操作，复杂筛选条件仅执行一次，是千万级数据量下的性能最优方案。

实现代码 #

WITH target_orders AS (
  -- 仅在此处执行一次复杂筛选，拿到所有待删除的订单ID
  SELECT id AS order_id
  FROM orders
  WHERE complex_condition
),
target_items AS (
  -- 关联查询待删除订单对应的所有订单项，以及关联的文章ID
  SELECT i.id AS item_id, i.article_id
  FROM items i
  INNER JOIN target_orders t_o 
    ON i.order_id = t_o.order_id
),
target_articles AS (
  -- 去重得到所有待删除的文章ID，避免重复执行删除操作
  SELECT DISTINCT article_id AS id
  FROM target_items
),
-- 按照外键依赖顺序从子表到父表执行删除
del_articles AS (
  DELETE FROM articles a
  USING target_articles t_a
  WHERE a.id = t_a.id
  RETURNING a.id
),
del_items AS (
  DELETE FROM items i
  USING target_items t_i
  WHERE i.id = t_i.item_id
  RETURNING i.id
)
-- 最后删除订单，若已配置Order到Item的级联删除，可省略del_items步骤
DELETE FROM orders o
USING target_orders t_o
WHERE o.id = t_o.order_id
RETURNING o.id;

方案优势 #

• 性能开销最低：复杂筛选逻辑仅执行一次，省去了临时表方案中数据写入临时表、查询临时表、清理临时表的额外IO开销；所有关联查询均基于预筛选出的订单ID执行，只要items.order_id、items.article_id字段存在索引，关联效率极高。
• 逻辑简洁：单条SQL完成全链路删除操作，不需要多条语句配合，无需手动管理临时表生命周期。
• 数据一致性强：整个语句运行在同一个事务快照下，所有删除操作同生共死，不会出现并发操作导致的漏删、误删问题，也不会触发外键约束报错。

注意事项 #

• 提前检查索引：必须确保items.order_id、items.article_id上存在普通索引，否则千万级表的关联查询会产生全表扫描，性能极差。
• 超大批量删除优化：如果本次待删除的数据量超过百万级，建议评估长事务对线上业务的影响，必要时可以将target_orders的结果按ID分段分批执行，避免锁表时间过长阻塞正常业务请求。
• 级联删除适配：如果你确认orders到items的外键已经配置了ON DELETE CASCADE，可以直接删除del_items这个CTE步骤，删除订单时数据库会自动清理关联的订单项，进一步减少写入开销。

内容的提问来源于stack exchange，提问作者Sadeq Dousti