We all know that indexes can greatly improve the efficiency of database queries. Typically, when used, the key fields that are frequently queried are indexed.

For example, when trading records are queried by trans_date, this field is often indexed to improve query efficiency in the face of large amounts of data.

Trans_date union_idx_query = union_idx_query; trans_date union_idx_query = union_idx_query;

select count(1) from A; // 40000
​
EXPLAIN select * from A where trans_date = '20220222'; 
Copy the code

At this point, we assume that once we create an index, all other uses will follow the index as well. Such as the following query statement:

select count(1) from t_trans_log_info where trans_date > '20220122'; //11200
​
EXPLAIN select * from t_trans_log_info where trans_date > '20220122';
Copy the code

SQL > select trans_date, trans_date, trans_date, trans_date, trans_date; The answer is not necessarily.

Explain the above SQL statement, found that there is no index, but a full table scan.

But when changing a query parameter:

select count(1) from t_trans_log_info where trans_date > '20220222'; //1120
​
EXPLAIN select * from t_trans_log_info where trans_date > '20120222';
Copy the code

Explain results show off index:

Why the same query statement, but the query parameter value is different, but there will be an index, the other index does not go?

The answer is simple: the above indexes failed because the DBMS found that a full table scan was more efficient than a walk index, so it abandoned the walk index.

That is, when Mysql finds that the number of rows that pass an index scan exceeds 10% to 30% of the total table, the optimizer may drop the index and automatically switch to a full table scan. In some scenarios, forcing SQL statements to follow indexes also fails.

Similar problems tend to occur when performing range queries (such as >, <, >=, <=, in, etc.), and the threshold values mentioned above may vary from scenario to scenario.

So, if you’re using queries like this in your project and you want them to be indexed, you need to be careful. It is often necessary to add some additional constraints or other means to keep indexes valid.

About the blogger: Author of the technology book SpringBoot Inside Technology, loves to delve into technology and writes technical articles.

Public account: “program new vision”, the blogger’s public account, welcome to follow ~

Technical exchange: Please contact the weibo user at Zhuan2quan

\