MySQL细数发生索引失效的情况实例分析

---

索引的存储结构

首先了解一下索引的存储结构，知道了索引的存储结构，才方便我们更好地理解索引失效的问题。

索引的存储结构跟MySQL的存储引擎有关，存储引擎的不同采用的结构也会不同。

MySQL默认的存储引擎InnoDB采用B+Tree作为索引的数据结构，在创建表时，InnoDB会默认创建一个主键索引，这是一个聚簇索引，其他索引都属于二级索引。

MyISAM存储引擎在创建表时，默认是用的是B+树索引。

虽然和InnoDB一样都支持B+树索引，但是他们存储数据的方式不同；

InnoDB是聚簇索引（B+树索引的叶子结点保存数据本身）

MyISAM是非聚集索引（B+树的叶子结点保存数据的物理地址）

如下图所示：

InnoDB存储引擎可以分为【聚簇索引】和【二级索引】，它们的区别在于聚簇索引的叶子结点存放的是实际数据，所有完整的数据都存放在聚簇索引的叶子结点，二级索引的叶子结点存放的是主键值。

在使用二级索引字段作为查询条件，查询数据在聚簇索引上的时候，

会先根据条件在二级索引上找到对应的叶子结点得到主键值，

再根据主键值去聚簇索引上找到对应的叶子结点然后查询到对应的数据，

这个过程叫回表

使用二级索引作为查询条件，查询的数据在二级索引的叶子结点上的时候，那么只需找到二级索引的B+树对应的叶子结点，读取数据，这个过程叫覆盖索引

上面这些查询条件都用到了索引列，但并不表示用到索引列索引就一定会生效，我们再来看一看索引失效的情况

不合理的模糊查询条件

使用左或左右模糊查询的时候，也就是like "%张"或like "%张%"这两种模糊查询方式都会导致索引失效

因为B+树是根据索引值进行排列的，前缀不确定的时候可能是，“小张”，"二张"之类的所有的情况，就只能通过全表扫描的方式来查询

对索引使用函数

例如：SELECT * FROM sys_user WHERE LENGTH(user_id) = 3 ;

因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，所以使用函数的时候就不会走索引了

不过从MySQL8.0开始，索引特性增加了函数索引，也就是针对该函数计算后的值建立一个索引，这样就可以通过扫描索引来查询数据了；

alter table t_user add key idx_name_length ((length(name)));

对索引进行表达式计算

例如：select * from sys_user where user_id+1 =3;

但是如果是SELECT * FROM sys_user WHERE user_id = 1+1 ;这样的不在索引字段上进行计算，就又会走索引了

原因跟对索引使用函数差不多，索引保存的是索引字段的原始值，而不是运算后的值，所以无法走索引

对索引使用隐式转换

这里的phone字段是二级索引，且是varchar类型的

使用整型作为查询参数的时候，执行计划中type为ALL，也就是通过全表扫描查询的，但如果是字符串类型，还是走索引查询的

我们再看一个例子

这里user_id是bigint类型，但是使用字符串作为查询参数还是走了索引的

为什么第一个例子导致了索引失效，而第二个不会呢？

AILOGO

LOGO123旗下的AI智能LOGO生成器，只需输入品牌名称就能免费在线生成公司logo设计及配套企业VI，轻松打造您的个性品牌！

139 查看详情

这里就要了解一下MySQL的字符转换规则了，看是数字转字符串，还是字符串转数字

我们可以用select "10">9来测试一下

如果是数字转字符串，那么就相当于select "10">"9"结果应该是0

如果是字符串转数字，那么就相当于select 10>9，结果是1

在MySQL中的执行结果如下：

这就说明，MySQL在遇到数字与字符串的比较的时候，会自动把字符串转换为数字，然后进行比较

也就是说，在第一个例子中

SELECT * FROM sys_user WHERE phone = 18200000000 ;

相当于

SELECT * FROM sys_user WHERE CAST(phone AS UNSIGNED) = 18200000000 ;

这就在索引字段上使用了函数，所以导致索引失效

而在第二个例子中

SELECT * FROM sys_user WHERE user_id = "1" ;

相当于

SELECT * FROM sys_user WHERE user_id = CAST("1" AS UNSIGNED) ;

函数式作用在查询参数上的，并没有作用在索引字段上，所以还是走索引的

联合索引非最左匹配

多个普通字段组合在一起创建的索引叫做联合索引（组合索引）

在使用联合索引的时候，一定要注意顺序问题，联合索引的使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引匹配。

例如，创建了一个(a,b,c)联合索引，那么如果查询条件是一下几种，就可以匹配上联合索引

where a = 1where a = 1 and b = 2where a = 1 and b = 2 and c = 3

需要注意的是，因为有查询优化器，所以a字段在where子句中的顺序不重要

若缺少a字段，则以下几种情况由于不符合最左匹配原则将无法匹配联合索引，导致该联合索引失效

where b = 2
where c = 3
where b = 2 and c = 3

还有一个比较特殊的查询条件：where a = 1 and c = 3

在MySQL5.5的话，前面的a 会走索引，在联合索引找到主键值，然后回表，到主键索引读取数据行，然后在比对c字段的值

