针对非唯一聚簇索引的搜索的逻辑读取 [英] Logical reads for seeks on a non unique clustered index
问题描述
对于表定义
CREATE TABLE Accounts
(
AccountID INT ,
Filler CHAR(1000)
)
包含21行（每个AccountId值为7行） 4,6,7- ）。
Containing 21 rows (7 for each of the AccountId values 4,6,7).
它有1个根页面和4个叶子页面
It has 1 root page and 4 leaf pages
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
为什么？
我认为每次搜索它会寻找可能包含该值的第一页然后（如果需要）继续沿链表，直到找到第一行不等于搜索值。
I thought for each seek it would seek into the first page that might potentially contain that value and then (if necessary) continue along the linked list until it found the first row not equal to the seeked value.
然而，这最多只能增加10次页面访问次数
However that only adds up to 10 page accesses
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的原因是什么？
So what accounts for the additional 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
推荐答案
只是提供答案形式的答案，而不是评论中的讨论......
Just to supply the answer in answer form rather than as discussion in the comments...
由于预读机制而产生了额外的读数。这将扫描叶级别的父页面，以防它需要发出异步IO以将叶级别页面放入缓冲区高速缓存中，以便在范围搜索到达它们时它们就绪。
The additional reads arise due to the read ahead mechanism. This scans the parent pages of the leaf level in case it needs to issue an asynchronous IO to bring the leaf level pages into the buffer cache so they are ready when the range seek reaches them.
可以使用跟踪标志652来禁用该机制（服务器范围），并验证读取的数量现在正好是预期的10个。
It is possible to use trace flag 652 to disable the mechanism (server wide) and verify that the number of reads is now exactly 10 as expected.
这篇关于针对非唯一聚簇索引的搜索的逻辑读取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
