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 】

相关推荐： 抖音如何解除|直播|权限绑定_抖音关闭并解绑|直播|功能的方法  小红书网页版在线直达 小红书网页版免费登录入口  《红果免费短剧》下载观看方法  《地下城堡4：骑士与破碎编年史》墓穴挑战125攻略  深入理解Python对象引用与链表属性赋值  Linux如何优化系统启动流程_Linux启动项优化方案  win11怎么更改账户类型 Win11标准用户和管理员权限切换【教程】  解决VS Code中Python版本冲突与输出异常的指南  电脑没有声音了怎么办 电脑声音问题的全面排查与修复指南【详解】  word文档行距怎么调？word文档调行距的操作步骤  Google Drive API 认证：服务账户与OAuth 2.0的选择与实践  中大网校app做题记录清除方法  51漫画网实时入口 51漫画网页版官方免费漫画入口  芒果TV官网登录入口 芒果TV官方网站登录入口  J*a列表元素格式化输出教程  J*aScript模块加载器_RequireJS原理分析  曝《丝之歌》DLC有望开发！开发商还有神秘新企划  向日葵客户端怎么进行语音通话_向日葵客户端语音通话功能使用方法  哈尔滨城市通昵称修改方法  192.168.1.1路由器后台入口 192.168.1.1默认登录入口  Excel如何快速找到并断开外部数据源链接_Excel外部数据源断开方法  店铺如何关联视频号推广？视频号推广有什么用？  《via浏览器》强制缩放网页设置方法  口腔诊所管理软件推荐  手机自动关机是怎么回事？如何修复？手机异常关机的原因排查与修复技巧  Lar*el 中高效执行多列更新：单次查询实现  六级准考证号怎么查_四六级准考证查询入口官网  抖音手机分身两个账号怎么切换？分身两个系统是一样的吗？  OPPO手机参数配置如何开启护眼模式_OPPO手机参数配置护眼模式开启指南  《淘宝联盟》推广自己的店铺方法  《书耽》更换手机号方法  b站怎么查看视频的码率_b站视频码率查看方法  mysql离线安装后如何启动_mysql离线安装完成后启动服务的方法  谷歌学术论文搜索引擎 谷歌学术官网入口论坛永久链接  PHP 4 函数中引用参数的默认值限制与解决方案  使用Python和GBGB API高效抓取指定日期范围和赛道比赛结果教程  C++ switch case字符串_C++如何实现字符串switch匹配  批改网网页版登录 批改网电脑版学生登录入口  之了课堂app做题入口  mysql中如何配置字符集和排序规则_mysql字符集排序配置  mysql镜像配置如何恢复数据_mysql镜像配置数据恢复详细流程  汽水音乐官方网站登录入口_汽水音乐网页版进入链接  蛙漫2(台版)正版官网 2025免费网页版分享  怎么恢复删除的电脑文件_数据恢复软件使用教程  以下哪一个是适应长期护理制度发展而设立的新职业  优酷官网登录入口电脑版 优酷官网网址入口  VB表达式书写规则解析  《桃源记2》资源采集攻略  微信注销后银行卡解绑了吗_微信注销后银行卡解绑状态  iPhone 13 Pro Max如何设置桌面小组件_iPhone 13 Pro Max小组件添加指南 

 2023-05-28