MySQL （InnoDB）在什么情况下无法使用索引

MySQL （InnoDB）在什么情况下无法使用索引

目录

• 1. 前置知识
  • 1.1 InnoDB 索引结构
• 2. 什么情况下不能使用索引
  • 2.1 索引列在表达式或函数中【必】
  • 2.2 联合索引中，非最左前缀【必】
  • 2.3 联合索引中，最左前缀，但是中间有范围查询，那么范围查询后面的列都用不到索引【必】
  • 2.4 join... on...，主键和外键的索引数据类型不一致【必】
  • 2.5 在无索引的列上使用了 or 那么有索引的列也用不上了【可】
  • 2.6 in 查询语句中多个值的数据类型不一致的情况，如：in (1, 2, 3, 4, "5")【必】
  • 2.7 in 语句中超过 200 个值【可】

1. 前置知识

1.1 InnoDB 索引结构

InnoDB 索引使用的数据结构是 B+ 树。

百度百科中的结构图：

一个 m 阶 B+ 树的几个特点：

1. 每个节点可能有最多 m 个子节点
2. 除根结点外，每个结点至少有[m/2]个子女，根结点至少有两个子女
3. 有 k 个子女的结点必有 k 个关键字

可以类比字典，通过笔画找到一个字怎么办？总不能一页一页去翻吧？当然不能。

字典的修订者会加上字笔画目录，只要查清楚字的笔画数，然后去对应的笔画目录下去找就可以了。

咦~ 怎么这个笔画下面还有这么多字？总不能一页一页去翻吧？当然不会。

找到对应的笔画数之后，目录下还有部首的目录，部首的目录是按照部首笔画数排序的，查清部首的笔画数，然后去挨个找部首就行了。

找到部首之后，就会定位到具体的字了。

当然 B+ 树和字典目录还是有很多不一样的地方，只是为了比较好理解

每次搞不懂 B+ 树的时候，可以想想小时候怎么查字典的。

2. 什么情况下不能使用索引

简单来说，我们能使用索引进行高效查询是基于索引的以下特性

1. 多级目录
2. 有序性（根据比较规则排序）
3. 使用成本较低（成本计算）

2.1 索引列在表达式或函数中【必】

这个很好理解，因为函数会改变索引本身的值，不再具有有序性

2.2 联合索引中，非最左前缀【必】

联合索引中，非最左前缀是无序的

2.3 联合索引中，最左前缀，但是中间有范围查询，那么范围查询后面的列都用不到索引【必】

a, b, c 三列索引，先按照 a 排序，后按照 b 排序，再按照 c 排序

语句 a=1 and b > 1 and c=2 只能使用 a, b 索引进行筛选，c=1 条件需要将前面筛选之后的索引结果逐一比较之后返回结果。

a 索引过滤之后是有序的，所以可以使用 b 索引进行过滤，b 过滤之后是无序的（也有可能是有序的，但是 innodb 不会再去判断是否有序）

2.4 join... on...，主键和外键的索引数据类型不一致【必】

强类型下，数据类型不一致，需要特殊处理才能比较

2.5 在无索引的列上使用了 or 那么有索引的列也用不上了【可】

这个只是有可能，因为 innodb 底层是基于成本选择使用索引的。

因为在无索引的列上使用 or 会使成本变大，所以很容易无法使用索引。

2.6 in 查询语句中多个值的数据类型不一致的情况，如：in (1, 2, 3, 4, "5")【必】

强类型下，数据类型不一致，需要特殊处理才能比较

但是如果 in 里面都是字符串或者都是数字，innodb 的优化器会将其统一转成索引所需类型。

2.7 in 语句中超过 200 个值【可】

在 in 语句中 5.7 版本上 超过 200 个值，就会放弃使用 index_dive 方式计算cost，从而导致估算不准确，很容易用不上索引

5.6 以下版本 req_index_dive_limit 为 10，可以酌情修改成 200