8.面试宝典-数据库索引数据结构

携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第6天，点击查看活动详情

书接前两回,继续分析数据库的索引结构

6.面试宝典-数据库事务概述 - 掘金 (juejin.cn)

7.面试宝典-数据库索引概述 - 掘金 (juejin.cn)

参考文档：

MySql之索引

红黑树详解

mysql回表查询

上回说到： 从存储结构上来划分：B-Tree，B+Tree，Hash索引，下面讲讲数据库索引的数据结构

索引概述

MyISAM索引文件

InnoDB索引文件

B tree

B+ tree

Hash索引

哈希索引是一种基于哈希表的索引结构，它是一种需要精确匹配才生效的索引结构。

实现原理：对索引列计算哈希值把记录映射到哈希槽中，然后指向对应记录行的地址。因此，在查询的时候只要正确匹配到索引列，就能在O(1)的时间复杂度内查到记录。

以下是一个哈希索引的示例，左边是哈希槽，右边是对应的数据列：

相比于B-Tree索引而言，哈希索引有不少的局限性：

• 哈希索引不支持排序
• 哈希索引不支持部分列索引查找
• 哈希索引只支持等值查询，无法提供范围查询功能

哈希索引的查找效率是非常高的，大多数时候都能在O(1)的时间内找到记录，除非哈希冲突很高。

二叉树

二叉树的特点：

1.一个节点只能有两个子节点，也就是一个节点度不能超过2

2.左子节点小于本节点，右子节点大于等于本节点

在二叉树结构，计算比较 3 次就可以检索到 id=7 的数据，相对于直接遍历查询省了一半的时间，从检索效率上能做到高速检索的。此外二叉树的结构还能提供的范围查找功能，上图二叉树的叶子节点都是按序排列的，从左到右依次升序排列，如果我们需要找 id>5 的数据，那我们取出节点为 6 的节点以及其右子树就可以了，范围查找也是比较容易实现。

缺点: 主键自增情况下会退化为线性链表，二分查找也会退化为遍历查找（全盘扫描），检索性能急剧下降。

二叉查找树存在不平衡问题，让二叉树始终保持基本平衡的状态，就能保持二叉查找树的最佳查找性能了。 基于这种思路的自调整平衡状态的二叉树有 AVL 树和红黑树。

平衡二叉树(AVL)

AVL 树的特点：

1. 平衡二叉树又称AVL树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过1。
2. 很好的查找性能，不存在极端的低效查找的情况。
3. 可以实现范围查找、数据排序。

平衡二叉树和非平衡二叉树的对比： AVL 树顺序插入 1~16 个节点，查找 id=16 需要比较的节点数为 4。从查找效率而言，AVL 树查找的速度要高于红黑树的查找效率（AVL 树是 4 次比较，红黑树是 6 次比较）。

mysql 如果使用的是 AVL 树，我们每一个树节点只有一个根节点，内存一次磁盘IO只加载了一个数据，每层只加载2的n-1次方个，如查询id=7 这个数据我们就要进行磁盘IO三次，这很消耗时间。所以我们设计数据库索引时需要首先考虑怎么尽可能减少磁盘 IO 的次数。因此不能直接用于实现 Mysql 底层索引。

红黑树

从图示，可以发现红黑树的一些规律：

• 节点不是红色就是黑色，根节点是黑色
• 红黑树的叶子节点并非传统的叶子节点，红黑树的叶子节点是null节点（空节点）且为黑色
• 同一路径，不存在连续的红色节点
• 以上是我们能发现的一些规律，这些规律其实是红黑规则的一部分

红黑规则

1. 节点不是黑色，就是红色（非黑即红）
2. 根节点为黑色
3. 叶节点为黑色（叶节点是指末梢的空节点 Nil或Null）
4. 一个节点为红色，则其两个子节点必须是黑色的（根到叶子的所有路径，不可能存在两个连续的红色节点）

5.每个节点到叶子节点的所有路径，都包含相同数目的黑色节点（相同的黑色高度）

说明

• 约束4和5，保证了红黑树的大致平衡：根到叶子的所有路径中，最长路径不会超过最短路径的2倍。
• 使得红黑树在最坏的情况下，也能有O ( l o g 2 N )的查找效率
• 黑色高度为3时，最短路径：黑色→ 黑色 → 黑色，最长路径：黑色→ 红色 →黑色 → 红色 → 黑色
• 最短路径的长度为2（不算Nil的叶子节点），最长路径为4
• 关于叶子节点：Java实现中，null代表空节点，无法看到黑色的空节点，反而能看到传统的红色叶子节点
• 默认新插入的节点为红色：因为父节点为黑色的概率较大，插入新节点为红色，可以避免颜色冲突

