sql-server - 在非唯一聚集索引上查找的逻辑读取

Question

对于表定义

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

包含 21 行(AccountId 值 4,6,7 每个 7 行)。

它有 1 个根页面和 4 个叶页面

index_depth page_count           index_level
----------- -------------------- -----------
2           4                    0
2           1                    1

根页面看起来像

FileId      PageId      ROW         LEVEL       ChildFieldId ChildPageId AccountId (KEY) UNIQUIFIER (KEY) KeyHashValue
----------- ----------- ----------- ----------- ------------ ----------- --------------- ---------------- ------------------------------
1           121         0           1           1            119         NULL            NULL             NULL
1           121         1           1           1            151         6               0                NULL
1           121         2           1           1            175         6               3                NULL
1           121         3           1           1            215         7               1                NULL

AccountId 记录在这些页面上的实际分布是​​

AccountID   page_id     Num
----------- ----------- -----------
4           119         7
6           151         3
6           175         4
7           175         1
7           215         6

查询

SELECT AccountID 
FROM Accounts 
WHERE AccountID IN (4,6,7) 

提供以下 IO 统计信息

Table 'Accounts'. Scan count 3, logical reads 13

为什么？

我认为对于每次搜索，它都会搜索可能包含该值的第一页，然后(如果需要)continue along the linked list直到发现第一行不等于所查找的值。

但是这最多只能增加 10 个页面访问

4)  Root Page -> Page 119 -> Page 151             (Page 151 Contains a 6 so should stop)
6)  Root Page -> Page 119 -> Page 151 -> Page 175 (Page 175 Contains a 7 so should stop)
7)  Root Page -> Page 175 -> Page 215             (No more pages)      

那么额外的 3 个是由什么造成的呢？

要重现的完整脚本

USE tempdb

SET NOCOUNT ON;

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

CREATE CLUSTERED INDEX ix ON Accounts(AccountID)


INSERT INTO Accounts(AccountID)
SELECT C
FROM (SELECT 4 UNION ALL SELECT 6 UNION ALL SELECT 7) Vals(C)
CROSS JOIN (SELECT TOP (7) 1 FROM master..spt_values) T(X)

DECLARE @AccountID INT

SET STATISTICS IO ON
SELECT @AccountID=AccountID FROM Accounts WHERE AccountID IN (4,6,7) 
SET STATISTICS IO OFF

SELECT index_depth,page_count,index_level
FROM
sys.dm_db_index_physical_stats (2,OBJECT_ID('Accounts'), DEFAULT,DEFAULT, 'DETAILED')

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

DECLARE @index_info  TABLE
(PageFID  VARCHAR(10), 
  PagePID VARCHAR(10),   
  IAMFID   TINYINT, 
  IAMPID  INT, 
  ObjectID  INT,
  IndexID  TINYINT,
  PartitionNumber TINYINT,
  PartitionID BIGINT,
  iam_chain_type  VARCHAR(30),    
  PageType  TINYINT, 
  IndexLevel  TINYINT,
  NextPageFID  TINYINT,
  NextPagePID  INT,
  PrevPageFID  TINYINT,
  PrevPagePID INT, 
  PRIMARY KEY (PageFID, PagePID));
  
INSERT INTO @index_info 
    EXEC ('DBCC IND ( tempdb, Accounts, -1)'  ); 

DECLARE @DynSQL NVARCHAR(MAX) = 'DBCC TRACEON (3604);'
SELECT @DynSQL = @DynSQL + '
DBCC PAGE(tempdb, ' + PageFID + ', ' + PagePID + ', 3); '
FROM @index_info     
WHERE IndexLevel = 1

SET @DynSQL = @DynSQL + '
DBCC TRACEOFF(3604); '

CREATE TABLE #index_l1_info  
(FileId  INT, 
  PageId INT,   
  ROW   INT, 
  LEVEL  INT, 
  ChildFieldId  INT,
  ChildPageId INT,
  [AccountId (KEY)] INT,
  [UNIQUIFIER (KEY)] INT,
  KeyHashValue  VARCHAR(30));
  
INSERT INTO #index_l1_info  
EXEC(@DynSQL)


SELECT *
FROM #index_l1_info

DROP TABLE #index_l1_info
DROP TABLE Accounts

Answer 1

只是以答案的形式提供答案，而不是在评论中进行讨论......

额外的读取是由于预读机制而产生的。这会扫描叶级的父页面，以防需要发出异步 IO 将叶级页面放入缓冲区高速缓存，以便在范围查找到达它们时它们已准备好。

可以使用跟踪标志 652 禁用该机制(服务器范围)并验证读取次数现在是否正好是预期的 10。