MySQL 高级---索引及调优篇（1）

VIP文章睫毛进眼睛了！

已于 2022-06-10 07:27:52 修改

阅读量604

点赞数

分类专栏： SQL 文章标签： mysql b树数据结构

于 2022-06-10 07:23:57 首次发布

本文链接：https://blog.csdn.net/Xbai_hang/article/details/125214459

版权

MySQL 高级篇—索引及调优篇（1）

1. 索引的数据结构

1.1. 索引及其优缺点

1.1.1. 索引概述

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

索引的本质：索引是数据结构。可以简单理解为：排好序的快速查找数据结构，满足特定查找算法。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现 高级查找算法。

1.1.2. 优点

（1）类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本，这也是创建索引最主要的原因。

（2）通过创建唯一索引，可以保证数据库表中每一行 数据的唯一性。

（3）在实现数据的参考完整性方面，可以 加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。

（4）在使用分组和排序子句进行数据查询时，可以显著 减少查询中分组和排序的时间，降低了 CPU 的消耗。

1.1.3. 缺点

（1）创建索引和维护索引要 耗费时间，并且随着数据量的增加，所耗费的时间也会增加。

（2）索引需要占 磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间 存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。

（3）虽然索引大大提高了查询速度，同时却会 降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

⭐1.2. InnoDB 中索引的推演

1.2.1. 索引之前的查找

SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;

1. 在一个页中的查找

假设目前表中的记录比较少，所有的记录都可以被存放在一个页中，在查找记录的时候可以根据搜索条件的不同分为两种情况：

以主键为搜索条件
- 可以在页目录中使用 二分法 快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。
以其他列作为搜索条件
- 因为在数据页中并没有对非主键建立所谓的页目录，所以无法通过二分法快速定位相应的槽。只能从最小记录开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。查找效率非常低。

2. 在多个页中查找

大部分情况下表中存放的记录都是非常多的，需要好多的数据页来存储这些记录。在很多页中查找记录的话可以分为两个步骤：

定位到记录所在的页。
从所在的页内查找相应的记录。

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，都并不能快速的定位到记录所在的页，所以只能 从第一个页 沿着 双向链表 一直往下找，在每一个页中根据上面的查找方式去查找指定的记录。因为要遍历所有的数据页，超级耗时。

1.2.2. 设计索引

mysql> CREATE TABLE index_demo(
    -> c1 INT,
    -> c2 INT,
    -> c3 CHAR(1),
    -> PRIMARY KEY(c1)
    -> ) ROW_FORMAT = Compact;

新建的表 index_demo 中有 2 个 INT 类型的列，1 个 CHAR(1) 类型的列，且规定了 c1 列为主键，这个表使用 Compact 行格式来实际存储记录的。简化的 index_demo 表的行格式示意图：

record_type：记录头信息的一项属性，表示记录的类型，0 表示普通记录、1 表示目录项记录、2 表示最小记录、3 表示最大记录。
next_record：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用箭头来表明下一条记录是谁。
各个列的值：指记录在 index_demo 表中的三个列，分别是 c1、c2 和 c3。
其他信息：除了上述 3 种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

将记录格式示意图的其他信息项暂时去掉并把它竖起来的效果就是这样：

把一些记录放到页里的示意图就是：

一个简单的索引设计方案

Q：在根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢？

A：因为各个页中的记录并没有规律，无法确定搜索条件匹配哪些页中的记录，所以不得不依次遍历所有的数据页。

Q：如何 想快速的定位到需要查找的记录在哪些数据页 中？

A：可以为快速定位记录所在的数据页而 建立一个目录，建这个目录必须完成下边这些事：

下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。
给所有的页建立一个目录项。

以 页 28 为例，它对应 目录项 2，这个目录项中包含着该页的页号 28 以及该页中用户记录的最小主键值 5。

只需要把几个目录项在物理存储器上连续存储（比如：数组），就可以实现根据主键值快速查找某条记录的功能。比如：查找主键值为 20 的记录，具体查找过程分两步：

先从目录项中根据 二分法 快速确定出主键值为 20 的记录在 目录项 3 中（因为 12 < 20 < 209 ），它对应的页是 页 9。
在页中查找定位具体的记录。

至此，针对数据页做的简易目录就搞定了。这个目录有一个别名，称为索引。

InnoDB 中的索引方案

① 迭代 1 次：目录项纪录的页

把前边使用到的目录项放到数据页中：

从图中可以看出，新分配了一个编号为 30 的页来专门存储目录项记录。

注意 目录项记录 和普通的 用户记录 的区别。

不同点：

目录项记录 的 record_type 值是 1，而 普通用户记录 的 record_type 值是 0。
目录项记录只有 主键值和页的编号 两个列，而普通的用户记录的列是用户自己定义的，可能包含 很多列 ，另外还有 InnoDB 添加的隐藏列。
了解：记录头信息里还有一个叫 min_rec_mask 的属性，只有在存储 目录项记录 的页中的主键值最小的 目录项记录 的 min_rec_mask 值为 1，其他别的记录的 min_rec_mask 值都是0。

相同点：

两者用的是一样的数据页，都会为主键值生成 Page Directory（页目录），从而在按照主键值进行查找时可以使用 二分法 来加快查询速度。

以查找主键为 20 的记录为例，根据某个主键值去查找记录的步骤就可以大致拆分成下边两步：

先到存储 目录项记录 的页，也就是页 30 中通过 二分法 快速定位到对应目录项，因为 12 < 20 < 209 ，所以定位到对应的记录所在的页就是页 9。
再到存储用户记录的页 9 中根据 二分法 快速定位到主键值为 20 的用户记录。

