Mysql深度讲解InnoDB引擎与Index索引（二）

最新推荐文章于 2024-04-28 17:52:42 发布

Smallc0de

最新推荐文章于 2024-04-28 17:52:42 发布

阅读量647

点赞数

分类专栏： Mysql 数据结构

本文链接：https://blog.csdn.net/Smallc0de/article/details/110955641

版权

数据结构同时被 2 个专栏收录

15 篇文章 2 订阅

订阅专栏

Mysql

14 篇文章 4 订阅

订阅专栏

前言

上一篇【Mysql深度讲解InnoDB引擎与Index索引（一）】已经详细说了InnoDB引擎的结构以及存储数据的模型，结束于Dynamic处理行溢出方式。那么本篇正式从零开始详细的说下Index是如何产生的。以及为什么说InnoDB中的Index用的是B+树数据结构。更多Mysql调优内容请点击【Mysql优化-深度讲解系列目录】。

InnoDB与MyISAM

首先先对InnoDB与MyISAM区别做一个简单的对比，MyISAM是一个已经过时的引擎(Mysql5.5后被InnoDB替代)，所以不多说。这里仅仅对它俩的表现举个例子。

先创建分别用两个引擎创建两个表，然后插入数据。
create table t1(
a int primary key,
b int,
c int,
d int,
e varchar(20)
) engine=InnoDB;

insert into t1 values(12,1,2,4,'a');
insert into t1 values(6,4,5,4,'b');
insert into t1 values(9,1,1,1,'c');
insert into t1 values(1,6,7,4,'d');
insert into t1 values(15,2,2,5,'e');
insert into t1 values(7,9,3,6,'f');
insert into t1 values(4,2,1,7,'g');
insert into t1 values(3,3,3,3,'h');

create table t2(
a int primary key,
b int,
c int,
d int,
e varchar(20)
) engine=MyISAM;

insert into t2 values(12,1,2,4,'a');
insert into t2 values(6,4,5,4,'b');
insert into t2 values(9,1,1,1,'c');
insert into t2 values(1,6,7,4,'d');
insert into t2 values(15,2,2,5,'e');
insert into t2 values(7,9,3,6,'f');
insert into t2 values(4,2,1,7,'g');
insert into t2 values(3,3,3,3,'h');

插入相同的数据(包括顺序也相同)，之后分别对t1和t2进行全表查询 select * from t1和 select * from t2结果如下：

InnoDB：
在这里插入图片描述
MyISAM：

从上面两个查询结果来看，有一个很直观的感受：InnoDB对数据根据主键做了一个排序再返回出来。MyISAM没有做排序直接返回了出来，查询顺序和插入顺序一致。那么可以说两个存储引擎对于数据的存储方式是不同的，对于InnoDB的这种方式一般称之为聚集，而MyISAM这种一般称为堆表。那么大胆猜测一下InnoDB在存储的时候会对数据进行一个排序。

模拟InnoDB的存储

既然我们猜测InnoDB会对数据进行排序，而且查询出来的时候也是按照顺序结构返回的，那么就应该还有一个指针从小到大把主键串起来，还是按照上面的数据为例。
在这里插入图片描述

虽然查询的时候可以很方便的按照顺序查出来，但是插入的时候就会对性能造成影响，因此InnoDB在插入的时候应该尽量做到按照主键从小到大的顺序插入，这样可以避免掉排序对插入操作性能的影响。那再说排序有什么用，数据存到表里一定是有意义的，不可能只是为了存储而存储。可以想这样一个场景：在一串杂乱的数据中，查找某一个具体的数，无论是什么算法，要提高查询效率，第一件事情必然是排序，然后才会应用其他算法进行查找，比如二分法之类的算法进行效率提高。

排序还有另外一个好处。比如要查主键为2(a=2)的数据，因为是排好序的，只要查到3就不需要再往下查了，因为下面的一定比3还大，a=2不存在。只需要比较两次就可以返回结果。如果没有排序，要把整个表都比较一遍，查询效率谁自然高一目了然。因此InnoDB在插入数据的同时做好了排序，就为后面应用索引(index)做了一个很好的基础。

页目录优化查询

不管怎么说，我们猜出来的结构本质上来说还是一个链表，链表的查询效率还是很低的。对于一页中有很多数据行的情况下怎么提高查询效率呢？那么就要提到上篇说的页结构里面的页目录(Page Directory)了。一般来说目录就是用来记录页码的，InnoDB里也是一样。在InnoDB的页目录里存的就是数据的页码。根据上面说的t1中插入的n条数据，按照两两一组在页里分开，然后在页目录里面记录每一组最小的主键值(页码)。由于页目录里面的数据是相邻的可以看作一个数组，由于写入的数据是按照主键递增的，那么页目录就形成了一个顺序递增的数组，具体结构如下。注：这里面的10、20是举例子随便写的。
在这里插入图片描述

