mysql sort默认 mysql默认索引数据结构

一、为什么使用索引

如下如果不使用索引，进行查找某个字段的话就是全表扫描，如果改查询数据是最后面，数据量还比较大，那么效率就非常低，进行的I/O读取次数就很多。影响效率。

假如给数据使用二叉树这样的数据结构进行存储，如下图所示：

这样的查询的效率就是全表的扫描的一半了。效率提升比较高

二、索引的优缺点

2.1 索引概述

MySQL官方对索引的定义为：索引(Index) 是帮助MySQL高效获取数据的数据结构。

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

2.2 优点

(1) 类似于大学图书馆建书目索引，提高数据检索的效率，降低 数据库的IO成本

(2) 通过创建唯一索引，可以保证数据库中每一行数据的唯一性。

(3) 在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的字表和父表联合查询时，可以提高查询速度。

(4) 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

2.3 缺点

增加索引也有需要不利的方面，主要表现在如下几个方面：

(1) 创建索引和维护索引要耗费时间，并且随着数据量的增加，所消耗的时间也会增加。

(2) 索引需要占 磁盘空间 ，除了数据表占数据空间之外，每个索引还要占一定的物理空间，存储在磁盘上，如果有大量的索引，索引文件就可能比文件更快达到最大文件尺寸。

(3) 虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

因此，选择使用索引时，需要综合考虑索引的优点和缺点。

三、InnoDB 中索引的推演

3.1 索引之前的查找

先来看一个精确的匹配的例子：

SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所再的页，所以只能从第一页 沿着双向链表 一直往下找，在每一个页中根据我们上面的查找方法去查找指定的记录。因为要遍历所有的数据页，所以这种方式显然是超级耗时的。如果一个表有一亿条数据呢？此时索引应运而生。全表扫描

3.2 索引设计

建一个表：

mysql> CREATE TABLE index_demo(
-> c1 INT,
-> c2 INT,
-> c3 CHAR(1),
-> PRIMARY KEY(c1)
-> ) ROW_FORMAT = Compact;

这个新建的 index_demo 表中有2个INT 类型的列，1个CHAR(1) 类型的列，而却我们规定了c1列为主键，这个表使用Compact

这种方式可以看做是一个链表方式了。

我们只在示意图里展示记录的这几个部分：

• record_type ：记录头信息的一项属性，表示记录的类型,0表示普通记录、2表示最小记录、3表示最大记录、1暂时没有用过。
• next_record： 记录头信息的一项属性，表示下一条地址相对于本条记录的地址的偏移量，我们用箭头来表明下一条记录的是谁。
• 各个列的值：这里只记录在index_demo 表中三个列，分别是c1、c2 和c3。
• 其他信息：除了上述3中信息以外的所有信息，包括其他隐藏列的值以及记录的额信息。

将记录格式示意图的其他信息项暂时去掉并把它竖起来的效果就是这样：

把一些记录放到页里的示意图就是：

一个简单的索引设计方案

我们在根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢？因为各个的数据并没有规则，我们不知道我们的搜索条件匹配哪些也中记录，所以不得不依次遍历所有的数据页。所以如果我们想快速的定位到需要查找的记录在哪些数据页 中该咋办？我们可以快速定位记录所再的数据页而建立一个目录，建一个目录必现完成下边这些事：

• 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。
• 给所有的页建立一个目录项。

所以我们为上边几个页做好的目录就像这样子：

以页28为例，它对应目录项2，这个目录项中包含着该页的页号28以及该页中用户记录的最小主键值5。我们只需要把几个目录项在物理存储器上连续存储（比如：数组），就可以实现根据主键值快速查找某条记录的功能了。比如：查找主键值为20的记录，具体查找过程分为俩步：

1. 先从目录项中根据 二分法 快速确定出主键值为 20 的记录在 目录项3 中（因为 12 < 20

209 ），它对应的页是 页9

2. 再根据前边说的在页中查找记录的方式去 页9

至此，针对数据页做的简易目录就搞定了。这个目录有一个别名，较为索引索引。

InnoDB 中的索引方案

① 迭代1次：目录项记录的页

我们把前边使用到的目录项放到数据页中的样子就是这样：

从图中可以看出，我们新分配了一个表还为30的页来专门存储目录项记录。这里再次强调目录项记录和普通的用户记录的不同点：

• 目录项记录 的record_type 值是1，而普通用户记录的record_type 值是0.
• 目录项记录只有主键值和页的编号俩列，普通的用户记录的列是用户自定义的，可能包含了很多列，另外还有INNDB自己添加的隐藏列。
• 了解：记录头信息还有一个叫min_rec_mask的属性，只有在存储目录项记录的页中的主键最小的目录项记录的min_rec_mask值为1，其他别的记录的min_rec_mask值都是0。