红黑树的应用 Java中，TreeMap、TreeSet都使用红黑树作为底层数据结构 JDK 1.8开始，HashMap也引入了红黑树：当冲突的链表长度超过8时，自动转为红黑树 Linux底层的CFS进程调度算法中，vruntime使用红黑树进行存储。 多路复用技术的Epoll，其核心结构是红黑树 + 双向链表。

问题 红黑树很好的解决线性链表问题，但红黑树问题也比较大。 每次插入都要检查规则，再把树进行重新平衡，这个是非常消耗时间,数据量大的话，红黑树的深度会比较深，并且产生“右倾”,树一旦深就代表着我们读取磁盘次数就会增加，因此 不能直接用于实现 Mysql 底层索引。我们如果把有序二叉树变成有序多叉树，就能降低树的高度，这个就是基于红黑树演变出来的B树的核心思想。

B-Tree （PostgreSQL默认索引结构）

磁盘 IO 特点：从磁盘读取1B 数据和 1KB 数据所消耗的时间是基本一样的(空间局部性与时间局部性决定)，根据这个思路，可以在一个树节点上尽可能多地存储数据，一次磁盘 IO 就尽可能多的加载数据到内存，影响数据查询时间的是树的高度，高度越高，比较的次数越多，尽量把树的高度降低，这就是B树的设计原理了 B-Tree特点:

1. 叶节点具有相同的深度。
2. 节点中的元素从左向右递增排序
3. 所有的元素不重复
4. 所有节点都存数据

B树简单地说就是多叉树（多路平衡树），每个叶子会存储数据，和指向下一个节点的指针。 例如要查找9，步骤如下

