Mysql--索引

参考链接:https://xiaolincoding.com/mysql/index/page.html#innodb-%E6%98%AF%E5%A6%82%E4%BD%95%E5%AD%98%E5%82%A8%E6%95%B0%E6%8D%AE%E7%9A%84
详细请看以上链接

从数据页角度看B+树

InnoDB如何存储数据

InnoDB的数据是按照数据页为单位进行读写
数据库的 I/O 操作的最小单位是页,InnoDB 数据页的默认大小是 16KB。

InnoDB数据页结构图:
在这里插入图片描述
在这里插入图片描述
FileHeader中有两个指针,连接前后两个数据页,数据页组成一个双向链表
数据页中的记录按照「主键」顺序组成单向链表
由于链表遍历效率不高,数据页中有一个页目录,起到索引的作用
页目录与记录关系:

在这里插入图片描述
页目录创建过程:
1.将所有记录分组,这些记录包括最小记录和最大记录,但不包括标记为已删除的记录
2。每个记录组的最后一条数据是组内最大记录,该最大记录的头信息中会存储改组共有多少条记录,作为n_owned字段(粉色)
3.页目录存储每组最后一条记录的地址偏移量,这些地址偏移量会按照先后顺序存储,每组的地址偏移量被称为槽(slot),每个槽相当于指针指向了不同组的最后一个记录
从图可以看到,页目录就是由多个槽组成的,槽相当于分组记录的索引。然后,因为记录是按照「主键值」从小到大排序的,所以我们通过槽查找记录时,可以使用二分法快速定位要查询的记录在哪个槽(哪个记录分组),定位到槽后,再遍历槽内的所有记录,找到对应的记录,无需从最小记录开始遍历整个页中的记录链表。

**总结:**一个数据页中的记录是有限的,且主键值是有序的,所以通过对所有记录进行分组,然后将组号(槽号)存储到页目录,使其起到索引作用,通过二分查找的方法快速检索到记录在哪个分组,来降低检索的时间复杂度。

B+树如何进行查询

InnoDB采取了B+树作为索引,每个节点都是一个数据页

结构图:
在这里插入图片描述

由上图,得出B+树特点:
1.只有叶子节点存放数据,非叶子节点用来存放目录项作为索引
2.非叶子节点分为不同层次,通过分层来降低每一层的搜索量。
3.所有节点按照索引键大小排序,构成一个双向链表,便于范围查询

聚簇索引和二级索引

索引可分为聚簇索引和非聚簇索引(二级索引),他们区别在于叶子节点存放的是什么数据

聚簇索引叶子节点存放的是实际数据,所有完整的用户记录都存放在聚簇索引的叶子节点。一张表聚簇索引只能有一个
二级索引的叶子节点存放的是主键值,而不是实际数据。

二级索引B+树如图:
在这里插入图片描述
如果某个查询语句使用了二级索引,但是查询的数据不是主键值,这时在二级索引找到主键值后,需要去聚簇索引中获得数据行,这个过程就叫作「回表」,也就是说要查两个 B+ 树才能查到数据。不过,当查询的数据是主键值时,因为只在二级索引就能查询到,不用再去聚簇索引查,这个过程就叫作「索引覆盖」,也就是只需要查一个 B+ 树就能找到数据。

总结:
如果叶子节点存储的是实际数据的就是聚簇索引,一个表只能有一个聚簇索引;如果叶子节点存储的不是实际数据,而是主键值则就是二级索引,一个表中可以有多个二级索引。

在使用二级索引进行查找数据时,如果查询的数据能在二级索引找到,那么就是「索引覆盖」操作,如果查询的数据不在二级索引里,就需要先在二级索引找到主键值,需要去聚簇索引中获得数据行,这个过程就叫作「回表」。

为什么MYSQL采用B+树索引

怎样的索引的数据结构是好的

能在尽可能少的磁盘IO操作中完成查询操作
要能高效地查询某个记录,也要能高效地执行范围查找

什么是二分查找树

