sql-server - 在 SQL Server 2005 中的大表上更新为 "not in"

Question

我有一个包含大约 115k 行的表。像这样:

Table: People
Column: ID PRIMARY KEY INT IDENTITY NOT NULL
Column: SpecialCode NVARCHAR(255) NULL
Column: IsActive BIT NOT NULL

最初，我有一个这样定义的索引:

PK_IDX (clustered) -- clustered index on primary key
IDX_SpecialCode (non clustered, non-unique) -- index on the SpecialCode column

我正在做这样的更新:

Update People set IsActive = 0 
Where SpecialCode not in ('...enormous list of special codes....')

这个庞大的列表基本上包含表中 99% 的用户。

此更新在我的服务器上永远。作为测试，我将“not in”子句中的特殊代码列表修剪为大约表中 1% 的用户，我的执行计划最终使用 PK_IDX 索引上的 INDEX SCAN 而不是我使用的 IDX_SpecialCode 索引以为会用。

所以，我想也许我需要修改 IDX_SpecialCode，以便它在其中包含列“IsActive”。我这样做了，但我仍然看到执行计划默认为 PK_IDX 索引扫描，我的查询仍然需要很长时间才能运行。

那么 - 进行这种性质的更新更正确的方法是什么？我有我想从更新中排除的用户列表，但我试图避免从数据库中加载所有员工的特殊代码，在我的应用程序端过滤掉那些不在我的列表中的代码，然后运行我的查询带有 in 子句，在我的实际使用中这将是一个小得多的列表。

谢谢

Answer 1

如果您有要排除的员工，为什么不使用这些 PK_ID 填充索引表并执行以下操作:

Update People 
set IsActive = 0 
Where NOT EXISTS (SELECT NULL
                  FROM lookuptable l
                  WHERE l.PK = People.PK)

您正在进行索引扫描，因为 SQL Server 并不愚蠢，并且意识到只查看整个表而不是一次检查 100 个不同的条件更有意义。如果您的统计信息是最新的，优化器就会知道您的 IN 语句覆盖了多少表，如果它认为这样会更快，就会执行表或聚簇索引扫描。