MySQL Index build

How do I build a MySQL index?

CREATE INDEX principle

1. The left prefix principle:

The MySQL index match will be matched from right to the end of the encounter (> < between like). As we build the index (a,b,c,d), a = 1 and b=2 and C>3 and d=4. At this point, index d is not used. A truly valid index (a, B, c). If the index is set to (A,B,D,C), then four indexes can be used

2, in and = order can be arbitrary.

If an index (a, B, c) can be built in any order, a = 1 and c=2 and b=3, three indexes remain valid. This is the MySQL query optimizer optimized to a recognizable index form

3, expand the index, try not to create a new index.

If the original index (a) is now in demand and needs to be indexed (a, b), then there is absolutely no need to create a new index (a, B). You just need to expand your original index (a) and change it to (a, B). At this point, both the original index and the new index can be achieved. such as a = 1 a = 1 and b=2 The two can go to the index (a), (A, B)

4. Try to select a high-sensitivity column as the index

The formula for the degree of sensitivity is count (distinct X)/count (*), which indicates that the field does not repeat the scale, the larger the proportion we scan the less the number of records, the uniqueness of the unique key is 1, and some states, sex fields may be in front of the big data to differentiate the degree is 0

MySQL Index build