MySQL —— 索引

介绍MySQL 的索引，索引的运作流程分析，索引的创建修改以及删除。

熵减玩家

2206人浏览 · 2024-09-16 15:03:51

熵减玩家 · 2024-09-16 15:03:51 发布

索引的概念

MySQL的索引是⼀种数据结构，它可以帮助数据库高效地查询、更新数据表中的数据。索引通过
⼀定的规则排列数据表中的记录，使得对表的查询可以通过对索引的搜索来加快速度。

MySQL索引类似于书籍的目录，通过指向数据行的位置，可以快速定位和访问表中的数据，比如
汉语字典的目录（索引）页，我们可以按笔画、偏旁部首、拼音等排序的⽬录（索引）快速查找到需
要的字。

探讨索引的数据结构

我们来一起探讨那种数据结构用来当作索引更适合数据库

首先是哈希表，对于哈希表来说，查找的时间复杂度为 O(1)，十分优秀，但是却不适合数据库，因为哈希表不支持范围查找，而我们数据库是需要支持范围查找的，例如：我们有张成绩表，要查询成绩大于等于60以上的学生时，这时就要求我们数据库能支持**范围查找，**所以哈希表并不适合作索引

接下来登场的是二叉搜索树，查找的时间复杂度为O(logN)，但是如果出现极端情况，二叉搜索树是可能会退化成一颗单分支的树，这时候时间复杂度就变成O(N)，还是不理想，这时候大家可能会想到AVL树或者红黑树，AVL树就不说了，旋转次数太多了，在数据库面前，数据量十分庞大，如果旋转，呵呵…
我们来看一下红黑树，虽然保持相对平衡，但是在数据一多的情况下，我们无法保证树到底有多高

为什么要讨论树高呢？
因为数据库的数据是存储在磁盘上的，所以当你需要读取数据的时候，是需要进行磁盘的IO的，磁盘的IO速度是十分慢的，如果IO 次数一高，效率就会低下哎，所以我们应该减少磁盘IO次数，也就是减小树高，那么红黑树就不能满足了

磁盘IO 是制约数据库性能的主要因素

既然红黑树不行，那我们可以考虑B树，这时一颗多路平衡树，由于是多路，所以可以降低书高，但是我们的MySQL还是不满意，觉得效率还不是不够高，于是MySQL 就使用B树的变形也就是B+树，我们在前面就知道B+树有一些特点：真实的数据都是保存在叶子节点上的，非叶子结点只是起到一个导航的作用，并且叶子结点是使用双向链表进行连接的，所以在数据库进行范围查找的时候十分方便。

B树示例：
在这里插入图片描述

B+树示例：
在这里插入图片描述
B+树的时间复杂度是O(logN)，并且可以有效的控制树高

B+树与B树的对比：
1.B+树的叶子结点之间有相互连接的引用，可以通过这个连接找到与其相邻的兄弟节点，mysql 在组织叶子结点时使用的时双向链表
2.非叶子结点的值包涵在叶子结点中，MySQL 非叶子结点只保存了对子结点的引用，没有保存真实的数据，所有真实的数据都是在叶子结点中
3.对于B+树而言，在相同书高的情况下，查找任意元素的时间复杂度都是一样的，性能均衡。

MySQL的页

在 .ibd 文件中最重要的结构体是 Page(页)，页是内存与磁盘交互最小单元，默认大小是 16KB

在这里插入图片描述

每一个.ibd 文件由页组成
在这里插入图片描述

每次内存与磁盘的交互至少读取一页，所以在磁盘中每个页内部的地址都是连续的，之所以这么做，是因为在使用数据的过程中，根据局部性原理，将来要使用的数据大概率与当前访问的数据在空间上是临近的所以一次从磁盘中读取一页的数据放入内存中，当下次查询的数据还在这个页中的时候，就可以直接从内存中读取，从而减少磁盘IO，以此来提高性能。

