优化含子查询与Join的事务SQL查询速度请求

需求：查询存储事务详情的数据库，返回所有曾关联'Purch'的TransactionID，并获取这些事务各自生命周期的最后一步（即StepCount最新的记录）。现有查询可得到正确结果，但运行时长约15分钟，表包含数百万条记录，需优化性能。

示例表数据 #

预期返回结果 #

当前慢查询语句 #

Select 
    * 
From 
    (Select 
         A.TransactionID, 
         Company, 
         OrderType, 
         Status, 
         Row_Number() over (partition by A.TransactionID 
                            order by StepCount desc) as RecordNo 
     From 
         OrderTable as A 
     Inner Join 
         (Select 
              TransactionID 
          From 
              OrderTable 
          Where 
              Company = 'Company A' 
              And OrderType = 'Purch') as B on A.TransactionID = B.TransactionID 
     Group By 
         A.TransactionID, Company, OrderType, Status) as Data 
Where 
    Data.RecordNo = 1

性能优化建议 #

1. 简化查询逻辑，移除冗余操作 #

原查询中的GROUP BY是多余的（ROW_NUMBER()不需要分组就能计算），且子查询JOIN可以用EXISTS替代，减少一次全表扫描的开销。优化后的SQL如下：

SELECT 
    TransactionID, 
    Company, 
    OrderType, 
    Status, 
    StepCount
FROM (
    SELECT 
        TransactionID, 
        Company, 
        OrderType, 
        Status, 
        StepCount,
        ROW_NUMBER() OVER (PARTITION BY TransactionID ORDER BY StepCount DESC) AS RecordNo
    FROM OrderTable
    WHERE EXISTS (
        SELECT 1 
        FROM OrderTable AS B
        WHERE B.TransactionID = OrderTable.TransactionID
          AND B.Company = 'Company A'
          AND B.OrderType = 'Purch'
    )
) AS Data
WHERE Data.RecordNo = 1

2. 创建针对性的复合索引 #

针对查询中的过滤条件、分区字段和排序字段创建复合索引，让数据库能快速定位目标数据，避免全表扫描：

CREATE NONCLUSTERED INDEX IX_OrderTable_Company_OrderType_TransactionID_StepCount
ON OrderTable (Company, OrderType, TransactionID, StepCount)
INCLUDE (Status);

这个索引可以直接覆盖子查询中过滤Company='Company A' AND OrderType='Purch'的需求，同时主查询中按TransactionID分区、StepCount排序也能利用索引，减少排序开销。

3. 避免使用SELECT *，明确指定所需字段 #

原查询用SELECT *会返回所有字段，增加数据传输和内存处理的开销，只选择业务需要的字段（如TransactionID, Company, OrderType, Status, StepCount）能显著提升效率。

4. 分析执行计划定位瓶颈 #

查看数据库的执行计划，确认是否存在全表扫描、键查找、排序警告等低效操作：

• 如果存在全表扫描，说明索引未被正确使用，需调整索引或查询条件；
• 如果有大量排序操作，确认是否能通过索引消除排序。

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