2020-08-05

MySQL中有很多存储引擎，本文中如不加解释默认为Innodb引擎。

MySQL的索引存储结构

MySQL的数据存储文件

       在MyISAM引擎中用.frm、.MYI和.MYD文件来存储数据表，其中.frm存储了表的结构，.MYI存储了索引的，而.MYD存储了表的数据，在Innodb中只有.frm和.idb文件，.frm同MyISAM一样存储了表的结构，.idb存储了索引和数据，也就是MyISAM中.MYI和.MYD文件的合体。

MySQL中的5大索引简介

• 主键索引：（primary key）主键是一种特殊的索引，要求唯一且不为空，一张表只能有一个主键，有约束性。
• 唯一索引：字段必须唯一但可以为空，一张表可以有多个唯一索引，没有约束性。
• 组合索引：(多个字段组合成的一个索引，有使用生效原则，后面会讲到。
• 普通索引：没有任何限制。
• 全文索引：只能为CHAR、VARCHAR、TEXT列创建。

MySQL中的主键定义，如果不显示地定义主键，Innodb按照如下方式定义主键：

1.Innodb会在表中选择唯一且不为空（unique not null）的字段作为主键
2.如果没有上述字段，那Innodb会隐式的定义一个6个字节的int型作为主键

MySQL索引结构

       说到索引结构就会谈到磁盘的I/O和二叉树（Binary tree）、红黑树（Red Black Tree）、B-tree、B+tree。

       磁盘I/O：数据的读写与磁盘I/O密切相关（索引结构是为了减少I/O次数，因为磁盘I/O很耗时），影响磁盘I/O时间的主要是寻道时间、旋转延时和传输时间，数据传输是很快的，所以传输时间可以忽略不记。

• 寻道时间：磁头移动到数据所在的对应磁道上的时间。

• 旋转延时：当磁头找到对应磁道后不能马上读取数据，磁头要等到磁盘盘片旋转到初始数据块所在的扇区落在磁头的正上方之后，才能开始读取数据。

二叉树和红黑数：这两种结构在一些特殊情况下都会造成树很高，这样对与磁盘的I/O是灾难性的。

二叉树图

红黑树图

       上面是二叉树和红黑树存储一些数据后的树形，从上图可以看出树的高度随着数据的增加快速的增高，尤其二叉树在遍历的时候直接就变成了链表的遍历，当数据量很大的时候系统性能可想而知。

       B-tree：B-Tree是为磁盘等外存储设备设计的一种平衡查找树（当前树设置可以有三个叶子节点），图中值可看作索引，data为对应索引下的一行完整数据。

B-tree图

       B+tree：B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，只有叶子节点可以存储数据（当前树设置可以有三个叶子节点）图中值可看作索引，data为对应索引下的一行完整数据。

B+tree图

Innodb为什么选择B+tree而不选择B-tree？

       由B-tree和B+tree图来看你可能觉得应该选择B-tree来存储数据要好点，毕竟只要读到相应索引就可以拿到对应的一行完整数据了，但是上面的树的叶节点个数我是设置了最大个数的（只能存三个数据节点），在实际的存储数据中MySQL会根据分配的页大小进行叶节点的个数分配。

SHOW VARIABLES LIKE 'innodb_page_size';  //查看MySQL一页的大小，这一页大小确定了叶节点的上限

MySQL页大小

由上图我们可以看出MySQL分配的页大小为16kb（可以自己设置为4kb、8kb），既然页的大小设置了上限，那么一页能存放的节点数就相应确定了。

1. 不使用B-tree的原因：

   • 在B-tree中每一个索引节点下都有对应的一行完整数据，大家都知道索引其实没有占到多大空间，但是data却占了极大的空间，一行完整的数据大约占1kb的大小，也就是说一页只能存16个节点，这随之造成的是树变高了（树变高造成磁盘I/O增多）。
   • 当使用范围查询时候磁盘I/O次数比使用B+tree多。由B-tree图看，如果查询小于007的所有数据且当前磁头在003的那一页上，这样的话磁头就得来回移动，当数据量大和使用范围查询多的情况下系统性能会急剧下降。

2. 使用B+tree的原因：

   • 由B+tree图可以看出，只有最后的页节点上有完整的数据，这就意味着一页可以存放的索引节点大大增加，比如使用int型字段作为主键索引，那么一页就可以存放16*1024/4=4096（一页总字节数/索引字节数）个索引节点。一页能存放的节点数增多了就意味着树的高度变矮了，磁盘I/O次数也跟着减少了）。
   • 使用范围查询的时候不用过多地进行磁盘的I/O，因为所有索引和数据在叶子节点都有且是有序的，只要到指定的节点上就可以通过指针（B+tree图中的箭头）进行顺序遍历所有节点就行了。