mysql 数组 索引,Mysql索引总结

本文针对InnoDB索引总结

总结于：掘金小册 MySQL 是怎样运行的：从根儿上理解 MySQL https://juejin.im/book/5bffcbc9f265da614b11b731

作者：小孩子4919

数据页结构分析

InnoDB存储引擎会把表数据存储到磁盘上，当想对数据操作时需要把磁盘上的数据加载到内存中来，为了提高效率，InnoDB不会一条一条的加载，它会以页(16KB)为单位读取磁盘上的数据到内存中。页是InnoDB进行内存和硬盘交互的基本单位

在每个页中数据是是怎么存放的？(其实页有很多类型，这里指存放真实数据的数据页)

除了每个页中的用户真实数据外，数据页中还会自己生成两个虚拟的记录 一个最大记录一个最小记录

这些记录会以主键的顺序从小到大链接在一起形成一个单链表，每行记录信息中的next_record 代表下一行记录地址的偏移量，可以认为这个next_record指向着下一行记录

最小记录的next_record指向着用户数据的第一条记录也就是主键值最小的那一条记录。

用户数据的最后一条记录也就是主键值最大的那一条记录的next_record指向着最大记录

如图所示 来源：MySQL 是怎样运行的：从根儿上理解 MySQL ：

现在看的话如果我们想在这个页中找到主键值为4的记录，我们必须从最小记录开始，一行一行遍历这个单链表直到找到这个主键为4的记录或者找到第一个主键值比4大的那行记录就可以放弃查找了。

这样找的话是不是太慢了？如果当前页的数据有很多，这样的查找速度是相当慢的。

InnoDB会为这些数据分组，然后组成一个目录方便我们查找 具体如下：

假设当前页没有存放真实数据，默认会有两条记录一个最小记录一个最大记录

那么InnoDB首先会把这些数据分为两组，并把每个组内的最后一条记录的地址偏移量顺序提取出来放在页的尾部

形成2个槽。

最小记录自己形成一个组，最大记录自己形成一组，页的尾部有2个槽，分别指向他们

每个分组中的记录条数是有规定的： 对于最⼩记录所在的分组只能有 1 条记录，最⼤记录所在的分组拥 有的记录条数只能在 1 ~ 8条之间，剩下的分组中记录的条数范围只 能在是 4 ~ 8 条之间

所以现在当我们插入数据，都会被分在以最大记录为最后一条记录的那个组中，直到这个组内的数据到达8条

如果往一个记录已经到达8条的组中添加数据，InnoDB会将组中 的记录拆分成两个组，⼀个组中4条记录，另⼀个5条记录。然后新添加一个槽指向那4条记录中最大的那条记录

之后每插⼊⼀条记录，都会从⻚⽬录中找到主键值⽐本记录的 主键值⼤并且差值最⼩的槽进行插入，

注意：每个分组内的数据以主键顺序从小到达存放，每个槽指向每个组内的最后一条数据也就是最大的那条记录。

假设此时这个页中的数据有18条，那么具体结构如图所示 来源：MySQL 是怎样运行的：从根儿上理解 MySQL ：

有了这个目录之后，此时我们进行查找一个主键值为10的数据是这样操作的：

对这4个槽进行2分查找：

先定位到槽2 槽2指向的记录的主键值为8 10比8大

继续二分，定位到槽3，槽3指向记录的主键值为12 10比12小

确定要找的记录在槽3，此时只需要遍历槽3所在的分组

因为每个槽指向的是所在分组的最大的那条记录，我们可以找到槽2指向的那条记录 然后一个个遍历找到我们所需要的结果。

这时可以总结一波

页是InnoDB进行内存和硬盘交互的基本单位，一页大小为1 6 KB，在⼀般情况下，⼀次最少从磁盘

中读取16KB的内容到内存中，⼀次最少把内存中的16KB内容刷新到 磁盘中。

在页中以主键查找数据时分为2步

需要经过二分查找找到数据所对应的槽，并找到槽内最小的那条记录

通过记录的next_record属性遍历单链表找到所需要的那条记录

注意前提!!！必须以主键为条件查找，因为页内的数据是以主键从小到大排列的，形成的目录也是以每个组的最后一条记录的主键大小排列的,如果以其他列为条件进行搜索，那很不幸，必须遍历整个单链表

索引分析

在InnoDB中每个数据页都有2个属性一个上一页一个下一页，这些数据页连接起来形成了一个双向链表，他们在物理上是不连续的 因为没有那么大的连续存储空间。

如图所示 来源：MySQL 是怎样运行的：从根儿上理解 MySQL：

此时此刻我们已经知道在单个页中可以快速找到我们所需要的数据，但是又如何找到数据所在的页呢？

