What are the situations in which MySQL causes index failure?

1. Preparation work

First prepare two tables for demonstration:

CREATE TABLE `student_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `course_id` int NOT NULL,
  `class_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;

CREATE TABLE `course` (
  `id` int NOT NULL AUTO_INCREMENT,
  `course_id` int NOT NULL,
  `course_name` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;

#准备数据
select count(*) from student_info;#1000000
select count(*) from course;      #100

2. Index invalidation rules

1. Prioritize the use of joint indexes

The following sql statement does not have an index:

#平均耗时291毫秒
select * from student_info where name='123' and course_id=1 and class_id=1;

We optimize its query efficiency by creating an index. There are several options as follows:

① Create an ordinary index:

#建立普通索引
create index idx_name on student_info(name);
#平均耗时25毫秒，查看explain执行计划，使用到的是idx_name索引查询
select * from student_info where name='MOKiKb' and course_id=1 and class_id=1;

② On the basis of the ordinary index, add a joint index:

#name，course_id组成的联合索引
create index idx_name_courseId on student_info(name,course_id);
#该查询语句一般使用的是联合索引，而不是普通索引，具体看优化器决策
#平均耗时20ms
select * from student_info where name='zhangsan' and course_id=1 and class_id=1;

As you can see, when multiple indexes can be used, the system generally gives priority to using longer joint indexes, because joint indexes are faster in comparison, this should also be the case It is easy to understand, The premise is to comply with the leftmost matching principle of the joint index.

If you create a joint index composed of name, course_id, class_id, then the above sql statement will use this joint index with a longer key_len as expected (unexpectedly, the optimizer may choose other better solutions, If it were faster).

The speed of joint index is not necessarily better than that of ordinary index. For example, if the first condition filters all records, then there is no need to use a subsequent index.

2. Leftmost matching principle

#删除前例创建的索引，新创建三个字段的联合索引，name-course_id-cass_id
create index idx_name_cou_cls on student_info(name,course_id,class_id);

① Situation when all joint indexes match:

#关联字段的索引比较完整
explain select * from student_info where name='11111' and course_id=10068 and class_id=10154;

Each field condition matches the joint index, so this SQL statement follows the leftmost prefix rule. The use of a joint index allows for fast lookups and avoids additional queries, so this is the optimal situation.

②The rightmost part of the joint index is missing:

explain select * from student_info where name='11111' and course_id=10068;

The sql statement condition does not contain all of the joint index condition, but erased the right half. The index used by this statement is still the related query, but only part of it is used. By looking at key_len, we can know that 5 bytes are missing. These 5 bytes correspond to class_id, which proves class_id is not in effect (it is not in where, so of course it is not used).

Similarly, if you erase the course_id field in where, the joint index will still take effect, but key_len will be reduced.

③The situation of missing in the joint index:

#联合索引中间的字段未使用，而左边和右边的都存在
explain select * from student_info where name='11111' and class_id=10154;;

The above sql statement still uses the joint index, but its key_len It has become smaller. Only the name field uses the index. Although the class_id field is in the joint index, it is GG because it does not comply with the leftmost matching principle.

The execution process of the entire sql statement is: first find all the records with the name 11111 in the B-tree of the joint index, and then filter out the full text of these records whose class_id is not 10154. With one more step of full-text search, the performance will be worse than in ① and ②.

④The situation where the leftmost part of the joint index is missing:

explain select * from student_info where class_id=10154 and course_id=10068;

This situation is a special case of the previous situation. The leftmost part of the joint index is missing. The field on the left was not found, so although there are other parts, they are all invalid and full-text search is used.

Conclusion: The leftmost matching principle means that the query starts from the leftmost column of the index, and the columns in the index cannot be skipped. If a column is skipped, the index will be partially invalid (the following All field indexes are invalid).

Note: When creating a joint index, the order of the fields is fixed, and the leftmost match is compared according to this order; but in the query statement, the order of the fields in the where condition is optional. Changing means that there is no need to follow the order of the associated index fields, as long as there is one in the where condition.

3. The column index on the right side of the range condition is invalid

Take over the above joint index and use the following sql query:

#key_len=> name:63,course_id:5,class_id:5
explain select * from student_info where name='11111' and course_id>1 and class_id=1;

key_len is only 68, which means that the class_id in the associated index is not used. Although it conforms to the leftmost matching principle, the > symbol makes the index on the right side of the condition field in the associated index invalid .

But if you use the >= sign:

#不是>、<,而是>=、<=
explain select * from student_info where name=&#39;11111&#39; and course_id>=20 and course_id<=40 and class_id=1;

The index on the right is not invalid, key_len is 73, and the indexes of all fields are used.

结论：为了充分利用索引，我们有时候可以将>、=、的条件的字段尽量放在关联索引靠后的位置。

4.计算、函数导致索引失效

#删除前面的索引，新创建name字段的索引，方便演示
create index idx_name on student_info(name);

现有一个需求，找出name为li开头的学生信息：

#使用到了索引
explain select * from student_info where name like 'li%';
#未使用索引，花费时间更久
explain select * from student_info where LEFT(name,2)='li';

上面的两条sql语句都可以满足需求，然而第一条语句用了索引，第二条没有，一点点的改变真是天差地别。

结论：字段使用函数会让优化器无从下手，B树中的值和函数的结果可能不搭边，所以不会使用索引，即索引失效。字段能不用就不用函数。

类似：

#也不会使用索引
explain select * from student_info where name+''='lisi';

类似的对字段的运算也会导致索引失效。

5.类型转换导致索引失效

#不会使用name的索引
explain select * from student_info where name=123;
#使用到索引
explain select * from student_info where name='123';

如上，name字段是VARCAHR类型的，但是比较的值是INT类型的，name的值会被隐式的转换为INT类型再比较，中间相当于有一个将字符串转为INT类型的函数。

6.不等于(!= 或者)索引失效

#创建索引
create index idx_name on student_info(name);
#索引失效
explain select * from student_info where name<>&#39;zhangsan&#39;;
explain select * from student_info where name!=&#39;zhangsan&#39;;

不等于的情况是不会使用索引的。因为!=代表着要进行全文的查找，用不上索引。

7.is null可以使用索引，is not null无法使用索引

#可以使用索引
explain select * from student_info where name is null;
#索引失效
explain select * from student_info where name is not null;

和前一个规则类似的，!=null。同理not like也无法使用索引。

最好在设计表时设置NOT NULL约束，比如将INT类型的默认值设为0，将字符串默认值设为''。

8.like以%开头，索引失效

#使用到了索引
explain select * from student_info where name like 'li%';
#索引失效
explain select * from student_info where name like '%li';

只要以%开头就无法使用索引，因为如果以%开头，在B树排序的数据中并不好找。

9.OR前后存在非索引的列，索引失效

#创建好索引
create index idx_name on student_info(name);
create index idx_courseId on student_info(course_id);

如果or前后都是索引：

#使用索引
explain select * from student_info where name like 'li%' or course_id=200;

 如果其中一个没有索引：

explain select * from student_info where name like 'li%' or class_id=1;

那么索引就失效了，假设还是使用索引，那就变成了先通过索引查，然后再根据没有的索引的字段进行全表查询，这种方式还不如直接全表查询来的快。

10.字符集不统一

字符集如果不同，会存在隐式的转换，索引也会失效，所有应该使用相同的字符集，防止这种情况发生。

三、建议

• 对于单列索引，尽量选择针对当前query过滤性更好的索引

• 在选择组合索引时，query过滤性最好的字段应该越靠前越好

• 在选择组合索引时，尽量选择能包含当前query中where子句中更多字段的索引

• 在选择组合索引时，如果某个字段可能出现范围查询，尽量将它往后放

The above is the detailed content of What are the situations in which MySQL causes index failure?. For more information, please follow other related articles on the PHP Chinese website!