【MySQL】第八弹——索引(上)

🌍索引

🪐什么是索引

MySQL的索引是⼀种数据结构，它可以帮助数据库高效地查询、更新数据表中的数据。索引通过⼀定的规则排列数据表中的记录，使得对表的查询可以通过对索引的搜索来加快速度。
MySQL索引类似于书籍的目录，通过指向数据行的位置，可以快速定位和访问表中的数据，比如汉语字典的目录（索引）页，我们可以按笔画、偏旁部首、拼音等排序的目录(索引)快速找到需要的字

• 笔画索引

确定要查询的字有多少画,再去画数节点下找目标自,一步找到 - 一对一的关系

• 偏旁部首索引

先确定查询的字的偏旁,再去对应的画数节点下找目标字 两步

• 拼音索引

先去找声母,再去找韵母 ,然后找到一声对应的页数,再去这个页中顺序查找目标字 一声 ~ 四声

⭐为什么使用索引

显而易见,使用索引的目的只有一个,就是提升数据检索的效率,再应用程序的运行程序的过程中,查询操作的频率远远高于增删改的频率

MySQL实现的两个关键目标,保证安全的基础上,实现高效率

🪐索引应该选择哪种数据结构

⭐HASH

时间复杂度:O(1)

查询速度非常快,但是MySQL并没有选择HASH作为索引的默认数据结构,主要原因是不支持范围查找

⭐二叉搜索树

二叉树的中序遍历是一个有序数组 --> 支持范围查找

时间复杂度:可能会退化成一个单边树O(N)

缺点:节点个数过多时,无法保证树的高度
由于数据库中的数据实在磁盘上保存的,每一次访问子节点都会发生一次磁盘IO
磁盘IO是制约 数据库性能的主要因素

⭐N叉树 (B树)

每个节点可以有超过两个的子节点,可以解决树高的问题

时间复杂度:O(logN)

在数据量相同的情况下,可以有效的控制树高,也就是说可以使用更少的IO次数找到目标节点,从而提高数据库的效率

⭐B+树

B+树是一种经常用于数据库和文件系统等场合的平衡查找树,MySQL索引所采用的数据结构

以4阶B+树为例:

时间复杂度:O(logN) 可以有效的控制树高

✨B+树特点

1. 能够保持数据稳定有序,插入与修改有效稳定的时间复杂度
2. 非叶子节点仅具有索引作用,不存储数据,所有叶子结点保存真实数据
3. 所有的叶子节点构成一个有序链表,可以按照key 排序的次序依次遍历全部数据

✨B树与B+树对比

1. 叶子节点之间有一个相互连接的应用,可以通过一个叶子结点找到其他的兄弟节点
MySQL在组织叶子节点时使用双向链表
2.非叶子节点的值都包含在叶子节点中
MySQL 非叶子节点只保存对子节点的引用,没有保存真实的数据,所有真实的数据都保存在叶子结点中
3.对于B+树而言,在相同的树高下,查找 任一元素的时间复杂度都一样,性能平衡

🌍索引是如何工作的

索引在整个数据检索的过程中是如何工作的,要从MySQL存储结构说起

🪐MySQL中的页

⭐为什么使用页

在 .ibd 文件中最重要的结构体就是Page(页)，页是内存与磁盘交互的最小单元，默认大小为16KB，每次内存与磁盘的交互至少读取一页，所以在磁盘中每个页内部的地址都是连续的，之所以这样做，是因为在使用数据的过程中，根据局部性原理，将来要使用的数据大概率与当前访问的数据在空间上是临近的，所以一次从磁盘中读取一页的数据放入内存中，当下次查询的数据还在这个页中时就可以从内存中直接读取，从而减少磁盘I/O提高性能

每一个页中即使没有数据也会使用16KB的存储空间,同时与索引的B+树中的节点对应

• 查看页大小

当从内存中王磁盘里写数据页的时候,写到一半操作系统突然挂了这时MySQL应该如何保证数据安全?

在落盘之前会记录各种日志,保证重启之后可以找到没有落盘的数据内容

⭐页文件头和页文件尾

这里我们只关注,上一页页号和下一页页号,通过这两个属性可以把页与页之间连接起来,形成一个双向链表

⭐页主体

• 页主体部分是保存真实数据的主要区域，每当创建一个新页，都会自动分配两个行，一个是页内最小行 Infimun，另一个是页内最大行 Supremun，这两个行并不存储任何真实信息，而是做为数据行链表的头和尾，第一个数据行有一个记录下一行的地址偏移量的区域 next_record 将页内所有数据行组成了一个单向链表

• 当向一个新页插入数据时，将 Infimun 连接第一个数据行，最后一行真实数据行连接 Supremun，这样数据行就构建成了一个单向链表，更多的行数据插入后，会按照主键从小到大的顺序进行链接

⭐页目录

