MySQL 数据库索引数据结构的选择

1. MySQL 数据库索引的数据结构选择

1.1 Hash索引

哈希索引就是采用一定的哈希算法，将键值通过hash(key)算法转化为数组的桶下标（比如John Smith转换为152，LisaSmith转化为001等），查找时不需要像B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

从上面的图来看，B+树索引和哈希索引的明显区别是：

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；

不适合做范围查询，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了（比如原来John Smith和Lisa Smith原来是在一起的，但是经过hash算法后就变成了001 和152）

无法利用索引完成排序和分组

哈希索引也不支持多列联合索引的最左匹配规则；

在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

InnoDB索引是存放在硬盘上的，如果使用hash存储的话需要将所有的数据文件加载至内存，比较耗费内存空间。但是Memory存储引擎的索引是存在内存中的，因此可以使用Hash索引，而且默认使用的也是hash索引。

1.2 二叉搜索树 & 平衡二叉树

我们为user 表（用户信息表）建立了一个二叉查找树的索引。图中的圆为二叉查找树的节点，节点中存储了键（key）和数据（data）。键对应 user 表中的id（主键），数据对应 user表中的行数据。（数据库表中一行的记录，这里只有id、name字段，如果还有age、weight等字段也在data中）

查找 id=12 的用户信息，只需要 3 次即可找到匹配的数据。如果在表中一条条的查找的话，我们需要 6 次才能找到。二叉树可以提高我们的查询效率，但是如果上面的二叉查找树是这样的构造：

二叉树退化成了链表，相当于全表扫描。 原因其实是二叉查找树变得不平衡了。平衡二叉树：在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过 1

为了保证这棵树是一颗平衡树，在进行数据插入的时候，需要进行N多次的左旋和右旋操作，因此插入的性能比较低，但是查询的时候性能比较高，损失了插入性能满足了查询性能；

红黑树和AVL树类似，都是在进行插入和删除操作时通过特定操作保持二叉查找树的平衡，从而获得较高的查找性能。但红黑树不追求绝对平衡，而是相对平衡，保证每次插入最多只需要三次旋转就能达到平衡；

索引和数据都是存放在磁盘上的，索引不但能帮我们提升查询效率还能够减少IO次数，索引的数据结构有多种，选择哪种数据结构还要使用磁盘I/O次数评价索引结构的优劣，如何理解磁盘IO次数？

磁盘和内存之间是通过页进行数据交换的：

为了尽量减少I/O操作，计算机系统一般采取磁盘预读的方式，预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页的大小通常为4k），主存和磁盘以页为单位交换数据。

计算机系统是分页读取和存储的，一般一页为4KB，每次读取和存取的最小单元为一页，而磁盘预读时通常会读取页的整倍数。根据【局部性原理】①当一个数据被用到时，其附近的数据也通常会马上被使用。②程序运行期间所需要的数据通常比较集中。由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），所以即使只需要读取一个字节，磁盘也会读取一页的数据。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数 。

不管用什么二叉树，最终都会导致树的深度越来越深，树的深度越深IO次数就会增加，读取效率就会降低，为什么？

因为内存的易失性，一般情况下，我们都会选择将表中的数据和索引存储在磁盘这种外围设备中。但是和内存相比，从磁盘中读取数据的速度会慢很多，所以，我们应当尽量减少从磁盘中读取数据的次数。

另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。

如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块 ，平衡二叉树可是每个节点只存储一个键值和数据的。那说明什么？说明每个磁盘块仅仅存储一个键值和数据！那如果我们要存储海量的数据呢？

可以想象到二叉树的节点将会非常多，高度也会极其高，我们查找数据时也会进行很多次磁盘 IO，我们查找数据的效率将会极低！

如果使用二叉树这种数据结构，每一个磁盘块只会存储一个键值和数据，并不能填满一页上的所有内容，那多余的内容岂不是要浪费了？我们怎么才能把浪费的这部分内容利用起来呢？为了解决平衡二叉树的这个弊端，我们应该寻找一种单个节点可以存储多个键值和数据的平衡树。也就是B 树。

数据库系统巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页（16K）

1.3 B 树（多路平衡搜索树）

磁盘块存储的数据： ① 键值（主键id）② 数据（数据库表整行数据）③ 指向子节点的指针

图中的每个节点称为页，页就是我们上面说的磁盘块，在 MySQL 中数据读取的基本单位都是页，所以我们这里叫做页更符合 MySQL 中索引的底层数据结构。

从上图可以看出，B 树相对于平衡二叉树，每个节点存储了更多的键值（key）和数据（data），并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的 B 树为 3 阶 B 树，高度也会j降低。因此B 树查找数据读取磁盘的次数将会减少，数据的查找效率也会比平衡二叉树高很多。

假如我们要查找 id=28 的用户信息，那么我们在上图 B 树中查找的流程如下：

1. 先找到根节点页 1，判断 28 在键值 17 和 35 之间，根据页 1 中的指针 p2 找到页 3。（1次IO）

2. 将 28 和页 3 中的键值相比较，28 在 26 和 30 之间，根据页 3 中的指针 p2 找到页 8。（1次IO）

3. 将 28 和页 8 中的键值相比较，发现匹配的键值 28，键值 28 对应的用户信息为（28，bv）。（1次IO）

1.4 B + 树

