MySQL index failure principle

1. Reasons for index failure

First, let's look at the situations where the search cannot take advantage of the orderliness of the index.

Suppose a table test has four fields a, b, c, and d, and c is the primary key.

Create a joint index (a,b) on fields a and b CREATE index idx_a_b on test(a,b) test(a,b); B+ tree joint index.JPG

The rule we can get is: sort by field a from small to large first, and if field a is equal, sort by field b from small to large;

Analyze the following situations to determine whether the index will become invalid and the reasons for the invalidation:

The condition only contains the b field

select * from test where b=2;

Index failure:

Obviously, the full text is scanned when going, and the index is not used. Because we only look at the index of field b, which is 2, 4, 1, 3, 4, 5, we cannot use the orderliness of the index to quickly locate the data.

Query the range of field a:

select * from test where a>1 and b=2;

Index failure:

It can be seen that the index is not completely invalid, but the index is first used to locate the position of a. Because the key_len here is 4 , and the key_len of the joint index is 8.

Query the equal value of field a and the range of field b:

Index failure:

It can be seen that using index and key_len is 8, that is, the indexes of both fields are used. This also corresponds to the rule of joint index arrangement: when the a fields are the same, the b fields are arranged in order.

The above situations can be summarized as: failure to comply with the leftmost prefix matching principle results in index failure.

The leftmost matching prefix ensures that the order of index sorting can be utilized. Putting the equal query in front and the range query in the back takes advantage of the feature that [when the prefix fields are equal, the subsequent index fields are ordered], which is the leftmost prefix matching principle in a special sense.

2. Let's take a look at what situations will destroy the orderliness of the index.

- Perform function operations on index fields

Performing function operations on index fields, such as y=f(x), does not guarantee that the obtained values ​​of y are still in order. In this case, the optimizer will abandon the tree search function. However, it is not ruled out that the optimizer will choose to scan this index if it finds that the index tree is much smaller than the primary key index.

- Implicit type conversion

In MySQL, when comparing strings and numbers, the strings are converted to numbers. The essence of implicit type conversion is to use the CAST() function on the index field. The principle is the same as above.

- Implicit character encoding conversion

The essence of string encoding conversion is to use CONVERT() function.

3. Summary

The reason for index failure is that the optimizer finds that it cannot utilize the orderliness of the index. Therefore, when using the index, we should try to meet the leftmost prefix matching principle, put the range query at the end, and do not use fuzzy queries such as %like and %like% , which is to maximize the use of the orderliness of the index. However, in some cases, the optimizer simply abandons the search function of the index tree and may still choose to scan the index.

This is the end of this article about the principle of MySQL index invalidation. For more relevant MySQL index invalidation content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!