SQL Server Tour-the third stop for those who have been on the back of a multi-year clustered index

The original: SQL Server Tour--the third stop for those who have been on the back of a multi-year clustered index

When it comes to clustered indexes, I think every yard knows, but there are a lot of rustic programmers like me that can only use rote memorization to solve this problem, what table can only build a clustered index,

Then it pulls in a directory search to help readers remember .... The problem is here, we're not studying liberal arts, we don't need to memorize, we need to be able to see in the eyes

Real things ..... We all like the clustered index because it can make the unordered heap table records orderly and play the B-tree ... This reduces the complexity from N to logmn ...

So the logical reading, the physical reading down.

A: Phenomenon

1: Non-indexed case

Or the usual, look at an example to feel, first I have a product table, there is no index, such as:

From this, I saw the tragedy, the physical reading is 9 times, also explained that walked 9 times the hard disk, you can also think that the purpose of the drive is to take data, logic read 1636 times, to pay attention to is here

"Times" is the meaning of "page", that is, in memory walk 1636 data pages, I use DBCC IND to show you, there is not 1636 table data page.

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

2: The case of a clustered index

Here I build a product_idx_productid clustered index in the product table, and then look at the IO situation again, such as:

When you see this "logical reading" for 3 times, you are not already crazy ... Find the target data in up to 1636 data pages, just 3 times .... This is not a God in Literacy's eyes.

The fairy is down to earth??? Of course, this thing is in the sky, and the earth has ... Since there is, there should be a very strong desire to explore .... Let's see how this is played ...

Second: the principle of exploration

1: Explore leaf nodes

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

I think you've heard that the clustered index is to sort the indexed column data and put it into the B-tree, and to make you see it, I'm going to set up a 3-ID unordered record.

Insert  intoPersonValues(2,'bbbbb')Insert  intoPersonValues(3,'CCCCC')Insert  intoPersonValues(1,'AAAAA')

Then I use the DBCC IND command to see which data page the next 3 records are in,

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

DBCC traceon (3604)DBCC page (Ctrip,1,148,1)

From there, 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.

Create Clustered Index  on Person (ID)

But here is an interesting question, my number 148th "table Data page" where??? is also a wonderful enough, in exchange for the 173th index page, that in order to ensure data integrity, it should be

The contents of the 148th data page are poured into the 173 index page, right???? It's okay, check it out.

1 DBCC traceon (3604)2DBCC page (Ctrip,1,173,1 )

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

page field values, such as id,name information, take the following slot0 slots for example:

So far, I think you have a general understanding of the content of the leaf node, at least not to let you memorize it ~ ~ ~

2: Explore Branch nodes

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

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

Data page content of a node I've said it, and now I'm curious what's in the branch node??? I'm so excited, I'm going to export the index data page 120th ...

1 DBCC traceon (3604)2DBCC page (Ctrip,1,1 )

A simple analysis of the contents of the slot0:06000000ad0000 xx0100

00000000: The minimum key value in the Leaf index page (a bit special here, except that a row of records is not the minimum value, the rest is the same), and the conversion to decimal is 0.

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

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

However, through the analysis, we see that, in fact, there are two values in the branch node, a childpage Minkey, a childpage PageID, similarly, the other slots are the same.

Let's change the parameter command to make the results more intuitive, and the record is to keep "PageID" and "Minkey".

In this case, I have a picture in my mind, I do not know if you have now????

The above analysis, in addition to the first row of records not to save the value of the smallest key in the Sub-index page, the other records are extracted from the sub-index page of the smallest index key value, it is important to note ...

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

When you see this, do not know if you already understand, why the table can only have a clustered index it??? Well, the mess of the chicken pulled a lot, I hope you have some help.

SQL Server Tour-the third stop for those who have been on the back of a multi-year clustered index