MySQL InnoDB index Small note

1. Index Use principle

1), the leftmost prefix matching principle, MySQL will always match to the right until it encounters a range query (>, <, between, like) to stop matching, such as a = 1 and B = 2 and C > 3 and D = 4 If the order is established (A,B,C,D) Index, D is not indexed, if the establishment (A,B,D,C) of the index can be used, the order of a,b,d can be arbitrarily adjusted. 2), = and in can be disorderly, such as a = 1 and B = 2 and c = 3 establish (A,B,C) can be arbitrary order, MySQL query optimizer will help you to optimize the index can be recognized as the form of 3), as far as possible to select a high-sensitivity column as the index, the formula of the degree of differentiation is the count (distinct col)/count (*), which indicates that the field does not repeat the scale, the larger the scale 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, then someone may ask, What is the empirical value of this ratio? Using different scenarios, this value is also difficult to determine, the general need to join the field we are required to be more than 0.1, that is, the average 1 scan 10 records. 4), the index column can not participate in the calculation, keep the column "clean", such as from_unixtime (create_time) = ' 2014-05-29 ' can not be used to the index, the reason is very simple, B + tree is stored in the Data table field values, but when the retrieval, You need to apply all the elements to the function to compare, obviously the cost is too large. So the statement should be written create_time = Unix_timestamp (' 2014-05-29'); 5), 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. 6), if the table records are relatively small, you can use no index.

2. Seven cases of using federated indexes

1), full column index    MySQL uses the index, and the column order can be reversed. 2), the leftmost prefix index    mysql will use index 3), the query criteria used in the index of the exact match, but the middle of a condition does not provide    MySQL will not use the index, but if the middle Missing index column to fill, You can have index optimization.    "In" can be used for indexed columns. 4), query condition not specified index first column    MySQL does not use the index. 5), match the prefix string of a column    as long as the wildcard % does not start now, MySQL can use the index for this column. 6), scope query    range column can be used to index, must be the leftmost prefix, but the index after the range column is not available to the index. 7), Query Condition column contains function or expression    MySQL does not use an index for this column. 

Reference:

http://blog.coderland.net/mysql/2015/08/26/MySQL%E7%B4%A2%E5%BC%95%E5%AE%9E%E8%B7%B5/

http://www.kancloud.cn/kancloud/theory-of-mysql-index/41857

Http://tech.meituan.com/mysql-index.html

MySQL InnoDB index small note