关联千万级ScanCode表查询DISTINCT TOP记录性能过低如何优化

问题根因 #

原查询性能劣化核心原因是：左连接会把单个Item对应的所有ScanCode行全部关联生成重复数据，DISTINCT需要对超大关联结果集做全量排序去重，ScanCode表1200万行的数据量下这个操作开销极高。

可行优化方案 #

方案1：用EXISTS半连接代替JOIN（最优方案，无重复，性能最优） #

你不需要返回ScanCode表的任何字段，仅需要按ScanCode过滤，用半连接匹配即可，不会生成重复行，完全不需要DISTINCT：

Declare @p_scanCode BIGINT = NULL, @p_limit INT = 500

SELECT TOP(@p_limit) i.ItemCode, i.StandardDescription,i.ItemType 
FROM Item i
WHERE 
  (@p_scanCode IS NULL) 
  OR EXISTS(
    SELECT 1 FROM ScanCode sc 
    WHERE sc.FK_ItemCode = i.ItemCode AND sc.ScanCode = @p_scanCode
  )
ORDER BY ItemCode

这个写法的优势是EXISTS匹配到对应Item的任意一个ScanCode就会停止校验，不需要关联所有ScanCode行，也不会产生重复数据，性能和最初不加DISTINCT的版本基本一致。

方案2：ScanCode预去重后再关联 #

如果有特殊场景必须用JOIN写法，可以先对ScanCode做聚合去重，再和Item表关联，避免生成大量重复行：

Declare @p_scanCode BIGINT = NULL, @p_limit INT = 500

SELECT TOP(@p_limit) i.ItemCode, i.StandardDescription,i.ItemType 
FROM Item i
LEFT OUTER JOIN (
  SELECT DISTINCT FK_ItemCode 
  FROM ScanCode 
  WHERE @p_scanCode IS NULL OR ScanCode = @p_scanCode
) sc ON sc.FK_ItemCode = i.ItemCode
ORDER BY ItemCode

配套索引优化（可进一步提升性能） #

给ScanCode表创建联合索引idx_scancode_fkitem (ScanCode, FK_ItemCode)，当@p_scanCode有值时可以直接通过索引定位到对应ItemCode，不需要全表扫描，查询性能可以进一步压缩到毫秒级。

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