Overview
What is a single index and what is a composite index? When to create a new composite index, and what should I pay attention to in the composite index? This article is mainly a summary of some discussions on the Internet.
One. concept
A single index is a situation where the index is listed in one column, that is, the statement of creating a new index is implemented on only one column.
Users can create indexes on multiple columns, which are called composite indexes (combined indexes). Composite indexes are created in exactly the same way as single indexes. But composite 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 keys, this method can significantly speed up the query speed of the table.
There are two concepts at the same time, narrow indexes and wide indexes, narrow indexes refer to indexes with 1-2 columns, and generally refer to a single index unless otherwise specified. A wide index is an index with more than 2 columns.
An important principle of index design is to use narrow indexes instead of wide indexes, because narrow indexes tend to be more effective than combined indexes. Having more narrow indexes will give the optimizer more choice, 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 is not scanning the table, but directly takes the data from the index, because there is this data in the index, which is called an overlay query, and the query speed is very fast.
Three. Notes:
1. When to use a compound index
In the where condition, the field is indexed, and if multiple fields are used, a composite index is used. Generally, do not build any indexes in the select field (if you want to query select col1, col2, col3 from mytable, you don't need the above index). Indexing based on where conditions is an extremely important principle. Be careful not to use too many indexes, otherwise it will have a great impact on the efficiency of table updates, because you have to spend a lot of time creating indexes when operating tables.
2. For composite indexes, when using query, it is best to follow the order of finding indexes, 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 noticeable
3. Will a composite index replace a single index?
Many people think that adding any field to the clustered index can improve query speed, but some people are confused: If the compound clustered index fields are queried separately, will the query speed be slowed down? With this question, let's take a look at the following query speed (the result set is 250,000 pieces of data) :( date column fariqi ranks first in the starting column of the composite aggregation index, and the username neibuyonghu ranks second)
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 milliseconds
From the above experiments, we can see that if only the starting column of the clustered index is used as the query condition, the query speed of all columns with the composite clustered index is almost the same, even slightly faster than using all the composite index columns (when the number of query result sets is the same). If only the non-starting columns of the compound aggregated index are used as query conditions, this index will not do anything. Of course, the query speed of statements 1 and 2 is the same because the number of entries in the query is the same, if all the columns of the composite index are used, and the query results are few, this will form an "index override", so that the performance can be optimized. Also, keep in mind that whether or not you use other columns in the aggregate index frequently, the leading column must be the one that is used most frequently.
[Ref: Query Optimization and Pagination 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 at the same time?
Experiment: Sysbase 5.0 table table1 fields: col1, col2, col3
Test Steps:
(1) Create the index idx1 on col1
Execute select * from table1 where col1=A uses idx1
Execute select * from table1 where col1=A and col2=B also use idx1
(2) Delete the index idx1, and then create an idx2 on (col1,col2) composite index
Both queries use idx2
(3) If both indexes idx1 and idx2 exist
It's not where col1='A' uses idx1; where col1=A and col2=B uses idx2.
Its query optimizer uses one of the previously commonly used indexes. Either use idx1 or idx2.
It can be seen that
(1) For a table, if there is a compound index on (col1, col2), there is no need to create a single index on col1 at the same time.
(2) If the query conditions require it, you can add the compound index on (col1, col2) when there is already a single index on col1, which can improve the efficiency to a certain extent.
(3) There are no particularly many benefits in establishing a composite index with multiple fields (containing 5 or 6 fields) at the same time, relatively speaking, establishing an index with multiple narrow fields (containing only one, or at most 2 fields) can achieve better efficiency and flexibility.
5. Do I need to cover the query?
It's generally best not to employ a strategy that emphasizes full query coverage. If all columns in the Select clause are overwritten by a non-clustered index, the optimizer will recognize this and provide good performance. However, this often results in an overly wide index and over-reliance on the likelihood that the optimizer uses the policy. Typically, narrow indexes are used for a larger number of queries, which provides better performance for large queries. |
Posted on 1/29/2015 1:06:03 PM
|
|
|
