In-depth understanding of Oracle index (14): Analysis of the two principles of Composite Index

Statement: although the title is Oracle, it is also suitable for MySQL InnoDB indexing.

In most cases, compound indexes are better than single-field indexes.
Many systems rely on the creation of some suitable composite indexes to greatly improve efficiency.
Composite indexes are more complex than single-field indexes. There are two principles to grasp: prefix and optionality.
However, unfortunately, many IT system developers in China do not realize that they should give priority to designing compound indexes.
The prefix and optionality of composite indexes are not fully understood.

(I) prefixing)

In the predicate condition, the composite index is used only when the first field of the composite index is used as the constraint.
Of course, there is an exception: Index skip Scan

This principle emphasizes Use

For example:

① Suppose C1 is much more selective than C2, and if there are a large number of queries as follows:
Select * from t where c1 = and C2 =: Y;
Select * from t where C2 =: Y;
This time (C2, C1) is better than (C1, C2)

② There are two fields: ID and create_time. One is the primary key and the other is the creation time.
In general, when creating a composite index, you need to put the ID first,
However, if there are more SQL statements with create_time as the query condition and fewer IDs are used
Therefore, we should prefix create_time during design.

(Ii) selectitability)

Concept visibility

Introduction to selecti.pdf

This principle emphasizesCost

For example:

① Placing highly selective fields in front can reduce the scanning cost of index range scan.
② There is a tax system with the original compound index (month, Tax Authority code, taxpayer identification number, invoice code, charge type)
The optimizer does not choose to index this index, but scans the entire table for the following reasons:
● There are too many records in January, more than 1/3, and the cost of full table scan is low.
● Field order is not optional
The new compound index is (taxpayer identification number, month, charge type, Tax Authority code, invoice code)


(Iii) benefits of composite indexes

① Try to make an index more SQL services
② Composite indexes are sorted. If order by involves index fields, the sorting cost can be reduced.


What do I think,When sorting compound index fields, you should consider prefix and optionality comprehensively, and never favor any party.