如何优化SQL Server中生成lookup table的关联查询性能？

本次操作目标是创建查找表，避免后续针对更大数据集执行自连接。场景里销售订单可能包含账单客户ID、发货客户ID中的一个或两个。

涉及的表是5台不同服务器的聚合数据，用box_id区分：

• customer表：约170万行
• sales_order表：约5500万行
• 查询最终返回约5200万条记录，平均运行时长约80分钟

当前使用的查询语句：

SELECT DISTINCT sog.box_id  ,
    sog.sales_order_id  ,
    cb.cust_id AS bill_to_customer_id  ,
    cb.customer_name AS bill_to_customer_name  ,
    cs.cust_id AS ship_to_customer_id  ,
    cs.customer_name AS ship_to_customer_name 
FROM sales_order sog 
LEFT JOIN customer cb  ON cb.cust_id = sog.bill_to_id   AND cb.box_id = sog.box_id 
LEFT JOIN customer cs  ON cs.cust_id = sog.ship_to_id   AND cs.box_id = sog.box_id

环境为SQL Server，已尝试用CTE重构账单/发货客户集再关联，但无性能提升。现有索引仅为主键（合成ID），且执行计划分析器未建议新增索引（与它通常的行为不符），希望找到提速方法并提升查询优化能力。

1. 创建针对性覆盖索引 #

别依赖执行计划的建议，当前主键索引无法覆盖JOIN和查询所需的列，直接创建复合覆盖索引：

• 针对customer表：以box_id, cust_id为索引键，包含customer_name，这样JOIN时无需回表查询数据：

  CREATE NONCLUSTERED INDEX IX_Customer_BoxId_CustId
  ON customer (box_id, cust_id)
  INCLUDE (customer_name);
  

• 针对sales_order表：若sales_order_id是主键，创建包含bill_to_id, ship_to_id, box_id的覆盖索引；若不是，则将sales_order_id加入索引键或包含列：

  CREATE NONCLUSTERED INDEX IX_SalesOrder_BoxId_BillShip
  ON sales_order (box_id, sales_order_id)
  INCLUDE (bill_to_id, ship_to_id);
  

  这类索引能让SQL Server直接从索引获取所需全部字段，避免全表扫描。

2. 移除不必要的DISTINCT #

先确认sales_order表中box_id + sales_order_id是否唯一：如果sales_order_id是主键，这个组合必然唯一，此时DISTINCT完全多余，会额外增加排序、去重的开销。先去掉DISTINCT测试结果，若数据无重复则保留修改后的查询，能大幅降低CPU和内存消耗。

3. 尝试分批处理或分区策略 #

• 分区：box_id仅包含5个固定值，可按box_id对sales_order和customer表分区，查询时只会扫描对应分区的数据，减少IO压力。
• 分批处理：既然目标是创建查找表，可按box_id分批处理单服务器的数据，避免一次性处理5500万行的负载，同时利用并行处理提升效率。

4. 手动排查执行计划关键环节 #

即使看不到具体执行计划，也可以重点检查：

• 是否存在表扫描/聚集索引扫描：若有，说明现有索引未被有效利用，上述覆盖索引可解决该问题。
• JOIN类型：大表连接通常哈希匹配更高效，但有合适索引时，合并连接性能更优。
• 内存授予情况：若查询占用大量tempdb空间，说明内存授予不足，可调整内存设置或通过优化索引降低内存需求。

5. 更新统计信息 #

过时的统计信息会导致SQL Server生成低效执行计划，需确保两张表的统计信息为最新：

UPDATE STATISTICS customer WITH FULLSCAN;
UPDATE STATISTICS sales_order WITH FULLSCAN;

大表使用FULLSCAN能保证统计信息准确性，虽耗时较长，但能从根源避免执行计划偏差。

内容的提问来源于stack exchange，提问作者Koz-SQL