局部性原理：
是指程序在执行时呈现出局部性规律，在一段时间内，整个程序的执行仅限于程序中的某⼀部
分。相应地，执行所访问的存储空间也局限于某个内存区域，局部性通常有两种形式：时间局部
性和空间局部性。
时间局部性（Temporal Locality）：如果⼀个信息项正在被访问，那么在近期它很可能还会被再
次访问。
空间局部性（Spatial Locality）：将来要用到的信息大概率与正在使用的信息在空间地址上是临
近的。

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

我们可以查询MySQL中页的大小：

在这里插入图片描述
16384 / 1024 = 16 KB

数据页

数据库由很多种的页，索引页、数据页等等，这里我们讨论数据页：

页头页尾

数据页由页头和页尾：
在这里插入图片描述

这里我们关注的是页头里包含上一页页号和下一页页号，通过这两个属性可以把页与与之间链接起来，形成一个双向链表。

页主体

页主体部分是保存真实数据的主要区域，每当创建一个新页时，都会自动分配两个行，一个是页内最小行Infinum，另一个是页内最大行Supremun，这两行并不存储任何真实数据，而是最为数据行链表的头和尾【你可以理解为链表的两个空节点，一个是空的头节点，另一个是空的尾节点】

第一个数据行有一个记录下一行的地址偏移量的区域next_record将页内所有的数据行组成了一个单向链表
在这里插入图片描述

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

页目录

看到这里，大家是不是又疑惑，单向链表的查找效率不是很慢吗？时间复杂度是O(N)，当然开发数据库的人也想到了，为了提高查找效率，InnoDB采用二分查找来解决查询效率问题，于是他们开发了页目录：

在这里插入图片描述

具体的实现方式是，在每一个页中加入一个叫做页目录的结构，将页内包括最大行和最小行在内的数据行进行分组，这里单独约定最小行独自为一组，其他分组最多8条数据，同时将最大行放在最后一个分组中，按照主键从小到大的顺序记录在页目录中，页目录每一个位置称为槽，每一个槽对应一个分组，一旦分组中数据行超过8个的时候，就会分裂出一个新的分组

在后续查找某条数据行时，通过二分查找，就可以找到对应的槽，然后在槽对应的分组中进行最多8条数据的便即可获得目标数据

数据页头

数据页头记录了当前页保存数据相关的信息
在这里插入图片描述

B+树在数据库中的应用

非叶子节点保存索引数据，叶子节点保存真实数据

在这里插入图片描述

举个例子：查找 id 为7 的数据行，首先通过索引页1，发现 7 = 7 ，进入到索引页2，然后 7 < 9，进入到数据页4，最后找到id 为 7 的数据行，一共进行了三次磁盘IO。

以上的IO过程，加载索引页1–>加载索引页2–>加载数据页3

我们现在来感受B+树的强大：

假设一条数据大小为 1KB，忽略数据页中页头页尾等等非真实数据的内容，也就是说一个数据页可以保存16条数据。

索引页保存的是索引，这里我们以主键索引为例，索引页保存主键值和下一页的地址，主键用bigint 来保存，也就是8字节，地址为6字节，一共就是14字节，页的大小为16KB，那么一个索引页可以保存 16 * 1024 / 14 = 1170 条索引记录

如果B+树树高为三层的话，一条索引记录对应一个数据页，那么三层B+树就可以保存 1170 * 1170 * 16 = 21,902,400 条数据（第一层一个结点，有1170 条索引，那么第二层就有1170个结点，第二层每个结点都有1170条索引），也就是说如果是在两千多万条数据的表中，我们通过三次IO 就可以完成数据的检索了。

索引的分类

主键索引

当在表中定义了一个主键primary key 的时候，MySQL会将其作为索引，这个索引我们称之为主键索引。

我们推荐在每一张表都去创建一个主键，如果没有的话，可以添加一个自增列。

唯一索引

当在表中定义一个唯一键unique 时，MySQL会自动创建索引，这个索引被称之为唯一索引

普通索引

最基本的索引类型，没有唯一性的限制，也就是相比于唯一索引来说，可以存在重复的列

普通索引可以由多列组合组成，这时候可以称为复合索引。

普通索引是由用户自己手动创建的，如果用户觉得某个列查询比较频繁，可以考虑为这一列创建索引。

全文索引

基于文本列（char、varchar）上创建的索引，叫做全文索引。

