《MySql是怎样运行的：从根儿上理解MySQL》复习记录（前部分：索引）

蓝色 - Lanse

已于 2023-04-17 12:57:02 修改

阅读量272

点赞数

文章标签： mysql 数据库 java

于 2023-04-06 23:14:18 首次发布

本文链接：https://blog.csdn.net/qq_61465103/article/details/130000632

版权

一、字符集

建立字符与二进制数据的映射关系: 将一个字符映射成一个二进制数据的过程叫做编码，将一个二进制数据映射到一个字符的过程叫做解码。
字符集：描述某个字符范围的编码规则
常见字符集： ASCII 字符集、ISO 8859-1 字符集、GB2312 字符集、GBK 字符集、utf8 字符集
注意
（1）MySQL中的 utf8 和 utf8mb4

在 MySQL 中 utf8 是 utf8mb3 的别名，所以之后在 MySQL 中提到 utf8 就意味着使用1~3个字节来表示一个字符，如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情啥的，那请使用 utf8mb4 。

… (先复习后面的)

二、InnoDB从一条记录说起

1. 页的概念：将数据划分为若干个页，InnoDB存储引擎以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为16KB

2. 主要内容：InnoDB行格式，常见的行格式 COMPACT、Dynamic、Compressed，如下：

（1） COMPACT行格式:一条完整的记录其实可以被分为记录的额外信息和记录的真实数据两大部分

先看 记录的额外信息 部分：

变长字段长度列表
变长字段：VARCHAR(M) 、 VARBINARY(M) 、各种 TEXT 类型，各种 BLOB 类型，我们也可以把拥有这些数据类型的列称为变长字段
变长字段长度列表：把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长
字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放
NULL值列表
NULL值列表: 我们知道表中的某些列可能存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中存储会很占地方，所以 Compact 行格式把这些值为 NULL 的列统一管理起来，存储到 NULL 值列表中。如果表中没有允许存储 NULL 的列，则 NULL值列表也不存在了。
存储方式为在NULL值列表中将每个允许存储 NULL 的列对应一个二进制位，二进制位按照列的顺序逆序排列
记录头信息

然后看 记录的真实数据部分

隐藏列
从行格式中可以只看到了我们自己定义的列的数据，除此之外，MySQL 会为每个记录默认的添加一些列（也称为隐藏列），具体列如下：

三个隐藏列的说明：InnoDB存储引擎会为每条记录都添加 transaction_id 和 roll_pointer 这两个列，但是 row_id 是可选的（在没有自定义主键以及Unique键的情况下才会添加该列）
真实数据
注意：对于 CHAR(M) 类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字
段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表。因为当该列采用ASCII字符集时候，列中内容一个字符需要一个字节，如果采用utf-8字符集，那么一个字符占用的字节数为 1 ~ 3
行溢出： MySQL 对一条记录占用的最大存储空间是有限制的，除了 BLOB 或者 TEXT 类型的列之
外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。
在 Compact 和 Reduntant 行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处用20个字节存储指向这些页的地址，从而可以找到剩余数据所在的页, 如图：
对于 Compact 和 Reduntant 行格式来说，如果某一列中的数据非常多的话，在本记录的真实数据处只会存储该列的前 768 个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中，这个过程叫做 行溢出 ，存储超出 768 字节的那些页面被称为 溢出页 。

（2）Dynamic和Compressed行格式

特点： 在处理行溢出数据时，它们不会在记录的真实数据处存储字段真实数据的前 768 个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址
二者区别： Compressed 行格式和 Dynamic 不同的一点是， Compressed 行格式会采用压缩算法对页面进行压缩，以节省空间。

三、盛放记录的大盒子-InnoDB数据页结构

1. InnoDB存储引擎中不同类型页简介：

页是InnoDB存储空间的单位，InnoDB 有存放undo日志信息的页等等类型、这章主要写的是存放表中记录的那种页，它是索引页（下面内容称为数据页）。

2. 数据页简介

数据页的结构如下：
在这里插入图片描述

首先看， 记录在页中是怎么存储的。图示如下：
在这里插入图片描述

