SQL Server big Talk Storage structure (4) _ Composite Index and inclusion index

Index this block from the storage structure, there are 2 categories, clustered index and nonclustered index, and non-clustered index in the heap table or in the clustered Index table will affect its key value, this block can be detailed view of the second article of this series: SQL Server big Talk Storage structure _ (2) _ Nonclustered index how to find Row Records. Nonclustered indexes are divided into multiple categories: single-column index, composite index, include index, filtered index, and so on. The previous article had a specific analysis of the storage of nonclustered indexes, but there was no too much explanation for the composite index and the included index, this article is about the two indexes.

If reproduced, please indicate the source of the blog: www.cnblogs.com/xinysu/, copyright belongs to the blog Garden su Home radish all. Hope you support! 1 Syntax and instructions

--composite Index CREATE index indexname on Tbname (COLUMNA,COLUMNB [, COLUMNC ...]) --Contains the index "CREATE index indexname on Tbname" (Columna [, COLUMNB,COLUMNC ...]) INCLUDE (Column1 [, column2,column3 ...])

Composite indexes, as the name implies, and multiple columns of indexes, the order of the columns is very important, related to query performance, which is explained later.     Include index, indexed SQL contains include fields, index key values are used for where condition filtering, and include fields are used for SELECT displays, which are explained later. Index key values cannot exceed the 900-byte limit, regardless of whether they conform to an index or an index, but be aware that the include field containing the index is not included. 2 Index page storage conditionFrom the storage of the index page analysis, the analysis process, the focus is to view the composite index and contains the index in the child node and leaf nodes of the key value situation. 2.1 Creating a test tableCreate a table Tbindex, set up two test indexes, and build the data at the same time.

1 CREATE TABLE tbindex (2 ID int identity (a) NOT NULL primary key, 3 name varchar (a) NOT NULL, 4 type varchar (TEN) not NULL, 5 numbers int not NULL 6) 7 Go 8 9 CREATE index ix_number_name on Tbindex (numbers,name) Ten go CREATE index Ix_n Ame on Tbindex (numbers) INCLUDE (name) GO DECLARE @ID INT SET @ID =1 and @ID <=5 at BEGIN inser      T into Tbindex (name,type,numbers) SELECT name, type, [email protected] 23 From sys.objects SET @[email protected]+1 END

2.2 Analyzing index rows

--View the ID of the table index SELECT * from sys.indexes WHERE object_id=object_id (' Tbindex ')--pk__tbindex__3213e83f89582ac3 1--ix_nu Mber_name 2--ix_number 3 dbcc TRACEON (3604) DBCC IND (' dbpage ', ' Tbindex ', -1) dbcc page (' Dbpage ', 1,395,3) DBCC page  (' Dbpage ', 1,396, 3) DBCC page (' Dbpage ', 1,397,3) dbcc page (' Dbpage ', 1,398, 3)

Analysis View, learned:

• The index node of compound index ix_number_name is pageid=395, and then a leaf node is selected to analyze pageid=396.
• The index node containing the index ix_number is pageid=397, and a leaf node is selected to analyze the pageid=398.

--composite index, 395 for index page node, 396 for index page leaf node DBCC page (' Dbpage ', 1,395,3) dbcc page (' Dbpage ', 1,396, 3)--contains index, 397 is index page node, 398 for index page leaf node DBCC page (' Dbpage ', 1,397,3) dbcc page (' Dbpage ', 1,398, 3)

as can be seen from here, the composite Index and all index columns containing the index are stored in the index leaf node and the child node, but the include column containing the index is not stored on the index page's child node and is stored only on the leaf node of the index page. It is not difficult to understand why the include column was used in the Select column, rather than the where column to filter. Because nonclustered indexes when the index page has multiple layers, the index's child nodes are queried, and the leaf nodes of the index are queried, and the include column containing the index is not stored in the leaf node and cannot be filtered according to it. 3 impact on queries 3.1 Composite Index query considerationsDue to the need for data volume as an experimental support, so do not have to analyze the index row structure of the table Tbindex, change a tall tb_composite as follows.

1 CREATE TABLE Tb_composite (2 ID int identity () not null primary key, 3 name varchar (a) NOT NULL, 4 userid int not nu LL, 5 timepoint datetime NOT NULL 6) 7 Go 8 9 CREATE index ix_userid_name on Tb_composite (userid,name) GO Ten Crea       Te index Ix_userid on Tb_composite (userid)-GO-to-tb_composite (name,userid,timepoint)-SELECT 17 NEWID (), orderid%10000, createddate from ORDERS

Big Data table

    At this point, the test form is completed, and the index page information is analyzed, and the statistics table Tb_composite information is as follows: 

1--View table data size with nonclustered index size 2 with data as (3 SELECT 4  5       o.name tb_name, 6  &nbs p;    reservedpages = SUM (reserved_page_count), 7       usedpages = SUM ( Used_page_count), 8       pages = SUM (case if (index_id < 2) then (In_row_data_page_c Ount + lob_used_page_count + row_overflow_used_page_count) ELSE 0 END), 9       rowCounts = SUM (case if (index_id < 2) then Row_count ELSE 0 END) sys.dm_db_partition_stats S one JOIN sys.objects o O n s.object_id=o.object_id WHERE o.type= ' U ' GROUP by O.name) SELECT 16            Tb_name,          rowcounts, 19           reservedpages*8/1024 RESERVED_MB, 20           pages*8/1024 DATA_MB, 21          index_mb= (usedpages-pages) *8/1024, 22           unused_mb=case when Usedpages>reservedpages then 0 else (reservedpages-usedpages) *8/1024 End from DATA, WHERE tb_name = ' Tb_composite ' ORDER by RESERVED_MB DESC

