·天新网首页·加入收藏·设为首页·网站导航
数码笔记本手机摄像机相机MP3MP4GPS
硬件台式机网络服务器主板CPU硬盘显卡
办公投影打印传真
家电电视影院空调
游戏网游单机动漫
汽车新车购车试驾
下载驱动源码
学院开发设计
考试公务员高考考研
业界互联网通信探索
SQL SERVER中Key Hash Value的作用(上)
http://www.21tx.com 2014年01月17日 cnblogs 桦仔

1 2 3 下一页

测试环境:SQLSERVER2005 开发者版

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

问题是这样的:

SQL SERVER中Key Hash Value的作用(上)

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 次。

聚集索引表的情况

上一篇: 再谈SQL Server中日志的的作用
下一篇: SQL Server CheckPoint的几个误区

1 2 3 下一页

关于我们 | 联系我们 | 加入我们 | 广告服务 | 投诉意见 | 网站导航
Copyright © 2000-2011 21tx.com, All Rights Reserved.
晨新科技 版权所有 Created by TXSite.net