MySQL细数发生索引失效的情况

目录

• 索引的存储结构
• 不合理的模糊查询条件
• 对索引使用函数
• 对索引进行表达式计算
• 对索引使用隐式转换
• 联合索引非最左匹配
• where子句中的or
• 总结

 

索引的存储结构

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

索引的存储结构跟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类型，但是使用字符串作为查询参数还是走了索引的

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

这里就要了解一下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 = 1
where a = 1 and b = 2
where 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都进行了扫描，然后进行了合并

 

总结

导致索引失效的情况有：

不合理的使用模糊查询：like "%张"或like %张%

对索引列使用函数

对索引使用表达式计算

对索引使用隐式转换，这三个都是引起了索引列值的变化导致索引失效

联合索引非最左匹配

where子句中or条件列没有使用索引

到此这篇关于MySQL细数发生索引失效的情况的文章就介绍到这了,更多相关MySQL索引失效内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文地址：https://blog.csdn.net/qq_43460095/article/details/125538837