PostgreSQL中关联Order与OrderStatus表查询最新状态的SQL优化

现有两张表Order和OrderStatus，其中Order.OrderCode与OrderStatus.Status已建立索引。

表结构 #

Order表 #

示例数据：

Id(PK),OrderCode
1,"AA-001"
2,"AA-001"
3,"AA-002"
4,"AA-002"
5,"AA-003"

OrderStatus表 #

示例数据：

Id(PK),OrderId(FK),Status,CreatedAt
1,1,"Open",2021-05-01 13:00:00
2,1,"Close",2021-05-01 13:05:00
3,2,"Open",2021-05-01 13:10:01
4,3,"Open",2021-05-01 13:10:02
5,3,"Close",2021-05-01 13:11:00
6,4,"Open",2021-05-01 13:11:01
7,4,"Close",2021-05-01 13:11:05
8,5,"Open",2021-05-01 13:12:00

需求说明 #

返回所有最新状态为Open的OrderCode分组对应的订单总数量，最终期望的分组统计结果如下：

OrderCode,Count
"AA-001",2
"AA-003",1

现有查询问题 #

你原来的写法存在两个明显的性能缺陷：

• 嵌套子查询+IN条件重复扫描了Order表两次，数据量大时开销会显著上升
• DISTINCT ON依赖全量排序去重，OrderStatus数据量大时性能损耗很高

优化方案 #

最优写法（适配PostgreSQL，兼容你原有使用DISTINCT ON的数据库环境） #

WITH valid_order_codes AS (
  -- 先筛选出最新状态为Open的订单对应的OrderCode
  SELECT DISTINCT o.OrderCode
  FROM "Order" o
  INNER JOIN (
    -- 用NOT EXISTS判断最新状态，无需全表排序
    SELECT os.OrderId
    FROM OrderStatus os
    WHERE os.Status = 'Open'
      AND NOT EXISTS (
        SELECT 1 FROM OrderStatus os2 
        WHERE os2.OrderId = os.OrderId 
          AND os2.CreatedAt > os.CreatedAt
      )
  ) os ON o.Id = os.OrderId
)
-- 统计符合条件的OrderCode对应的总订单数
SELECT 
  o.OrderCode,
  COUNT(o.Id) AS Count
FROM "Order" o
INNER JOIN valid_order_codes voc ON o.OrderCode = voc.OrderCode
GROUP BY o.OrderCode;

如果只需要总数量不需要分组结果，直接在外层套COUNT即可：

SELECT COUNT(*) AS total_count
FROM "Order" o
INNER JOIN (
  SELECT DISTINCT o1.OrderCode
  FROM "Order" o1
  INNER JOIN (
    SELECT os.OrderId
    FROM OrderStatus os
    WHERE os.Status = 'Open'
      AND NOT EXISTS (
        SELECT 1 FROM OrderStatus os2 
        WHERE os2.OrderId = os.OrderId AND os2.CreatedAt > os.CreatedAt
      )
  ) os ON o1.Id = os.OrderId
) voc ON o.OrderCode = voc.OrderCode;

额外优化建议 #

• 给OrderStatus新增联合索引(OrderId, CreatedAt DESC, Status)，子查询可以直接走索引定位最新状态，无需全表扫描，性能提升幅度可达数倍
• 所有关联用INNER JOIN替代LEFT JOIN，提前过滤无效数据，缩小中间结果集
• 去掉多余的DISTINCT去重逻辑，分组统计阶段天然会做去重，减少额外计算开销

内容的提问来源于stack exchange，提问作者Wilts C