MySQL index invalid
1. Summary
Index InvalidIt refers to the fact that under certain specific conditions, the MySQL database fails to use the index expected by the query optimizer, but instead adopts full table scanning or other inefficient access methods, resulting in a sharp decline in query performance.
like:
SELECT * FROM users WHERE name LIKE 'Jack%';
Ideally, if there is an index on the name column, the query should quickly locate the matching data through the index. But if MySQL does not use indexes for some reason, it may lead to full table scans, which is the manifestation of index failure.
2. Common causes of MySQL index failure
1. Data type mismatch:
When the field data type in the query condition does not match the data type of the index column, the index will be invalid
-- Assume that the index field is INT type SELECT * FROM orders WHERE order_id = '123';
In this example, order_id is INT type, but a STRING type parameter is passed in during query. Even if there is an index on the order_id column, MySQL cannot use it effectively for retrieval.
Workaround: Make sure the data type of the query condition is consistent with the index field.
2. Use functions or expressions:
The index will usually fail if a function or expression is applied to the index column in the query condition.
-- Suppose there is an index in name On the column SELECT * FROM users WHERE LENGTH(name) > 5;
In the above query, the LENGTH() function is applied to the name column, and MySQL cannot use the index on the name column for quick search.
Workaround: Avoid using functions or expressions in WHERE clauses, or consider processing data outside of the function.
3. The "OR" condition was used:
When a query condition contains multiple "ORs", MySQL sometimes cannot select the optimal index, especially when the field index used by each condition does not match exactly.
-- Assume that the index is name and age On the column SELECT * FROM users WHERE name = 'Jack' OR age = 25;
In this case, MySQL may choose to use full table scan instead of using indexes.
Solution: Try to avoid OR conditions, especially when using OR for different index columns.
4. Wildcard position in LIKE statement:
MySQL cannot use indexes effectively when the wildcard % in the LIKE condition is at the beginning of the string.
-- Assume that the index is name On the column SELECT * FROM users WHERE name LIKE '%Jack';
The % wildcard character in the above query conditions is at the beginning of the string, and MySQL will not be able to use the index, resulting in a full table scan.
Workaround: Place the wildcard % at the end of the query string, or avoid queries starting with wildcards.
5. Improper use of composite indexes:
Composite indexes (multi-column indexes) are important in MySQL, but their use needs to follow specific rules. A typical error is that the query is not performed in the order of index columns.
-- Suppose there is a composite index (first_name, last_name) SELECT * FROM users WHERE last_name = 'Doe' AND first_name = 'Jack';
Although both last_name and first_name fields have indexes, MySQL may not be able to use composite indexes because the query order is inconsistent with the index order.
Workaround: Follow the order of composite indexes, prioritizing the leftmost prefix of the index.
6. Processing of NULL values:
If an index column contains a large number of NULL values, the index may fail during querying.
SELECT * FROM users WHERE middle_name IS NULL;
If there is an index on the middle_name column and the column contains a large number of NULL values, the query may choose a full table scan instead of using an index.
Solution: Try to avoid using IS NULL in the WHERE clause for querying.
3. How to diagnose MySQL index failure
1. Check the execution plan:
Through the EXPLAIN command, we can view the execution plan of the query and determine whether the index is used.
EXPLAIN SELECT * FROM users WHERE name = 'Jack';
In the execution plan, if the type field is ALL, it means that the query does not use the index, but has performed a full table scan.
2. Check SHOW INDEX information:
You can view the index status of a certain table through SHOW INDEX.
SHOW INDEX FROM users;
Through this command, we can confirm whether an index has been created for the query field.
4. How to solve the problem of MySQL index failure
1. The data type is consistent:
Ensure that the data type of the query condition is consistent with the data type of the index column and avoid implicit conversion.
SELECT * FROM orders WHERE order_id = 123; -- Ensure that incoming data types are consistent
2. Avoid using functions or expressions in WHERE clauses:
Try to avoid using functions or expressions on index columns to reduce the complexity of the query.
SELECT * FROM users WHERE name LIKE 'Jack%'; -- Do not use LENGTH(name) Equal function
3. Optimize OR conditions:
Avoid using multiple OR conditions in queries, especially for different index columns.
-- A better way SELECT * FROM users WHERE name = 'Jack' AND age = 25;
4. Optimize LIKE queries:
Avoid using % as the beginning of a wildcard, instead using % at the end of the query string.
SELECT * FROM users WHERE name LIKE 'Jack%'; -- Change it to this
5. Optimize the use of composite indexes:
Make sure the query follows the order of composite indexes, using the leftmost prefix of the index first.
SELECT * FROM users WHERE first_name = 'Jack' AND last_name = 'Doe'; -- Correct order
6. Process NULL values:
Avoid using IS NULL queries, especially on columns containing a large number of NULL values.
SELECT * FROM users WHERE middle_name IS NOT NULL; -- Avoid using NULL
Summarize
The problem of MySQL index failure is often related to query conditions, index design and data distribution.
By understanding the principle of index and the working mechanism of the query optimizer, we can effectively avoid and solve the problem of index failure, thereby improving query performance.
In the actual development process, developing good index usage habits and specifications will help reduce performance bottlenecks and ensure efficient operation of the system.
The above is personal experience. I hope you can give you a reference and I hope you can support me more.