别告诉我，你还不懂索引

大家好，我是🐟老师，今天我们来学习索引。
文章阅读时长约13分钟。

什么是索引呢

索引是一个对存储的数据进行快速检索的数据目录，在数据磁盘的索引区内存储的就是数据的目录，数据磁盘的数据区就是具体存放数据的区域。

索引是帮助MySQL高效获取数据的排好序的数据结构（存储结构）
索引的数据结构有很多种：

• 二叉树
• 红黑树
• Hash表
• B-Tree
• …

下面我们用较为简单的二叉树来体验一下索引是如何高效的查找mysql表数据的？
其中二叉树的节点结构为key和value两个属性，key为字段的数值，value为索引指向的磁盘地址文件指针
当前执行select * from temp where b = 22：

• 执行sql语句时，会去找当前的字段是否建立了索引文件，没有建立则全表扫描（结束）
• 根据当前字段根据二叉树的搜索方法（遍历）索引结构找到对应的key
• 根据key，则根据value的磁盘地址的文件指针去找到对应的数据文件

可以发现当我们适当的使用索引可以快速的提高我们的查询效率！！！

为什么使用B+树作为MySQL底层结构

在了解“为什么使用B+树作为MySQL底层数据结构”的前提下，我们需要学习一下关于树的数据结构的小前置知识点（这样可以帮我们更好的理解数据结构之间优势和劣势对比）：

二叉搜索树

平衡二叉搜索树（AVL树）

B树

红黑树

以上这些树结构，都有一个特点就是

• 右节点元素值 > 其父亲节点元素值
• 左节点元素值 < 其父亲节点元素值

插入：

• 从根节点开始，根据节点进行遍历，根据节点进行对比，
• 当 插入节点 > 当前节点的时候，则将 插入节点与当前节点的右节点对比
• 当 插入节点 < 当前节点的时候，则将 插入节点与当前节点的左节点对比
• 一直循环此步骤对比，直到没有节点进行对比，则插入完成

查询：

• 从根节点开始，根据节点进行遍历，根据节点进行对比，
• 当 查询节点 > 当前节点的时候，则将 查询节点与当前节点的右节点对比
• 当 查询节点 < 当前节点的时候，则将 查询节点与当前节点的左节点对比
• 一直循环此步骤对比，直到出现 查询节点 = 当前节点，则停止或者已经没有节点可以进行对比即结束

使用二叉搜索树（缺点：单边增长）

根据主键索引（id自增）字段插入，使用二叉树会变成线性链表，跟数据库的表的遍历一样没区别，某些特定场景（单边增长）效率太低

使用平衡二叉搜索树（AVL树）（缺点：旋转次数多，维护平衡成本大）

• 使用平衡二叉搜索树，通过节点之间的旋转（每个节点的左右子树高度差不超过1）解决了二叉搜索树的单边增长问题。
• 但是为了维护平衡，频繁的插入和删除时需要多次旋转，旋转是非常耗时的，并且插入新节点时旋转的次数不能确定。
• 因此适合用于插入与删除次数比较少，但查找多的情况。

使用红黑树（对比平衡叉树，减少旋转次数，性能更优）

• 使用红黑树（是平衡二叉搜索树的增强版）可以解决二叉树的单边增长问题（例如：Jdk1.8对hahsmap的链表优化为红黑树，不会导致单边增长过分）

• 通过旋转，红黑树追求大致平衡（通过红黑节点的一些规则约束）。在和平衡二叉搜索树查找时间复杂度相差不大的情况下，保证每次插入最多只用三次旋转就能达到平衡。

• 红黑树牺牲了部分平衡性以换取插入/删除操作时的少量的旋转操作，整体来说红黑树要优于平衡二叉搜索树

• 如果索引底层使用红黑树，红黑树的最大高度是2 * log2（n+1）

  当一百万表数据的时候，红黑树的最大高度为40左右，也就是当我们要查找的元素在叶子节点，就会达到最大的查询次数，这样查询的次数是特别久。 
  

• 如何让使用查找次数到3-5，使高度为3-5？

