SQL SERVER中Key Hash Value的作用（上）

真的不好意思，我做实验的时候到最后还是没有找到这个问题的答案

问题是这样的：

当通过聚集索引查找和非聚集索引查找的时候，通过哈希码来匹配，然后找到相应记录的

既然通过哈希码来匹配，那么就需要一个hash bucket把所有索引页面的所有key/value全部加载到hash bucket

既然要全部加载到hash bucket就需要读取所有的索引页

我的测试脚本，我使用SET STATISTICS IO ON来测试是否有读取索引页的情况，但是到最后还是找不到规律

--sql在聚集索引下如何找到哈希值的随想   
       
USE master   
GO   
--新建数据库IAMDB   
CREATE DATABASE SCANDB   
GO   
       
USE SCANDB   
GO   
       
       
       
--DROP TABLE clusteredtable   
--DROP TABLE nonclusteredtable   
       
       
--建立测试表   
CREATE TABLE clusteredtable(c1 INT IDENTITY(1,1), c2 VARCHAR (900))   
GO   
CREATE TABLE nonclusteredtable(c1 INT IDENTITY(1,1), c2 VARCHAR (900))   
GO   
       
       
--建立索引   
CREATE CLUSTERED INDEX cix_clusteredtable ON clusteredtable([C2])   
GO   
CREATE  INDEX ix_nonclusteredtable ON nonclusteredtable([C2])   
GO   
       
       
--插入测试数据   
DECLARE @a INT;   
SELECT @a = 1;   
WHILE (@a <= 100)   
BEGIN
    INSERT INTO clusteredtable VALUES ( CAST(@a AS NVARCHAR(2))+replicate('a', 880))   
    SELECT @a = @a + 1   
END
       
       
DECLARE @a INT;   
SELECT @a = 1;   
WHILE (@a <= 100)   
BEGIN
    INSERT INTO nonclusteredtable VALUES ( CAST(@a AS NVARCHAR(2))+replicate('a', 880))   
    SELECT @a = @a + 1   
END
       
       
       
       
--查询数据   
SELECT * FROM clusteredtable  ORDER BY [c1] ASC
SELECT * FROM nonclusteredtable  ORDER BY [c1] ASC
       
       
CREATE TABLE DBCCResult (   
PageFID NVARCHAR(200),   
PagePID NVARCHAR(200),   
IAMFID NVARCHAR(200),   
IAMPID NVARCHAR(200),   
ObjectID NVARCHAR(200),   
IndexID NVARCHAR(200),   
PartitionNumber NVARCHAR(200),   
PartitionID NVARCHAR(200),   
iam_chain_type NVARCHAR(200),   
PageType NVARCHAR(200),   
IndexLevel NVARCHAR(200),   
NextPageFID NVARCHAR(200),   
NextPagePID NVARCHAR(200),   
PrevPageFID NVARCHAR(200),   
PrevPagePID NVARCHAR(200)   
)   
       
TRUNCATE TABLE [dbo].[DBCCResult]   
       
INSERT INTO DBCCResult EXEC ('DBCC IND(SCANDB,nonclusteredtable,-1) ')   
       
SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC
       
DBCC TRACEON(3604,-1)   
GO   
DBCC PAGE(SCANDB,1,89,3)    
GO   
       
checkpoint
DBCC DROPCLEANBUFFERS   
DBCC freesystemcache('all')   
GO   
-----------------------------------   
SET STATISTICS IO ON
GO   
--聚集索引查找   
SELECT * FROM clusteredtable WHERE [c2]='18aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
SET STATISTICS IO OFF
GO   
       
       
       
(1 行受影响)   
表 'clusteredtable'。扫描计数 1，逻辑读取 4 次，物理读取 2 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。   
       
       
       
       
----------------------------------------------------------------------------------------   
checkpoint
DBCC DROPCLEANBUFFERS   
DBCC freesystemcache('all')   
GO   
-----------------------------------   
SET STATISTICS IO ON
GO   
--索引查找  、RID查找 、嵌套循环   
SELECT * FROM nonclusteredtable WHERE [c2]='17aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
SET STATISTICS IO OFF
GO   
       
       
       
(1 行受影响)   
表 'nonclusteredtable'。扫描计数 1，逻辑读取 5 次，物理读取 1 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

（编辑：滁州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!