MySQL进阶之（二）索引的数据结构

2.1 关于索引

2.1.1 什么是索引？

索引，可以理解为《新华字典》中的目录，通过目录可以快速定位到要查的汉字。MySQL 中的数据同样也是根据索引进行分类的，通过索引可以快速高效地查询到我们想要的数据。

MySQL 官方对索引的定义是这样的：索引（Index）是帮助 MySQL 高效获取数据的数据结构。

索引的本质：索引是一种数据结构。可以简单理解为是一组"满足某种特定算法，并排好序的快速查找的数据结构"，这种数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法。

2.1.2 为什么使用索引？

数据存储在磁盘上，我们每次读取数据时都会产生磁盘 I/O，如果数据不规则存放，那么读取数据时磁盘的摆臂就会前后摆动去查找数据，这样的操作是非常耗时的：

假如给数据使用二叉树这样的数据结构进行存储，显然可以减少磁盘的 I/O 次数，提高查询效率：

所以，建立索引的目的就是为了减少磁盘的 I/O 次数，加快查询效率。

索引是在存储引擎中实现的，不同的存储引擎支持的索引类型不一定相同。存储引擎中可以定义每张表的最大索引数和最大索引长度，所有的存储引擎支持每个表至少 16 个索引，每个索引的长度为 16 个字节（8 字节键 + 8 字节值），所以支持的最少总索引长度为 256 个字节。

2.1.3 索引的优缺点

01、优点

• 提高数据检索效率，降低数据库磁盘 I/O 成本
• 通过创建唯一索引，可以保证数据库表中每一行 数据的唯一性
• 加速表和表之间的连接。对于有依赖关系的子表和主表做联合查询时，可以提高查询速度
• 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，大大降低了 CPU 的消耗

02、缺点

• 增加索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间会越来越大
• 除了数据表要占用空间之外，索引也需要占用磁盘空间，并且不同的存储引擎，索引和数据的存储位置可能不同，InnoDB 存储引擎是将索引和数据存放在一个以 .ibd 结尾的文件中，MyISAM 存储引擎将索引和数据分开存储，索引存放在以 .myi 结尾的文件中，数据存放在以 .myd 结尾的文件中
• 虽然索引大大提高了查询速度，但是却会降低更新表的速度。当表中的数据要进行增删改的时候，索引也要动态维护（要重新动态分组归类排序数据的存储结构），这样就降低了数据的维护速度

2.1.4 一个简单的索引设计方案

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找时，由于我们并不能快速地定位到记录所在的页，所以只能从第一个页开始沿着双向链表一直往下找，在每一个页中根据查询条件去查找指定的记录。因为要遍历所有的数据页，所以这种方式是非常耗时的，如果一个表里有一亿条记录，那查询的效率指定是超级慢的。所以就引入了索引。

先新建一张表：

create table test (
  c1 int,
  c2 int,
  c3 char,
  PRIMARY KEY(c1)
) engine=InnoDB;

此表的行格式简化图如下：

其中，记录头里的 record_type 属性，表示记录的类型，其取值有：

• 0：普通的用户记录
• 1：目录项记录
• 2：最小记录
• 3：最大记录

next_record 表示下一条地址相对于本条记录的地址偏移量，使用箭头来表明下一条记录是谁。

把一些数据记录放到页里就是这样的：

页的编号可能不是连续的，但是只要求在逻辑上连续即可：

我们在根据某个搜索条件查找一些记录时为什么需要遍历所有的数据页呢？因为各个页中的记录并没有规律，我们也并不知道搜索条件匹配哪些页中的记录，所以就不得不依次遍历所有的数据页了。

如果我们想快速地定位到需要查找的记录在哪些数据页中，可以为快速定位记录所在的数据页建立一个目录，这个目录必须有以下两点要求：

1. 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值；
2. 给所有的页建立目录项（每个目录项有一个 key 用来存储当前页最小的主键值；page_no 用来存储页码）。

于是就有了以下的结构图：

行和行之间以单链表存储，页和页之间以双向链表存储。

记录与记录之间以单链表存储，叶子节点与叶子节点之间以双向链表存储。

针对数据页实现的简易目录就完成了，这个目录就称为 "索引"。

举个例子：现要查找主键值为 20 的记录，具体查找过程可分为两步：
第一步，先从目录项中根据二分法快速确定出主键值为 20 的记录是在目录项 3 中（因为 12 < 20 < 209），它对应的数据页是 页 9。
第二步，根据第一步确定的数据页，去数据页中定位具体的记录。

