mysql数据结构和索引

MySQL的索引是一个非常重要的知识点，也基本上是面试必考的一个技术点，所以非常重要。那你了解MySQL索引的数据结构是怎么样的吗？为什么要采用这样的数据结构？

现在化身为MySQL的架构师，一步步迭代设计出MySQL的索引结构，保证你再也忘记不了索引的结构了，轻松通过面试。

索引介绍

MySQL表中存储的数据量非常大，可能有上亿条记录，如果一条条去匹配，就是所谓的全表扫描，会非常的慢。那么有什么办法呢？

想想我们生活中的例子，比如新华字典，我们有一个目录，目录根据拼音排序，内容包含了汉字位于字典中具体的的页码。聪明的你肯定也想到了，我们也可以借鉴这种思想，建立一个MySQL的目录，叫做“索引”。

所以你对“索引”做了抽象和定义：索引（Index）是帮助MySQL高效获取数据的数据结构。

索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，MySQL有InnoDB、MyISAM、Memory等存储引擎，你想了下，就拿最常用的InnoDB作为存储引擎设计索引。

索引设计目标

你现在拼命转动大脑，开始去思考如何设计出这样的一个索引结构。你就在脑子里想，索引设计中需要解决哪些问题，以及要达成什么样的目标。

我要怎么样才能在索引目录（数据结构）中快速找到具体的某条数据记录呢？那么这个数据结构需要有顺序规律，我按照这个规律就可以定位到具体的某条数据。MySQL中的数据中的记录如何能够快速找到呢？是不是可以将记录进行排序，然后根据 二分法 快速找到对应的数据记录。MySQL中架构老大一开始定义数据是按照数据页存放的，每个数据页默认是16kb, 每次满了，就会重新有新的一页。我的索引目录数据应该也是放到页中，而且索引的数据尽量少些，这样每页可以放更多的目录信息。我怎么样才能查询效率最高呢？其实每次慢都是慢在磁盘IO上，我再后面设计中一定要减少磁盘IO的访问，越少访问磁盘IO越好。磁盘中的空间还是不连续的啊，那我还得有个指针去连接下一条记录的位置。

带着这些问题和思考，你开始设计啦。

索引设计迭代

你想着我就拿一个例子具象化的思考设计索引。

下面是一个新建的表：

CREATE TABLE demo(c1 INT,c2 INT,c3 ｃｈａｒ(1),PRIMARY KEY(c1)) ROW_FORMAT = Compact;

行记录的格式简化如下：

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

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

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

注意：一页可以存放16kb的数据，并不是图上的3条数据，这里只是一个示例。

迭代一

我们为什么要遍历所有的数据页或者记录？因为各个页中的记录并没有规律，不知道这条数据出现在哪个数据页中。那么如何快速定位要查找的数据在哪个数据页中呢？我们需要建立一定的规律，如下：

1.下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。

页中的数据根据主键按顺序排序不同页中的数据，下一页数据大于上一页数据新分配的数据页编号可能并不是连续的。它们只是通过维护者上一个页和下一个页的编号而建立了 链表 关系

2.给所有的页建立一个目录项

key表示目录中最小的主键值。page_on表示对应的页码。

查找主键值为 20 的记录，具体查找过程分两步：

先从目录项中根据二分法快速确定出主键值为 20 的记录在 目录项3 中（因为 12 < 20 < 209 ），它对应的页是页9。再根据前边说的在页中查找记录的方式去页9中定位具体的记录。

迭代二

迭代一中的目录项是怎么存储的呢？我们是不是也可以用行记录格式存储到数据页中呢。答案是肯定的，我们通过行记录格式中的record_type等于1表示是目录记录，如下图所示：

目录项记录的 record_type 值是1，而 普通用户记录的 record_type 值是0。目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列 ，另外还有InnoDB自己添加的隐藏列。

现在以查找主键为 20 的记录为例，根据某个主键值去查找记录的步骤就可以大致拆分成下边两步：

先到存储目录项记录的页，也就是页30中通过二分法快速定位到对应目录项，因为 12 < 20 < 209 ，所以定位到对应的记录所在的页就是页9。再到存储用户记录的页9中根据二分法快速定位到主键值为20的用户记录。

迭代三

