Nonclustered indexes in SQL Server detail _mssql

Nonclustered index, this is a very familiar thing, sometimes we for business reasons, SQL write very complex, need to join a lot of tables, and then burst into tears ... At this time there is a DBA or Senior developer to show you this wretched SQL, through the implementation of the plan analysis ... Maybe you can see the wrong table scan ... The source of all evils ... Then give you after the key field plus nonclustered index ... Only to find it quicker than Aston Martin ... So a question comes up, why nonclustered indexes can speed up so fast ... How to do it??? Is it very curious???

Let's unlock the veil of mystery.

One: Phenomenon

Let's start by looking at the ticket of the nonclustered index and see what it looks like in the execution plan ... I have a product table with 8w of data in it, and then I build it on the Name column.

A nonclustered index, just like the following figure:

　　

There are two interesting things to see from the picture above, one is what I want to see "index lookup [nonclustered]", which everyone is familiar with, and this one is going to say, and then we see a "RID lookup", at first glance, what's this dick thing ... What does a nonclustered index have to do with it???

Two: What is a RID

Through the first few, I think we all know how to find the records in the data page? The secret is determined by the offsets in the slot slot, and the problem is, if you go up to the data page level, I just need (Pageid:slotid) to find the record, right? So if I go up the file level, is it just that I need to know (FileID:pageID:slotID) to find the records in the data page? In fact, the RID here is standing in the height of the file through (FileID:pageID:slotID) to find the table records ... Rid=rowid= (FileID:pageID:slotID), if you have to see the truth, in the sq l also really provide such a function (Sys.fn_physlocformatter (%%physloc%%)), we look at the following figure:

Look at the above picture, is not very excited, at a glance, such as productid=18088 this record, it is in the number 1th file, No. 34941 data page, No. 0 slot, productid=18089 is in 1th slot, well, when you know what the RID is a thing, I think you're not far from fully understanding nonclustered indexes ...

Third: Nonclustered indexes

One thing we must know, is the nonclustered index can be accelerated to find, if the table scan like the turtle speed, that also lost the purpose of indexing, since it can be accelerated, because it and clustered index, at the bottom of the play up the B-tree, first we insert some sample data.

DROP TABLE dbo. Person

Create TABLE person (ID INT identity,name CHAR (900))
CREATE INDEX idx_person_name on dbo. Person (Name)

DECLARE @ch as int=97 while

@ch <=122
BEGIN
  inserts into dbo. Person VALUES (REPLICATE (CHAR (@ch), 5))
  SET @ch = @ch +1
End

The above SQL, I deliberately set the name column to 900 char, which is also the upper limit of the index, so that I can dbcc to export a lot of data pages and index pages.

You can see, when I DBCC IND, found that there are already 4 data pages, 5 index pages in the person table, where 151th data pages are table tracking pages and 174th are index tracking pages, which means that when I build the index, the engine assigns us a special index page to store the name index we built. , the next step is for us to look at what is stored in these indexes, which I am very concerned about, and then I will export the index page 173th.

Copy Code code as follows:

DBCC PAGE (ctrip,1,173,1)

Slot 0, Offset 0x60, Length 912, Dumpstyle BYTE record Type = Index_record record Attributes = Null_bitmap record Size = 912 Memory Dump @0x000000000ef1c060 0000000000000000:16,616,161 61612020 20202020 20202020†.aaaaa 0000000         
000000010:20,202,020 20202020 20202020 20202020†0000000000000020:20202020 20202020 20202020 20202020† 0000000000000030:20,202,020 20202020 20202020 20202020†0000000000000040:20202020 20202020 20202020 2020202 0†0000000000000050:20202020 20202020 20202020 20202020†0000000000000060:20202020 20202020 202020 20 20202020†0000000000000070:20202020 20202020 20202020 20202020†0000000000000080:20202020 20202 020 20202020 20202020†0000000000000090:20202020 20202020 20202020 20202020†00000000000000a0:2020 2020 20202020 20202020 20202020†00000000000000b0:20202020 20202020 20202020 20202020†0000000000000 0c0:20202020 2020202020202020 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†0000000         
000000120:20,202,020 20202020 20202020 20202020†0000000000000130:20202020 20202020 20202020 20202020† 0000000000000140:20,202,020 20202020 20202020 20202020†0000000000000150:20202020 20202020 20202020 2020202 0†0000000000000160:20202020 20202020 20202020 20202020†0000000000000170:20202020 20202020 202020 20 20202020†0000000000000180:20202020 20202020 20202020 20202020†0000000000000190:20202020 20202 020 20202020 20202020†00000000000001a0:20202020 20202020 20202020 20202020†00000000000001b0:2020 2020 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†0000000         
000000230:20,202,020 20202020 20202020 20202020†0000000000000240:20202020 20202020 20202020 20202020† 0000000000000250:20,202,020 20202020 20202020 20202020†0000000000000260:20202020 20202020 20202020 2020202 0†0000000000000270:20202020 20202020 20202020 20202020†0000000000000280:20202020 20202020 202020 20 20202020†0000000000000290:20202020 20202020 20202020 20202020†00000000000002a0:20202020 20202 020 20202020 20202020†        
00000000000002b0:20202020 20202020 20202020 20202020†00000000000002c0:20202020 20202020 20202020 2 0202020†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†0000000         
000000340:20,202,020 20202020 20202020 20202020†0000000000000350:20202020 20202020 20202020 20202020† 0000000000000360:20,202,020 20202020 20202020 20202020†0000000000000370:20202020 20202020 20202020 2020202 0†0000000000000380:20202020 20940000 00010000 00020000† .....

Copy Code code 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)-(0x60)

At least three interesting phenomena can be found from above:

The contents of the slot0 and slot1 slots in the <1>:173 index page are ordered, such as: aaaaa,bbbbb .... So .... The original nonclustered index is also orderly ah ...

<2>:6161616161 is the AAAAA of the 16 system.

9400000001000000: These numbers are very important, because it is a 16 binary representation, so the 2-bit 16 binary represents a byte, so you can explain that the preceding 4 bytes represent

PageID, the middle 2 bytes represent Fileid, the following 2 bytes represent slot, see here you are not thinking of RID ... Because the RID is the combination of these three ... Original non-aggregate cable

The record of the citation is "Key+rowid" ....

<3>: Through the last slot list, you can tell that there are 8 index records on the 173th index page.

Well, after reading the leaf node, we look at the branch node, that is, the Indexlevel=1 index data page, that is, number 78th. OK,DBCC look at it.

When you see this list, you don't know if there's a picture in your head, just like the one you see on a clustered index, because its structure is very much like a clustered index, except that there's a rid of the nonclustered index. Finally, I will also contribute to the picture.

To sum up: when taking a nonclustered index, such as your condition is where name= ' JJJJJ ', its logic is this, according to the 78th Index data page key range, and then through the ROWID walk to the 79th Index data page, Then in the 79th Index data page successfully found the JJJJJ, this time you can take out the JJJJJ rowid to table data pages directly locate records, the final output ... This is where the beginning of the blog is, why the RIDs are found ...