2.2 InnoDB 中的索引设计

2.2.1 迭代①：目录项记录的页

为每个数据页新建一个目录页之后，就要考虑：如果之后存储的数据量越来越大，这样的结构还是否合适？显然，如果目录项在物理空间中连续存储，在新增或者删除数据页时，目录项也要随着新增或删除，这样耗时就会增长。

那么，如何改造这种结构，以缩短这些操作的耗时呢？考虑到数据页中的行数据是以单链表的形式存储的，在新增或删除时只需要改变指针的指向即可。所以，可以将目录项也使用单链表连接，然后再组成另一个页，也就是为多个目录项建立一个页，这样实现的操作耗时也是很客观的：

那么上述的例子（查找主键值为 20 的记录）实现步骤可以变更为：
第一步：在目录项记录页中使用二分法快速定位到是哪个目录项（12 < 20 < 209），并找到此目录项对应的数据页（页 9）；
第二步：根据第一步确定的数据页，去数据页中定位具体的记录。

2.2.2 迭代②：多个目录项记录的页

多个目录项记录页也使用双向链表关联（同多个数据页）：

那么上述的例子（查找主键值为 20 的记录）实现步骤可以变更为三步了：
第一步：确定目录项记录页，因目录项记录页有多个，所以要先判断在哪个目录项记录页中；
第二步：根据第一步确定的目录项记录页，确定用户记录真实所在的页；
第三步：根据第二步记录真实所在的页定位到具体的记录。

2.2.3 迭代③：目录项记录页的目录页

如果目录项记录页过多，就可以为目录项记录页建立一个新的目录页：

2.2.4 B+Tree 结构

由 2.2.1 至 2.2.3 节汇总之后，最终形成的结构就是 B+Tree 结构：

不论是存放用户记录的数据页，还是存放目录项记录的记录页（数据页），最终都存放在 B+Tree 的结构中，我们称这些数据页为节点。

实际用户记录都存放在最下面的节点上，这些几点称为叶子节点，其余用来存储目录项的节点被称为非叶子节点或内节点，其中 B+Tree 最上边的节点也称为根节点（有可能会有多个根节点）。

一个 B+Tree 的结构可以分为很多层，规定最下面的层也就是存放实际用户记录的那一层为第 0层，之后依次往上加。

在上述推演的过程中，我们做一个非常极端的假设：存放用户记录的页最多存放 3 条记录，存放目录项记录的页最多存放 4 条记录。但在真实的环境中一个页存放的记录数量是非常大的，假设存放用户记录的叶子节点（最下层的叶子节点）代表的数据页可以存放 100 条用户记录，所有存放目录项的页（上层的非叶子节点）最多可以存放 1000 条记录（一个页默认会占用 16KB 的存储空间，键和值分别占用 8B 的存储空间，16KB / 16B = 1000），那么计算方式如下：

• 如果 B+Tree 有 1 层，也就是只有一个存放用户记录的节点，最多能存放 100 条记录
• 如果 B+Tree 有 2 层，最多能存放 1000 * 100 = 10,0000 条记录
• 如果 B+Tree 有 3 层，最多能存放 1000 * 1000 * 100 = 1,0000,0000 条记录
• 如果 B+Tree 有 4 层，最多能存放 1000 * 1000 * 1000 * 100 = 1000,0000,0000 条记录

到达 4 层的时候，就可以存放 1000 亿条记录了，但是真实的环境中几乎是不可能达到 1000 亿条数据的，所以 B+Tree 一般不会超过 4 层。

在做查询时，查找每层数据页都会产生一次 I/O，而 B+Tree 的高度一般都在 2~4 层，所以 B+Tree 一般会经历 2~4 次 I/O，但由于 InnoDB 存储引擎在设计时是将根节点常驻内存的，所以查找某一键值的记录时最多需要 1~3 次 I/O 操作。

2.3 InnoDB 中 B+ 树索引的注意事项

2.3.1 根页面位置万年不动

上面推演 B+Tree 的过程是自下而上的：

1. 先将存储用户记录的叶子节点都画出来；
2. 然后再画存储目录项记录的页；
3. 最后再画目录项记录的页的目录页。

但实际上B+Tree 是自上而下形成的，大致步骤是这样的：

