MySQL的索引原理你真的了解吗？

Sma2tArt
319 阅读20分钟

金三银四，作为面试必问内容，着实有必要从头到尾学习/复习一下MySQL的索引。那么本文将针对:1.什么是索引？2.为什么使用索引？3.数据结构的选择。4.数据页中的记录格式。5.目录页。6.常见索引方式。7.InnoDB中的B+树创建过程。8.MyISAM索引方案。9.InnoDB与MyISAM索引方式的区别进行讲解。

什么是索引？

官方定义：索引（Index）是帮助MySQL高效获取数据的数据结构

索引的本质：你可以将理解为排好序的数据结构，满足特定的查找算法。索引以某种方式指向数据，这样我们就可以使用查找算法获取我们所需要的数据了。

索引是在存储引擎中实现的，因此不同的存储引擎所支持的索引也是不同的。 比如，InnoDB 和 MyISAM 默认的索引是 B-tree 索引；而 Memory 默认的索引是 Hash 索引。

为什么要使用索引？

MySQL查找数据的方式有两种：顺序查找&索引查找

  1. 顺序查找

    顺序查找就是进行全表扫描，在无序的数据记录中一条一条进行查找，直到找到与条件符合的记录。

    上面这种是通俗意义上的顺序查找。实际上是：（大家都知道数据都存储在页中）

    • 从第一个页沿着双向链表一直向下寻找，在每个页中筛选出满足条件的数据记录，直至所有数据页遍历结束。

    • 而在每一个页中的查找，与对应的搜索条件有关

      1）若以主键为搜索条件：可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录（这里涉及到了页结构，大家可以查阅相关资料）；

      2）以其它列作为搜索条件：在数据页中并没有对非主键列建立所谓的页目录，所以我们无法通过二分法快速定位相应的槽。因此只能从最小记录依次遍历单链表中的记录，逐条比对；

    可想而知，这种方法对于数据量非常大的表效率是非常低下的，需要进行多次I/O操作，从磁盘中获取页数据。

    比如：在新华字典中查找一个文字，需要一页一页翻，再从每个页中的文字依次匹配。

  2. 索引查找

    索引查找就是通过遍历索引，从而找到对应数据记录的位置。在表中建立了索引，就相当于给该表建立了映射，按照某(些)列进行排序，这样我们查找时就可以使用二分查找等。

    比如：在新华字典中查找一个文字，首先去目录中找对应音序，直接定位到对应页，遍历那一页数据就可以了。

综上，索引的目的就是为了减少磁盘I/O的次数，从而增加查询速率。

数据结构的选择

加速查找速度的数据结构，常见的有两类：

  • Hash，查询/插入/修改/删除的平均时间复杂度都是O(1)
  • 树，例如平衡二叉搜索树，查询/插入/修改/删除的平均时间复杂度都是O(logN)

1）Hash

大家平时肯定都用过HashMap等集合。知道采用Hash进行检索效率会非常高，时间复杂度为O(1)，基本一次就能检索到对应的数据，而树结构需要自顶向下进行查找，需要进行多次I/O。既然Hash结构效率这么高，那为什么常用的存储引擎InnoDB、MyISAM都不支持Hash呢？（Memory是支持Hash的）主要有以下原因：

  • Hash适合精确查找，不适合范围查询

    hash数据的存储是没有顺序的，两个数据由于hash值原因，可能会距离很远，因此范围查询Hash结构就会退化成O(n)，而树形结构能够保持O(logn)的效率。

  • 联合索引情况下，Hash将多个索引键合并后进行计算，无法对单独的一个索引键或几个索引键进行查询。

  • 对于等值查询来说，索引列的重复值过多（比如性别等），就会产生"碰撞"，这时需要遍历桶中的行指针来进行比较。（桶就是盛放不同key链表的容器）

Hash的优点这么香，真就一点没有借鉴吗？

不不不，InnoDB本身不支持Hash索引，但是提供 自适应Hash索引（Adaptive Hash Index）

