preface

I have read many articles about MySQL index invalidation caused by improper use of SQL statements. There are also MySQL “catch-all” or specifications that state that certain SQL cannot be written this way, otherwise the index will fail.

Most of the content is recognized by the author, but some examples of the author think the wording is too absolute, and did not explain the reason, many people do not know why. So I will definitely sort through the common scenarios of index failure in MySQL, and analyze the reasons for your reference.

Remember, of course, explain is a good habit!

Common scenarios of MySQL index failure

When verifying the following scenario, prepare a sufficient amount of data, because the MySQL optimizer sometimes determines that a full table scan is harmless and will not hit the index.

1. If the WHERE statement contains OR, the index may become invalid

Using OR does not necessarily invalidate an index; you need to see if the query columns to the left and right of or match the same index.

Assume that the user_id column in the USER table has an index and the age column does not.

The following statement actually hits the index (if you are using an older version of MySQL, you can use explain to verify).

select * from `user` where user_id = 1 or user_id = 2;
Copy the code

But this statement cannot match the index.

select * from `user` where user_id = 1 or age = 20;
Copy the code

If the age column also has an index, it will still fail to hit the index.

select * from `user` where user_id = 1 or age = 20;
Copy the code

Therefore, it is recommended to avoid using the OR statement as much as possible. Instead, use the union all or in statement as the case may be. These two statements are also more efficient than OR.

2. The index column in the WHERE statement uses a negative query, which may cause index invalidation

Negative queries include: NOT,! =, <>! <,! >, NOT IN, NOT LIKE, etc.

Some “military rules” said that the use of negative query will certainly index failure, THE author checked some articles, some netizens refuted this point and provide proof.

It is up to the MySQL optimizer to determine whether a full table scan or an index run is cheaper.

3. The index field can be null. If is NULL or IS not NULL is used, the index may be invalid

If an index is null or is not null, then the index will be invalid. If an index is not null, then the index will be invalid

Assume that the user_id column in the USER table has an index and allows NULL, and the AGE column has an index and allows NULL.

select * from `user` where user_id is not null or age is not null;
Copy the code

However, it is emphasized in some catch-all and specification that there is a reason to set the field to not NULL and provide a default value.

  • Null columns complicate index/index statistics/value comparisons and are more difficult to optimize for MySQL.
  • This type requires special processing inside MySQL to increase the complexity of processing records. All things being equal, database processing performance deteriorates when there are many empty fields in the table.
  • Null values require more storage space, and null columns in each row of a table or index require additional space to identify.
  • The value can only be IS NULL or is not NULL. The value cannot be =, in, <, <>, or! =, not in these operation symbols. Such as: where the name! = ‘shenjian’, if there is a record whose name is null, the query result will not contain the record whose name is null.

4. Using built-in functions on index columns will definitely cause index invalidation

Alter table login_time; alter table login_time;

select * from `user` where DATE_ADD(login_time, INTERVAL 1 DAY) = 7;
Copy the code

Optimization recommendations, as much as possible in the application for calculation and conversion.

In fact, there are two kinds of index failure scenarios mentioned by netizens, should be attributed to the use of index columns function.

4.1 Index invalidation due to implicit type conversion

Select * from vARCHAR; select * from varchar; select * from varchar;

select * from `user` where user_id = 12;
Copy the code

This is because MySQL is implicitly casting user_id by calling the function.

select * from `user` where CAST(user_id AS signed int) = 12;
Copy the code

4.2 Index invalidation due to implicit character encoding conversion

When an associative query is performed between two tables, if the character encoding of the associated field in the two tables is inconsistent, MySQL may call the CONVERT function to implicitly CONVERT the different character encoding to achieve unification. When applied to the associated field, it invalidates the index.

For example, the following statement, where the d.tab id character is encoded utF8 and the l.tab ID character is encoded utF8MB4. Since UTF8MB4 is a superset of UTF8, MySQL converts UTF8MB4 with CONVERT. CONVERT is applied to d.trade ID, so the index is invalid.

select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;
Copy the code

There are generally two solutions to this situation.

Scheme 1: Unify the character codes of associated fields.

Option 2: If the character encoding cannot be unified, manually CONVERT the function to the right of the association =, to achieve the purpose of character encoding unity, here is to force utF8MB4 to UTF8, of course, conversion from superset to subset is at the risk of data truncation. As follows:

select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 
Copy the code

5. Performing operations on index columns must cause index invalidity

Operations such as +, -, *, /, etc., are as follows:

select * from `user` where age - 1 = 10;
Copy the code

To optimize, put the operation on the value, or calculate it directly in the application, such as:

select * from `user` where age = 10 - 1;
Copy the code

6. The like wildcard may cause index invalidity

A like query that begins with % invalidates the index. There are two solutions:

  • Move % after, as in:
select * from `user` where `name` like 'l %';
Copy the code
  • Use the override index to hit the index.
select name from `user` where `name` like '% % li';
Copy the code

7. In a joint index, where columns violate the leftmost matching principle, which will definitely cause index failure

When creating a joint index, such as (k1,k2,k3), it creates (k1), (k1,k2), and (k1,k2,k3), which is the left-most matching principle.

For example, the following statement will not hit the index:

select * from t where k2=2;
select * from t where k3=3;
slect * from t where k2=2 and k3=3;
Copy the code

The following statement will only hit index (k1):

slect * from t where k1=1 and k3=3;
Copy the code

8. MySQL optimizer’s ultimate choice, do not walk the index

As mentioned above, even if the index is in full effect, it is up to the MySQL optimizer to decide whether to use the index or not, considering the actual amount of data and so on. Of course, you can also specify in the SQL statement to force an index.

Some suggestions for optimizing indexes

  1. Do not index attributes that are frequently updated and not highly differentiated.
  • Updates change the B+ tree, and indexing frequently updated fields can significantly degrade database performance.
  • It is meaningless to create an index for the attribute of “gender”, which is not highly differentiated. It cannot effectively filter data and has similar performance to full table scan.
  1. To build composite indexes, you must put the most distinguished fields first.

reference

“Why do these SQL statements have the same logic but different performance?”

“Backend Programmers must Have: Top 10 Miscellaneous Diseases of Index Failure”

Interpretation of 30 Military rules in 58 Home Database

The MySQL or / / in the union with index optimization | road to the architect,