二叉查找树的特点是 一个节点的左子树的所有节点都小于这个节点,右子树的所有节点都大于这个节点
也就是二叉搜索树
是一种基于二分查找的数据结构
请添加图片描述
但是二分查找树有一种极端情况,如果每次插入都是最大元素,全插入在右边,那么二叉树会退化成链表,是时间复杂度从logn到n

自平衡二叉树

为了解决二分查找树的极端情况,出现了自平衡二叉树。
自平衡二叉树就是在二叉查找树的基础上添加了一些条件约束:每个节点的左子树和右子树的高度差不能超过1.

自平衡二叉树还是有一个问题,随着插入元素的增多,会导致树变高,磁盘IO操作次数增多,降低查询效率

B树

为了解决自平衡二叉树的问题,出现了B树,不在限制一个节点只有两个子节点,而是允许M个子节点,从而降低树的高度。
B 树的每一个节点最多可以包括 M 个子节点,M 称为 B 树的阶,所以 B 树就是一个多叉树。

假设 M = 3,那么就是一棵 3 阶的 B 树,特点就是每个节点最多有 2 个(M-1个)数据和最多有 3 个(M个)子节点,超过这些要求的话,就会分裂节点,比如下面的的动图:
请添加图片描述

B+树

B+树就是对B树进行了升级
在这里插入图片描述
B+树与B树的差异的点:
1.叶子节点才会存放实际数据,非叶子节点存放索引
2.所有索引都会在叶子节点出现,叶子节点间构成一个有序链表
3.非叶子节点的索引也会同时存在在子节点中,并且在子节点中所有索引的最大(或最小)
4.非叶子节点中有多少个子节点,就有多少个索引

下面通过三个方面,比较B+树和B树的区别
1.单点查询
B 树进行单个索引查询时,最快可以在 O(1) 的时间代价内就查到,而从平均时间代价来看,会比 B+ 树稍快一些。

但是 B 树的查询波动会比较大,因为每个节点即存索引又存记录,所以有时候访问到了非叶子节点就可以找到索引,而有时需要访问到叶子节点才能找到索引。

B+ 树的非叶子节点不存放实际的记录数据,仅存放索引,因此数据量相同的情况下,相比存储即存索引又存记录的 B 树,B+树的非叶子节点可以存放更多的索引,因此 B+ 树可以比 B 树更「矮胖」,查询底层节点的磁盘 I/O次数会更少。

2.插入和删除效率
B+树有大量冗余节点,使得在删除一个节点时可以直接从叶子节点删除,甚至可以不动非叶子节点,这样删除更快,对整个树的影响更小
插入同理

3.范围查询
B+树所有叶子节点间有一个链表进行链接,用助于进行范围查找
存在大量范围检索的场景,适合使用 B+树,比如数据库。而对于大量的单个索引查询的场景,可以考虑 B 树,比如 nosql 的MongoDB。

MYSQL中的B+树

InnoDB的B+树:
在这里插入图片描述
InnoDB使用的B+树有一些特别的点,比如:
B+树叶子节点间使用双向链表链接,可以双向遍历
B+树叶子节点存放的是数据页,数据页中有用户的记录和各种信息,每个数据页默认大小16KB
总结:
MySQL 是会将数据持久化在硬盘,而存储功能是由 MySQL 存储引擎实现的,所以讨论 MySQL 使用哪种数据结构作为索引,实际上是在讨论存储引使用哪种数据结构作为索引,InnoDB 是 MySQL 默认的存储引擎,它就是采用了 B+ 树作为索引的数据结构。

要设计一个 MySQL 的索引数据结构,不仅仅考虑数据结构增删改的时间复杂度,更重要的是要考虑磁盘 I/0 的操作次数。因为索引和记录都是存放在硬盘,硬盘是一个非常慢的存储设备,我们在查询数据的时候,最好能在尽可能少的磁盘 I/0 的操作次数内完成。

二分查找树虽然是一个天然的二分结构,能很好的利用二分查找快速定位数据,但是它存在一种极端的情况,每当插入的元素都是树内最大的元素,就会导致二分查找树退化成一个链表,此时查询复杂度就会从 O(logn)降低为 O(n)。