全文索引可以加快对这列文本列包含的数据查询和DML操作，用于全文搜索，仅有MyISAM 和 InnoDB 引擎支持该索引。

聚集索引

与主键索引是同义词，也就是说主键索引就是聚集索引，也可以称为聚簇索引。

如果表中没有定义主键primary key ，InnoDB 会使用第一个 unique 和 not null 的列作为聚集索引。

如果表中实在没有primary key 或者合适的 unique 索引，InnoDB 会为新生成的行生成一个行号为 6 字节的 ROW_ID 字段记录，ROW_ID 是单调递增的，并且使用 ROW_ID 作为索引。

注意ROW_ID 是MySQL内部的设置，用户是无法获取的，MySQL使用这个索引是为了更好地创建索引树来管理数据。

非聚集索引

非聚集索引（也可以称为二级索引），就是除了聚集索引以外的索引就是非聚集索引。

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

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

索引覆盖

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

简单解释一下：聚集索引创建的索引树是包含完整的真实数据的，非聚集索引创建的索引树是没有包含完整的真实数据但是包含对应的主键值，如果通过非聚集索引查询到的数据不满足用户要求会通过这个主键值来到主键索引树获取完整的数据，这个现象就是回表查询，也就是查了两个索引树

如果通过非聚集索引的索引树能获取到目标数据，就不需要进行回表查询，直接返回数据即可，这就是索引覆盖

举个例子：我们有一张学生表，包含学生的 id ，姓名，班级，其中定义学生的 id 为主键值，姓名字段定义为普通索引，现在我要查询张三的所有信息：

由于姓名就是索引，通过张三到姓名的索引树中查找，随后只能获得主键值id + 姓名为张三的数据，但是我们要的是张三所有的数据，所以数据库就会通过主键值到主键索引树去查找张三的完整的数据，这就是回表查询

如果我们至少想查张三的姓名：select name from student where name = '张三'; 这时废话sql ，至少作为演示，数据库通过姓名这个索引树就可以得到张三这个名字，直接返回数据，不需要进行回表查询，这就是索引覆盖

索引的使用

自动创建

当表中定义了主键、外键、唯一键，MySQL都会为其创建对应的索引以及索引树。

如果表中没有任何约束，MySQL回自动为每一列生成一个索引并且使用ROW_ID 进行标识

手动创建

主键索引

在创建表时，直接在字段后面定义主键：

create table test0 (
	id bigint primary key auto_increment,
	name varchar(50)
);

在创建表时，先定义好字段，最后定义主键

create table test0 (
	id bigint auto_increment,
	name varchar(50),
	primary key(id)
);

创建表之后，我们可以通过修改表来定义主键

create table test0 (
	id bigint,
	name varchar(50)
);

alter table test0 add primary key(id);

如果你还想让主键自增，还可以在写下面的语句：

alter table test0 modify id bigint auto_increment;

modify 后面要跟完整的字段定义语句。

唯一索引

在创建表时在定义字段的同时，直接定义唯一键unique

create table test1 (
	id bigint,
	name varchar(50) unique
);

创建表时，在定义完字段后，单独定义唯一键unique

create table test1 (
	id bigint,
	name varchar(50),
	unique(name)
);

在创建表后可以通过修改表的字段来定义唯一键

create table test1 (
	id bigint,
	name varchar(50)
);

alter table test1 add unqiue(name);

普通索引

创建表时，再创建完字段后，定义普通索引index

create table test2 (
	id bigint,
	name varchar(50),
	index(name)
);

通过查看表结构，我们得知普通索引的标记为MUL

在这里插入图片描述

创建完表后，可以通过修改表的方式添加普通索引index

create table test2 (
	id bigint,
	name varchar(50)
);

alter table test2 add index(name);

再创建表后，可以通过 create 语句手动创建普通索引

语法形式：create index index_name on table_name(字段名);

index_name 表示索引名，也就是说你可以自己命名索引。

推荐索引命名为 ind_表名_字段名，这样我们在查询索引的时候，可以得知这是在哪张表的普通索引，并且是哪个字段。

create table test2 (
	id bigint,
	name varchar(50)
);

