SQL Server Cable Introduction order: Level tenth, index internal structure

Original address:

Stairway to SQL Server indexes:level 10,index Internal Structure

This article is part of the SQL Server cable Introduction order series (Stairway to SQL Server Indexes).

In the previous level, we introduced the index from a logical perspective, focusing on what they can do for us. Now, it's time to take a physical view and examine the internal structure of the index, from understanding the internal structure of the index, to guide us through the work of the index on the top. Through the structure of the index, how it is maintained, you can understand when inserting, updating, deleting, minimizing the creation, modification, and movement of the index.

So, from now on, we have to care about index consumption in addition to the benefits of indexing. After all, minimizing consumption can bring maximum benefits and maximizing benefits is the purpose of this series.

Leaves and non-leaf layers

The body of the index is composed of leaves and non-leaf layers. Although there is no obvious explanation, our previous levels were mainly focused on the leaf layer of the index. Therefore, the leaf layer of the clustered index is the table itself, and the entry of each leaf layer is a row in the table. For nonclustered indexes, there is one entry for each row in the leaf layer (except the filtered index), each entry consists of an index key column, an optional include column, and a label, the memory of which is the key column of the clustered index, or the RID (Row ID).

An index entry is also called an index row, whether it is a row of a table (clustered index leaf entry), a reference to a row in a table (nonclustered index leaf layer), or a page that points to a lower level (non-leaf layer).

The non-leaf layer is a structure built on the leaf layer, allowing SQL Server to do the following:

• The entry that maintains the index in the order of the index keys.
• The leaf layer is quickly found based on the given index key value.

In the first level, we used the phone to introduce the benefits of indexing. In the phone book, the person named "Meyer,helen", because the phone book is sorted by last name, so we know that this person should be in the middle position, jump directly to the middle of the phone book to start looking. However, SQL Server does not have this knowledge. It does not know which page is the middle page, unless it is accessed from the beginning of the index to the end. Therefore, SQL Server constructs some additional structure in the index.

Non-leaf layer

These additional structures are called non-leaf layers, also called node layers. is built on the leaf layer, regardless of the physical location of the page. The goal is to give SQL Server a separate page entry point for each index, from one page to another.

Each page in the index, regardless of which layer he is, contains an index row or a portal. In the leaf layer page, each entry point points to a row in the table or to a row in the table. If the table has 1 billion rows of data, the index's leaf layer will have 1 billion entrances.

Next to the upper layer of the leaf layer is the lowest non-leaf layer, and each of his entrances points to a leaf-layer page. If we have 1 billion entrances with an average of 100 entrances per page, the leaf layer will contain 1,000,000,000/100=10,000,000 pages. If the lowest non-leaf layer contains 10,000,000 entrances, each pointing to the leaf layer page, will contain the 10,000,000/100=100,000 page.

The entry in each of the higher non-leaf layers of the page points to the next level of the page. Therefore, the next higher non-leaf layer will have 100,000 entrances, 1000 pages. On the upper level, there will be 1000 entrances, 10 pages, then 10 entrances, 1 pages, this is the top.

The page at the top of the index is called the root page. In the index, below the root page, the layer above the leaf layer is called the middle tier. The number of layers starts at 0 (the leaf layer is 0) and increases upward. Therefore, the middle tier is at least 1.

The entrance to the non-leaf layer contains only the columns of the index key and pointers to the next layer of pages. The included column of an index exists only at the entrance of the leaf layer, and there is no such information in the entrance to the non-leaf layer.

Each page in the index, except the root page, contains two additional pointers. One point to the next page, and one to the previous page. The result of a bidirectional chain of pages is that SQL Server can scan any layer of pages in a forward or reverse direction.

Simple example

Through, you can describe the tree structure of the index. We created an index on the LastName and FirstName columns of the Personnel.employee table.

CREATE nonclustered INDEX   onGO

The pointer to the page contains the number of the page, along with the number of the data file. If a pointer is 5:4,567, it points to page No. 4567 of the # # file.

To clear and understand, the index above and the actual index are somewhat different in the following ways:

The number of entries per page, in the actual index is much more than the above figure, each layer of the page is much more than the graph. In particular, the leaf layer, in practice will be much more than the figure.

In the actual index, the entry on the page is unordered. An offset pointer to the page entry that provides sequential access to the portal. (about offset pointers can be seen in level fourth, pages and sections of the introduction.) ）

Depth of index

The location of the root page and additional information about the index are stored in a system table. When SQL Server needs access to the index entry for the given index key value, it starts from the root page in its own way, accessing each page of each layer until it contains the leaf layer of the index's typing port. In the example of our 1 billion-row table, SQL Server accesses only 5 pages to the required leaf-layer entry, and in the example, only 3 pages are required to be read. In a clustered index, the entrance to the leaf layer is the actual data row, and in a nonclustered index, the ingress may be the key of the clustered index or the RID (Row ID).

The number of layers, also called depth, in the AdventureWorks database, does not have a depth of more than 3 index. If there is a large table in the database, or there are many columns in the index key, the depth may be more than 6 or deeper.

The Sys.dm_db_index_physical_stats function gives us some index information, including the type of index, depth, and size; is a table-valued function that can execute a query. The following example is to view the index information for the SalesOrderDetail table.

SELECT object_name(P.object_id) as 'Table', I.name as 'Index', p.index_id as 'IndexID', P.index_type_desc, p.index_depth, P.page_count fromSys.dm_db_index_physical_stats (db_id(),                                         object_id('Sales.SalesOrderDetail'),                                         NULL,NULL,NULL) PJOINSys.indexes I onI.object_id =P.object_id                       andi.index_id=p.index_id;

The results are shown below.

The following code displays information about the specified index of the table, the nonclustered index of the uniqueidentifier column of the SalesOrderDetail table, and one row in the result is the first level of the index.

SELECT object_name(P.object_id) as 'Table', I.name as 'Index', p.index_id as 'IndexID', P.index_type_desc, P.index_level, P.page_count fromSys.dm_db_index_physical_stats (db_id(),object_id('Sales.SalesOrderDetail'),2,NULL,'detailed') PJOINSys.indexes I onI.object_id =P.object_id                       andi.index_id=p.index_id;

The results are as follows.

From the results, we can see:

• The leaf layer of the index has 408 pages.
• The only middle tier is only 2 pages.
• The root layer is only 1 pages.

The non-leaf layer size is usually one-tenth or 2% of the leaf layer, depending on which columns the query key contains, the size of the label, and whether there are included columns. In other words, the index can be large or small.

Keep in mind that the included columns are only available in the nonclustered index, and they appear only at the entrance of the leaf layer. They are ignored at the top entrance, which is why they do not increase the size of the non-leaf layer.

Because the leaf layer of the clustered index is the data row of the table, there is extra space in the clustered index for additional information in the non-leaf layer. Data rows exist regardless of whether the index is created or not. Therefore, it may take some time to create a clustered index and consume some resources, but only a small amount of data space is required after the creation is complete.

Conclusion

The structure of the index allows SQL Server to quickly access the indexed portals. Once the portal is discovered, SQL Server can:

• Accesses the data row of the portal.
• Forward or reverse access to the index.

The tree structure of the index has been used for a long time, even longer than a relational database, which has proven useful over time.

SQL Server Cable Introduction order: Level tenth, index internal structure