每当我们插入一条记录，都会从 Free Space 部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到 User Records 部分，当 Free Space 部分的空间全部被 User Records 部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了。
为了更好的管理在 User Records 中的这些记录， InnoDB 可费了一番力气呢，在哪费力气了呢？不就是把记录按
照指定的行格式一条一条摆在 User Records 部分么？其实这话还得从记录行格式的 记录头信息 中说起。

记录头信息的秘密

记录头信息结构如下：在这里插入图片描述
各属性含义

属性含义分析：

delete_mask
这个属性标记着当前记录是否被删除，占用1个二进制位，值为 0 的时候代表记录并没有被删除，为 1 的时
候代表记录被删除掉了。这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗，所以只是打一个删除标记而已，所有被删除掉的记录都会组成一个所谓的垃圾链表 ，在这个链表中的记录占用的空间称之为所谓的可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖
min_rec_mask
B+树的每层非叶子节点中的最小记录都会添加该标记.
n_owned
下文介绍它
heap_no
这个属性表示当前记录在本页中的位置，每个页里边儿加两个记录，由于这两个记录并不是我们自己插入的，所以有时候也称为伪记录或者虚拟记录。这两个伪记录一个代表 最小记录 ，一个代表 最大记录。记录可根据主键大小比较大小。（注：最小记录和最大记录不在User Records部分，在Infimum + supremum部分）。
record_type
这个属性表示当前记录的类型，一共有4种类型的记录， 0 表示普通记录， 1 表示B+树非叶节点记录， 2 表
示最小记录， 3 表示最大记录。
next_record
从当前记录的真实数据到下一条记录的真实数据的地址偏移量。（下一条记录是根据主键值大小排序之后的记录），可以想到 Infimum记录（也就是最小记录）的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是 Supremum记录（也就是最大记录）
（注：第一、不论我们怎么对页中的记录做增删改操作，InnoDB始终会维护一条记录的单链表，链表中的各个
节点是按照主键值由小到大的顺序连接起来的。第二、当数据页中存在多条被删除掉的记录时，这些记录的next_record属性将会把这些被删除掉的记录组成一个垃圾链表，以备之后重用这部分存储空间。)

2. Page Directory（页目录）

引出：每次查找一条记录要全表扫描？答案：肯定不是，可以通过在每页制定目录来，先找到目标记录所在的部分，然后再找它

页目录

将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。
每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的 n_owned 属性表示该记录拥有多少条记录
录，也就是该组内共有几条记录。
将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近页的尾部的地方，这个地方就是所
谓的 Page Directory ，也就是 页目录 （此时应该返回头看看页面各个部分的图）。页面目录中的这些地址
偏移量被称为槽（英文名： Slot ），所以这个页面目录就是由槽组成的。
举例如下：上面为两个组：第一个为最小记录形成的一个组，第二个为用户记录与最大记录形成的组。每个组的最后那条记录的地址偏移量存储到当前页中的 Page Directory 部分。该部分中的每个值就是一个槽。

分组规则：对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在1~ 8条之间，剩下的分组中记录的条数范围只能在是 4 ~ 8 条之间。

至此，查找一条记录可根据此方法查找：

通过二分法确定该记录所在的槽，并找到该槽中主键值最小的那条记录。
通过记录的 next_record 属性遍历该槽所在的组中的各个记录。

时间复杂度不就由 O(n) 变为 O(logn)了

3. Page Header（页面头部）

各个属性如下：
在这里插入图片描述

PAGE_DIRECTION
假如新插入的一条记录的主键值比上一条记录的主键值大，我们说这条记录的插入方向是右边，反之则是左
边。用来表示最后一条记录插入方向的状态就是 PAGE_DIRECTION 。
PAGE_N_DIRECTION
连续几次插入新记录的方向都是一致的

4. File Header（文件头部）

简介：File Header 针对各种类型的页都通用，也就是说不同类型的页都会以 File Header 作为第一个组成部分，它描述了一些针对各种页都通用的一些信息，比方说这个页的编号是多少，它的上一个页、下一个页是谁。这个部分占用固定的 38 个字节，是由下边这些内容组成的：在这里插入图片描述
重要属性含义如下：

