Shulou(Shulou.com)06/01 Report--

The reason why MySQL can retrieve data efficiently, it can be said that it all depends on the index. In the process of using the index, you should pay attention to several points.

1. MySQL uses the leftmost matching principle when using the index.

If it is a single-column index, it is easy to understand. If it is a multi-column index, for example, idx_a_b_c (arecoverb), you can combine the indexing functions into (a), (arecoery b), (arem breagle c), and the index is convenient without backtracking at one time, so if you use more than two columns, the preceding ones need to be matched exactly except the last column. Indexes can be used in the following SQL.

Select * from t order by a dint bjorc

Select * from t where a=constant order by b asc,c asc

Select * from t where a=constant order by b desc,c desc

Select * from t where a=constant and b=constant order by c

Select * from t where a=constant and b > constant order by b

The following situations do not require an index or sort without an index.

Select * from t order by a ~ d

Select * from t where b=constant order by a

Select * from t where a=constant order by b asc,c desc

2. MySQL cannot use indexes in computed columns.

For example, the following SQL cannot use indexes.

Select * from t where ABS (a) = constant

Select * from t where f (a) = constant;//f is an arbitrary function

3. MySQL cannot use indexes in negative conditions.

For example, when there are, not in, not exists in the where condition, even adding an index to these judgment fields will not work.

4. MySQL cannot use an index if the join field types in join are not consistent.

For example, a field of type INT cannot be the same as a field of type VARCHAR join. One exception is that CHAR and VARCHAR can join when defining the same field length, and vice versa. For example, CHAR (20) and VARCHAR (20) can use indexes, while CHAR (20) and VARCHAR (25) cannot, no matter how long the actual value stored in VARCHAR is.

5. Use the overlay index tactfully.

When MySQL uses the B+ number index to retrieve data, if it is not based on the clustered index, or if it is not based on the primary key, then even if the SQL statement can make use of the index, the information returned by the index is only the primary key value of the desired result row. In order to channel all the data, it is necessary to redirect the data file through these primary key values to do another retrieval operation, which requires additional IO, which reduces the query efficiency. The following is an example.

Select namespace from dbrequest_info where update_date between '2017-01-01' and '2017-02-01'

If you have an index (update_date) in the dbrequest_info table, you can use the index to query. If you want to take advantage of an overlay index, you can add an index (update_date,namespace).

6. Other areas that need to be paid attention to.

The resolution of the index is not high, even if the index is used, the contents of the whole table are often scanned more than 20%, so it is necessary to carefully add the index. In addition, avoid using like queries as soon as possible, especially in the case of fuzzy matching on the left such as like'% name', where indexes cannot be used with MySQL. If there is an implicit character type conversion, MySQL cannot use the index, which is like adding a function to the judgment column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.