SQL Server query performance optimization-covering indexes (1)

Covering indexes is also called covering indexes.
Interpretation 1: select data columns can be obtained only from the index without reading from the data table. In other words, the query column must be overwritten by the index used.
Explanation 2: indexing is an efficient way to find rows. When you can retrieve the index, you can read the desired data, and you do not need to read the row from the data table. If an index contains (or overwrites) data that meets the fields and conditions in the query statement, it is called overwriting index.
Explanation 3: a form of non-clustered Composite Index, it includes all the columns used by the Select, Join, and Where clauses in the query (that is, the index creation field exactly overwrites the data involved in the query statement [select clause] and query condition [Where clause], that is, the index contains all the data being queried ).

 

First of all, starting with clustered index, clustered index is actually a covered index. In most cases, it can intuitively tell whether the current clustered index of a data table is useful, the clustered index controls the order of data rows based on key-value fields. Because SQL SERVER sorts data rows by the key-value fields of clustered indexes, when you need to sort certain fields, use these fields as the key-value of clustered indexes, creating a clustered index will greatly improve the query performance. Because the data has been sorted in the order of the key value fields of the clustered index, no additional sorting operation is required for query execution. At the same time, if the clustered index is used to search for the data of other fields in the same record, SQL SERVER does not need to search data through pointers, because the entire data is found when the clustered index finds the index key value.

Clustered indexes are also useful when retrieving data that matches a certain range. For example, if you want to find all the orders with the sales order number between 18000-19999 and the clustered index is created through the sales order number field, close records will all be placed together, the access page is of course relatively small. You can quickly locate the row containing the starting sales order number through the clustered index, and then retrieve all the consecutive rows in the table until the last sales order number is retrieved.

Clustered indexes are also useful for retrieving data rows that account for a large proportion of the total number of rows.

Is to use clustered index to search for data.

 

The benefit of using clustered indexes is:

1) the required data is on the slave layer (that is, the data page). After finding the correct index key value, you do not need to use the pointer for additional search.

2) SQL SERVER puts data that meets the same conditions together

 

Second, non-clustered indexes. The non-clustered index structure is shown in.

 

If you want to make the created non-clustered index have both of the above two benefits, you need to create a non-clustered covered index. By overwriting the index, all the data fields to be queried are part of the index key value, which is stored at the base layer of the index. The covered index not only contains the fields you wrote in The WHERE condition, but also all the fields required BY the SELECT statement and the fields in the group by or order by clause.

Example:

Select <field A, B...> from <data table T> where <condition field C>

In SQL SERVER 2000, we use the following method to create a coverage index:

Create index idx on T (C, A, B)

 

When creating a composite index, the field order is very important. We need to put condition field C first in the composite index and use it as the main sorting object in the upper-layer structure of the index, only the statistical data is included in the table, so that the non-child layer can find the matching records and then read the required data from the child layer that stores records with other fields.

However, columns A and B also appear on the non-slave layer of the index, unless the WHERE condition is that multiple fields or multiple fields are sorted, otherwise, it is of little use to place the index non-slave layer on other data fields, increasing the index data volume and reducing the index performance.

 

In SQL SERVER 2005, you can use the following methods:

Create index idx on T (C) INCLUDE (A, B ...)

To improve the Index overwriting function and query efficiency, SQL SERVER 2005 provides the INCLUDE parameter in the Create Index statement, add other fields of the data table unrelated to the key-value column to the leaf layer of the non-clustered index to expand the non-clustered index function. However, these fields are not sorted and other additional maintenance actions are not performed. When querying, you can obtain all relevant data only by reading the index structure, without accessing the table or clustered index data, thus reducing disk I/O operations, reduce the resources consumed by reading data tables. The SQL SERVER composite index can have up to 16 fields, and the related fields added to the index leaf layer are not included in these 16 fields.

In addition, when the query optimization program finds the data required for processing the query in this index, although the first field of the composite index is not in the WHERE condition, however, the query optimization program may still adopt the applicable composite index. Alternatively, if the query statement does not have the WHERE condition, but the composite index overwrites all required fields, you can directly scan the base layer of the index to obtain the data instead of scanning the data table to find the data.

Example:

Create index Statement on [WBK_PDE_LIST_ORG_HISTROY] ([WBOOK_NO]) include ([QTY_1], [COP_G_NO], [G_NO], [CODE_T], [UNIT_1], [TRADE_TOTAL], [GROSS_WT])

The index idx_WBK_PDE_LIST_ORG_HISTROY structure contains the upper-layer structure of the sorting object in the order of WBOOK_NO key values, and contains all the WBOOK_NO, [QTY_1], [COP_G_NO], [G_NO], [CODE_T], [UNIT_1], [TRADE_TOTAL], and [GROSS_WT] fields. For example.

 

Next I will use an example to explain the situation. Let's take a look at how the following SQL statement is executed in the SQL Execution engine:

SELECT G_NO, UNIT_1 FROM [WBK_PDE_LIST_ORG_HISTROY] WHERE [COP_G_NO] = 'bennet'

 

1) The [WBK_PDE_LIST_ORG_HISTROY] table has a non-clustered index on the [COP_G_NO] column. Therefore, it finds [COP_G_NO] = 'bennet' records in the root node of the non-clustered index. Medium 1)

2) Find the leaf node number pointing to the record from the index center node that contains the [COP_G_NO] = 'bennet' record. Medium 2)

3) obtain the bookmarks (consisting of the database physical file number, the corresponding Page number, and the corresponding row number) for each row of data (assuming there are 100 rows) from the base layer of the index ), the SQL Server engine uses bookmarks to find the locations of real rows on the corresponding page from clustered indexes or data tables. Medium 3)

4) the SQL Server engine searches for values in the G_NO and UNIT_1 columns from the corresponding row.

In the above step, all data for [COP_G_NO] = 'bennet' (100 records ), the SQL Server engine searches for 100 clustered indexes or data tables to retrieve other columns (G_NO, UNIT_1) specified in the query ).

If the non-clustered index page contains the values of the Data columns (COP_G_NO, G_NO, UNIT_1) required in the query statement, the SQL Server engine may not execute the preceding steps 3rd and 4, the speed of directly searching for [COP_G_NO] columns from non-clustered indexes is faster, and the values of these three columns are directly read from the index's leaf layer.