MySQL in index optimization

1. Composite Index
For example, there is a statement like this: SELECT * from users where area= ' Beijing ' and age=22;
If we were to create a single index on area and age, because the MySQL query can only use one index at a time, even though this has been relatively non-indexed, the full table scan has improved a lot
Rate, but if you create a composite index on the area and age two columns, it will be more efficient. If we create a (area, age,
Salary), then it is actually equivalent to creating (Area,age,salary), (Area,age), (area) Three indexes, which is called the best left prefix
Characteristics. Therefore, when creating a composite index, the columns that are most commonly used as constraints should be placed on the leftmost, decreasing in turn.

2. Indexed issues for sorting
The MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns.
SELECT * from Zl_yhjbqk ORDER by QC_BH (build QC_BH index)
SELECT * from zl_yhjbqk where qc_bh= ' 7001 ' ORDER by CB_SX (build QC_BH+CB_SX Index, note: Just an index, which includes QC_BH and CB_SX fields)

3. Using a short index
Index A string, or specify a prefix length if possible. For example, if you have a column of char (255), and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations.

MySQL in index optimization