MySQL index design principles

MySQL index design principles

1. The index column to be searched is not necessarily the column to be selected. In other words, the column most suitable for indexing is the column that appears in the WHERE clause or the column specified in the join clause, rather than the column in the selection 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 effect is the best, while for columns with multiple duplicate values, the index effect is the worst. For example, columns with different age values can easily distinguish rows. The columns used to record gender only contain "M" and "F", so it is not very useful to index this column (no matter which value is searched, it will produce about half of the rows)

3. Use short indexes. If you index a string or column, you should specify a prefix length. If possible, you should do this. For example, if a CHAR (200) Column exists and multiple values are 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 make the query faster. A smaller index involves less disk I/O, and a shorter value is faster. More importantly, for shorter key values, the index cache blocks can accommodate more key values. Therefore, MySQL can also accommodate more values in the memory. This increases the possibility of finding rows without reading more data from the index. (Of course, some common sense should be used. If you only use the first character of the column value for indexing, it is impossible to have much benefit, because there will not be many different values in this index .)

4. Use the leftmost prefix. When creating an index with n columns, you actually created n indexes available for MySQL. Multi-column indexes can act as several indexes, because the leftmost column set in the index can be used to match rows. Such a column set is called the leftmost prefix. (This is different from the prefix of an indexed column. The prefix of an indexed column uses the first n characters as the index value .)

5. Do not over-indexing. Do not think that the index is "more and better". It is wrong to use indexes for anything. Each additional index takes up extra disk space and reduces write performance. We have already discussed this. When modifying the table content, the index must be updated and may sometimes need to be reconstructed. Therefore, the more indexes, the longer the time it takes. If an index is rarely used or never used, the modification speed of the table is not necessarily slowed down. In addition, it takes time for MySQL to consider indexes when generating an execution plan. Creating redundant indexes brings more work to query optimization. Too many indexes may cause MySQL to fail to select the best index to be used. Maintaining only the required indexes is conducive to query optimization. To add an index to an indexed table, consider whether the index to be added is the leftmost index of an existing multi-column index. If yes, you do not need to add this index because it already exists.

6. Consider the comparison type in the column. Indexes can be used for operations such as <, <= "," = ","> = ","> ", and. When the pattern has a direct volume prefix, the index is also used for the LIKE operation. If you only use a column for other types of operations (such as STRCMP (), there is no value for indexing it.

MySQL index isolation Column

Hash indexes of MySQL Indexes

Prefix index and index selectivity of MySQL Indexes

MySQL index usage monitoring

Replace MySQL index optimization and in or with union all

This article permanently updates the link address: