[SQL Server] combined index tips

Combined index tips

1, Define

An index is based on one or more columns and specifies two or more column names. You can create a composite index for the composite values of a specified column. In general, a composite index also overwrites the index (coveringIndex). Generally, composite indexes (excluding covering indexes) should be avoided as much as possible, because composite indexes occupy more space and require more Io and CPU time for reading, thus affecting performance.

The main purpose of using composite indexes is to enhance the selectivity of indexes. The ultimate goal is to design databases instead of creating composite indexes.

If you do need to create a composite index, you should make the index lightweight as much as possible, because this will reduce the index size, reduce Io read/write, and improve performance.

2. When to use

Composite indexes are most useful only when the where condition in the query statement uses the leftmost column of the composite index. For example, a group is created for city and state.
If the where condition of the query statement is where city='Xiamen', The composite index will be used, but where State ='China'This index will not be used.

However, even if the where condition does not specify the first column of the composite index, if the composite index contains the columns involved in the query statement, the query is optimized.
This composite index will also be used because it is a Covering Index)

3. Column order
When a composite index is created, the column order is critical. We recommend that you place highly selective columns on the leftmost end of the composite index. If you select
If the selected column is placed on the leftmost end, this index may be unavailable. The so-called high selectivity generally means that the non-repeated value reaches 95%

4. Combined index and Single Column Index

Sometimes, it is a good way to divide a composite index into a single index, because for composite indexes, only the first column has statistical information, because
If the column is not highly selective, the query optimizer will not use the index. If it is divided into a single column index, each column has a statistical letter.
The query optimizer has more options to generate better execution plans.

However, this does not mean that multiple single-column indexes are better than the combined indexes. The results can be obtained only through testing.

5. Notes

The first column of the composite index needs to be highly selective. However, when multiple primary keys or unique constraints are created
The composite index may not be very selective at this time. You need to manually modify it to put the highly selective column first.
Bit.