• 我们与根节点的关键字 (17，35）进行比较，9 小于 17 那么得到指针 P1；
• 按照指针 P1 找到磁盘块 2，关键字为（8，12），因为 9 在 8 和 12 之间，所以我们得到指针 P2；
• 按照指针 P2 找到磁盘块 6，关键字为（9，10），然后我们找到了关键字 9。

总结来说，B 树用作数据库索引有以下优点：

1. 优秀检索速度
2. 尽可能少的磁盘 IO，加快了检索速度；
3. 可以支持范围查找。 缺点： B 树节点中不仅存储键值，也会存储数据，会导致每层阶数变少。 一般根节点都是常驻内存的，数据大的话层数多io就多

B+Tree

B+Tree特点：

1. 非叶子节点不存储data,只存储索引(冗余),可以放更多的索引
2. 叶子节点包含所有索引字段
3. 叶子节点用双向指针相连，提高区间访问性

B+树是通过二叉树，平衡二叉树，B树和索引顺序访问演化而来,是对B树的进一步优化。 B+Tree结构图:

B+树是B树的改进，简单地说是：只有叶子节点才存数据，非叶子节点是存储的指针；所有叶子节点构成一个有序链表

例如要查找关键字16，步骤如下

• 与根节点的关键字 (1，18，35) 进行比较，16 在 1 和 18 之间，得到指针 P1（指向磁盘块 2）
• 找到磁盘块 2，关键字为（1，8，14），因为 16 大于 14，所以得到指针 P3（指向磁盘块 7）
• 找到磁盘块 7，关键字为（14，16，17），然后我们找到了关键字 16，所以可以找到关键字 16 所对应的数据。

B+树和B树有什么不同:

1. B+树非叶子节点不存储数据的，仅存储键值(索引地址)，而B树节点中不仅存储键值，也会存储数据。B+树之所以这么做是因为在数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数会再次减少，数据查询的效率也会更快 。
2. B+树索引的所有数据均存储在叶子节点，且数据是按照顺序排列的。B+树使得范围查找，排序查找，分组查找以及去重查找变得简单高效
3. B+树各个页之间是通过双向链表连接，叶子节点中的数据是通过单向链表连接的。我们通过双向链表和单向链表连接的方式可以找到表中所有的数据。

MySql中 B+Tree

在 mysql分别创建 以myisam 和 Innodb 作为存储引擎的数据表。 Innodb 创建表后生成的文件有：

• frm:创建表的语句
• idb:表里面的数据+索引文件

Myisam 创建表后生成的文件有：

• frm:创建表的语句
• MYD:表里面的数据文件（myisam data）
• MYI:表里面的索引文件（myisam index） Myisam不支持事务原因索引与数据分开存储，两个文件无法做到一致性

MyISAM 是非聚集索引方式

即数据和索引落在不同的两个文件上。B+树索引的叶子节点并不存储数据，而是存储数据的文件地址，MyISAM 在建表时以主键作为 KEY 来建立主索引 B+树，树的叶子节点存的是对应数据的物理地址。拿到这个物理地址后，就可以到 MyISAM 数据文件中直接定位到具体的数据记录了。

Myisam检索数据过程中有 "回表操作"

回表查询

要说回表查询，先要从InnoDB的索引实现说起。InnoDB有两大类索引，一类是聚集索引（Clustered Index），一类是普通索引（Secondary Index）。

InnoDB的聚集索引 InnoDB聚集索引的叶子节点存储行记录，因此InnoDB必须要有且只有一个聚集索引。 1.如果表定义了PK（Primary Key，主键），那么PK就是聚集索引。 2.如果表没有定义PK，则第一个NOT NULL UNIQUE的列就是聚集索引。 3.否则InnoDB会另外创建一个隐藏的ROWID作为聚集索引。 这种机制使得基于PK的查询速度非常快，因为直接定位的行记录。

InnoDB的普通索引 InnoDB普通索引的叶子节点存储主键值（MyISAM则是存储的行记录头指针）。 什么是回表查询 假设有个t表(id PK, name KEY, sex, flag)，这里的id是聚集索引，name则是普通索引。 表中有四条记录： id name sex flag 1 sj m A 3 zs m A 5 ls m A 9 ww f B

聚集索引的B+树索引（id是PK，叶子节点存储行记录）：

普通索引的B+树索引（name是KEY，叶子节点存储PK值，即id）：

普通索引因为无法直接定位行记录，其查询过程在通常情况下是需要扫描两遍索引树的。 select * from t where name = 'lisi'; 这里的执行过程是这样的：

粉红色的路径需要扫描两遍索引树，第一遍先通过普通索引定位到主键值id=5，然后第二遍再通过聚集索引定位到具体行记录。这就是所谓的回表查询，即先定位主键值，再根据主键值定位行记录，性能相对于只扫描一遍聚集索引树的性能要低一些。

索引覆盖 索引覆盖是一种避免回表查询的优化策略。 具体的做法就是将要查询的数据作为索引列建立普通索引（可以是单列索引，也可以一个索引语句定义所有要查询的列，即联合索引），这样的话就可以直接返回索引中的的数据，不需要再通过聚集索引去定位行记录，避免了回表的情况发生。 覆盖索引的定义与注意事项 如果一个索引覆盖（包含）了所有需要查询的字段的值，这个索引就是覆盖索引。因为索引中已经包含了要查询的字段的值，因此查询的时候直接返回索引中的字段值就可以了，不需要再到表中查询，避免了对主键索引的二次查询，也就提高了查询的效率。 要注意的是，不是所有类型的索引都可以成为覆盖索引的。因为覆盖索引必须要存储索引的列值，而哈希索引、空间索引和全文索引等都不存储索引列值，索引MySQL只能使用B-Tree索引做覆盖索引。 另外，当发起一个被索引覆盖的查询（索引覆盖查询）时，在explain（执行计划）的Extra列可以看到【Using Index】的信息。

覆盖索引的优点

1.索引条目通常远小于数据行的大小，因为覆盖索引只需要读取索引，极大地减少了数据的访问量。

2.索引是按照列值顺序存储的，对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO小很多。

3.一些存储引擎比如MyISAM在内存中只缓存索引，数据则依赖操作系统来缓存，因此要访问数据的话需要一次系统调用，使用覆盖索引则避免了这一点。

4.由于InnoDB的聚簇索引，覆盖索引对InnoDB引擎下的数据库表特别有用。因为InnoDB的二级索引在叶子节点中保存了行的主键值，如果二级索引能够覆盖查询，就避免了对主键索引的二次查询。

Innodb 引擎的底层实现（聚集索引方式）

InnoDB 是聚集索引方式，因此数据和索引都存储在同一个文件里。首先 InnoDB 会根据主键 ID 作为 KEY 建立索引 B+树，如左下图所示，而 B+树的叶子节点存储的是主键 ID 对应的数据，比如在执行 select * from user_info where id=15 这个语句时，InnoDB 就会查询这颗主键 ID 索引 B+树，找到对应的 user_name='Bob'。

这是建表的时候 InnoDB 就会自动建立好主键 ID 索引树，这也是为什么 Mysql 在建表时要求必须指定主键的原因。 当我们为表里某个字段加索引时 InnoDB 会怎么建立索引树呢？比如我们要给 user_name 这个字段加普通索引，那么 InnoDB 就会建立 user_name 索引 B+树，节点里存的是 user_name 这个 KEY，叶子节点存储的数据的是主键 KEY。注意，叶子存储的是主键 KEY（普通索引，非主键非聚集索引）！拿到主键 KEY 后，InnoDB 才会去主键索引树里根据刚在 user_name 索引树找到的主键 KEY 查找到对应的数据（回表）。

Inodb存储引擎特点:

1. 表本身是按B+Tree组织的一个索引结构文件
2. 叶子节点包含了完整的数据记录
3. 非主键索引结构叶子节点存储的是主键的值，使其保持一致性和节省空间

为什么 InnoDB 只在主键索引树的叶子节点存储了具体数据 ?

为节省存储空间，一个表里可能有很多个索引，InnoDB 都会给每个加了索引的字段生成索引树，如果每个字段的索引树都存储了具体数据，那么这个表的索引数据文件就变得非常巨大（数据极度冗余了）。