使用B-Tree（B树一个节点存储更多元素）

在B-Tree中，一个节点可以存储多个索引元素，想对比红黑树一个节点存储一个元素而言，树的高度更矮，可以减少查询的次数。

使用B+Tree（B-Tree的加强版，存储元素更多）

对比B-Tree而言，B+Tree则是它的加强版本，其非叶子节点不存储data，只存储冗余的索引，这样子一个节点就可以放更多的索引。

• 叶子节点：没有孩子节点的节点叫作叶子节点
• 非叶子节点：跟叶子节点相反，有孩子节点的节点

叶子节点包含了所有索引字段和元素值，上面一层存放中间叶子节点的元素（作为冗余）
B+Tree多了指针叶子节点用指针链接，提高了区间访问的性能。

为什么要冗余？

• 为了非叶子节点只存储索引，叶子节点需要存储全部的元素值，那么就必须冗余。
• 同等大小的节点如果只存储索引的话，不存储元素，可以存储更多的索引。

那么到这里有同学要问了，为什么不把所有节点索引以及数据全部存储到一个节点，这样不是更快吗？

1. 在MySQL中一个节点的大小是有限制的
2. 当我们加载节点的时候，会先把节点内容全部加载到内存中去，然后在内存中进行对比要查找的值，如果一次性把全部节点放在一行，数据扫描加载需要很多的内存和时间。

下面我们来分析一下：

# 查询innodb引擎一页的大小（一页就是一个节点）
SHOW GLOBAL STATUS LIKE 'Innodb_page_size';

# 查询结果  1KB=1024Byte
16384Byte = 16KB

我们对B+树（多叉平衡树）做以下假设：

• 索引元素占用bigint，8Byte
• 空白地方指向下一个大节点的磁盘文件的地址指针，占6Byte
• data假设占用为1KB

非叶子节点存储一个索引（空白指针 + 索引）需要14Byte(8Byte + 6Byte)
节点默认大小给了16KB，16384Byte / 14Byte = 1170.2857，也就是一个非叶子节点可以存储1170个索引
叶子节点没有空白节点，data数据和索引元素假设为1KB ，16KB / 1KB = 16 这棵树可以存储16个索引元素** **

按照这样计算的话，我们按照树的高度为3来计算，那么一颗B+Tree可以存储1170 * _1170 * _16 = 21902400 = 2100w差不多。这样使用B+Tree来存储，当树高度为3的时候，就可以存储几千万行的索引元素啦！！！
因此当我们有千万级别的表的时候，如果不建立索引可能要几十秒，建立所以可能就是几百毫秒。

数据如何加载？
因为数据库的索引是存储在磁盘上的，需要将该节点进行加载到内存，在进行遍历检索比较。这样就称之为一次的磁盘加载，也就做磁盘I/O。当我们的磁盘I/O的次数越多，那么所需要消耗的时间也就越大。

关于myisam和innodb引擎中实现B+树的存储原理

聚集索引：指的是数据和索引文件存放在一起存储
非聚集索引：指的是数据和索引文件分开存储

MyISAM存储引擎索引实现

• 数据库文件的存放（表结构，数据文件，索引文件）
• 存放在数据库文件夹的data目录下的数据库目录
• 存储引擎为MyISAM，frm为表结构，MYD数据文件，MYI索引文件

MyISAM引擎中的索引文件和数据文件是分离的(非聚集索引)
流程（当我们发起一条select *from temp where id = 49）：

1. 先根据字段id判断是否是索引元素（也就是是否建立了索引）
2. 建立了索引则根据值49去根节点去查找，根节点是常驻内存的，一直往下找
3. 进行比对，找到key=49索引元素，对应的data存放的是索引所在的那一行的磁盘文件地址的指针
4. 拿到指针后，去到myd文件，快速定位到这一行

以下为使用主键建立索引：

以下为使用非主键列建立索引：

InnoDB存储引擎索引实现

