Understanding clustered indexes in SQL Server _mssql

When it comes to clustered indexes, I think every code farmer knows, but there are a lot of rustic programmers like me who can only use rote to solve this problem, what table can only build a clustered index, and then pull to the directory to help readers remember ... The problem is here, we are not learning the arts,, do not need to memorize,,, we need to be able to see in the eyes of the real things ... We all like the clustered index, because it can turn the disorderly heap table record into order, and play the B-tree ... This reduces the complexity from N to logmn ...

So the logic reads and the physics reads down.

One: Phenomenon

1: No index condition

Or the old rules, look at an example to feel, first I have a product table, which does not have any index, the following figure:

From the above picture, I have seen the tragedy, physics reading is 9 times, also shows that go 9 times hard drive, you can also think that the purpose of the hard drive is to get the data, logic read 1636 times, to note that the "times" Here is "page" meaning, that is, in memory walked 1636 data pages, I use DBCC IND To show you whether there are 1636 tables of data pages.

Here are 1637 data pages because the first is the IAM trace page.

2: The situation with the clustered index

Below I build a product_idx_productid clustered index in the product table, and then look at Io again, as shown in the following figure:

When you see this "logic reading" for 3 times, you are not already crazy ... Find target data in up to 1636 data pages, just 3 times .... This is not a god in the age of illiteracy.

Fairy descend to the Earth??? Of course,, this thing is in the sky, the earth has ... Since there is, there should be a very strong desire to explore .... Let's see how it's played in here ...

Two: the principle of exploration

1: Explore the leaf node

Just also said, clustered index play is B-tree, since it is B-tree, there are leaf nodes and branch nodes, the professional term is 0 for leaf node, degrees >0 is called Branch node ....

I think you've heard that clustered indexes are sorted by indexed column data and put into B-tree, so to make you see it, I first set up a 3 record with an ID disorder.

Copy Code code 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 3 records are in, as shown in the figure:

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

Copy Code code as follows:

DBCC TRACEON (3604)
DBCC page (ctrip,1,173,1)

From the above illustration, we see that each slot in the data page is pointing to the actual storage record in the table, so let me create a clustered index to see if the actual data is actually in order.

Copy Code code as follows:

Create clustered index ctrip_idx_id on person (ID)

But here's an interesting question, where is my number 148th "table Data page"??? is also a wonderful flower, in exchange for the exact index page 173th, that in order to ensure data integrity, it should be the contents of the 148th data page into the 173 index page inside it??? It doesn't matter, verify it.

Copy Code code as follows:

DBCC TRACEON (3604)
DBCC page (ctrip,1,173,1)

Through the above diagram, there is no intuitive feeling? The data is now a AAAAA,BBBBB,CCCCC mode ... Orderly .... 148th data is also saved in the index page

The field value of the page, such as id,name information, take the following slot0 slot for example:

So far, I think you have a general understanding of the content of leaf node, at least not let you learn by rote ~ ~ ~

2: Explore the Branch node

In order for you to see the branch node, I have to put some more data in, at least let the data burst an index data page, so that the branch node index data page came out, look at the following example:

As you can see from the diagram, when I insert 1000 data, there is already a branch node (Index 120th data page), three leaf nodes (173,121,126), leaves

Data page contents of a node I also said, now I'm curious what is stored in the branch node??? I'm so excited, I'm going to export the 120th Index data page ...

Copy Code code as follows:

DBCC TRACEON (3604)
DBCC page (ctrip,1,120,1)

Simple analysis of the contents of slot0:06000000 00ad0000 000100

00000000: Leaf index page of the minimum key value (here is a bit special, except for a row of records is not to save the minimum value, the rest are), converted to decimal is 0.

ad000000: page number of leaf index page, converted to decimal is 173.

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

But through analysis, we see, in fact, there are two nodes in the branch node, a childpage Minkey, a childpage PageID, the same, the other slot is the same.

We change the parameter command, let the result more intuitive point, record is to save "PageID" and "Minkey".

In this way, I have a picture out of my mind, do not know if you have now????

With the above analysis, except that the first row of records is not the value of saving the smallest key in the Child index page, the other records are extracting the minimum index key value from the Child index page ...

Perhaps for the SQL Server team, as long as the judgment is less than 449 directly to (1:173) data pages, less than 889 of direct to (1:121) data page can be ...

When you see here, do not know if you have understood, why the table can only have a clustered index??? Well, the chicken is a lot of confusion, I hope to help you.