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

MySQL optimization strategy (recommended)

In summary: 1. Consider performance when designin...

How to set Tomcat as an automatically started service? The quickest way

Set Tomcat to automatically start the service: I ...

MySQL SQL statement performance tuning simple example

MySQL SQL statement performance tuning simple exa...

CentOS7 64-bit installation mysql graphic tutorial

Prerequisites for installing MySQL: Install CentO...

Share 13 basic syntax of Typescript

Table of contents 1. What is Ts 2. Basic Grammar ...

How to use firewall iptables strategy to forward ports on Linux servers

Forwarding between two different servers Enable p...

Detailed explanation of using javascript to handle common events

Table of contents 1. Form events 2. Mouse events ...

MySQL 5.7.23 installation and configuration graphic tutorial

This article records the detailed installation pr...

Solve the problem of ifconfig being unavailable in docker

Recently, when I was learning docker, I found tha...

Implementation of Nginx load balancing cluster

(1) Experimental environment youxi1 192.168.5.101...

Windows Service 2016 Datacenter\Stand\Embedded Activation Method (2021)

Run cmd with administrator privileges slmgr /ipk ...

Implementation of LNMP for separate deployment of Docker containers

1. Environmental Preparation The IP address of ea...

Pure HTML and CSS to achieve JD carousel effect

The JD carousel was implemented using pure HTML a...

Modify the maximum number of mysql connections and configuration files in docker

1. Find the mysql image docker ps 2. Enter the mi...