• 数据库文件的存放（表结构，数据文件，索引文件）
• 存放在数据库文件夹的data目录下的数据库目录
• 存储引擎为innodb，frm表结构，ibd文件包括了数据文件和索引文件（表数据文件本身就是按B+Tree组织的一个索引结构文件）

InnoDB引擎中的主键索引：索引文件和数据文件是在一起的(属于聚集索引)
InnoDB引擎中的二级索引：索引文件和数据文件不是在一起的(属于非聚集索引，叶子节点存储的是主键值)
流程（当我们发起一条select *from temp where id = 49）：

1. 先根据字段id判断是否是索引元素（也就是是否建立了索引）
2. 建立了索引则根据值49去根节点去查找，根节点是常驻内存的，一直往下找
3. 进行比对，找到key=49索引元素，其对应的存储了该行数据的所有内容

以下为使用主键建立索引（也称之为主键索引）：

以下为使用非主键列建立索引（也称之为二级索引）：
流程1（当我们发起一条select * from temp where name = Alice）：

1. 先根据字段name判断是否是索引元素（也就是是否建立了索引）
2. 建立了索引则根据值Alice去根节点去查找，根节点是常驻内存的，一直往下找
3. 进行比对，找到key=Alice索引元素，其对应的存储了该行数据的主键id
4. 根据主键id，到主键索引去查询，则可以获取到该条数据的全部信息

通过二级索引的主键值，去主键索引查询对应的数据，这个操作叫做回表！！！

流程2（当我们发起一条select id，name from temp where name = Alice）：

1. 先根据字段name判断是否是索引元素（也就是是否建立了索引）
2. 建立了索引则根据值Alice去根节点去查找，根节点是常驻内存的，一直往下找
3. 进行比对，找到key=Alice索引元素，其对应的存储了该行数据的主键id

需要查询的列信息，在二级索引里能找到，则不需要去主键索引查询回表，这个操作叫做索引覆盖！！！

提问1: 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

• innodb优先使用用户自定义主键作为主键
• 没有建立主键，则选取一个唯一列作为主键
• 没有唯一列，会为表默认添加一个名为 row_id 的隐藏列作为主键。

不用整形主键，使用uuid字符串（随机流水号）

• B+树进行比较，整形进行比较大小的时候运算更快更高效
• uuid字符串，字符串比大小，还要转成asii码，进行比较
• uuid字符串的占用空间大，磁盘空间使用更大，整形小
• uuid字符串插入（不能保证数据有序性），如果算出来的值是比较小的话，那么将往中间插入，且在一个满了一个节点16kb的情况下，这样就需要进行分裂，平衡，向上合并，导致高度变高，并且插入的时候也是需要去维护索引的。

提问2: 为什么非主键索引结构（二级索引）叶子节点存储的是主键值？(一致性和节省存储空间)
如果我们不使用主键值来组织整张表的数据（主键索引），使用其他非主键列来组织整张表的数据

• 当我们一颗树有多个索引的时候，需要多个非主键列来维护
• 其次我们在插入二级索引，需要维护两颗B+树的数据
• 在然后我们必须非主键索引维护组织的表数据和二级索引都插入成功，才算成功
• 如果主键来组织整张表的数据，我们可以让主键索引插入成功，在建立二级索引设置值

行格式

我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。InnoDB 存储引擎设计了 4 种不同类型的行格式，分别是 Compact、Redundant、Dynamic 和 Compressed 行格式。而在innodb默认的行格式为Dynamic（当然我们可以在创建或修改表的语句中指定行格式：CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称）。

数据溢出

关于数据溢出：
指的是 存储的列的数据所占字节大小 > 该列数据类型所能存储的最大字节，导致一页存放不了这条记录

在 Compact 和 Redundant 行格式中：

• 对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的该列的前 768 个字节的数据。
• 然后把剩余的数据分散存储在几个其他的页中，记录的真实数据处用 20 个字节存储指向这些页的地址。这个过程也叫做行溢出。
• 存储超出 768 字节的那些页面也被称为溢出页。

Dynamic 和 Compressed 行格式：

• 不会在记录的真实数据处存储字段真实数据的前 768 个字节，而是把所有的字节都存储到其他页面中。
• 只在记录的真实数据处存储其他页面的地址。

