写在前面:关于索引的存储结构
在直面问题之前,我们先来了解下索引的存储结构,这样有利于更好地理解“索引失效”。
首先,索引的存储结构跟 MySQL 具体使用的存储引擎有关,因为存储引擎负责将数据持久化在磁盘中,而不同的存储引擎采用的索引数据结构也会不同。从MySQL5.5版本开始,InnoDB成为MySQL的默认存储引擎,而在这之前的默认存储引擎则是MyISAM。两者在创建表时都会默认创建一个主键索引(也叫聚簇索引),该索引默认以B+树索引实现。虽然两者都支持B+树索引,但不同之处在于:
- InnoDB 存储引擎:B+ 树索引的叶子节点保存数据本身
- MyISAM 存储引擎:B+ 树索引的叶子节点保存数据的物理地址
具体如下图所示(第一张图是MyISAM的存储方式,第二张图是InnnoDB的存储方式)
其中InnoDB 存储引擎根据索引类型不同,又分为聚簇索引(上图就是聚簇索引)和二级索引。它们区别在于,聚簇索引的叶子节点存放的是实际数据,所有完整的用户数据都存放在聚簇索引的叶子节点,而二级索引的叶子节点存放的是主键值,而不是实际数据。
在使用二级索引字段作为条件查询的时候,如果要查询的数据都在聚簇索引的叶子节点里,那么需要检索两颗B+树(先检索聚簇索引的B+树找到对应主键值,再根据该值检索二级索引的B+树,得到要查询的数据),这个过程就称为“回表”。若上述过程中要找的数据就在二级索引的叶子结点(主键值)中,无须再检索聚簇索引,该过程称为“索引覆盖”。
正文:总结出现索引失效的6种常见情况
(注:下面演示过程中使用的是MySQL5.7版本)
1.对索引使用了左模糊匹配或左右模糊匹配
在对表中数据进行模糊查询时,当使用“like %xx”或者“like %xx%”即左模糊匹配或左右模糊匹配时,会使模糊查询中用到的索引字段失效,进行全表扫描(比如下图,创建好的表test01中在name字段建立了索引,然后查询表中name以k结尾的数据或中间含h的数据,都出现了type=ALL即全表扫描,说明索引失效)。
失效原因:索引的B+ 树是按照索引值有序排列存储的,只能根据前缀进行比较。
解决方案:在实际操作中应尽量使用右模糊匹配。
2.对索引列使用函数
注意:下面两种情况都是在where筛选条件中对索引name使用了length函数,但是后者查询的就是索引列的数据,则会走索引;前者查询的结果包含除该索引列之外的数据,于是出现索引失效。
失效原因:索引保存的是索引字段的原始值,而不是经过函数计算后的值。
解决方案:尽量避免对索引使用函数,或者针对函数计算后的值另外建立索引,具体操作如下。(MySQL8.0开始支持函数索引)
alter table test01 add key name_length ((length(name)));
3.对索引进行表达式计算
失效原因:与第二种情况同理,由于索引保存的是索引字段的原始值,要针对表达式计算后的值进行筛选的话,就只能对每一行都进行计算,无法走索引。
解决方案:当需要对索引进行表达式计算时,应在SQL语句中简化表达式,如id + 5 = 10 可改为
id = 5.
4.对索引进行隐式类型转换
失效原因:在MySQL 的数据类型转换规则中,字符串和数字比较时会被自动转换为数字类型,
这个过程中隐式地使用了CAST函数,相当于下图中的操作,与第二种情况同理。
select * from test01 where CAST(phone AS signed int) = 12345678911;
解决方案:当索引列是字符串类型时,尽量避免直接和数字比较。
5.WHERE子句中OR两侧的条件列中含非索引列
如下所示,其中id是自增主键,age是非索引列。
失效原因:OR含义指的是两侧条件中只要满足一个即可,因此只有一个条件列是索引列是没有意义的,只要有条件列不是索引列,就会进行全表扫描。
解决方案:在使用OR时尽量保证两侧都是索引列。
6.使用联合索引时非最左匹配
创建表test02时对于a, b, c列建立了联合索引(a,b,c):
若进行如下where筛选则无法满足联合最左匹配原则,导致索引失效:
select * from test02 where b = 2;
select * from test02 where c = 3;
select * from test02 where b = 2 or c = 3;
如下查询语句则可以走索引:
select * from test02 where a = 1;
select * from test02 where a = 1 and b = 2;
select * from test02 where a = 1 and c = 3;
其中“where a = 1 and c = 3”比较特殊,称作“索引截断”,不同MySQL版本的处理方法不一样。
若是MySQL 5.5版本,前面 a 会走索引,在联合索引找到主键值后,开始回表,到主键索引读取数据行,Server 层从存储引擎层获取到数据行后,然后在 Server 层再比对 c 字段的值。
从MySQL 5.6版本之后,引入了索引下推功能,可以在存储引擎层进行索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,再返还给 Server 层,从而减少回表次数。
失效原因:在联合索引的情况下,数据是按照索引第一列排序,第一列数据相同时才会按照第二列排序。
解决方案:使用联合索引查询时应尽量符合最左匹配原则。
总结:
MySQL查询过程中导致“索引失效”的6种常见情况如下:
- 当使用左或者左右模糊匹配的时候,即 like '%xx' 或者 like '%xx%' 这两种方式都会造成索引失效;
- 在查询条件中对索引列使用函数,会导致索引失效;
- 在查询条件中对索引列进行表达式计算,也无法走索引;
- MySQL 在遇到字符串和数字比较的时候,会自动把字符串转为数字,然后再进行比较。如果字符串是索引列,而条件语句中的输入参数是数字的话,那么索引列会发生隐式类型转换,由于隐式类型转换是通过 CAST 函数实现的,等同于对索引列使用了函数,所以就会导致索引失效;
- 联合索引要能正确使用需要遵循最左匹配原则,也就是按照最左优先的方式进行索引的匹配,否则就会导致索引失效;
- 在 WHERE 子句中,如果OR两侧条件含有非索引列,那么索引会失效。