Compound index, compound index reprinted

Compound index (reprinted) and compound index reprinted
Composite Index

I. concept:
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 );
Composite indexes require less overhead during database operations and can replace multiple single indexes;
At the same time, there are two concepts: narrow indexes and wide indexes. Narrow indexes refer to the index of 1-2 columns of the index column, and wide indexes refer to the index of more than 2 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;

 

Ii. Usage:
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. This is called overwrite query, which is very fast;

 

Iii. Notes:
1. For composite indexes, it is best to sort the conditions in the order of search indexes during query, which is the most efficient;
Select * from table1 where col1 = a and col2 = B AND col3 = D
If you use where col2 = B and col1 = A or where col2 = B, the index is not used.

2. When is composite index used?
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 an index when operating the table.

3. Will compound indexes replace a single index?
You can create a composite index if the index meets the requirements of a narrow index, which saves space and time.

Iv. Remarks:
Generally: 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;
Special case: 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. (When there are a lot of queries and few changes are made, you can consider the loss of some changes to reduce query consumption ).
Creating a composite index with multiple fields (including five or six fields) at the same time does not have many advantages. Relatively speaking, creating multiple narrow fields (only one or at most two fields are included) the index can achieve better efficiency and flexibility.

V. graphic explanation: 6. Compound unique index:

Create table T_Test (ID int identity, TypeID smallint not null, DKey smallint not null, DValue nvarchar (50) not null, Comment nvarchar (100) null, constraint PK_T_Test primary key (ID) gocreate unique index IX_Test on T_Test (TypeID ASC, DKey ASC) goinsert into T_Test (TypeID, DKey, DValue) values (, 'common user ') insert into T_Test (TypeID, DKey, DValue) values (, 'admin') insert into T_Test (TypeID, DKey, DValue) values (, 'administrator 22 ') select * from T_Test

-- The insert statement returns an error on the third day, because the TypeID and DKey of the inserted data cannot be the same. Only one of them is equal or both are not equal. The principle is similar: Composite primary key. Source: https://blog.csdn.net/kangbin825/article/details/54646110https://www.cnblogs.com/summer0space/p/7247778.html