Mysql index creation principles

Mysql index creation principles
Index Creation Principles

1. the leftmost prefix matching principle is very important. mysql always matches the right until it encounters a Range Query (>, <, between, like) to stop matching, for example, if a 1 = "" and = "" B = "2" c = ""> 3 and d = 4 is created, d cannot be used. If an index (a, B, d, c) is created, the order of a, B, and d can be used.
2. = and in can be out of order. For example, a = 1 and B = 2 and c = 3 (a, B, c) indexes can be created in any order, the mysql query optimizer helps you optimize it into a recognizable form of indexes.
3. select a column with a high degree of discrimination as the index. The formula for differentiation is count (distinct col)/count (*), indicating the proportion of fields that are not repeated. The larger the proportion, the fewer records we scan, the differentiation of the unique key is 1, while some state and gender fields may be 0 in front of big data. Someone may ask, is there any experience with this proportion? Different use cases make it hard to determine this value. Generally, we require more than 0.1 join fields, that is, to scan 10 records on average.
4. index Columns cannot be used for calculation and keep the columns "clean". For example, if from_unixtime (create_time) = '2017-05-29 ', indexes cannot be used. The reason is very simple, the B + tree stores the Field Values in the data table. However, when searching, you must apply functions to all elements for comparison. Obviously, the cost is too high. Therefore, the statement should be written as create_time = unix_timestamp ('2017-05-29 ');
5. Extend the index as much as possible. do not create a new index. For example, if the table already has an index of a and now you want to add an index of (a, B), you only need to modify the original index.