FIL_PAGE_SPACE_OR_CHKSUM
这个代表当前页面的校验和（checksum）。啥是个校验和？就是对于一个很长很长的字节串来说，我们会
通过某种算法来计算一个比较短的值来代表这个很长的字节串，这个比较短的值就称为校验和。这样在比
较两个很长的字节串之前先比较这两个长字节串的校验和，如果校验和都不一样两个长字节串肯定是不同
的，所以省去了直接比较两个比较长的字节串的时间损耗。
FIL_PAGE_OFFSET
页号
FIL_PAGE_TYPE
这个代表当前页的类型，我们前边说过， InnoDB 为了不同的目的而把页分为不同的类型，我们上边介绍的
其实都是存储记录的数据页，其实还有很多别的类型的页
FIL_PAGE_PREV 和 FIL_PAGE_NEXT
FIL_PAGE_PREV 和 FIL_PAGE_NEXT 就分别代表本页的上一个 和下一个页的页号。这样通过建立一个双向链表把许许多多的页就都串联起来了。（注：并不是所有类型的页都有上一个和下一个页的属性）。
双向链表图如下：
5. File Trailer

为了检测一个页是否完整（也就是在同步的时候有没有发生只同步一半的尴尬情况），设计 InnoDB 的大叔们在每个页的尾部都加了一个 File Trailer 部分，这个部分由 8 个字节组成，可以分成2个小部分：

前4个字节代表页的校验和
这个部分是和 File Header 中的校验和相对应的。每当一个页面在内存中修改了，在同步之前就要把它的校
验和算出来，因为 File Header 在页面的前边，所以校验和会被首先同步到磁盘，当完全写完时，校验和也
会被写到页的尾部，如果完全同步成功，则页的首部和尾部的校验和应该是一致的。如果因为断电或某些原因导致同步未完全完成，则在 File Trialer 中的校验和代表着原先的页，二者不同则意味着同步中间出了错。
后4个字节代表页面被最后修改时对应的日志序列位置（LSN）
这个部分也是为了校验页的完整性的，只不过我们目前还没说 LSN 是个什么意思，所以大家可以先不用管这
个属性。

（注： File Trailer 与 File Header 类似，都是所有类型的页通用的。）

总结本章内容如下：在这里插入图片描述

四、快速查询的秘籍-B+树索引

1. 没有索引的查找

在一个页中查找，方法如下：

以主键为搜索条件（根据Page Directory 二分查找到记录所在目录，然后在开始在该目录中进行扫描）
以其他列作为搜索条件（从最小记录开始扫描该页数据）

在多个页中查找，步骤如下：

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

由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们刚刚唠叨过的查找方式去查找指定的记录，因此索引就出现了。

2. 索引类型：

聚簇索引
二级索引
为非主键列建立索引，B+树的各叶子节点中的用户记录只有该列和主键，需要找到该列后，然后通过主键进行回表，才能查询到该条记录的完整信息
联合索引
本质上也是一个二级索引，只不过B+树的叶子节点的各条记录是依次根据联合索引的各列进行排序的，就是第一个列的值若相同的话，才根据第二个列的值进行比较，依次类推

五、好东西也得先学会怎么用-B+树索引的使用

索引的代价

空间上的代价：若为一个列建立一个索引，就会建立与该列索引对应的B+树，每个B+树的每个节点都是16KB的页。
时间上的代价：若为一个列建立一个索引，那么当每次对表进行增删改查时，因为要维护整个B+树，那么这就需要很大的，况且若为多个列都建立索引，那么时间上代价将会很大。

B+树索引适用的条件

例表：

CREATE TABLE person_info(
	 id INT NOT NULL auto_increment,
	 name VARCHAR(100) NOT NULL,
	 birthday DATE NOT NULL,
	 phone_number CHAR(11) NOT NULL,
	 country varchar(100) NOT NULL,
	 PRIMARY KEY (id),
	 KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);

适用条件一（全值匹配）
如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配，sql如下：

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_num
ber = '15123983239';

联合索引情况下，查询过程如下：先根据name二分查找到记录位置，然后根据birthday二分查找到记录位置，然后再根据phone_num查找到记录位置。（联合索引中的三个列可能没全被用到）

