1.5万字+30张图盘点索引常见的11个知识点（上篇）

大家好，我是三友~~

今天来盘点一下关于MySQL索引常见的知识点

本来这篇文章我前两个星期就打算写了，提纲都列好了，但是后面我去追《漫长的季节》这部剧去了，这就花了一个周末的时间，再加上后面一些其它的事，导致没来得及写

不过不要紧，好饭不怕晚，虽迟但到，走起，开干！

对了，本文主要是针对InnoDB存储引擎进行讲解。

索引分类

索引的分类可以从不同的维度进行分类

1、按使用的数据结构划分

• B+树索引
• Hash索引
• ...

2、按实际的物理存储数据构划分

• 聚簇索引
• 非聚簇索引（二级索引）

聚簇索引和非聚簇索引后面会着重说。

3、按索引特性划分

• 主键索引
• 唯一索引
• 普通索引
• 全文索引
• ...

4、按字段个数划分

• 单列索引
• 联合索引

索引数据结构

准备

为了接下来文章更好地讲解，这里我准备了一张​​user​​表，接下来整篇文章的示例会以这张表来讲解

CREATE TABLE `user` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(10) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Hash索引

Hash索引其实用的不多，最主要是因为最常见的存储引擎InnoDB不支持显示地创建Hash索引，只支持自适应Hash索引。

虽然可以使用sql语句在InnoDB显示声明Hash索引，但是其实是不生效的

对name字段建立Hash索引，但是通过​​show index from 表名​​就会发现实际还是B+树

在存储引擎中，Memory引擎支持Hash索引

Hash索引其实有点像Java中的HashMap底层的数据结构，他也有很多的槽，存的也是键值对，键值为索引列，值为数据的这条数据的行指针，通过行指针就可以找到数据

假设现在​​user​​表用Memory存储引擎，对name字段建立Hash索引，表中插入三条数据

Hash索引会对索引列name的值进行Hash计算，然后找到对应的槽下面，如下图所示

当遇到name字段的Hash值相同时，也就是Hash冲突，就会形成一个链表，比如有name=张三有两条数据，就会形成一个链表。

之后如果要查name=李四的数据，只需要对李四进行Hash计算，找到对应的槽，遍历链表，取出name=李四对应的行指针，然后根据行指针去查找对应的数据。

Hash索引优缺点

• hash索引只能用于等值比较，所以查询效率非常高
• 不支持范围查询，也不支持排序，因为索引列的分布是无序的

B+树

B+树是mysql索引中用的最多的数据结构，这里先不介绍，下一节会着重介绍。

除了Hash和B+树之外，还有全文索引等其它索引，这里就不讨论了

聚簇索引

数据页数据存储

我们知道，我们插入表的数据其实最终都要持久化到磁盘上，InnoDB为了方便管理这些数据，提出了页的概念，它会将数据划分到多个页中，每个页大小默认是16KB，这个页我们可以称为数据页。

当我们插入一条数据的时候，数据都会存在数据页中，如下图所示

当数据不断地插入数据页中，数据会根据主键（没有的话会自动生成）的大小进行排序，形成一个单向链表

数据页中除了会存储我们插入的数据之外，还会有一部分空间用来存储额外的信息，额外的信息类型比较多，后面遇到一个说一个

单个数据页的数据查找

既然数据会存在数据页中，那么该如何从数据页中去查数据呢？

假设现在需要在数据页中定位到id=2的这条记录的数据，如何快速定位？

有一种笨办法就是从头开始顺着链表遍历就行了，判断id是不是等于2，如果等于2就取出数据就行了。

虽然这种方法可行，但是如果一个数据页存储的数据多，几十或者是几百条数据，每次都这么遍历，不是太麻烦了

所以mysql想了一个好办法，那就是给这些数据分组

假设数据页中存了12条数据，那么整个分组大致如下图所示

为了方便了，我这里只标出了id值，省略了其它字段的值

