MySQL index optimization

Here are some of the learning experience after reading "high performance MySQL"

Before I figure out MySQL InnoDB to understand the principle of the index in advance, the most basic to understand the disk structure and working methods and b-tree structure.

Purpose of Indexing:

1. Reduce the amount of data scanned when the server executes SQL
2. Temporary tables that are generated when an order by or group by operation is avoided
3. Convert random io into sequential io

Criteria for evaluating Index quality:

1. One star, the index puts the relevant data together
2. Two stars, the order of the columns in the index is the same as the order of the columns in the query
3. Samsung, the column in the index contains all the columns in the query

Design Index Considerations:

1. The federated Index of multiple fields is the same as the order of the queries, so here is a question about which column should be the first queried field when designing a query?

e.g. use the following principles:

The reason for this: 1th level if the use of the aggregation of a few keys, then it is easier to make the 2nd level more aggregation, that is, the number of times to query B-tree less.

MySQL index optimization