Understand clustered indexes in SQL Server,

Understand clustered indexes in SQL Server,

When it comes to clustered indexes, I think every programmer can understand it, but there are also many growth programmers like me who can only use rote memorization to solve this problem. What table can only create a clustered index, then we pulled the Directory Search to help readers remember .... The problem is that we are not studying liberal arts. We don't need to memorize it. What we need is to see the real things in our eyes ..... We all like clustered indexes, because they can turn unordered heap table records into orders and play B tree... In this way, the complexity is reduced from N to LogMN...

In this case, the logical read is completed.

I. symptom

1: No Index

Let's look at the example. First, I have a Product table without any indexes, such:

From this, I am miserable to see that the physical reads are nine times, which means 9 hard disks are used. You can also think that the hard disk is used to get data, there are 1636 logical reads. Note that "times" here means "pages", that is, 1636 data pages are taken in the memory, I will use dbcc ind to show you if there are 1636 table data pages.

There are 1637 data pages because the first is the IAM tracking page.

 2: clustered Index

Next I will create a clustered index of product_idx_productid in the Product table, and then look at the io situation again, such:

When you see that this "logical read" is three times, are you crazy... Find the target data in up to 1636 data pages, only 3 times .... This seems to be a god in algorithm blindness.

Xianxiafan ??? Of course, there are things in the sky, and there are people in the world... Since there is, there should be a very strong desire to explore .... Let's see how it works ......

Ii. Exploring principles

1: Exploring leaf nodes

As I mentioned just now, clustering indexes play with B. Since B is used, there are leaf nodes and branch nodes. The terminology is that if the degree is 0, the leaf node is used, if the degree is greater than 0, it is called a branch node ....

I think you have also heard that clustered index sorts the index column data and puts it into the B-tree. In order to make you see it, I will first create three records with unordered IDs.

Copy codeThe Code is as follows:
Dbcc traceon (3604)
Dbcc page (Ctrip, 1,120, 1)

Then I run the dbcc ind command to check which data page contains the three records,

As you can see, my three records are stored on the 148 data page, and then I export the 148 data page to see what the content is.

Copy codeThe Code is as follows:
Dbcc traceon (3604)
Dbcc page (Ctrip, 1,173, 1)

We can see that the slots in the "data page" point to the actual storage records in the table. Well, I will create a clustered index below, check whether the actual data is actually ordered?

Copy codeThe Code is as follows:
Create clustered index Ctrip_idx_ID on Person (ID)

But here is an interesting question: Where is my "table data page" No. 148 ??? It is amazing enough. In exchange for the index page No. 173, we should add the content on the data page No. 148 to the index page No. 173 to ensure data integrity ???? It doesn't matter. verify it.

Copy codeThe Code is as follows:
Dbcc traceon (3604)
Dbcc page (Ctrip, 1,173, 1)

Is there any intuitive feeling through the above figure? The data is now in the aaaaa, bbbbb, and ccccc modes... Ordered .... At the same time, the index page also saves the 148 data

Take the following slot0 slot for example:

 

So far, I think you have a rough idea about the content of the leaf node. At least it didn't let you remember it ~~~

2: Explore branch nodes

In order to let you see the branch node, I need to add more data to it so that the data can be broken through an index data page, so that the branch node index data page will come out. Let's look at the following example:

As you can see, when I insert 1000 pieces of data, a branch node (index data page No. 120), three leaf nodes (173,121,126), and leaves appear.

I have also said about the data page content of a node. Now I'm curious about what is stored in the "branch node ??? I'm so excited that I want to export the index data page No. 120...

Copy codeThe Code is as follows:
Dbcc traceon (3604)
Dbcc page (Ctrip, 1,120, 1)

Simple Analysis of slot0: 06000000 00ad0000 000100 content

00000000: The minimum key value on the leaf index page (this is a bit special, except for a row of records that do not save the minimum value, the rest are all). The value is 0 in decimal format.

Ad000000: the page number of the leaf index page. The value is 173 in decimal format.

0100: the file number on the leaf index page, which is 1 in decimal format.

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

Let's change the parameter command to make the result more straightforward. The record stores "pageID" and "minKey".

 

In this way, I have a picture in my mind. I don't know if you have it now ????

Through the above analysis, except that the first record is not the minimum key value saved on the sub-index page, other records are extracted from the minimum index key value on the sub-index page, pay attention to this...

For the SQL Server team, if the judgment is less than 449, the data page (1: 173) will be directly taken, and the data page () for less than 889 will be available...

When you see this, I wonder if you have understood why the table can only have one clustered index ??? Well, it's a lot of fun and I hope it will help you.