select * from t_user where id = CAST("1" AS signed int);
可以看到,索引字段并没有用任何函数,CAST 函数是用在了输入参数 , 因此是可以走索引扫描的 。
联合索引非最左匹配对主键字段建立的索引叫做聚簇索引,对普通字段建立的索引叫做二级索引 。
那么多个普通字段组合在一起创建的索引就叫做联合索引,也叫组合索引 。
创建联合索引时,我们需要注意创建时的顺序问题,因为联合索引 (x, y, z) 和 (z, y, x) 在使用的时候会存在差别 。
联合索引要能正确使用需要遵循最左匹配原则,也就是按照最左优先的方式进行索引的匹配 。
比如,如果创建了一个 (a, b, c)
联合索引,如果查询条件是以下这几种,就可以匹配上联合索引:
- where a=1;
- where a=1 and b=2 and c=3;
- where a=1 and b=2;
但是,如果查询条件是以下这几种,因为不符合最左匹配原则,所以就无法匹配上联合索引,联合索引就会失效:
- where b=2;
- where c=3;
- where b=2 and c=3;
这种其实严格意义上来说是属于索引截断,不同版本处理方式也不一样 。
MySQL 5.5 的话,前面 a 会走索引 , 在联合索引找到主键值后,开始回表,到主键索引读取数据行,然后再比对 z 字段的值 。
从 MySQL5.6 之后,有一个索引下推功能 , 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录 , 减少回表次数 。
大概原理是:截断的字段会被下推到存储引擎层进行条件判断(因为 c 字段的值是在
(a, b, c)
联合索引里的),然后过滤出符合条件的数据后再返回给 Server 层 。由于在引擎层就过滤掉大量的数据,无需再回表读取数据来进行判断,减少回表次数 , 从而提升了性能 。比如下面这条 where a = 1 and c = 0 语句,我们可以从执行计划中的 Extra=Using index condition 使用了索引下推功能 。
文章插图
为什么联合索引不遵循最左匹配原则就会失效?原因是,在联合索引的情况下,数据是按照索引第一列排序,第一列数据相同时才会按照第二列排序 。
也就是说 , 如果我们想使用联合索引中尽可能多的列,查询条件中的各个列必须是联合索引中从最左边开始连续的列 。如果我们仅仅按照第二列搜索,肯定无法走索引 。
WHERE 子句中的 OR在 WHERE 子句中,如果在 OR 前的条件列是索引列,而在 OR 后的条件列不是索引列,那么索引会失效 。
举个例子,比如下面的查询语句,id 是主键,age 是普通列,从执行计划的结果看,是走了全表扫描 。
select * from t_user where id = 1 or age = 18;
文章插图
这是因为 OR 的含义就是两个只要满足一个即可 , 因此只有一个条件列是索引列是没有意义的 , 只要有条件列不是索引列,就会进行全表扫描 。
要解决办法很简单,将 age 字段设置为索引即可 。
文章插图
可以看到 type=index merge , index merge 的意思就是对 id 和 age 分别进行了扫描,然后将这两个结果集进行了合并,这样做的好处就是避免了全表扫描 。
总结今天给大家介绍了 6 种会发生索引失效的情况:
- 当我们使用左或者左右模糊匹配的时候,也就是
like %xx
或者like %xx%
这两种方式都会造成索引失效; - 当我们在查询条件中对索引列使用函数,就会导致索引失效 。
- 当我们在查询条件中对索引列进行表达式计算 , 也是无法走索引的 。
- MySQL 在遇到字符串和数字比较的时候,会自动把字符串转为数字,然后再进行比较 。如果字符串是索引列,而条件语句中的输入参数是数字的话,那么索引列会发生隐式类型转换 , 由于隐式类型转换是通过 CAST 函数实现的 , 等同于对索引列使用了函数,所以就会导致索引失效 。
- 联合索引要能正确使用需要遵循最左匹配原则,也就是按照最左优先的方式进行索引的匹配,否则就会导致索引失效 。
推荐阅读
- MySQL 全局锁、表级锁、行级锁,你搞清楚了吗?
- golang中的nil接收器
- llinux下mysql建库、新建用户、用户授权、修改用户密码
- RedHat7.6安装mysql8步骤
- golang中的字符串
- flutter系列之:flutter中可以建索引的栈布局IndexedStack
- 究极无敌细节版 Mysql索引
- Mysql通过Canal同步Elasticsearch
- MySQL的日志文件
- 01-MySQL8主从详解