Mysql索引B+树

Mysql中索引的结构？
为什么选择B+树作为索引结构？
Mysql查询如何优化？

相信大家在面试的时候会经常被问到Mysql索引相关的问题，由于本人最近也在准备面试，于是将学习到的索引相关只是记录下来，以便后面温习用到。

Mysql的索引结构

数据页结构

由于页是InnoDB管理存储空间的基本单位，即表中的记录就是存放在页上，所以在这里有必要先了解一下页结构；一个页的大小一般有16KB,InnoDB为了不同的目的设计了多种不同类型的页，包含存放表中记录的页（索引页或数据页）、存放表空间头部信息的页、存放Change Buffer信息的页、存放INODE信息的页、存放undo日志信息的页；而这里即将要说到索引就是用的数据页结构，所以这里围绕数据页展开说说，以下是数据页结构：

从数据页结构可以看出，一个数据页的存储空间大致被分为7个部分，各个部分占用字节是不一样，有些是确定的有些不确定；然而我们的记录存放位置其实只用到了其中两个部分，分为Infimum + Supremum 和 User Records 两个部分，其中Infimum + Supremum是两条虚拟的记录，而且是InnoDB默认创建的记录,为了与用户自己插入的记录进行区分，不把他们存放在页的User Records部分；User Records自然就是存放插入的数据。

页中的记录

从页的结构中可以看出，我们插入的记录会按照指定的行格式存储到User Records部分，但是一开始生成页的时候，其实没有User Records部分，每当插入一条记录是，都会从Free Space部分（也就是尚未使用的存储空间）申请一个记录大小的空间，并将这个空间划分到User Records部分。当Free Space部分的空间全部被User Records部分替换掉之后，也就意味着这个页使用完了。此时如果还有新的记录插入，就需要申请新的页了。

从插入记录数据的结构来看，User Records中的记录时紧密相连的，通过记录其中记录头信息中next_record属性形成了单向链表；

通过分析InnoDB中页的结构和记录的插入结构，我们可以看出查询一条记录的时候，首先是找到记录的所在的页，然后通过页中记录链表进行查询，然而记录也并不是靠着单向链表进行依次遍历直到找到记录，而是会把记录通过分组（类似跳表的结构），在查询的时候就可以通过二分查找确定记录所在的槽位置，提高效率。

B+树

之所以要讲到页的结构和存储记录格式，是因为接下来要说的B+树跟页和其中的记录息息相关，我们通过下图来了解一下B+树的结构：

观察图片，发现真正存放记录的都放在最底层的节点上，这些节点成为叶子节点或叶节点，其余用来存放目录项记录的节点称为非叶子节点或内节点，最上边的节点也成为根节点，这种数据结构的名称就是我们常说的B+树。通过数据页编制的目录就叫做索引;

现在以查找主键为20的记录为例：

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

接下来，我们通过插入一条新的记录，观察B+树是如何变化（假设数据页都已经占满16KB）:

由于产生存储目录项的页会越来越多，所以我们要为这些存储目录项记录的页再生成一个更高级的目录，就像是一个多级目录一样，大目录里嵌套小目录，小目录里才是实际的数据。假设存放用户记录的数据页可以存放100条记录，存放目录记录的数据也可以存储1000条目录项记录，那么：

如果B+树只有1层，最多能存放 100条记录；
如果B+树有2层，最多能存放 100 * 1000 = 100000 条用户记录；
如果B+树有3层，最多能存放 100 * 1000 * 1000 = 100000000 条用户记录；
如果B+树有4层，最多能存放 100 * 1000 * 1000 * 1000 = 100000000000 条用户记录

当B+树达到4层的时候，一个表中的数据已经大到你想都不不敢想，所以B+树通常不会超过4层。

聚簇索引、二级索引、联合索引

聚簇索引

通过上面的分析，B+树就是一个目录，或者说是一个索引，他有下面两个特点：

使用记录主键值的大小进行记录和页的排序
B+树的叶子节点存储的完整的用户记录，所谓完整的用户记录，就是指这个记录中存储了所有列的值（包含隐藏列）。

我们把具有这两个特点的B+树成为聚簇索引；聚簇索引不需要显示使用INDEX语句去创建，InnoDB存储引擎会自动为我们创建聚簇索引；在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有用户记录都存储在了叶子节点），也就是所谓的“索引即数据，数据即索引”。

二级索引

聚簇索引只能在搜索条件是主键值时才能发挥作用，所以我们查询非主键列的时候，可以新建一棵B+树，也就是我们吉经常说的二级索引（辅助索引），它的特点如下：

使用某一列如：c2（创建索引指定的列）的大小进行记录和页的排序；
B+树的叶子节点存储的并不是完整的用户记录，而只是某一列如:c2 + 主键这两个列的值；
目录项记录中不再是主键 + 页号的搭配，而变成了某一列如： c2 + 页号的搭配；

所以我们在查找用户记录的时候，并不能在二级索引中查询到全部用户记录，有时候需要到聚簇索引中查询，也就是我们所说的回表查询（尽量避免，查询只有存在二级索引中的列）。

联合索引

如果由多个列组成的二级索引，成为联合索引，也成为复合索引和多列索引；如：以 c2 和 c3列创建联合索引：

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

我们回到一开始讲到的三个问题，通过知识回顾，发现可以很轻松的解决上面三个问题：

Mysql中索引的结构？

Mysql采用的索引结构就是 B+树

为什么选择B+树作为索引结构？

B+树在确保层级结构很少的情况下，就可以存放很多条用户记录
相对于hash索引，B+树支持范围查询；hash索引没办法利用索引完成排序，也没办法完成like查询；hash索引没办法完成联合索引，还会导致哈希碰撞
相对于红黑树，B+树节点存储的信息更多，树的高度更低，树的检索速度更快

Mysql查询如何优化? （这篇文章没有提及，后面会继续补上）

只为用于搜索、排序或分组的列创建索引；
当列中不重复值的个数在总记录条数中的占比很多时，才为列建立索引；
索引列的类型尽量小；
可以只为索引列前缀进行索引，以减少索引占用的存储空间；
尽量使用覆盖索引进行查询，避免回表操作导致性能损耗；
让索引列以列名的形式单独出现在搜索条件中；
为了尽可能少的让聚簇索引发生页面分裂的情况，最好让主键拥有AUTO_INCREMENT属性；
定位并删除表中冗余和重复索引；

由于整篇文章是在学习完《Mysql是怎样运行的——从根儿上理解Mysql》一书之后总结出来的，所以如果有写的不好不全面的，或者有疑问的，可以交流一下，大佬轻一点

最后，祝大家工作顺利，日富一日，早日退休，哈哈哈哈哈哈