这里我假设每4条数据算一个组，图上就有3个组，组分好之后，mysql会取出每组中最大的id值，就是图中的4、8、12，放在一起，在数据页中找个位置存起来，这就是前面提到的数据页存储的额外信息之一，被称为页目录

假设此时要查询id=6的数据之后，此时只需要从页目录中根据二分查找，发现在4-8之间，由于4和8是他们所在分组的最大的id，那么id=6肯定在8那个分组中，之后就会到id=8的那个分组中，遍历每个数据，判断id是不是等于6即可。

由于mysql规定每个组的数据条数大概为4~8条，所以肯定比遍历整个数据页的数据快的多

上面分组的情况实际上我做了一点简化，但是不耽误理解

多个数据页中的数据查找

当我们不断的往表中插入数据的时候，数据占用空间就会不断变大，但是一个数据页的大小是一定的，当一个数据页存不下数据的时候，就会重新创建一个数据页来存储数据

mysql为了区分每个页，会为每个数据页分配一个页号，存在额外信息的存储空间中，同时额外信息还会存储当前数据页的前一个和后一个数据页的位置，从而形成数据页之间的双向链表

数据页2的页号就是2，数据页3的页号就是3，这里我为了方便理解，就直接写数据页几。

并且mysql规定，前一个数据页的存储数据id的最大值要小于后一个数据页的存储数据id的最小值，这样就实现了数据在所有数据页中按照id的大小排序。

现在，如果有多个数据页，当我们需要查找id=5的数据，怎么办呢？

当然还是可以用上面的笨办法，那就是从第一个数据页开始遍历，然后遍历每个数据页中的数据，最终也可以找到id=5的数据。

但是你仔细想想，这个笨办法就相当于全表扫描了呀，这肯定是不行的。

那么怎么优化呢？

mysql优化的思路其实跟前面单数据页查找数据的优化思路差不多

它会将每个数据页中最小的id拿出来，单独放到另一个数据页中，这个数据页不存储我们实际插入的数据，只存储最小的id和这个id所在数据页的页号，如图所示

为了图更加饱满，我加了一个存放数据的数据页4

此时数据页5就是抽取出来的，存放了下面三个存放数据的数据页的最小的id和对应的数据页号

如果此时查找id=5的数据就很方便了，大致分为以下几个步骤：

• 从数据页5直接根据二分查找，发现在4-7之间
• 由于4和7是所在数据页最小的id，那么此时id=5的数据必在id=4的数据页上(因为id=7的数据页最小的id就是7)，
• 接下来就到id=4对应的数据页2的页号找到数据页2
• 之后再根据前面提到的根据数据的主键id从单个数据页查找的流程查找数据

这样就实现了根据主键id到在多个数据页之间查找数据

聚簇索引

随着数据量不断增多，存储数据的数据页不断变多，数据页5的数据就会越来越多，但是每个数据页默认就16k，所以数据页5也会分裂出多个数据页的情况，如下图

数据页10的作用就跟数据页5是一样的

此时如还要查找id=5的数据，那么应该去数据页5进行二分查找呢还是去数据页10进行二分查找呢？

笨办法就是遍历，但是真没必要，mysql会去抽取数据页5和数据页10存储的最小的数据的id和对应的数据页号，单独拎出来放到一个数据页中，如下图

数据页11就是新抽取的数据页，存储了id=1和对应的数据页5的页号以及数id=10和对应的数据页10的页号

而这就是B+树。

一般来说，mysql数据库的B+树一般三层就可以放下几千万条数据

此时查找id=5的数据，大致分为以下几个步骤：

• 从数据页11根据二分查找定位到id=5对应数据页5
• 再到数据页5根据id=5二分查找定位到数据页3
• 再到数据页3根据id=5查找数据，具体的逻辑前面也提到很多次了

这样就能成功查找到数据了

而这种叶子节点存储实际插入的数据的B+树就被称为聚簇索引，非叶子节点存储的就是记录的id和对应的数据页号。

所以对于InnoDB存储引擎来说，数据本身就存储在一颗B+树中。

​

​

​

文章转载自公众号：三友的java日记