相同点：两者用的是一样的数据页，都会为主键生成Page Directory （页目录），从而在按照主键值进行查找时可以使用二分法来加快查询速度。

现在以查找主键为 20

1. 先到存储 目录项记录 的页，也就是页30中通过 二分法 快速定位到对应目录项，因为 12 < 20

209

2. 再到存储用户记录的页9中根据 二分法 快速定位到主键值为 20

② 迭代2次：多个目录项纪录的页

从图中可以看出，我们插入了一条主键值为320的用户记录之后需要两个新的数据页：

为存储该用户记录而新生成了 页31

因为原先存储目录项记录的 页30的容量已满

不需要一个新的 页32 来存放 页31

现在因为存储目录项记录的页不止一个，所以如果我们想根据主键值查找一条用户记录大致需要3个步

骤，以查找主键值为 20

1. 确定 目录项记录页

我们现在的存储目录项记录的页有两个，即 页30 和 页32

范围是 [1, 320) ，页32表示的目录项的主键值不小于 320 ，所以主键值为 20

录项记录在 页30

2. 通过目录项记录页 确定用户记录真实所在的页

在一个存储 目录项记录

3. 在真实存储用户记录的页中定位到具体的记录。

③ 迭代3次：目录项记录页的目录页

如图，我们生成了一个存储更高级目录项的 页33

户记录的主键值在 [1, 320) 之间，则到页30中查找更详细的目录项记录，如果主键值 不小于320

话，就到页32中查找更详细的目录项记录。

我们可以用下边这个图来描述它：

这个数据结构，它的名称是 B+树

④ B+Tree

一个B+树的节点其实可以分为好多层，规定最下边的那层，也就是存放我们用户记录的那层为第0层，之后依次往上加。之前那我们做了一个非常阶段的假设：存放用户记录的页最多存放3条记录，存放目录的页最多存放4条记录。其实真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录，所有存放目录项记录的内节点代表的数据页可以存放1000条目录项记录，那么：

• 如果B+树 只有一层，也就说只有1个用于存放用户记录的节点，最多能存放 100条记录。
• 如果 B+树 有2层，最多能存放1000x100=10,0000条记录。
• 如果 B+树 有3层，最多能存放1000x1000x100=1,0000,0000条记录。
• 如果 B+树 有2层，最多能存放1000x1000x1000x100=1000,0000,0000条记录。相当多的记录！！！

你的表里能存放1000,0000,0000条记录吗？所以一般情况下，我们用到的B+树都不会超过4层，那我们通过主键值去查找某条记录最多只需要做4个页面内的查找（查找3个目录项页和一个用户记录页），又因为在每个页内所谓的pageDirectory （页目录），所以在页面内可以通过二分法实现快速的定位记录。

# b+tree Innodb 聚簇索引的介绍

1.b+tree 里面存放是数据页，数据页又可以分为 目录页和数据页，目录页用来存放具体的数据页的位置的用来方便具体定位的

2.最底层或者说叶子叶子节点存放的是具体的表中的数据。其他都是存放的叶子节点的索引和数据页的地址。

3.b+tree的索引都是排序的，必现是排序的。

4.数据页 是一个里面存放的是表中具体数据，一个记录有看做成一个单项链表，链表中里面包括 记录类型、聚簇索引、和所有字段信息、下一个数据页的地址。数据页之间是一个双向链表绑定，都是排好序

5.目录页存放的 记录类型 、数据页的聚簇索引和数据页地址、下一个目录页的地址。都是排好序。目录页之间是一个双向链表绑定

6.一个数据页的大小是16kb

4. 创建流程
 - 1. 首先在启动时会加表中数据（数据页），会先创建一个跟节点，（数据页）节点里面存放的具体的数据
 - 数据的结构是一个单项链表里面存放这个 类型和具体字段还有下一个数据节点的地址。一个数据页的大小是16kb,当超过16kb，就会进行创建一个新的数据页节点，将的当前跟节点的数据拷贝到新的节点上，然后删除当前跟节点里的数据，保存新节点的最小索引和数据页地址，数据页和数据页之间是通过双向链表绑定的。然后都是有序的。如果下面的数据页过多，那么一个目录页存放不下了，那么就会在创建一个目录页将当前数据（存储的数据页的节点信息）复制到新的目录页，然后当前跟跟节点的存储新的目录页的依赖的数据页索引信息和目录页地址。所以说跟跟节点是永远不动的。

3.3 常见的索引概念

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

