This is the first article about SQL optimization.
Here, I mainly express my point of view that if an index does not exist, it should be eliminated, leaving it in the way.
During the period from 15:39:01 to 15:55:00, we found two problems in a business system:
This phenomenon is also very common in the database, that is, a slow query, the initiator, the execution of ttrojan slow, the subsequent query should be blocked quickly, resulting in the emergence of a series of long queries
After diagnosis, we found that a dateline index exists in a table. This index will cause the MySQL optimizer to select an incorrect execution plan, resulting in a large number of SQL congestion in the future. About 5000 queries blocked each other.
If the dateline index is not used, the effect is good. The following comparison:
1. Comparison of execution plans
2. Comparison of execution time:
Therefore, our feedback to developer shoes is that the dateline index is erased.
At first, we didn't know whether the index was provided for other queries, so we were scared that it would cause other queries to slow down.
However, after this period of time, no other queries are actually using this index.
Indexing is a good thing, but don't be greedy.
Good Luck!