Preliminary study on index (III.)

As the previous article was a little hurried, many places are not very simple, and this piece describes some concepts and details.

First, we all know that the storage in the database is divided into two kinds of structure, one is heap, the other is B-tree. The data for the heap is not sorted and there is no structure, and we can simply understand that table data without indexes is in the form of heaps. In contrast, the indexes are stored in the form of B-trees, so that the data in the index is ordered in order.

Second, the structure of the index is also discussed, we are here to compare the structure of the nonclustered index of the clustered index.

(The structure of the officially provided nonclustered index):

It is not difficult to see that the above-mentioned non-clustered index is marked with a clustered index or heap. Analyzed by this graph:

1. A nonclustered index is a B-tree structure just like a clustered index.

2. The leaf node of a nonclustered index is not a data page, so that the leaf nodes of a nonclustered index contain only key values and locators (locators, there are two possible, if the table has a clustered index then the locator is a direct pointer to the data row of the physical pointer, if there is a clustered index, then a pointer to the index of the clustered key)

3. Unlike a clustered index, the nonclustered index itself does not alter the storage mode of the data page.

Focus of this article: nonclustered indexes

The nonclustered index contains the index key columns, including columns and bookmarks. The value of the bookmark depends on whether the table is a heap or a clustered index can be either RID or a clustered index key, let's look at a glance with two graphs.

The actual structure of the displayed nonclustered index on the pair can be found in addition to the index key value, that is, "RID" is a pointer to the data page.

The structure of a nonclustered index on a clustered index can be found to drop the index key value, which is the clustered index key, which continues to the index to look for data when querying the data.

Then the advantages of nonclustered indexes:

1, because one page in SQL Server is only 8K, page space is limited, so the fewer columns a row contains, the more rows it can save. A nonclustered index typically does not contain all the columns in a table, and it generally contains only a very small number of columns. Therefore, a single page will be able to contain a nonclustered index that is more rows than the table row (all columns).

2. Another benefit of nonclustered indexes is that it has a structure that is independent of the data table, so it can be placed in different filegroups, using different I/O paths, which means that SQL Server can access indexes and tables in parallel, making lookups faster.

3, unlike a clustered index, a table can have multiple nonclustered indexes to increase query coverage and intersection, and so on can improve the query speed.

By describing both the basic structure and the pros and cons of the two indexes, the description of the clustered and nonclustered indexes is a general way of establishing which indexes are based on what others summarize:

Using a clustered index with a nonclustered index
Foreign key columns should be
The primary key column should be
Columns are often sorted by grouping should be
Returning data in a range should not be
A small number of different values should not be
A large number of different values should not be
Columns that are frequently updated should not be
Frequently modifying index columns should not be
One or very few different values should not be

Summarize:

through the introduction and testing of the index, we find that the index can greatly improve the efficiency of the query, but the same corresponding maintenance costs and performance consumption is also very large, which requires us to be reasonable according to the actual situation of design. When a query is passed to the data engine, SQL Server can fetch the data through three paths to satisfy the query.

1. The need to access the table only requires access to the index itself, which must be an index that overrides the columns contained in the request
2. Use index key values to access nonclustered indexes and then use bookmarks to access tables with nonclustered indexes
3. Full table scan to get data

Understanding these basic concepts next we will address how to optimize and rationalize the use of indexes from a practical application.

Preliminary study on index (III.)