Records an event in which a range query hits an index but does not use an index

Time:2020-12-20

cause

There are two general indexes create in the main order table of production environment_ Time and debts_ notify_ Num, when these two conditions are used for filtering, both indexes are hit, but only debts is used_ notify_ Num, resulting in more query columns and slower queries. It looks like “the index hits, but the query speed is slower.”.

Investigation process

The first step

create_ Time is a range query. When the range is small, create is used_ Time index.
Records an event in which a range query hits an index but does not use an index
Records an event in which a range query hits an index but does not use an index

Step two

Basically, it is the strategy problem of MySQL optimizer, but what is its strategy? Open the official website and find the where optimization strategy
Records an event in which a range query hits an index but does not use an index
To translate this is to say:The MySQL optimizer will judge whether the index query is faster or the full table scan is faster each time. The original judgment is that if the column filtered by the index query exceeds 30% of the total table, it will go to the full table index. However, the optimizer is not so low now. People have more conditions to judge whether to go to full table query, such as table size, row number and I / O block size