1. 每当为某个表创建一个 B+Tree 索引（聚簇索引不是人为创建的，而是创建表的时候默认就有的）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个 B+Tree 索引对应的根节点中既没有用户记录，也没有目录项记录。
2. 随后向表中插入用户记录时，先把用户记录存储到这个根节点中（此时这个节点可以理解为叶子节点）；
3. 当根节点中的可用空间用完时，仍继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如说页 A，然后对这个新页进行页分裂的操作得到另一个新页，比如说页 B。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到页 A 或页 B 中，而根节点便升级为存储目录项记录的页了（此时变成了内节点）。

其中需要特别注意的是：
一个B+Tree 索引创建后，它的根节点就不会再移动了。
也就是说，对某个表创建索引后，它的根节点的页号就会被存储在某个地方，之后凡是 InnoDB 存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

2.3.2 内节点(非叶子节点)中目录项记录唯一

我们都知道，B+Tree 中聚簇索引的内节点目录项记录的内容是索引列 + 页号的组合，但是这个组合对于非聚簇索引来说就不太适用了。

举个例子：
现有表 test，有三个字段：c1（逐渐）、c2、c3，假设此表中有这么几行数据：

c1	c2	c3
1	1	‘u’
3	1	‘d’
5	1	‘y’
7	1	‘a’

当我们为 c2 列创建二级索引时，如果目录项页中记录的只是索引列 + 页号的组合时，创建完索引后 B+Tree 的结构大概是这样的：

其中，数据结构的组成是这样的：

• 红色方块是页码值
• 黄色方块是索引列的值（也就是 c2 列的值）
• 蓝色方块是主键值

如果此时，我们想新插入一条记录（9, 1, ‘c’），那么就会出现一个问题：页 3 中存储的目录项记录是由 c2 + 页码组成的，页 3 中的两条目录项记录对应的 c2 列的值还都是 1，我们想要插入的新纪录中 c2 列的值也是 1，那如何判定我这条数据是放到页 4 中还是 页 5 中呢?

为了让新插入的记录能找到自己在哪个页里，我们需要保证在 B+Tree 中的同一层内节点（非叶子节点）的目录项记录是唯一的，现在我们根据索引列的值来查找，显然不能保证唯一性，所以我们就可以把主键值 + 索引列值一起存储在目录项记录中（因为很容易就能想到主键的唯一性）：

这样的话，我们再插入新的记录时就可以很快的判断出应该将数据插入到哪个页中了。

新数据（9, 1, ‘c’）插入后应该是这样的：

2.3.3 一个页面最少存储两条记录

一个 B+Tree 只需要很少的层级就可以轻松存储数亿条记录，查询速度也是相当快的。

B+Tree 本质上就是一个很大的多层级目录，每经过一个目录时就会过滤掉很多无效的子目录，最终会查找到存储真实数据的目录。

一个大的目录中只存放一个子目录是什么效果呢？那就是目录层级非常非常非常多，但是最后的那个存放真实数据的目录中只能存放一条记录，也就是我们费了老半天劲去二分、查找等一系列 I/O 之后，最后发现却只能存放一条数据，这不得直接心态爆炸啊。

所以，InnoDB 存储引擎的一个数据页中至少可以存放两条记录。

2.4 常见索引概念

索引按照物理实现方式，索引可以分为两种：聚簇（聚集）索引和非聚簇（非聚集）索引。我们也把非聚集
索引称为二级索引或者辅助索引。

2.4.1 聚簇索引

一个表中只能有一个聚簇索引，主键就是一个默认的聚簇索引。
聚簇索引的叶子节点存储的是一整行记录。

优点：

1. 数据访问更快 。因为聚簇索引将索引和数据保存在同一个 B+Tree 中，因此从聚簇索引中获取数据比非聚簇索引更快。
2. 聚簇索引对于主键的排序查找和范围查找速度非常快。
3. 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的 I/O 操作。

缺点：

1. 插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的 ID 列为主键.
2. 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于 InnoDB 表，我们一般定义主键为不可更新.
3. 二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据。

限制：

1. 对于 MySQL 数据库目前只有 InnoDB 数据引擎支持聚簇索引，MyISAM 不支持。
2. 一个表中只能有一个聚簇索引，主键就是一个默认的聚簇索引。
3. 如果没有定义主键，InnoDB 会选择非空的唯一索引代替。如果没有这样的索引，InnoDB 会隐式的定义一个主键来作为聚簇索引。
4. 为了充分利用聚簇索引的聚簇特性，所以 InnoDB 表的主键列尽量选用有序的顺序 id，而不建议用无序的 id。

2.4.2 二级索引

二级索引页称为非聚簇索引、辅助索引，它是主键索引之外的索引。

