MySQL索引的数据结构

索引的数据结构

本专栏学习内容来自尚硅谷宋红康老师的视频

有兴趣的小伙伴可以点击视频地址观看

1. 为什么要使用索引？

索引是存储引擎用于快速找到数据记录的一种数据结构，就好比去图书馆找书，或者新华字典里找字，相当于一个目录，可以帮助我们快速的查找到数据所在的位置。

在MySQL中也是同样的道理，进行数据查找时，首先看查询条件是否命中索引，符合则通过索引查找相关数据，如果不符合则需要全表扫描，即一条一条的查找记录，知道找到与条件符合的记录。

对于MySQL来说，数据是存储在磁盘上的，当我们总共有10条数据时如果使用全表扫描，则是与磁盘进行10次I/O操作，这是非常耗时间的；如果索引使用二叉搜索树来存储数据，将会大大减少我们的I/O次数。对数据结构不了解的可以查看小黄的另外一篇文章

2. 索引及其优缺点

2.1 索引概述

MySQL官方对索引的定义为：索引是帮助MySQL高校获取数据的数据结构。

索引的本质：索引是数据结构，可以理解为“排好序的快速查找数据结构”，满足特定查找算法。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法

索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。

2.2 优点

• 提高数据检索效率，降低数据库的IO成本
• 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
• 在实现数据的参考完整性方面，可加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询效率
• 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗

2.3 缺点

• 创建索引和维护索引要消耗时间，并且随着数据量的增加，所耗费的时间也会增加
• 索引需要占用磁盘空间，除了数据表占数据空间外，每一个索引还要占一定的物理空间，存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度

3. InnoDB中索引的推演

3.1 设计索引

首先，建一个表：

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

这个新建的index_demo表有2个INT类型的列，1个CHAR类型的列，而且规定了c1位逐渐，这个表使用Compact行格式来实际存储记录，行格式之后会学习到。以下是简化了行格式的示意图

我们只在示意图中展示记录这几个部分

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

将行格式示意图的其他信息项暂时去掉并把它竖起来的效果是这样的：

把一些记录放到页里的示意图就是这样的：

1. 一个简单的索引设计方案

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

• 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。

  假设：每一个数据页最多能存放三条记录，实际上一个数据页非常大，可以存放好多记录。向表中插入3条记录，填充数据页

  INSERT INTO index_demo VALUES(1,4,'u'),(3,9,'d'),(5,3,'y');
  

  那么这些记录已经按照主键值的大小串联成一个单向链表了

  

  那这时候我们插入一条主键为4的记录，这个数据页已经显示不下了，只能新建一个数据页，而且因为4 < 5，所以这条记录应该保存在页10中，把主键为5的记录移动到下一个页中。

  

• 给所有的页建立一个目录项。

  由于数据页的编号可能是不连续的，所以插入了多条记录后，可能会出现以下的情况：

  

  因为这些数据页在物理存储上是不连续的，所以如果想从这么多页中根据主键值快速定位某些记录所在的位置，我们需要给他们做一个目录，每一个页对应一个目录项，每个目录项包括下边两个部分：

  • 页的用户记录中最小的主键值，用key表示
  • 页号，用page_no表示

  

  以页28为例，它对应的目录项2，这个目录项包含页号，以及用户记录的最小主键值5.我们只需要把这几个目录项在物理存储器上连续存储，就可以事项根据主键快速查找某条记录的功能了。比如：查找主键为30的记录，具体查找过程分2步

  1. 先从目录项中根据二分法快速确定出主键值为20的记录在目录项3中，它对应的是页9
  2. 再去页9中定位具体的记录

2. InnoDB中的索引方案

1⃣️ 迭代1次：目录项记录的页

上边称为一个简易的索引方案，是因为我们为了根据主键值进行查找时使用二分法快速定位具体的目录项而假设所有目录项都可以在物理存储器上连续存储，但是这样做有几个问题：

• InnoDB是使用页来作为管理存储空间的基本单位，最多能保证16KB的连续存储空间，而随着表中记录数量的增多，需要非常大的连续的存储空间才能把所有的目录项都放下，这对记录数量非常多的表是不现实的。
• 我们时常会对记录进行增上，假设我们把页28中的记录删除了，那意味着目录项2也就没有存在的必要，这就需要把目录项2后的目录项都向前移动一下，这样牵一发而动全身的操作效率很差