在Innodb中索引及数据，数据及索引。

聚集索引就是数据和索引存储在一起的，叶子节点的数据页之间的主键值多是有排好序的，在添加数据的同时b+树已经建立起来了，索引建议使用自增的，因为如果前面都排好序了，再次插入的索引是在一个中间位置

此时中间位置的叶子节点的数据也已经满了，所以此时该数据也的数据就需要往后移动，那么就会影响到后面的移动因为还有目录页节点，又会联代影响到目录页节点。

聚簇索引

特点：

1.使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

• 页内的记录时按照主键的大小顺序排成一个单项链表。
• 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
• 存放目录项记录的页分为不同的层次，在同一层中的页也是根据页中目录记录的主键大小排成一个双向链表。

2.B+树的叶子节点存储的是完整的用户记录。

所谓完整的用户记录，就是指这个记录中存储了所有列的值。（包括隐藏列）。

优点：

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

缺点：

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

限制：

• 对于MySQL 数据库目前只有 Innodb 数据引擎支持聚簇索引，而MyISAM 并不支持聚簇索引。
• 由于数据物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。一般情况下就是该表的主键。
• 如果没有自定义主键，Innodb会选择非空的唯一索引替代。如果没有这样的索引，Innodb会隐式的定义一个主键作为聚簇索引。
• 为了充分利于聚簇索引的聚簇的特性，所以Innodb表的主键尽量选用有序的顺序id，而不建议用无序的id，比如UUID、MD5、HASH、字符串作为主键无法保证数据的顺序增长。

二级索引 （辅助索引、非聚簇索引）

# 非聚合索引的介绍

1. 存储也是数据页和目录页

2. 只在叶子节点存放数据，目录节点只存放 数据页的地址和索引信息 

3. 数据页和目录页的索引都是排序的

4. 不同点：数据页，它存放了头信息、聚簇索引、非聚簇索引、没有存储其他的列。目录页还需要存储主键值来保证唯一性。当二级索引相同时，则需要判断主键值来进行定位数据页位置。

5. 所以查询时需要先查询到该字段的聚簇索引，然后根据聚簇索引查询具体的数据 

6. 结构都是单链表+双链表的结构。

7. 跟节点不动
8. 实际上了非聚合索引在目录页存储时需要加上主键索引,因为如果非聚合索引相同的话，那么就不知道具体要往哪里插入了,所以需要有主键索引因为主键索引是唯一的
9. 二级索引在内节点(目录页节点)也是有保存聚合索引的值

如果我们想别的列作为搜索条件该怎么办呢？肯定不能是从头到尾验证链表依次遍历一遍。

答案：我们可以使用多建几棵B+树，不同的B+树中的数据采用的不用的排序规则。比如我们用c2列的大小作为数据页，页中记录的排序规则，再建一颗B+树，效果如图：

这个B+树与上边介绍的聚簇索引有几处不同的：

• 使用记录c2列的大小进行记录和页的排序，这包括三个方面的含义
• 页内的记录是按照c2列的大小排列成一个单项链表。
• 各个存放用户记录的页也是根据页中记录的c2列大小顺序排成一个双向链表。
• 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录记录的c2大小排列成一个双向链表。
• B+树 的叶子节点存储的并不是完整的用户记录，而只是c2列+主键这俩个列的值。
• 目录项记录中不再是主键+页号的搭配，而变为c2列+页号的搭配。

所以如果我们现在想通过c2列的值查找某些记录的话就可以使用我们刚刚建好的这个B+树了。以查找c2列的值为4的记录为列。查询过程如下：

1.确定目录项记录页

根页面，也就是页44，可以快速定位到目录项记录所在的页42（因为 2<4<9）。

2.通过目录项记录页确定用户记录真实所在的页。

页42中可以快速定位到实际存储用户记录的页，但是由于c2列并没有唯一索引约束，所以c2列值和4的记录可能分布在多个数据页中，又因为 2< 4 <=4 ，所以确定实际存储用户记录的页在页34和 页35中。

3.在真实存储用户记录的页中定位到具体的记录。

到 页34和 页35中定位到具体记录。

4.但是这个B+树的叶子节点中的记录只存储了c2 和 c1 （也就是主键）俩个列，所以我们必须再根据主键值取聚簇索引中再查找一遍完整的用户记录。

5.查询到4.索引值为多个，那么就会返回多个，并且多个会进行拿着主键进行回表操作。

非聚簇索引，它的目录页结构是和聚簇索引一致，不同点在于聚簇索引的数据页里面不仅存放了索引信息，还存放了所有行字段。

