SELECT * FROM dbo.Accounts WHERE AccountID IN (4,6,7,9,10) SELECT * FROM dbo.Accounts WHERE AccountID NOT IN (4,10)
解决方法
首先,假设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页的页面应该会导致相当准确的逻辑读数,这是非常好的原因,我不太确定自己！不过我现在已经填补了一大堆知识.
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版本：
寻求= 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.
