Mysql索引失效场景

https://www.pudn.com/news/62c7cfde5f75f3409e88fe33.html

1 准备

本次测试数据库版本为8.0.11。

select VERSION();

8.0.11

创建数据库表：

CREATE TABLE `t_user` (
  `id` bigint(11) NOT NULL COMMENT '主键',
  `name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `id_card` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '身份证号',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `IDX_T_USER_NAME` (`name`),
  KEY `IDX_T_USER_ID_CARD` (`id_card`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

导入测试数据：

INSERT INTO `t_user` VALUES ('1', '李四', '11', '11111111', '2022-02-26 14:04:23');
INSERT INTO `t_user` VALUES ('2', '张三', '12', '22222222', '2022-02-26 17:25:26');
INSERT INTO `t_user` VALUES ('3', '王五', '13', '33333333', '2022-02-25 07:07:23');
INSERT INTO `t_user` VALUES ('4', '李华', '14', '44444444', '2022-12-25 09:04:23');

2 失效场景及验证

2.1 模糊查询

模糊查询使用like关键字的时候，模糊匹配的占位符（%）位于条件的首位时，则索引失效。这种情况索引失效的原因很容易理解，索引本身就相当于目录，从左到右逐个排序。而条件的左侧使用了占位符，导致无法按照正常的目录进行匹配，导致索引失效。

示例：

explain select * from t_user where name like '%李%';

explain结果：

如果%不在条件首位，则索引是起作用的：

示例：

explain select * from t_user where name like '李%';

explain结果如下：

2.2 数据类型错误

参数类型与字段类型不匹配，导致类型发生了隐式转换，索引失效。

示例：

explain select * from t_user where id_card = 11111111;

explain结果：

id_card字段类型为varchar，但在SQL语句中使用了int类型，导致全表扫描。出现索引失效的原因是：varchar和int是两个种不同的类型。

这种情况还有一个特例，如果字段类型为int类型，而查询条件添加了单引号或双引号，则Mysql会将其转化为int类型，这种情况下索引是可以生效的：

2.3 索引列使用函数

索引列参与函数处理，会导致全表扫描，索引失效。是因为数据库要先进行全表扫描，获得数据之后再进行截取、计算，导致索引索引失效。同时，还伴随着性能问题

示例：

explain select * from t_user where SUBSTR(id_card,1,3) = '111';

explain结果：

2.4 索引列参与计算

对索引列进行加、减、乘、除等运算时，会导致索引失效

示例，因为id为主键，通过主键索引进行了查询：

explain select * from t_user where id = 1;

如果id列参与运算，如下所示，索引失效：

explain select * from t_user where id + 1 = 2;

针对这种情况，其实不单单是索引的问题，还会增加数据库的计算负担。就以上述SQL语句为例，数据库需要全表扫描出所有的id字段值，然后对其计算，计算之后再与参数值进行比较。如果每次执行都经历上述步骤，性能损耗可想而知。

建议的使用方式是：先在内存中进行计算好预期的值，或者在SQL语句条件的右侧进行参数值的计算。

针对上述示例的优化如下：

-- 内存计算，得知要查询的id为1
explain select * from t_user where id = 1 ;
-- 参数侧计算
explain select * from t_user where id = 2 - 1 ;

2.5 使用OR

查询条件使用or关键字，其中一个字段没有创建索引，则会导致整个查询语句索引失效； or两边为“>”和“<”范围查询时，索引同样失效

示例：

explain select * from t_user where id = 2 or age = 11;

上述示例中，id是有主键索引的，age是没有索引的，由于使用or关键字，id的索引竟然也失效了。

换一个角度来想，如果单独使用age字段作为条件很显然是全表扫描，既然已经进行了全表扫描了，前面id的条件再走一次索引反而是浪费了。所以，在使用or关键字时，切记两个条件都要添加索引，否则会导致索引失效。

2.6 两列做比较

两列数据做比较，即便两列都创建了索引，索引也会失效。

如果两个列数据都有索引，但在查询条件中对两列数据进行了对比操作，则会导致索引失效。

这里举个不恰当的示例，比如id_card小于id这样的两列（真实场景可能是两列同维度的数据比较，这里迁就现有表结构）：

explain select * from t_user where id > id_card;

这里虽然id有索引，id_card也创建了索引，但当两列做比较时，索引还是会失效的

2.7 order by

当查询条件涉及到order by、limit等条件时，是否走索引情况比较复杂，而且与Mysql版本有关，通常普通索引，如果未使用limit，则不会走索引。order by多个索引字段时，可能不会走索引。其他情况，建议在使用时进行expain验证。

示例：

explain select * from t_user order by id_card;

2.8 联合索引不满足最左匹配原则

这里新建一个联合索引，name列和age列的联合索引（IDX_T_USER_NAME_ID_CARD），IDX_T_USER_NAME 和 IDX_T_USER_ID_CARD 索引先删除。

如果根据最左原则进行查询，即name在前，索引生效，如下：

explain select * from t_user WHERE name = '李华';

但是，如果不满足最左原则，则索引失效，如下：

explain select * from t_user WHERE id_card = '111111';