而非聚簇索引，值存放表头结构和索引，还有就是聚簇索引。没有存储其他列信息。所有会有一个回表的概念。

概念：回表 我们根据这个 c2列大小排序的 B+树只能确定我们查找记录的主键值，所以如果我们想根据c2列查出完整的用户记录的话，仍需要到 聚簇索引中查询一遍，这个过程为回表。也就是根据c2列的值查询一条完整的用户记录需要使用 2棵B+树！

问题：为什么我还需要一次回表操作呢？直接把完整的用户记录存放到叶子节点不ok吗？

答：如果将完整的记录都存放到叶子节点，那么占用的空间太大了。已经有了聚簇节点。

因为这种按照非主键列创建的B+树需要一次回表操作才可以定位到完整的用户记录，所以这种B+树也被称之为二级索引（英文名 secondary index）或者辅助索引。由于我们使用的是c2 列的大小为B+树的排列规则，所以我们也称这个B+树是为c2列创建的索引。

非聚簇索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个非聚簇索引。

3.联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让 B+树按照c2和 c3列的大小进行排序，这个包含俩层含义：

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

注意一点，以c2和c3列的大小为排序规则建立的 B+树称之为联合索引，本质上也是一个二级索引。它的意思与分别为 c2和c3列分别建立索引的表述不同的，不同点如下：

• 建立联合索引只会建立如上图一样的一棵
• 为c2和c3分别建立索引会分别为c2和c3列的大小为排序规则建立2棵B+树。

3.4 InnoDB 的 B+树索引的注意事项

根页面位置万年不动

我们前边介绍 B+树索引的时候，为了大家理解上的方便，先把存储用户记录的叶子节点都画出来，然后接着画存储目录记录的内节点。实际上B+树的形式过程是这样的；

• 每当某个表创建一个 B+数索引（聚簇索引不是人为创建，默认就有）的时候，都会为这个索引创建一个根节点 页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。
• 随后向表中插入用户记录时，先把自己记录存储到这个根节点中。
• 当根节点中的可用空间用完时接着插入数据，此时会将根节点的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值。（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会分配到页a或者页b中，而根节点便升级为存储目录项记录的页。

这个过程特变注意的是：一个B+树索引的根节点自诞生之日起，便不会移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是 InnoDB

内节点中目录项记录的唯一性

我们知道B+树索引的内节点目录项记录的内容是索引列+页号的搭配，但是这个搭配对于二级索引来说有点不严谨。还拿index_demo表为例，假设这个表中数据是这样的：

问题：如果在插入的时候，二级索引的值相同了，此时就不知道插入到那个数据页中，解决就是在目录页中不仅要添加二级索引和数据页地址还添加聚合索引信息

如果二级索引中目录记录的内容只有索引列+页号的搭配的话，那么为c2列建立索引后B+树应该长这样：

如果我们想新插入一行记录，其中c1、c2、c3的值分别为：9、1、'c'，那么在修改这个为c2列建立的二级索引对应的B+树时便碰到了一个大问题：由于页3中存储的目录项记录时有c2列+页号的值构建成的，页3中的两条目录项记录对应的 c2列的值都是1，而我们新插入的这条记录的c2列的值也为1，那么我们这条新插入的记录到底应该插入页4中，还是放到页5中，此时就出现问题了。查询的话还好，大不了全部查询而插入就出现问题了。

为了让新插入记录能找到自己在那个页里，我们需要保证B+树的同一层节点的目录项记录除页号这个字段意外唯一的。所以对于二级索引的内节点目录项记录的内容实际上是由三部分构成的：

• 索引列的值
• 主键值
• 页号

也就是我们把主键值也添加到二级索引内节点中的目录项记录了，这样就能保证B+树每一层节点中各条目录记录除页号这个字段外都是唯一的，所以我们为c2列创建二级索引后的示意图实际上应该是这样子的：

这样我们再插入记录（9,1,'c'）时，由于页3中存储的目录记录时由c2列+主键+页号的值构成的，可以先把新记录的c2列的值和页3总各目录项记录的c2列的值作比较，如果c2列的值相同的话，可以接着比较主键值，因为B+树同一层中不同目录项记录的c2列+主键的值肯定是不一样的，所以最后肯定能定位唯一的一条目录项记录，在本列中最后确定新记录应该被插入到页5中。

一个页面最少存储2条记录

一个B+树只需要很少的层级就可以轻松存储数亿条记录，查询速度相当不错！这是因为 B+树本质上就是一个大的多层级目录，每经过一个目录时都会过滤掉需要无效的子目录，直到最后访问到存储真实数据的目录。那如果一个大的目录中只存放一个子目录是啥效果呢？那就是目录层级非常非常多，而且最后那个存放真实数据的目录中只能存放一条记录。费了半天劲只能存放一条真实的用户记录？所以INnodb的一个数据页至少可以存放俩条记录