为了解决二分查找树退化成链表的问题,就出现了自平衡二叉树,保证了查询操作的时间复杂度就会一直维持在 O(logn) 。但是它本质上还是一个二叉树,每个节点只能有 2 个子节点,随着元素的增多,树的高度会越来越高。

而树的高度决定于磁盘 I/O 操作的次数,因为树是存储在磁盘中的,访问每个节点,都对应一次磁盘 I/O 操作,也就是说树的高度就等于每次查询数据时磁盘 IO 操作的次数,所以树的高度越高,就会影响查询性能。

B 树和 B+ 都是通过多叉树的方式,会将树的高度变矮,所以这两个数据结构非常适合检索存于磁盘中的数据。

但是 MySQL 默认的存储引擎 InnoDB 采用的是 B+ 作为索引的数据结构,原因有:

B+ 树的非叶子节点不存放实际的记录数据,仅存放索引,因此数据量相同的情况下,相比存储即存索引又存记录的 B 树,B+树的非叶子节点可以存放更多的索引,因此 B+ 树可以比 B 树更「矮胖」,查询底层节点的磁盘 I/O次数会更少。
B+ 树有大量的冗余节点(所有非叶子节点都是冗余索引),这些冗余索引让 B+ 树在插入、删除的效率都更高,比如删除根节点的时候,不会像 B 树那样会发生复杂的树的变化;
B+ 树叶子节点之间用链表连接了起来,有利于范围查询,而 B 树要实现范围查询,因此只能通过树的遍历来完成范围查询,这会涉及多个节点的磁盘 I/O 操作,范围查询效率不如 B+ 树。

索引失效有哪些

索引数据结构

InnoDB存储索引默认会创建一个主键索引,其他索引为二级索引

在我们使用「主键索引」字段作为条件查询的时候,如果要查询的数据都在「聚簇索引」的叶子节点里,那么就会在「聚簇索引」中的 B+ 树检索到对应的叶子节点,然后直接读取要查询的数据。如下面这条语句:

// id 字段为主键索引
select * from t_user where id=1;

在我们使用「二级索引」字段作为条件查询的时候,如果要查询的数据都在「聚簇索引」的叶子节点里,那么需要检索两颗B+树:

1.先在「二级索引」的 B+ 树找到对应的叶子节点,获取主键值;
2.然后用上一步获取的主键值,在「聚簇索引」中的 B+ 树检索到对应的叶子节点,然后获取要查询的数据。
上面这个过程叫做回表,如下面这条语句:

// name 字段为二级索引
select * from t_user where name="林某";

以上查询语句都用到了索引列,所以在查询过程都用上了索引。

但是并不意味着,查询条件用上了索引列,就查询过程就一定都用上索引,有一些情况会导致索引失效,而发生全表扫描。

对索引使用左或者左右模糊查询

当我们使用左或者左右模糊匹配的时候,也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。

比如下面的 like 语句,查询 name 后缀为「林」的用户,执行计划中的 type=ALL 就代表了全表扫描,而没有走索引。

// name 字段为二级索引
select * from t_user where name like '%林';

在这里插入图片描述
如果是查询 name 前缀为林的用户,那么就会走索引扫描,执行计划中的 type=range 表示走索引扫描,key=index_name 看到实际走了 index_name 索引:

// name 字段为二级索引
select * from t_user where name like '林%';

在这里插入图片描述
为什么like关键字左或者左右模糊匹配无法总索引?
因为索引B+树是按照索引值有序排列存储的,只能根据前缀进行比较。

对索引使用函数

如果查询条件中对索引字段使用函数,就会导致索引失效。
比如下面这条语句查询条件中对 name 字段使用了 LENGTH 函数,执行计划中的 type=ALL,代表了全表扫描:

// name 为二级索引
select * from t_user where length(name)=6;

在这里插入图片描述

索引保存的是索引字段的原始值,而不是经过函数计算后的值,所以函数计算的值无法走索引。
但从MYSQL8.0开始,索引特性增加了函数索引,即可以针对函数计算后的值建立一个索引,也就是说该索引的值是函数计算后的值,可以通过扫描索引来查询数据。

对索引进行表达式计算

