SQL Server compound index learning [reprint]

Summary
What is a single index and what is a composite index? When to create a composite index? This article mainly summarizes some discussions on the Internet.

1. Concepts

A single index refers to the case where the index column is one column, that is, the statement for creating an index is only implemented on one column.

You can create an index on multiple columns. This index is called a composite index ). The method for creating a composite index is exactly the same as that for creating a single index. However, compound indexes require less overhead during database operations and can replace multiple single indexes. When the number of rows in a table is much greater than the number of index keys, this method can significantly speed up the table query.

At the same time, there are two concepts called Narrow indexes and wide indexes. Narrow indexes refer to indexes with 1-2 columns in an index column. Unless otherwise specified, they generally refer to a single index. A wide index is an index with more than two columns.

An important principle for designing indexes is to use narrow indexes without wide indexes, because narrow indexes are often more effective than composite indexes. Having more narrow indexes will give optimization programs more options, which usually helps improve performance.

Ii. Use

Create an index
Create index idx1 on table1 (col1, col2, col3)
Query
Select * from table1 where col1 = A and col2 = B and col3 = C

At this time, the query optimizer does not scan the table, but directly retrieves data from the index because the index contains the data, which is called overwrite query, which is very fast.

Iii. Considerations

1. When is composite index used?
In the where condition, fields are indexed. If multiple fields are used, compound indexes are used. Generally, do not create any indexes for select fields (if you want to query select col1, col2, col3 from mytable, you do not need the above indexes ). Creating an index based on the where condition is an extremely important principle. Note that you do not need to use multiple indexes. Otherwise, the efficiency of table update will be greatly affected, because it takes a lot of time to create indexes when operating the table.

2. For composite indexes, it is best to sort the conditions in the order of search indexes during query, which is the most efficient. For example:
IDX1: create index idx1 on table1 (col2, col3, col5)
Select * from table1 where col2 = A and col3 = B and col5 = D

If it is "select * from table1 where col3 = B and col2 = A and col5 = D"
Or "select * from table1 where col3 = B" will not use the index, or the effect is not obvious

3. Will compound indexes replace a single index?
Many people think that adding any field to the clustered index can increase the query speed. Some people are also confused: If the composite clustered index field is queried separately, will the query speed slow down? With this problem, let's take a look at the following query speed (the result set contains 0.25 million pieces of data): (the date column fariqi is first placed in the starting column of the composite clustered index, and the user name neibuyonghu is placed in the back column)

IDX1: create index idx1 on Tgongwen (fariqi, neibuyonghu)

(1) select gid, fariqi, neibuyonghu, title from Tgongwen
Where fariqi> '2017-5-5'

Search speed: 2513 Ms

(2) select gid, fariqi, neibuyonghu, title from Tgongwen
Where fariqi> '2017-5-5' and neibuyonghu = 'Office'

Search speed: 2516 Ms

(3) select gid, fariqi, neibuyonghu, title from Tgongwen
Where neibuyonghu = 'Office'

Search speed: 60280 Ms

From the above experiment, we can see that the query speed is almost the same if only the starting column of the clustered index is used as the query condition and all columns of the composite clustered index are used at the same time, it is even a little faster than using all the composite index columns (when the number of query result sets is the same). If only the non-start column of the composite index is used as the query condition, this index does not have any effect. Of course, the query speed of statements 1 and 2 is the same because the number of queried items is the same. If all columns of the composite index are used and the query results are few, the index Overwrite will be formed ", therefore, the performance can be optimal. At the same time, please remember: No matter whether you frequently use other columns of the aggregate index, but its leading column must be the most frequently used column.

[Reference: Query Optimization and paging algorithm scheme http://blog.csdn.net/chiefsailor/archive/2007/05/28/1628339.aspx]

4. Do I need to create a single index and a composite index on the same column?
Test: Table 1 field of sysbase 5.0: col1, col2, col3

Test procedure:
(1) Create an index idx1 on col1
Run select * from table1 where col1 = A using idx1
Execute select * from table1 where col1 = A and col2 = B and also use idx1

(2) Delete the index idx1, and then create the idx2 on (col1, col2) Composite Index
Idx2 is also used to execute the preceding two queries.

(3) If two indexes, idx1 and idx2, exist
It is not where col1 = 'A' with idx1; where col1 = A and col2 = B with idx2.
Its query optimizer uses one of the frequently used indexes. Either idx1 or idx2.

As you can see,
(1) for a table, if there is a composite index on (col1, col2), there is no need to create a single index on col1 at the same time.
(2) If the query condition is required, you can add a composite index on (col1, col2) with a single index on col1, which improves the efficiency.
(3) Creating a composite index with multiple fields (including five or six fields) at the same time has no special benefit. Relatively speaking, creating multiple narrow fields (including only one, or at most two fields) can be indexed to improve efficiency and flexibility.



5. Are overwrite queries required?
Generally, it is better not to adopt a policy that emphasizes full coverage of queries. If all columns in the Select clause are overwritten by a non-clustered index, the optimizer identifies this and provides good performance. However, this usually leads to an excessively wide index and will be overly dependent on the possibility that the optimizer will use this policy. Generally, more narrow indexes are used, which provides better performance for a large number of queries.