V. Index of MySQL

For established index (surname, first name, data)

5.1, the index of the query type is valid

1, full value matching: can find the surname + named Allen, the date of birth is 1990-11-05 person;

2. The leftmost prefix matches: You can find the person whose surname is Allen; that is, only the first column of the index is used;

3. Column prefix matching: You can find the person whose surname is J; Here the index also uses only the first column;

4. Range Matching: You can find the person whose surname begins with A-j; Here the index also uses only the first column;

5, the exact match the previous column, the range matches after one column;

6, only access the index query;

7, ORDER by also satisfies the index;

5.2. Limitations of the Index

1, unable to find a specific name of the person;

2, unable to find a J ending surname;

3, can not skip the index, that is, no surname A, the date of birth xx person;

4, the right index of the scope query is invalid, for example, the surname is Ax, the name is like b%, the date of birth is 199-01-01, only the first two columns of the index are used;

5.3 High-performance indexing strategy

1. Independent columns, the following SQL invalidation: Selext * from user where id +1 = 10;

2, select the appropriate index column,

3. Clustered index and non-clustered index (difference between INNODB and MyISAM engines)

Try to use the self-increment primary key instead of the UUID, as this will result in the data having no aggregation properties;

4, Index overlay-if the query criteria already contains the value to query, then for the clustered index, there is no need to do two back-check;

For example, the table has a federated index (Id,name), and when the Sql=select ID, name from table, the index overlay is triggered;

5. When multiple table joins, only the ordery by condition of the first table can use the index;

On the entry into force and expiration of order by

For table with Federated Unique Index (A,B,C)

The following indexes take effect:

where a= x order by B; Can use index, because a is constant, and a+b is the leftmost prefix index;

Where a >x order by a A, and can use the index because A+B makes up a federated index;

The following indexes fail:

where a = x order by B desc,c Desc, because two different sort directions, but the index column is a positive order;

Where a =x order by B, D uses a column that is not in the index;

Where a =x ordery C, A+c cannot combine the leftmost prefix combination;

Where a > x order by B,c; failure, a using range query condition, b,c combination failure;

6, use in sex to not selectivity, let the range query fields (such as age) to the last;

　　

　　　　

V. Index of MySQL