为什么长度少于等于768字节，则溢出页并不会被使用：

• 这个768是经过测试的最佳字节大小，使用溢出页会增加磁盘的I/O次数。
• 因为数据溢出，当前页的行记录某列数据存储指针指向别的页进行了存储，这样会导致多些磁盘I/O。

COMPACT行格式

行格式的数据结构以及描述如下：

变长字段长度列表（记录可变的数据类型的真实长度）：

• 在msql中有些数据类型需要存储多少的字节的数据是不确定的（例如varchar，txt等）。
• 因为这些数据类型的内存空间是可以变化的，我们需要在存储真实数据的时候把其存储的实际数据的占用的字节存起来。

NULL值列表（使用二进制的方式管理null值，节省内存空间）：

• 表中的某些列可能存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中存储会很占地方。
• 所以 Compact 行格式把这些值为 NULL 的列统一管理起来，存储到 NULL 值列表。
• 每个允许存储 NULL 的列对应一个二进制位，二进制位的值为1时，代表该列的值为NULL。二进制位的值为0时，代表该列的值不为NULL。

Redundant 行格式

Redundant 行格式是 MySQL5.0 之前用的一种行格式。

Dynamic 和 Compressed 行格式

Dynamic 和 Compressed 行格式和Compact 行格式挺像，只不过在处理行溢出数据时有所不同。Compressed 行格式和 Dynamic 不同的一点是，Compressed 行格式会采用压缩算法对页面进行压缩，以节省空间。

索引的内部架构（页）

InnoDB 是一个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启，我们的数据还是存在的。而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中 的内容刷新到磁盘上。而我们知道读写磁盘的速度非常慢，和内存读写差了几个 数量级，所以当我们想从表中获取某些记录时，InnoDB 存储引擎需要一条一条的把记录从磁盘上读出来么？

我们知道数据库的表记录是按照行来存储的，假设数据库的读取是按照行来读取的（读取一次为一次磁盘I/O），这样效率非常低，所耗时间非常长。因为MySQL是高性能的，不可能这样方式考虑去读取数据的。
因此InnoDB 引擎采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB 中页的大小一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取 16KB 的内容到内存中，一次最少把内存中的 16KB 内容刷新到磁盘中。

索引页的结构（B+树 -> 索引页）

索引页的构成如下图所示：

我们自己存储的记录会按照我们指定的行格式存储到 User Records 部分

当前记录数据插入：

• 在一开始生成页的时候，其实并没有User Records 这个部分。
• 当插入一条记录，会从 Free Space 部分（尚未使用的存储空间）中申请一个记录大小的空间划分到 User Records 部分。
• 当 Free Space 部分的空间全部被 User Records部分替代掉之后，如果还有新的记录插入的话，就需要去申请新的页了。

当前记录被删除：

• 会修改记录头信息中的 delete_mask 为 1（打一个删除标记），也就是说被删除的记录还在页中，还在真实的磁盘上。因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗以及分配空间也需要消耗时间。
• 所有被删除掉的记录都会组成一个垃圾链表，在这个链表中的记录占用的空间称之为所谓的可重用空间。
• 之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉。

页之间的关联（索引页 -> 索引页）

我们知道一个索引页存储多个记录，那么多个行记录如何进行关联的呢？
索引页之间通过上一个页、下一个页建立一个双向链表把许许多多的页就串联起来， 而无需这些页在物理上真正连续。

索引页的数据关联（索引页 -> 行格式记录）

一个主键索引页内部结构详细如下：

前缀概念：
分组：
索引页构成的部分中的User Records（用户记录->包含了多条记录）进行分组，进行分组能够更好的管理我们的记录。每个分组的最后一条记录的头信息中的 n_owned 字段会存储该组一共有多少条记录。
槽：
在索引页构成部分中的页面目录里面包含了多个槽

