邀朋友种豆,一起分享吧
喜欢购买正品行货?那就去品牌街

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

来自:种豆 时间:2018-01-01 阅读:382次 原文链接

测试环境:SQLSERVER2005 开发者版

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

问题是这样的:

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

既然通过哈希码来匹配,那么就需要一个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 次。

聚集索引表的情况

非聚集索引表的情况

今天中午跟高文佳兄讨论了很长时间,我把关键讨论部分贴出来,大家参考参考,讨论的最后结果是:还没有解释到keyhashvalue字段实际的作用

感谢高文佳,头脑非常灵活

ō笑东风ō 9:27:10

对了 你那个hash的问题
ō笑东风ō 9:27:21

感觉你研究的方向不对

ō笑东风ō 9:28:05

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

桦少 9:28:53

请指教

ō笑东风ō 9:29:00

查找时不会使用hash来查找 因为hash值没有排序 无法最快查找

桦少 13:08:47

如果是这样

桦少 13:08:59

那么,keyhashvalue就没有意义了

ō笑东风ō 13:09:06

不是二叉树 是B树

桦少 13:09:15  

桦少 13:09:23

b树 桦少 13:09:51

所以我从hash bucket的角度去思考

ō笑东风ō 13:10:22

hash桶这个概念是为了HASH JOIN才产生的

桦少 13:10:36

如果用b树,从第一个最左边的叶子节点开始从磁盘读取索引页面,组装一棵B树

ō笑东风ō 13:11:05

继续

桦少 13:11:23

如果是这样,keyhashvalue这个字段根本不需要

桦少 13:12:12

用到key

桦少 13:14:49  

桦少 13:15:58

我以前做实验的时候也看到过keyhashvalue全部为null

桦少 13:16:47

想写在SQLSERVER聚集索引与非聚集索引的再次研究(上)文章的最后面的

桦少 13:16:58

但是因为解释不了这个现象

桦少 13:17:01

最后没有写

桦少 13:19:42

为什麽我提出这个想法

桦少 13:19:52

其实我也是从性能和速度考虑的

ō笑东风ō 13:20:09

骚等

桦少 13:20:21

我的想法是:sqlserver有可能不用你刚才说的B树来找记录

ō笑东风ō 13:20:31

我怀疑这个HASHvalus是为了在seek时做比较用的

桦少 13:20:45

我画图给你看

桦少 13:22:42

当我用聚集索引查找的时候

桦少 13:23:11

key的字段是id

桦少 13:23:25

表中的字段是id

桦少 13:23:32

id是聚集索引字段

桦少 13:23:50

value是数据页面号

桦少 13:24:12

我要找id为9的那条记录

桦少 13:24:58

等一下

桦少 13:25:02

图还没画好

桦少 13:26:27  

桦少 13:26:51

我需要将索引页69,88,102读取到内存

桦少 13:26:57

构成一棵b树

桦少 13:27:13

从左到右,从上到下查找

桦少 13:27:30

直至找到key为9那条记录

桦少 13:27:59

如果我select的是id为3的那条记录

桦少 13:28:17

我就不用读取索引页88,102读取到内存

桦少 13:28:23

只需要读取索引页面69

桦少 13:30:29

改一下,数据页面编号没有英文字母的

桦少 13:30:30  

桦少 13:30:37

睡醒再聊

桦少 14:05:59

当我找id为9的记录的时候

桦少 14:06:16

我需要扫描索引页面69和索引页面88

ō笑东风ō 14:06:28

不需要扫面69

ō笑东风ō 14:06:42

只需要扫描88和102

桦少 14:06:43

说错了

桦少 14:06:54

是的

桦少 14:07:15

但是你也需要从磁盘读取索引页面69吧

桦少 14:07:22

组装出一棵b树

桦少 14:08:56

逐行逐行扫描 索引页面88和102里的记录
桦少 14:09:08

直到扫描到id为9的那条记录才停止

桦少 14:09:14

我的想法是

桦少 14:09:51

我的想法是:sqlserver有可能不用你刚才说的B树来找记录
ō笑东风ō 14:09:53

页面内扫描是这样

桦少 14:10:59

将所有索引页面的key列和value列放进去hash桶

桦少 14:11:07  

ō笑东风ō 14:11:31

我刚在我本地跑了下你的脚本 SQL SERVER 2008 SP2

桦少 14:11:32

通过算法查找到id为9的那一条记录

ō笑东风ō 14:11:38

没有hashkey

ō笑东风ō 14:11:59

你的平台是什么

桦少 14:12:04

这样就不用扫描:索引页面88和102里的记录
桦少 14:12:32

这个过程当中,也是需要读取页面69,88,102

桦少 14:12:39  但是他就不用扫描

桦少 14:12:47  sql2005

ō笑东风ō 14:13:14

我到时有一种猜测

桦少 14:13:57

不然无办法解释keyhashvalue这个字段

ō笑东风ō 14:14:07

当比如较大字符串的时候 如果将字符串先hash后比较hash值 如果hash值相同 在比较字符串 这样效率会高一些

桦少 14:16:54

这种方法有一个缺点

ō笑东风ō 14:17:17

什么缺点

桦少 14:18:10

如果我select的是id为3的那条记录 他都会把所有索引页面读取到内存
桦少 14:18:20

而不像B树

桦少 14:18:36  

桦少 14:18:43

因为他需要在桶里面找

ō笑东风ō 14:19:53

如果按照你所想的这样 无法快读定位某一个值的行

ō笑东风ō 14:20:03

必须扫描所有页

ō笑东风ō 14:20:17

除非对hashvalue进行排序

桦少 14:21:01

扫描所有索引页面

桦少 14:21:20

把所有索引页面里的keyhashvalue读取到桶里面

桦少 14:21:22

然后查找

ō笑东风ō 14:26:27

而且到hash桶后 还需要排序 如果不排序 需要全部遍历

桦少 14:27:33

嗯嗯

桦少 14:27:43

所以我的文章标题是:随想

ō笑东风ō 14:29:36

我知道有一种程序设计 是这样做的 就是对大字段做hash 然后对hash作为一列存储 对hash列建立索引

ō笑东风ō 14:30:04

这样做等值查询时能提高查询效率

桦少 14:32:58

高兄你是不是想偏了

桦少 14:33:09

不是只有大字段才有hash

ō笑东风ō 14:33:38

我只是说这是一种设计思路

桦少 14:33:39  

ō笑东风ō 14:34:14

任何数据都可以被hash

桦少 14:34:37

不过这里好像说不过去

ō笑东风ō 14:36:10

而且林兄你看到的这些都是非叶子节点哈

桦少 14:50:22

当然是非叶子节点啦

桦少 14:50:33

叶子节点就是数据页面

ō笑东风ō 14:51:28

这个hashvalue应该跟seek无关

桦少 14:52:39

所以我才说无办法解释嘛

ō笑东风ō 14:59:25

嗯嗯

查看本栏目:database/SQLServer/

 
关于种豆 ┊ 联系我们 ┊ 免责声明 ┊ 发帖须知 ┊ 请提意见 ┊ 站点地图
本站为个人爱好兴趣分享网站,不代表本人观点,如有侵权请联系QQ3033380280进行处理
sowsoy.com 版权所有 Copyright©2010-2021 备案号:蜀ICP备2020025376号-3
Email:sowsoy#hotmail.com