网站首页 > 技术文章 正文
为了验证 MySQL 中哪些情况下会导致索引失效,我们可以借助 explain 执行计划来分析索引失效的具体场景。
explain 使用如下,只需要在查询的 SQL 前面添加上 explain 关键字即可,如下图所示:
而以上查询结果的列中,我们最主要观察 key 这一列,key 这一列表示实际使用的索引,如果为 NULL 则表示未使用索引,反之则使用了索引。
以上所有结果列说明如下:
- id — 选择标识符,id 越大优先级越高,越先被执行;
- select_type — 表示查询的类型;
- table — 输出结果集的表;
- partitions — 匹配的分区;
- type — 表示表的连接类型;
- possible_keys — 表示查询时,可能使用的索引;
- key — 表示实际使用的索引;
- key_len — 索引字段的长度;
- ref— 列与索引的比较;
- rows — 大概估算的行数;
- filtered — 按表条件过滤的行百分比;
- Extra — 执行情况的描述和说明。
其中最重要的就是 type 字段,type 值类型如下:
- all — 扫描全表数据;
- index — 遍历索引;
- range — 索引范围查找;
- index_subquery — 在子查询中使用 ref;
- unique_subquery — 在子查询中使用 eq_ref;
- ref_or_null — 对 null 进行索引的优化的 ref;
- fulltext — 使用全文索引;
- ref — 使用非唯一索引查找数据;
- eq_ref — 在 join 查询中使用主键或唯一索引关联;
- 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 数据引擎下。
索引失效情况1:非最左匹配
最左匹配原则指的是,以最左边的为起点字段查询可以使用联合索引,否则将不能使用联合索引。
我们本文的联合索引的字段顺序是 sn + name + age,我们假设它们的顺序是 A + B + C,以下联合索引的使用情况如下:
从上述结果可以看出,如果是以最左边开始匹配的字段都可以使用上联合索引,比如:
- A+B+C
- A+B
- A+C
其中:A 等于字段 sn,B 等于字段 name,C 等于字段 age。
而 B+C 却不能使用到联合索引,这就是最左匹配原则。
索引失效情况2:错误模糊查询
模糊查询 like 的常见用法有 3 种:
- 模糊匹配后面任意字符:like '张%'
- 模糊匹配前面任意字符:like '%张'
- 模糊匹配前后任意字符:like '%张%'
而这 3 种模糊查询中只有第 1 种查询方式可以使用到索引,具体执行结果如下:
索引失效情况3:列运算
如果索引列使用了运算,那么索引也会失效,如下图所示:
索引失效情况4:使用函数
查询列如果使用任意 MySQL 提供的函数就会导致索引失效,比如以下列使用了 ifnull 函数之后的执行计划如下:
索引失效情况5:类型转换
如果索引列存在类型转换,那么也不会走索引,比如 address 为字符串类型,而查询的时候设置了 int 类型的值就会导致索引失效,如下图所示:
索引失效情况6:使用 is not null
当在查询中使用了 is not null 也会导致索引失效,而 is null 则会正常触发索引的,如下图所示:
总结
导致 MySQL 索引失效的常见场景有以下 6 种:
- 联合索引不满足最左匹配原则。
- 模糊查询最前面的为不确定匹配字符。
- 索引列参与了运算。
- 索引列使用了函数。
- 索引列存在类型转换。
- 索引列使用 is not null 查询。
猜你喜欢
- 2024-10-28 MySQL查询为什么没走索引?这篇文章带你全面解析
- 2024-10-28 什么情况会导致 MySQL 索引失效?(mysql什么情况下会导致索引失效)
- 2024-10-28 用了索引一定就有用吗?如何排查?(使用索引)
- 2024-10-28 MySQL 索引优化分析:为啥你的SQL慢?为啥你建的索引常失效?
- 2024-10-28 MySQL基础(索引分析和使用)(mysql各种索引的使用场景)
- 2024-10-28 MySQL基础(其它SQL优化)(mysql数据库优化及sql调优)
- 2024-10-28 除了网络问题之外,即使对于数据量较小的表
- 2024-10-28 研究了 4.7 个小时终于了解到了索引使用了却没变快的原因
- 2024-10-28 MySQL索引失效带来的性能瓶颈:如何解决这个棘手问题?
- 2024-10-28 常见mysql索引失效条件(常见mysql索引失效条件是什么)
- 1517℃桌面软件开发新体验!用 Blazor Hybrid 打造简洁高效的视频处理工具
- 594℃Dify工具使用全场景:dify-sandbox沙盒的原理(源码篇·第2期)
- 521℃MySQL service启动脚本浅析(r12笔记第59天)
- 489℃服务器异常重启,导致mysql启动失败,问题解决过程记录
- 489℃启用MySQL查询缓存(mysql8.0查询缓存)
- 477℃「赵强老师」MySQL的闪回(赵强iso是哪个大学毕业的)
- 456℃mysql服务怎么启动和关闭?(mysql服务怎么启动和关闭)
- 454℃MySQL server PID file could not be found!失败
- 最近发表
-
- PS所有滤镜的说明(六)(ps滤镜详解)
- 5款小白也能用的在线图片编辑器!电商效率飙升就靠它!
- Java变量(java变量有什么作用)
- Java面试常见问题:Java注解(java中的面试题)
- Java编程入门第一课:HelloWorld(java编程从入门到实践)
- Java基础教程:Java继承概述(java里继承的概述)
- java基础之——访问修饰符(private/default/protected/public)
- 如何规划一个合理的JAVA项目工程结构
- 将机器指令翻译成 JavaScript -- 终极目标
- Web 服务器基准测试:Go vs. Node.js vs. Nim vs. Bun
- 标签列表
-
- cmd/c (90)
- c++中::是什么意思 (84)
- 标签用于 (71)
- 主键只能有一个吗 (77)
- c#console.writeline不显示 (95)
- pythoncase语句 (81)
- es6includes (73)
- windowsscripthost (67)
- apt-getinstall-y (86)
- node_modules怎么生成 (76)
- c++int转char (75)
- static函数和普通函数 (76)
- el-date-picker开始日期早于结束日期 (70)
- js判断是否是json字符串 (67)
- checkout-b (67)
- c语言min函数头文件 (68)
- asynccallback (71)
- localstorage.removeitem (74)
- vector线程安全吗 (70)
- java (73)
- js数组插入 (83)
- mac安装java (72)
- eacces (67)
- 查看mysql是否启动 (70)
- 无效的列索引 (74)