随着数据量变多，势必一个目录项存放不下，因为一页只有16kb大小，就会分裂出多页，如下图所示：

那么现在查找主键值为 20 的记录，流程如下：

我们现在的存储目录项记录的页有两个，即页30和页32 ，又因为页30表示的目录项的主键值的 范围是 [1, 320) ，页32表示的目录项的主键值不小于 320 ，所以主键值为 20 的记录对应的目录项记录在页30中。通过目录项记录页确定用户记录真实所在的页。在真实存储用户记录的页中定位到具体的记录。

迭代四

如果我们表中的数据非常多则会产生很多存储目录项记录的页，如果直接这么查，也是很慢，我们是不是可以针对目录项记录的页再生成一个更高级的目录，就像是一个多级目录一样，如下图所示：

那么现在查找主键值为 20 的记录，流程如下：

生成了一个存储更高级目录项的页33，这个页中的两条记录分别代表页30和页32, 主键20的记录在 [1, 320) 之间，则到页30中查找更详细的目录项记录。在页30中通过二分法查找主键为20记录的用户记录页码。在真实存储用户记录的页中定位到具体的记录。

迭代小结

以上这个数据结构就是我们索引最终的数据结构，B+树， 图形描述如下：

所有的叶子节点存放全量的用户记录信息，包含所有的字段。所有的目录节点只存放索引字段、主键以及对应的页码信息，要求信息越少越好，因为一页最多16kb，只有目录信息越少，每页存放的信息越多，树的层级就越小，树的层级越小，那么和磁盘的IO就越少，查询就会越快。一般来说，B+树4层，就可以存放上亿数据了。索引结构总结

聚簇索引

我们按照前面的迭代推演出了基于主键的索引结构，是一颗B+树，我们把这种索引叫做聚簇索引。

特点：

聚簇索引中的叶子节点存放了用户记录的全部数据，它就是innoDB中数据存放的格式，即数据即聚簇索引，聚簇索引即数据，这也是聚簇索引名字的由来吧，数据和索引聚集在一起。InnoDB要求表必须有主键。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐 含字段作为主键，这个字段长度为6个字节，类型为长整型，这样始终就会有一个聚簇索引。

非聚簇索引

既然有了聚簇索引，那么肯定有非聚簇索引，非聚簇索引也叫二级索引或者辅助索引。

它是在什么场景出现的呢？比如我们想以别的列作为搜索条件，总不能是从头到尾沿着链表依次遍历记录一遍，肯定要慢死了。这时候就需要建立非聚簇索引，那它的索引结构和聚簇索引有什么区别呢？

索引目录的内容由3部分组成，索引列的值+主键值+页码，通过索引列的值+主键值唯一确定新插入的列是在哪个页中，也可以唯一确定从那个页中查询。索引的叶子节点存放内容为索引列的值+主键值。

那可能你有疑问了，只有主键值，我要查记录的其他信息怎么办呢？

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

回表的过程会耗时，为什么不直接存放所有的数据记录呢？

如果把完整的用户记录放到叶子结点是可以不用回表。但是太占地方了，相当于每建立一课B+树都需要把所有的用户记录再都拷贝一遍，这就有点太浪费存储空间了。

联合索引

联合索引是一种特殊的非聚簇索引，那么它的数据结构又是怎么样的呢？

比方说我们想让B+树按照c2和c3列的大小进行排序，为c2和c3建立的索引的示意图如下：

每条目录项都有c2、c3、主键、页号这4个部分组成，各条记录先按照c2列的值进行排序，如果记录的c2列相同，则按照c3列的值进行排序B+树叶子节点处的用户记录由c2、c3和主键c1列组成。索引优点和缺点

我们在了解了索引的数据结构以后，就更加明白索引的优缺点了。

优点

提高数据查询的效率，降低数据库的IO成本。通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

缺点

创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。索引中的数据都是有序的，比如插入一条主键较小的数据，势必导致其他数据进行移动，页码发生调整，这种现象也叫做页分裂，这也是为什么推荐主键要求自增。总结

本为让你亲身作为一个MySQL架构师的身份，一步步带你理解MySQL中索引的数据结构，现在是不是理解的很透彻了，如果对你有帮助的话，请留下一个赞吧。

作者：JAVA旭阳链接：https://juejin.cn/post/7162553989861474317来源：稀土掘金