所以我们需要一种灵活管理所有目录项的方式，目录项其实跟用户记录差不多，也可以使用用户记录的方式来存储，为了和用户记录作区分，我们把这些迎来表示目录项的记录称为目录项记录。InnoDB用recrd_tyoe来区分这些记录

• 0：普通的用户记录
• 1：目录项记录
• 2：最小记录
• 3：最大记录

我们把前面使用到的目录项放到数据页中就是下面这个样子

2⃣️ 迭代2次：多个目录项记录的页

虽然说目录项记录中只存住主键值和对应的页号，比用户记录需要的存储空间小多了，但是不论怎么说，一个页只有16KB大小，能存放的目录项记录是有限的，如果超过了一个目录记录想的最大值，那就需要分配一个新的存储目录记录项的页

3⃣️ 迭代3次：目录记录项的目录列

在上面查询步骤的第一步中，我们需要定位存储目录项记录的页，但是这些页是不连续的，如果我们表中数据非常多，则会产生很多存储目录项记录的页，那就需要为这些存储目录项记录的页再生成一个更高级的目录，就像是一个多级目录一样，大目录嵌套小目录，小目录里才是实际的数据。

4⃣️ B+Tree

像上述这种数据结构，我们把他称作B+树，不论是存放用户记录的数据页，还是存放目录项记录的数据页，我们都把他放到B+树中，所以我们也成这些数据页为节点。从图中可以看出存放用户记录的数据页到了B+树的最底层节点上，这些节点也被称为叶子节点，其余用来存放目录项记录的节点称为非叶子节点，其中B+树最上边的那个节点也称为根节点

一般情况下，我们用到的B+树不会超过4层，那我们通过主键值去查找某条记录最多只需要做4个页面内的查找，又因为每个页面内有所谓的页目录，所以在页面内也可以通过二分法实现快速的定位。

3.2 常见索引概念

1. 聚簇索引

特点：

1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

   • 页内的记录是按照主键的大小顺序排成一个单向链表
   • 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
   • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表

2. B+树的叶子节点存储的是完整的用户记录

   完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）

我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建，InnoDB存储引擎会自动的为我们创建聚簇索引。

优点：

• 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
• 聚簇索引对于主键的排序查找和范围查找速度非常快
• 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作

缺点：

• 插入速度严重依赖插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能，因此对于InnoDB表，我们一般都会定义一个自增的ID列为主键
• 更新主键的代价很高，因为将会导致被更新的行移动。因此对于InnoDB表，我们一般定义主键为不可更新
• 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

限制：

• 对于MySQL数据库，目前只有InnoDB数据引擎支持聚簇索引，而MyISAM并不支持聚簇索引
• 由于数据物理存储排序方式只能由一种，所以每个MySQL的表只能有一个聚簇索引。一般情况下就是该表的主键
• 如果没有定义主键，InnoDB会选择非空的唯一索引代替。如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引
• 为了充分利用聚簇索引的聚簇的特性，索引InnoDB表的主键列尽量选择有序的顺序id，而不建议用无序的id，比如UUID、MD5、HASH、字符串列作为主键无法保证数据的顺序增长

2. 二级索引

二级索引也被称为辅助索引、非聚簇索引，上面介绍的聚簇索引只能在搜索条件是主键时才能发挥作用，因为B+树的数据都是按照主键进行排序的。

在实际开发过程中，我们经常使用别的列作为搜索条件，如果使用该列为搜索条件的频率非常高时，我们就可以考虑使用此列创建一个二级索引，依次来提升搜索的速度。

这个B+树于聚簇索引的B+树稍有不同

• 使用记录c2列的大小进行记录和页的排序，这包括三个方面的含义：
  • 页内的记录是按照c2列的大小顺序排成一个单向链表
  • 各个存放用户记录的页也是根据页中用户记录的c2列的大小顺序排成一个双向链表
  • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的c2列的大小顺序排成一个双向链表
• B+树的叶子节点存储的并不是完整的用户记录，而只是c2列+主键这两个列的值
• 目录项记录中不再是主键+页号的搭配，而是c2列+页号的

通过二级索引搜索，需要先从二级索引的B+树找到符合条件的主键id，然后再去聚簇索引的B+树进行搜索。这里就会有同学想问了，为什么不在二级索引的B+树中存储完整的用户记录呢？

如果把完整的用户记录放到叶子节点是可以不用回表，但是太占用地方了，相当于每创建一个索引，就将用户记录都拷贝一份，非常浪费存储空间。

