What is the principle of MySQL index failure?

This article mainly explains "what is the principle of MySQL index failure". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the principle of MySQL index failure".

1. The cause of index failure

First, take a look at the circumstances that will cause lookups to fail to take advantage of the ordering of the index.

Suppose there are four fields in a table test, c is the primary key.

Create a federated index on the aformab field (ameme b): CREATE index idx_a_b on test (arecine b); B + tree federated index .JPG

The rule that can be obtained is that priority is sorted according to a field from small to large, and equal a field is sorted by b field from small to large.

Analyze the following situations, whether the index will fail and why it will fail:

The condition contains only b fields

Select * from test where baggage 2

Index invalidation:

Obviously, the full-text scan did not use the index when leaving. Because only looking at the index of the b field, it is 2Magne 4Jing 1Jing 3Jing 4pm 5, so we can not make use of the order of the index to locate quickly.

Query the range of a field:

Select * from test where a > 1 and baggage 2

Index invalidation:

As you can see, the index is not completely invalid, but first uses the index to locate the location of a. Because the key_len here is 4, while the key_len of the federated index is 8.

Query for a field equivalent, b field range query:

Index invalidation:

You can see that it is using index and key_len is 8, that is, the indexes of both fields are used, which corresponds to the law of joint index arrangement: when a fields are the same, b fields are arranged in order.

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

The leftmost matching prefix ensures that the ordering of index sorting can be taken advantage of, while putting the equivalent query in front and the range query in the back takes advantage of the characteristic that the index fields are ordered when the prefix fields are equal. It is the leftmost prefix matching principle in a special sense.

2. Let's take a look at what will break the order of the index.

-perform functional operations on index fields

Functional operations on index fields, such as yquof (x), do not guarantee that the resulting values of y are still ordered. In this case, the optimizer will abandon the tree search function, but does not rule out the possibility that the optimizer will choose to scan the index if it finds that the index tree is much smaller than the primary key index.

-implicit type conversion

In MySQL, when comparing a string with a number, it converts a string into a number. The essence of implicit type conversion is the use of the CAST () function on the index field, with the same principle as above.

-implicit character coding conversion

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

Thank you for your reading, these are the contents of "what is the principle of MySQL index failure". After the study of this article, I believe you have a deeper understanding of what the principle of MySQL index failure is, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!