Functions of KeyHashValue in SQLSERVER (I)

The role of KeyHashValue in SQLSERVER (I) the role of KeyHashValue in SQLSERVER (II) the title of the original article is: SQLSERVER how to find the hash value under the index and now I want to know the role of KeyHashValue, so I changed the title ~ Test environment: SQLSERVER2005 Developer Edition is really embarrassing.

The role of KeyHashValue in SQLSERVER (I) the role of KeyHashValue in SQLSERVER (II) the title of the original article is: SQLSERVER how to find the hash value under the index and now I want to know the role of KeyHashValue, so I changed the title ~ Test environment: SQLSERVER2005 Developer Edition is really embarrassing.

Functions of KeyHashValue in SQLSERVER (I)

Role of KeyHashValue in SQLSERVER (below)

The title of the original article is: how to find the hash value under the index of SQLSERVER

Now that we know the role of KeyHashValue, we changed the title ~

Test environment: SQLSERVER2005 Developer Edition

Sorry, I still did not find the answer to this question at the end of my experiment.

The problem is as follows:

When you search for clustered index and non-clustered index, you can use the hash code to match and then find

Since the hash code is used for matching, a hash bucket is required to load all the keys/Values on all index pages to the hash bucket.

To load all the data to a hash bucket, you must read all the index pages.

In my test script, I use SET STATISTICS IO ON to test whether the index page is read, but the rule still cannot be found at the end.

