High-performance MySQL (5) Indexing strategy

The advantages of the index

1, the index can greatly reduce the amount of data the server needs to scan

2, the index can help the server avoid sorting and temporary tables.

3, the index can turn I/O into sequential I/O

Second, indexing strategy

1, Independent column

A separate column means that an indexed column cannot be part of an expression, nor can it be a function argument. For example:

A table with a record of 500W, ID is the primary key.

So always place the index column on one side of the comparison symbol.

2, prefix index and index selectivity

For larger columns, you can usually index the starting part of the character, which saves the index space and increases the index rate. But it also lowers the selectivity of the index.

The selectivity of an index is the ratio of a distinct index value (cardinality) to the total number of records in a table, and the higher the selectivity of the index, the higher the query efficiency, since it is possible to filter out more rows in the lookup, the selectivity of the unique index is 1, and the performance is best.

How to choose a suitable length, so that the cardinality of the prefix is close to the cardinality of the complete column, generally we have 2 methods:

A, to determine the appropriate length of the prefix, you need to find a list of the most common values, and then compare them with the most common list of prefixes.

For example: A table with a 100W record to add a prefix index to name