• 第一个槽的地址偏移量指向索引页构成部分中的的infimum（最小记录，虚拟行记录），且限制只能1条记录
• 中间的槽的地址偏移量指向了**当前分组中的最大的主键的行记录，**限制只能有4-8记录
• 最后一个槽的地址偏移量指向了索引页构成部分中的的Supremum（最大记录，虚拟行记录），限制只能有1-8条记录

之所以对不同的位置的槽进行了条数限制，是因为槽中的记录是单链表的形式，检索的时候需要通过指针去一条条遍历，如果一个槽的条数太多，那么会导致时间消耗太长。所以限制了位置在中间的槽只能4-8，当槽中的数量为4-8条的时候，链表的查询遍历性能是最好的（想对比其他的数据结构的查询插入删除的成本维护而言）
（参考hashmap中的当链表长度大于8，数组长度大于64，需要转换成红黑树）。

一个数据页中查找指定主键值的记录的过程分为两步：

• 通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最大的那条记录。
• 通过记录的 next_record 属性遍历该槽所在的组中的各个记录。

下面我们以B+树的角度来看看索引：

🎣

提问1: 为什么选择B+树作为索引？

因此我们的数据是存储在磁盘中的，每次进行数据的检索的时候，需要将数据加载到内存，然后进行处理检索数据，这样的操作为一次磁盘I/O，磁盘I/O是非常耗时的。因此为了检索的速度，需要最大限度的减少磁盘I/O，减少磁盘的数据加载到内存中。
由于一个数据库的节点页的可存储的大小有限为16KB（可以自行调整），在有限的大小存储下，B+树非叶子节点只存储索引元素，叶子节点存储数据，利用冗余索引的方式的来实现。这样可以使一个节点页存储最大限度的的索引，最大限度的减少磁盘I/O。

提问2: myisam和innodb引擎在实现B+树的存储的区别是什么？

• myisam属于非聚集索引，索引文件和数据文件分开

（myisam索引文件和数据文件分开存储，查找两次 ，myi索引文件 -> myd数据文件）

• innodb的主键索引属于聚集索引，索引文件和数据文件在一起

  （叶子节点包含了完整的数据记录，查找一次 ，ibd数据索引文件 ，效率更高）

• innodb的二级索引属于非聚集索引，索引文件和数据文件分开

  （如果查询的数据都在二级索引则出现索引覆盖，否则需要进行回表的操作）

提问3: 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

• 当我们没有建立主键，则会根据我们的唯一列去建立主键索引。

  如果都不满足，则会默认帮我们添加一个隐藏列作为主键。在实际开发中，我们应该自己去建立主键，这样就不需要mysql帮我们去添加隐藏列。
  

• 其次使用整形进行比较大小更快更高效，如果使用uuid字符串比较大小，还需要进行转码在比较，且字符串占用的空间更大，字符串是无序性的，容易导致在已经满的页进行插入，导致的树的分裂，平衡，向上合并，导致高度变高，需要时间成本去进行一个树的平衡维护。

提问4: innodb索引页的内部的行记录数据是如何组织的以及页与页之间如何查找的？

innodb索引页的内部的行记录数据是如何组织的：

1. innodb默认的行格式为Dynamic，每一条行记录的格式包含了记录头的信息，记录头信息包含了n_ownd(当前拥有的记录数)，next_record（指向下一跳记录的相对位置）。
2. 在一个索引页中包含了Page Directort（页面目录），User Records（用户记录）， Infimum+Supremum（最小记录和最大记录）
3. 在行记录中，会对行记录进行一个分组，按照主键的大小进行分组。根据页目录的每个槽位，进行标记记录最小最大记录，以及当前记录组的最大记录一一对应。
4. 通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最大的那条记录。
5. 通过记录的 next_record 属性遍历该槽所在的组中的各个记录。

页与页之间如何查找的：

1. 索引页的构成包含了File Header（文件头头部）记录了上下索引的前后指针。
2. 索引页之间通过上一个页、下一个页建立一个双向链表把许许多多的页就串联起来。
3. 不需需这些页在物理上真正连续，只是在逻辑上的连续。

关于此次的索引讲解到此结束啦，下集我们来讲讲如何优化索引。