文章目录
- MySQL为什么要使用B+树
- 磁盘 IO
- B 树
- B 树和 B+ 树区别
- MySQL的优化方案
- Page
- B+树
- B树和B+树
- 为什么使用B+树
- MyISAM 和 InnoDB 索引原理
- MyISAM 主键索引与辅助索引的结构
- InnoDB 主键索引与辅助索引的结构
- 主键索引
- 辅助（非主键）索引
- InnoDB 索引结构需要注意的点
MySQL为什么要使用B+树
索引可以有很多种结构类型，这样可以为不同的场景提供更好的性能
首先要明白索引（index）是在存储引擎（storage engine）层面实现的，而不是 server 层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型，它们的实现和行为也可能有所差别
介绍索引结构之前，我们先了解下磁盘IO与B树
磁盘 IO
磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分
- 寻道时间：磁臂移动到指定磁道所需要的时间，主流磁盘一般在 5ms 以下
- 旋转延迟：就是磁盘转速，比如一个磁盘 7200 转，表示每分钟能转 7200 次，也就是说 1 秒钟能转 120 次，旋转延迟就是
1/120/2 = 4.17ms - 传输时间：从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计
那么访问一次磁盘的时间，即一次磁盘 IO 的时间约等于 5+4.17 = 9ms 左右。但是一台 500-MIPS 的机器每秒可以执行 5 亿条指令，因为指令依靠的是电的性质，换句话说执行一次 IO 的时间可以执行 40 万条指令，数据库动辄十万百万乃至千万级数据，每次 9 毫秒的时间，显然是个灾难。
考虑到磁盘 IO 是非常高昂的操作，计算机操作系统做了一些优化，当一次 IO 时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次 IO 读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为 4k 或 8k，也就是我们读取一页内的数据时候，实际上才发生了一次 IO，这个理论对于索引的数据结构设计非常有帮助。
那是不应该有一种数据结构，可以在每次查找数据时把磁盘 IO 次数控制在一个很小的数量级， B+ 树就这样应运而生。
B 树
在 B 树中，内部（非叶子）节点可以拥有可变数量的子节点（数量范围预先定义好）。当数据被插入或从一个节点中移除，它的子节点数量发生变化。为了维持在预先设定的数量范围内，内部节点可能会被合并或者分离。因为子节点数量有一定的允许范围，所以B 树不需要像其他自平衡查找树那样频繁地重新保持平衡，但是由于节点没有被完全填充，可能浪费了一些空间。子节点数量的上界和下界依特定的实现而设置。例如，在一个 2-3 B树（通常简称2-3树），每一个内部节点只能有 2 或 3 个子节点。
B 树中每一个内部节点会包含一定数量的键，键将节点的子树分开。例如，如果一个内部节点有 3 个子节点（子树），那么它就必须有两个键： a1 和 a2 。左边子树的所有值都必须小于 a1 ，中间子树的所有值都必须在 a1 和 a2 之间，右边子树的所有值都必须大于 a2 。
在存取节点数据所耗时间远超过处理节点数据所耗时间的情况下，Ｂ树在可选的实现中拥有很多优势，因为存取节点的开销被分摊到里层节点的多次操作上。这通常出现在当节点存储在二级存储器如硬盘存储器上。通过最大化内部里层节点的子节点的数量，树的高度减小，存取节点的开销被缩减。另外，重新平衡树的动作也更少出现。子节点的最大数量取决于，每个子节点必需存储的信息量，和完整磁盘块的大小或者二次存储器中类似的容量。虽然 2-3 树更易于解释，实际运用中，Ｂ树使用二级存储器，需要大量数目的子节点来提升效率。
而 B+ 树 又是 B 树的变种，B+ 树结构，所有的数据都存放在叶子节点上，且把叶子节点通过指针连接到一起，形成了一条数据链表，以加快相邻数据的检索效率。
将 [11,13,15,16,20,23,25,30,23,27] 用 B 树 和 B+ 树存储，看下结构
B 树和 B+ 树区别
B-Tree 和 B+Tree 都是为磁盘等外存储设备设计的一种平衡查找树
关键词 | B-树 | B+树 | 备注 |
最大分支，最小分支 | 每个结点最多有m个分支（子树），最少⌈m/2⌉（中间结点）个分支或者2个分支（是根节点非叶子结点） | 同左 | m阶对应的就是就是最大分支 |
n个关键字与分支的关系 | 分支等于n+1 | 分支等于n | 无 |
关键字个数（B+树关键字个数要多） | 大于等于⌈m/2⌉-1小于等于m-1 | 大于等于⌈m/2⌉小于等于m | B+树关键字个数要多，+体现在的地方 |
叶子结点相同点 | 每个节点中的元素互不相等且按照从小到大排列；所有的叶子结点都位于同一层 | 同左 | 无 |
叶子结点不相同 | 不包含信息 | 叶子结点包含信息，指针指向记录。 | 无 |
叶子结点之间的关系 | 无 | B+树上有一个指针指向关键字最小的叶子结点，所有叶子节点之间链接成一个线性链表 | 无 |
非叶子结点 | 一个关键字对应一个记录的存储地址 | 只起到索引的作用 | 无 |
存储结构 | 相同 | 同左 | 无 |
MySQL的优化方案
由于有磁盘IO和B树的限制，
mysql针对这两个问题分别使用页（page）和B+树来优化。达到减少磁盘IO的目的，以InnoDB为例
Page
系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么
在InnoDB 设计页（page），页是磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为16KB
系统一个磁盘块的存储空间往往没有16KB，因此 InnoDB 每次申请磁盘空间时都是取多个连续磁盘块以满足16KB的页大小。也就是说，在查询数据时，如果一个页的每条数据都能有助于定位数据记录的位置，这将会减少磁盘 I/O 次数，提高查询效率
B+树
B树和B+树
以范围查找为例简单看下，B Tree 结构查询 [10-25] 的数据
- 加载根节点，第一个节点元素15，大于10【磁盘 I/O 操作第 1 次】
- 通过根节点的左子节点地址加载，找到 11，13【磁盘 I/O 操作第 2 次】
- 重新加载根节点，找到中间节点数据 16，20【磁盘 I/O 操作第 3 次】
- 再次加载根节点，23 小于 25，再加载右子节点，找到 25，结束【磁盘 I/O 操作第 4 次】
而 B+ 树对范围查找就简单了，数据都在最下边的叶子节点下，而且链起来了，我只需找到第一个然后遍历就行（暂且不考虑页分裂等其他问题）
为什么使用B+树
B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构。
用 B+ 树不用 B 树考虑的是 IO 对性能的影响，B 树的每个节点都存储数据，而 B+ 树只有叶子节点才存储数据，所以查找相同数据量的情况下，B 树的高度更高，IO 更频繁。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。其中在 MySQL 底层对 B+ 树进行进一步优化：在叶子节点中是双向链表，且在链表的头结点和尾节点也是循环指向的。
B-Tree 结构图每个节点中不仅要包含数据的 key 值，还有 data 值。而每一个页的存储空间是有限的，如果 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小，当存储的数据量很大时同样会导致 B-Tree 的深度较大，增大查询时的磁盘 I/O 次数，进而影响查询效率。在 B+Tree 中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的 key 值数量，降低 B+Tree 的高度。
IO 次数取决于 B+ 数的高度 h，假设当前数据表的数据为 N，每个磁盘块的数据项的数量是 m，则有
h=㏒(m+1)N，当数据量 N 一定的情况下，m 越大，h 越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如 int 占 4 字节，要比 bigint 8 字节少一半。这也是为什么 B+ 树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于 1 时将会退化成线性表。
MyISAM 和 InnoDB 索引原理
MyISAM 主键索引与辅助索引的结构
MyISAM 引擎的索引文件和数据文件是分离的。MyISAM 引擎索引结构的叶子节点的数据域，存放的并不是实际的数据记录，而是数据记录的地址。MyISAM 的主索引与辅助索引区别并不大，主键索引就是一个名为 PRIMARY 的唯一非空索引。
聚簇：表示数据行和相邻的键值紧凑的存储在一起
非聚簇索引：索引文件与数据文件分离
在 MyISAM 中，索引（含叶子节点）存放在单独的 .myi 文件中，叶子节点存放的是数据的物理地址偏移量（通过偏移量访问就是随机访问，速度很快）
主索引是指主键索引，键值不可能重复；辅助索引则是普通索引，键值可能重复
通过索引查找数据的流程：先从索引文件中查找到索引节点，从中拿到数据的文件指针，再到数据文件中通过文件指针定位了具体的数据
辅助索引类似
InnoDB 主键索引与辅助索引的结构
InnoDB 引擎索引结构的叶子节点的数据域，存放的就是实际的数据记录
- 对于主键索引，此处会存放表中所有的数据记录
- 对于辅助索引此处会引用主键，检索的时候通过主键到主键索引中找到对应数据行
InnoDB 的数据文件本身就是主键索引文件，这样的索引被称为“聚簇索引”，一个表只能有一个聚簇索引
主键索引
InnoDB 索引是聚集索引，它的索引和数据是存入同一个 .idb 文件中的，因此它的索引结构是在同一个树节点中同时存放索引和数据，如下图中最底层的叶子节点有三行数据，对应于数据表中的 id、name、score 数据项。
在 InnoDB 中，索引分叶子节点和非叶子节点，非叶子节点就像新华字典的目录，单独存放在索引段中，叶子节点则是顺序排列的，在数据段中
辅助（非主键）索引
以 name 列建立辅助索引，它的索引结构如图，在最底层的叶子结点有两行数据，第一行的字符串是辅助索引，按照 ASCII 码进行排序，第二行的整数是主键的值。
这就意味着，对 name 列进行条件搜索，需要两个步骤：
- 在辅助索引上检索 name，到达其叶子节点获取对应的主键；
- 使用主键在主索引上再进行对应的检索操作
这也就是所谓的“回表查询”
InnoDB 索引结构需要注意的点
- 数据文件本身就是索引文件
- 表数据文件本身就是按 B+Tree 组织的一个索引结构文件
- 聚集索引中叶节点包含了完整的数据记录
- InnoDB 表必须要有主键，并且推荐使用整型自增主键
正如上面介绍 InnoDB 存储结构，索引与数据是共同存储的，不管是主键索引还是辅助索引，在查找时都是通过先查找到索引节点才能拿到相对应的数据，
- 如果在设计表结构时没有显式指定索引列的话，MySQL 会从表中选择数据不重复的列建立索引
- 如果没有符合的列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，并且这个字段长度为 6 个字节，类型为整型。
