SQL Server 2017：无需子查询查询同表不同行的方法

问题背景 #

现有一个用于数据迁移转换的大型查询，包含大量子查询。关联的表数据量极大（部分表超20万行，部分超50万行），子查询的使用导致查询复杂度指数级上升——新增一个子查询就会让执行时间从约1分钟飙升到数小时。

需求是利用当前行的值，在关联表中获取另一行的ID。使用SQL Server 2017，原有的子查询方案性能极差（可能耗时数小时甚至数天），需要更高效的替代方案。

示例表结构与原查询 #

表定义 #

create table dbo.sm (
  id varchar(max) primary key,
  apn varchar(max)
)
create table dbo.gi (
  global_id varchar(max) primary key,
  au_stock_code varchar(max)
)

测试数据 #

insert into dbo.sm 
values
('a', 'b'),
('b', 'abcdefgh')

insert into dbo.gi 
values
(1, 'a'),
(2, 'b')

原查询（性能极差） #

select
  gi.global_id, 
  sm.id,
  (select global_id from dbo.gi where au_stock_code  = sm.apn)
from dbo.sm sm
left join dbo.gi gi on sm.id = gi.au_stock_code

期望输出 #

1 - 'a' - 2

解决方案 #

改用JOIN替代子查询 #

子查询性能差的核心原因是它会对每一行执行一次单独查询，属于嵌套循环的最坏情况。改用JOIN能让SQL Server优化器生成哈希连接、合并连接这类更适合大数据量的高效执行计划。

修改后的查询：

select
  gi.global_id,
  sm.id,
  gi_apn.global_id as apn_global_id
from dbo.sm sm
left join dbo.gi gi on sm.id = gi.au_stock_code
left join dbo.gi gi_apn on sm.apn = gi_apn.au_stock_code

额外性能优化建议 #

• 替换varchar(max)为定长/有限长度字符串：varchar(max)的索引效率远低于固定长度或有限长度的字符串类型，若业务允许，将au_stock_code、id、apn改为varchar(N)（N为实际所需最大长度，如50），并为关联字段创建非聚集索引：

  CREATE NONCLUSTERED INDEX IX_gi_au_stock_code ON dbo.gi(au_stock_code) INCLUDE(global_id);
  CREATE NONCLUSTERED INDEX IX_sm_apn ON dbo.sm(apn) INCLUDE(id);
  

• 检查执行计划：通过SQL Server Management Studio查看执行计划，定位表扫描、键查找等低效操作，针对性优化索引或查询逻辑。

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