我们可以为非主键列各自建立一个非聚簇索引（B+Tree），不同的 B+Tree 的数据采用不同的排序规则，现在为 c2 列创建一个非聚簇索引，B+Tree 的结构是这样的：

其中，数据结构的组成是这样的：

1. 蓝色方块存储的是 c2 列本身的 value值。
2. 黄色方块存储的是 c1(主键) 的 value。

所以，我们可以得出一个结论：非聚簇索引中的叶子节点，存储的是当前列的值 + 主键值。

01、查询语句1

现在有一条查询语句：

select c1, c2 from test where c2 = 4;

c2 列是非聚簇索引，查找的顺序是这样的：

此时因为叶子节点中包含了我们要查找的 c1 和 c2，所以直接将记录返回给客户端就好了。

02、查询语句2

如果把查询语句改一下：

select c1, c2, c3 from test where c2 = 4;

查询结果中包含了非索引列，那么查找顺序又是如何呢？分为两个步骤：

1. 按照上面的流程，根据 where 条件查找出满足条件的列（上面查出了两条记录）；
2. 根据主键（黄色方块的值 c1 = 4, c1 = 10）再回表查询（通过主键索引结构查出真正的结果）。

03、回表查询

非主键列建立的 B+Tree 需要一次回表查询才可以定位到完整的用户记录，所以称这种索引为二级索引（second index）或辅助索引。

那为什么不能把一整行记录数据放在非聚簇索引的叶子节点上呢？

原因：一个表中可以有多个非聚簇索引，如果每个非聚簇索引的叶子节点上都存放一份完整的数据，假设表中有 1000 行数据，总共四个字段，每个字段都单独创建一个索引，那最终就会存储四份（4 个 1000 行），这样会造成大量的数据冗余，浪费存储空间。

非聚簇索引的存在不影响聚簇索引的组织结构，所以一张表可以有多个非聚簇索引：

总结：

• 聚簇索引的叶子节点存储的是用户记录，非聚簇索引的叶子节点存储的是数据位置(索引列的值和主键值)，非聚簇索引不会影响数据表的物理存储顺序。
• 一个表中只能有一个聚簇索引，但是可以有多个非聚簇索引。
• 使用聚簇索引的时候，数据的查询效率很高，但是对表进行增删改操作的时候，效率会比非聚簇索引要低的多。

对表进行新增和删除操作，对索引的影响很大，特别是主键索引，对二级索引影响相对较小。

对表进行更新操作，对主键索引有影响，如果更新的字段中也存在索引，只会对涉及到的二级索引有影响。

2.4.3 联合索引

联合索引：即同时为多个列建立的索引。

比如：我们想让 B+Tree 按照 c2 列和 c3 列的大小进行排序，其中包含了两层含义：

• 先把各个记录和页按照 c2 列进行排序；
• 在记录中 c2 列相同的情况下，采用 c3 列进行排序

下面是为 c2 列和 c3 列组成的一个联合索引结构（index_c2_c3），其中，数据结构的组成是这样的：

• 蓝色方块是 c2 的字段值
• 紫色方块是 c3 的字段值
• 黄色方块是 c1 的字段值（主键值）
  
  现有一条查询 SQL：

select * from test where c2 = 4 and c3 = 'u';

执行的过程是这样的：

1. 按照 c1 列查找；
2. 如果 c1 列相同，按照 c2 列查找。
   
   需要注意的是，以 c2 和 c3 的大小为排序规则建立的 B+Tree 称为联合索引，其本质上也是一个二级索引，它的意思与分别给 c2 和 c3 列建立索引的表述是不同的：
3. 建立联合索引只会建立像上图一样的一颗 B+Tree。
4. 为 c2 和 c3 列分别建立索引时，会分别以 c2 和 c3 列的大小为排序规则建立两棵 B+Tree。

2.5 MyISAM 中的索引设计

2.5.1 MyISAM 索引结构

适用于 B+Tree 的存储引擎有：

索引/存储引擎	MyISAM	InnoDB	Memory
B+Tree	支持	支持	支持

虽然多个存储引擎都支持 B+Tree 索引，但是在底层的实现原理上是不同的：InnoDB 和 MyISAM 底层默认使用 B+Tree 索引，而 Memory 底层默认使用 hash 索引。

InnoDB 的索引即数据：

• 聚簇索引的叶子节点存储的是完整的数据：主键 + 数据
• 非聚簇索引的叶子节点存储的是：索引列 + 主键

MyISAM 的索引虽然也是 B+Tree 结构，但是底层却是将数据和索引分开单独存储的：

