MySQL Index management principles

Recently in the study of MySQL index optimization, combined with some of our network system business, read some of the information, sorting out some of the following ideas:

1, Index Establishment principle one: The leftmost prefix matching principle , very important principle, MySQL will always match right until encounters the range query (>, <, between, like) stops the match, for example a = 1 and B = 2 and C > 3 an D = 4 If the index of the (A,B,C,D) order is established, D is not indexed, if the index of the Establishment (A,B,D,C) can be used, the order of a,b,d can be arbitrarily adjusted

2, as far as possible to choose a high-sensitivity column as an index, the formula for the degree of sensitivity is count (distinct col)/count (*), indicating the scale of the field is not repeated, the greater the proportion of the number of records we scan, the difference between the unique key is 1, and some states, The gender field may be 0 in front of big data, and one might ask, what is the empirical value of this ratio? Using different scenarios, this value is also difficult to determine, generally need to join the field we are required to be more than 0.1, that is, the average 1 scan 10 records

3, try to expand the index, do not create a new index. For example, the table already has an index of a, now to add (A, b) of the index, then only need to modify the original index (the less the better; Reason: The main changes in the data, each index to be updated, reduce write speed).

MySQL Index management principles