Principles for designing MySQL indexes

1. The index column of the search is not necessarily the column you want to select. In other words, the column that best fits the index is the column that appears in the WHERE clause, or the column specified in the JOIN clause, rather than the column that appears in the select list after the SELECT keyword.

2. Use a unique index. Consider the distribution of values in a column. For columns with unique values, the index works best, and columns with multiple duplicate values have the worst indexes. For example, a column that holds age has different values, and it's easy to differentiate between rows. The columns used to record the gender, which contain only "M" and "F", are not much useful for indexing this column (whichever value you search, you will get about half of the rows)

3. Use a short index. If you index a string, you should specify a prefix length, which you should do whenever possible. For example, if you have a char (200) column, and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Indexing the first 10 or 20 characters can save a lot of index space, and may make queries faster. Smaller indexes involve fewer disk I/O, and shorter values compare faster. More importantly, for shorter key values, the blocks in the index cache can hold more key values, so MySQL can also accommodate more values in memory. This increases the likelihood of finding rows without having to read more chunks in the index. (Of course, some common sense should be used.) It is not possible to have the advantage of indexing only the first character of a column value, because there are not many different values in the index. ）

4. Use the leftmost prefix. When you create an index of n columns, you are actually creating n indexes that MySQL can use. Multiple-column indexes can serve several indexes because the leftmost Lie in the index can be used to match rows. Such a column set is called the leftmost prefix. (This is different from indexing a column, where the prefix of a column is the index value using the first n characters.) ）

5. Do not over-index. Do not assume that the index "the more the better", everything is indexed is wrong. Each additional index consumes additional disk space and reduces the performance of write operations, as we have described earlier. When you modify the contents of a table, the index must be updated, and sometimes refactoring may be required, so the more indexes you have, the longer it takes. If an index is seldom exploited or never used, it will unnecessarily slow down the table's modification speed. In addition, MySQL takes time to consider each index when generating an execution plan. Creating redundant indexes has brought more work to query optimization. Too many indexes may also make MySQL choose the best index to use. Maintaining only the desired index is advantageous for query optimization. If you want to add an index to an indexed table, you should consider whether the index you want to increase is the leftmost index of an existing multi-column index. If so, don't bother to increase the index because it's already there.

6. Consider the type of comparison that is performed on the column. Indexes are available for <, < =, =, > =, >, and between operations. When a pattern has a direct volume prefix, the index is also used for the like operation. If you use only one column for other types of operations, such as strcmp (), it is not worthwhile to index it.

Principles for designing MySQL indexes