2020年5月20日星期三

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+ 树。

百度百科中的结构图:

img

一个 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_limit10,可以酌情修改成 200


没有评论:

发表评论