知道MySQL索引会失效，但是你能说全吗？

原创

小黑说Java 2022-03-10 14:25:40 ©著作权

文章标签 后端 MySQL 字段 mysql 联合索引 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者小黑说Java的原创作品，请联系作者获取转载授权，否则将追究法律责任

我们都知道在数据库查询优化时，有一种非常重要且有效的优化，就是索引优化。在很多情况下，如果查询语句编写不当，会导致使用了索引字段进行查询，但是实际执行时索引失效。并且在面试时，索引失效的相关的问题也会高频出现。

本期内容，小黑给大家归纳一下，索引失效主要可以归纳为以下场景。

知道MySQL索引会失效，但是你能说全吗？_字段

数据准备

为了模拟场景，我这里准备如下t_user表：

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `gender` char(1) DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
复制代码

除了主键索引，还给name字段建了索引inx_name。

然后插入如下数据：

insert into `t_user` (`id`, `name`, `age`, `birthdate`) values('1','吴彦祖','10','2022-01-20');
insert into `t_user` (`id`, `name`, `age`, `birthdate`) values('2','小黑说Java','18','2022-01-20');
insert into `t_user` (`id`, `name`, `age`, `birthdate`) values('3','Jay.chou','10','2022-01-20');
insert into `t_user` (`id`, `name`, `age`, `birthdate`) values('4','马尔扎哈','20','2022-01-20');
insert into `t_user` (`id`, `name`, `age`, `birthdate`) values('5','唔西迪西','18','2022-01-20');
insert into `t_user` (`id`, `name`, `age`, `birthdate`) values('6','古力娜扎','20','2022-01-20');
复制代码

索引失效场景

模糊查询

并不是所有的模糊查询都会失效，只有当模糊查询以%开头时，索引字段才会失效。

EXPLAIN SELECT * FROM t_user WHERE NAME LIKE '%小黑说';索引失效；

知道MySQL索引会失效，但是你能说全吗？_字段_02

类型转换

比如在查询条件中，如果字段的数据类型是字符型，但是查询时使用数字，导致在数据库中需要进行类型转换，将数字转换成字符，也会导致索引失效。

比如我们要查询name='12'的用户。（实际场景中可能姓名不会是12，这里主要理解意思）

知道MySQL索引会失效，但是你能说全吗？_字段_03

但是要注意，如果字段是数字类型，使用字符数据作为条件查询，并不会让索引失效。

这里我们给age字段添加上索引。

alter table t_user add index idx_age(age);
复制代码

age字段是int类型，使用age='20'查询，还是会走索引。

知道MySQL索引会失效，但是你能说全吗？_联合索引_04

条件中有函数

在查询条件中对索引字段使用内部函数，也会使索引失效。

比如对name字段使用substr函数切割后再进行比较。

知道MySQL索引会失效，但是你能说全吗？_mysql_05

对索引字段进行运算

如果在查询条件中对索引字段进行运算，也会导致索引失效。

知道MySQL索引会失效，但是你能说全吗？_联合索引_06

对于图中的两种场景，如果在等号左边对索引字段进行运算会导致索引失效，在等号右边的常量运算结果和索引字段比较则不会导致索引失效。

所以要避免在查询时索引字段的计算，这两种情况都不推荐，尽量在应用代码中完成计算，再传给MySQL。

不符合最左匹配原则

最左匹配原则是指在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

所以，如果在查询时没有联合索引的最左边字段，同样会导致索引失效。

这里我们可以将t_user表重建：

CREATE TABLE `t_user` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(32) DEFAULT NULL,
    `age` int(11) DEFAULT NULL,
    `gender` char(1) DEFAULT NULL,
    `birthdate` date DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

CREATE TABLE `t_user` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `name` varchar(32) DEFAULT NULL,    `age` int(11) DEFAULT NULL,    `gender` char(1) DEFAULT NULL,    `birthdate` date DEFAULT NULL,    PRIMARY KEY (`id`),    KEY `idx_name_age` (`name`,`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4复制代码

在t_user表中除了主键索引外，只有一个联合索引idx_name_age。

知道MySQL索引会失效，但是你能说全吗？_联合索引_07