四、 MyISAM 中的索引方案

B树索引适用存储引擎如表所示：

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

即使多个存储引擎支持同一种类型的索引，但是他们的实现原理也是不同的。Innodb 和MyISAM 默认的索引是 Btree索引；而Memory 默认的索引是 Hash 索引。

MyISAM引擎使用B+Tree 作为索引结构，叶子节点的data域存放的是数据记录的地址。

因为 MyISAM的数据和索引是分别存放的.MyD .MyI 。

所有MyISAM是没有 聚簇索引的，都是非聚簇索引，需要回表，但是它的效率比较快

4.1 MyISAM索引的原理

下图是MyISAM索引的原理图。

如果我们在Col2上建立一个二级索引，则此索引的结构如下图所示：

4.2 MyISAM 与 InnoDB 对比

MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。小结两种引擎中索引的区

别：

① 在InnoDB存储引擎中，我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录，而在

MyISAM 中却需要进行一次 回表 操作，意味着MyISAM中建立的索引相当于全部都是 二级索引 。

② InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是 分离的 ，索引文件仅保存数

据记录的地址。

③ InnoDB的非聚簇索引data域存储相应记录 主键的值 ，而MyISAM索引记录的是 地址 。换句话说，

InnoDB的所有非聚簇索引都引用主键作为data域。

④ MyISAM的回表操作是十分 快速 的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通

过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。

⑤ InnoDB要求表 必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个 可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐 含字段作为主键，这个字段长度为6个字节，类型为长整型。

MyISAM的索引顺序是按照插入顺序，数据和索引是分开的，没有聚簇索引

小结：

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，比如：

举例1：知道了 InnoDB 的索引实现后，就很容易明白为什么不建议使用过长的字段为主键，因为所有二级索引都是引用主键索引，过长的索引会令二级索引变得占用内存过大。

举例2：用非单调（非唯一）的字段作为组件在Innodb中不是好主意，因为 InnoDB 数据文件本身是一颗B+Tree，非单调的主键会造成插入新记录的，数据文件为了维持 B+Tree的特性而频繁的分裂调整，是否低效率。而使用自增字段作为组件则是一个很好的选择

五、 索引的代价

索引是一个好东西，但是不能乱添加，它在内存和时间上都会有消耗。过多的索引会导致维护难度增加。

• 空间上的代价

每建立一个索引都要为他建立一棵 B+树，每一颗B+树的每一个节点都是一个数据页，一个页默认占用16kb的存储空间，一棵很大的B+数由许多数据页组成，那就是很大的一片存储空间。

• 时间上的代价

每次对表中数据进行 增、删、改操作时，都需要去修改各个 B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排列而组成一个双向链表。不论是叶子节点中记录，还是内节点中的记录（也就是不论是用户记录还是目录记录）都是按照索引列的值从小到大的顺序而形成的一个单项链表。而增、删、改 操作可能会对节点和记录排序操作破坏，所以存储引擎需要额外的时间进行记录移位，页面分裂、页面回收等操作维护号节点和记录的排序。如果我们建立需要索引，每个说个对应的B+树都要进行相关的维护，会给性能拖后腿。

一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。为了能建立又好又少的索引，我们得学学这些索引在哪些条件下起作用。

六、MySQL数据结构选择的合理性

6.1 全表遍历

全表遍历就是按照顺序进行读取数据一条条的查找。效率很慢。

6.2 Hash结构

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

Hash 算法是通过某种确定性的算法（比如 MD5、SHA1、SHA2、SHA3）将输入转变为输出。相同的输入永远可以得到相同的输出，假设输入的内容有微小偏差，在输出中通常会有不同的结果

举例：如果你想要验证俩个我呢间是否相同，那么你不需要把俩份文件直接拿过来对比，只需要让对方把 Hash 函数结果得到的结果告诉你即可，然后在本地相同文件进行 Hash 函数的运算，最后通过比较这俩个 Hash 函数的结果是否相同，就可以知道这两个文件是否相同。

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

（1）树，例如平衡二叉搜索树，查询/插入/修改/删除的平均时间复杂度都是 O(log2N)；

（2）哈希，例如HashMap，查询/插入/修改/删除的平均时间复杂度都是o(1)；（key,value）

上图中哈希函数 h 有可能将俩个不同的关键字映射到相同的为位置，这叫做哈希碰撞，在数据库中一般采用链接法来解决。在链接法，将散列到不同槽位的元素放到一个链表中，如下图所示：