1 -- How to Find the hash value of SQL under the clustered index 2 3 USE master 4 GO 5 -- CREATE DATABASE IAMDB 6 CREATE DATABASE SCANDB 7 GO 8 9 USE SCANDB 10 GO 11 12 13 14 -- drop table clusteredtable 15 -- drop table nonclusteredtable 16 17 18 -- CREATE Test TABLE 19 create table clusteredtable (c1 int identity (1, 1 ), c2 VARCHAR (900) 20 GO 21 create table nonclusteredtable (c1 int identity (900), c2 VARCHAR () 22 GO 23 24 25 -- create index 26 create clustered index c Ix_clusteredtable ON clusteredtable ([C2]) 27 GO 28 create index ix_nonclusteredtable ON nonclusteredtable ([C2]) 29 GO 30 31 32 -- insert test data 33 DECLARE @ a INT; 34 SELECT @ a = 1; 35 WHILE (@ a <= 100) 36 BEGIN 37 insert into clusteredtable VALUES (CAST (@ a as nvarchar (2 )) + replicate ('A', 880) 38 SELECT @ a = @ a + 1 39 END 40 41 42 DECLARE @ a INT; 43 SELECT @ a = 1; 44 WHILE (@ a <= 100) 45 BEGIN 46 INSERT I NTO nonclusteredtable VALUES (CAST (@ a as nvarchar (2) + replicate ('A', 880 )) 47 SELECT @ a = @ a + 1 48 END 49 50 51 52 53 -- query data 54 SELECT * FROM clusteredtable order by [c1] ASC 55 SELECT * FROM nonclusteredtable order by [c1] ASC 56 57 58 create table DBCCResult (59 PageFID NVARCHAR (200 ), 60 PagePID NVARCHAR (200), 61 iamfid nvarchar (200), 62 iampid nvarchar (200), 63 ObjectID NVARCHAR (200), 64 Ind ExID NVARCHAR (200), 65 PartitionNumber NVARCHAR (200), 66 PartitionID NVARCHAR (200), 67 iam_chain_type NVARCHAR (200), 68 PageType NVARCHAR (200 ), 69 IndexLevel NVARCHAR (200), 70 NextPageFID NVARCHAR (200), 71 NextPagePID NVARCHAR (200), 72 PrevPageFID NVARCHAR (200), 73 PrevPagePID NVARCHAR (200) 74) 75 76 truncate table [dbo]. [DBCCResult] 77 78 insert into DBCCResult EXEC ('dbcc IND (SCANDB, nonclustere Dtable,-1) ') 79 80 SELECT * FROM [dbo]. [DBCCResult] order by [PageType] DESC 81 82 dbcc traceon (3604,-1) 83 GO 84 dbcc page (SCANDB, 3) 85 GO 86 87 checkpoint 88 dbcc dropcleanbuffers 89 DBCC freesystemcache ('all ') 90 GO 91 ----------------------------------- 92 set statistics io on 93 GO 94 -- clustered index search 95 SELECT * FROM clusteredtable WHERE [c2] = 'clustered Zookeeper aaaaaaaaaaaaaaaaaaa Zookeeper aaaaaaaaaaaaaaaaaaa Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa '96 set statistics io off 97 GO 98 99 100 101 102 (one row affected) Table 'clusteredtable '. 1 scan count, 4 logical reads, 2 Physical reads, 0 pre-reads, 0 lob logical reads, 0 lob physical reads, and 0 lob pre-reads. 103 104 105 106 107 checkpoints 108 checkpoint 109 DBCC DROPCLEANBUFFERS110 DBCC freesystemcache ('all ') 111 GO112 --------------------------------- 113 set statistics io ON114 GO115 -- index search, RID search, nested loop 116 SELECT * FROM nonclusteredtable WHERE [c2] = 'struct' Zookeeper aaaaaaaaaaaaaaaaaaa Zookeeper aaaaaaaaaaaaaaaaaaa Aaaaaa '2017 set statistics io OFF118 GO119 117 120 121 (1 row affected) 122 table 'nonclusteredtable '. 1 scan count, 5 logical reads, 1 physical read, 0 preread, 0 lob logical reads, 0 lob physical reads, and 0 lob preread.

View Code

Clustered index tables

Non-clustered index tables

At noon today, I had a long discussion with Gao wenjia. I posted the key discussion part for your reference. The final result of the discussion is: the actual role of the keyhashvalue field has not been explained.

Thanks to Gao wenjia for his flexible mind

Xiao Dongfeng Yu 9:27:10

By the way, your hash Problem
Xiao Dongfeng Yu 9:27:21

I feel that your research direction is wrong.

Xiao Dongfeng Yu 9:28:05

When you search for clustered index and non-clustered index, you can use the hash code to match and then find

Hua Shao 9:28:53

Please advise

Xiao Dongfeng Yu 9:29:00

Hash is not used for searching, because hash values are not sorted and cannot be searched as quickly as possible.

Hua Shao 9:29:18

Xiao, Dongfeng Yu, 9:29:20, should be searched by key

Hua Shao 9:33:52
Xiao Dongfeng Yu 9:29:20
Search by key
Hua Shao 9:33:55
Talk about your ideas
Xiao Dongfeng Yu 9:34:18
The index is sorted by KEY, right?
Xiao Dongfeng Yu 9:34:34
Sort by key to find the desired value as soon as possible
Hua Shao 9:35:38
Key sorting controversial
Hua Shao 9:35:42
What about
Hua Shao 9:35:58
You have not made it clear

Xiao Dongfeng Yu 9:37:21
Let's talk about the key search

I remember you mentioned hashjoin in your blog.
Hua Shao 9:39:38
Which three classic connections are not written?
Xiao Dongfeng Yu 9:40:56
In my opinion, the key search is the fastest and no need to use hash to locate it.
Xiao Dongfeng Yu 9:41:26
Hash is used only in hash join.

Xiao Dongfeng Yu 9:40:56
In my opinion, the key search is the fastest and no need to use hash to locate it.
Hash is used only in hash join.

Hua Shao 12:50:35
Are you sure you want?
Xiao Dongfeng Yu 12:51:57
Well, I still think that clustering indexes and non-clustering indexes only have key lookup.
Hua Shao 12:55:46
Key lookup
What is the principle?
Hua Shao 12:55:52
What is the procedure?
Hua Shao 12:55:56
Do you know
Xiao Dongfeng Yu 12:59:31
Is the principle of the Balance Tree.
Hua Shao 13:00:31
Good
Xiao Dongfeng Yu 13:00:38
Only four steps are required to use the Balance Tree to search for millions of INT values.
Hua Shao 13:00:58
Do you need to read the index page from the disk to the memory?
Xiao Dongfeng Yu 13:01:05
Pair
Hua Shao 13:01:06
Not to mention how many steps he uses
Hua Shao 13:01:08
Good performance
Hua Shao 13:01:26
Reads the index page of the entire table from the disk to the memory.
Hua Shao 13:01:29
Entire table
Hua Shao 13:01:41
Then it forms the so-called Balance Tree

Hua Shao 13:01:46

Right

Xiao Dongfeng Yu 13:02:06

Pair

Hua Shao 13:02:52

This is my problem.

Hua Shao 13:03:01

I use statictis io

Hua Shao 13:03:10

It cannot be seen that he will read all the index pages.

Xiao Dongfeng Yu 13:04:25

Of course, one seek won't read all the pages.

Xiao Dongfeng Yu 13:04:48

Only scan can read all pages.

Hua Shao 13:05:36

You still don't understand what I asked

Hua Shao 13:06:18

I'm talking about index pages.

Hua Shao 13:06:26

Not a data page

Xiao Dongfeng Yu 13:06:45

The same is true for indexes.

Xiao Dongfeng Yu 13:06:50

Let me give you a demo.

Hua Shao 13:08:00

Xiaoxiao Dongfeng Yu 13:08:30

I have 245461 data entries in the [BackupTestDB]. [dbo]. [TB1] table.

Hua Shao 13:08:43

Binary Tree

Xiao Dongfeng Yu 13:08:44

Hua Shao 13:08:47

If so

Hua Shao 13:08:59

Then, keyhashvalue is meaningless.

Xiao Dongfeng Yu 13:09:06

Not binary tree or B tree

Hua Shao 13:09:15

Hua Shao 13:09:23

B Shuhua Shao 13:09:51

So I think about it from the hash bucket perspective.

Xiao Dongfeng Yu 13:10:22

The hash bucket concept is generated for hash join.

Hua Shao 13:10:36

If Tree B is used, read the index page from the disk from the first leftmost leaf node and assemble a Tree B.

Xiao Dongfeng Yu 13:11:05

Continue

Hua Shao 13:11:23

If so, the keyhashvalue field is not required at all.

Hua Shao 13:12:12

The concept of bucket can be used for keyalue.

Hua Shao 13:12:19

I think

Hua Shao 13:12:47

I don't think you need to crack books.

Hua Shao 13:13:06

Reading a dead book is equal to reading a dead book.
Xiao Dongfeng Yu 13:14:28

Hua Shao 13:14:49

Hua Shao 13:15:58

I also saw that all keyhashvalues are null when I did my experiments.

Hua Shao 13:16:47

I want to write it at the end of the restudy on SQL Server clustered index and non-clustered index (I ).

Hua Shao 13:16:58

But it cannot be explained.

Hua Shao 13:17:01

Not written at last

Hua Shao 13:19:42

Why did I propose this idea?

Hua Shao 13:19:52

In fact, I also consider performance and speed.

Xiao Dongfeng Yu 13:20:09

Sao

Hua Shao 13:20:21

My idea is: sqlserver may not use the B tree you just mentioned to find records.

Xiao Dongfeng Yu 13:20:31

I suspect this HASHvalus is used for comparison in seek.

Hua Shao 13:20:45

I will show you a picture

Hua Shao 13:22:42

When I use clustered indexes for search

Hua Shao 13:23:11

The key field is id

Hua Shao 13:23:25

The field in the table is id

Hua Shao 13:23:32

Id is the clustered index Field

Hua Shao 13:23:50

Value is the data page number.

Hua Shao 13:24:12

I want to find the record with id 9.

Hua Shao 13:24:58

Wait.

Hua Shao 13:25:02

The image has not been painted.

Hua Shao 13:26:27

Hua Shao 13:26:51

I need to read index pages 102, to the memory

Hua Shao 13:26:57

Construct a B-tree

Hua Shao 13:27:13

Search from left to right, from top to bottom

Hua Shao 13:27:30

Until the record whose key is 9 is found

Hua Shao 13:27:59

If I select the record with id 3

Hua Shao 13:28:17

I don't need to read index page 88,102 To Read Memory

Hua Shao 13:28:23

You only need to read index page 69

Hua Shao 13:30:29

Changed the data page number without English letters.

Hua Shao 13:30:30

Hua Shao 13:30:37

Sleep and chat

Hua Shao 14:05:59

When I look for a record with id 9

Hua Shao 14:06:16

I need to scan index page 69 and index page 88

Xiao Dongfeng Yu 14:06:28

No need to scan 69

Xiao Dongfeng Yu 14:06:42

You only need to scan 88 and 102

Hua Shao 14:06:43

Wrong

Hua Shao 14:06:54

Yes

Hua Shao 14:07:15

But you also need to read the index page 69 from the disk.

Hua Shao 14:07:22

Assemble a B-tree

Hua Shao 14:08:56

Scan records in index Pages 88 and 102 row by row
Hua Shao 14:09:08

It does not stop until the record with id 9 is scanned.

Hua Shao 14:09:14

My idea is:

Hua Shao 14:09:51

My idea is: sqlserver may not use the B tree you just mentioned to find records.
Xiao Dongfeng Yu 14:09:53

In-page scanning is like this

Hua Shao 14:10:59

Put the key and value columns on all index pages into the hash bucket.

Hua Shao 14:11:07

Xiao Dongfeng Yu 14:11:31

I just ran your script SQL SERVER 2008 SP2 locally.

Hua Shao 14:11:32

Search for the record with id 9 using the algorithm

Xiao Dongfeng Yu 14:11:38

No hashkey

Xiao Dongfeng Yu 14:11:59

What is your platform?

Hua Shao 14:12:04

In this way, you don't need to scan: Index records in Pages 88 and 102
Hua Shao 14:12:32

In this process, you also need to read the pages 102

Hua Shao: 14:12:39, but he does not need to scan.

Hua Shao 14:12:47 sql2005

Xiao Dongfeng Yu 14:13:14

I guess

Hua Shao 14:13:57

Otherwise, there is no way to explain the keyhashvalue field.

Xiao Dongfeng Yu 14:14:07

For example, when a large string is used, if the string is first hash and then compared with the hash value, if the hash value is the same as the string, the efficiency will be higher.

Hua Shao 14:16:54

This method has a disadvantage.

Xiao Dongfeng Yu 14:17:17

What are the disadvantages?

Hua Shao 14:18:10

If I select the record with id 3, all index pages will be read to the memory.
Hua Shao 14:18:20

Unlike B-tree

Hua Shao 14:18:36

Hua Shao 14:18:43

Because he needs to find it in the bucket.

Xiao Dongfeng Yu 14:19:53

If you cannot quickly read the rows that locate a value as you want

Xiao Dongfeng Yu 14:20:03

All pages must be scanned

Xiao Dongfeng Yu 14:20:17

Unless hashvalue is sorted

Hua Shao 14:21:01

Scan all index pages

Hua Shao 14:21:20

Read keyhashvalue from all index pages to the bucket

Hua Shao 14:21:22

Find

Xiao Dongfeng Yu 14:26:27

In addition, sorting is also required after the hash bucket. If not sorted, all traversal is required.

Hua Shao 14:27:33

Hmm

Hua Shao 14:27:43

So the title of my article is:

Xiao Dongfeng Yu 14:29:36

I know there is a program designed to do this by performing hash on large fields and then using hash as a column to store indexes on the hash column.

Xiao Dongfeng Yu 14:30:04

This improves query efficiency when performing equivalent queries.

Hua Shao 14:32:58

Do you want to be biased?

Hua Shao 14:33:09

Hash is not available only for large fields.

Xiao Dongfeng Yu 14:33:38

I just said this is a design idea.

Hua Shao 14:33:39

Xiao Dongfeng Yu 14:34:14

Any data can be hashed

Hua Shao 14:34:37

However, it seems that I can't say it.

Xiao Dongfeng Yu 14:36:10

What you see, brother Lin, is not a leaf node.

Hua Shao 14:50:22

Of course, it's not a leaf node.

Hua Shao 14:50:33

The leaf node is the data page.

Xiao Dongfeng Yu 14:51:28

This hashvalue should be irrelevant to seek.

Hua Shao 14:52:39

That's why I cannot explain it.

Xiao Dongfeng Yu 14:59:25

Hmm