Effective rules of multi-column index

Myisam,innodb in mysql uses Btree index by default, and it doesn't matter what the data structure of btree is.

You only need to know the result, since it is an index, then the data structure is sorted at last; just like Xinhua Dictionary, its directory is in accordance with ABM c.. It's sorted like this.

That's why you're fast when you're looking for something. For example, if you look for the explanation of the word "Zhong", you will definitely locate the beginning of z in the directory.

Combinatorial indexes can be understood in this way, for example, abc is ordered, under any paragraph a, b is ordered, and c under any paragraph b is ordered

The effective principle of a combined index is to use it one after the other. If an index in the middle is not used, then the index part in front of the breakpoint works, and the index after the breakpoint does not.

such as

Where axiom 3 and bang 45 and cantilever 5.... This three indexing order uses no breakpoints in the middle and all works; where axiom 3 and clock5. In this case, b is the breakpoint, a has an effect, and c has no effect where baked 3 and clock4. In this case, an is the breakpoint, and the index after a does not work, and the joint index does not have any effect; where bread45 and axiom 3 and cantilever 5. This is the same as the first one, it all works. Abc just needs to be used, regardless of the order in which it is written.

(a) A federated index is added on three columns (a federated index is not a separate index on each column)

It should also be noted that the multi-column index is different from the multi-column index. Looking at the diagram above, we can also see that the order of relations is different.

Analyze several practical examples to enhance understanding

Analyze the indexes used in sentences

(0) select * from mytable where axiom 3 and baggage 5 and cantilever 4 and cantilever 4 are all used in the where condition, and all play a role (1) select * from mytable where canti4 and bread6 and axiom 3; this sentence is listed only to show that mysql is not so stupid, and the order of conditions in where will be automatically optimized by mysql before query, and the effect is the same as the previous sentence (2) select * from mytable where aquisition 3 and cantilever 7 A uses the index, but b is useless, so c does not use the index effect. (3) select * from mytable where axiom 3 and b > 7 and cantilever 3 is used, b is also used, c is not used, this place b is a range value, it is also a breakpoint, but it uses the index (4) select * from mytable where bread3 and cantilever 4 Because an index is not used, there is no index effect here in bc (5) select * from mytable where a > 4 and bread7 and caterpillar 9a, b is not used, c is not used, (6) select * from mytable where adep3 order by bttera is used, and b is also used in the sorting of results. As mentioned earlier, any paragraph b under an is ordered (7) select * from mytable where adep3 order by c A uses the index, but in this place c does not play the sorting effect, because there are intermediate breakpoints, and using explain you can see that filesort (8) select * from mytable where bread3 order by a share b does not use the index, and sorting a does not play the index effect.