create index ind_test2_name on test2(name);

复合索引

和创建普通主键一样，只是多加一些字段名。这里直接上sql 语句

create table test3(
	id bigint primary key auto_increment,
	sn bigint unique,
	name varchar(50) not null,
	class_name varchar(20) not null,
	index(sn,name)
);

create table test3(
	id bigint primary key auto_increment,
	sn bigint unique,
	name varchar(50) not null,
	class_name varchar(20) not null
);

alter table test3 add index (sn,name);

create table test3(
	id bigint primary key auto_increment,
	sn bigint unique,
	name varchar(50) not null,
	class_name varchar(20) not null
);

create index ind_test3_sn_name on test3(sn,name);

注意事项

索引的创建应该建立在高频的查询列上，并且数据量比较大，因为数据量小尽管这个是高频查询的列，但是全表扫描会比索引树要更快一些。

索引需要占用额外的空间，每创建一个索引都会创建对应的索引树。

对于表进行插入、删除、更新操作的时候，由于同时会修改索引树，可能会影响到性能。

创建过多或者不合理的索引会导致性能的下降，所以我们要谨慎创建索引。

查看索引

有三种方式查看索引：方式一：show keys from table_name;

在这里插入图片描述

我们来看一下上面的表格，Table 表示表名，
Non_unique 表示是否不唯一：0 表示否（唯一）1表示是（不唯一）
Seq_in_index 表示在索引中的标号，从 1 开始排号，test3 有一个复合索引（sn, name），所以 name 标号为 2
Column_name 表示对应的字段名
Key_name 表示索引名，主键索引默认索引名为 PRIMARY
Index_type 表示索引的数据结构，这里显示BTREE，说明是B树，其实就是B+树，B+树是B树的变形。

方式二：show index from table_name;

获得的结果集和上面是一样的：
在这里插入图片描述

方式三：desc table_name; 这是简单查询索引：

在这里插入图片描述

删除索引

删除主键索引：alter table table_name drop primary key;
在这里插入图片描述
如果发生上面的报错信息，是因为这个主键值带有自增属性，我们要先删除其自增的属性，然后才能进行删除主键索引的操作：

alter table test3 modify id bigint;

在这里插入图片描述

删除了自增属性后，可以进行主键索引的删除：alter table test3 drop primary key;

在这里插入图片描述

删除其他索引：alter table table_name drop index 索引名;

在这里插入图片描述

查看查询语句有没有走索引

以学生表为例：
在这里插入图片描述
查看索引：

进行全表数据获取：select * from student;

查看查询语句有没有走索引的语法格式为：explain 查询语句

我们现在来看一下全表数据获取有没有走索引：
在这里插入图片描述

介绍一下：select_type 表示查询方式，type 表示访问类型，key 表示索引，possible_keys 表示可能走的索引

由上图可知：查询为简单查询，访问类型为ALL(全表扫描)，key 索引为 NULL，说明没有走索引。

我们来看一下 type:

ALL	index	range	ref	eq_ref	const,system	NULL

从左到右，性能越来越好

ALL：扫描全表
index : 扫描全部索引树
range: 扫描部分索引，在索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于 between、<、>等查询
ref : 使用非唯一索引或非唯一索引前缀进行的查找，不是主键或者不是唯一索引
eq_ref：唯一性索引扫描，遂于每个索引键，表中只要一条记录与之匹配，常见于主键或唯一索引扫描
const，system : 单表中最多有一个匹配行，查询起来非常迅速，例如根据主键或唯一索引查询。system 是const类型的特例，当查询的表只有一行的情况下，使用system
NULL:不用访问表或者索引，直接就能得到结果

我们使用主键来进行查询：
在这里插入图片描述

可以看出走的是主键索引，并且进行的是范围查找。

最后我们来看一下Extra:
Using index: 表示使用索引，如果只有 Using index ，说明他没有查询到数据表，只用索引表就完成了这次查询，这个也叫索引覆盖
Using where: 表示条件查询，如果不读取表中的所有数据，或者不是仅仅通过索引树就能获得所许需要的数据的时候，则会出现 Using where