索引的简单理解

五星颠勺大厨

已于 2023-07-30 03:29:41 修改

阅读量141

点赞数

文章标签：数据结构 java 算法

于 2023-07-30 03:27:34 首次发布

本文链接：https://blog.csdn.net/qq_53972532/article/details/132002825

版权

MySQL中索引的简单理解

在开始聊什么是索引前我们先从在数据库存一条数据开始说。然后会提到聚簇索引、二级索引、联合索引等等内容的简单理解。

一、lnnoDB

lnnoDB是将我们表中的数据存在磁盘的的存储引擎，有很多的存储引擎，我们MySQL是将lnnoDB作为默认的存储引擎的。lnnoDB读取数据是将数据分成一页一页的，每页的大小是16KB的。这个知道就可以了。

二、表的结构

我们先建立一个表，一个表中的数据都是从一行数据开始的，当我们的数据增加后，会形成组，一个组是有4~8行的数据所存在的，当我们继续增加数据就会形成页，一页的大小是16KB的。

我们从一行数据开始详细的了解，想直接看什么是索引的话可以直接跳到后面自行查看。

1）lnnoDB行的格式

lnnoDB引擎是有不同的类型的行格式的。有 Compact 、 Redundant 、 Dynamic 和 Compressed行格式，它的格式都差不多，我们用Compact 来举例。

它是分成了我们能看见的真实数据和额外信息的。大家看下就可以了，详细的信息可以自己去查阅了解。

2）分组

这里规定了：对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。当我们存入18条记录时如图：

图中可以看出，每四条数据形成一组，包括最小记录和最大记录一共是18条记录。一个被分成了5个组，每个槽都是按顺序排列的，标号是0、1、2、3、4。当我们要搜索想要的数据时，如果是组内查找就用线性查找就可以了，那如果是在整页里查找数据的话，就要对槽用二分查找的方式进行查找。这里举个例子假设我们想要找主键为10的这条数据：

1.中间槽对应的主键：（0+4）/2=2，槽2对应了主键8，8<10。

2.重新计算中间的主键：（2+4）/2=3，槽3对应了主键12，10<12。

3.这个时候我们可以确定我们要的主键10是锁定在了槽3，然后我们对槽3进行线性查找就可以找到我们要的数据了。

三、B+树索引

我们一张表如果数据有很多的话，就会有很多页，页与页之间是双向链表的形式，每个页中的主键都是按照从小到大排列的组成的单向链表。如图：

每一页的示意图：

页号只是个标记没有其它的意义。

当我们往里面往输入数据如图：

假设一页数据我们最多存放三条数据，当我们再增加一条数据的时候就会再增加新页。

这个时候会发现我新增加页的页号是28，为什么不是11，是因为新增的页它不一定是紧挨着上一页的，然后页28的这条数据的主键是4，而页10最后一条数据的主键是5，5>4这是不符合下一页主键是大于上一页主键的要求的，这个时候就会发生一次移动，先把主键为5的这条数据放到页28当中，然后再将主键为4的这条数据插入到页10中。

当我们记录多条数据的时候就会有很多很多页的数据，这个时候就会形成一个存放目录项的数据页，下面把这个数据页先称为目录页（自己怎么好记怎么来）。

这里可以看到页30是作为目录项的，它是没有我们的详细数据的，它只有每个页的页号，打个比方我们想要主键为20的这条数据的话，在目录项中1、5、12、209中20肯定是比12大比209小的，这个时候我们先确定了我们要的20这条数据是在第9页里面的，找到第9页以后，因为我们的例子里面的数据很少，所以直接线性查找就可以找到我们要的主键为20的这条详细的信息了。

这个时候如果假设每个目录页能存4页的数据，那么我们再填一页的数据的时候，就会新增一个目录页。如图：

然后假设我们有很多很多上千万条数据，即使我们有目录页，那么目录页也会非常非常的多，这个时候就会和之前一样，在我们的目录页上面会再增加一个范围更加大的目录页。如图：

我们接着举个例子，假如我们要找到主键为220的这条数据，我们的步骤就是：

1.在最上面的目录页中220是比1大，比320小的，所以我们选择左边的页30。

2.在页30中，220是比209还要大的，所以我们选择页20。

3.在页20中，就可以很快的锁定我们要的主键为220的这条数据了。

然后大家可以看到，我们现在的这个样子是很像树状结构的，它的名称就是B+树。为什么叫这个名字，大家可以自己去查看了解。

这个时候我们的数据页和存放目录的数据页都是在我们这个结构中了。我们把这些数据页称为节点，把存放详细信息的，最底层的这些数据页称为叶子节点，其它的存放目录的称为非叶子节点。其中最上面的称为根节点。

这里我们假设存放我们详细数据的叶子节点可以存放100条数据，我们存放目录的非叶子节点可以存放1000个目录。

