MySQL index failure principle

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!

You may also be interested in:
  • Detailed explanation of MySQL database indexes and failure scenarios
  • Common scenarios and avoidance methods for index failure in MySQL
  • MySQL joint index effective conditions and index invalid conditions
  • Several methods to solve the problem of MySQL fuzzy query index failure
  • Share 15 scenarios where MySQL indexes fail

<<:  How to implement DIV's blur function

>>:  7 interesting ways to achieve hidden elements in CSS

Recommend

HTML table_Powernode Java Academy

To draw a table in HTML, use the table tag tr me...

Learn asynchronous programming in nodejs in one article

Table of Contents Introduction Synchronous Asynch...

How to make your own native JavaScript router

Table of contents Preface Introduction JavaScript...

Using react+redux to implement counter function and problems encountered

Redux is a simple state manager. We will not trac...

8 examples of using killall command to terminate processes in Linux

The Linux command line provides many commands to ...

Linux traceroute command usage detailed explanation

Traceroute allows us to know the path that inform...

In-depth analysis of MySQL query interception

Table of contents 1. Query Optimization 1. MySQL ...

JavaScript Basics Objects

Table of contents 1. Object 1.1 What is an object...

Simple web page code used in NetEase blog

How to use the code in NetEase Blog: First log in...

Detailed explanation of the use of props in React's three major attributes

Table of contents Class Component Functional Comp...

Understanding JavaScript prototype chain

Table of contents 1. Understanding the Equality R...

Deep understanding of line-height and vertical-align

Several concepts Line box: A box that wraps an in...

HTML table tag tutorial (46): table footer tag

The <tfoot> tag is used to define the style...

Example code for converting Mysql query result set into JSON data

Mysql converts query result set into JSON data Pr...