• 数据文件(.myd 文件)：存数据的文件，插入记录时，并没有按照主键大小刻意去排序，有多少塞多少
• 索引文件(.myi 文件)：MyISAM 为每张表的主键都创建一个 B+Tree 索引，但是叶子节点中存储的数据是：主键 + 地址

MyISAM 存储引擎的结构大概是这样的：

其中，数据结构的组成：

• 绿色方块是主键值
• 紫色方块是地址偏移量

这样来看，其结构与 InnoDB 的聚簇索引没什么区别。但是有一点，因为主键索引的每一行记录都是唯一的，所以只需要存储主键+地址即可。但是非主键列（二级索引）是不唯一的，很有可能会重复，如果为非主键列创建索引，大概是这样的：

最终组成节点的结构为：

• 叶子节点：索引列 + 主键值 + 地址
• 非叶子节点：索引列 + 主键值

2.5.2 MyISAM 与 InnoDB 对比

• MyISAM 中的索引都是非聚簇索引，InnoDB 中包含两种索引聚簇索引和非聚簇索引。
• MyISAM 的叶子节点中存储的为索引 + 地址，所以查询到地址之后，至少还需要一次回表查询；InnoDB 的聚簇索引叶子节点中的存储的是完整的记录，所以根据主键查询可以直接返回，不需要回表查询。
• MyISAM 索引记录存储的是地址，InnoDB 聚簇索引存储的是···主键 + 数据···，非聚簇索引 data 域存储的是主键。
• MyIASM 回表查询的速度非常快，因为叶子节点中查询到是数据的地址偏移量直接去文件中查找相当的快，而 InnoDB 叶子节点查到的是主键值，然后再根据主键去聚簇索引中查询数据。虽然也不慢，但是相比于用地址查询，还是差了点。
• MyISAM 可以没有主键；InnoDB 必须要有主键，如果没有显示指定，则 MySQL 自动选择一个 非空且能唯一标识记录的列作为主键，如果不存在这样的列，则会自动为 InnoDB 表生成一个 隐含字段作为主键，字段长度为 6 个字节，类型为长整型。

两个索引分布对比图：

2.5.3 索引的建议

为什么不建议使用过长的字段作为主键？

1. 因为二级索引节点中都会引用主键索引，过长的主键索引会导致二级索引树结构变的很臃肿。

2. 用非单调的字段作为主键在 InnoDB 中不是一个好主意，因为 InnoDB 索引本身是一颗 B+Tree，非单调的主键会导致在插入记录时，数据文件为了维护树的结构而频繁的进行页分裂，导致性能比较低效，而使用自增且单一的字段作为主键是个很好的选择。

2.5.4 索引的代价

索引是个好东西，但是可不能乱建，它在空间和时间上都会有消耗：

• 占用空间：每个索引都要建立一棵 B+Tree，每个节点都是一个数据页，一个数据页为 16 KB，一棵很大的 B+Tree 由很多个数据页组成，就会占用很大的空间。
• 消耗时间：对表进行增删改操作时，都要去修改各个 B+Tree 的结构。因为 B+Tree 的各个节点都是根据索引列值从小到大按顺序排序而组成的双向链表。而不论是叶子节点还是内节点（非叶子节点）中的记录都是按照索引列的值从小到达按顺序组成的单向链表，所以如果对表进行增删改的操作，会对各个节点和记录的排序造成破坏，存储引擎为了维护索引结构的平衡会进行额外的记录移位、页面分裂、页面回收等操作，会造成性能大幅下降。

一个表中创建的索引越多，占用的空间越大。在增删改操作时，存储引擎维护索引消耗的时间就越多。

为了能建立好的索引，所以要根据数据的分布情况建立合理的索引结构。

2.6 MySQL 数据结构选择的合理性

从 MySQL 的角度讲，不得不考虑一个现实问题就是磁盘 I/O。如果我们能让索引的数据结构尽量减少磁盘的 I/O 操作，所消耗的时间也越小。可以说，磁盘 I/O 对索引的使用效率至关重要。

数据库索引是存储在外部磁盘上的，当我们利用索引查询的时候，不可能把整个索引全部加载到内存，只能逐一加载，所以 MySQL 衡量查询效率的标准就是磁盘 I/O 次数。

B+Tree 中的根节点从创建开始就常驻内存中的，每次查询数据的时候，从内存中的根节点开始查找到合适的子节点记录（这一步是不需要磁盘 I/O 的），B+Tree 最多有 4 层，所以查询某一个键值的时候，最多只需要 1~3 次磁盘 I/O。