适用条件二（匹配列前缀）
以联合索引中的第一个列name为例，字符串比较是依次字符比较大小。所以这个sql会用到name列的索引。（因为name列的各个值的前缀都是排好序的）

SELECT * FROM person_info WHERE name LIKE 'As%';

适用条件三（匹配范围值）

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

查询过程：

找到 name 值为 Asa 的记录；
找到 name 值为 Barlow 的记录；
然后取他们中间所有记录。

注意：如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到 B+ 树索引，sql如下：

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-0
1';

通过 name 进行范围查找的记录中可能并不是按照 birthday 列进行排序的（除非根据name列查到的所有记录name值相同）

适用条件四（精确匹配某一列并范围匹配另外一列）

很好理解~ ：当联合索引中的前部分列都精确匹配了，那么当前查到的记录的前部分列的值都是相同的，那么当然可以用到B+树索引根据下一个列的值来进行范围匹配了

适用条件五（用于排序）
若某条sql排序的根据字段依次是联合索引中的各个列。那么就相当于直接从联合索引对应的叶子节点中依次取数据（需要进行回表）。
适用条件六（用于分组）
若某条sql分组的根据字段依次是联合索引中的各个列。那么就相当于直接从联合索引对应的叶子节点中依次取数据（需要进行回表）。

回表的代价

1. 代价

就一句话：访问二级索引使用顺序I/O ，访问聚簇索引使用随机I/O

sql例子：

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

这句话的理解是：由于在二级索引中的叶子节点的记录是依次根据联合索引的字段进行排序，那么我们在执行这条sql的时候，查询过程如下：
先根据二级索引找到对应的 name 值在 Asa ～ Barlow 之间的用户记录，我们每次对各条记录进行回表时候，是挨着一条一条进行的，这是顺序I/O，但是，这一条一条的并不是根据主键进行排序的，所以在回表时候，找聚簇索引的B+树中叶子结点的记录时候，是可能查询多个数据页的，这是 随机I/O

2. 减少代价

覆盖索引
在查询语句中，只包含联合索引中的列，那么这就可以直接从B+树中的叶子结点中取出数据了，而且是在确定范围之后，一条一条的取，即顺序I/O，sql如下：

SELECT name, birthday, phone_number FROM person_info WHERE name > 'Asa' AND name < 'Barlo
w'

如何挑选索引？

只为用于搜索、排序或分组的列创建索引
只为出现在 WHERE 子句中的列、连接子句中的连接列，或者出现在 ORDER BY 或 GROUP BY 子句中的
列创建索引。
考虑列的基数
列的基数指的是某一列中不重复数据的个数，因此可以想到假设某个列的基数为 1 ，也就是所有记录在该列中的值都一样，那为该列建立索引是没有用的，因为所有值都一样就无法排序，无法进行快速查找了，所以，最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。
索引列的类型尽量小
原因如下：
（1）数据类型越小，在查询时进行的比较操作越快
（2）数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘 I/O 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
为字符串值的前缀建立索引
这种只为字符串值的前缀建立索引的策略是非常鼓励的，尤其是在字符串类型能存储的字符比较多的时候
注意： 二级索引中不包含完整的 name 列信息，所以无法对前十个字符相同，后边的字符不同的记录进行排序
让索引列在比较表达式中单独出现
（1）WHERE my_col * 2 < 4
（2）WHERE my_col < 4 / 2
第1个 WHERE 子句中 my_col 列并不是以单独列的形式出现的，而是以 my_col * 2 这样的表达式的形式出现的，存储引擎会依次遍历所有的记录，计算这个表达式的值是不是小于 4 ，所以这种情况下是使用不到为 my_col 列建立的 B+ 树索引的。而第2个 WHERE 子句中 my_col 列并是以单独列的形式出现的，这样的情况可以直接使用B+ 树索引。
如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出
现的话，是用不到索引的。
主键插入
假设某个数据页存储的记录已经满了，它存储的主键值在 1~100 之间，如果再插入一条入一条主键值为 9 的记录：
就需要页面分裂和记录移动位置，就会造成性能消耗，所以再一个表中要给主键设置自动递增，让存储引擎自己为表生成主键，而不是我们手动插入
冗余和重复索引
定位并删除表中的重复和冗余索引