Typical cases of MySQL index failure

Typical cases of MySQL index failure

Typical Cases

There are two tables with the following structure:

CREATE TABLE `student_info` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

CREATE TABLE `student_score` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

One is the info table, and the other is the score table. The score table has one more score field than the info table.

Insert data:

mysql> insert into student_info values ​​(1,'zhangsan'),(2,'lisi'),(3,'wangwu'),(4,'zhaoliu');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> insert into student_score values ​​(1,'zhangsan',60),(2,'lisi',70),(3,'wangwu',80),(4,'zhaoliu',90);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from student_info;
+----+----------+
| id | name |
+----+----------+
| 2 | lisi |
| 3 | wangwu |
| 1 | zhangsan |
| 4 | zhaoliu |
+----+----------+
4 rows in set (0.00 sec)

mysql> select * from student_score;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | zhangsan | 60 |
| 2 | lisi | 70 |
| 3 | wangwu | 80 |
| 4 | zhaoliu | 90 |
+----+----------+-------+
4 rows in set (0.00 sec)

When we execute the following statement:

mysql> explain select B.*
        from
        student_info A,student_score B
        where A.name=B.name and A.id=1;
+----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | A | NULL | const | PRIMARY,idx_name | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | B | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where |
+----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

Why is there an index on B.name, but when selecting table B for the second time in the execution plan, the index is not used and a full table scan is used instead? ? ?

Analysis:

This SQL will perform three steps:

1. First filter the records with A.id=1, use the primary key index, and scan only one row of LA

2. Find the value of name "zhangsan" from the LA row.

3. Search in Table B according to the value of LA.name, find the same value zhangsan, and return it.

Among them, the third step can be simplified as follows:

select * from student_score where name=$LA.name

Here, because LA is the content in table A info, and the character set of table info is utf8mb4, and the character set of table B score is utf8.

so

When executed, it is equivalent to comparing a left value of type utf8 with a right value of type utf8mb4. Because utf8mb4 completely contains the utf8 type (long bytes contain short bytes), MySQL will convert utf8 to utf8mb4 (not reverse conversion, mainly to prevent data truncation).

Therefore, it is equivalent to executing:

select * from student_score where CONVERT(name USING utf8mb4)=$LA.name

As we know, once an implicit type conversion is used in an index field, the index becomes invalid and the MySQL optimizer will use a full table scan to execute the SQL.

To solve this problem, there are two ways:

a. Modify the character set.

b. Modify the SQL statement.

Here is a method to modify the character set:

mysql> alter table student_score modify name varchar(10) character set utf8mb4;
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> explain select B.* from student_info A,student_score B where A.name=B.name and A.id=1;
+----+-------------+-------+------------+-------+------------------+----------+---------+-------+------+------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+------------------+----------+---------+-------+------+------+------+
| 1 | SIMPLE | A | NULL | const | PRIMARY,idx_name | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | B | NULL | ref | idx_name | idx_name | 43 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+------------------+----------+---------+-------+------+------+------+
2 rows in set, 1 warning (0.01 sec)

You can try to modify the SQL method yourself.

Appendix: Common index failure situations

1. When using a function on a column, the index of the column will not take effect.

2. When performing operations on a column (+, -, *, /, !, etc.), the index of the column will not take effect.

3. In some cases, the LIKE operation will not work for the column index.

4. In some cases, when using reverse operations, the index of the column will not work.

5. When using OR in WHERE, if one column has no index, the indexes of other columns will not work.

6. Implicit conversion causes index invalidation. This should be taken seriously. It is also a common mistake in development.

7. When using statements such as not in and not exist.

8. When the variable is a times variable, and the field of the table is a date variable, or vice versa.

9. When the B-tree index is null, it will not fail. When using is not null, it will fail. The bitmap index is null and is not null will both fail.

10. The combined index is not null will become invalid as long as the index columns are created (in no particular order).

The above is the details of a typical case of MySQL index failure. For more information about MySQL index failure, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • MySQL table return causes index invalidation case explanation
  • Several methods to solve the problem of MySQL fuzzy query index failure
  • Analysis of several situations where MySQL index fails
  • Detailed analysis of several situations in which MySQL indexes fail
  • Summary of several situations in which MySQL indexes fail
  • Analysis of five situations of MySQL index failure
  • Analysis of several situations where Mysql indexes fail
  • Summary of the top ten problems of MySQL index failure

<<:  Ideas and codes for implementing iframe in html to control the refresh of parent page

>>:  How to design a web page? How to create a web page?

Recommend

Detailed explanation of MySQL monitoring tool mysql-monitor

1. Overview mysql-monitor MYSQL monitoring tool, ...

MySQL Installer Community 5.7.16 installation detailed tutorial

This article records the detailed tutorial of MyS...

A brief analysis of MySQL's lru linked list

1. Briefly describe the traditional LRU linked li...

Detailed description of shallow copy and deep copy in js

Table of contents 1. js memory 2. Assignment 3. S...

Detailed explanation of the principle of js Proxy

Table of contents What is Proxy Mode? Introducing...

MySQL reports an error: Can't find file: './mysql/plugin.frm' solution

Find the problem Recently, I found a problem at w...

What is a MySQL tablespace?

The topic I want to share with you today is: &quo...

Solution for mobile browsers not supporting position: fix

The specific method is as follows: CSS Code Copy ...

React implements dynamic pop-up window component

When we write some UI components, if we don't...

Solution for multiple Docker containers not having the same port number

Background In Docker, four containers are created...

Application of Beautiful Style Sheets in XHTML+CSS Web Page Creation

This is an article written a long time ago. Now it...

Vue scaffolding learning project creation method

1. What is scaffolding? 1. Vue CLI Vue CLI is a c...

Example of utf8mb4 collation in MySQL

Common utf8mb4 sorting rules in MySQL are: utf8mb...

Some tips for writing high-performance HTML applications

How can you improve web page performance? Most de...