比如,下面这条查询语句,执行计划中 type = ALL,说明是通过全表扫描的方式查询数据的:

explain select * from t_user where id + 1 = 10;

在这里插入图片描述
但是,如果把查询语句的条件改成 where id = 10 - 1,这样就不是在索引字段进行表达式计算了,于是就可以走索引查询了。
在这里插入图片描述
因为索引保存的是索引字段的原始值,而不是id+1表达式计算后的值,所以无法走索引。

对索引隐式类型转换

MySQL 在遇到字符串和数字比较的时候,会自动把字符串转为数字,然后再进行比较。如果字符串是索引列,而条件语句中的输入参数是数字的话,那么索引列会发生隐式类型转换,由于隐式类型转换是通过 CAST 函数实现的,等同于对索引列使用了函数,所以就会导致索引失效。
详情见参考链接:https://xiaolincoding.com/mysql/index/index_lose.html#%E5%AF%B9%E7%B4%A2%E5%BC%95%E9%9A%90%E5%BC%8F%E7%B1%BB%E5%9E%8B%E8%BD%AC%E6%8D%A2

联合索引非最左匹配

多个普通字段组合在一起创建的索引叫做联合索引,也叫组合索引。
创建联合索引时,我们需要注意创建时的顺序问题,因为联合索引 (a, b, c) 和 (c, b, a) 在使用的时候会存在差别。

联合索引要能正确使用需要遵循最左匹配原则,也就是按照最左优先的方式进行索引的匹配。

比如,如果创建了一个 (a, b, c) 联合索引,如果查询条件是以下这几种,就可以匹配上联合索引:

where a=1;
where a=1 and b=2 and c=3;
where a=1 and b=2;
需要注意的是,因为有查询优化器,所以 a 字段在 where 子句的顺序并不重要。

但是,如果查询条件是以下这几种,因为不符合最左匹配原则,所以就无法匹配上联合索引,联合索引就会失效:

where b=2;
where c=3;
where b=2 and c=3;
有一个比较特殊的查询条件:where a = 1 and c = 3 ,符合最左匹配吗?

这种其实严格意义上来说是属于索引截断,不同版本处理方式也不一样。

MySQL 5.5 的话,前面 a 会走索引,在联合索引找到主键值后,开始回表,到主键索引读取数据行,然后再比对 c 字段的值。

从 MySQL 5.6 之后,有一个索引下推功能,可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

大概原理是:截断的字段会被下推到存储引擎层进行条件判断(因为 c 字段的值是在 (a, b, c) 联合索引里的),然后过滤出符合条件的数据后再返回给 Server 层。由于在引擎层就过滤掉大量的数据,无需再回表读取数据来进行判断,减少回表次数,从而提升了性能。

联合索引的情况下,数据是按照索引第一列排序,第一列数据相同时才会按照第二列排序。

也就是说,如果我们想使用联合索引中尽可能多的列,查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索,肯定无法走索引。

WHERE子句中的OR

如果OR前的条件列是索引列,而OR后的条件列不是索引列。那么索引会失效。
因为 OR 的含义就是两个只要满足一个即可,因此只有一个条件列是索引列是没有意义的,只要有条件列不是索引列,就会进行全表扫描。

总结:
6中索引失效情况;
我们使用左或者左右模糊匹配的时候,也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效;
当我们在查询条件中对索引列使用函数,就会导致索引失效。
当我们在查询条件中对索引列进行表达式计算,也是无法走索引的。
MySQL 在遇到字符串和数字比较的时候,会自动把字符串转为数字,然后再进行比较。如果字符串是索引列,而条件语句中的输入参数是数字的话,那么索引列会发生隐式类型转换,由于隐式类型转换是通过 CAST 函数实现的,等同于对索引列使用了函数,所以就会导致索引失效。
联合索引要能正确使用需要遵循最左匹配原则,也就是按照最左优先的方式进行索引的匹配,否则就会导致索引失效。
在 WHERE 子句中,如果在 OR 前的条件列是索引列,而在 OR 后的条件列不是索引列,那么索引会失效。

  • 4
    点赞
  • 21
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值