--Detailed analysis of index page number per index CREATE TABLE tbind (pagefid int, pagepid int,iamfid int,iampid int,objectid int,indexid int,partitionn umber Int,partitionid varchar (), iam_chain_type varchar, PageType int,indexlevel int,nextpagefid int,nextpagepid       Int,prevpagefid int,prevpagepid int) INSERT into Tbind EXEC (' DBCC IND (' Yaochufa ', ' ' tb_composite ' ', -1) ') SELECT I.name,i.index_id,p.page_numsfrom sys.indexes I join (SELECT indexid,count (*) page_nums from Tbind Group by INDEXID) p on I.index_id=p.indexidwhere object_id=object_id (' Tb_composite ') ORDER by index_id

You can see the total nonclustered index size of this table ≈598mb≈ (43022+33279) *8k/1024≈596mb.    Ix_userid_name significantly more pages than Ix_userid, this is because ix_userid_name more than Ix_userid store the index key value of name, index page increase, meaning that using this index will increase IO accordingly. For example, two sql:set STATISTICS IO on--before execution, press the shortcut key: ctrl+m, executes the SQL will display the actual execution plan (note, ctrl+l, then the estimated execution plan) SELECT * from Tb_composite WI TH (index=ix_userid_name) where UserID =6500select * from Tb_composite with (index=ix_userid) where UserID = 6500 view its IO condition ：

Go composite index will be more than a single-column index 3 more io,userid conditions of expansion This IO difference will gradually increase. View the execution plan as follows:

As you can see, both are indexed by index seek to the corresponding index row, and then based on the primary key on the index row, go to the clustered index to look up the row records in the key lookup. The implementation plan for both is identical. Add one more SQL query here. SELECT * from Tb_composite WHERE name= ' 6cdc4a13-36ff-4fa2-94d0-f1cbea40852c ' name this column, there is no single-column index, exists in the composite index Ix_userid_name    (Userid,timepoint,name), can the query be looked up based on this index?    The answer is: No no, the database will be based on its IO situation to make a choice, there are two possible, one is based on the primary key to do a full table scan, the other is the composite index of the index scan full scan, and then based on the key value to the clustered index to find the corresponding row records. And look at the execution plan and IO as follows, it can be seen that the logical read basically all the data pages (clustered index leaf node) are scanned out, one IO is a 8KB data page.

Come on, sum it up:

1. The leftmost matching principle: The composite index key Value column is assumed to be (a, B, C, D, E), which is equivalent to the index: (a), (A, B), (A, B, C), (A, B, C, D), (A, B, C, D, E)
   1. When the Where condition conforms to the least-left matching principle, then the execution plan is index seek, which goes through the indexing search;
   2. When the Where condition does not conform to the leftmost matching principle, then according to the performance evaluation, go Primary index scan or nonclustered indexes scanning and then primary key lookup according to the key value;
2. Based on the leftmost matching principle, you can avoid adding redundant redundant indexes in daily management.
3. But there is one thing to note: As the columns of the composite index increase, the index pages increase, and the index increases by a certain amount of Io, so it is often rare to consider this when judging redundant indexes.

3.2 The difference between a composite index and a query that contains an index    The previous tests already understand the composite index and the storage structure that contains the index, where the query is tested.    Note Here the number of index pages = Index node page + index leaf node page. Create the data by first creating the table containing the index. CREATE TABLE tb_include (id int identity () NOT NULL primary key,name varchar (a) not Null,userid int not null,timepoint datetime NOT NULL) go to CREATE INDEX Ix_userid on Tb_include (userid) include (timepoint,name) go INSERT into Tb_include (name  , UserID, Timepoint) Select Name,userid,timepoint from Tb_compositego do two queries as follows: Select Userid,name from Tb_composite Where Userid=71select userid,name from Tb_include where userid=71 SELECT userid,name from Tb_composite where userid=71 A ND name= ' 010cc1bd-1736-46a8-9497-7f4dbfd082b2 ' SELECT userid,name from Tb_include where userid=71 and Name= ' 010cc1bd-1736-46a8-9497-7f4dbfd082b2 '

Summary:

1. If the Where condition contains an include column
   1. The Include column cannot participate in index seek because its index child node does not exist and only exists in the index leaf node, so the include column is generally a display column;
   2. Include column cannot be a where filter index seed, which is a relatively large IO
2. If the display column is limited to index key values and include columns
   1. Included in the index, according to the index key value to find the index leaf node, do not need to base on the primary key value or RID value back to the table to query row records, but directly to the index leaf node of the Include column to display the content, reduce back to the table IO;
3. If the Where condition contains only key-value columns, the Select Display column contains only the key-value column-level include columns
   1. The performance is basically the same, including the index relatively small IO, but the difference is not small.
4. The limit length for all nonclustered indexes is 900 bytes, but the include column in the included index is not counted in the index length, so if you encounter a particular case where the index exceeds the bytes, consider putting the relevant field in the Include.

SQL Server big Talk Storage structure (4) _ Composite Index and inclusion index