如果现在要找主键等于6(a=6)的数据，在这个模型下要怎么找呢？首先去找Page Directory里面找页码，通过比较6>1所以肯定不在组1里；然后比较可知6在4和10之间，那就有可能在组2里面，就直接取组2里面遍历拿到数据。这样就省去了遍历组1的消耗，因此页目录的功能就可以提高查询效率。要注意：上面说的”页码”和图里的页号是两回事。到此需要查找某一个数据是看要去哪一个组查找，既然已经把数据抽象为组，那么就可以进一步去优化查询算法，比如最容易想到算法：用二分法查询。虽然InnoDB不是使用的二分法，但是通过分析，有理由相信InnoDB也一定做了类似的事情。

提取页目录

继续进一步假设：如果表里面的数据很多，一个页面已经放不下了。这个时候就需要开辟新的一页去存放表里其他的数据，当然存放规则肯定还是一样的。假设一页只能存四条数据，我们之前插入的8条数据就要分为两页了。注意：虽然页目录里面的数据是相邻的，但是总体上数据并不一定会被分配到相邻的页面里，所以页号不一定相邻。因此上篇说的文件指针(File Trailer) 就为两个页之间的链接提供了保障。
在这里插入图片描述

如果说现在要找主键为9(a=9)的数据，那么首先需要确定是哪一页，进入页面以后才能进一步根据页目录去寻找数据在哪一组。因此为了方便的寻找页面，可以很容易想到：也针对页面做一个目录。所以我们重新构建一个页面专门用来存放页面的目录。
在这里插入图片描述

可以看到这个目录其实就是把某一页里面主键的最小值和页号连起来做一个类似于Map的<Key,Value>结构，key也是从小到大顺序排放的。有了这样一个额外的结构以后，再去查询的时候，就可以先按照主键查询属于哪一页然后再进行分组遍历查询。当查询主键为9(a=9) 时，只需要比较Key的值，就可以知道第100页没有这个数据，至少是在第107页里面，直接进入107查询就可以了，这样就又省去了一个页面的查询消耗。这种结构其实也是另外一种目录结构，只不过对应的不是数据内容而是页面号码。

抽象结构B+树

既然已经对结构做了这么多优化，可以看到画的图越来越复杂，那么不妨抽象一下这个图。页面的内容我们直接抽象为数据，目录页保持不变，最终抽象为这样。
在这里插入图片描述

不知道大家对这个结构是不是有些眼熟，像不像一棵树？相信有些熟悉数据结构的同学会比较块的认出来这个结构，是不是就是一棵B+树呢？标准的B+树长什么样子呢？B+树最大的特征就是每个节点可以存多个数据，并且每个叶子节点都是用指针连起来的。
在这里插入图片描述

对比下，是不是一模一样。我们的目录里也存了多个元素，而数据页包括内部也都是连起来的，就是一个B+树模型。这里也就说明了为什么经常说InnoDB里创建索引(index)会建立一个B+树数据结构。通过我们的推演可以发现，创建B+树这种数据结构，就是为了提高我们查询数据的效率。

真实的InnoDB

上面说完了模拟，那么真实的InnoDB里面是不是这样的呢？基本上是一样的，但是有一个关键点是我们模拟的例子中没有的，那就是入口页。既然要查询数据，而且是按照指针查询数据，那么必须有一个固定入口。InnoDB是这么做的：当创建一个表的时候，InnoDB首先会开辟一个页(第一页)，当插入数据的时候，就会放到这个第一页当中，直到这个页面被写满数据。一旦数据被写满了，就需要开辟第二页去写入新的数据。InnoDB此时会先复制第一页，然后再去开辟第二页，把放不下的数据放到第二页中。之后把最初的第一页改为目录页。通过这样的方法，就把表的入口给固定下来，形成了一个根页，这样每次查数据都会有一个总的入口。以此类推，即便目录页满了，也不用担心开辟新出来的新页面会更换入口页。
在这里插入图片描述

总结

就像本文一开始说的，无论做什么样的查询，首先要做的就是排序，有了排序才有应用各种查询算法提高查询效率的可能，无论是B+树、B树、或者二叉树，首先要做的事情就是去想办法给数据排序。形成这样一个树(无论是什么样的树)以后，最终数据形成的叶子节点都会是一个递增的数列。回到存储数据的B+树，每个叶子节点就相当于存储了整行数据，通过指针连接起来的所有叶子节点就相当于整个表的所有数据。所以我们执行Select *的时候就相当于从第一个叶子节点一直往下读就可以了，极大的提高了查询的效率。这个逻辑就是InnoDB构建数据排序，并且使用B+树作为索引优化查询方案的原因。那么下一篇【Mysql深度讲解InnoDB引擎与Index索引（三）】将会说下Index的使用以及优化。