在MySQL5.6之后，有一个索引下推的功能，

下推就是将部分上层(服务层)负责的事情，交给了下层(引擎层)处理

存储引擎直接在联合索引里按照c=3过滤，按照过滤后的数据在进行回表扫描，减少了回表的次数，从而提升了性能

在执行计划中Extra = Using index condition就表示使用了索引下推

联合索引不遵循最左匹配原则的原因：在联合索引中，数据按照第一列索引进行排序，第一列数据相同时，才会按照第二列进行排序，以此类推，所以直接使用第二列进行查询的时候，联合索引就会失效

where子句中的or

where子句中or的条件列有不是索引列会导致索引失效

例如：下图中id是索引列，email不是索引列，从执行计划来看，进行了全文扫描并没有使用到索引

因为or关键字只满足一个条件就可以，因此只要有一个列不是索引列，其他索引列也就没有意义了，就会进行全表扫描

在email列上建立索引之后，可以看到执行计划中使用到了两个索引

type = index_merge表示对id 和email都进行了扫描，然后进行了合并

以上就是MySQL细数发生索引失效的情况实例分析的详细内容，更多请关注其它相关文章！

# 这就  # 营销推广方式都选一 诺enuo  # 辽源优化seo  # 乐清网站建设银行暑假  # 阿里云网站建设产品介绍  # 通讯产品关键词排名专家  # 遵义网站建设遵义  # 跨国医疗seo  # 最新音乐推广视频素材网站  # 国美网站建设特点  # 如何搜索网站排名优化  # MySQL  # 只需  # 句中  # 第一个  # 就可以  # 进行了  # 就会  # 主键  # 镜像  # 的是 

相关栏目： 【 Google疑问12 】 【 Facebook疑问10 】 【 优化推广96088 】 【 技术知识133117 】 【 IDC资讯59369 】 【 网络运营7196 】 【 IT资讯61894 】

相关推荐： 风神瞳获取全攻略  学习通网页版个人登录_学习通网页版个人账户登录入口  《环球网校》设置报考省市方法  苹果iPhone14ProMax如何新建AppleID_iPhone14ProMax新建AppleID具体流程  C++ optional用法详解_C++17处理可能为空的返回值  百度竞价WAP显示PC链接问题  TikTok私信无法发送表情怎么办 TikTok消息表情发送修复方法  漫蛙漫画官方版直通入口 2025漫蛙漫画免注册访问说明  繁花漫画使用教程  PSD转AI文件的简单方法  Golang如何使用log记录日志信息_Golang log日志记录方法总结  蛙漫2(台版)正版官网 2025免费网页版分享  知音漫客官网首页入口_知音漫客热门漫画推荐  视频号视频怎么免费保存到相册？保存到相册需要注意什么？  汽水音乐网页端访问 汽水音乐官方网页直达  《盗墓笔记手游》技能介绍  在Flask应用中安全高效地更新SQLAlchemy用户数据  ao3入口镜像地址 ao3镜像入口可靠跳转  Microsoft Edge网页字体太淡看不清怎么办_Microsoft Edge字体渲染优化技巧  快手网页版官方访问 快手网页版页面在线打开  sublime如何处理超大文件不卡顿 _sublime打开大日志文件技巧  快递查询，一键速查  照片整理的黄金法则是怎样的？ 理解“收集-筛选-归档-备份”四步流程  解决Windows上Composer PATH变量冲突导致的命令无法识别问题  win11如何诊断DirectX问题 Win11运行dxdiag工具排查显卡故障【排错】  Vue 3中独立响应式实例的创建与应用  search中maxlength属性用法解析  智云Q3和Q2有什么升级_智云Q3与Q2手持云台功能与性能对比分析  J*aScript对象中深度嵌套URL键的查找与更新策略  抖音号怎么解除企业认证改成个人？改成个人有影响吗？  菜鸟裹裹怎样获得取件码_菜鸟裹裹获得取件码步骤  J*aScript深度克隆：实现高效、健壮与安全的复杂对象复制  哔哩哔哩黑名单怎么查看  基于键值条件高效映射 Pandas DataFrame 多列数据  使用VS Code调试Python代码：从入门到精通  铁拳8在线玩 铁拳8在线秒玩入口  Windows 11怎么删除恢复分区_Windows 11使用Diskpart命令强行删除分区  阿里旺旺电脑网页版入口 阿里旺旺电脑版网页登录入口  OpenWeatherMap API：通过城市名称获取天气预报数据指南  快递物流路径揭秘  抖音号升级企业号怎么改名字？升级企业号有哪些好处？  《星露谷物语》克林特好感度事件介绍  《大周列国志》皇帝律令功能介绍  PHP utf8_encode 字符编码转换疑难解析与最佳实践  XPath动态元素定位：如何精准选择文本内容变化的元素  荣耀Magic7拍照夜景噪点处理_荣耀Magic7相机优化  荣耀盒子应用管理技巧  解决SQLAlchemy模型跨文件关联的Linter兼容性指南  j*a中赋值运算符是什么？  咸鱼怎么设置仅粉丝可见的动态_咸鱼动态粉丝可见设置方法 

 2023-05-28