SQL JOIN场景下多子查询的性能优化咨询

原查询的核心问题是多次重复扫描Order History和Orders表，且使用JOIN会过滤掉未触发部分事件的订单（比如未发布/未关闭的订单会被排除，不符合期望结果）。以下是针对性优化方案：

方案一：条件聚合（推荐） #

只扫描一次Order History表，通过条件聚合直接计算所有需要的时间字段，再与Orders表关联，大幅减少表扫描次数：

SELECT
    o.Order,
    o.Part,
    o.Status,
    -- 创建时间：筛选Created事件的时间（每个订单仅创建一次）
    MAX(CASE WHEN oh.Message = 'Created' AND oh.NewStatus = 'Planned' THEN oh.Timestamp END) AS CreatedDate,
    -- 首次发布时间：筛选Released状态变更的最小时间
    MIN(CASE WHEN oh.Message = 'Status Changed' AND oh.NewStatus = 'Released' THEN oh.Timestamp END) AS FirstRelease,
    -- 最新发布时间：筛选Released状态变更的最大时间
    MAX(CASE WHEN oh.Message = 'Status Changed' AND oh.NewStatus = 'Released' THEN oh.Timestamp END) AS LatestRelease,
    -- 关闭时间：筛选Closed状态变更的最大时间
    MAX(CASE WHEN oh.Message = 'Status Changed' AND oh.NewStatus = 'Closed' THEN oh.Timestamp END) AS ClosedDate
FROM Orders o
LEFT JOIN Order_History oh ON o.Order = oh.Order
GROUP BY o.Order, o.Part, o.Status
ORDER BY o.Order;

优势 #

• 仅扫描Orders和Order History各一次，避免重复IO开销
• 使用LEFT JOIN保留所有订单，未触发的事件对应字段显示NULL，完全匹配期望结果
• 逻辑简洁，后续维护成本低

方案二：预聚合子查询 #

如果数据库对条件聚合的优化有限，可先对Order History做一次预聚合，再关联Orders表：

WITH OrderHistoryAgg AS (
    SELECT
        oh.Order,
        MAX(CASE WHEN oh.Message = 'Created' AND oh.NewStatus = 'Planned' THEN oh.Timestamp END) AS CreatedDate,
        MIN(CASE WHEN oh.Message = 'Status Changed' AND oh.NewStatus = 'Released' THEN oh.Timestamp END) AS FirstRelease,
        MAX(CASE WHEN oh.Message = 'Status Changed' AND oh.NewStatus = 'Released' THEN oh.Timestamp END) AS LatestRelease,
        MAX(CASE WHEN oh.Message = 'Status Changed' AND oh.NewStatus = 'Closed' THEN oh.Timestamp END) AS ClosedDate
    FROM Order_History oh
    GROUP BY oh.Order
)
SELECT
    o.Order,
    o.Part,
    o.Status,
    oha.CreatedDate,
    oha.FirstRelease,
    oha.LatestRelease,
    oha.ClosedDate
FROM Orders o
LEFT JOIN OrderHistoryAgg oha ON o.Order = oha.Order
ORDER BY o.Order;

索引优化建议 #

为Order History表创建复合索引，进一步加速聚合查询：

CREATE INDEX idx_orderhistory_event ON Order_History (Order, NewStatus, Message, Timestamp);

该索引能让数据库快速定位每个订单的特定事件，避免全表扫描。

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