MySQL study Notes-guidelines for selecting indexes in 17th optimization-MySQL

MySQL study Notes-guidelines for selecting indexes based on 17th optimization I. 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, for example:

SELECT
Col_a distinct is not suitable for indexing columns
FROM
Tbl1 left join tbl2
ON tbl1.col _ B = tbl2.col _ c labels are suitable for index columns.
WHERE
Col_d = expr partition is suitable for index columns

Of course, the selected columns and columns used for the WHERE clause may also be the same. The key is to list the columns that should not be indexed in the current selection list.

Columns that appear in the join clause or in expressions such as col1 = col2 are suitable for indexing. The example of col_ B and col_c in the query is as follows. If MySQL can use the connection column to optimize a query,

It reduces the combination of table rows by eliminating full table scans.

II. Use Unique Indexes

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. Used to record gender

If a column contains only "M" and "F", it is not very useful to index the column (no matter which value is searched, about half of the rows will be obtained ).

III. 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 there is a CHAR (200) column, if the first 10 or 20 characters, multiple values are unique, then 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 short 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

Knowledge. 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 .)

Assume that a table has an index on the three columns named state, city, and zip respectively. The rows in the index are stored in the order of state/city/zip. Therefore, the rows in the index are automatically in the order of state/city and

State. This means that MySQL can use indexes even if only the state value or the city value is specified in the query. Therefore, this index can be used to search for the following column combinations:

MySQL cannot search with no left prefix. For example, if you search by city or zip, you cannot use this index. If you want to search for a state and a zip code (Column 1 and column 3 in the index), the index does not

Can be used for combination of corresponding values. However, indexes can be used to find rows that match the state to reduce the search range.

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. Modify the table content

The index must be updated, and sometimes it may need to be reconstructed. Therefore, the more indexes, the longer it takes. If an index is rarely used or never used, the modification speed of the table is not necessarily slowed down. In addition, MySQL generates

It takes time to consider each index for 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. Only keep required

The index 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. comparison types in columns

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

STRCMP () has no value for indexing it.