目录前言创建测试表和数据索引失效情况1:非最左匹配索引失效情况2:错误模糊查询索引失效情况3:列运算索引失效情况4:使用函数索引失效情况5:类型转换索引失效情况6:使用 is not
为了验证 Mysql 中哪些情况下会导致索引失效,我们可以借助 explain 执行计划来分析索引失效的具体场景。
explain 使用如下,只需要在查询的 sql 前面添加上 explain 关键字即可,如下图所示:
而以上查询结果的列中,我们最主要观察 key 这一列,key 这一列表示实际使用的索引,如果为 NULL 则表示未使用索引,反之则使用了索引。
以上所有结果列说明如下:
其中最重要的就是 type 字段,type 值类型如下:
const — 将一个主键放置到 where 后面作为条件查询, mysql 优化器就能把这次查询优化转化为一个常量,如何转化以及何时转化,这个取决于优化器,这个比 eq_ref 效率高一点。
为了演示和测试那种情况下会导致索引失效,我们先创建一个测试表和相应的数据:
-- 创建表
drop table if exists student;
create table student(
id int primary key auto_increment comment '主键',
sn varchar(32) comment '学号',
name varchar(250) comment '姓名',
age int comment '年龄',
sex bit comment '性别',
address varchar(250) comment '家庭地址',
key idx_address (address),
key idx_sn_name_age (sn,name,age)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 添加测试数据
insert into student(id,sn,name,age,sex,address)
values(1,'cn001','张三',18,1,'高老庄'),
(2,'cn002','李四',20,0,'花果山'),
(3,'cn003','王五',50,1,'水帘洞');
当前表中总共有 3 个索引,如下图所示:
PS:本文以下内容基于 MySQL 5.7 InnoDB 数据引擎下。
最左匹配原则指的是,以最左边的为起点字段查询可以使用联合索引,否则将不能使用联合索引。 我们本文的联合索引的字段顺序是 sn + name + age,我们假设它们的顺序是 A + B + C,以下联合索引的使用情况如下:
从上述结果可以看出,如果是以最左边开始匹配的字段都可以使用上联合索引,比如:
其中:A 等于字段 sn,B 等于字段 name,C 等于字段 age。
而 B+C 却不能使用到联合索引,这就是最左匹配原则。
模糊查询 like 的常见用法有 3 种:
而这 3 种模糊查询中只有第 1 种查询方式可以使用到索引,具体执行结果如下:
如果索引列使用了运算,那么索引也会失效,如下图所示:
查询列如果使用任意 MySQL 提供的函数就会导致索引失效,比如以下列使用了 ifnull 函数之后的执行计划如下:
如果索引列存在类型转换,那么也不会走索引,比如 address 为字符串类型,而查询的时候设置了 int 类型的值就会导致索引失效,
如下图所示:
当在查询中使用了 is not null 也会导致索引失效,而 is null 则会正常触发索引的,
如下图所示:
导致 MySQL 索引失效的常见场景有以下 6 种:
到此这篇关于哪些情况会导致 MySQL 索引失效的文章就介绍到这了,更多相关MySQL 索引失效内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网!
--结束END--
本文标题: 哪些情况会导致 MySQL 索引失效
本文链接: https://www.lsjlt.com/news/153452.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0