Non-clustered index details in SQL Server,

Non-clustered index details in SQL Server,

Non-clustered index. This is something that everyone is very familiar with. Sometimes, due to business reasons, SQL writing is very complicated. We need to join many tables and then burst into tears... At this time, DBA or senior developers will show you this complicated SQL statement and analyze it through the execution plan... Maybe we can see that we shouldn't have a table scan... The source of evil... Then add a non-clustered index to the key fields... It was found that the speed was faster than that of Aston Martin... So the question is why non-clustered indexes can speed up so quickly... How can we do this ??? Are you very curious ???

Let's unveil this story.

I. symptom

Let's take a look at the real content of non-clustered indexes and see what it looks like in the execution plan... I have a product table, which contains more than data, and then creates

A non-clustered index is like the following:

　　

I have seen two interesting things. One is the index search [nonclustered] that I want to see. This is very familiar to everyone, then we can see a "RID lookup". At first glance, we can see what this is... What is the relationship between non-clustered index and it ???

Ii. What is RID?

Through the previous articles, I think everyone knows how to find records on the data page? The secret is determined by the offset in the slot. The problem arises. If the slot goes up to the data page level, I only need to (pageID: slotID) to find the record, right? If I raise the file level, do I only need to know (fileID: pageID: slotID) to find the records on the data page? In fact, here the RID is standing at the height of the file and finding the table record through (fileID: pageID: slotID... RID = RowID = (fileID: pageID: slotID). If you need to see the actual information, l actually provides such a function (sys. fn_PhysLocFormatter (% physloc %), we can see:

After reading the figure above, is it very excited and clear at a glance? For example, the productID = 18088 record is displayed in file 1, data page 34941, and slot 0, productID = 18089 is on the slot 1. Well, when you know what the RID is, I think you are not far from fully understanding non-clustered indexes...

Iii. Non-clustered Index

One thing we must know is that non-clustered indexes can accelerate the search. If the index speed is as fast as the table scan speed, the index will be lost. Since the index speed can be accelerated, because it is similar to clustered indexes, it plays the B tree at the underlying layer. First, we insert some sample data.

DROP TABLE dbo.PersonCREATE TABLE Person(ID INT IDENTITY,NAME CHAR(900))CREATE INDEX idx_Person_Name ON dbo.Person(Name)DECLARE @ch AS INT=97WHILE @ch<=122BEGIN  INSERT INTO dbo.Person VALUES(REPLICATE(CHAR(@ch),5))  SET @ch=@ch+1END

In the preceding SQL statement, I intentionally set the Name column to 900 char, which is also the upper limit of the index. In this way, DBCC can export many data pages and index pages.

As you can see, when I am using dbcc ind, I found that there are 4 data pages and 5 index pages in the Person table. The data page No. 151 is the table tracking page, 174 is the index tracking page, which means that when I create an index, the engine assigns a special index page to store the Name index we created, the next step is to check what is stored in these indexes, which is of great concern to me. Next I will export the index page No. 173.

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

Slot 0, Offset 0x60, Length 912, DumpStyle BYTERecord Type = INDEX_RECORD      Record Attributes = NULL_BITMAP   Record Size = 912Memory Dump @0x000000000EF1C0600000000000000000:  16616161 61612020 20202020 20202020 †.aaaaa      0000000000000010:  20202020 20202020 20202020 20202020 †         0000000000000020:  20202020 20202020 20202020 20202020 †         0000000000000030:  20202020 20202020 20202020 20202020 †         0000000000000040:  20202020 20202020 20202020 20202020 †         0000000000000050:  20202020 20202020 20202020 20202020 †         0000000000000060:  20202020 20202020 20202020 20202020 †         0000000000000070:  20202020 20202020 20202020 20202020 †         0000000000000080:  20202020 20202020 20202020 20202020 †         0000000000000090:  20202020 20202020 20202020 20202020 †         00000000000000A0:  20202020 20202020 20202020 20202020 †         00000000000000B0:  20202020 20202020 20202020 20202020 †         00000000000000C0:  20202020 20202020 20202020 20202020 †         00000000000000D0:  20202020 20202020 20202020 20202020 †         00000000000000E0:  20202020 20202020 20202020 20202020 †         00000000000000F0:  20202020 20202020 20202020 20202020 †         0000000000000100:  20202020 20202020 20202020 20202020 †         0000000000000110:  20202020 20202020 20202020 20202020 †         0000000000000120:  20202020 20202020 20202020 20202020 †         0000000000000130:  20202020 20202020 20202020 20202020 †         0000000000000140:  20202020 20202020 20202020 20202020 †         0000000000000150:  20202020 20202020 20202020 20202020 †         0000000000000160:  20202020 20202020 20202020 20202020 †         0000000000000170:  20202020 20202020 20202020 20202020 †         0000000000000180:  20202020 20202020 20202020 20202020 †         0000000000000190:  20202020 20202020 20202020 20202020 †         00000000000001A0:  20202020 20202020 20202020 20202020 †         00000000000001B0:  20202020 20202020 20202020 20202020 †         00000000000001C0:  20202020 20202020 20202020 20202020 †         00000000000001D0:  20202020 20202020 20202020 20202020 †         00000000000001E0:  20202020 20202020 20202020 20202020 †         00000000000001F0:  20202020 20202020 20202020 20202020 †         0000000000000200:  20202020 20202020 20202020 20202020 †         0000000000000210:  20202020 20202020 20202020 20202020 †         0000000000000220:  20202020 20202020 20202020 20202020 †         0000000000000230:  20202020 20202020 20202020 20202020 †         0000000000000240:  20202020 20202020 20202020 20202020 †         0000000000000250:  20202020 20202020 20202020 20202020 †         0000000000000260:  20202020 20202020 20202020 20202020 †         0000000000000270:  20202020 20202020 20202020 20202020 †         0000000000000280:  20202020 20202020 20202020 20202020 †         0000000000000290:  20202020 20202020 20202020 20202020 †         00000000000002A0:  20202020 20202020 20202020 20202020 †         00000000000002B0:  20202020 20202020 20202020 20202020 †         00000000000002C0:  20202020 20202020 20202020 20202020 †         00000000000002D0:  20202020 20202020 20202020 20202020 †         00000000000002E0:  20202020 20202020 20202020 20202020 †         00000000000002F0:  20202020 20202020 20202020 20202020 †         0000000000000300:  20202020 20202020 20202020 20202020 †         0000000000000310:  20202020 20202020 20202020 20202020 †         0000000000000320:  20202020 20202020 20202020 20202020 †         0000000000000330:  20202020 20202020 20202020 20202020 †         0000000000000340:  20202020 20202020 20202020 20202020 †         0000000000000350:  20202020 20202020 20202020 20202020 †         0000000000000360:  20202020 20202020 20202020 20202020 †         0000000000000370:  20202020 20202020 20202020 20202020 †         0000000000000380:  20202020 20940000 00010000 00020000 †   ...........

Copy codeThe Code is as follows:
Row-Offset
7 (0x7)-6480 (0x1950)
6 (0x6)-5568 (0x15c0)
5 (0x5)-4656 (0x1230)
4 (0x4)-3744 (0xea0)
3 (0x3)-2832 (0xb10)
2 (0x2)-1920 (0x780)
1 (0x1)-1008 (0x3f0)
0 (0x0)-96 (0x60)
 

At least three interesting phenomena can be found:

<1>: on the index page No. 173, slot0 and slot1 slot point to the recorded content in an orderly manner, for example, aaaaa, bbbbb .... This way .... It turns out that non-clustered indexes are also ordered...

<2>: 6161616161 is the hexadecimal aaaaa.

9400000001000000:These numbers are very important, because they are represented in hexadecimal notation, so two-bit hexadecimal notation represents a byte, so it can be explained that the first four bytes represent

PageID. Two bytes in the middle indicate fileID, and the last two bytes indicate slot. do you think of the RID here... Because RID is the combination of these three types... Original non-clustered Cable

The cited records are stored in "key + RowID ....

<3>: the last slot list shows that eight index records are stored on index page 173.

 

Now, after reading the leaf node, let's look at the branch node, that is, the index data page with IndexLevel = 1, that is, 78. OK, dbcc.

When you see this list, you don't know if there is a picture in your mind, just like the clustered index shown in the previous article, because its structure and clustered index are very similar, except that the non-clustered index has an extra RID... Finally, I will make a contribution to the figure.

 

To sum up, when using a non-clustered index, for example, if your condition is where name = 'jjjjj ', its logic is like this, based on the range of keys on index data page 78, then the rowid is used to go to index data page 79, and then jjjjj is successfully found on index data page 79, at this time, we can take out the rowid of jjjjj to directly locate the record in the table data page, and finally output ..... This is why the RID search appears at the beginning of the blog...