Mysql索引失效

mysql建表语句

CREATE TABLE `edu_teacher` (
  `id` char(19) NOT NULL COMMENT '讲师ID',
  `name` varchar(20) NOT NULL COMMENT '讲师姓名',
  `intro` varchar(500) NOT NULL DEFAULT '' COMMENT '讲师简介',
  `career` varchar(500) DEFAULT NULL COMMENT '讲师资历,一句话说明讲师',
  `level` int(10) unsigned NOT NULL COMMENT '头衔 1高级讲师 2首席讲师',
  `avatar` varchar(255) DEFAULT NULL COMMENT '讲师头像',
  `sort` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
  `is_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '逻辑删除 1（true）已删除， 0（false）未删除',
  `gmt_create` datetime NOT NULL COMMENT '创建时间',
  `gmt_modified` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='讲师';

mysql表数据

EXPLAIN详解见：MySQL-Explain详解_mysql explain-CSDN博客

1、隐式转换导致索引失效

分析下面两个查询语句：

EXPLAIN select * from edu_teacher where name = 101

EXPLAIN select * from edu_teacher where name = "101"

        两个查询语句中一个走了索引，一个没有通过索引，是因为存在字段的隐式转换（name字段的类型为varchar类型），未走索引的查询同时进行了全表扫描。

• 当操作符左右两边的数据类型不一致时，会发生隐式转换。
• 当 where 查询操作符左边为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。
• 当 where 查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。
• 字符串转换为数值类型时，非数字开头的字符串会转化为0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。

2、当索引为函数的一部分时会失效

分析下面两个查询语句：

EXPLAIN select * from edu_teacher where DATE(gmt_create) = "2020-09-03";

EXPLAIN select * from edu_teacher where gmt_create = "2020-09-03";

        因为索引作为函数的一部分时，索引的值就会改变。因此，会使得索引失效。索引必须是独立的列：即不能作为表达式的一部分，也不能作为函数的一部分。

3、当发生隐式编码转换的时候，就会造成做引的失效

        两个表进行关联操作，关联的字段是索引字段，但是两个表的编码是不一样的，比如两个表的编码字符集分别是 utf8mb4、utf8, utf8mb4 是 utf8 的超集，所以会自动将 utf8 转成 utf8mb4。