只有一层：100条数据

两层：100*1000=100000条数据

三层：100*1000*1000=100000000条数据

四层：100*1000*1000*1000=100000000000条数据

如果我们一张表中存放了这么这么多条数据，就算我们用二分查找或者用其它的方式也是要很长的时间的。所以我们用到B+树时都不会超过四层。

1）聚簇索引

讲了这么多，从记录一条数据到组到页，再到我们现在B+树的样子，终于可以开始了解我们的聚簇索引了。在我们的现实生活中，我们小的时候都要求会使用字典，我们用拼音查找的时候，是不是要在a-z中找到开头字母，然后再进行下一步的查找，最终找到我们想知道的那个字所在的页数。现在我们把B+树看成一个目录，我们最终的目的是为了找到我们想要的详细数据，那么我们就需要一步一步的锁定我们要的数据的那一页再进行查找。

首先我们要知道我们是用主键的大小进行排序的，页与页之间的主键是由大小顺序的，形成了双向链表。页内也是根据主键大小进行排序的，形成单向链表。

像我们之前的例子，我们是根据主键一步一步的找到我们想要的存放在叶子节点的详细信息。这个就是聚簇索引，lnnoDB存储引擎它是会自动帮我们建立一个聚簇索引的，是不需要我们自己来创建的。

2）二级索引

对聚簇索引有个大概的了解以后，我们看下什么是二级索引。我们之前的聚簇索引都是用主键去查找的，B+树是根据主键的大小排序进行创建的。如果我们用c2或者c3进行查找呢？用其它字段查找难道需要从头到尾的进行查找嘛？我们是可以用其它字段创建新的B+树。这个时候就会有人要问了，聚簇那么好用，为啥还要用其它字段查找不是更麻烦嘛，举个例子，在平时的生活中，大家互相称呼都是用姓名不会用身份证号码之类的吧，在学校里，我想找到你的信息，基本上都是说姓名进行查找的，不会特意用学号进行查找的。效果图如下：

大家可以看到，这个和我们之前聚簇索引的B+树是很像的，仔细的小伙伴可以看到我们的叶子节点存放的不是我们的详细信息了，而是我们的主键了。我们的查询条件也是变成了其它的字段。这里给大家举个例子，假设我要找到c2是7的详细信息：

1.在页44中，7是比2大比9小的，所以我们先选择左边的42页。

2.在页42中，7对应着40页，这个时候就会出问题了，细心的小伙伴可以看到在页36和页40中都有c2=7，这是因为我们之前都是用主键进行查找的，主键它是有唯一性的，而c2其它字段是没有唯一性的，打个比方，假如我们用性别进行查找，性别只有男和女，假如前一页全是男，那么后一页也有可能会是男，这是有可能的。所以c2=7是可能存在页36和页40中的。

3.我们通过c2=7找到了两个主键8和12。

这个时候我们得到的是主键，并没有得到我们想要的详细信息，这个时候就需要进行回表的操作了，把得到的两个主键用我们前面提到的聚簇索引进行查找，找到我们想要的详细信息。

3）联合索引

我们的二级索引是用c2或者c3其它字段来进行查找，那是不是可以用c2和c3的大小来排序呢？是可以的，其本质也是二级索引。来联合索引是我们先用c2来进行查询，如果在c2相同的情况下再通过c3来查询。如图：

这里可以看到，我们的联合索引它的叶子节点也是存的主键，和我们的二级索引是一样的，再找到主键后也是需要回表的。

四、使用索引

我们的聚簇索引它是主键自带的索引，MySQL会自动的帮我们创建好聚簇索引，但是在一些场景中我们不得不要用到二级索引来完成，我们来看下索引的一些注意点，二级索引需要查询的次数很多，还要进行回表的操作，没法利用缓存的局部性，如果回表的次数太多，我们的MySQL会优化，会直接全表扫描。我们来直接举几个例子：

首先我们先建个表往里面放一千五百万条数据。

我们的主键是id，会自动建立聚簇索引，我们手动创建以name, birthday, phone_number字段为我们的联合索引。两个索引所以创建了两颗B+树，简单的看下B+树的示意图：

这里内节点中的第一条目录的生日是有错误的。接下来的内容是要结合这张图来理解的。

在sql语句前面加上EXPLAIN来看我们有没有用到索引和优化的情况。使用EXPLAIN后type有const>ref>range>index>all,通常优化到前三个就还可以。

1）全值匹配

细心的朋友可以看出来我们的搜索条件是和我们建表时的idx_name_birthday_phone_number一致,这种情况我们称为全值匹配，大家看图可以想象，我们是先用name来查询，如果name相等我们就用birthday来查询，如果很不幸name和birthday都相同，就要用phone_number来查询了。

这个时候就会有朋友说，如果我们把查询的条件换一下会怎么样呢？我们来实验下。