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 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 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 - Implicit type conversionIn 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 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 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:
|
<<: How to implement DIV's blur function
>>: 7 interesting ways to achieve hidden elements in CSS
In summary: 1. Consider performance when designin...
Set Tomcat to automatically start the service: I ...
Problems: After adding the -v parameter to docker...
MySQL SQL statement performance tuning simple exa...
Prerequisites for installing MySQL: Install CentO...
Table of contents 1. What is Ts 2. Basic Grammar ...
Forwarding between two different servers Enable p...
Table of contents 1. Form events 2. Mouse events ...
This article records the detailed installation pr...
Recently, when I was learning docker, I found tha...
(1) Experimental environment youxi1 192.168.5.101...
Run cmd with administrator privileges slmgr /ipk ...
1. Environmental Preparation The IP address of ea...
The JD carousel was implemented using pure HTML a...
1. Find the mysql image docker ps 2. Enter the mi...