2.6.1 全表扫描

全表扫描也就是使用非主键列（且没有建立索引）作为 where 查询条件，只能进行全表扫描（顺序方式查找），性能非常差。

常见的加快查找速度的数据结构有两类：

• 树：例如平衡二叉树，增删改查的平均时间复杂度都是 O(log2N)
• 哈希：例如 HashMap，增删改查的平均时间复杂度都是 O(1)

2.6.2 Hash 结构

Hash 本身是一个函数，也被称为散列函数，可以帮助我们大幅度提升检索数据的效率。

Hash 是通过某种特定的算法（MD5、Base64、SHA256 等）将输入转化为输出。

相同的输入永远可以得到相同的输出。

采用 Hash 进行检索效率非常高，基本上一次检索就可以找到数据，而 B+Tree 需要自顶向下一次查找，多次访问节点才能找到数据，中间需要多次 I/O 操作，从效率上来说 Hash 比 B+Tree 更快。

Java 中常用的 HashMap 和 HashSet 的底层就是用的哈希结构存储的数据。

在 Hash 的方式下，一个元素处于 h(k) 中，即利用哈希函数算法，根据关键字 k 计算出一个哈希值（也就是在槽中的位置），函数 h 将关键字域映射到哈希表 T[0……m-1] 槽位上：

像下面这种，哈希函数 h 有可能将两个不同的关键字映射到同一个位置，这叫做哈希碰撞。

在数据库中一般采用链接法来解决，也就是将散列到同一槽位的元素放在一个链表中：

那么，既然 Hash 结构的效率高，为什么索引结构要设计成树型呢？

• Hash 索引仅可以满足 (=)、(<>)、(IN ) 条件下的查询。如果进行范围查询，哈希型的索引时间复杂度会退化为 O(n)；而树型的 “有序” 特性依然能够保持 O(log2n) 的高效率。
• Hash 索引还有一个缺陷，就是数据的存储是无序的，在 order by 的情况下，使用 Hash 索引还需要对数据重新排序。
• 对于联合索引的情况，Hash 值是将联合索引键合并后一起来计算的，无法对单独的一个键或者几个索引键进行查询。
• 对于等值查询来说，通常 Hash 索引的效率更高。但是也有一种特殊的情况，就是索引列的重复值有很多时，效率就会降低。因为遇到 Hash 冲突时，需要遍历桶中的行指针来进行比较，找到查询的关键字，这个步骤是非常耗时的。所以，Hash 索引通常不会用到重复值很多的列上，比如列为性别、年龄等的情况。

Hash 索引适用的存储引擎有：

索引/存储引擎	InnoDB	MyISAM	Memory
Hash 索引	不支持	不支持	支持

Hash 索引是 Memory 引擎默认的索引结构。

Hash 索引存在很多限制，相比之下 B+Tree 索引的适用会更加广泛，不过有些场景采用 Hash 索引效率会更高，比如在键值型数据库中，Redis 底层存储的核心就是 Hash 表。

另外，InnoDB 本身是不支持 Hash 索引的，但是提供了自适应 Hash 索引。

那么，什么情况下才会使用自适应 Hash 索引呢？

如果某个数据经常被访问，当满足一定条件时，就会将这个数据页的地址存放到 Hash 表中，这样下次查询的时候，就可以直接找到这个页面的所在位置，这样 B+Tree 也相当于具备了 Hash 索引的优点。

采用 Hash 索引目的是为了方便根据 SQL 的查询条件定位到叶子节点上，特别是 B+Tree 比较深的时候，通过自适应 Hash 索引可以明显提高数据的检索效率。

我们可以通过 innodb_adaptive_hash_index 变量来查看是否开启了自适应 Hash：

show variables like '%adaptive_hash_index';

2.6.3 二叉搜索树

二叉搜索树的特点：

• 每个节点只能有两个子节点。
• 左子节点 < 当前节点；右子节点 ≥ 当前节点

查询规则

最基础的二叉搜索树，搜索某个节点和插入节点的规则一样，假设搜索插入的数值为 key：

• 如果 key 大于根节点，则在右子树中查找；
• 如果 key 小于根节点，则在左子树中查找；
• 如果 key 等于根节点，说明根节点就是要找的数据，返回根节点即可。

但是有一种特殊情况：

很明显，树跑偏了。此二叉树的深度非常大，性能上已经退化成了一条链表，时间复杂度变成了 O(n)，树的深度也变成了 7，也就是最多要查询 7 次。那么，如果树的深度更深，有成千上百层，每次查询都要消耗一次磁盘 I/O，查询效率将会非常低下。

