Shulou(Shulou.com)05/31 Report--

This article will explain in detail what the role of clustered index in Sql Server is, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

One: phenomenon

1: case without index

It's the same old rule. Let's take a look at an example. First of all, I have a Product table without any indexes, as shown below:

From the picture above, I tragically saw that the physical read is 9 times, which means that I walked the hard disk nine times. You can also think that the purpose of going to the hard disk is to get the data, which is logically read 1636 times. It should be noted that the "times" here means "page", that is, I have walked 1636 data pages in memory. I will use dbcc ind to show you whether there are 1636 tabular data pages.

The reason there are 1637 data pages here is that the first one is the IAM trace page.

2: in the case of clustered indexes

Now I build a clustered index of product_idx_productid in the Product table, and then take a look at io again, as shown in the following figure:

When you see this "logical reading" for 3 times, are you crazy? no, no, no. It only takes 3 times to find the target data in as many as 1636 data pages. Is this a god in the eyes of algorithmic blindness?

Immortal? Of course, there is this thing in heaven and on earth. Since there is, there should be a very strong desire to explore. Let's see how it works in here.

Second: explore the principle

1: explore leaf nodes

As I just said, the clustered index plays with a B-tree. Since it is a B-tree, there are leaf nodes and branch nodes. The technical term is that the degree 0 is the leaf node, and the degree > 0 is called the branch node.

I think you have also heard that a clustered index sorts the index column data and puts it into the B-tree, so in order to make you believe what you see, I will first create an ID unordered three records.

The copy code is as follows: dbcc traceon (3604) dbcc page (Ctrip,1,120,1)

Then I use the dbcc ind command to see which data page the next three entries are recorded in, as shown in the figure:

As you can see from the picture, my three records are placed in data page 148, and then I export data page 148 to see what the content is.

The copy code is as follows: dbcc traceon (3604) dbcc page (Ctrip,1,173,1)

From the figure above, we can see that the directions of each slot in the "data page" are based on the actual stored records in the table. OK, let me create a clustered index to see if the actual data is really orderly?

The copy code is as follows: create clustered index Ctrip_idx_ID on Person (ID)

But here is an interesting question, where is my 148 "table data page"? It is also strange enough to get the 173 index page, so in order to ensure the integrity of the data, you should put the contents of the 148 data page into the 173 index page, right? It doesn't matter. Check it out.

The copy code is as follows: dbcc traceon (3604) dbcc page (Ctrip,1,173,1)

Through the picture above, do you feel it intuitively? The data is now in aaaaa,bbbbb,ccccc mode. In order. At the same time, 148 data is also saved in the index page.

The field values of the page, such as ID,Name information, take the following slot0 slot as an example:

So far, I think you have a general understanding of the content of the leaf node, at least it doesn't make you memorize.

2: explore branch nodes

In order for you to see the branch node, I have to fill in more data, at least let the data break an index data page, so that the branch node index data page comes out, see the following example:

As you can see from the figure, when I inserted 1000 pieces of data, there was already one branch node (index data page 173121126), three leaf nodes (120), and leaves.

I have also mentioned the content of the data page of the node, and now I am curious about what is stored in the "branch node". I'm so excited. I'm going to export index page 120.

The copy code is as follows: dbcc traceon (3604) dbcc page (Ctrip,1,120,1)

Briefly analyze the contents of slot0:06000000 00ad0000 000100.

00000000: the minimum key value in the leaf index page (a little special here, except for a row of records that do not keep the minimum value), converted to decimal is 0.

Ad000000: the page number of a leaf index page, converted to decimal.

0100: the file number of the leaf index page, converted to decimal is 1.

However, through the analysis, we can see that, in fact, there are two values in the branch node, one minkey of childpage and one pageid of childpage. Similarly, the same is true of other slots.

Let's change the parameter command to make the result more straightforward. "pageID" and "minKey" are stored in the record.

In that case, I have a picture in my mind. I don't know if you have it now.

Through the above analysis, it should be noted that except for the first row record which is not the minimum key value in the sub-index page, all the other records extract the minimum index key value in the sub-index page.

Perhaps for the sqlserver team, as long as it is judged that it is less than 449, it is OK to go directly to the data page (1purl 173) and the data page less than 889. no, no, no.

About what the role of clustered index in Sql Server is shared here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.