• 例如要查找主键为6的行
  1.首先要找到这个记录的所有页
  2.在页中找到所在的槽
  3.在槽对应的分组中找到对应的记录
  没有就返回空

⭐数据页头

数据页头记录了当前页保存数据相关的信息

🌍B+树在MySQL索引中的应用

• 非叶子节点保存索引数据,叶子结点保存真实数据

• 以查找id为5的记录，完整的检索过程：
  1.首先判断B+树的根节点中的索引记录，此时 5 < 7 ,应访问左孩子节点，找到索引页2
  2.在索引页2中判断id的大小，找到与5相等的记录，命中，加载对应的数据页

以上的IO过程，加载索引页1 --> 加载索引页2 --> 加载索引页3 共三次IO

所有关于页的操作和访问都是在内存中进行的

🪐计算三层树高的B+树可以存放多少条记录-理论上的

一个数据页默认为16KB。假设一条数据为1KB，一页中至多可以存16条数据

索引页中存的是主键值和子节点的引用，也就是说下一个节点的偏移（地址）

主键 bigint类型 占8Byte 下一页地址 6Byte 也就是说一条索引记录占 8 + 6 = 14Byte

一个索引页可以存 16 * 1024 / 14 = 1170
理论上一个三层树高的B+树可以存：1170 * 1170 * 16 = 21,902,400 条记录
在当前的场景下，表中有21，902,400条记录的情况下，通过3次IO就可以完成数据的查询

加载索引页:把索引页加载到内存中进行缓存,当查到一条没有加载过的数据时,一次真实的IO就可以搞定

🌍索引分类

🪐主键索引

• 当一个表上定义一个主键PRIMARY KEY 时,自动创建索引,InnoDB使用它作为聚集索引/聚簇索引
• 推荐为每个表定义一个主键,如果没有逻辑上唯一且非空的列或列集可以使用主键,则添加一个自增列

🪐普通索引

• 最基本的索引类型,没有唯一性的限制
• 可能为多列创建组合索引,称为复合索引或组合索引

1.为提升查询效率,工作中通常为查询频繁的列创建索引,首先必须是列的重复度不高的情况下才可以
如果是gender 虽然会进行频繁查询,但是上万条数据只有两个值,男或女 重复度太高了,并不适合创建索引

2.可以包含一个列也可以包含多个列
3.创建索引后会生成一颗索引树,创建多少索引就会有多少索引树
生成的是索引树,会占用磁盘空间,创建索引是,要慎重考虑需不需要
索引树越多,对增删改的效率影响越大

🪐唯一索引

• 当在一个表上定义一个唯一键UNIQUE时,自动创建唯一索引
• 与普通索引类似,但区别在于唯一索引的列不允许有重复值

🪐全文索引

• 基于文本列(CHAR VARCHAR或TEXT)上创建,以加快对这些列中包含的数据查询和DML操作
• 用于全文搜索,仅MyISAM和InnoDB引擎支持

如果要存储文档类的数据,我们会专门使用文档类的数据库,全文索引用的并不多

🪐聚集索引

• 与主键索引是同义词
• 如果没有表定义PRIMARY KEY InnoDB使用第一个UNIQUE 和 NOT NULL的列作为聚集索引
• 如果表中没有PRIMARY KEY 或合适的UNIQUE 索引,InnoDB会为新插入的行生成一个行号并用**6字节的ROW_ID字段记录,**ROW_ID 单调递增,并使用ROW_ID作为索引

聚集索引可以标识数据行的唯一性
ROW_ID 是数据行中的一个隐藏列之一

🪐非聚集索引

• 聚集索引以外的索引称为非聚集索引或二级索引
• 二级索引中的每条记录都包含该行的主键列,以及二级索引指定的列
• InnoDB使用这个主键值来搜索聚集索引中的行,这个过程称为回表查询

🪐索引覆盖

• 当一个select语句使用了普通索引且查询列表中的列刚好是创建普通索引是的所有或部分列,这是就可以直接返回数据,而不用回表查询,这样的现象称为索引覆盖

🪐非聚集索引的查询过程

1.通过索引查到叶子结点中的索引记录
2.通过索引记录中的主键值,去主键索引树中找相应的完整记录–回表查询

回表查询:

通过普通索引找到了记录之后在通过索引记录里面的主键值回到主键索引树中找到完整记录这是两次查询,第一次查询是普通索引树,第二次查询是完整记录的主键索引树

索引覆盖:

当前组合索引中使用学号来查询姓名索引生不生效(走不走索引)?

select name  from  student where sn = '10001';

创建索引时,name 列在sn列之前, 那么使用的时候也要先使用name再使用sn – 就像拼音索引是要先找声母再找韵母,不能反过来
如果只使用sn列,那么索引就会失效,如果非要使用sn列查,可以为sn单独创建一个索引