mysql > index failure

MySQL is a widely used relational database management system. During development, indexes can improve query efficiency, but sometimes you encounter index failure, which causes queries to slow down or even make it impossible to use the index.

This article will introduce the causes of index failure, how to detect index failure, how to optimize the index and some practical cases.

1. Reasons for index failure

1. The function is used on the index column

For example: SELECT * FROM user WHERE DATE_FORMAT(create_time,'%Y -%m-%d')='2021-01-01';

Solution: Store the operation results of the function in a temporary table or a new table, and then query.

2. Index column type mismatch

For example: SELECT * FROM user WHERE create_time='2021-01-01';

If the create_time field is datetime type, and the query condition is a character type, it will cause the index to fail.

Solution: Keep the query condition type consistent with the index column type.

3. Operators are used on index columns

For example: SELECT * FROM user WHERE create_time INTERVAL 1 DAY > NOW();

Solution : Try to avoid using operators on index columns. You can calculate the operation results first and then query.

4. IS NULL or IS NOT NULL is used on the index column

For example: SELECT * FROM user WHERE create_time IS NULL;

Solution: use Joint index or modify the query statement.

5. The index column values ​​are unevenly distributed

For example: For a table containing millions of data, 90% of the values ​​​​of a certain field are equal. At this time Any queries using this field will fail.

Solution: Use joint index or use covering index.

6. The amount of data is too large

For example: SELECT * FROM user WHERE name LIKE '�c%';

Solution: Use full-text index or Modify query conditions.

2. How to detect index failure

You can view the query statement execution plan through the EXPLAIN keyword.

For example: EXPLAIN SELECT * FROM user WHERE create_time='2021-01-01';

If Using where appears in the query results, it means that the index is invalid and needs to be optimized.

3. How to optimize the index

1. Create a joint index

A joint index refers to an index composed of multiple columns, which can improve query efficiency.

For example: CREATE INDEX idx_user ON user(create_time, name);

The order of the query must be considered. For example, in the above statement, create_time is the first column and name is the second column. Create_time must exist in the query conditions before this index can be used.

2. Create a covering index

A covering index means that the query results only need to be obtained from the index, not from the data table.

For example: CREATE INDEX idx_user ON user(create_time) INCLUDE(name);

You must clearly specify the columns that need to be included. When querying, you only need to obtain the query results from the index. You can Reduce the number of accesses to the data table and improve query efficiency.

3. Using full-text index

Full-text index is a special index suitable for text search scenarios.

For example: CREATE FULLTEXT INDEX idx_user ON user(name);

It must be noted that MySQL only supports English and Chinese full-text indexes, other languages ​​require the use of third-party plug-ins.

4. Actual cases

1. Index failure causes query to slow down

A certain system has a user table user, which contains millions of data , which has a field name, used to query users.

Initially, the field did not have any index and the query speed was very slow. Later, an index was added to this field, and the query speed was greatly improved.

However, after the system went online for a period of time, it was found that the query speed dropped again, and many records similar to the following appeared in the access log:

SELECT * FROM user WHERE name LIKE '�c% ';

View the execution plan through the EXPLAIN keyword and find that the index is invalid and needs optimization.

The final solution is to create a full-text index for the field and modify the query statement. The modified query statement is as follows:

SELECT * FROM user WHERE MATCH(name) AGAINST('abc');

2. The index failure causes the query to run too long

A certain system has an order table order, which contains millions of data. There is a field create_time used to query orders.

Initially, the field was indexed and the query speed was acceptable. However, as the number of orders increases, the query running time gradually increases, even causing some queries to time out.

Check the execution plan through the EXPLAIN keyword and find that the index is invalid and needs to be optimized.

The final solution is to create a joint index for the field and modify the query statement. The modified query statement is as follows:

SELECT * FROM order WHERE create_time='2021-01-01' AND status='SUCCESS';

In summary, the index is to improve query efficiency important means, but in actual use, index failure needs to be avoided. By detecting and optimizing indexes, query efficiency can be improved and optimal database performance can be achieved.

The above is the detailed content of mysql > index failure. For more information, please follow other related articles on the PHP Chinese website!