如果某个数据经常被访问，当满足一定条件时，就会将这个数据页的地址存放到Hash表中。下次查询的时候，就可以直接找到这个页面所在的位置。

采用自适应 Hash 索引目的是方便根据 SQL 的查询条件加速定位到叶子节点（数据页），特别是当 B+ 树比较深的时候，通过自适应 Hash 索引可以明显提高数据的检索效率。

查看是否开启了自适应Hash，默认开启

mysql> show variables like '%adaptive_hash_index';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+
1 row in set (0.01 sec)

2）二叉搜索树

如果使用二叉搜索树作为索引结构，磁盘的I/O次数和树的高度有关。

二叉搜索树的特点

  • 一个节点只能有两个子节点，即节点的度不能超过2；
  • 左子节点 < 本节点；右子节点 > 本节点；

查找规则

搜索某个节点和插入节点的规则一样，假设搜索插入的数值为key

  1. 如果key大于根节点，则在右子树中进行查找；
  2. 如果key等于根节点，即找到了目标节点，返回即可；
  3. 如果key小于根节点，则在左子树中进行查找。

举例：按照（25，4，16，20，77，32，1）建立二叉搜索树

image-20230222145345260.png

image-20230222145646691.png

上面两棵树都属于二叉搜索树，但第二棵树已经退化成了一条链表，查找数据的时间复杂度就变成了O(n)。为了提高查询效率即减少I/O次数，需要降低树的高度，尽量变成第一棵树那样，树每层的分叉越多越好。

3）平衡二叉搜索树（AVL）

为了解决上面二叉搜索树退化成链表的问题，提出了平衡二叉搜索树（AVL）

平衡二叉搜索树的特点

  • 根节点会随着数据的变化而变化；
  • 左右两颗子树的高度差的绝对值不超过1；
  • 搜索时间复杂度O(logN)

如下图所示，插入节点77

image-20230223141154845.png

4）B树

综上，树分叉越多越好，降低高度。从“瘦高”变“矮胖”，因此在这个基础上引入了B树（B-Tree），B树的结构如下所示：

image-20230223150104299.png

可见上图所示的是一颗M=3的B树，有如下特征：

  1. 根节点的子节点数量范围是[2, M]
  2. 每个节点对应2个键值，该节点就对应3个子节点，即子节点的数量=键值的数量 + 1。其中子节点的数量范围是[ceil(M/2), M]（ceil是向上取整）
  3. 每个节点中的键值按升序排序，且左子节点中的键值对应小于父结点左侧键值；中间的子节点中的键值位于父结点键值范围之间；右侧子节点中的键值大于父结点右侧键值。
  4. 所有子节点位于同一层。
  5. 每个节点不仅包含主键，还包含对应的记录数据data

总结

  • B树会进行自平衡调整节点位置保证树的平衡
  • 叶子节点&非叶子节点都存放数据，因此搜索可能在非叶子节点就结束了。

5）B+树

B+树也是一种多路搜索树，在B树的基础上做出了改进，像比较流行的InnDB、MyISAM存储引擎默认都是使用B+树作为索引结构的。B+树的结构如下所示：

image-20230224202916550.png

CREATE TABLE tableA(
	c1 INT,
	c2 INT,
	c3 CHAR(1),
	PRIMARY KEY(c1)
) ROW_FORMAT = Compact;

以上是为主键c1列建立聚簇索引后的B+树，B+树的特点如下：

  • 叶子节点（数据页）存储用户记录，非叶子节点（目录页）存储目录项【包括键值】。这一点与B树不同，B树中的每个节点不仅存储键值也存储用户记录。

    B+树做法的优点：Innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，从而整体上降低了树的高度。这样一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。

  • B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

    1. 页与页之间以及页内部的记录都是按照键值从小到达进行排序的。
    2. 页与页之间采用双向链表进行连接。
    3. 所有用户记录数据均存储在叶子节点上，不需要挨层去遍历寻找。

