【MySQL 一数据库基础】深入解析 MySQL 的索引(2)

在这里插入图片描述

索引

MySQL中的页

为什么要使用页？

在.ibd文件中最重要的结构体就是Page(页)，页是内存与磁盘交互的最小单元，默认大小为16KB，每次内存与磁盘的交互至少读取一页，，所以在磁盘中每个页内部的地址都是连续的;

之所以至少读取一页记录，而不是一行记录，是因为在使用数据的过程中，根据局部性原理，将来要使用的数据，大概率与当前访问的数据在空间上是临近的；

所以一次从磁盘中读取一页的数据放入内存中，当下次查询的数据还在这个页中时，就可以从内存中直接读取，从而减少磁盘I/O提高性能；

在这里插入图片描述

查看页的大小的语法

show variables like 'innodb_page_size';  -- 可以通过系统变量innodb_page_size，查看页的大小

在这里插入图片描述

16384 / 1024 = 16 ( 16KB为页空间的默认大小 )

在这里插入图片描述

数据页

概念

在MySQL中有多种不同类型的页，最常用的就是用来存储数据和索引的"索引页"，也叫做"数据页"，但不论哪种类型的页，都会包含页头(File Header)和页尾(File Trailer)，页的主体信息使用数据"行"进行填充；

结构

数据页的基本结构如下图所示:

在这里插入图片描述

页文件头和页文件尾

在这里插入图片描述

页主体

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

在这里插入图片描述

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

在这里插入图片描述

页目录

一个数据页大小为16KB，但是一条记录可能只有4Byte，很可能一个数据页就可以存储几百条记录，这几百条记录是以一个单向链表的结构组织在一起，查询其中一条记录的时间复杂度为 O(N)，为了优化查询效率，我们引入页目录：

在这里插入图片描述

数据页头

数据页头记录了当前页保存数据相关的信息，如下图所示：

在这里插入图片描述

B+在MySQL索引中的应用

非叶子节点保存索引数据，叶子节点保存真实数据，如下图所示：

在这里插入图片描述

先加载页到内存，然后找子节点的地址，通过地址找到页，再通过页中的槽找到记录；

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

在这里插入图片描述

索引分类

主键索引

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

普通索引

为了提升查询效率，工作中通常为查询频繁的列创建索引，自创建的索引就是普通索引；
普通索引是最基本的索引类型，没有唯一性的限制，可能为一个列创建索引，也可能为多列创建组合索引；
为多个列创建组合索引，该组合索引称为复合索引或组全索引。
对于一个有大量重复值的列（如性别列，状态列…），不建议创建索引。
创建索引之后都会生成一棵索引树，创建多少个索引就生成多少棵索引树；
创建索引后，生成的索引树也是会占用空间的，所以创建索引时也需要慎重考虑一下需不需要，索引树越多，对insert , drop , update操作的影响会比较大，因为进行这些操作需要更新维护索引树。

唯一索引

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

全文索引

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

聚集索引

与主键索引是同义词
如果没有为表定义PRIMARY KEY，InnoDB 使用第一个 UNIQUE和 NOT NULL 的列作为聚集索引。

在这里插入图片描述

如果表中没有 PRIMARY KEY 或合适的 UNIQUE 索引，InnoDB会为新插入的行生成一个行号，并用 6字节的 ROW_ID 字段记录（这是数据行中的隐藏列之一) ，ROW_ID 单调递增，并使用 ROW_ID 做为索引。
所以主键值的列一定会填入值，可能是 primary key 所在列的值，也可能是 第一个 UNIQUE和 NOT NULL 列所在的值，也可能是自动生成的 ROW_ID 所在的列的值。

非聚集索引 / 二级索引

聚集索引以外的索引称为非聚集索引或二级索引;

二级索引中的每条记录都包含该行的主键列，以及二级索引指定的列;

InnoDB使用这个主键值，来搜索聚集索引中的行，这个过程称为回表查询。

非聚集索引通俗讲解

首先，想象一下你有一本书，这本书里面有很多章节和内容。当你翻到某个章节时，你需要先翻到书的目录（索引）找章节的页码，然后根据页码找到对应的内容。

非聚集索引就像这个目录，它告诉你某个数据的位置，但它本身并不包含所有的数据，只是指向数据的位置。也就是说，非聚集索引是单独存放的，它和数据表本身是分开的。

非聚集索引的查询过程

假设你有一个员工表 employees，字段包括 emp_id（员工ID）、emp_name（员工姓名）和 salary（薪水）。现在，你要查询某个员工的名字和薪水，查询条件是 emp_id = 101。

查询步骤

查索引：数据库首先查看非聚集索引，假设你查询的是 emp_id，索引中会存有 emp_id 和它对应的记录位置（类似目录中的页码）。
找到位置：通过查找索引，数据库知道 emp_id = 101 的记录在哪个位置（就像从目录找到具体页码）。
回到数据表：数据库根据索引找到的数据位置，去原始的数据表中找出完整的记录（因为非聚集索引本身不存储实际数据，只是位置指针）。
返回结果：拿到数据后，返回查询的结果。

例子

假设你的索引包含了员工ID（emp_id）和它在表中的位置。你要查找 emp_id = 101 对应的 emp_name 和 salary，非聚集索引会告诉你 emp_id = 101 在表中的位置，然后数据库去表里找到完整的员工信息。

总结

非聚集索引就像一本书的目录，它只包含“数据的位置”，而不包含实际的数据。
查询时，数据库首先查索引（目录），然后再去数据表中获取完整的数据（就像根据目录找到书的具体内容）。

这种方式比直接在表里查要快，但因为需要两次查找（先查索引，再查数据表），它的查询效率比聚集索引稍慢一些。

回表查询

想象你有两个文件夹：

一个文件夹里存的是“用户信息”（比如：名字、年龄等）。
另一个文件夹里存的是“订单信息”（比如：订单号、金额等）。

假设你想做一件事情：查找每个订单对应的用户名字。

你已经知道订单的 user_id（每个订单都有一个用户 ID），但是文件夹 2（订单信息）里没有用户的名字，只有 user_id。
于是，你就需要去文件夹 1（用户信息）找出这个 user_id 对应的名字。
这个过程，就是所谓的“回表”查询。数据库帮你去一个表里找你要的额外信息，把它“带回来”给你。

简单来说，回表就是数据库去别的表找额外的数据，把你想要的完整信息拿回来。

索引覆盖

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

简单来说，在数据库中，索引覆盖意味着查询可以直接通过索引获取所需的所有数据，而无需访问表的完整记录。换句话说，索引包含了查询所需的全部信息。

假设我们有一个表 employees，它记录了员工的信息，包含以下字段：

emp_id（员工ID）
emp_name（员工姓名）
salary（薪资）
department（部门）

我们在表上建立了一个索引，这个索引只包含 emp_id 和 emp_name 字段。

现在，我们查询某个员工的姓名：

SELECT emp_name FROM employees WHERE emp_id = 101;

如果我们查询的字段（在这个例子中是 emp_name）在索引中就能找到，并且这个索引包含了查询所需的全部数据，那么数据库就不需要回到表里去查找，它可以直接从索引中拿到结果。

这种情况就是索引覆盖，它避免了访问数据表，因此提高了查询速度。

总结

在这里插入图片描述