MySQL中的“索引”

最坏情况下（退化为单分支树）时间复杂度为 O(N)；虽然二叉搜索树的中序遍历是一个有序数组，但是节点个数过多的时候无法保证树高，即使是 AVL 或者红黑树也无法保证树高尽可能小；此外，在搜索数据时，每次访问某个节点的子节点时都会发生一次磁盘IO，而在整个数据库系统中，IO是性能的瓶颈，减少IO次数可以有效地提高性能。

所以二叉搜索树不适合作为索引的数据结构。

2.3 N叉搜索树（B树）

时间复杂度 O(log₂N)；

在相同数据量的情况下，N叉树的树高可以得到有效的控制，也就意味着在相同数据量的情况下可以减少 IO 的次数，从而提高效率。但是 MySQL 认为 N 叉树作为索引的数据结构还是不够好。

2.4 B+树

2.4.1 简介

B+树是一种经常用于数据库和文件系统等场合的平衡查找树。

2.4.2 B+树的特点

能够保持数据稳定有序，插入与修改有较稳定的时间复杂度 O(log₂N)；

非叶子节点仅具有索引作用，不存储数据，所有叶子节点则保留的是真实数据；

所有叶子节点构成一个有序双向链表，可以按照 key 排序的次序依次遍历全部数据。

2.4.3 B+树和B树的区别

1、B+树的叶子节点中的数据都是连续的，且相互链接，便于区间查找；

2、B+树的非叶子节点的值都包含在叶子节点中；

3、对于B+树而言，在相同树高的情况下，查找任意元素的时间复杂度都是一样的，性能均衡。

三、MySQL 中的页

3.1 概念&好处

在数据库文件夹下每创建一张表，innoDB 存储引擎就会生成一个 .ibd 为后缀的表空间文件，在 .ibd 文件中最重要的结构体就是 Page（页）。页是内存与磁盘交互的最小单元，默认大小为 16KB。每次内存与磁盘交互至少读取一页，所以在磁盘中每个页内部的地址都是连续的。

之所以每次交互至少读取一页，是因为在使用数据的过程中，根据局部性原理，将来要使用的数据大概率与当前访问的数据在空间上是临近的，所以一次从磁盘中读取一页的数据放入内存中，当下次查询的数据还在这个页中时，就可以从内存中直接读取，从而减少磁盘 IO，提高性能。

局部性原理：

是指程序在执行时呈现出局部性规律，在一段时间内，整个程序的执行仅限于程序中的某一部分。相应地，执行所访问的存储空间也局限于某个内存区域，局部性通常有两种形式：
时间局部性：如果一个信息项正在被访问，那么近期它很可能还会被再次访问；

空间局部性：将来要用到的信息大概率与正在使用的信息在空间地址上是临近的。

当从内存中往磁盘里写数据页的时候，写到一般操作系统出错，此时 MySQL 如何保证数据安全？

=> 在落盘（即写入磁盘）之前会记录各种日志，保证重启之后可以找到没有落盘前的数据内容。

每一个页中即使没有数据也会使用 16KB 的存储空间，同时与索引的B+树中的节点对应。查看页的大小，可以通过系统变量 innodb_page_size 进行查看：16384 bit / 1024 = 16KB

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set, 1 warning (0.09 sec)

3.2 基本结构

在 MySQL 中有多种不同类型的页，最常用的是用来存储数据和索引的“索引页”，也叫做“数据页”，但无论是哪种类型的页都会包含页头和页尾。

数据页的基本结构如下图：

3.3 页文件头和页文件尾

此处只关注上一页页号和下一页页号，通过这两个属性可以把页和页之间链接起来，形成一个双向链表。

3.4 页主体

页主体部分是保存真实数据的主要区域，每创建一个新页，都会自动分配两个行，一个是页内最小行，另一个是页内最大行。这两行并不存储任何真实信息。

第一个数据行有一个记录下一行的地址偏移量的区域 next_record 将页内所有数据行组成了一个单向链表，此时新页的结构如下所示：

当向一个新页插入数据时，将最小行连接第一个数据行，最后一行真实数据行连接最大行，这样数据行就改建成了一个单向链表，更多的行数据插入后，会按照主键从小到大的顺序进行链接，如下图所示：

3.5 页目录

InnoDB 采用二分查找来解决效率问题，具体实现方式是在每一页中加入一个叫 页目录 的结构，将页内包括最小行、最大行在内的所有行进行分组，约定最小行单独为一组，其他每个组最多有8条数据；（但并非严格必须每组8条记录，少于8条完全可以分为一组）

同时把每个组最后一行在页中的地址，按主键从小到大的顺序记录在页目录中，在页目录中的每一个位置成为一个槽，每个槽则对应一个分组；一旦分组中的数据行超过分组的上限8个的时候，就会分裂出一个新的分组。

        特点：
        最小行单独为一组；
        最大行永远在最后一个分组中；
        槽的数量与组数相同；
        槽会指定对应分组的最后一条记录，同时保存这条记录的主键值。

目的是实现高效的二分查找。

        例如要查找主键值6的数据行：
1、首先找到这条记录所在的页；
        2、再比对槽中记录的主键值，定位到最后一个槽2；
        3、最后从最后一个槽中的第一条记录开始遍历组内的数据，即可找到第二条记录。

3.6 数据页头

记录当前页保存数据相关的信息。如下图

四、B+树在 MySQL 中的应用

4.1 介绍

非叶子节点保存索引数据，叶子节点保存真实数据。

   · 以查找 id 为 5 的记录为例，完整的检索过程如下：
        1、首先判断B+树的根节点中的索引记录，此时 5 < 7，应访问左孩子节点，找到索引2；
        2、在索引2中判断 id 的大小，找到与5相等的记录，命中则加载对应的数据页得到具体的数据信息。

上面的IO过程为：加载索引页1 --> 加载索引页2 --> 加载数据页3；
因为所有页的操作和访问都是在内存中进行的，由上面的例子只用加载3次即可找到目标数据的高效性能证明：使用B+树能提高查找效率，减少内存的消耗。

4.2 计算三层树高的B+树可以存放多少条记录

1、在忽略数据页中数据页自身属性空间占用的情况下，设一条数据大小为 1 KB
==> 一个数据页可以存放 16 条数据；

2、索引页一条数据的大小：主键用 BIGINT 类型占 8 Byte，下一个地址 6 Byte，一共 14 Byte
==> 一个索引页可以保存 16 * 1024 / 14 = 1170 条索引记录；

3、如果只有三层树高的情况，综合只保留索引的根节点和二级节点的索引页以及保存真实数据的数据页，那么一共可以保存 1170 * 1170 * 16 = 21,902,400 条记录，也就是说在两千多万条数据的表中，可以通过三次IO就可以完成数据的检索。

（ 1170 * 1170 * 16 解读：第一个1170表示根节点这个索引页可以指向1170个第二层的索引页；第二个1170是指第二层的每一个索引页可以指向1170个第三层的叶子页；1170*1170 则表示这颗三层高的B+树总共可以有多少个第三层的数据页；16表示每个叶子节点能存放多少条数据行）

【现实一条记录要比1KB小很多，最后得到的总记录也会比我们估计的要大非常多，而三层B+树支撑千万级到亿级的数据量是完全可行的。】