② 迭代 2 次：多个目录项纪录的页

从图中可以看出，插入了一条主键值为 320 的用户记录之后需要两个新的数据页：

为存储该用户记录而新生成了 页 31。
因为原先存储目录项记录的 页 30 的容量已满（前边假设只能存储 4 条目录项记录），所以不得不需要一个新的 页 32 来存放 页 31 对应的目录项。

因为存储目录项记录的页不止一个，所以如果想根据主键值查找一条用户记录大致需要 3 个步骤，以查找主键值为 20的记录为例：

确定 目录项记录页，现在的存储目录项记录的页有两个，即 页 30 和 页 32，又因为页 30 表示的目录项的主键值的范围是 [1, 320) ，页 32 表示的目录项的主键值不小于 320 ，所以主键值为 20 的记录对应的目录项记录在 页 30 中。
通过目录项记录页 确定用户记录真实所在的页。在一个存储 目录项记录 的页中通过主键值定位一条目录项记录的方式说过了。
在真实存储用户记录的页中定位到具体的记录。

③ 迭代 3 次：目录项记录页的目录页

如图，生成了一个存储更高级目录项的 页 33，这个页中的两条记录分别代表页 30 和页 32，如果用户记录的主键值在 [1, 320) 之间，则到页 30 中查找更详细的目录项记录，如果主键值 不小于 320 的话，就到页 32 中查找更详细的目录项记录。

可以用下边这个图来描述它：

这个数据结构，它的名称是 B+ 树。

④ B+ Tree

一个 B+ 树的节点其实可以分成好多层，规定最下边的那层，也就是存放用户记录的那层为第 0 层，之后依次往上加。

之前便于理解做了一个非常极端的假设：存放用户记录的页 最多存放 3 条记录，存放目录项记录的页 最多存放 4 条记录。

其实真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录的叶子节点代表的数据页可以存放 100 条用户记录，所有存放目录项记录的内节点代表的数据页可以存放 1000 条目录项记录，那么：

如果 B+ 树只有 1 层，也就是只有 1 个用于存放用户记录的节点，最多能存放 100 条记录。
如果B+树有 2 层，最多能存放 1000 × 100 = 10,0000 条记录。
如果B+树有 3 层，最多能存放 1000 × 1000 × 100 = 1,0000,0000 条记录。
如果B+树有 4 层，最多能存放 1000 × 1000 × 1000 × 100 = 1000,0000,0000 条记录。相当多的记录！！！

这种情况下 4 层的 b+ 树存储的用户记录达到千亿级别，表内一般不会存储这么多记录！！！所以一般情况下，用到的 B+ 树都不会超过 4 层；那么通过主键值去查找某条记录最多只需要做 4 个页面内的查找（查找 3 个目录项页和 1 个用户记录页），又因为在每个页面内有所谓的 Page Directory（页目录），所以在页面内可以通过 二分法 实现快速定位记录。

1.2.3. 常见索引概念

1. 聚簇索引

特点：

使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
- 页内的记录是按照主键的大小顺序排成一个 单向链表。
- 各个存放 用户记录的页 是根据页中用户记录的主键大小顺序排成一个 双向链表。
- 存放 目录项记录的页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个 双向链表。
B+ 树的 叶子节点 存储的是完整的用户记录。

所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

优点：

数据访问更快，因为聚簇索引将索引和数据保存在同一个 B+ 树中，因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对于主键的 排序查找 和 范围查找 速度非常快
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以 节省了大量的 io 操作。

缺点：

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于 InnoDB 表，我们一般都会定义一个自增 ID 列为主键
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于 InnoDB 表，一般定义 主键为不可更新
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

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

概念：回表 根据这个以 c2 列大小排序的 B+ 树只能确定要查找记录的主键值，所以如果想根据 c2 列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查一遍，这个过程称为回表。也就是根据 c2 列的值查询一条完整的用户记录需要使用到 2 棵B+树！

3. 联合索引

同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，例如让 B+ 树按照 c2 和 c3 列 的大小进行排序，这个包含两层含义：

先把各个记录和页按照 c2 列进行排序。
在记录的 c2 列相同的情况下，采用 c3 列进行排序

注意一点，以 c2 和 c3 列的大小为排序规则建立的 B+ 树称为 联合索引，本质上也是一个二级索引。它的意思与分别为 c2 和 c3 列分别建立索引的表述是不同的，不同点如下：

建立 联合索引 只会建立如上图一样的 1 棵B+树。
为 c2 和 c3 列分别建立索引会分别以 c2 和 c3 列的大小为排序规则建立 2 棵 B+ 树。

1.2.4. InnoDB 的 B+ 树索引的注意事项

1. 根页面位置万年不动

前边介绍 B+ 索引时，为了便于理解，先把存储用户记录的叶子节点都画出来，然后接着画存储目录项记录的内节点，实际上 B+ 树的形成过程是这样的：

每当为某个表创建一个 B+ 树索引（聚簇索引不是人为创建的，默认就有）时，都会为这个索引创建一个 根节点 页面。

表中没有数据时，每个 B+ 树索引对应的 根节点 中既没有用户记录，也没有目录项记录。
向表中插入用户记录时，先把用户记录存储到 根节点 中。
当根节点中的可用 空间用完时，继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页 页a 中，然后对 页a 进行 页分裂 的操作，得到另一个新页 页 b。新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到 页a 或者 页b 中，而 根节点 便升级为存储目录项记录的页。