实验：体会数组（全表扫描）和hash表的查找方面的效率区别

// 算法复杂度为 O(n)
@Test 
public void test1(){ 
int[] arr = new int[100000];
for(int i = 0;i < arr.length;i++){ 
    arr[i] = i + 1; 
}
long start = System.currentTimeMillis();
for(int j = 1; j<=100000;j++){ int temp = j;
for(int i = 0;i < arr.length;i++){ 
    if(temp == arr[i]){ 
        break; 
  } } 
 }
    long end = System.currentTimeMillis();
    System.out.println("time： " + (end - start)); //time： 823 
}

//算法复杂度为 O(1)
@Test public void test2(){ 
    HashSet<Integer> set = new HashSet<>(100000);
    for(int i = 0;i < 100000;i++){ 
        set.add(i + 1); 
    }
    long start = System.currentTimeMillis();
    for(int j = 1; j<=100000;j++) {
        int temp = j; boolean contains = set.contains(temp);
    }
    long end = System.currentTimeMillis(); 
    System.out.println("time： " + (end - start)); 
    //time： 5 
}

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

原因1：Hash索引仅能满足 （=） （<>）和 IN 查询，如果进行 范围查询，哈希型的索引，时间复杂度会退化为 O(n)；而树形的“有序”特性，依然能够保存O(log2N)的高效率。

原因2：Hash索引还有一个缺陷，数据的存储时没有顺序的，在ORDER BY 的情况下，使用 Hash索引还需要对数据重新排序。

原因3：对于联合索引的情况，Hash值是将联合索引键合并后一起计算的，无法对单独的一个键或几个索引键进行查询。

原因4：对于等值查询来说，通过 Hash索引的效率更高，不过也存在一种情况下，就是索引列的充值如果很多，效率就会降低。这就是因为遇到 Hash冲突时，需要遍历桶中的行指针来比较，找到查询的关键字，非常耗时。所以，Hash索引通常不会用重复值多的列上，比如性别、年龄的情况等，

原因5：Innodb提供自适应Hash

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

Hash索引的适用性：

就是将经常被检索的数据存放自适应哈希中，每次查询时都会去看是否有，如果有则直接返回，

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

我们可以通过 innodb_adaptive_hash_index

mysql> show variables like '%adaptive_hash_index';

6.3 二叉搜索树

如果我们利用二叉树作为索引结构，那么磁盘的 IO 次数和索引数的高度是相关的。

二叉搜索树的特点

• 一个节点只能有两个子节点，也就是一个节点度不能超过2
• 左子节点<本节点；右子节点>=本节点，比我大的向右，比我小的向左

查找规则

我们先来看最基础的二叉搜索树 (Binary Search Tree) ，搜索某个节点和插入节点的规则一样，我们假设搜索插入的数值为key：

1.如果key大于根节点，则在右子树进行查找；

2.如果key小于根节点，则在左子树进行查找；

3.如果key等于根节点，这就是找到了这个节点，返回根节点即可。

举个例子，我们对数列 （）进行二分法查找数如下图

但是存在特殊的情况，就是有的时候二叉树的深度非常大，此时就会成为单向链表

上面第二棵树也属于二分查找数，但是性能上已经退化成了一条链表，查找数据的时间复杂度编程了 O(N)。你能看出第一个数的深度是3，也就是说最多只需要3次比较，就可以找到节点，而第二个深度是7，需要7次I/O操作。

为了提高查询效率，就需要 减少磁盘IO数 。为了减少磁盘IO的次数，就需要尽量 降低树的高度

原来“瘦高”的树结构变的“矮胖”，树的每层的分叉越多越好。

6.4 AVL树

为了解决上面二叉查询树的退化成链表的问题，人们提出了 平衡二叉树 <Balanced Binary Tree>，又称之为 AVL 树（有别于 AVL算法），他在二叉搜索树基础上增加了约束，具有一下约束性质：

它是一颗空树或它的左右两个子树的高度差的绝对值不超过1，并且左右俩个子树都是一棵平衡二叉树。

这里说一下，常见的自平衡二叉树有很多，包括了 平衡二叉树搜索树、红黑树、树堆、延伸树。平衡二叉树搜索树是最早提出来的自平衡二叉搜索树，当我们提高平衡二叉树时，一般指的就是平衡二叉树搜索树，实际上，第一棵树就属于二叉搜索树，搜索书剑复杂度 就是 O(log2n)。

数据查询的时间主要依赖于磁盘 I/O 的次数，如果我们采用二叉树的形式，即使通过平衡二叉树进行了改进，树的深度也是 O(log2n)，当n比较大时，深度也是比较高的，比如下图所示：