聚簇索引与非聚簇索引的原理不同，在使用上也有一些区别

1. 聚簇索引的叶子节点存储的就是我们的数据记录，非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响表的物理存储数据
2. 一个表只能由一个聚簇索引，因为只能有一种排序存储方式，但可以有多个非聚簇索引
3. 使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低。这是因为修改非聚簇索引，只需要操作当前的B+树，而如果修改聚簇索引，不仅要操作当前B+树，所有非聚簇索引的叶子节点的id都需要修改

3. 联合索引

联合索引也是二级索引的一种，如果我们使用了多个列创建索引，例如先按照c2排序，c2相同按照c3排序，那么这就是一个联合索引。

联合索引相较于二级索引，在每一个节点上存储的数据更多了，这里就画一个草图，不再赘述了。

3.3 InnoDB的B+树索引的注意事项

1. 根页面位置万年不动

我们前面介绍B+树索引的时候，为了理解上的方便，先把存储用户记录的叶子节点画出来，然后接着画存储目录项的内节点，实际上B+树的形成过程是这样的：

• 每当为某个表创建一个B+树缩影的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中即没有用户记录，也没有目录项记录
• 随后向表中插入用户记录时，先把用户u记录存储到这个根节点中
• 当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这是新插入的记录根据键值的大小就会分配到页a或者页b中，而根节点便升级为存储目录项记录的页

这个过程特别注意的是：一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡事InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方去除根节点的页号，从而来访问这个索引。

2. 内节点中目录项记录的唯一性

上面讲的二级索引的图示其实并不是完整的，我们知道B+树索引的内节点中目录项记录的内容是索引列+页号搭配的，但对于二级索引来说有点不严谨。假如我们在表中插入如下数据，以C2为索引列创建二级索引

C1	C2	C3
3	1	a
9	1	b
4	1	c

现在的B+树是这样的，但是当我们在插入一条这样记录时，走到目录页记录的时会发现key都等于1，这时候他就不知道如何走下去

C1	C2	C3
5	1	d

那么实际上在存储二级索引时，B+树的目录页记录不仅仅会存储索引列，还会存储主键值，可以理解为索引列和主键列的联合索引，这样就确保了内节点中目录项记录的唯一性，如下图所示

3. 一个页面最少存储2条记录

这个其实不难理解，如果不存储两条记录，就无法生成一颗最基本的树，所以InnoDB的一个数据页至少可以存放两条记录

4. MyISAM的索引方案

MyISAM引擎也使用B+树作为索引结构，不同的是叶子节点的data域存放的是数据记录的地址

4.1 MyISAM索引的原理

在InnoDB索引中，索引即数据，那是因为完整的用户记录都包含在聚簇索引的叶子节点下。

而MyISAM索引虽然使用了树形结构，但是将索引和数据分开存储。

• 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件不划分为若干个数据页，有多少条记录就忘这个文件中塞多少记录就成了。由于在插入数据的时候没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找
• 使用MyISAM存储引擎的表会把索引信息另外存储到一个成为索引文件的另一个文件中，MyISAM会单独为表的主键创建一个索引，只不过索引的叶子节点中存储的并不是完整的用户记录，而是主键值 + 数据记录地址的组合

4.2 MyISAM与InnoDB对比

MyISAM的索引方式都是非聚簇的，与InnoDB包含一个聚簇索引是不同的

• 在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引
• InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的
• InnoDB的非聚簇索引data域存储相应记录主键的值，而MyISAM存储的是地址
• MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据，而InnoDB是通过获取主键值之后再去聚簇索引里找记录
• InnoDB要求表必须有主键，而MyISAM可以没有主键

5. 索引的代价

索引是个好东西，但不可以乱建，他在空间和时间上都会有消耗：

• 空间上的代价

  每建立一个索引都要为他创建一颗B+树，每一颗B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一颗很大的B+树由许多数据页组成，那就非常占用存储空间了。

• 时间上的代价

  索引是需要维护的。每次对表的增、删、改操作时，都需要去修改各个B+树的索引。索引的每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点的记录，都是按照索引列的值从小到大形成了一个单向链表。而增、删、改的操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂，页面回收等操作维护好节点和记录的排序。如过索引太多，每个索引对应的B+树都要惊喜相关的维护操作，会给性能拖后腿。

一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。为了建立又好又少的索引，我们得学习这些索引在哪些条件下起作用