优化含SELECT子查询的SQL报表查询性能求助

首先明确你原查询的核心逻辑：

• 保留transaction_log的所有行
• 仅当transaction_log.location_id不为空时，从location_table中找到与transaction_log.source_location_id匹配的第一条非空master_location_id，否则返回NULL

你尝试的JOIN查询出现行数膨胀，主要有三个原因：

1. 关联字段错误：原查询用transaction_log.source_location_id关联location_table.location_id，但你的JOIN写的是t1.location_id = t2.location_id，匹配逻辑完全错误
2. 未做去重处理：location_table中同一个location_id可能存在多条master_location_id非空的记录，原查询用TOP 1只取一条，而你的JOIN会把所有匹配记录都关联，导致原表行被重复
3. JOIN类型错误：原查询是保留所有事务日志行的逻辑，你用了INNER JOIN会过滤掉无匹配的行，但前两个问题导致行数反而增加

正确的JOIN改写方案 #

方案1：用ROW_NUMBER()预过滤location_table

先对location_table按location_id分组，只保留每个分组的第一条有效记录，再与事务日志表做LEFT JOIN，完全匹配原查询逻辑：

SELECT 
       t.transaction_type, 
       t.description,
       t.id_num,
       t.product_id,
       t.quantity,
       t.location_id,
       loc.master_location_id AS master_location,
       t.dummy_value, 
       t.employee_id
FROM 
      transaction_log t WITH (NOLOCK)
LEFT JOIN (
    -- 给每个location_id的有效记录编号，只取第一条
    SELECT 
        location_id,
        master_location_id,
        ROW_NUMBER() OVER (PARTITION BY location_id ORDER BY (SELECT NULL)) AS rn
    FROM location_table
    WHERE master_location_id IS NOT NULL
) loc ON t.source_location_id = loc.location_id AND loc.rn = 1
-- 当transaction_log.location_id为空时，master_location自动为NULL，无需额外CASE判断

方案2：用FIRST_VALUE简化聚合

如果SQL Server版本支持（2012+），可以用FIRST_VALUE直接获取每个分组的第一条有效值：

SELECT 
       t.transaction_type, 
       t.description,
       t.id_num,
       t.product_id,
       t.quantity,
       t.location_id,
       loc.master_location_id AS master_location,
       t.dummy_value, 
       t.employee_id
FROM 
      transaction_log t WITH (NOLOCK)
LEFT JOIN (
    SELECT DISTINCT
        location_id,
        FIRST_VALUE(master_location_id) OVER (PARTITION BY location_id ORDER BY (SELECT NULL)) AS master_location_id
    FROM location_table
    WHERE master_location_id IS NOT NULL
) loc ON t.source_location_id = loc.location_id

性能优化关键步骤 #

1. 添加索引：给location_table创建联合索引，让预过滤子查询直接走索引，避免全表扫描：

CREATE NONCLUSTERED INDEX IX_location_table_location_master 
ON location_table(location_id) 
INCLUDE (master_location_id);

如果transaction_log的source_location_id查询频繁，也可以给这个字段创建包含查询常用字段的索引，减少回表开销。

2. 替换相关子查询：原查询的CASE中的子查询是相关子查询，每一行事务日志都要执行一次子查询，数据量大时性能极差。改成预聚合后JOIN的方式，location_table只需要扫描一次，性能会大幅提升。

3. 避免不必要的锁：保持WITH (NOLOCK)的使用（如果业务允许脏读），减少锁等待时间。

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