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

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

image-20220120230347886

数据准备

为了模拟场景,我这里准备如下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 '%小黑说';索引失效;

类型转换

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

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

image-20220120203029823

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

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

alter table t_user add index idx_age(age);

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

条件中有函数

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

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

对索引字段进行运算

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

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

所以要避免在查询时索引字段的计算,这两种情况都不推荐,尽量在应用代码中完成计算,再传给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

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

在第一个查询中没有name条件,导致索引失效,全表扫描。

全表扫描更快

MySQ在执行查询语句时,优化器会对扫描数量进行预估,如果认为全表扫描比使用索引更快时,使用的索引字段也会失效。

当然,MySQL也可能会存在误判的情况,如果真的使用索引比全表扫描更快,但是MySQL误判选择全表扫描,可以使用force index强制使用索引,具体问题具体分析。

总结

本质上,MySQL在选择索引时会计算使用索引和不使用索引的成本哪个较低,如果不使用索引成本更低,则会选择全表扫描。

以上是本期的全部内容,主要是对索引失效场景的一个归纳总结,希望对你有所帮助。同时,如果对文中内容有疑问或者你认为有错误的地方,欢迎留言指正。

写文不易,需要一点正反馈,喜欢的话点个赞~

往期推荐文章:

快丢掉BeanUtils吧,MapStruct才是YYDS

🔥 三分钟学会!自动化给她发微信 👩 送春节祝福!

面试官让我设计个LRU缓存,结果…

maven中有重复依赖该怎么搞

淦!做一个短链接系统需要考虑这么多

小黑的专栏:

小黑说优质文章合集

【跟着小黑学设计模式】

【并发编程系列】

【Maven系列】

我是小黑,一名在互联网“苟且”的程序员

流水不争先,贵在滔滔不绝

分类: MySQL

0 条评论

发表评论

Avatar placeholder

您的电子邮箱地址不会被公开。