如果想要提高查询的效率，就需要减少磁盘 I/O，也就是需要尽量降低树的高度，也就是把原来的 “瘦高” 树变成 “矮胖” 树，每层分叉越多越好。

2.6.4 AVL 树

为了解决上面二叉查找树退化成链表的问题，又提出了平衡二叉搜索树，又被称为 AVL 树，它在二叉搜索树上增加了约束。

它所具有的性质：它是一棵空树或左右两颗子树的高度差最大不会超过 1，并且左右两个子树都是一棵平衡二叉树。

常见的平衡二叉树有很多种，包括了平衡二叉搜索树、红黑树、数堆、伸展树。平衡二叉搜索树是最早提出来的自平衡二叉搜索树，我们一般提到的二叉树指的就是平衡二叉搜索树。

如果树结构跑偏了，则会让节点自动平衡旋正：
上面有 5 个层级，如果要查找的数据恰巧在第 5 层叶子节点中，则需要经历 5 次 I/O 操作。虽然平衡二叉搜索树的效率比较高，但是树的深度如果也足够高时，磁盘的 I/O 操作次数依然会很多，效率也会很低。

这对这种情况，我们把二叉树改为了 M 叉树（M>2），也就是说打破上面每个节点只有两个子节点的问题，如果 M=3，同样存储上述 31 个节点，结构就变成了这样：

每个节点都有 3 个子节点，整体变为了最多 4 次 I/O 操作。

这样看来，相比最开始的高瘦，确实矮胖了很多。

结论：M（M>2）叉树的高度远小于二叉树的高度。

2.6.5 B-Tree

B 树（Balance Tree），也称为多路平衡查找树，简写为 B-Tree（中间的杠，是为了将其连起来，不是减号）。

它的高度远小于二叉平衡树。

B-Tree 作为多路平衡二叉树，它的每一个节点最多可以包括M 个子节点，M 称为 B 树的阶。

阶是每个磁盘最多容纳的关键字个数。

每个磁盘块中，包括了关键字和子节点的指针。

一个磁盘中包含 x 个关键字，那么指针数就是 x+1。

如果一个 B 树的阶是 100，树的层级为 3，那最多可以存储 100 万条数据。对于大量的索引数据，采用 B-Tree 的结构是非常合适的。

结论：

• B-Tree 相比于平衡二叉树来说磁盘 I/O 操作要少很多，查询效率也就越高。
• 只要树的高度足够低，I/O 次数足够少，查询效率就会越高。
• B-Tree 在增删改的时候会导致树不平衡，这个时候就需要树进行自旋保持树的自平衡。
• 关键字集合分布在整棵树中，也就是叶子节点和非叶子节点都会存储数据。
• 其搜索性能等价于在关键字集合内做一次二分查找。

真正 B-Tree 的结构如下：

举例：

假设我们想要查找的关键字是 9 ，那么步骤可以分为以下几步：

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

2.6.6 B+Tree

B+Tree 也是一种多路搜索树，它是在 B-Tree 的基础上做了改进。

主流的 DBMS 都支持 B+Tree 的索引方式，相比于 B-Tree，B+Tree 更适合于文件索引系统。

B-Tree 和 B+Tree 的区别：

• B-Tree 的每一个节点都存储数据， B+Tree 的中间节点不存储数据。

• B-Tree 查询不稳定（非叶子节点也会存储数据），B+Tree 查询相对比较稳定（B+Tree 每次只有访问到叶子节点时才能找到对应的数据）。

• B-Tree 查询效率比较低，B+Tree 查询效率比较高（B+Tree 比 B-Tree 更矮胖，阶数更大，深度更低）。

• 在查询范围上，B+Tree 的效率要更高（因为 B+Tree 中关键字存储在叶子节点中，叶子节点之间会有指针，并且数据是递增的，范围查找时可以通过指针连接查找。而 B-Tree 中需要通过中序遍历才能完成范围的查找）。

• B+Tree 的所有数据都在叶子节点上，并且数据是顺序排序的，可以通过指针查找。

• B-Tree 的数据存储左右的节点上，需要通过中序遍历查找。

B-Tree 和 B+Tree 都可以作为 MySQL 的索引结构，没有谁比谁更好，他们两个都有各自的应用场景，结合场景才能发挥各自的优势。

下面有几个常见的问题：

01、为了减少IO，索引树会一次性加载吗？