数据页中的记录项格式

从B+树中的图中可以看出，数据页中无论是记录项还是目录项都是使用单向链表进行连接的，那么记录项和目录项是如何区分的呢？里面存储的内容有什么要求呢？

上面我们创建表的时候ROW_FORMAT = Compact就表明该表使用Compact行格式来实际存储记录的。以下是行格式的简化图【详细行格式的内容大家可以去查阅相关资料哈】：

image-20230224153048705.png

B+树中出现的那几部分：

  • record_type：记录头信息的一项属性，表示记录的类型， 0 表示普通记录、 2 表示最小记 录、 3 表示最大记录、 1 表示目录项的记录。
  • next_record：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用箭头来表明下一条记录是谁。（用来保证数据逻辑上的连续）。
  • 各个列的值：这里只记录在 tableA 表中的三个列，分别是 c1 、 c2 和 c3。

目录页的出现&目录页迭代

新分配的数据页编号可能并不是连续的，它们之间只是通过页内维护着上一页与下一页的编号从而建立了链表关系，而页的大小默认是16KB，当我们不断地向里面插入数据，一个数据页总会有填满地时候，当数据页满后，就会页分裂，生成下一个页，以此类推（这里我们假设插入数据是按照主键升序插入的）。当有百万千万条数据时，就会形成一条连接着多个数据页的双向链表。

image-20230224210646298.png

就像我们上面提到的新华字典，如果没有目录的话，只能从前往后一页一页的进行查找。同样我们也需要为这些数据页建立相应的目录页，提升查找的效率。

那我们为上面的数据页做好目录项如下所示：

image-20230224210616986.png

我们可以把这几个目录项在物理存储器上连续存储（比如数组）， 接下来比如查找主键值为20的记录，具体过程如下

  • 在目录项中利用二分法确定主键值为20的记录在目录项3中（因为 12 < 20 < 209 ），它对应的页是 页 9。
  • 去相应数据页9中利用二分查找找到主键值为20的记录。

那么上面这种设计方案有没有什么问题呢？

比如我们把页9中的记录都删除了，那么目录项3就没有存在的必要了，即目录项2之后的目录项都要向前移动。这样牵一发而动全身的效率是非常低的。

1）第一次迭代：目录项纪录的页

针对上面的问题的解决方法就是：为多个目录项建立目录页，如同我们管理数据页一样。不过为了区分目录项和用户记录，就需要record_type【记录头信息中的一项属性】。将目录项放到数据页后如下所示：

image-20230224204449853.png

可见，目录项的特点：

  • 目录项只包含主键值和页编号两个列
  • 目录项记录 的 record_type 值是 1
  • 目录项和记录项用的是一样的页，都会为主键值生成 Page Directory （页目录），从而在按照主键值进行查找时可以使用 二分法 来加快查询速度（如果没有页目录，链表是无法进行二分查找）。【页目录的相关内容涉及到了数据页格式，读者可查阅相关资料，后续我会将写好的文章链接贴在这里】

2）第二次迭代：多个目录项纪录的页

还是那个问题，一个数据页的大小默认只有16KB，能存放的目录项记录也是有限的，如果表中数据太多，以至于一个数据页不足以存放所有的目录项记录，那怎么办呢？

我们只能再分配一个新的用于存储目录项的页。如下图所示：

image-20230224205439173.png

当我们插入了一条主键值为320的用户记录后，需要一个新的页31用来存储这条记录；需要一个目录页来存储这条记录的目录项。

问题又来了：数据持续增长下去，目录页不断增多，当我们查找的时候，在目录页中只能顺序查找了，无法使用二分，基于这个情况第三次迭代出现了。

3）第三次迭代：目录项记录页的目录页

最终呈现出了B+树的模样

image-20230224205932172.png

那么问题又来了，这迭代啥时候是个头啊？

