Exclusion_List表数据量过大导致SQL查询超时的优化咨询

核心优化方向及可落地措施 #

• 索引优化（优先级最高）
  你当前的超时根本原因是每次查询都对50万行级的Exclusion_List做全表扫描匹配，首先需要给两张表的关联字段加索引：

  1. 给Exclusion_List的EmailAddress字段建立非聚集索引，该场景下仅需要判断邮箱是否存在，不需要读取其他字段，这个索引会被直接用来做匹配，不需要扫描全表：

  CREATE NONCLUSTERED INDEX IX_ExclusionList_EmailAddress ON [Exclusion_List] (EmailAddress);
  

  2. 同步给Customer表的EMAIL字段也建立非聚集索引，进一步降低关联匹配的开销。
     注：你第一段SQL里写的Customerd为拼写笔误，实际应为Customer，执行时注意修正即可。

• 替换低效的NOT IN写法
  大数据量下NOT IN子查询的性能远低于NOT EXISTS或者LEFT JOIN + IS NULL，且如果Exclusion_List的EmailAddress存在NULL值，NOT IN还会返回不符合预期的空结果，推荐改为NOT EXISTS写法：

  SELECT TOP 10 PERCENT EMAIL,CUSTOMER_ID,GETDATE() AS date,CUSTOMER_UUID
  FROM Customer t1
  WHERE NOT EXISTS (
      SELECT 1 FROM [Exclusion_List] t2 WHERE t2.EmailAddress = t1.EMAIL
  )
  

• 预处理排除表数据
  因为Exclusion_List每月新增50万行，大概率存在大量重复的邮箱地址，可以新增一张去重的排除中间表，每月定时任务把Exclusion_List里的EmailAddress去重后同步到该表，查询时直接关联这张数据量小很多的中间表即可，能大幅降低关联数据量。

• 定期归档冷数据
  如果业务上有排除有效期规则，可以定期把Exclusion_List中超过有效期的历史数据归档到备份表，避免主表数据无限膨胀，进一步降低扫描开销。

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