每访问一次节点就需要进行一次磁盘 I/O操作，对于上面的树来说，我们需要进行5次 I/O操作。虽然平衡二叉树的效率高，但是树的深度也同样高，这意味磁盘 I/O操作次数多，会影响整体数据的查询效率。

针对同样的数据，如果我们把二叉树改成 M 叉树 （M>2）呢？当 M=3 时，同样的 31 个节点可以由下面

的三叉树来进行存储：

能看到 此时高度降低，当数据 N 大的时候，那么数据分叉数 M大的时候 ，M叉数的高度会远小于二叉树的高度(M>2)。所以，我们需要把树从"瘦高" 变“矮胖”

6.5 B-Tree

B树的英文 是 Balance Tree ，也就是 多路平衡查找树。简写为 B-Tree （注意横杠表示俩个单词连接的意思，不是减号）。它的高度远小于平衡二叉树的高度

B 树的结构如下图所示：

B树作为多路平衡查找树，它的每个节点最多可以包括 M个子节点，M成为 B树的阶 。每个磁盘中包括了关键字和子节点的指针。如果一个磁盘块中包括了 x个关键字，那么指针就是 x+1 。对于 100阶的B树来说，如果有3层的话最多可以存放 100万的索引数据。对于大量的索引数据来说，采用 B数的结构是非常核实，因为树的高度要远小于二叉树的高度。

一个 M 阶的 B 树（M>2）有以下的特性：

1. 根节点的儿子数的范围是 [2,M]。

2. 每个中间节点包含 k-1 个关键字和 k 个孩子，孩子的数量 = 关键字的数量 +1，k 的取值范围为

[ceil(M/2), M]。

3. 叶子节点包括 k-1 个关键字（叶子节点没有孩子），k 的取值范围为 [ceil(M/2), M]。

4. 假设中间节点节点的关键字为：Key[1], Key[2], …, Key[k-1]，且关键字按照升序排序，即 Key[i]

<Key[i+1]。此时 k-1 个关键字相当于划分了 k 个范围，也就是对应着 k 个指针，即为：P[1], P[2], …,P[k]，其中 P[1] 指向关键字小于 Key[1] 的子树，P[i] 指向关键字属于 (Key[i-1], Key[i]) 的子树，P[k]

指向关键字大于 Key[k-1] 的子树。

5. 所有叶子节点位于同一层。

上面那张图所表示的 B 树就是一棵 3 阶的 B 树。我们可以看下磁盘块 2，里面的关键字为（8，12），它

有 3 个孩子 (3，5)，(9，10) 和 (13，15)，你能看到 (3，5) 小于 8，(9，10) 在 8 和 12 之间，而 (13，15)

大于 12，刚好符合刚才我们给出的特征。

然后我们来看下如何用 B 树进行查找。假设我们想要 查找的关键字是 9 ，那么步骤可以分为以下几步：

