Typical cases of MySQL index failure

Typical Cases
Appendix: Common index failure situations

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.

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: