introduction
In MySQL databases, indexing is a key tool for improving query performance. However, in some cases, even if an index is created, the query performance may still be unsatisfactory and even index failure occurs.
Index failure will cause the database to perform full table scanning, greatly reducing query efficiency, especially in scenarios with large data volumes. Therefore, understanding common index failure scenarios and their optimization solutions is crucial for database performance tuning.
Through this article, you will be able to:
- Identify common index failure scenarios.
- Understand the root cause of index failure.
- Master targeted optimization strategies to avoid index failure.
- Index failure scenarios and how to solve them
1. LIKE query starting with %
Optimization solution:
First, scan the secondary index to obtain the PRIMARY KEY that meets the conditions and query the table based on the primary key.
SELECT * FROM (SELECT actor_id FROM actor WHERE last_name LIKE '%NI%') tmp INNER JOIN actor a ON a.`actor_id`=tmp.actor_id
2. Implicit conversion of types (character to numbers will be invalid, and numbers to characters will not be invalid)
For example:
SELECT * FROM actor WHERE last_name=1; -- Will fail SELECT * FROM actor WHERE actor_id='1'; -- 不Will fail
3. Union index does not meet the leftmost principle
For composite indexes, the query conditions should start from the leftmost column of the index and be contiguous.
"Improvement method":
If an index is (a, b, c), then it should be guaranteed that the query conditions start from a, such as WHERE a = 1 AND b = 2.
4. MYSQL Optimizer
It is estimated that using indexes is slower than scanning all.
5. When using or
It is necessary to ensure that there are indexes before and after it. If either of them does not include an index, it will cause the index to fail.
Summarize
The above is personal experience. I hope you can give you a reference and I hope you can support me more.