真实情况下，一个页存放的记录数量是非常大的，假设所有存放用户记录 的叶子节点代表的数据页可以存放 100 条用户记录 ，所有存放目录项记录的内节点代表的数据页可以存 放 1000 条目录项记录 ，那么，如果B+树有4层，最多存放1000×1000×1000×100=1000,0000,0000条记录。

因此，一般情况下，我们 用到的 B+ 树都不会超过 4 层 ，那我们通过主键值去查找某条记录最多只需要做 4 个页面内的查找（查找 3 个目录页和一个数据页），又因为在每个页面内有所谓的 Page Directory （页目录），所以在页面内也可以通过二分法实现快速定位记录。

常见索引方式

1）聚簇索引

  • InnoDB的索引方式

上面介绍B+树索引的时候，我们提到了图中的索引其实是聚集索引的实现方式。

聚簇索引的特征

  1. 无论是数据页还是目录页甚至是页内的记录都是按照主键值的大小进行排序。
  2. 叶子节点存储的是完整的用户记录（所有列的值）【对InnoDB中**“数据即索引，索引即数据”**是不是更深刻了！】。
  3. 每个表只能有一个聚簇索引，一般是该表的主键。

满足1，2两种特征的B+树称为聚簇索引。

聚簇索引的优势

  • 范围查找&排序查找效率非常高；（这主要基于聚簇索引按照主键排序&数据都存放在叶子节点上&叶子节点通过双向链表连接）
  • 查找目标记录，不用进行回表操作；（叶子节点保存了用户记录中所有列的值）

聚簇索引的劣势

  • 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
  • 更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于 InnoDB 表，我们一般定义主键为不可更新
    • 比如我们将上幅图中页20中的数据209 改为409，会导致数据的移动。同时对上层目录页中的数据以及指针也可能变动，依次往上推，从而影响性能！

注意：不需要显示使用INDEX创建聚簇索引，InnoDB引擎会自动地为我们创建聚簇索引

InnoDB的主键是为了生成聚簇索引。如果没有显式指定，则MySQL系统会自动选择一个可以**非空且唯一**标识数据记录的列作为主键。如果不存在这种列，则MySQ 自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

2）非聚簇索引（二级索引，辅助索引）

CREATE TABLE tableA(
	c1 INT,
	c2 INT,
	c3 CHAR(1),
	PRIMARY KEY(c1)
) ROW_FORMAT = Compact;

上面提到的聚簇索引是以主键作为索引列的，那么我们如果想以别的列作为索引列怎么办呢？

实际上原理是一样的，首先非聚簇索引是可以有多个的，你建立一个非聚簇索引，MySQL就会帮你维护一个B+树。区别就是数据页每条记录中存储的数据是你创建的索引列+主键，并不是所有列数据。比如我们以c2列建立索引，B+树如下所示：

image-20230225111659091.png

如图得非聚簇索引的特点：

  • 无论是数据页还是目录页还是业内记录都是按照c2列进行排序。
  • 数据页中的主键不再有序。
  • 在数据页中可能存在多个相同c2列，如果c2列相同，就会根据主键字段去排序。

说明下面这条sql语句的查找过程

SELECT * FROM tableA WHERE c2=4
  1. 从根页面开始，定位目录页为页42（2 < 4 < 9）
  2. 由于c2列没有唯一性约束，因此c2=4的记录可能分配在多个数据页中。根据目录页42定位实际存储c2=4的数据页为页34和页35（2 < 4 <=4）。
  3. 去到数据页34和35中定位具体记录。
  4. 根据记录项中的主键值去表tableA的聚簇索引再查询一遍完整的用户记录（回表）。

问题：那我们为什么还要进行一次回表操作呢？直接将完整的记录存放到相应的叶子节点不行吗？

一张数据表中只能有一个聚簇索引，但可以有多个非聚簇索引。如果每个聚簇索引都将完整的用户记录保存到叶子节点，太耗费存储空间了。

什么是回表？

