6_MySQL索引的数据结构-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_38926742/article/details/127597579

系列文章目录

一、索引是什么及索引的优缺点

1.1、索引

索引（Index）是帮助MySQL高效获取数据的数据结构。

1.2、优缺点

优点：

减少数据库的IO次数，提高查询速度。
通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
加速表和表之间的连接。
在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

缺点：

索引是数据结构，占磁盘空间。每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB 的存储空间。
创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。
降低更新表的速度。增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。要进行相关的维护操作，会给性能拖后腿。

二、InnoDB中索引的推演

2.1、设计索引

创建一个表：

CREATE TABLE index_demo(
	 c1 INT,
	 c2 INT,
	 c3 CHAR(1),
	 PRIMARY KEY(c1)
 ) ROW_FORMAT = Compact;

c1列为主键，这个表使用Compact 行格式来实际存储记录的。

每条记录竖着放：
在这里插入图片描述字段解释：

record_type ：记录头信息的一项属性，表示记录的类型， 0 表示普通记录、2 表示最小记录、3 表示最大记录、1 暂时还没用过，下面讲。
next_record ：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用箭头来表明下一条记录是谁。
各个列的值：这里只记录在index_demo 表中的三个列，分别是c1 、c2 和c3 。
其他信息：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

把一些记录放到页里的示意图就是：
在这里插入图片描述

2.1.1、简单索引设计方案

问题：我们在根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢？因为各个页中的记录并没有规律，我们并不知道我们的搜索条件匹配哪些页中的记录，所以不得不依次遍历所有的数据页。所以如果我们想快速的定位到需要查找的记录在哪些数据页中该咋办？我们可以为快速定位记录所在的数据页而建立一个目录，建这个目录必须完成下边这些事：

下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。
给所有的页建立一个目录项。
这个目录有一个别名，称为索引。

2.1.2、InnoDB中索引方案

① 迭代1次：目录项纪录的页
在这里插入图片描述

可以使用二分法来加快查询速度。
目录项记录的record_type 值是1，而普通用户记录的record_type 值是0。
目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列，另外还有InnoDB自己添加的隐藏列。

② 迭代2次：多个目录项纪录的页
在这里插入图片描述

③ 迭代3次：目录项记录页的目录页
在这里插入图片描述

④ B+Tree
之前我们做了一个非常极端的假设：存放用户记录的页最多存放3条记录，存放目录项记录的页最多存放4条记录。其实真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录，所有存放目录项记录的内节点代表的数据页可以存
放1000条目录项记录，那么：

如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放100 条记录。
如果B+树有2层，最多能存放1000×100=10,0000 条记录。
如果B+树有3层，最多能存放1000×1000×100=1,0000,0000 条记录。
如果B+树有4层，最多能存放1000×1000×1000×100=1000,0000,0000 条记录。相当多的记录！！！
我们用到的B+树都不会超过4层，层数和IO次数正相关。

2.2、常见索引概念

2.2.1、聚簇索引

上面针对C1主键建立的索引就是聚簇索引。
特点：

B+树的叶子节点存储的是完整的用户记录。
页内的记录是按照主键的大小顺序排成一个单向链表。各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

优点：

数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，不用回表了，因此从聚簇索引中获取数据比非聚簇索引更快。用的B+ Tree，IO次数少，查询也快。
聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点：

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

2.2.2、非聚簇索引（二级索引）

非聚簇索引是针对非主键字段建立索引，且叶子节点不存完整数据，存记录对应的主键。需要回表，共查两颗B+ Tree才能找到数据。

在这里插入图片描述

问题：为什么我们还需要一次回表操作呢？直接把完整的用户记录放到叶子节点不OK吗？

回表：我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程称为回表。也就是根据c2列的值查询一条完整的用户记录需要使用到2 棵B+树！因为有两次查询，所以也叫二级索引。
一个表只能有一个主键索引，可以有多个二级索引。如果我们按照多个非主键字段建立多个二级索引，里面都放用户记录（数据行），那就占用太多空间。
在这里插入图片描述

2.2.3、联合索引

同时以多个列的大小作为排序规则，也就是同时为多个列建立索引。只会建立一颗非聚簇索引。比方说我们想让B+树按
照c2和c3列的大小进行排序，这个包含两层含义：

先把各个记录和页按照c2列进行排序。
在记录的c2列相同的情况下，采用c3列进行排序

2.3、InnoDB中索引注意事项

1. 根页面位置万年不动
2. 内节点中目录项记录的唯一性
3. 一个页面最少存储2条记录

三、MyISAM中的索引方案

3.1、MyISAM索引的原理

Innodb和MyISAM默认的索引是Btree索引；而Memory默认的索引是Hash索引。
MyISAM引擎使用B+Tree 作为索引结构，叶子节点的data域存放的是数据记录的地址。

在Col1字段建立索引。
在这里插入图片描述

3.2、MyISAM 与 InnoDB对比

比较点	InnoDB	MyISAM
索引类型	聚簇索引或非聚簇索引	非聚簇索引
索引文件	.ibd文件数据文件本身就是索引文件	.myd是数据文件， .myi是索引文件，索引文件仅保存数据记录的地址
非聚簇索引data域	主键的值	地址
回表速度	快速，因为是拿着地址偏移量直接到文件中取数据的	获取主键之后再去聚簇索引里找记录
必须有主键？	是。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。	否