首先,假设AccountId是Accounts表的主键.

IN(4,10)将需要5个索引搜索,这意味着逻辑IO是索引的深度(每个搜索需要从根向下通过中间页面到正好一个叶页面) .

NOT IN(4,10)将需要一个完整的扫描和一个过滤器(可推式不可谓的谓词意味着它被推入扫描而不是一个单独的操作符),这意味着逻辑IO将等于页数在叶节点的索引中,非叶级数.

看到这个

CREATE  TABLE #Accounts
(
AccountID INT IDENTITY(1,1) PRIMARY KEY,Filler CHAR(1000)
)
INSERT INTO #Accounts(Filler)
SELECT 'A'
FROM master..spt_values

SET STATISTICS IO ON


SELECT * FROM #Accounts WHERE AccountID IN (4,10) 
/* Scan count 5,logical reads 10*/

SELECT * FROM #Accounts WHERE AccountID NOT IN (4,10)
/*Scan count 1,logical reads 359*/

SELECT index_depth,page_count
FROM
sys.dm_db_index_physical_stats (2,object_id('tempdb..#Accounts'),DEFAULT,'DETAILED')

返回

index_depth page_count
----------- --------------------
2           358
2           1

查看病理学上不同的情况,其中所有的行都符合IN子句,因此它们都不是NOT IN

SET STATISTICS IO OFF


CREATE  TABLE #Accounts
(
AccountID INT,Filler CHAR(1000)
)

CREATE CLUSTERED INDEX ix ON #Accounts(AccountID)

;WITH Top500 AS
(
SELECT TOP 500 * FROM master..spt_values
),Vals(C) AS
(
SELECT 4 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 9 UNION ALL
SELECT 10
)

INSERT INTO #Accounts(AccountID)
SELECT C
FROM Top500,Vals

SET STATISTICS IO ON

SELECT * FROM #Accounts WHERE AccountID IN (4,10) 
/*Scan count 5,logical reads 378*/

SELECT * FROM #Accounts WHERE AccountID NOT IN (4,10)
/*Scan count 2,logical reads 295*/

SELECT index_depth,OBJECT_ID('tempdb..#Accounts'),'DETAILED')

返回

index_depth page_count
----------- --------------------
3           358
3           2
3           1

(当聚合索引关键字中添加了唯一数组时,该索引将被替换)

IN仍然以5等式寻求实现,但是这次在每个搜索上读取的叶页数大大超过1.叶页面排列在链表中,sql Server进行导航,直到遇到行不匹配寻求.

NOT IN现在被实现为2范围寻求

[1] Seek Keys[1]: END: #Accounts.AccountID < Scalar Operator((4)),[2] Seek Keys[1]: START: #Accounts.AccountID > Scalar Operator((4))

用剩余谓词

WHERE  ( #Accounts.AccountID < 6 
          OR #Accounts.AccountID > 6 ) 
       AND ( #Accounts.AccountID < 7 
              OR #Accounts.AccountID > 7 ) 
       AND ( #Accounts.AccountID < 9 
              OR #Accounts.AccountID > 9 ) 
       AND ( #Accounts.AccountID < 10 
              OR #Accounts.AccountID > 10 )

所以可以看出,即使在这种极端情况下,最好的sql Server可以做的只是忽略叶子页面中只有一个NOT IN值.有些令人惊讶的是,即使我偏离了分发,所以AccountID = 7记录比AccountID = 4的6个记录都更为普遍,它仍然给出了相同的计划,并且没有重写它,因为范围寻求7的任一侧,类似地减少AccountID = 4记录到1,计划恢复为聚簇索引扫描,因此似乎仅限于针对索引中第一个值考虑此转换.

加成

在我的答案的上半部分,数字与我的描述和索引深度完全相同.

在第二部分中,我的答案并没有解释为什么一个索引3级和358页的页面应该会导致相当准确的逻辑读数,这是非常好的原因,我不太确定自己！不过我现在已经填补了一大堆知识.

首先这个查询(仅sql Server 2008语法)

SELECT AccountID,COUNT(DISTINCT P.page_id) AS NumPages
FROM #Accounts
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) P
GROUP BY AccountID
ORDER BY AccountID

给出这些结果

AccountID   NumPages
----------- -----------
4           72
6           72
7           73
9           72
10          73

添加NumPages总共有362个,反映出一些叶子页面包含2个不同的AccountId值.这些页面将被寻求访问两次.

SELECT COUNT(DISTINCT P.page_id) AS NumPages
FROM #Accounts
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) P
WHERE AccountID <> 4

给

NumPages
-----------
287

所以,

对于IN版本：

寻求= 4次访问1个根页面,1个中间页面和72页(74页)

寻求= 6次访问1个根页面,1个中间页面和72页(74页)

寻求= 7次访问1个根页面,1个中间页面和73页(75页)

寻求= 9次访问1个根页面,1个中间页面和72页(74页)

寻求= 10次访问1个根页面,1个中间页面和73页(75页)

总数：(372)(与统计资料中的378份相比)

而对于NOT IN版本：

寻求< 4访问1根页面,1中间页和1叶页(3) 寻求> 4次访问1个根页面,1个中间页面和287页页面(289) 总计：(292)(对比在295个统计数据中报告的295个) 那么IO的下落不明？ 事实证明这些与预读机制有关.可以(在开发实例上)使用跟踪标志来禁用此机制,并验证逻辑读取是否按上述描述按预期方式进行报告. This is discussed further in the comments to this blog post.