上面提到聚簇索引中只包含部分字段，如果我们根据对应非聚簇索引定位到某些记录，但其中某些列是不存在的，这时候怎么办呢？

这种情况就要根据我们在记录项中存放的主键索引（具有唯一性约束）去聚簇索引中查找，聚簇索引中存放的是一条完整的记录，这个过程就是回表。

MySQL中的索引下推（ICP）和覆盖索引都与是否进行回表操作有关，留意一下。

3）联合索引

联合索引也是可以归总到非聚簇索引中的。就是同时为多个列建立索引，比如我们以c2+c3列建立的联合索引，那么：

  • 每个目录页中的目录项都由c2+c3+页号三部分组成，各条记录先按照c2列的值进行排序，如果记录的c2列相同，则按照c3列的值进行排序。
  • 每个数据页中的记录项都由c2+c3和主键c1列组成。

image-20230225115517515.png

InnoDB构建B+树的过程

B+树是自上向下进行创建的

  1. 每当我们手动创建一个B+树索引【聚簇索引由MySQL自动给我们创建】，系统都会为这个索引创建一个根节点页。
  2. 向表中插入用户记录时，先将用户记录存储在这个根节点中。
  3. 根节点空间满时，会分配一个新的页[a]，然后将根节点中所有记录复制到页a中，对页a进行页分裂的操作，得到一个新页[b]。而根节点升级为目录页。
  4. 此时再插入用户记录，根据对应键值（聚簇索引中的主键，非聚簇索引的索引列值）的大小分配到页a/页b中。
  5. 依次类推，自上向下。

InnoDB中B+树索引的要点

1）根页面的位置万年不动

从上面的B+树构建过程就可以看出，根页面位置不会发生变化，通过页分裂操作不断产生新的页来扩充B+树。

2）内节点中目录项记录的唯一性

为了确保新插入的记录能够找到自己插入在哪个页中，B+树需要保证同一层内节点的目录项记录除页号这个字段以外是唯一的。因此对于非聚簇索引来说，目录项的构成：索引列+主键+页号

3）一个页面最少存储2条记录

一个页面存储的记录越多越好，这样的话需要很少层级就可以存储亿条数据，查询效率会非常高。而如果数据页只存储一条记录，那岂不是废废的了。

简单了解MyISAM的索引

MyISAM 引擎使用 B+Tree 作为索引结构，叶子节点的 data 域存放的是 数据记录的地址，MyISAM把索引和数据分开存储了。这也就是为什么我们在mysql目录对应data文件夹中查看由MyISAM存储引擎创建的表会有以下两个文件

表名.MYD
表名.MYI

  1. 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找。

  2. 使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM 会单独为表的主键创建一个索引， 只不过在索引的叶子节点中存储的不是完整的用户记录，而是 主键值+数据记录地址 的组合。

  3. 在MyISAM中，主键索引（Primary key）和二级索引（Secondary key）在结构上没有任何区别，都是仅保存数据记录的地址。只是主键索引要求key是唯一的，而二级索引的key可以重复。

    因此，MyISAM的索引方式是属于 "非聚簇" 的，之所以这么称呼是为了与InnoDB的聚簇索引区分

  4. MyISAM 的回表操作是十分 快速 的，因为是拿着地址偏移量直接到文件中取数据的，反观 InnoDB 是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。

以Col1列建立的主键索引如图所示：

image-20230227141717820.png

以Col2建立的二级索引与上图类似，只有对应key不同而已

InnoDB与MyISAM索引方式的区别

image-20230227142036172.png

  • MyISAM的索引方式都是 “非聚簇”的。InnoDB包含1个聚簇索引，可以包含多个非聚簇索引（二级索引）。
  • 在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引 。
  • InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的 ，索引文件仅保存数据记录的地址。
  • InnoDB的非聚簇索引data域存储相应记录主键的值 ，而MyISAM索引记录的是地址 。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。
  • MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
  • InnoDB要求表必须有主键（MyISAM可以没有）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。
avatar
文章
阅读
粉丝