SQL Server's composite Indexing learning "reprint"

Overview
What is a single index and what is a composite index? When to create a composite index, what do you need to be aware of in a composite index? This article is mainly a summary of some discussions on the Internet.

I. Concept

A single index is the case that the index is listed as a column, that is, the statement that creates the new index is only implemented on one column.

Users can index on multiple columns, which are called composite indexes (combined indexes). Composite indexes are created in exactly the same way that you create a single index. However, composite indexes require less overhead during database operations and can replace multiple single indexes. Using this method can significantly speed up the query speed of a table when the number of rows in the table is much larger than the number of index keys.

At the same time, there are two concepts called Narrow Index and wide index, narrow index refers to index column 1-2 index, if not special description is generally referred to as a single index. A wide index is an index that has more than 2 columns in the index column.

An important principle of designing indexes is the ability to use narrow indexes without wide indexes, because narrow indexes tend to be more efficient than composite indexes. Having more narrow indexes will give the optimizer more room to choose from, which often helps improve performance.

Two. Use

Create an index
CREATE INDEX idx1 on table1 (col1,col2,col3)
Inquire
SELECT * FROM table1 where col1= A and col2= B and col3 = C

At this time the query optimizer, not scanning the table, but directly from the index to take the data, because the index has this data, which is called the overlay query, such a query is very fast.

Three. Precautions

1. When to use a composite index
In the Where condition, the field is indexed, and the composite index is used if multiple fields are used. Generally do not index the field in select (If you are querying select col1, col2, col3 from MyTable, you do not need the index above). Indexing based on where conditions is a very important principle. Be careful not to use the index too much, otherwise it will have a significant effect on the efficiency of table updates because it takes a lot of time to create an index when you manipulate the table.

2. For composite indexes, it is best to use the order of the criteria in the index when the query is used, which is the most efficient. Such as:
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. Can a composite index replace a single index?
Many people think that as long as you add any field to the clustered index, you can improve the query speed, and some people are puzzled: if the composite clustered index field is queried separately, then the query speed will slow? With this problem, let's take a look at the following query speed (the result set is 250,000 data): (the date column Fariqi first in the composite clustered index starting column, the user name Neibuyonghu row in the back column)

Idx1:create index idx1 on Tgongwen (Fariqi,neibuyonghu)

(1) Select Gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> ' 2004-5-5 '

Query speed: 2513 ms

(2) Select Gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> ' 2004-5-5 ' and neibuyonghu= ' office '

Query speed: 2516 ms

(3) Select Gid,fariqi,neibuyonghu,title from Tgongwen
where neibuyonghu= ' office '

Query speed: 60280 ms

From the above experiment, we can see that if you use only the starting column of the clustered index as the query condition and the query speed of all columns that are used in the composite clustered index at the same time, it is even faster than using all of the composite index columns (in the same case as the number of query result sets) This index has no effect if only the non-starting column of the composite clustered index is used as the query condition. Of course, the query speed of statements 1, 2 is the same as the number of entries queried, if all the columns of the composite index are used, and the query results are small, so that will form an "index overlay", thus the performance can be achieved optimally. Also, keep in mind that no matter if you use other columns of the aggregated index frequently, the leading columns must be the most frequently used columns.

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

4. Do I need to build a single index and composite index on the same column?
Test: Sysbase 5.0 table table1 field: col1,col2,col3

Test steps:
(1) Build index idx1 on col1
Execute SELECT * FROM table1 where col1=a using idx1
Execute SELECT * FROM table1 where col1=a and col2=b also use idx1

(2) Delete index idx1, then build idx2 on (col1,col2) composite Index
Execute the above two queries, also use IDX2

(3) If two index idx1,idx2 are present
Not where col1= ' A ' is used with Idx1;where col1=a and col2=b with IDX2.
Its query optimizer uses one of the previously used indexes. Either use IDX1, or use IDX2.

Thus
(1) For a table, if there is a composite index on (col1,col2), it is not necessary to establish a single index on col1.
(2) If the query conditions require, can be in the case of a single index on col1, add Composite Index on (col1,col2), for a certain increase in efficiency.
(3) It is not particularly advantageous to build multiple fields (including 5, 6 fields) at the same time, and it is relatively possible to create more efficient and more flexible indexes with narrow fields (only one, or at most 2 fields).



5. Do I need a coverage query?
It is often best not to adopt a strategy that emphasizes full coverage of queries. If all the columns in the SELECT clause are overwritten by a non-clustered index, the optimizer recognizes this and provides good performance. However, this usually causes the index to be too wide and relies excessively on the likelihood that the optimizer will use the policy. Typically, more narrow indexes are used, which can provide better performance for a large number of queries.

SQL Server's composite Indexing learning "reprint"