根据上图我们来看下 B+ 树和 B 树有什么不同：

① B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据。

之所以这么做是因为在数据库中页的大小是固定的，InnoDB 中页的默认大小是 16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数又会再次减少，数据查询的效率也会更快。

另外，如果我们的 B+ 树一个节点可以存储 1000 个键值，那么 3 层 B+ 树可以存储 1000×1000×1000=10 亿个数据。一般根节点是常驻内存的(第一次检索根节点不用读取磁盘)，所以一般我们查找 10 亿数据，只需要 2 次磁盘 IO。

② B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。

**B+ 树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。**而 B 树因为数据分散在各个节点，要实现这一点是很不容易的。 B+ 树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。

通过上图可以看到，在 InnoDB 中，数据页之间通过双向链表连接，叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

MyISAM 中的 B+ 树索引实现与 InnoDB 中的略有不同。在 MyISAM 中，B+ 树索引的叶子节点并不存储数据，而是存储数据的文件地址。

2. 聚簇索引和非聚簇索引

在 MySQL 中，B+ 树索引按照存储方式的不同分为聚集索引和非聚集索引。

**① 聚集索引（聚簇索引）：**以主键作为键值而构建的 B+ 树索引， 称之为聚集索引。

② 非聚集索引（非聚簇索引）：以主键以外的列值作为键值构建的 B+ 树索引， 称之为非聚集索引。

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

2.1 利用聚集索引查找数据

现在假设我们要查找 id>=18 并且 id<40 的用户数据。对应的 sql 语句为：

select * from user where id>=18 and id <40

其中 id 为主键，具体的查找过程如下：

① 一般根节点都是常驻内存的，也就是说页 1 已经在内存中了，此时不需要到磁盘中读取数据，直接从内存中读取即可。

从内存中读取到页 1，要查找这个 id>=18 and id <40 或者范围值，我们首先需要找到 id=18 的键值。

从页 1 中我们可以找到键值 18，此时我们需要根据指针 p2，定位到页 3。

② 要从页 3 中查找数据，我们就需要拿着 p2 指针去磁盘中进行读取页 3。

从磁盘中读取页 3 后将页 3 放入内存中，然后进行查找，我们可以找到键值 18，然后再拿到页 3 中的指针 p1，定位到页 8。

③ 同样的页 8 页不在内存中，我们需要再去磁盘中将页 8 读取到内存中。

将页 8 读取到内存中后。因为页中的数据是链表进行连接的，而且键值是按照顺序存放的，此时可以根据二分查找法定位到键值 18。

此时因为已经到数据页了，此时我们已经找到一条满足条件的数据了，就是键值 18 对应的数据。

因为是范围查找，而且此时所有的数据又都存在叶子节点，并且是有序排列的，那么我们就可以对页 8 中的键值依次进行遍历查找并匹配满足条件的数据。

我们可以一直找到键值为 22 的数据，然后页 8 中就没有数据了，此时我们需要拿着页 8 中的 p 指针去读取页 9 中的数据。

④ 因为页 9 不在内存中，就又会加载页 9 到内存中，并通过和页 8 中一样的方式进行数据的查找，直到将页 12 加载到内存中，发现 41 大于 40，此时不满足条件。那么查找到此终止。

最终我们找到满足条件的所有数据，总共 12 条记录：

(18,kl), (19,kl), (22,hj), (24,io), (25,vg) , (29,jk), (31,jk) , (33,rt) , (34,ty) , (35,yu) , (37,rt) , (39,rt) 。

下面看下具体的查找流程图：

2.2 利用非聚集索引查找数据

在叶子节点中，不再存储所有的数据了，存储的是键值和主键。对于叶子节点中的 x-y，比如 1-1。左边的 1 表示的是索引的键值，右边的 1 表示的是主键值。

比如下面的数据库表：

select * from user where age=33

id	name	age
1	zs	23
2	ls	7

查找的流程跟聚集索引一样，比如我们想要找到age=33的用户信息，需要找到键值33，进而可以知道键值 age=33时，存储的数据为47即主键为47，找到主键后我们需要再到聚集索引中查找具体对应的数据信息，此时又回到了聚集索引的查找流程。

在 MyISAM 中，聚集索引和非聚集索引的叶子节点都会存储数据的文件地址。

3. 主键索引和非主键索引

非主键索引的叶子节点存放的是主键的值，而主键索引的叶子节点存放的是整行数据，其中非主键索引也被称为二级索引，而主键索引也被称为聚簇索引。

如果查询语句是 select * from table where ID = 100,即主键查询的方式，则只需要搜索 ID 这棵 B+树。

如果查询语句是 select * from table where k = 1，即非主键的查询方式，则先搜索k索引树，得到ID=100,再到ID索引树搜索一次，这个过程也被称为回表。

因为在InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也就是说,主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序),如果主键索引是自增ID,那么只需要不断向后排列即可,如果是UUID,由于到来的ID与原来的大小不确定,会造成非常多的数据插入,数据移动,然后导致产生很多的内存碎片,进而造成插入性能的下降。

Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

4. MySQl存储引擎

① InnoDB支持事务，MyISAM不支持

② InnoDB支持外键，而MyISAM不支持。

③ InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和 索引绑在一起的 ，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。

MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

④ InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

⑤ InnoDB表必须有主键（用户没有指定的话会自己找或生产一个主键），而Myisam可以没有