Common scenarios and avoidance methods for index failure in MySQL

Preface

I have read many similar articles before, which mentioned that improper use of some SQL statements will cause MySQL indexes to fail. There are also some MySQL "military regulations" or specifications that state that certain SQL cannot be written in this way, otherwise the index will be invalid.

I agree with most of the content, but I think the wording in some examples is too absolute and the reasons are not explained, so many people don’t know why. Therefore, I will definitely sort out the common scenarios of index failure in MySQL again and analyze the reasons for your reference.

Of course remember that explaining is a good habit!

Common scenarios for MySQL index failure

When verifying the following scenario, please prepare enough data. When the amount of data is small, the MySQL optimizer may sometimes determine that a full table scan is harmless and will not hit the index.

1. When the where statement contains or, the index may become invalid

Using or does not necessarily invalidate the index. You need to check whether the query columns on the left and right sides of or hit the same index.

Assume that the user_id column in the USER table has an index, but the age column has no index.

The following statement actually hits the index (it is said that this is only possible with newer versions of MySQL. If you are using an older version of MySQL, you can use explain to verify it).

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

However, this statement cannot hit the index.

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

Assuming that the age column also has an index, the index still cannot be hit.

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

Therefore, it is recommended to avoid using the or statement as much as possible. You can use union all or in instead according to the situation. The execution efficiency of these two statements is better than or.

2. Negative query is used in the index column in the where statement, which may cause the index to fail

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

A certain "military regulation" states that using negative queries will definitely cause the index to become invalid. The author checked some articles and found that some netizens refuted this point and provided evidence.

In fact, negative queries do not necessarily lead to index failure. This depends on the judgment of the MySQL optimizer, which one has lower cost, full table scan or index.

3. The index field can be null. Using is null or is not null may cause the index to fail.

In fact, a single index field can hit the index when using is null or is not null. However, netizens said in their evidence that when two different index fields are connected with or, the index becomes invalid. I think the index is indeed invalid, but the blame should be put on or. This belongs to the first scenario~~

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;

However, some "military regulations" and specifications emphasize that fields should be set to not null and provide default values, which is worth referring to for a reason.

• Null columns make indexes/index statistics/value comparisons more complicated and more difficult for MySQL to optimize.
• This type of null requires special processing within MySQL, which increases the complexity of database record processing. Under the same conditions, when there are many empty fields in the table, the database processing performance will be greatly reduced.
• Null values ​​require more storage space, and the null column in each row in both the table and the index requires additional space to identify it.
• When dealing with null, you can only use is null or is not null, and you cannot use operation symbols such as =, in, <, <>, !=, and not in. For example: where name!='shenjian', if there are records where name is null, the query results will not include records where name is null.

4. Using built-in functions on index columns will definitely cause the index to fail

For example, in the following statement, a function is used on the index column login_time, which will invalidate the index:

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

Optimization suggestion: Try to perform calculations and conversions in the application.

In fact, there are two index failure scenarios mentioned by netizens, both of which should be attributed to the use of functions in the index column.

4.1 Index invalidation caused by implicit type conversion

For example, in the following statement, the index column user_id is of varchar type and will not hit the index:

select * from `user` where user_id = 12;

This is because MySQL performs implicit type conversion and converts user_id by calling the function.

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

4.2 Index Invalidation Caused by Implicit Character Encoding Conversion

When performing a join query between two tables, if the character encodings of the fields in the two tables are inconsistent, MySQL may call the CONVERT function to implicitly convert the different character encodings to achieve uniformity. When it acts on the associated fields, the index will become invalid.

For example, in the following statement, the character encoding of d.tradeid is utf8, while the character encoding of l.tradeid is utf8mb4. Because utf8mb4 is a superset of utf8, MySQL uses CONVERT to convert utf8 to utf8mb4 when doing the conversion. Simply put, CONVERT acts on d.tradeid, so the index is invalid.

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

There are generally two solutions to this situation.

Solution 1: Unify the character encoding of related fields.

Solution 2: When it is really impossible to unify the character encoding, manually apply the CONVERT function to the right side of the = in the association to achieve the purpose of unifying the character encoding. Here, utf8mb4 is forced to be converted to utf8. Of course, there is a risk of data truncation when converting from a superset to a subset. as follows:

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

5. Operations on index columns will definitely cause the index to become invalid

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

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

To optimize, you need to put the operation on the value, or calculate it directly in the application, for example:

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

6. Like wildcards may cause index failure

When a LIKE query starts with %, the index will become invalid. There are two solutions:

Move the % to the back, such as:

select * from `user` where `name` like '李%';

Use covering indexes to hit the index.

select name from `user` where `name` like '%李%';

7. In a joint index, if the index column in the where clause violates the leftmost matching principle, the index will definitely become invalid.

When creating a joint index, such as (k1, k2, k3), it is equivalent to creating three indexes: (k1), (k1, k2) and (k1, k2, k3). This is the leftmost 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;

The following statement will only hit index (k1):

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

8. The final choice of MySQL optimizer is not to use index

As mentioned above, even if the scenario fully meets the requirements for index effectiveness, whether the index is ultimately used depends on the judgment of the MySQL optimizer, taking into account the actual data volume and other factors. Of course, you can also specify in the SQL statement to force a certain index.

Some suggestions for optimizing indexes

• Do not create indexes on attributes that are updated very frequently and have low differentiation.
  • Updates will change the B+ tree, and indexing frequently updated fields will greatly reduce database performance.
  • There is no point in creating an index for a property like "gender" that has little differentiation. It cannot effectively filter data, and the performance is similar to that of a full table scan.
• When creating a combined index, the fields with high discrimination must be placed in front.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.

refer to

Why do these SQL statements have the same logic but huge differences in performance? 》

《A must-have for backend programmers: Top 10 causes of index failure》

"Interpretation of 30 Military Rules of 58 Daojia Database"

《MySQL or/in/union and index optimization | The road to becoming an architect》