1. 我们与根节点的关键字 (17，35）进行比较，9 小于 17 那么得到指针 P1；

2. 按照指针 P1 找到磁盘块 2，关键字为（8，12），因为 9 在 8 和 12 之间，所以我们得到指针 P2；

3. 按照指针 P2 找到磁盘块 6，关键字为（9，10），然后我们找到了关键字 9。

你能看出来在 B 树的搜索过程中，我们比较的次数并不少，但如果把数据读取出来然后在内存中进行比

较，这个时间就是可以忽略不计的。而读取磁盘块本身需要进行 I/O 操作，消耗的时间比在内存中进行

比较所需要的时间要多，是数据查找用时的重要因素。 B 树相比于平衡二叉树来说磁盘 I/O 操作要少 ，

在数据查询中比平衡二叉树效率要高。所以 只要树的高度足够低，IO次数足够少，就可以提高查询性能 。

小结：

1.B树在插入和删除节点的时候如果导致树不平衡，就通过自动调整节点的位置来保证树的自平衡。

2.关键字集合分布式在整棵树中，即叶子节点和非叶子节点都存放数据。搜索又能在非叶子节点结束。

3.其他搜索性能等价于 关键字全集内做一次二分法查找。（也就是说在所示某个数据，需要做全索引的搜索，从根节点到叶子节点都要查询，因为目录节点也存储了数据）

1. 它和B+树的不同，目录节点是子节点数量减一。

6.6 B+Tree

B+树也是一种多路搜索树，基于 B树做出了改进，主流的DBMAS都支持 B+树的索引方式，比如MySQL。相比于B-Tree，B+Tree更适合文件索引系统

• MySQL官网说明：

B+ 树和 B 树的差异：

1. 有 k 个孩子的节点就有 k 个关键字。也就是孩子数量 = 关键字数，而 B 树中，孩子数量 = 关键字数（查询条件）

+1。索引 / 存储引擎 MyISAM InnoDB Memory

2. 非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大（或最

小）。

3. 非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而 B 树中， 非

叶子节点既保存索引，也保存数据记录 。

4. 所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大

小从小到大顺序链接。

B+树和B树过程看上去差不多，但是有个根本上的差异在于，B+树的中间节点并不会直接存储数据，这样的好处有什么？

首先,B+树查询效率更稳定。因为 B+数每次只要访问到叶子节点才能找到对应的数据，而B树中，非叶子节点也会存储数据，这样会造成查询效率不稳定的情况，有时候会访问到非叶子节点就可以找到关键字，而有时候访问到叶子节点才能找到关键字、

其次，B+树的查询效率更高。这是因为通常B+树比 B数更矮胖（阶数更大，深度更低），查询所需要的磁盘 I/O也会更少。同样的磁盘也大小，B+树可以存储更多的节点关键字。因为 B树的目录节点存储了数据。而B+树没有。

不仅是对单个关键字的查询上，在范围上，B+树的效率也比 B树高。这是因为所有关键字都出现 B+树的叶子节点上，叶子节点之间会有指针，数据是递增的。这使得我们范围查找可以通过指针连接查找。而在B树中则需要通过 中序遍历才能完成范围的查找，效率要低很多。

B 树和 B+ 树都可以作为索引的数据结构，在 MySQL 中采用的是 B+ 树。
但B树和B+树各有自己的应用场景，不能说B+树完全比B树好，反之亦然。

思考题：为了减少IO，索引树会一次性加载吗？

1. 不会，数据索引是存储在硬盘上的，如果数据量很大，必然会导致索引的大小也会很大，超过几个G。
2. 当我们利用索引查询的时候，是不可能将全部几个G的索引都加载进内存的，我们能做的只能是：逐一加载每一个磁盘页，因为磁盘页对应着索引树的节点。

思考题：B+树的存储能力如何？为何说一般查找行记录，最多只需1~3次磁盘IO

- 这个问题首先需要知道一棵B+树的最大阶 。一般一个B+树的层阶就是3-4就最够存储所有的数据了
- InnoDB 存储引擎中页的大小为16kb，一般表的主键类型为 INT（占用4个字节）或 BIGINT（占8个字节），指针类型一般为4或8个字节，也就是说一个页(B+Tree中的一个节点)中大概可以存储16kb/(8b+8b)=1k个键值(因为是估量，为方便计算，这里的k取值为10^3。也就是说一个深度为3的B+Tree索引可以维护 10^3*10^3*10^3=10亿条记录。（这里假设一个数据页存储10^3条行记录数据了）
实际情况中每个节点可能不能填满，因此在数据库中，B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计的是是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘1/O操作。

思考题：为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？

1. B+树的磁盘读写代价更低
B+树的内部节点并没有指向关键字具体信息的指针。因此其内部节点相对B树更小。如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO读写次数也就降低了

2. B+树的查询效率更加稳定
由于非终结点并不是最终指向文件内容的节点，而只是叶子节点中关键字的索引（也就是执行数据页的地址）。所以任何关键字的查找必须走一条从根节点到叶子节点的路。所有关键字查询的路径长度相同，导致每个数据的效率相当。（因为都在子节点操作，IO的次数相同，然后在具体二分法查找）

思考题：Hash 索引与 B+ 树索引的区别

我们之前讲到过 B+树 索引的结构，Hash索引结构和 B+树的结构不同，因此在索引使用也会有差别。

1. Hash索引 `不能进行范围查询` ，而B+树可以。这是因为 Hash索引指向的数据是无序的。而B+树的叶子节点是一个有序的链表。

2. Hash索引 `不支持联合索引的最左原则` (即联合索引的部分索引无法使用)，而 B+树可以，对于联合索引来说，Hash索引在计算 `Hash`值的时候是将索引键合并后在计算 Hash值，所以不会针对每个索引单独计算Hash值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。

3. Hash索引 `不支持ORDER BY 排序`，因为 Hash索引指向的数据是无序的，不能无法起到排序优化的作用，而B+树的索引数据是有序的。可以起到对字段的ORDER BY排序优化的作用。同理，我们也无法用 Hash索引进行 `模糊查询`，而B+树使用 LIKE 进行模糊查询的时候，LIKE后面模糊查询(比如%结尾)的话就可以起到优化作用。

4. `InnoDB`不支持哈希索引。