索引都是存储在磁盘中的，如果数据量很大，那索引的大小也会很大，甚至几个 G。

所以不可能一次性加载到内存中，只能逐一加载每一个磁盘页，因为磁盘页对应着索引的节点。

02、B+Tree 的存储能力如何？为何说一般查找行记录，最多只需 1~3 次磁盘 I/O

我们都知道，一个数据页的大小为 16KB，一般表的主键类型为 INT（4 个字节） 或 BIGINT（8 个字节），指针类型一般也为 4 个字节或 8 个字节，也就是一个数据页大概存储 【16KB / (8B+8B) = 1k 个键值】（其中键和值各为 8 字节B）。

因为这是非叶子节点不存储数据，所以键值会多一点，但真实的数据表一般都会挺大的，叶子节点中的每个页可能大概会存储 500-1000 条，假设我们取每页 1000 条数据，一个深度为 3 的 B+Tree，可以维护 10 的三次方 × 10 的三次方 × 10 的三次方 = 10 亿条数据。

但实际上每个数据页可能存不满，因此在数据库中，B+Tree 的高度一般在 2 ~ 4 层左右。假设达到了 4 层，那顶层也就是根节点，根据存储引擎的设计：根页面位置万年不动。所以一般从创建开始就会将根节点提前加载到内存中（常驻内存）了。所以 2 ~ 4 => 2-1 ~ 4-1 = 1 ~ 3。

03、为什么说 B+Tree 比 B-Tree 更适合实际应用中操作系统的文件索引和数据库索引？

• B+Tree 的磁盘读写代价更低。

  B+Tree 的内部节点并没有指向关键字具体信息的指针，所以其内部节点相对 B-Tree 更小。如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。这相对于 I/O 读写次数也就降低了。

• B+Tree 的查询效率更加稳定

  因为非叶子节点并不是最终指向文件内容的节点，而只是叶子节点中关键字的索引（B+Tree 每次只有访问到叶子节点时才能找到对应的数据）。所以任何关键字的查找必须走一条从根节点到叶子节点的路，所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

04、Hash 索引与 B+Tree 索引的区别

• Hash 索引不能进行范围查询，而 B+Tree 可以。因为 Hash 索引指向的数据是无序的，而 B+Tree 的叶子结点是个有序的链表。
• Hash 索引不支持联合索引的最左前缀原则（联合索引的部分索引无法适用，like 查询就无法起到优化作用），而 B+Tree 可以。Hash 索引在计算 Hash 值的时候是将联合索引键合并后再一起计算 Hash 值，所以不会针对每个索引单独计算 Hash 值。所以，如果用到联合索引的一个或者几个索引时，联合索引就无法被使用了。
• Hash 索引不支持 order by 排序，因为 Hash 索引指向的数据是无序的，所以没办法起到排序优化的作用。而 B+Tree 索引数据是有序的，可以对该字段 order by 排序优化。
• InnoDB 不支持哈希索引。

05、Hash 索引与 B+Tree 索引是在建索引的时候手动指定的吗？

MySQL 支持的存储引擎对各类索引的支持：

针对 InnoDB 和 MyISAM 存储引擎都会默认采用 B+Tree 索引，无法使用 Hash 索引。

InnoDB 提供的自适应 Hash 是不需要手动指定的，但如果是 Memory/Heap 和 NDB 存储引擎，是可以进行选择 Hash 索引的。

2.6.7 R 树

R-Tree 在 MySQL 中很少使用，仅支持 geometry 数据类型，支持该类型的存储引擎有：

索引/存储引擎	MyISAM	InnoDB	Memory
R-Tree索引	支持	支持	不支持

举例子：查询 20 英里以内所有的餐厅。正常情况下会将每个餐厅的（x，y）的经纬度分别存储到一个字段中，然后挨个去遍历查找，但是面对超大型地图数据库，这种遍历方式就不行了，效率非常低下，所以这个时候 R-Tree 的作用就出来了，很好的解决了高纬度空间搜索问题。

R-Tree 是一棵用来存储高纬度数据的平衡树。

2.6.8 小结

使用索引可以帮助我们从海量的数据中快速定位想要查找的数据。但是索引也存在一些不足，比如：占用存储空间、降低数据库写操作的性能等等，如果有多个索引还会增加索引选择的空间。

所以，我们在使用索引的时候，需要平衡索引的利（提升查询效率）和弊（维护索引所需的代价）。

原文地址：https://blog.csdn.net/qq_50994235/article/details/135306359

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！