浙江龙网

  1. 首页 > 生活百科 > >

MySQL 索引失效-模糊查询，最左匹配原则，OR条件等。( 二 )

生活百科


  • 先在「二级索引」的 B+ 树找到对应的叶子节点，获取主键值；
  • 然后用上一步获取的主键值，在「聚簇索引」中的 B+ 树检索到对应的叶子节点，然后获取要查询的数据 。
上面这个过程叫做回表，如下面这条语句：
// name 字段为二级索引select * from t_user where name="林某";在我们使用「二级索引」字段作为条件查询的时候，如果要查询的数据在「二级索引」的叶子节点，那么只需要在「二级索引」的 B+ 树找到对应的叶子节点 ， 然后读取要查询的数据 ， 这个过程叫做覆盖索引 。如下面这条语句：
// name 字段为二级索引select id from t_user where name="林某";上面这些查询语句的条件都用到了索引列，所以在查询过程都用上了索引 。
但是并不意味着，查询条件用上了索引列，就查询过程就一定都用上索引，接下来我们再一起看看哪些情况会导致索引实现，而发生全表扫描 。
首先说明下 ， 下面的实验案例，我使用的 MySQL 版本为 8.0.26 。
对索引使用左或者左右模糊匹配当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效 。
比如下面的 like 语句，查询 name 后缀为「林」的用户，执行计划中的 type=ALL 就代表了全表扫描，而没有走索引 。
// name 字段为二级索引select * from t_user where name like '%林';
MySQL 索引失效-模糊查询，最左匹配原则，OR条件等。

文章插图
如果是查询 name 前缀为林的用户 ， 那么就会走索引扫描 ， 执行计划中的 type=range 表示走索引扫描，key=index_name 看到实际走了 index_name 索引：
// name 字段为二级索引select * from t_user where name like '林%';
MySQL 索引失效-模糊查询，最左匹配原则，OR条件等。

文章插图
为什么 like 关键字左或者左右模糊匹配无法走索引呢？
因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较 。
举个例子，下面这张二级索引图，是以 name 字段有序排列存储的 。
MySQL 索引失效-模糊查询，最左匹配原则，OR条件等。

文章插图
假设我们要查询 name 字段前缀为「林」的数据，也就是 name like '林%'，扫描索引的过程：
  • 首节点查询比较：林这个字的拼音大小比首节点的第一个索引值中的陈字大 ， 但是比首节点的第二个索引值中的周字小 ， 所以选择去节点2继续查询；
  • 节点 2 查询比较：节点2的第一个索引值中的陈字的拼音大小比林字小，所以继续看下一个索引值 ， 发现节点2有与林字前缀匹配的索引值，于是就往叶子节点查询 ， 即叶子节点4；
  • 节点 4 查询比较：节点4的第一个索引值的前缀符合林字，于是就读取该行数据，接着继续往右匹配，直到匹配不到前缀为林的索引值 。
如果使用 name like '%林' 方式来查询，因为查询的结果可能是「陈林、张林、周林」等之类的，所以不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询 。
想要更详细了解 InnoDB 的 B+ 树查询过程 ， 可以看我写的这篇：B+ 树里的节点里存放的是什么呢？查询数据的过程又是怎样的？
对索引使用函数有时候我们会用一些 MySQL 自带的函数来得到我们想要的结果，这时候要注意了，如果查询条件中对索引字段使用函数，就会导致索引失效 。
比如下面这条语句查询条件中对 name 字段使用了 LENGTH 函数，执行计划中的 type=ALL，代表了全表扫描：
// name 为二级索引select * from t_user where length(name)=6;
MySQL 索引失效-模糊查询，最左匹配原则，OR条件等。

文章插图
为什么对索引使用函数 ， 就无法走索引了呢？
因为索引保存的是索引字段的原始值 ， 而不是经过函数计算后的值，自然就没办法走索引了 。
不过，从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据 。
举个例子，我通过下面这条语句，对 length(name) 的计算结果建立一个名为 idx_name_length 的索引 。
alter table t_user add key idx_name_length ((length(name)));

推荐阅读


上一篇：NAS数据存储之NFS搭建和使用

下一篇：HDFS追加数据报错解决办法