如果不采取任何优化措施，我们就必须遍历整个双向链表去查找数据所在的页，然后在页中通过目录查找数据，这个查询过程是相当的慢的，相当于全表扫描

那怎么优化呢？

可以参考在页中根据主键值快速定位⼀条记录在⻚中的位置⽽设 ⽴的⻚⽬录呀，我们也可以为每个页建一个目录，让我们快速找到数据所在的页

这个目录怎么建呢?

初步思路是这样的：

为这些页建立一个目录，将每个页的最小记录提取出来当作目录项 比如：

将页10的最小记录 主键值1 提取出来

将页28的最小记录主键值 5提取出来

将页9的最小记录 主键值12 提取出来

将页20的最小记录 主键值 209提取出来

为了找到所在的页 目录项还包含主键值所在的页号

这些目录项在物理上是连续的 我们把它放在一个数组中方便二分查找

如图所示：来源：MySQL 是怎样运行的：从根儿上理解 MySQL：

这样的话想找到数据所在的页是不是方便很多了呀

比如此时想找到主键为8的那条记录

只需要通过二分法确定到目录项2 然后定位到页28 然后在这个数据页中通过目录查找数据

但是这里有一个问题，当我们的数据页有很多时，我们需要生成很多目录项，这些目录项是连续存储的 我们根本没有这么多连续的空间存储。

InnoDB会生成一个存储目录项的页，这个页和我们存储真实数据的页并没有什么不同，只不过存储的是目录项记录。

存储目录项记录的页中 也有和存储真实数据页一样的槽 来方便我们在页中找到目录项记录

目录项记录包含主键值和页号，方便定位到指定页号主键值最小的那条记录

真实记录包含所有列的值

这些目录项和真实数据一样以页为单位来存储，多个存储目录项的页也是通过双向链表连接起来的

此时上图变成了这样 来源：MySQL 是怎样运行的：从根儿上理解 MySQL：

此时想象一种情况，当最底层存储真实数据的页越来越多，越来越多，上一层存储目录项的页肯定也会越来越多

此时我们为了找到符合条件的目录项 还不是要遍历存储目录项的页所形成的双向链表？

你傻啊！ 再为这一层形成一个目录呗！，一个目录不够就2个 再不够就3个

如图所示： 来源：MySQL 是怎样运行的：从根儿上理解 MySQL：

其实这就是一颗B+树

InnoDB存储引擎索引的实现方式就是B+树

其实这个B+树的高度最多也就3、4层，因为这个B+树的每个结点都相当于一个数据页，每个页中的数据其实是可以存放很多的，假设一个结点中可以存1000条数据，那么4层的B+树就可以存放很多个亿的数据了！

这样的实现方式极大的减少了访问磁盘的次数，因为InnoDB是以页为单位从磁盘加载数据到内存中的，

假设有4层结点(3层目录项结点，最后一层是存储真实数据的结点)，那我们只需要访问4次磁盘就可以找到我们所需要的数据，极大的降低了IO成本！

假设一个结点只能存放2条数据，相当于二叉树，那你想想看，你要访问几次数据页，相当于访问几次磁盘！！

注意！形成这颗B+树必须有几个前提！

⻚内的记录是按照主键的⼤⼩顺序排成⼀个单向链表。

各个存放⽤户记录的⻚也是根据⻚中⽤户记录的主键⼤⼩ 顺序排成⼀个双向链表

存放⽬录项记录的⻚分为不同的层次，在同⼀层次中的⻚ 也是根据⻚中⽬录项记录的主键⼤⼩顺序排成⼀个双向链 表。

这3个方面都必须是有序的 不然 你仔细想想怎么生成目录进行查找 不过不一定非得是按照主键进行排序：

主键索引

上面这颗以主键进行排序形成的B+树 叫做主键索引 也叫做聚簇索引。

当我们创建表时 Mysql会自动为主键生成一个主键索引，主键索引的特点是：

叶子结点上存储的是整行数据

非叶子结点存储不是真实数据，而是一个主键值和页号 用来指向下一层的结点

最上面3点都是以主键进行排序的

当我们对只有一个主键索引的表进行查询时，只有通过主键进行条件查询，这个索引才会查询，因为这个B+树是按照主键大小的顺序进行生成的！

二级索引

如果想以别的列进行查询而且想利用索引，我们需要手动指定一个索引列，这种索引叫做二级索引

特点如下：

叶子结点上存储的是索引列的值和主键值

非叶子结点存储不是真实数据，而是索引列的值和主键值还有页号 用来指向下一层的结点

最上面3点都是以索引列的值进行排序的

标签：总结,记录,目录,索引,键值,Mysql,数据,主键

来源： https://blog.csdn.net/wmh1152151276/article/details/90488623