MySQL篇 -《MySQL是怎样运行的》

image.png

第1章 装作自己是个小白-重新认识MySQL

MySQL的客户端／服务器架构

MySQL采用C/S结构

MySQL的安装

小贴士： MySQL的大部分安装包都包含了服务器程序和客户端程序，不过在Linux下使用RPM包时会有单独的服 务器RPM包和客户端RPM包，需要分别安装。

类UNIX操作系统非常多，比如FreeBSD、Linux、macOS、Solaris等都属于UNIX操作系统的范畴

目录信息 [官网文档]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
./bin
│── ibd2sdi
│── innochecksum----innodb页文件数据校验和工具
│── lz4_decompress
│── myisamchk
│── myisam_ftdump
│── myisamlog
│── myisampack
│── my_print_defaults
│── mysql
│── mysqladmin----执行管理操作的客户端程序，检查服务器的配置和当前状态、创建和删除数据库
│── mysqlbinlog-----binglong工具
│── mysqlcheck-----用来 检查，分析，优化，修复表
│── mysql_config-----提供编译 MySQL 客户端并将其连接到 MySQL 的有用信息
│── mysql_config_editor-----将身份验证凭据存储 在名为.mylogin.cnf
│── mysqld----个可执行文件就代表着MySQL服务器程序，运行这个可执行文件就可以直接启动一个服务器进程。
│── mysqld-debug----
│── mysqld_multi----可执行文件可以对每一个服务器进程的启动或停止进行监控。
│── mysqldump---导出数据相关功能
│── mysqldumpslow
│── mysqlimport----数据导入工具
│── mysql_migrate_keyring
│── mysqlpump----导出工具
│── mysql_secure_installation
│── mysqlshow
│── mysqlslap
│── mysql_ssl_rsa_setup
│── mysql_tzinfo_to_sql
│── mysql_upgrade
│── perror
│── mysql.server----也是一个启动脚本，它会间接的调用 mysqld_safe ，在调用 mysql.server 时在后边指定 start参数就可以启动服务器程序了
│── mysqld_safe----是一个启动脚本，它会间接的调用 mysqld ，而且还顺便启动了另外一个监控进程，这个监控进程
│	在服务器进程挂了的时候，可以帮助重启它。另外，使用 mysqld_safe 启动服务器程序时，它会将服务器程序的
│	出错信息和其他诊断信息重定向到某个文件中，产生出错日志，这样可以方便我们找出发生错误的原因。
└── zlib_decompress

1.启动MySQL服务器

1
service mysql start

2.启动MySQL客户端

1
mysql -h主机名 -u用户名 -p密码

MySQL 采用 TCP 作为服务器和客户端之间的网络通信协议。在网络环境下，每台计算机都有一个唯一的 IP地 址 ，如果某个进程有需要采用 TCP 协议进行网络通信方面的需求，可以向操作系统申请一个 端口号 ，这是一个 整数值，它的取值范围是 0~65535 。这样在网络中的其他进程就可以通过 IP地址 + 端口号 的方式来与这个进程 连接，这样进程之间就可以通过网络进行通信了。 MySQL 服务器启动的时候会默认申请 3306 端口号，之后就在这个端口号上等待客户端进程进行连接，用书面一 点的话来说， MySQL 服务器会默认监听 3306 端口。

端口冲突怎么办？

使用其他端口运行可采用mysqld -P3307。这样就指定来运行的端口。当然你登入的时候就应该采用mysql -h127.0.0.1 -uroot -P3307 -p来进行登入了。

客户端进程可以采用 TCP/IP 、 命名管道或共享内存 、 Unix域套接字 这几种方式之一来与服务 器进程建立连接，每当有一个客户端进程连接到服务器进程时，服务器进程都会创建一个线程来专门处理与这个 客户端的交互，当该客户端退出时会与服务器断开连接，服务器并不会立即把与该客户端交互的线程销毁掉，而是把它缓存起来，在另一个新的客户端再进行连接时，把这个缓存的线程分配给该新客户端。这样就起到了不频繁创建和销毁线程的效果，从而节省开销。从这一点大家也能看出， MySQL 服务器会为每一个连接进来的客户端 分配一个线程，但是线程分配的太多了会严重影响系统性能，所以我们也需要限制一下可以同时连接到服务器的 客户端数量

查询缓存

如果两个查询请求在任何字符上的不同（例如：空格、注释、大小写），都会导致缓存不会命中。另外，如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql 、information_schema、 performance_schema 数据库中的表，那这个请求就不会被缓存。以某些系统函数举例，可能同样的函数的两次调用会产生不一样的结果，比如函数 NOW ，每次调用都会产生最新的当前时间，如果在一个查询请求中调用了这个函数，那即使查询请求的文本信息都一样，那不同时间的两次查询也应该得到不同的结果，如果在第一次查询时就缓存了，那第二次查询的时候直接使用第一次查询的结果就是错误的！

不过既然是缓存，那就有它缓存失效的时候。MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了 INSERT 、 UPDATE 、 DELETE 、 TRUNCATE TABLE 、 ALTER TABLE 、 DROP TABLE 或 DROP DATABASE 语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除！

小贴士：

虽然查询缓存有时可以提升系统性能，但也不得不因维护这块缓存而造成一些开销，比如每次都要去查询缓存中检索，查询请求处理完需要更新查询缓存，维护该查询缓存对应的内存区域。从MySQL 5.7.20开始，不推荐使用查询缓存，并在MySQL 8.0中删除。

语法解析

如果查询缓存没有命中，接下来就需要进入正式的查询阶段了。因为客户端程序发送过来的请求只是一段文本而已，所以 MySQL 服务器程序首先要对这段文本做分析，判断请求的语法是否正确，然后从文本中将要查询的表、各种查询条件都提取出来放到 MySQL 服务器内部使用的一些数据结构上来。

这个从指定的文本中提取出我们需要的信息本质上算是一个编译过程，涉及词法解析、语法分析、语义分析等阶段

查询优化

MySQL 的优化程序会对我们的语句做一些优化，如外连接转换为内连接、表达式简化、子查询转为连接吧啦吧啦的一堆东西。优化的结果就是生成一个执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是啥样的。我们可以使用EXPLAIN 语句来查看某个语句的执行计划。

储存引擎

为了管理方便，人们把 连接管理 、 查询缓存 、 语法解析 、 查询优化 这些并不涉及真实数据存储的功能划分为 MySQL server 的功能，把真实存取数据的功能划分为 存储引擎 的功能。各种不同的存储引擎向上边的 MySQL server 层提供统一的调用接口（也就是存储引擎API），包含了几十个底层函数，像”读取索引第一条内容”、”读取索引下一条内容”、”插入记录”等等。 所以在 MySQL server 完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。

image.pngimage.png!image.png
查看当前服务器程序支持的存储引擎

1
show engines;

image.png
设置表的存储引擎

1
2
3
create table 表名(
	...
)ENGINE = 存储引擎名称;

修改表存储引擎

1
ALTER TABLE 表名 ENGINE = 存储引擎名称;

第2章 MySQL的调控按钮-启动选项和系统变量

缺省值

  • 服务器允许同时连入的客户端的默认数量是 151
  • 表的默认存储引擎是 InnoDB

命令行使用选项

1.禁止客户端使用TCP/IP网络通信

1
mysqld --skir-networking

2.修改默认存储引擎

1
2
3
4
5
6
#修改默认存储引擎
msqld --default-storage-engine=MyISAM
#创建表
create table sys_var_demo(i int);
#查看
show create table sys_var_demo;

选项的长形式和短形式
image.png

配置文件使用选项

1.配置文件路径
Windows下
image.png
Linux下
image.png

在我的计算机中这几个路径中的任意一个都可以当作配置文件来使用,如果它们不存在,你可以手动创建一个,比方说我手动在 ~/.my.cnf 这个路径下创建一个配置文件。

配置文件的优先级

不同文件：~/.my.cnf > /etc/my.cnf
同个文件内：以最后出现的配置为优先

如果我们不想让 MySQL 到默认的路径下搜索配置文件(就是上表中列出的那些),可以在命令行指定 defaults-file 选项,比如这样(以 UNIX 系统为例):

1
mysqld --defaults-file=/tmp/myconfig.txt

这样,在程序启动的时候将只在 /tmp/myconfig.txt 路径下搜索配置文件

系统变量

1
2
3
4
5
6
#查看全部
show variables
#查看最大链接数
SHOW VARIABLES like 'max_connections';
#查看默认存储引擎
SHOW VARIABLES LIKE 'default_storage_engine';

设置系统变量

可通过两种方式进行设置，命令行和配置文件（上述）

1
mysqld --default-storage-engine=MyISAM --max-connections=10

对于启动选项来说,如果启动选项名由多个单词组成,各个单词之间用短划线 - 或者下划线 _ 连接起来都可以,但是对应的系统变量之间必须使用下划线 _ 连接起来

系统变量 比较牛逼的一点就是,对于大部分系统变量来说,它们的值可以在服务器程序运行过程中进行动态修改而无需停止并重启服务器。

系统变量的作用域

有一些系统变量并不是针对单个客户端的,比如允许同时连接到服务器的客户端数量 max_connections ,查询缓存的大小 query_cache_size ,这些公有的系统变量让某个客户端私有显然不合适。

一个新连接到服务器的客户端对应的系统变量的值该怎么设置?
为了解决这两个问题,设计 MySQL 的大叔提出了系统变量的 作用范围 的概念,具体来说 作用范围 分为这两种:

  • GLOBAL :全局变量,影响服务器的整体操作。
  • SESSION :会话变量,影响某个客户端连接的操作。(注: SESSION 有个别名叫 LOCAL )
    1
    2
    3
    4
    在服务器程序运行期间通过客户端程序设置系统变量的语法:
    SET [GLOBAL|SESSION] 系统变量名 = 值;
    或者写成这样也行:
    SET [@@(GLOBAL|SESSION).]var_name = XXX;
    比如我们想在服务器运行过程中把作用范围为 GLOBAL 的系统变量 default_storage_engine 的值修改为MyISAM ,也就是想让之后新连接到服务器的客户端都用 MyISAM 作为默认的存储引擎,那我们可以选择下边两条
    语句中的任意一条来进行设置:
    1
    2
    语句一:SET GLOBAL default_storage_engine = MyISAM;
    语句二:SET @@GLOBAL.default_storage_engine = MyISAM;
    如果只想对本客户端生效,也可以选择下边三条语句中的任意一条来进行设置:
    1
    2
    3
    语句一:SET SESSION default_storage_engine = MyISAM;
    语句二:SET @@SESSION.default_storage_engine = MyISAM;
    语句三:SET default_storage_engine = MyISAM;
    从上边的 语句三 也可以看出,如果在设置系统变量的语句中省略了作用范围,默认的作用范围就是 SESSION 。
    也就是说 SET 系统变量名 = 值 和 SET SESSION 系统变量名 = 值 是等价的。

查看不同作用域的系统变量

1
2
3
4
5
SHOW [GLOBAL|SESSION] VARIABLES [LIKE 匹配的模式];
SHOW SESSION VARIABLES LIKE 'default_storage_engine';
SHOW GLOBAL VARIABLES LIKE 'default_storage_engine';
SET SESSION default_storage_engine = MyISAM;
SHOW SESSION VARIABLES LIKE 'default_storage_engine';

状态变量

为了让我们更好的了解服务器程序的运行情况, MySQL 服务器程序中维护了好多关于程序运行状态的变量,它们被称为 状态变量 。比方说 Threads_connected 表示当前有多少客户端与服务器建立了连接, Handler_update
表示已经更新了多少行记录吧啦吧啦,像这样显示服务器程序状态信息的 状态变量 还有好几百个．

所以查看 状态变量 的语句可以这么写:

1
SHOW [GLOBAL|SESSION] STATUS [LIKE 匹配的模式];

类似的,如果我们不写明作用范围,默认的作用范围是 SESSION ,比方说这样:

1
SHOW STATUS LIKE 'thread%';

image.png

第3章乱码的前世今生-字符集和比较规则

将一个字符映射成一个二进制数据的过程也叫做 编码 ,将一个二进制数据映射到一个字符的过程叫做 解码

比较规则简介

在我们确定了 xiaohaizi 字符集表示字符的范围以及编码规则后,怎么比较两个字符的大小呢?最容易想到的就是直接比较这两个字符对应的二进制编码的大小,比方说字符 ‘a’ 的编码为 0x01 ,字符 ‘b’ 的编码为 0x02 ,所以 ‘a’ 小于 ‘b’ ,这种简单的比较规则也可以被称为二进制比较规则,英文名为 binary collation 。

二进制比较规则是简单,但有时候并不符合现实需求,比如在很多场合对于英文字符我们都是不区分大小写的,也就是说 ‘a’ 和 ‘A’ 是相等的,在这种场合下就不能简单粗暴的使用二进制比较规则了,这时候我们可以这样指定比较规则:

  1. 将两个大小写不同的字符全都转为大写或者小写。
  2. 再比较这两个字符对应的二进制数据。

这是一种稍微复杂一点点的比较规则,但是实际生活中的字符不止英文字符一种,比如我们的汉字有几万之多,对于某一种字符集来说,比较两个字符大小的规则可以制定出很多种,也就是说同一种字符集可以有多种比较规则,我们稍后就要介绍各种现实生活中用的字符集以及它们的一些比较规则。

MySQL中支持的字符集和排序规则

  • MySQL中的utf8和utf8mb4

我们上边说 utf8 字符集表示一个字符需要使用14个字节,但是我们常用的一些字符使用13个字节就可以表
示了。而在 MySQL 中字符集表示一个字符所用最大字节长度在某些方面会影响系统的存储和性能,所以设计
MySQL 的大叔偷偷的定义了两个概念:

  • utf8mb3 :阉割过的 utf8 字符集,只使用1~3个字节表示字符。
  • utf8mb4 :正宗的 utf8 字符集,使用1~4个字节表示字符。

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

字符集查看

1
SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];

image.png
注意返回结果中的最后一列 Maxlen ,它代表该种字符集表示一个字符最多需要几个字节。

比较规则的查看

1
SHOW COLLATION LIKE 'utf8\_%';

image.png
比较规则的命名的规律

  • 比较规则名称以与其关联的字符集的名称开头。如上图的查询结果的比较规则名称都是以 utf8 开头的。
  • 后边紧跟着该比较规则主要作用于哪种语言,比如 utf8_polish_ci 表示以波兰语的规则比较,utf8_spanish_ci 是以西班牙语的规则比较, utf8_general_ci 是一种通用的比较规则。
  • 名称后缀意味着该比较规则是否区分语言中的重音、大小写啥的,具体可以用的值如下:
    1
    2
    3
    4
    5
    6
    7
    8
    |后缀|英文释义|描述| 
    |:--:|:--:|:--:| 
    | _ai | accent insensitive |不区分重音|
    | _as | accent sensitive |区分重音| 
    | _ci | case insensitive |不区分大小写| 
    | _cs | case sensitive |区分大小写| 
    | _bin | binary |以二进制方式比较|

    比如 utf8_general_ci 这个比较规则是以 ci 结尾的,说明不区分大小写。
    每种字符集对应若干种比较规则,每种字符集都有一种默认的比较规则, SHOW COLLATION 的返回结果中的Default 列的值为 YES 的就是该字符集的默认比较规则,比方说 utf8 字符集默认的比较规则就是utf8_general_ci 。

MySQL 有4个级别的字符集和比较规则,分别是:

  • 服务器级别
  • 数据库级别
  • 表级别
  • 列级别

查看比较规则系统变量

1
SHOW VARIABLES LIKE 'collation_server';

指定比较规则和修改比较规则

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#数据库级别
CREATE DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称];

ALTER DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称];

#表级别
CREATE TABLE 表名 (列的信息)
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称]]

ALTER TABLE 表名
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称]

#列级别
CREATE TABLE 表名(
列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
其他列...
);

ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

character_set_database 和 collation_database 这两个系统变量是只读的,我们不能通过修改这两个变量的值而改变当前数据库的字符集和比较规则

  • 只修改字符集,则比较规则将变为修改后的字符集默认的比较规则。

  • 只修改比较规则,则字符集将变为修改后的比较规则对应的字符集

  • 如果创建或修改列时没有显式的指定字符集和比较规则,则该列默认用表的字符集和比较规则

  • 如果创建或修改表时没有显式的指定字符集和比较规则,则该表默认用数据库的字符集和比较规则

  • 如果创建或修改数据库时没有显式的指定字符集和比较规则,则该数据库默认用服务器的字符集和比较规则

我们知道字符 ‘我’ 在 utf8 字符集编码下的字节串长这样: 0xE68891 ,如果一个程序把这个字节串发送到另一个程序里,另一个程序用不同的字符集去解码这个字节串,假设使用的是 gbk 字符集来解释这串字节,解码过程
就是这样的:

  1. 首先第一个字节 0xE6 ,它的值大于 0x7F (十进制:127),说明是两字节编码,继续读一字节后是0xE688 ,然后从 gbk 编码表中查找字节为 0xE688 对应的字符,发现是字符 ‘鎴’
  2. 继续读一个字节 0x91 ,它的值也大于 0x7F ,再往后读一个字节发现木有了,所以这是半个字符。
  3. 所以 0xE68891 被 gbk 字符集解释成一个字符 ‘鎴’ 和半个字符。

假设用 iso-8859-1 ,也就是 latin1 字符集去解释这串字节,解码过程如下:

1.先读第一个字节 0xE6 ,它对应的 latin1 字符为 æ 。
2.再读第二个字节 0x88 ,它对应的 latin1 字符为 ˆ 。
3.再读第二个字节 0x91 ,它对应的 latin1 字符为 ‘ 。
4.所以整串字节 0xE68891 被 latin1 字符集解释后的字符串就是 ‘我’

可见,如果对于同一个字符串编码和解码使用的字符集不一样,会产生意想不到的结果,作为人类的我们看上去就像是产生了乱码一样。

MySQL中字符集的转换
我们知道从客户端发往服务器的请求本质上就是一个字符串,服务器向客户端返回的结果本质上也是一个字符串,而字符串其实是使用某种字符集编码的二进制数据。这个字符串可不是使用一种字符集的编码方式一条道走到黑的,从发送请求到返回结果这个过程中伴随着多次字符集的转换,在这个过程中会用到3个系统变量.
image.png
处理过程
image.png
为了方便我们设置， MySQL 提供了一条非常简便的语句： 

1
SET NAMES 字符集名;

这一条语句产生的效果和我们执行这3条的效果是一样的： 

1
2
3
SET character_set_client = 字符集名; 
SET character_set_connection = 字符集名; 
SET character_set_results = 字符集名;

比方说我的客户端使用的是 utf8 字符集，所以需要把这几个系统变量的值都设置为 utf8 ： 

1
SET NAMES utf8;

第4章从一条记录说起-InnoDB记录结构

简介

InnoDB 是一个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启我们的数据还是存在的。而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。而我们知道读写磁盘的速度非常慢，和内存读写差了几个数量级，所以当我们想从表中获取某些记录时， InnoDB 存储引擎需要一条一条的把记录从磁盘上读出来么？不，那样会慢死， InnoDB 采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小 一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB 内容刷新到磁盘中。

InnoDB行格式

我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为 行格式 或者 记录格式 。 设计 InnoDB 存储引擎的大叔们到现在为止设计了4种不同类型的行格式 ，分别是 **Compact**(紧凑) 、 **Redundant**(冗余) 、 **Dynamic**(动态) 和 **Compressed**(压缩) 行格式，随着时间的推移，他们可能会设计出更多的行格式，但是不管怎么变，在原理上大体都是相同的。

页是 MySQL 中磁盘和内存交互的基本单位，也是 MySQL 是管理存储空间的基本单位。

指定和修改行格式的语法如下： 

1
2
CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称 
ALTER TABLE 表名 ROW_FORMAT=行格式名称

InnoDB 目前定义了4种行格式

Row Format Compact Storage Characteristics Enhanced Variable-Length Column Storage Large Index Key Prefix Support Compression Support Supported Tablespace Types
REDUNDANT No No No No system, file-per-table, general
COMPACT Yes No No No system, file-per-table, general
DYNAMIC Yes Yes Yes No system, file-per-table, general
COMPRESSED Yes Yes Yes Yes file-per-table, general

查看表的行结构

1
show table status like 'table_name';

响应结果

1
2
3
4
5
6
7
8
9
10
11
+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+-----------------------+
| Name           | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment               |
+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+-----------------------+
| sys_dictionary | InnoDB |      10 | Compact    |    2 |           8192 |       16384 |               0 |        16384 |         0 |              3 | 2022-07-08 11:15:08 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                | 系统配置字典表          |
+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+-----------------------+

+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+--------------------+---------+
| Name           | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation       | Checksum | Create_options     | Comment |
+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+--------------------+---------+
| xxxxxxxx_order | MyISAM |      10 | Dynamic    | 1213 |            117 |      142772 | 281474976710655 |        80896 |         0 |          12673 | 2022-05-06 11:42:18 | 2022-09-05 16:44:10 | 2022-05-06 11:42:18 | utf8_general_ci |     NULL | row_format=DYNAMIC |         |
+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+--------------------+---------+

具体的落地细节可以参考 ：《MySQL技术内幕InnoDB存储引擎》的 4.3 章

Compact（紧凑）行格式

具体组成如图：
image.png

记录的额外信息

这部分信息是服务器为了描述这条记录而不得不额外添加的一些信息，这些额外信息分为3类，分别是 变长字段长度列表 、 NULL值列表 和 记录头信息

  • **变长字段长度列表 **

该部分存储的是一些可变长的字段如varchar、varbinary、各种text类型和各种blob类型，变长字段的存储字节大小是不固定的

在 Compact 行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长 字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放，我们再次强调一遍，是逆序存放！

如插入一条记录
image.png
其存储的示意图为
image.png

变长字段长度列表中只存储值为 非NULL 的列内容占用的长度，值为 NULL 的列的长度是不储存的

  • **NULL值列表 **

在我们插入数据的时候有些字段的值是NULL的，如果把这些NULL的值放到记录的真实数据中会很占空间，所以Compact行格式把这些NULL的列统一管理起来，存储到NULL值列表中

处理过程：

1.先统计表中允许存储 NULL 的列有哪些
2.如果表中没有允许存储 NULL 的列，则 NULL值列表 也不存在了，否则将每个允许存储 NULL 的列对应一个 二进制位，二进制位按照列的顺序逆序排列，二进制位表示的意义如下：

二进制位的值为 1 时，代表该列的值为 NULL 。
二进制位的值为 0 时，代表该列的值不为 NULL 。

字段c1、c2、c3的值设置为允许为NULL时示意图如下
image.png
MySQL 规定 NULL值列表 必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节 的高位补 0 。

image.png

  • 记录头信息

是由固定的 5 个字节组 成。 5 个字节也就是 40 个二进制位，不同的位代表不同的意思，如图：

image.png
二进制位代表的详细信息
截屏2022-09-09 11.45.35.png

记录的真实数据

MySQL 会为每个记录默认的添加一些列（也称为 隐藏列 ），具体的列如下：

image.png
主键的生成策略：优先使用用户自定义主键作为主键，如果用户没有定义主键，则 选取一个 Unique 键作为主键，如果表中连 Unique 键都没有定义的话，则 InnoDB 会为表默认添加一个名为 row_id 的隐藏列作为主键。

所以我们从上表中可以看出：InnoDB存储引擎会为每条记录都添加 transaction_id 和 roll_pointer 这两个列，但是 row_id 是可选的（在没有自定义主键以及Unique键的情况下才会添加该列）。 这些隐藏列的值不用我们操心， InnoDB 存储引擎会自己帮我们生成的。

image.png

Redundant（冗余）行格式

Redundant 行格式是 MySQL5.0 之前用的一种行格式，也就是说它已经非常老了

行格式示意图
image.png

Redundant和Compact的区别就是其 “记录的额外信息” 并没有记录NULL列表和记录的是字段长度的偏移列表

image.png

Redundant和Compact的对比

  • 字段长度偏移列表

    Compact 行格式的开头是 变长字段长度列表 ，而 Redundant 行格式的开头是 字段长度偏移列表 ，与 变长字段长度列表 有两处不同：

  1. 没有了变长两个字，意味着 Redundant 行格式会把该条记录中所有列（包括 隐藏列 ）的长度信息都按 照逆序存储到 字段长度偏移列表 。

  2. 多了个偏移两个字，这意味着计算列值长度的方式不像 Compact 行格式那么直观，它是采用两个相邻数 值的差值来计算各个列值的长度。

    比如第一条记录的 字段长度偏移列表 就是：

25 24 1A 17 13 0C 06 因为它是逆序排放的，所以按照列的顺序排列就是：
06 0C 13 17 1A 24 25 按照两个相邻数值的差值来计算各个列值的长度的意思就是：

第一列(row_id)的长度就是 0x06个字节，也就是6个字节。
第二列(transaction_id)的长度就是 (0x0C - 0x06)个字节，也就是6个字节。
第三列(roll_pointer)的长度就是 (0x13 - 0x0C)个字节，也就是7个字节。
第四列(c1)的长度就是 (0x17 - 0x13)个字节，也就是4个字节。
第五列(c2)的长度就是 (0x1A - 0x17)个字节，也就是3个字节。
第六列(c3)的长度就是 (0x24 - 0x1A)个字节，也就是10个字节。
第七列(c4)的长度就是 (0x25 - 0x24)个字节，也就是1个字节。

  • 记录头信息

Redundant 行格式的记录头信息占用 6 字节， 48 个二进制位，这些二进制位代表的意思如下

名称 大小（单位：bit） 描述
预留位1 1 没有使用
预留位2 1 没有使用
delete_mask 1 标记该记录是否被删除
min_rec_mask 1 B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned 4 表示当前记录拥有的记录数
heap_no 13 表示当前记录在页面堆的位置信息
n_field 10 表示记录中列的数量
1byte_offs_flag 1 标记字段长度偏移列表中每个列对应的偏移量是使用1字节还是2字节表示的
next_record 16 表示下一条记录的相对位置

第一条记录中的头信息是： 00 00 10 0F 00 BC
根据这六个字节可以计算出各个属性的值，如下： 

1
2
3
4
5
6
7
8
9
预留位1：0x00 
预留位2：0x00 
delete_mask: 0x00 
min_rec_mask: 0x00 
n_owned: 0x00 
heap_no: 0x02 
n_field: 0x07 1
byte_offs_flag: 0x01 
next_record:0xBC

与 Compact 行格式的记录头信息对比来看，有两处不同：

  1. Redundant 行格式多了 n_field 和 1byte_offs_flag 这两个属性。
  2. Redundant 行格式没有 record_type 这个属性。

字段长度偏移列表 实质上是存储每个列中的值占用的空间在 记录的真实数据 处结束的位置

第一条记录为例， 0x06 代表第一个列在 记录的真实数据 第6个字节处结束， 0x0C 代 表第二个列在 记录的真实数据 第12个字节处结束， 0x13 代表第三个列在 记录的真实数据 第19个字节处结 束，等等等等，最后一个列对应的偏移量值为 0x25 ，也就意味着最后一个列在 记录的真实数据 第37个字 节处结束，也就意味着整条记录的 真实数据 实际上占用 37 个字节。

当记录的真实数据占用的字节数不大于127（十六进制 0x7F ，二进制 01111111 ）时，每个列对应的偏 移量占用1个字节。

  • Redundant 行格式中 NULL 值的处理

因为 Redundant 行格式并没有 NULL值列表 ，所以设计 Redundant 行格式的大叔在 字段长度偏移列表 中的 各个列对应的偏移量处做了一些特殊处理 —— 将列对应的偏移量值的第一个比特位作为是否为 NULL 的依 据，该比特位也可以被称之为 NULL比特位 。也就是说在解析一条记录的某个列时，首先看一下该列对应的 偏移量的 NULL比特位 是不是为 1 ，如果为 1 ，那么该列的值就是 NULL ，否则不是 NULL 。

行溢出数据

在 Compact 和 Reduntant 行格式中，对于占用存储空间非常大的列，在 记录的真实数据 处只会存储该列的一部 分数据，把剩余的数据分散存储在几个其他的页中，然后 记录的真实数据 处用20个字节存储指向这些页的地址 （当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页， 如图所示：
image.png

不只是 VARCHAR(M) 类型的列，其他的 TEXT、BLOB 类型的列在存储数据非常多的时候 也会发生 行溢出

MySQL 中规定一个页中至少存放两行记录

Dynamic（动态）行格式

行格式提供与DYNAMIC行格式相同的存储特性，COMPACT但为长可变长度列增加了增强的存储能力，并支持大索引键前缀。

当使用 , 创建表时 ROW_FORMAT=DYNAMIC，InnoDB 可以完全离页存储 long 可变长度列值（对于 VARCHAR、 VARBINARY和 BLOBand TEXT类型），聚集索引记录仅包含指向溢出页的 20 字节指针。大于或等于 768 字节的固定长度字段被编码为可变长度字段。例如， CHAR(255)如果字符集的最大字节长度大于 3，则列可以超过 768 个字节，就像utf8mb4.

列是否存储在页外取决于页面大小和行的总大小。当一行太长时，选择最长的列进行页外存储，直到聚集索引记录适合B 树页面。 小于或等于 40 字节的列存储在行中 TEXT。 BLOB如果合适的DYNAMIC话，行格式保持了将整行存储在索引节点中的效率（就像 COMPACT和REDUNDANT 格式一样），但是DYNAMIC行格式避免了用长列的大量数据字节填充B树节点的问题。行DYNAMIC格式基于这样一种思想，即如果长数据值的一部分存储在页外，则将整个值存储在页外通常是最有效的。使用DYNAMIC格式，较短的列可能会保留在 B 树节点中，从而最大限度地减少给定行所需的溢出页数。

DYNAMIC行格式支持最多 3072 字节的索引键前缀 。

使用DYNAMIC行格式的表可以存储在系统表空间、file-per-table 表空间和通用表空间中。要将DYNAMIC表存储在系统表空间中，请禁用 innodb_file_per_table并使用常规CREATE TABLE 或者 ALTER TABLE语句，或者将TABLESPACE [=] innodb_systemtable 选项与CREATE TABLE或一起使用ALTER TABLE。该 innodb_file_per_table变量不适用于一般表空间，也不适用于使用TABLESPACE [=] innodb_systemtable 选项将DYNAMIC表存储在系统表空间中。

动态行格式存储特性

DYNAMIC行格式是行格式的一种变 体COMPACT。有关存储特性，请参阅 COMPACT 行格式存储特性。

Compressed（压缩）行格式

行格式提供与COMPRESSED行格式相同的存储特性和功能， DYNAMIC但增加了对表和索引数据压缩的支持。

行格式对页外存储使用与COMPRESSED行格式类似的内部细节，DYNAMIC但表和索引数据被压缩并使用更小的页面大小需要额外的存储和性能考虑。使用COMPRESSED行格式，该 KEY_BLOCK_SIZE选项控制在聚集索引中存储多少列数据，以及在溢出页上放置多少。有关 COMPRESSED行格式的更多信息，请参阅 第 15.9 节，“InnoDB 表和页面压缩”。

COMPRESSED行格式支持最多 3072 字节的索引键前缀 。

COMPRESSED可以在 file-per-table 表空间或通用表空间中创建 使用行格式的表。系统表空间不支持 COMPRESSED行格式。要将 COMPRESSED表存储在 file-per-table 表空间中， innodb_file_per_table必须启用该变量。该 innodb_file_per_table变量不适用于一般表空间。通用表空间支持所有行格式，但需要注意的是，由于物理页大小不同，压缩表和未压缩表不能在同一个通用表空间中共存。有关更多信息，请参阅 第 15.6.3.3 节，“通用表空间”。

压缩行格式存储特性

COMPRESSED行格式是行格式的一种变 体COMPACT。有关存储特性，请参阅 COMPACT 行格式存储特性。

后面这两种行格式类似于 COMPACT行格式 ，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实 数据处存储字符串的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存
储其他页面的地址。 另外， Compressed 行格式会采用压缩算法对页面进行压缩。

一个页一般是 16KB ，当记录中的数据太多，当前页放不下的时候，会把多余的数据存储到其他页中，这种现象称为行溢出 。

第5章盛放记录的大盒子-InnoDB数据页结构

不同类型的页简介

前边我们简单提了一下 页 的概念,它是 InnoDB 管理存储空间的基本单位,一个页的大小一般是 16KB 。

InnoDB 为了不同的目的而设计了许多种不同类型的 页 ,比如存放表空间头部信息的页,存放 Insert Buffer信息的页,存放 INODE 信息的页,存放 undo 日志信息的页等等等等。当然了,如果我说的这些名词你一个都没有听过,就当我放了个屁吧~ 不过这没有一毛钱关系,我们今儿个也不准备说这些类型的页,我们聚焦的是那些存放我们表中记录的那种类型的页,官方称这种存放记录的页为索引( INDEX )页,鉴于我们还没有了解过索引是个什么东西,而这些表中的记录就是我们日常口中所称的 数据 ,所以目前还是叫这种存放记录的页为 数据页吧。

image.png
从图中可以看出,一个 InnoDB 数据页的存储空间大致被划分成了 7 个部分,有的部分占用的字节数是确定的,有的部分占用的字节数是不确定的。
image.png
在页的7个组成部分中,我们自己存储的记录会按照我们指定的 行格式 存储到 User Records 部分。但是在一开始生成页的时候,其实并没有 User Records 这个部分,每当我们插入一条记录,都会从 Free Space 部分分配,也就是尚未使用的存储空间中申请一个记录大小的空间划分到 User Records 部分,当 Free Space 部分的空间全部被 User Records 部分替代掉之后,也就意味着这个页使用完了,如果还有新的记录插入的话,就需要去申请新的页了,这个过程的图示如下:

image.png

1
2
3
4
5
6
CREATE TABLE page_demo(
 c1 INT,
 c2 INT,
 c3 VARCHAR(10000),
 PRIMARY KEY (c1)
) CHARSET=ascii ROW_FORMAT=Compact;

创建的 page_demo 表有3个列，其中 c1 和 c2 列是用来存储整数的， c3 列是用来存储字符串的。需要注 意的是，我们把 c1 列指定为主键，所以在具体的行格式中InnoDB就没必要为我们去创建那个所谓的 row_id 隐 藏列了。而且我们为这个表指定了 ascii 字符集以及 Compact 的行格式。所以这个表中记录的行格式示意图就是 这样的
image.png
从图中可以看到,我们特意把 记录头信息 的5个字节的数据给标出来了,说明它很重要,我们再次先把这些 记录头信息 中各个属性的大体意思浏览一下(我们目前使用 Compact 行格式进行演示):
image.png
行格式简化图
image.png
向 page_demo 表中插入几条记录:

1
2
INSERT INTO page_demo VALUES(1, 100, 'aaaa'), (2, 200, 'bbbb'), (3, 300, 'cccc'),
(4, 400, 'dddd');

为了方便大家分析这些记录在 页 的 User Records 部分中是怎么表示的,我把记录中头信息和实际的列数据都用十进制表示出来了(其实是一堆二进制位),所以这些记录的示意图就是:
image.png

  • delete_mask

    这个属性标记着当前记录是否被删除,占用1个二进制位,值为 0 的时候代表记录并没有被删除,为 1 的时候代表记录被删除掉了。啥?被删除的记录还在 页 中么?是的,摆在台面上的和背地里做的可能大相径庭,你以为它删除了,可它还在真实的磁盘上。这些被删除的记录之所以不立即从磁盘上移除,是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗,所以只是打一个删除标记而已,所有被删除掉的记录都会组成一个所谓的 垃圾链表 ,在这个链表中的记录占用的空间称之为所谓的 可重用空间 ,之后如果有新记录插入到表中的话,可能把这些被删除的记录占用的存储空间覆盖掉。

  • min_rec_mask

    B+树的每层非叶子节点中的最小记录都会添加该标记。反正我们自己插入的四条记录的 in_rec_mask 值都是 0 ,意味着它们都不是 B+ 树的非叶子节点中的最小记录。

  • heap_no

    这个属性表示当前记录在本 页 中的位置,从图中可以看出来,我们插入的4条记录在本 页 中的位置分别是: 2 、 3 、 4 、 5 。是不是少了点啥?是的,怎么不见 heap_no 值为 0 和 1 的记录呢?这其实是设计 InnoDB 的大叔们玩的一个小把戏,他们自动给每个页里边儿加了两个记录,由于这两个记录并不是我们自己插入的,所以有时候也称为 伪记录 或者 虚拟记录 。这两个伪记录一个代表 最小记录 ,一个代表 最大记录 ,等一下哈~,记录可以比大小么?是的,记录也可以比大小,对于一条完整的记录来说,比较记录的大小就是比较 主键 的大小。比方说我们插入的4行记录的主键值分别是: 1 、 2 、 3 、 4 ,这也就意味着这4条记录的大小从小到大依次递增。

小贴士:请注意我强调了对于一条完整的记录来说,比较记录的大小就相当于比的是主键的大小。

但是不管我们向 页 中插入了多少自己的记录,设计 InnoDB 的大叔们都规定他们定义的两条伪记录分别为最小记录与最大记录。这两条记录的构造十分简单,都是由5字节大小的 记录头信息 和8字节大小的一个固定的部分组成的,如图所示
image.png
由于这两条记录不是我们自己定义的记录,所以它们并不存放在 页 的 User Records 部分,他们被单独放在一个称为 Infimum + Supremum 的部分,如图所示:
image.png
从图中我们可以看出来,最小记录和最大记录的 heap_no 值分别是 0 和 1 ,也就是说它们的位置最靠前。

  • record_type

    这个属性表示当前记录的类型,一共有4种类型的记录, 0 表示普通记录, 1 表示B+树非叶节点记录, 2 表示最小记录, 3 表示最大记录。从图中我们也可以看出来,我们自己插入的记录就是普通记录,它们的record_type 值都是 0 ,而最小记录和最大记录的 record_type 值分别为 2 和 3 。至于 record_type 为 1 的情况,我们之后在说索引的时候会重点强调的。

  • next_record

    表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。比方说第一条记录的 next_record 值为 32 ,意味着从第一条记录的真实数据的地址处向后找 32 个字节便是下一条记录的真实数据。如果你熟悉数据结构的话,就立即明白了,这其实是个 链表 ,可以通过一条记录找到它的下一条记录。但是需要注意注意再注意的一点是, 下一条记录 指得并不是按照我们插入顺序的下一条记录,而是按照主键值由小到大的顺序的下一条记录。而且规定 Infimum 记录(也就是最小记录) 的下一条记录就是本页中主键值最小的用户记录,而本页中主键值最大的用户记录的下一条记录就是 Supremum 记录(也就是最大记录) ,为了更形象的表示一下这个 next_record 起到的作用,我们用箭头来替代一下next_record 中的地址偏移量:

image.png
从图中可以看出来,我们的记录按照主键从小到大的顺序形成了一个单链表。 最大记录 的 next_record 的值为 0 ,这也就是说最大记录是没有 下一条记录 了,它是这个单链表中的最后一个节点。如果从中删除掉
一条记录,这个链表也是会跟着变化的,比如我们把第2条记录删掉:

1
mysql> DELETE FROM page_demo WHERE c1 = 2;

删掉第2条记录后的示意图就是:
image.png
从图中可以看出来,删除第2条记录前后主要发生了这些变化:

  • 第2条记录并没有从存储空间中移除,而是把该条记录的 delete_mask 值设置为 1 。
  • 第2条记录的 next_record 值变为了0,意味着该记录没有下一条记录了。
  • 第1条记录的 next_record 指向了第3条记录。
  • 还有一点你可能忽略了,就是 最大记录 的 n_owned 值从 5 变成了 4 ,关于这一点的变化我们稍后会详细说明的。

所以,不论我们怎么对页中的记录做增删改操作,InnoDB始终会维护一条记录的单链表,链表中的各个节点是按照主键值由小到大的顺序连接起来的。

小贴士:你会不会觉得next_record这个指针有点儿怪,为啥要指向记录头信息和真实数据之间的位置呢?为啥不干脆指向整条记录的开头位置,也就是记录的额外信息开头的位置呢?因为这个位置刚刚好,向左读取就是记录头信息,向右读取就是真实数据。我们前边还说过变长字段长度列表、NULL值列表中的信息都是逆序存放,这样可以使记录中位置靠前的字段和它们对应的字段长度信息在内存中的距离更近,可能会提高高速缓存的命中率。当然如果你看不懂这句话的话就不要勉强了,果断跳过~

再来看一个有意思的事情,因为主键值为 2 的记录被我们删掉了,但是存储空间却没有回收,如果我们再次把这条记录插入到表中,会发生什么事呢?

1
mysql> INSERT INTO page_demo VALUES(2, 200, 'bbbb');

我们看一下记录的存储情况:
image.png
从图中可以看到, InnoDB 并没有因为新记录的插入而为它申请新的存储空间,而是直接复用了原来被删除记录的存储空间。

小贴士:当数据页中存在多条被删除掉的记录时,这些记录的next_record属性将会把这些被删除掉的记录组成一个垃圾链表,以备之后重用这部分存储空间。

Page Directory(页目录)

目录制作过程：

  1. 将所有正常的记录(包括最大和最小记录,不包括标记为已删除的记录)划分为几个组。

  2. 每个组的最后一条记录(也就是组内最大的那条记录)的头信息中的 n_owned 属性表示该记录拥有多少条记录,也就是该组内共有几条记录。

  3. 将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近 页 的尾部的地方,这个地方就是所谓的 Page Directory ,也就是 页目录 (此时应该返回头看看页面各个部分的图)。页面目录中的这些地址偏移量被称为 槽 (英文名: Slot ),所以这个页面目录就是由 槽 组成的。

比方说现在的 page_demo 表中正常的记录共有6条, InnoDB 会把它们分成两组,第一组中只有一个最小记录,第二组中是剩余的5条记录,看下边的示意图:
image.png
现在 页目录 部分中有两个槽,也就意味着我们的记录被分成了两个组, 槽1 中的值是 112 ,代表最大记录的地址偏移量(就是从页面的0字节开始数,数112个字节); 槽0 中的值是 99 ,代表最小记录的地址偏移量。
注意最小和最大记录的头信息中的 n_owned 属性

  • 最小记录的 n_owned 值为 1 ,这就代表着以最小记录结尾的这个分组中只有 1 条记录,也就是最小记录本身。
  • 最大记录的 n_owned 值为 5 ,这就代表着以最大记录结尾的这个分组中只有 5 条记录,包括最大记录本身还有我们自己插入的 4 条记录。

99 和 112 这样的地址偏移量很不直观,我们用箭头指向的方式替代数字,这样更易于我们理解,所以修改后的
示意图就是这样:
image.png
单纯从逻辑上看一下这些记录和页目录的关系
image.png

对于最小记录所在的分组只能有 1 条记录,最大记录所在的分组拥有的记录条数只能在 18 条之间,剩下的分组中记录的条数范围只能在是 48 条之间。

步骤:

  • 初始情况下一个数据页里只有最小记录和最大记录两条记录,它们分属于两个分组。
  • 之后每插入一条记录,都会从 页目录 中找到主键值比本记录的主键值大并且差值最小的槽,然后把该槽对应的记录的 n_owned 值加1,表示本组内又添加了一条记录,直到该组中的记录数等于8个。
  • 在一个组中的记录数等于8个后再插入一条记录时,会将组中的记录拆分成两个组,一个组中4条记录,另一个5条记录。这个过程会在 页目录 中新增一个 槽 来记录这个新增分组中最大的那条记录的偏移量。

由于现在 page_demo 表中的记录太少,无法演示添加了 页目录 之后加快查找速度的过程,所以再往 page_demo表中添加一些记录:

1
2
3
INSERT INTO page_demo VALUES(5, 500, 'eeee'), (6, 600, 'ffff'), (7, 700, 'gggg'),
(8, 800, 'hhhh'), (9, 900, 'iiii'), (10, 1000, 'jjjj'), (11, 1100, 'kkkk'), (12, 1200, 'l
lll'), (13, 1300, 'mmmm'), (14, 1400, 'nnnn'), (15, 1500, 'oooo'), (16, 1600, 'pppp');

哈,我们一口气又往表中添加了12条记录,现在页里边就一共有18条记录了(包括最小和最大记录),这些记录
被分成了5个组,如图所示:
image.png
比方说我们想找主键值为 6 的记录,过程是这样的:

  1. 计算中间槽的位置: (0+4)/2=2 ,所以查看 槽2 对应记录的主键值为 8 ,又因为 8 > 6 ,所以设置high=2 , low 保持不变。
  2. 重新计算中间槽的位置: (0+2)/2=1 ,所以查看 槽1 对应的主键值为 4 ,又因为 4 < 6 ,所以设置low=1 , high 保持不变。
  3. 因为 high - low 的值为1,所以确定主键值为 5 的记录在 槽2 对应的组中。此刻我们需要找到 槽2 中主键值最小的那条记录,然后沿着单向链表遍历 槽2 中的记录。但是我们前边又说过,每个槽对应的记录都是该组中主键值最大的记录,这里 槽2 对应的记录是主键值为 8 的记录,怎么定位一个组中最小的记录呢?别忘了各个槽都是挨着的,我们可以很轻易的拿到 槽1 对应的记录(主键值为 4 ),该条记录的下一条记录就是 槽2 中主键值最小的记录,该记录的主键值为 5 。所以我们可以从这条主键值为 5 的记录出发,遍历 槽2 中的各条记录,直到找到主键值为 6 的那条记录即可。由于一个组中包含的记录条数只能是1~8条,所以遍历一个组中的记录的代价是很小的。

所以在一个数据页中查找指定主键值的记录的过程分为两步:
1. 通过二分法确定该记录所在的槽,并找到该槽中主键值最小的那条记录。
2. 通过记录的 next_record 属性遍历该槽所在的组中的各个记录。

Page Header(页面头部)

设计 InnoDB 的大叔们为了能得到一个数据页中存储的记录的状态信息,比如本页中已经存储了多少条记录,第一条记录的地址是什么,页目录中存储了多少个槽等等,特意在页中定义了一个叫 Page Header 的部分,它是
页 结构的第二部分,这个部分占用固定的 56 个字节,专门存储各种状态信息,具体各个字节都是干嘛的看下
image.png
PAGE_DIRECTION
假如新插入的一条记录的主键值比上一条记录的主键值大,我们说这条记录的插入方向是右边,反之则是左边。用来表示最后一条记录插入方向的状态就是 PAGE_DIRECTION 。

PAGE_N_DIRECTION
假设连续几次插入新记录的方向都是一致的, InnoDB 会把沿着同一个方向插入记录的条数记下来,这个条数就用 PAGE_N_DIRECTION 这个状态表示。当然,如果最后一条记录的插入方向改变了的话,这个状态的值会被清零重新统计。

File Header(文件头部)

我们现在描述的 File Header 针对各种类型的页都通用,也就是说不同类型的页都会以 File Header 作为第一个组成部分,它描述了一些针对各种页都通用的一些信息,比方说这个页的编号是多少,它的上一个页、下一个页是谁啦吧啦吧啦~ 这个部分占用固定的 38 个字节,是由下边这些内容组成的:
image.png
对照着这个表格,我们看几个目前比较重要的部分:

  • FIL_PAGE_SPACE_OR_CHKSUM

这个代表当前页面的校验和(checksum)。啥是个校验和?就是对于一个很长很长的字节串来说,我们会通过某种算法来计算一个比较短的值来代表这个很长的字节串,这个比较短的值就称为 校验和 。这样在比较两个很长的字节串之前先比较这两个长字节串的校验和,如果校验和都不一样两个长字节串肯定是不同的,所以省去了直接比较两个比较长的字节串的时间损耗。

  • FIL_PAGE_OFFSET

每一个 页 都有一个单独的页号,就跟你的身份证号码一样, InnoDB 通过页号来可以唯一定位一个页 。

  • FIL_PAGE_TYPE

这个代表当前 页 的类型,我们前边说过, InnoDB 为了不同的目的而把页分为不同的类型,我们上边介绍的其实都是存储记录的 数据页 ,其实还有很多别的类型的页,具体如下表:

类型名称 十六进制 描述
FIL_PAGE_TYPE_ALLOCATED 0x0000 最新分配,还没使用
FIL_PAGE_UNDO_LOG 0x0002 Undo日志页
FIL_PAGE_INODE 0x0003 段信息节点
FIL_PAGE_IBUF_FREE_LIST 0x0004 Insert Buffer空闲列表
FIL_PAGE_IBUF_BITMAP 0x0005 Insert Buffer位图
FIL_PAGE_TYPE_SYS 0x0006 系统页
FIL_PAGE_TYPE_TRX_SYS 0x0007 事务系统数据
FIL_PAGE_TYPE_FSP_HDR 0x0008 表空间头部信息
FIL_PAGE_TYPE_XDES 0x0009 扩展描述页
FIL_PAGE_TYPE_BLOB 0x000A BLOB页
FIL_PAGE_INDEX 0x45BF 索引页,也就是我们所说的 数据页

我们存放记录的数据页的类型其实是 FIL_PAGE_INDEX ,也就是所谓的 索引页 。

  • FIL_PAGE_PREV 和 FIL_PAGE_NEXT

我们前边强调过, InnoDB 都是以页为单位存放数据的,有时候我们存放某种类型的数据占用的空间非常大(比方说一张表中可以有成千上万条记录), InnoDB 可能不可以一次性为这么多数据分配一个非常大的存储空间,如果分散到多个不连续的页中存储的话需要把这些页关联起来, FIL_PAGE_PREV 和 FIL_PAGE_NEXT就分别代表本页的上一个和下一个页的页号。这样通过建立一个双向链表把许许多多的页就都串联起来了,而无需这些页在物理上真正连着。需要注意的是,并不是所有类型的页都有上一个和下一个页的属性,不过我们本集中唠叨的 数据页 (也就是类型为 FIL_PAGE_INDEX 的页)是有这两个属性的,所以所有的数据页其
实是一个双链表,就像这样:
image.png

File Trailer

我们知道 InnoDB 存储引擎会把数据存储到磁盘上,但是磁盘速度太慢,需要以 页 为单位把数据加载到内存中处理,如果该页中的数据在内存中被修改了,那么在修改后的某个时间需要把数据同步到磁盘中。但是在同步了一半的时候中断电了咋办,这不是莫名尴尬么?为了检测一个页是否完整(也就是在同步的时候有没有发生只同步一半的尴尬情况),设计 InnoDB 的大叔们在每个页的尾部都加了一个 File Trailer 部分,这个部分由 8 个字节组成,可以分成2个小部分:

  • 前4个字节代表页的校验和这个部分是和 File Header 中的校验和相对应的。每当一个页面在内存中修改了,在同步之前就要把它的校验和算出来,因为 File Header 在页面的前边,所以校验和会被首先同步到磁盘,当完全写完时,校验和也会被写到页的尾部,如果完全同步成功,则页的首部和尾部的校验和应该是一致的。如果写了一半儿断电了,那么在 File Header 中的校验和就代表着已经修改过的页,而在 File Trialer 中的校验和代表着原先的页,二者不同则意味着同步中间出了错。

  • 后4个字节代表页面被最后修改时对应的日志序列位置(LSN)这个部分也是为了校验页的完整性的,只不过我们目前还没说 LSN 是个什么意思,所以大家可以先不用管这个属性。

这个 File Trailer 与 File Header 类似,都是所有类型的页通用的。

第6章快速查询的秘籍-B+树索引

各个数据页可以组成一个 双向链表 ,而每个数据页中的记录会按照主键值从小到大的顺序组成一个 单向链表 ,每个数据页都会为存储在它里边儿的记录生成一个页目录 ,在通过主键查找某条记录的时候可以在 页目录 中使用二分法快速定位到对应的槽,然后再遍历该槽对应分组中的记录即可快速找到指定的记录
image.png
其中页a、页b、页c … 页n 这些页可以不在物理结构上相连,只要通过双向链表相关联即可。

没有索引的查找

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

在一个页中的查找

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

  • 以主键为搜索条件

    这个查找过程我们已经很熟悉了,可以在 页目录 中使用二分法快速定位到对应的槽,然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

  • 以其他列作为搜索条件

    对非主键列的查找的过程可就不这么幸运了,因为在数据页中并没有对非主键列建立所谓的 页目录 ,所以我们无法通过二分法快速定位相应的 槽 。这种情况下只能从 最小记录 开始依次遍历单链表中的每条记录,然后对比每条记录是不是符合搜索条件。很显然,这种查找的效率是非常低的。

在很多页中查找

大部分情况下我们表中存放的记录都是非常多的,需要好多的数据页来存储这些记录。在很多页中查找记录的话可以分为两个步骤:
1. 定位到记录所在的页。
2. 从所在的页内中查找相应的记录。
在没有索引的情况下,不论是根据主键列或者其他列的值进行查找,由于我们并不能快速的定位到记录所在的页,所以只能从第一个页沿着双向链表一直往下找,在每一个页中根据我们刚刚唠叨过的查找方式去查找指定的记录。因为要遍历所有的数据页,所以这种方式显然是超级耗时的,如果一个表有一亿条记录,使用这种方式去查找记录那要等到猴年马月才能等到查找结果。所以祖国和人民都在期盼一种能高效完成搜索的方法, 索引 同志就要亮相登台了。

索引

1
2
3
4
5
6
CREATE TABLE index_demo(
c1 INT,
c2 INT,
c3 CHAR(1),
PRIMARY KEY(c1)
) ROW_FORMAT = Compact;

image.png

  • record_type :记录头信息的一项属性,表示记录的类型, 0 表示普通记录、 2 表示最小记录、 3 表示最大记录
  • next_record :记录头信息的一项属性,表示下一条地址相对于本条记录的地址偏移量。

各个列的值 :这里只记录在 index_demo 表中的三个列,分别是 c1 、 c2 和 c3 。
image.png
我们在根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢?因为各个页中的记录并没有规律,我们并不知道我们的搜索条件匹配哪些页中的记录,所以 不得不依次遍历所有的数据页。所以如果我们想快速的定位到需要查找的记录在哪些数据页中该咋办?还记得我们为根据主键值快速定位一条记录在页中的位置而设立的页目录么?我们也可以想办法为快速定位记录所在的数据页而建立一个别的目录,建这个目录必须完成下边这些事儿:

下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。为了故事的顺利发展,我们这里需要做一个假设:假设我们的每个数据页最多能存放3条记录(实际上一个数据页非常大,可以存放下好多记录)。有了这个假设之后我们向 index_demo 表插入3条记录:

1
INSERT INTO index_demo VALUES(1, 4, 'u'), (3, 9, 'd'), (5, 3, 'y');

那么这些记录已经按照主键值的大小串联成一个单向链表了,如图所示:
image.png
从图中可以看出来, index_demo 表中的3条记录都被插入到了编号为 10 的数据页中了。此时我们再来插入一条记录:

1
INSERT INTO index_demo VALUES(4, 4, 'a');

因为 页10 最多只能放3条记录,所以我们不得不再分配一个新页:
image.png
咦?怎么分配的页号是 28 呀,不应该是 11 么?再次强调一遍,新分配的数据页编号可能并不是连续的,也就是说我们使用的这些页在存储空间里可能并不挨着。它们只是通过维护着上一个页和下一个页的编号而建立了链表关系。另外, 页10 中用户记录最大的主键值是 5 ,而 页28 中有一条记录的主键值是 4 ,因为 5 > 4 ,所以这就不符合下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值的要求,所以在插入主键值为 4 的记录的时候需要伴随着一次记录移动,也就是把主键值为 5 的记录移动到 页28 中,然后再把主键值为 4 的记录插入到 页10 中,这个过程的示意图如下:
image.png
这个过程表明了在对页中的记录进行增删改操作的过程中,我们必须通过一些诸如记录移动的操作来始终保证这个状态一直成立:
**下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值**。这个过程我们也可以称为 **页分裂** 。
给所有的页建立一个目录项。

由于数据页的编号可能并不是连续的,所以在向 index_demo 表中插入许多条记录后,可能是这样的效果:
image.png
因为这些 16KB 的页在物理存储上可能并不挨着,所以如果想从这么多页中根据主键值快速定位某些记录所在的页,我们需要给它们做个目录,每个页对应一个目录项,每个目录项包括下边两个部分:

  • 页的用户记录中最小的主键值,我们用 key 来表示。
  • 页号,我们用 page_no 表示。

所以我们为上边几个页做好的目录就像这样子:
image.png
以 页28 为例,它对应 目录项2 ,这个目录项中包含着该页的页号 28 以及该页中用户记录的最小主键值 5 。我们只需要把几个目录项在物理存储器上连续存储,比如把他们放到一个数组里,就可以实现根据主键值快速查找某条记录的功能了。比方说我们想找主键值为 20 的记录,具体查找过程分两步:

  • 先从目录项中根据二分法快速确定出主键值为 20 的记录在 目录项3 中(因为 12 < 20 < 209 ),它对应的页是 页9 。
  • 再根据前边说的在页中查找记录的方式去 页9 中定位具体的记录。

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

InnoDB中的索引方案

上边之所以称为一个简易的索引方案,是因为我们为了在根据主键值进行查找时使用二分法快速定位具体的目录项而假设所有目录项都可以在物理存储器上连续存储,但是这样做有几个问题:

  • InnoDB 是使用页来作为管理存储空间的基本单位,也就是最多能保证 16KB 的连续存储空间,而随着表中记录数量的增多,需要非常大的连续的存储空间才能把所有的目录项都放下,这对记录数量非常多的表是不现实的。
  • 我们时常会对记录进行增删,假设我们把 页28 中的记录都删除了, 页28 也就没有存在的必要了,那意味着目录项2 也就没有存在的必要了,这就需要把 目录项2 后的目录项都向前移动一下,这种牵一发而动全身的设计不是什么好主意~

所以,设计 InnoDB 的大叔们需要一种可以灵活管理所有 目录项 的方式。他们灵光乍现,忽然发现这些 目录项其实长得跟我们的用户记录差不多,只不过 目录项 中的两个列是 主键 和 页号 而已,所以他们**复用了之前存储用户记录的数据页来存储目录项,为了和用户记录做一下区分,我们把这些用来表示目录项的记录称为 目录项记录 **。那InnoDB 怎么区分一条记录是普通的 用户记录 还是 目录项记录 呢?别忘了记录头信息里的record_type 属性,它的各个取值代表的意思如下:

0 普通的用户记录
1 目录项记录
2 最小记录
3 最大记录

这个值为 1 的 record_type 是这个意思呀,我们把前边使用到的目录项放到数据页中的样子就是这样:
image.png
这里再次强调一遍 目录项记录和普通的 用户记录的不同点:

  • 目录项记录 的 record_type 值是1,而普通用户记录的 record_type 值是0。
  • 目录项记录 只有主键值和页的编号两个列,而普通的用户记录的列是用户自己定义的,可能包含很多列,另外还有 InnoDB 自己添加的隐藏列。
  • 还记得我们之前在唠叨记录头信息的时候说过一个叫 min_rec_mask 的属性么,只有在存储 目录项记录 的页中的主键值最小的 目录项记录 的 min_rec_mask 值为 1 ,其他别的记录的 min_rec_mask 值都是 0 。

除了上述几点外,这两者就没啥差别了,它们用的是一样的数据页(页面类型都是 0x45BF ,这个属性在 FileHeader),页的组成结构也是一样一样的,都会为主键值生成 Page Directory (页目录),从而在按照主键值进行查找时可以使用二分法来加快查询速度。现在以查找主键为 20 的记录为例,根据某个主键值去查找记录的步骤就可以大致拆分成下边两步:

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

虽然说 目录项记录 中只存储主键值和对应的页号,比用户记录需要的存储空间小多了,但是不论怎么说一个页只有 16KB 大小,能存放的 目录项记录 也是有限的,那如果表中的数据太多,以至于一个数据页不足以存放所有的 目录项记录 ,该咋办呢?
当然是再多整一个存储 目录项记录 的页喽~ 为了大家更好的理解新分配一个 目录项记录 页的过程,我们假设一个存储 目录项记录 的页最多只能存放4条 目录项记录 (请注意是假设哦,真实情况下可以存放好多条的),所以如果此时我们再向上图中插入一条主键值为 320 的用户记录的话,那就需要分配一个新的存储 目录项记录的页喽:
image.png
从图中可以看出,我们插入了一条主键值为 320 的用户记录之后需要两个新的数据页:为存储该用户记录而新生成了 页31 。

因为原先存储 目录项记录 的 页30 的容量已满(我们前边假设只能存储4条 目录项记录 ),所以不得不需要一个新的 页32 来存放 页31 对应的目录项。

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

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

那么问题来了,在这个查询步骤的第1步中我们需要定位存储 目录项记录 的页,但是这些页在存储空间中也可能不挨着,如果我们表中的数据非常多则会产生很多存储 目录项记录 的页,那我们怎么根据主键值快速定位一个存储 目录项记录 的页呢?其实也简单,为这些存储 目录项记录 的页再生成一个更高级的目录,就像是一个多级目录一样,大目录里嵌套小目录,小目录里才是实际的数据,所以现在各个页的示意图就是这样子:
image.png
如图，我们生成了一个存储更高级目录项的 页33 ，这个页中的两条记录分别代表 页30 和 页32 ，如果用户记录 的主键值在 [1, 320) 之间，则到 页30 中查找更详细的 目录项记录 ，如果主键值不小于 320 的话，就到 页32 中查找更详细的 目录项记录 。不过这张图好漂亮喔，随着表中记录的增加，这个目录的层级会继续增加，如果 简化一下，那么我们可以用下边这个图来描述它：
image.png
不论是存放用户记录的数据页，还是存放目录项记录的数据页，我们都把它们存放到 B+ 树这个数据结构中了， 所以我们也称这些数据页为 节点 。从图中可以看出来，我们的实际用户记录其实都存放在B+树的最底层的节点 上，这些节点也被称为 叶子节点 或 叶节点 ，其余用来存放 目录项 的节点称为 非叶子节点 或者 内节点 ，其 中 B+ 树最上边的那个节点也称为 根节点 。

从图中可以看出来，一个 B+ 树的节点其实可以分成好多层，设计 InnoDB 的大叔们为了讨论方便，规定最下边的 那层，也就是存放我们用户记录的那层为第 0 层，之后依次往上加。之前的讨论我们做了一个非常极端的假设： 存放用户记录的页最多存放3条记录，存放目录项记录的页最多存放4条记录。其实真实环境中一个页存放的记录 数量是非常大的，假设，假设，假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录，所有 存放目录项记录的内节点代表的数据页可以存放1000条目录项记录，那么：

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

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

聚簇索引

两个特点：

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

  2. B+ 树的叶子节点存储的是完整的用户记录。

所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。我们把具有这两种特性的 B+ 树称为 聚簇索引 ，所有完整的用户记录都存放在这个 聚簇索引 的叶子节点处。这种 聚簇索引 并不需要我们在 MySQL 语句中显式的使用 INDEX 语句去创建（后边会介绍索引相关的语句），InnoDB 存储引擎会自动的为我们创建聚簇索引。另外有趣的一点是，在 InnoDB 存储引擎中， 聚簇索引 就是数据的存储方式（所有的用户记录都存储在了 叶子节点 ），也就是所谓的索引即数据，数据即索引。

二级索引

不同的 B+ 树中的数据采用不同的排序规则。比方说我们用 c2 列的大小作为数据 页、页中记录的排序规则，再建一棵 B+ 树，效果如下图所示：

image.png
这个 B+ 树与上边介绍的聚簇索引有几处不同：
使用记录 c2 列的大小进行记录和页的排序，这包括三个方面的含义：

  • 页内的记录是按照 c2 列的大小顺序排成一个单向链表。
  • 各个存放用户记录的页也是根据页中记录的 c2 列大小顺序排成一个双向链表。
  • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的 c2 列大小顺序排成一个双向链表。
  • B+ 树的叶子节点存储的并不是完整的用户记录，而只是 c2列+主键 这两个列的值。
  • 目录项记录中不再是 主键+页号 的搭配，而变成了 c2列+页号 的搭配。

所以如果我们现在想通过 c2 列的值查找某些记录的话就可以使用我们刚刚建好的这个 B+ 树了。以查找 c2 列的值为 4 的记录为例，查找过程如下：

  1. 确定 目录项记录 页根据 根页面 ，也就是 页44 ，可以快速定位到 目录项记录 所在的页为 页42 （因为 2 < 4 < 9 ）。

  2. 通过 目录项记录 页确定用户记录真实所在的页。

    在 页42 中可以快速定位到实际存储用户记录的页，由于 c2 列并没有唯一性约束，所以 c2 列为 4 的记录可能分布在多个数据页中，又因为 2 < 4 ≤ 4 ，所以确定实际存储用户记录的页在 页34 和 页35 中。

  3. 在真实存储用户记录的页中定位到具体的记录。到 页34 和 页35 中定位到具体的记录。

  4. 但是这个 B+ 树的叶子节点中的记录只存储了 c2 和 c1 （也就是 主键 ）两个列，所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。各位各位，看到步骤4的操作了么？我们根据这个以 c2 列大小排序的 B+ 树只能确定我们要查找记录的主键值，所以如果我们想根据 c2 列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查一遍，这个过程也被称为 回表 。也就是根据 c2 列的值查询一条完整的用户记录需要使用到 2 棵 B+ 树！！！
    为什么我们还需要一次 回表 操作呢？直接把完整的用户记录放到 叶子节点 不就好了么？你说的对，如果把完整的用户记录放到 叶子节点 是可以不用 回表 ，但是太占地方了呀～相当于每建立一棵 B+ 树都需要把所有的用户记录再都拷贝一遍，这就有点太浪费存储空间了。因为这种按照 非主键列 建立的 B+ 树需要一次 回表 操作才可以定位到完整的用户记录，所以这种 B+ 树也被称为 二级索引 （英文名 secondary index ），或者 辅助索引 。由于我们使用的是 c2 列的大小作为 B+ 树的排序规则，所以我们也称这个 B+ 树为为c2列建立的索引。

联合索引

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

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

为 c2 和 c3 列建立的索引的示意图如下：
image.png
如图所示，我们需要注意一下几点：

  • 每条 目录项记录 都由 c2 、 c3 、 页号 这三个部分组成，各条记录先按照 c2 列的值进行排序，如果记录 的 c2 列相同，则按照 c3 列的值进行排序。
  • B+ 树叶子节点处的用户记录由 c2 、 c3 和主键 c1 列组成。

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

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

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

根页面万年不动窝

实际上 B+ 树的形成过程是这样的：

  • 每当为某个表创建一个 B+ 树索引（聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一 个 根节点 页面。最开始表中没有数据的时候，每个 B+ 树索引对应的 根节点 中既没有用户记录，也没有目 录项记录。

  • 随后向表中插入用户记录时，先把用户记录存储到这个 根节点 中

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

这个过程需要大家特别注意的是：一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表 建立一个索引，那么它的 根节点 的页号便会被记录到某个地方，然后凡是 InnoDB 存储引擎需要用到这个索引的 时候，都会从那个固定的地方取出 根节点 的页号，从而来访问这个索引。

MyISAM中的索引方案简单介绍

MyISAM 的索引方案虽然也使用树形 结构，但是却将索引和数据分开存储：

将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为 数据文件 。这个文件并不划分为若干个 数据页，有多少记录就往这个文件中塞多少记录就成了。我们可以通过行号而快速访问到一条记录。

MyISAM 记录也需要记录头信息来存储一些额外数据，我们以上边唠叨过的 index_demo 表为例，看一下这个 表中的记录使用 MyISAM 作为存储引擎在存储空间中的表示：
image.png
由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找。

  • 使用 MyISAM 存储引擎的表会把索引信息另外存储到一个称为 索引文件 的另一个文件中。 MyISAM 会单独为 表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是 主键值 + 行号 的组 合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录！

  • 这一点和 InnoDB 是完全不相同的，在 InnoDB 存储引擎中，我们只需要根据主键值对 聚簇索引 进行一次查 找就能找到对应的记录，而在 MyISAM 中却需要进行一次 回表 操作，意味着 MyISAM 中建立的索引相当于全 部都是 二级索引 ！ 如果有需要的话，我们也可以对其它的列分别建立索引或者建立联合索引，原理和 InnoDB 中的索引差不 多，不过在叶子节点处存储的是 相应的列 + 行号 。这些索引也全部都是 二级索引 。

第7章好东西也得先学会怎么用-B+树索引的使用

索引的代价

  • 空间上的代价

    这个是显而易见的，每建立一个索引都要为它建立一棵 B+ 树，每一棵 B+ 树的每一个节点都是一个数据页， 一个页默认会占用 16KB 的存储空间，一棵很大的 B+ 树由许多数据页组成，那可是很大的一片存储空间呢。

  • 时间上的代价

    每次对表中的数据进行增、删、改操作时，都需要去修改各个 B+ 树索引。而且我们讲过， B+ 树每层节点都 是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录 （也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而 增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页 面分裂、页面回收啥的操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的 B+ 树都 要进行相关的维护操作，这还能不给性能拖后腿么？ 所以说，一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。为了能建立又好 又少的索引，我们先得学学这些索引在哪些条件下起作用的。

B+树索引适用的条件

1
2
3
4
5
6
7
8
9
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)
);

  • 表中的主键是 id 列，它存储一个自动递增的整数。所以 InnoDB 存储引擎会自动为 id 列建立聚簇索引。

  • 我们额外定义了一个二级索引 idx_name_birthday_phone_number ，它是由3个列组成的联合索引。所以在这 个索引对应的 B+ 树的叶子节点处存储的用户记录只保留 name 、 birthday 、 phone_number 这三个列的值 以及主键 id 的值，并不会保存 country 列的值。

image.png
索引规则：

  • 先按照 name 列的值进行排序。
  • 如果 name 列的值相同，则按照 birthday 列的值进行排序。
  • 如果 birthday 列的值也相同，则按照 phone_number 的值进行排序。

全值匹配

如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配，比方说下边这个查找语句： 

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

我们建立的 idx_name_birthday_phone_number 索引包含的3个列在这个查询语句中都展现出来了。大家可以想象 一下这个查询过程：

因为 B+ 树的数据页和记录先是按照 name 列的值进行排序的，所以先可以很快定位 name 列的值是 Ashburn 的记录位置。 在 name 列相同的记录里又是按照 birthday 列的值进行排序的，所以在 name 列的值是 Ashburn 的记录里又 可以快速定位 birthday 列的值是 ‘1990-09-27’ 的记录。 如果很不幸， name 和 birthday 列的值都是相同的，那记录是按照 phone_number 列的值排序的，所以联合 索引中的三个列都可能被用到。

有的同学也许有个疑问， WHERE 子句中的几个搜索条件的顺序对查询结果有啥影响么？也就是说如果我们调换 name 、 birthday 、 phone_number 这几个搜索列的顺序对查询的执行过程有影响么？比方说写成下边这样：

1
SELECT * FROM person_info WHERE birthday = '1990-09-27' AND phone_number = '15123983239' A ND name = 'Ashburn';

答案是：没影响哈。 MySQL 有一个叫查询优化器的东东，会分析这些搜索条件并且按照可以使用的索引中列的顺 序来决定先使用哪个搜索条件，后使用哪个搜索条件。

匹配左边的列

联合索引遵循最左匹配原则，如果我们想要走索引的话就要遵守

其实在我们的搜索语句中也可以不用包含全部联合索引中的列，只包含左边的就行，比方说下边的查询语句：

1
SELECT * FROM person_info WHERE name = 'Ashburn';

或者包含多个左边的列也行： 

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

那为什么搜索条件中必须出现左边的列才可以使用到这个 B+ 树索引呢？比如下边的语句就用不到这个 B+ 树索引 么？ 

1
SELECT * FROM person_info WHERE birthday = '1990-09-27';

是的，的确用不到，因为 B+ 树的数据页和记录先是按照 name 列的值排序的，在 name 列的值相同的情况下才使 用 birthday 列进行排序，也就是说 name 列的值不同的记录中 birthday 的值可能是无序的。而现在你跳过 name 列直接根据 birthday 的值去查找，臣妾做不到呀～ 那如果我就想在只使用 birthday 的值去通过 B+ 树索 引进行查找咋办呢？这好办，你再对 birthday 列建一个 B+ 树索引就行了。 但是需要特别注意的一点是，如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中 从最左边连续的列。比方说联合索引idx_name_birthday_phone_number 中列的定义顺序是 name 、 birthday 、 phone_number ，如果我们的搜索条件中只有 name 和 phone_number ，而没有中间的 birthday ， 比方说这样：

1
SELECT * FROM person_info WHERE name = 'Ashburn' AND phone_number = '15123983239';

这样只能用到 name 列的索引， birthday 和 phone_number 的索引就用不上了，因为 name 值相同的记录先按照 birthday 的值进行排序， birthday 值相同的记录才按照 phone_number 值进行排序。

匹配范围值

回头看我们 idx_name_birthday_phone_number 索引的 B+ 树示意图，所有记录都是按照索引列的值从小到大的顺序排好序的，所以这极大的方便我们查找索引列的值在某个范围内的记录。比方说下边这个查询语句：

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

由于 B+ 树中的数据页和记录是先按 name 列排序的，所以我们上边的查询过程其实是这样的：

  • 找到 name 值为 Asa 的记录。
  • 找到 name 值为 Barlow 的记录。

哦啦，由于所有记录都是由链表连起来的（记录之间用单链表，数据页之间用双链表），所以他们之间的记录都可以很容易的取出来喽～
找到这些记录的主键值，再到 聚簇索引 中 回表 查找完整的记录。不过在使用联合进行范围查找的时候需要注意，如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到 B+ 树索引，比方说这样：

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

上边这个查询可以分成两个部分：

  1. 通过条件 name > 'Asa' AND name < 'Barlow' 来对 name 进行范围，查找的结果可能有多条 name 值不同的记录，
  2. 对这些 name 值不同的记录继续通过 birthday > ‘1980-01-01’ 条件继续过滤。

这样子对于联合索引 idx_name_birthday_phone_number 来说，只能用到 name 列的部分，而用不到 birthday 列的部分，因为只有 name 值相同的情况下才能用 birthday 列的值进行排序，而这个查询中通过 name 进行范围查找的记录中可能并不是按照 birthday 列进行排序的，所以在搜索条件中继续以 birthday 列进行查找时是用不到这个 B+ 树索引的。

精确匹配某一列并范围匹配另外一列

对于同一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找，比方说这样：

1
2
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday
< '2000-12-31' AND phone_number > '15100000000';

这个查询的条件可以分为3个部分：

  1. name = ‘Ashburn’ ，对 name 列进行精确查找，当然可以使用 B+ 树索引了。
  2. birthday > ‘1980-01-01’ AND birthday < ‘2000-12-31’ ，由于 name 列是精确查找，所以通过 name =’Ashburn’ 条件查找后得到的结果的 name 值都是相同的，它们会再按照 birthday 的值进行排序。所以此时对 birthday 列进行范围查找是可以用到 B+ 树索引的。
  3. phone_number > ‘15100000000’ ，通过 birthday 的范围查找的记录的 birthday 的值可能不同，所以这个条件无法再利用 B+ 树索引了，只能遍历上一步查询得到的记录。

同理，下边的查询也是可能用到这个 idx_name_birthday_phone_number 联合索引的：

1
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1980-01-01' AND AND phone_number > '15100000000';

用于排序

一般情况下， 我们只能把记录都加载到内存中，再用一些排序算法，比如快速排序、归并排序、吧啦吧啦排序等等在内存中对 这些记录进行排序，有的时候可能查询的结果集太大以至于不能在内存中进行排序的话，还可能暂时借助磁盘的 空间来存放中间结果，排序操作完成后再把排好序的结果集返回到客户端。在 MySQL 中，把这种在内存中或者磁 盘上进行排序的方式统称为文件排序（英文名： filesort ）

如果 ORDER BY 子句里使用到了我们的 索引列，就有可能省去在内存或文件中排序的步骤，比如下边这个简单的查询语句： 

1
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;

这个查询的结果集需要先按照 name 值排序，如果记录的 name 值相同，则需要按照 birthday 来排序，如果 birthday 的值相同，则需要按照 phone_number 排序。大家可以回过头去看我们建立的idx_name_birthday_phone_number 索引的示意图，因为这个 B+ 树索引本身就是按照上述规则排好序的，所以直 接从索引中提取数据，然后进行 回表 操作取出该索引中不包含的列就好了。

**使用联合索引进行排序注意事项 **

对于 联合索引 有个问题需要注意， ORDER BY 的子句后边的列的顺序也必须按照索引列的顺序给出，如果给出 ORDER BY phone_number, birthday, name 的顺序，那也是用不了 B+ 树索引，

同理， ORDER BY name 、 ORDER BY name, birthday 这种匹配索引左边的列的形式可以使用部分的 B+ 树索引。 当联合索引左边列的值为常量，也可以使用后边的列进行排序，比如这样： 

1
SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10;

这个查询能使用联合索引进行排序是因为 name 列的值相同的记录是按照 birthday , phone_number 排序的。

不可以使用索引进行排序的几种情况

  • ASC、DESC混用

    对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是 ASC 规则 排序，要么都是 DESC 规则排序。

  • WHERE子句中出现非排序使用到的索引列

    如果WHERE子句中出现了非排序使用到的索引列，那么排序依然是使用不到索引的，比方说这样： 

1
SELECT * FROM person_info WHERE country = 'China' ORDER BY name LIMIT 10;

这个查询只能先把符合搜索条件 country = ‘China’ 的记录提取出来后再进行排序，是使用不到索引。注意和下 边这个查询作区别： 

1
SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10;

虽然这个查询也有搜索条件，但是 name = ‘A’ 可以使用到索引 idx_name_birthday_phone_number ，而且过滤剩 下的记录还是按照 birthday 、 phone_number 列排序的，所以还是可以使用索引进行排序的。

  • 排序列包含非同一个索引的列

    有时候用来排序的多个列不是一个索引里的，这种情况也不能使用索引进行排序，比方说： 

1
SELECT * FROM person_info ORDER BY name, country LIMIT 10;

name 和 country 并不属于一个联合索引中的列，所以无法使用索引进行排序。

  • 排序列使用了复杂的表达式

    要想使用索引进行排序操作，必须保证索引列是以单独列的形式出现，而不是修饰过的形式，比方说:

1
SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;

使用了 UPPER 函数修饰过的列就不是单独的列啦，这样就无法使用索引进行排序啦。

用于分组

有时候我们为了方便统计表中的一些信息，会把表中的记录按照某些列进行分组。如下边这个分组查询：

1
SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number

这个查询语句相当于做了3次分组操作：

  1. 先把记录按照 name 值进行分组，所有 name 值相同的记录划分为一组。
  2. 将每个 name 值相同的分组里的记录再按照 birthday 的值进行分组，将 birthday 值相同的记录放到一个小分组里，所以看起来就像在一个大分组里又化分了好多小分组。
  3. 再将上一步中产生的小分组按照 phone_number 的值分成更小的分组，所以整体上看起来就像是先把记录分成一个大分组，然后把 大分组 分成若干个 小分组 ，然后把若干个 小分组 再细分成更多的 小小分组 。

然后针对那些 小小分组 进行统计，比如在我们这个查询语句中就是统计每个 小小分组 包含的记录条数。如果没有索引的话，这个分组过程全部需要在内存里实现，而如果有了索引的话，恰巧这个分组顺序又和我们的 B+ 树中的索引列的顺序是一致的，而我们的 B+ 树索引又是按照索引列排好序的，这不正好么，所以可以直接使用B+ 树索引进行分组。和使用 B+ 树索引进行排序是一个道理，分组列的顺序也需要和索引列的顺序一致，也可以只使用索引列中左边的列进行分组

回表的代价

查询：

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

在使用 idx_name_birthday_phone_number 索引进行查询时大致可以分为这两个步骤：

  1. 从索引 idx_name_birthday_phone_number 对应的 B+ 树中取出 name 值在 Asa ～ Barlow 之间的用户记录。
  2. 由于索引 idx_name_birthday_phone_number 对应的 B+ 树用户记录中只包含 name 、 birthday 、
    phone_number 、 id 这4个字段，而查询列表是 * ，意味着要查询表中所有字段，也就是还要包括 country字段。这时需要把从上一步中获取到的每一条记录的 id 字段都到聚簇索引对应的 B+ 树中找到完整的用户记录，也就是我们通常所说的 回表 ，然后把完整的用户记录返回给查询用户。

由于索引 idx_name_birthday_phone_number 对应的 B+ 树中的记录首先会按照 name 列的值进行排序，所以值 在 Asa ～ Barlow 之间的记录在磁盘中的存储是相连的，集中分布在一个或几个数据页中，我们可以很快的把这 些连着的记录从磁盘中读出来，这种读取方式我们也可以称为 顺序I/O 。根据第1步中获取到的记录的 id 字段 的值可能并不相连，而在聚簇索引中记录是根据 id （也就是主键）的顺序排列的，所以根据这些并不连续的 id 值到聚簇索引中访问完整的用户记录可能分布在不同的数据页中，这样读取完整的用户记录可能要访问更多的数 据页，这种读取方式我们也可以称为 随机I/O 。一般情况下，顺序I/O比随机I/O的性能高很多，所以步骤1的执行 可能很快，而步骤2就慢一些。所以这个使用索引 idx_name_birthday_phone_number 的查询有这么两个特点：

  • 会使用到两个 B+ 树索引，一个二级索引，一个聚簇索引。
  • 访问二级索引使用 顺序I/O ，访问聚簇索引使用 随机I/O 。

需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用 二级索引 。比 方说 name 值在 Asa ～ Barlow 之间的用户记录数量占全部记录数量90%以上，那么如果使用 idx_name_birthday_phone_number 索引的话，有90%多的 id 值需要回表，这不是吃力不讨好么，还不如直接去 扫描聚簇索引（也就是全表扫描）。

那什么时候采用全表扫描的方式，什么时候使用采用 二级索引 + 回表 的方式去执行查询呢？这个就是传说中的 查询优化器做的工作，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的 条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用 二级索 引 + 回表 的方式。当然优化器做的分析工作不仅仅是这么简单，但是大致上是个这个过程。一般情况下，限制 查询获取较少的记录数会让优化器更倾向于选择使用 二级索引 + 回表 的方式进行查询，因为回表的记录越少， 性能提升就越高

覆盖索引

为了彻底告别 回表 操作带来的性能损耗，我们建议：最好在查询列表里只包含索引列，比如这样： 

1
SELECT name, birthday, phone_number FROM person_info WHERE name > 'Asa' AND name < 'Barlow'

因为我们只查询 name , birthday , phone_number 这三个索引列的值，所以在通过idx_name_birthday_phone_number 索引得到结果后就不必到 聚簇索引 中再查找记录的剩余列，也就是 country 列的值了，这样就省去了 回表 操作带来的性能损耗。我们把这种只需要用到索引的查询方式称为 索引 覆盖 。排序操作也优先使用 覆盖索引 的方式进行查询，比方说这个查询： 

1
SELECT name, birthday, phone_number FROM person_info ORDER BY name, birthday, phone_numbe r;

虽然这个查询中没有 LIMIT 子句，但是采用了 覆盖索引 ，所以查询优化器就会直接使用 idx_name_birthday_phone_number 索引进行排序而不需要回表操作了。 当然，如果业务需要查询出索引以外的列，那还是以保证业务需求为重。但是我们*很不鼓励用 * 号作为查询列表*，最好把我们需要查询的列依次标明。

如何挑选索引

只为用于搜索、排序或分组的列创建索引

只为出现在 WHERE 子句中的列、连接子句中的连接列，或者出现在 ORDER BY 或 GROUP BY 子句中的 列创建索引。而出现在查询列表中的列就没必要建立索引了： 

1
SELECT birthday, country FROM person name WHERE name = 'Ashburn';

像查询列表中的 birthday 、 country 这两个列就不需要建立索引，我们只需要为出现在 WHERE 子句中的 name 列创建索引就可以了。

考虑列的基数

列的基数 指的是某一列中不重复数据的个数，比方说某个列包含值 2, 5, 8, 2, 5, 8, 2, 5, 8 ，虽然有 9 条 记录，但该列的基数却是 3 。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散，列的基 数越小，该列中的值越集中。这个 列的基数 指标非常重要，直接影响我们是否能有效的利用索引。假设某个列 的基数为 1 ，也就是所有记录在该列中的值都一样，那为该列建立索引是没有用的，因为所有值都一样就无法排 序，无法进行快速查找了～ 而且如果某个建立了二级索引的列的重复值特别多，那么使用这个二级索引查出的记 录还可能要做回表操作，这样性能损耗就更大了。所以结论就是：最好为那些列的基数大的列建立索引，为基数 太小列的建立索引效果可能不好。

索引列的类型尽量小

我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有 TINYINT 、 MEDIUMINT 、 INT 、 BIGINT 这么几种，它们占用的存储空间依次递增，我们这里所说的 类型大小 指的就是该类型表示的数据范围的大小。 能表示的整数范围当然也是依次递增，如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况 下，尽量让索引列使用较小的类型，比如我们能使用 INT 就不要使用 BIGINT ，能使用 MEDIUMINT 就不要使用 INT ～ 这是因为：

  • 数据类型越小，在查询时进行的比较操作越快（这是CPU层次的东东）
  • 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘 I/O 带 来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会 存储一份记录的主键值，如果主键适用更小的数据类型，也就意味着节省更多的存储空间和更高效的 I/O 。

索引字符串值的前缀

如果我们选择索引的字段是字符串且长度较长，暂用的空间较大，那么我们可以设置索引的字段长度。

1
2
3
4
5
6
7
CREATE TABLE person_info(
 name VARCHAR(100) NOT NULL,
 birthday DATE NOT NULL,
 phone_number CHAR(11) NOT NULL,
 country varchar(100) NOT NULL,
 KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

索引列前缀对排序的影响 如果使用了索引列前缀，比方说前边只把 name 列的前10个字符放到了二级索引中，下边这个查询可能就有点儿 尴尬了： 

1
SELECT * FROM person_info ORDER BY name LIMIT 10;

因为二级索引中不包含完整的 name 列信息，所以无法对前十个字符相同，后边的字符不同的记录进行排序，也 就是使用索引列前缀的方式无法支持使用索引排序，只好乖乖的用文件排序喽。

让索引列在比较表达式中单独出现

假设表中有一个整数列 my_col ，我们为这个列建立了索引。下边的两个 WHERE 子句虽然语义是一致的，但是在
效率上却有差别：

  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+ 树索引。

所以结论就是：如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话，是用不到索引的。

主键插入顺序

我们知道，对于一个使用 InnoDB 存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存 储在 聚簇索引 的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺 序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页 继续插，而如果我们插入的主键值忽大忽小的话，这就比较麻烦了，假设某个数据页存储的记录已经满了，它存 储的主键值在 1~100 之间：
image.png
如果此时再插入一条主键值为 9 的记录，那它插入的位置就如下图：
image.png
可这个数据页已经满了啊，再插进来咋办呢？我们需要把当前页面分裂成两个页面，把本页中的一些记录移动到 新创建的这个页中。页面分裂和记录移位意味着什么？意味着：性能损耗！所以如果我们想尽量避免这样无谓的 性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。所以我们建议：让主键具 有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入 ，比方说我们可以这样定义 person_info 表：

1
2
3
4
5
6
7
8
9
CREATE TABLE person_info(
 id INT UNSIGNED 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(10), birthday, phone_number)
);

我们自定义的主键列 id 拥有 AUTO_INCREMENT 属性，在插入记录时存储引擎会自动为我们填入自增的主键值。

冗余和重复索引

有时候有的同学有意或者无意的就对同一个列创建了多个索引，比方说这样写建表语句： 

1
2
3
4
5
6
7
8
9
10
CREATE TABLE person_info(
 id INT UNSIGNED 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(10), birthday, phone_number),
 KEY idx_name (name(10))
);

我们知道，通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索，再创建一个专门针对 name 列的索引就算是一个 冗余 索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。 另一种情况，我们可能会对某个列重复建立索引，比方说这样：

1
2
3
4
5
6
CREATE TABLE repeat_index_demo (
 c1 INT PRIMARY KEY,
 c2 INT,
 UNIQUE uidx_c1 (c1),
 INDEX idx_c1 (c1)
);

我们看到， c1 既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚 簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。

第8章数据的家-MySQL的数据目录

InnoDB 、 MyISAM 这样的存储引擎都是把表存储在磁盘上的，而操作系统用来管理磁盘的那个东东又 被称为 文件系统 ，所以用专业一点的话来表述就是：像 InnoDB 、 MyISAM 这样的存储引擎都是把表存储在文 件系统上的。当我们想读取数据的时候，这些存储引擎会从文件系统中把数据读出来返回给我们，当我们想写入 数据的时候，这些存储引擎会把这些数据又写回文件系统。本

确定MySQL中的数据目录

1
SHOW VARIABLES LIKE 'datadir';

查看该目录下的文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
drwxr-x---   26 _mysql  _mysql   832B  9 10 16:05 .
drwxr-xr-x   13 root    wheel    416B 11 29  2021 ..
-rw-r-----    1 _mysql  _mysql    56B 11 29  2021 auto.cnf
drwxr-x---    5 _mysql  _mysql   160B  6 16 11:24 bank
-rw-------    1 _mysql  _mysql   1.6K 11 29  2021 ca-key.pem
-rw-r--r--    1 _mysql  _mysql   1.1K 11 29  2021 ca.pem
-rw-r--r--    1 _mysql  _mysql   1.1K 11 29  2021 client-cert.pem
-rw-------    1 _mysql  _mysql   1.6K 11 29  2021 client-key.pem
drwxr-x---   23 _mysql  _mysql   736B  3 28 21:31 ddd_framework
drwxr-x---   75 _mysql  _mysql   2.3K  6  4 00:09 devops@002dplatform
-rw-r-----    1 _mysql  _mysql   991B  9  9 10:25 ib_buffer_pool
-rw-r-----    1 _mysql  _mysql    48M  9 12 20:29 ib_logfile0
-rw-r-----    1 _mysql  _mysql    48M 11 29  2021 ib_logfile1
-rw-r-----    1 _mysql  _mysql    76M  9 12 20:29 ibdata1
-rw-r-----    1 _mysql  _mysql    12M  9 12 20:29 ibtmp1
drwxr-x---   77 _mysql  _mysql   2.4K 11 29  2021 mysql
drwxr-x---    7 _mysql  _mysql   224B  9 12 20:29 mysql_innodb
-rw-r-----    1 _mysql  _mysql   8.9M  9 13 09:30 mysqld.local.err
-rw-r-----    1 _mysql  _mysql     6B  9 10 16:05 mysqld.local.pid
drwxr-x---   90 _mysql  _mysql   2.8K 11 29  2021 performance_schema
-rw-------    1 _mysql  _mysql   1.6K 11 29  2021 private_key.pem
-rw-r--r--    1 _mysql  _mysql   452B 11 29  2021 public_key.pem
-rw-r--r--    1 _mysql  _mysql   1.1K 11 29  2021 server-cert.pem
-rw-------    1 _mysql  _mysql   1.6K 11 29  2021 server-key.pem
drwxr-x---  108 _mysql  _mysql   3.4K 11 29  2021 sys
drwxr-x---   85 _mysql  _mysql   2.7K  6  3 18:17 teamwork

以mysql_innodb数据库为例，主要组成文件如下

1
2
3
4
5
6
mysql_innodb
├── db.opt # 数据库的各种属性，比方说该数据库的字符集和比较规则是个啥
├── page_demo.frm # 数据库表定义文件
├── page_demo.ibd # 数据文件
├── person_info.frm
└── person_info.ibd

系统表空间

默认情况下， InnoDB 会在 数据目录 下创 建一个名为 ibdata1、大小为 12M 的文件，这个文件就是对应的 系统表空间 在文件系统上的表示。

如果你想让系统表空间对应文件系统上多个实际文件，或者仅仅觉得原来的 ibdata1 这个文件名难听，那 可以在 MySQL 启动时配置对应的文件路径以及它们的大小，比如我们这样修改一下配置文件： 

1
2
[server] 
innodb_data_file_path=data1:512M;data2:512M:autoextend

这样在 MySQL 启动之后就会创建这两个512M大小的文件作为 系统表空间 ，其中的 autoextend 表明这两个文件 如果不够用会自动扩展 data2 文件的大小。 我们也可以把 系统表空间 对应的文件路径不配置到 数据目录 下，甚至可以配置到单独的磁盘分区上，涉及到的 启动参数就是 innodb_data_file_path 和 innodb_data_home_dir

独立表空间

InnoDB 并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表 建立一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间。使用 独立表空间 来存储表数据 的话，会在该表所属数据库对应的子目录下创建一个表示该 独立表空间 的文件，文件名和表名相同，只不过添 加了一个 .ibd 的扩展名而已，所以完整的文件名称长这样： 

1
表名.ibd

我们也可以自己指定使用 系统表空间 还是 独立 表空间 来存储数据，这个功能由启动参数 innodb_file_per_table 控制，比如说我们想刻意将表数据都存储到 系统表空间 时，可以在启动 MySQL 服务器的时候这样配置：

1
2
[server] 
innodb_file_per_table=0

当 innodb_file_per_table 的值为 0 时，代表使用系统表空间；当 innodb_file_per_table 的值为 1 时，代表 使用独立表空间。不过 innodb_file_per_table 参数只对新建的表起作用，对于已经分配了表空间的表并不起作 用。如果我们想把已经存在系统表空间中的表转移到独立表空间，可以使用下边的语法： 

1
ALTER TABLE 表名 TABLESPACE [=] innodb_file_per_table;

或者把已经存在独立表空间的表转移到系统表空间，可以使用下边的语法： 

1
ALTER TABLE 表名 TABLESPACE [=] innodb_system;

其中中括号扩起来的=可有可无，比方说我们想把 test 表从独立表空间移动到系统表空间，可以这么写： 

1
ALTER TABLE test TABLESPACE innodb_system;

其他类型的表空间

随着MySQL的发展，除了上述两种老牌表空间之外，现在还新提出了一些不同类型的表空间，比如通用表空间 （general tablespace）、undo表空间（undo tablespace）、临时表空间（temporary tablespace）

MyISAM是如何存储表数据的

MyISAM 中的索引全部都是 二级索引 ，该存储引擎的数据和索引是分开存放的。所以在文件系统 中也是使用不同的文件来存储数据文件和索引文件。而且和 InnoDB 不同的是， MyISAM 并没有什么所谓的 表空 间 一说，表数据都存放到对应的数据库子目录下。假如 test 表使用 MyISAM 存储引擎的话，那么在它所在数据 库对应的 数据库 目录下会为 test 表创建这三个文件：

1
2
3
test.frm # 代表表结构
test.MYD # 代表表的数据文件
test.MYI # 代表表的索引文件

视图在文件系统中的表示

我们知道 MySQL 中的视图其实是虚拟的表，也就是某个查询语句的一个别名而已，所以在存储 视图 的时候是不 需要存储真实的数据的，只需要把它的结构存储起来就行了。和 表 一样，描述视图结构的文件也会被存储到所 属数据库对应的子目录下边，只会存储一个 视图名.frm 的文件。

其他的文件

  • 服务器进程文件。 我们知道每运行一个 MySQL 服务器程序，都意味着启动一个进程。 MySQL 服务器会把自己的进程ID写入到一 个文件中。
  • 服务器日志文件。 在服务器运行过程中，会产生各种各样的日志，比如常规的查询日志、错误日志、二进制日志、redo日志吧 啦吧啦各种日志，这些日志各有各的用途，我们之后会重点唠叨各种日志的用途，现在先了解一下就可以 了。
  • 默认/自动生成的SSL和RSA证书和密钥文件。 主要是为了客户端和服务器安全通信而创建的一些文件

文件系统对数据库的影响

因为 MySQL 的数据都是存在文件系统中的，就不得不受到文件系统的一些制约，这在数据库和表的命名、表的大 小和性能方面体现的比较明显，比如下边这些方面：

  • 数据库名称和表名称不得超过文件系统所允许的最大长度。

    每个数据库都对应 数据目录 的一个子目录，数据库名称就是这个子目录的名称；每个表都会在数据库子目 录下产生一个和表名同名的 .frm 文件，如果是 InnoDB 的独立表空间或者使用 MyISAM 引擎还会有别的文件 的名称与表名一致。这些目录或文件名的长度都受限于文件系统所允许的长度～

  • 特殊字符的问题

    为了避免因为数据库名和表名出现某些特殊字符而造成文件系统不支持的情况， MySQL 会把数据库名和表名 中所有除数字和拉丁字母以外的所有字符在文件名里都映射成 @+编码值 的形式作为文件名。比方说我们创 建的表的名称为 ‘test?’ ，由于 ? 不属于数字或者拉丁字母，所以会被映射成编码值，所以这个表对应 的 .frm 文件的名称就变成了 test@003f.frm 。

  • 文件长度受文件系统最大长度限制

    对于 InnoDB 的独立表空间来说，每个表的数据都会被存储到一个与表名同名的 .ibd 文件中；对于 MyISAM 存储引擎来说，数据和索引会分别存放到与表同名的 .MYD 和 .MYI 文件中。这些文件会随着表中记录的增加 而增大，它们的大小受限于文件系统支持的最大文件大小。

第9章存放页面的大池子-InnoDB的表空间

可以把表空间想 象成被切分为许许多多个 页 的池子，当我们想为某个表插入一条记录的时候，就从池子中捞出一个对应的页来 把数据写进去。

相关知识

页面类型

类型名称 十六进制 描述
FIL_PAGE_TYPE_ALLOCATED 0x0000 最新分配,还没使用
FIL_PAGE_UNDO_LOG 0x0002 Undo日志页
FIL_PAGE_INODE 0x0003 段信息节点
FIL_PAGE_IBUF_FREE_LIST 0x0004 Insert Buffer空闲列表
FIL_PAGE_IBUF_BITMAP 0x0005 Insert Buffer位图
FIL_PAGE_TYPE_SYS 0x0006 系统页
FIL_PAGE_TYPE_TRX_SYS 0x0007 事务系统数据
FIL_PAGE_TYPE_FSP_HDR 0x0008 表空间头部信息
FIL_PAGE_TYPE_XDES 0x0009 扩展描述页
FIL_PAGE_TYPE_BLOB 0x000A BLOB页
FIL_PAGE_INDEX 0x45BF 索引页,也就是我们所说的 数据页

页面通用部分

image.png

  • File Header ：记录页面的一些通用信息
  • File Trailer ：校验页是否完整，保证从内存到磁盘刷新时内容的一致性。

File Header 的各个组成部分
image.png

独立表空间结构

区（extent）的概念

表空间中的页实在是太多了，为了更好的管理这些页面，设计 InnoDB 的大叔们提出了 区 （英文名： extent ） 的概念。对于16KB的页来说，连续的64个页就是一个 区 ，也就是说一个区默认占用1MB空间大小。不论是系统 表空间还是独立表空间，都可以看成是由若干个区组成的，每256个区被划分成一组。
image.png
其中 extent 0 ~ extent 255 这256个区算是第一个组， extent 256 ~ extent 511 这256个区算是第二个 组， extent 512 ~ extent 767 这256个区算是第三个组（上图中并未画全第三个组全部的区，请自行脑补）， 依此类推可以划分更多的组。这些组的头几个页面的类型都是类似的，就像这样：

image.png
第一个组最开始的3个页面的类型是固定的，也就是说 extent 0 这个区最开始的3个页面的类型是固定的， 分别是：

  • FSP_HDR 类型：这个类型的页面是用来登记整个表空间的一些整体属性以及本组所有的 区 ，也就是 extent 0 ~ extent 255 这256个区的属性。需要注意的一点是，整个表空间只有一 个 FSP_HDR 类型的页面。

  • IBUF_BITMAP 类型：这个类型的页面是存储本组所有的区的所有页面关于 INSERT BUFFER 的信息。

  • INODE 类型：这个类型的页面存储了许多称为 INODE 的数据结构

其余各组最开始的2个页面的类型是固定的，也就是说 extent 256 、 extent 512 这些区最开始的2个页面 的类型是固定的，分别是:

  • XDES 类型：全称是 extent descriptor ，用来登记本组256个区的属性，也就是说对于在 extent 256 区中的该类型页面存储的就是 extent 256 ~ extent 511 这些区的属性，对于在 extent 512 区中的该 类型页面存储的就是 extent 512 ~ extent 767 这些区的属性。上边介绍的 FSP_HDR 类型的页面其实 和 XDES 类型的页面的作用类似，只不过 FSP_HDR 类型的页面还会额外存储一些表空间的属性。

  • IBUF_BITMAP 类型

段（segment）的概念

我们每向表中插入一条记录，本质上就是向该表的聚簇索引以及所有二级索引代表的 B+ 树的节点中插入数 据。而 B+ 树的每一层中的页都会形成一个双向链表，如果是以 页 为单位来分配存储空间的话，双向链表相 邻的两个页之间的物理位置可能离得非常远。我们介绍 B+ 树索引的适用场景的时候特别提到范围查询只需 要定位到最左边的记录和最右边的记录，然后沿着双向链表一直扫描就可以了，而如果链表中相邻的两个页 物理位置离得非常远，就是所谓的 随机I/O 。再一次强调，磁盘的速度和内存的速度差了好几个数量级， 随 机I/O 是非常慢的，所以我们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可 以使用所谓的 顺序I/O 。

所以才引入了 区 （ extent ）的概念，一个区就是在物理位置上连续的64个页。在表中数据量大 的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照 区 为单位分配，甚至在表中的数据 十分非常特别多的时候，可以一次性分配多个连续的区。虽然可能造成一点点空间的浪费（数据不足填充满整个 区），但是从性能角度看，可以消除很多的随机 I/O ，功大于过。

对 B+ 树叶子节点中的记录进行顺序扫描，而 如果不区分叶子节点和非叶子节点，统统把节点代表的页面放到申请到的区中的话，进行范围扫描的效果就大打 折扣了。所以设计 InnoDB 的大叔们对 B+ 树的叶子节点和非叶子节点进行了区别对待，也就是说叶子节点有自己 独有的 区 ，非叶子节点也有自己独有的 区 。存放叶子节点的区的集合就算是一个 段 （ segment ），存放非叶 子节点的区的集合也算是一个 段 。也就是说一个索引会生成2个段，一个叶子节点段，一个非叶子节点段。

默认情况下一个使用 InnoDB 存储引擎的表只有一个聚簇索引，一个索引会生成2个段，而段是以区为单位申请存 储空间的，一个区默认占用1M存储空间，所以默认情况下一个只存了几条记录的小表也需要2M的存储空间么？ 以后每次添加一个索引都要多申请2M的存储空间么？这对于存储记录比较少的表简直是天大的浪费。设计 InnoDB 的大叔们都挺节俭的，当然也考虑到了这种情况。这个问题的症结在于到现在为止我们介绍的区都是非 常 纯粹 的，也就是一个区被整个分配给某一个段，或者说区中的所有页面都是为了存储同一个段的数据而存在 的，即使段的数据填不满区中所有的页面，那余下的页面也不能挪作他用。现在为了考虑以完整的区为单位分配 给某个段对于数据量较小的表太浪费存储空间的这种情况，设计 InnoDB 的大叔们提出了一个碎片（fragment） 区的概念，也就是在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页 可以用于不同的目的，比如有些页用于段A，有些页用于段B，有些页甚至哪个段都不属于。碎片区直属于表空 间，并不属于任何一个段。所以此后为某个段分配存储空间的策略是这样的：

  • 在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的。
  • 当某个段已经占用了32个碎片区页面之后，就会以完整的区为单位来分配存储空间。

所以现在段不能仅定义为是某些区的集合，更精确的应该是某些零散的页面以及一些完整的区的集合。除了索引 的叶子节点段和非叶子节点段之外， InnoDB 中还有为存储一些特殊的数据而定义的段，比如回滚段，当然我们 现在并不关心别的类型的段，现在只需要知道段是一些零散的页面以及一些完整的区的集合就好了。

区的分类

  • 空闲的区：现在还没有用到这个区中的任何页面。 有剩余空间的
  • 碎片区：表示碎片区中还有可用的页面。
  • 没有剩余空间的碎片区：表示碎片区中的所有页面都被使用，没有空闲页面。
  • 附属于某个段的区。每一个索引都可以分为叶子节点段和非叶子节点段，除此之外InnoDB还会另外定义一些 特殊作用的段，在这些段中的数据量很大时将使用区来作为基本的分配单位。

image.png
需要再次强调一遍的是，处于 FREE 、 FREE_FRAG 以及 FULL_FRAG 这三种状态的区都是独立的，算是直属于表空 间；而处于 FSEG 状态的区是附属于某个段的。

表>段>区>页>行

为了方便管理这些区，设计 InnoDB 的大叔设计了一个称为 XDES Entry 的结构（全称就是Extent Descriptor Entry），每一个区都对应着一个 XDES Entry 结构，这个结构记录了对应的区的一些属性。我们先看图来对这个 结构有个大致的了解：

image.png

XDES Entry 是一个40个字节的结构，大致分为4个部分，各个部分的释义如下：

  • Segment ID （8字节） 每一个段都有一个唯一的编号，用ID表示，此处的 Segment ID 字段表示就是该区所在的段。当然前提是该 区已经被分配给某个段了，不然的话该字段的值没啥意义。
  • List Node （12字节）这个部分可以将若干个 XDES Entry 结构串联成一个链表

image.png
如果我们想定位表空间内的某一个位置的话，只需指定页号以及该位置在指定页号中的页内偏移量即可。所以：

  • Pre Node Page Number 和 Pre Node Offset 的组合就是指向前一个 XDES Entry 的指针

  • Next Node Page Number 和 Next Node Offset 的组合就是指向后一个 XDES Entry 的指针。

  • State （4字节）

    这个字段表明区的状态。可选的值就是我们前边说过的那4个，分别是： FREE 、 FREE_FRAG 、 FULL_FRAG 和 FSEG 。具体释义就不多唠叨了，前边说的够仔细了。

  • Page State Bitmap （16字节）

    这个部分共占用16个字节，也就是128个比特位。我们说一个区默认有64个页，这128个比特位被划分为64 个部分，每个部分2个比特位，对应区中的一个页。比如 Page State Bitmap 部分的第1和第2个比特位对应 着区中的第1个页面，第3和第4个比特位对应着区中的第2个页面，依此类推， Page State Bitmap 部分的第 127和128个比特位对应着区中的第64个页面。这两个比特位的第一个位表示对应的页是否是空闲的，第二个 比特位还没有用。

XDES Entry链表

我们把事情搞这么麻烦的初心仅仅是想提高向表插入数据的效率 又不至于数据量少的表浪费空间。现在我们知道向表中插入数据本质上就是向表中各个索引的叶子节点段、非叶 子节点段插入数据，也知道了不同的区有不同的状态，再回到最初的起点，捋一捋向某个段中插入数据的过程：

  • 当段中数据较少的时候，首先会查看表空间中是否有状态为 FREE_FRAG 的区，也就是找还有空闲空间的碎片 区，如果找到了，那么从该区中取一些零碎的页把数据插进去；否则到表空间下申请一个状态为 FREE 的 区，也就是空闲的区，把该区的状态变为 FREE_FRAG ，然后从该新申请的区中取一些零碎的页把数据插进 去。之后不同的段使用零碎页的时候都会从该区中取，直到该区中没有空闲空间，然后该区的状态就变成了 FULL_FRAG 。

怎么知道表空间里的哪些区是 FREE ？哪些区的状态是 FREE_FRAG 的 ？哪些区是 FULL_FRAG 的？

这时候就是 XDES Entry 中的 List Node 部分发 挥奇效的时候了，我们可以通过 List Node 中的指针，做这么三件事：

  • 把状态为 FREE 的区对应的 XDES Entry 结构通过 List Node 来连接成一个链表，这个链表我们就称之 为 FREE 链表。
  • 把状态为 FREE_FRAG 的区对应的 XDES Entry 结构通过 List Node 来连接成一个链表，这个链表我们就 称之为 FREE_FRAG 链表。
  • 把状态为 FULL_FRAG 的区对应的 XDES Entry 结构通过 List Node 来连接成一个链表，这个链表我们就 称之为 FULL_FRAG 链表。

这样每当我们想找一个 FREE_FRAG 状态的区时，就直接把 FREE_FRAG 链表的头节点拿出来，从这个节点 中取一些零碎的页来插入数据，当这个节点对应的区用完时，就修改一下这个节点的 State 字段的值， 然后从 FREE_FRAG 链表中移到 FULL_FRAG 链表中。同理，如果 FREE_FRAG 链表中一个节点都没有，那 么就直接从 FREE 链表中取一个节点移动到 FREE_FRAG 链表的状态，并修改该节点的 STATE 字段值为 FREE_FRAG ，然后从这个节点对应的区中获取零碎的页就好了。

当段中数据已经占满了32个零散的页后，就直接申请完整的区来插入数据了。

还是那个问题，我们怎么知道哪些区属于哪个段的呢？再遍历各个 XDES Entry 结构？遍历是不可能遍历 的，这辈子都不可能遍历的，有链表还遍历个毛线啊。所以我们把状态为 FSEG 的区对应的 XDES Entry 结构 都加入到一个链表喽？傻呀，不同的段哪能共用一个区呢？你想把索引a的叶子节点段和索引b的叶子节点段 都存储到一个区中么？显然我们想要每个段都有它独立的链表，所以可以根据段号（也就是 Segment ID ）来建立链表，有多少个段就建多少个链表？好像也有点问题，因为一个段中可以有好多个区，有的区是完全 空闲的，有的区还有一些页面可以用，有的区已经没有空闲页面可以用了，所以我们有必要继续细分，设计 InnoDB 的大叔们为每个段中的区对应的 XDES Entry 结构建立了三个链表：

  • FREE 链表：同一个段中，所有页面都是空闲的区对应的 XDES Entry 结构会被加入到这个链表。注意和直属于表空间的FREE链表区别开了，此处的 FREE 链表是附属于某个段的。
  • NOT_FULL 链表：同一个段中，仍有空闲空间的区对应的 XDES Entry 结构会被加入到这个链表。
  • FULL 链表：同一个段中，已经没有空闲空间的区对应的 XDES Entry 结构会被加入到这个链表。

再次强调一遍，每一个索引都对应两个段，每个段都会维护上述的3个链表

链表基节点

上边光是介绍了一堆链表，可我们怎么找到这些链表呢，或者说怎么找到某个链表的头节点或者尾节点在表空间 中的位置呢？设计 InnoDB 的大叔当然考虑了这个问题，他们设计了一个叫 List Base Node 的结构，翻译成中文 就是链表的基节点。这个结构中包含了链表的头节点和尾节点的指针以及这个链表中包含了多少节点的信息，我 们画图看一下这个结构的示意图：
image.png
我们上边介绍的每个链表都对应这么一个 List Base Node 结构，其中：

  • List Length 表明该链表一共有多少节点，
  • First Node Page Number 和 First Node Offset 表明该链表的头节点在表空间中的位置。
  • Last Node Page Number 和 Last Node Offset 表明该链表的尾节点在表空间中的位置。

一般我们把某个链表对应的 List Base Node 结构放置在表空间中固定的位置，这样想找定位某个链表就变得so easy啦。

链表小结

综上所述，表空间是由若干个区组成的，每个区都对应一个 XDES Entry 的结构，直属于表空间的区对应的 XDES Entry 结构可以分成 FREE 、 FREE_FRAG 和 FULL_FRAG 这3个链表；每个段可以附属若干个区，每个段中的区对 应的 XDES Entry 结构可以分成 FREE 、 NOT_FULL 和 FULL 这3个链表。每个链表都对应一个 List Base Node 的 结构，这个结构里记录了链表的头、尾节点的位置以及该链表中包含的节点数。正是因为这些链表的存在，管理 这些区才变成了一件so easy的事情。

段的结构

段其实不对应表空间中某一个连续的物理区域，而是一个逻辑上的概念，由若干个零散的页面以 及一些完整的区组成。像每个区都有对应的 XDES Entry 来记录这个区中的属性一样，设计 InnoDB 的大叔为每个 段都定义了一个 INODE Entry 结构来记录一下段中的属性。大家看一下示意图：
image.png

  • Segment ID 就是指这个 INODE Entry 结构对应的段的编号（ID）。
  • NOT_FULL_N_USED 这个字段指的是在 NOT_FULL 链表中已经使用了多少个页面。下次从 NOT_FULL 链表分配空闲页面时可以直接 根据这个字段的值定位到。而不用从链表中的第一个页面开始遍历着寻找空闲页面。
  • 3个 List Base Node 分别为段的 FREE 链表、 NOT_FULL 链表、 FULL 链表定义了 List Base Node ，这样我们想查找某个段的某 个链表的头节点和尾节点的时候，就可以直接到这个部分找到对应链表的 List Base Node 。so easy!
  • Magic Number ： 这个值是用来标记这个 INODE Entry 是否已经被初始化了（初始化的意思就是把各个字段的值都填进去 了）。如果这个数字是值的 97937874 ，表明该 INODE Entry 已经初始化，否则没有被初始化。（不用纠结 这个值有啥特殊含义，人家规定的）。
  • Fragment Array Entry 我们前边强调过无数次段是一些零散页面和一些完整的区的集合，每个 Fragment Array Entry 结构都对应 着一个零散的页面，这个结构一共4个字节，表示一个零散页面的页号。

各类型页面详细情况

FSP_HDR 类型

首先看第一个组的第一个页面，当然也是表空间的第一个页面，页号为 0 。这个页面的类型是 FSP_HDR ，它存储 了表空间的一些整体属性以及第一个组内256个区的对应的 XDES Entry 结构
image.png
组成成分
image.png
File Space Header部分 从名字就可以看出来，这个部分是用来存储表空间的一些整体属性的
image.png
属性描述
image.png

  • List Base Node for FREE List 、 List Base Node for FREE_FRAG List 、 List Base Node for FULL_FRAG List 。

    这三个大家看着太亲切了，分别是直属于表空间的 FREE 链表的基节点、 FREE_FRAG 链表的基节点、 FULL_FRAG 链表的基节点，这三个链表的基节点在表空间的位置是固定的，就是在表空间的第一个页面（也 就是 FSP_HDR 类型的页面）的 File Space Header 部分。所以之后定位这几个链表就so easy啦。

  • FRAG_N_USED

    这个字段表明在 FREE_FRAG 链表中已经使用的页面数量，方便之后在链表中查找空闲的页面。

  • FREE Limit

    我们知道表空间都对应着具体的磁盘文件，一开始我们创建表空间的时候对应的磁盘文件中都没有数据，所 以我们需要对表空间完成一个初始化操作，包括为表空间中的区建立 XDES Entry 结构，为各个段建立 INODE Entry 结构，建立各种链表吧啦吧啦的各种操作。我们可以一开始就为表空间申请一个特别大的空 间，但是实际上有绝大部分的区是空闲的，我们可以选择把所有的这些空闲区对应的 XDES Entry 结构加入 FREE 链表，也可以选择只把一部分的空闲区加入 FREE 链表，等啥时候空闲链表中的 XDES Entry 结构对应 的区不够使了，再把之前没有加入 FREE 链表的空闲区对应的 XDES Entry 结构加入 FREE 链表，中心思想就 是啥时候用到啥时候初始化，设计 InnoDB 的大叔采用的就是后者，他们为表空间定义了 FREE Limit 这个字 段，在该字段表示的页号之前的区都被初始化了，之后的区尚未被初始化。

  • Next Unused Segment ID

    表中每个索引都对应2个段，每个段都有一个唯一的ID，那当我们为某个表新创建一个索引的时候，就意味 着要创建两个新的段。那怎么为这个新创建的段找一个唯一的ID呢？去遍历现在表空间中所有的段么？我们 说过，遍历是不可能遍历的，这辈子都不可能遍历，所以设计 InnoDB 的大叔们提出了这个名叫 Next Unused Segment ID 的字段，该字段表明当前表空间中最大的段ID的下一个ID，这样在创建新段的时候赋予 新段一个唯一的ID值就so easy啦，直接使用这个字段的值就好了。 Space Flags 表空间对于一些布尔类型的属性，或者只需要寥寥几个比特位搞定的属性都放在了这个 Space Flags 中存 储，虽然它只有4个字节，32个比特位大小，却存储了好多表空间的属性，详细情况如下表：

标志名称 占用的空间（单位：bit） 描述
POST_ANTELOPE 1 表示文件格式是否大于 ANTELOPE
ZIP_SSIZE 4 表示压缩页面的大小
ATOMIC_BLOBS 1 表示是否自动把值非常长的字段放到BLOB页里
PAGE_SSIZE 4 页面大小
DATA_DIR 1 表示表空间是否是从默认的数据目录中获取的
SHARED 1 是否为共 享表空间
TEMPORARY 1 是否为临时表空间
ENCRYPTION 1 表空间是否加密
UNUSED 18 没有使用到的比 特位
  • List Base Node for SEG_INODES_FULL List 和 List Base Node for SEG_INODES_FREE List

每个段对应的 INODE Entry 结构会集中存放到一个类型位 INODE 的页中，如果表空间中的段特别多，则会有 多个 INODE Entry 结构，可能一个页放不下，这些 INODE 类型的页会组成两种列表：

  • SEG_INODES_FULL 链表，该链表中的 INODE 类型的页面都已经被 INODE Entry 结构填充满了，没空闲 空间存放额外的 INODE Entry 了。
  • SEG_INODES_FREE 链表，该链表中的 INODE 类型的页面都已经仍有空闲空间来存放 INODE Entry 结 构。

XDES Entry部分

我们知道一个 XDES Entry 结构的大小是40字节，但是一个页面的 大小有限，只能存放有限个 XDES Entry 结构，所以我们才把256个区划分成一组，在每组的第一个页面中存放 256个 XDES Entry 结构。

XDES 类型

表空间的区分为了若干个组，每组开头的一个页面记录着本组内所有的区对应的 XDES Entry 结构。 由于第一个组的第一个页面有些特殊，因为它也是整个表空间的第一个页面，所以除了记录本组中的所有区对应 的 XDES Entry 结构以外，还记录着表空间的一些整体属性，这个页面的类型就是我们刚刚说完的 FSP_HDR 类 型，整个表空间里只有一个这个类型的页面。除去第一个分组以外，之后的每个分组的第一个页面只需要记录本 组内所有的区对应的 XDES Entry 结构即可，不需要再记录表空间的属性了，为了和 FSP_HDR 类型做区别，我们 把之后每个分组的第一个页面的类型定义为 XDES ，它的结构和 FSP_HDR 类型是非常相似的：

image.png

  • IBUF_BITMAP 类型

    对比前边介绍表空间的图，每个分组的第二个页面的类型都是 IBUF_BITMAP ，这种类型的页里边记录了一些有 关 Change Buffer 的东东，由于这个 Change Buffer 里又包含了贼多的概念，考虑到大家在一章中接受这么多新 概念有点呼吸不适，怕大家心脏病犯了所以就把 Change Buffer 的相关知识放到后边的章节中，大家稍安勿躁 哈。

  • INODE 类型

    再次对比前边介绍表空间的图，第一个分组的第三个页面的类型是 INODE 。我们前边说过设计 InnoDB 的大叔为 每个索引定义了两个段，而且为某些特殊功能定义了些特殊的段。为了方便管理，他们又为每个段设计了一个 INODE Entry 结构，这个结构中记录了关于这个段的相关属性。而我们这会儿要介绍的这个 INODE 类型的页就是 为了存储 INODE Entry 结构而存在的。好了，废话少说，直接看图

image.png
从图中可以看出，一个 INODE 类型的页面是由这几部分构成的：
image.png

SEG_INODES_FULL 链表：该链表中的 INODE 类型的页面中已经没有空闲空间来存储额外的 INODE Entry 结构了。
SEG_INODES_FREE 链表：该链表中的 INODE 类型的页面中还有空闲空间来存储额外的 INODE Entry 结构了。

Segment Header 结构的运用

我们知道一个索引会产生两个段，分别是叶子节点段和非叶子节点段，而每个段都会对应一个 INODE Entry 结 构，那我们怎么知道某个段对应哪个 INODE Entry 结构呢？所以得找个地方记下来这个对应关系。希望你还记得 我们在唠叨数据页，也就是 INDEX 类型的页时有一个 Page Header 部分，当然我不能指望你记住，所以把 Page Header 部分再抄一遍给你看：
image.png
其中的 PAGE_BTR_SEG_LEAF 和 PAGE_BTR_SEG_TOP 都占用10个字节，它们其实对应一个叫 Segment Header 的结 构，该结构图示如下：
image.png
各个部分的具体释义如下：
image.png

系统表空间

系统表空间的结构和独立表空间基本类 似，只不过由于整个MySQL进程只有一个系统表空间，在系统表空间中会额外记录一些有关整个系统信息的页 面，所以会比独立表空间多出一些记录这些信息的页面。

系统表空间的整体结构

image.png

可以看到，系统表空间和独立表空间的前三个页面（页号分别为 0 、 1 、 2 ，类型分别是 FSP_HDR 、 IBUF_BITMAP 、 INODE ）的类型是一致的，只是页号为 3 ～ 7 的页面是系统表空间特有的，我们来看一下这些 多出来的页面都是干啥使的：

image.png
除了这几个记录系统属性的页面之外，系统表空间的 extent 1 和 extent 2 这两个区，也就是页号从 64 ~ 191 这128个页面被称为 Doublewrite buffer ，也就是双写缓冲区。

InnoDB数据字典

image.png
这些系统表也被称为 数据字典 ，它们都是以 B+ 树的形式保存在系统表空间的某些页面中，其中 SYS_TABLES 、 SYS_COLUMNS 、 SYS_INDEXES 、 SYS_FIELDS 这四个表尤其重要，称之为基本系统表（basic system tables），我们先看看这4个表的结构：

  • SYS_TABLES表

image.png
这个 SYS_TABLES 表有两个索引： 以 NAME 列为主键的聚簇索引 以 ID 列建立的二级索引

  • SYS_COLUMNS表

image.png
这个 SYS_COLUMNS 表只有一个聚集索引： 以 (TABLE_ID, POS) 列为主键的聚簇索引

  • SYS_INDEXES表

image.png

这个 SYS_INEXES 表只有一个聚集索引： 以 (TABLE_ID, ID) 列为主键的聚簇索引

  • SYS_FIELDS表

image.png
这个 SYS_INEXES 表只有一个聚集索引： 以 (INDEX_ID, POS) 列为主键的聚簇索引

Data Dictionary Header页面

只要有了上述4个基本系统表，也就意味着可以获取其他系统表以及用户定义的表的所有元数据。比方说我们想 看看 SYS_TABLESPACES 这个系统表里存储了哪些表空间以及表空间对应的属性，那就可以：

  • 到 SYS_TABLES 表中根据表名定位到具体的记录，就可以获取到 SYS_TABLESPACES 表的 TABLE_ID
  • 使用这个 TABLE_ID 到 SYS_COLUMNS 表中就可以获取到属于该表的所有列的信息。
  • 使用这个 TABLE_ID 还可以到 SYS_INDEXES 表中获取所有的索引的信息，索引的信息中包括对应的 INDEX_ID ，还记录着该索引对应的 B+ 数根页面是哪个表空间的哪个页面。
  • 使用 INDEX_ID 就可以到 SYS_FIELDS 表中获取所有索引列的信息。

也就是说这4个表是表中之表，那这4个表的元数据去哪里获取呢？没法搞了，只能把这4个表的元数据，就是它 们有哪些列、哪些索引等信息硬编码到代码中，然后设计 InnoDB 的大叔又拿出一个固定的页面来记录这4个表的 聚簇索引和二级索引对应的 B+树 位置，这个页面就是页号为 7 的页面，类型为 SYS ，记录了 Data Dictionary Header ，也就是数据字典的头部信息。除了这4个表的5个索引的根页面信息外，这个页号为 7 的页面还记录了 整个InnoDB存储引擎的一些全局属性，说话太啰嗦，直接看这个页面的示意图

image.png
可以看到这个页面由下边几个部分组成：
image.png
可以看到这个页面里竟然有 Segment Header 部分，意味着设计InnoDB的大叔把这些有关数据字典的信息当成一 个段来分配存储空间，我们就姑且称之为 数据字典段 吧。由于目前我们需要记录的数据字典信息非常少（可以 看到 Data Dictionary Header 部分仅占用了56字节），所以该段只有一个碎片页，也就是页号为 7 的这个页。

接下来我们需要细细唠叨一下 Data Dictionary Header 部分的各个字段：

Max Row ID ：我们说过如果我们不显式的为表定义主键，而且表中也没有 UNIQUE 索引，那么 InnoDB 存储 引擎会默认为我们生成一个名为 row_id 的列作为主键。因为它是主键，所以每条记录的 row_id 列的值不能 重复。原则上只要一个表中的 row_id 列不重复就可以了，也就是说表a和表b拥有一样的 row_id 列也没啥 关系，不过设计InnoDB的大叔只提供了这个 Max Row ID 字段，不论哪个拥有 row_id 列的表插入一条记录 时，该记录的 row_id 列的值就是 Max Row ID 对应的值，然后再把 Max Row ID 对应的值加1，也就是说这 个 Max Row ID 是全局共享的。

Max Table ID ：InnoDB存储引擎中的所有的表都对应一个唯一的ID，每次新建一个表时，就会把本字段的 值作为该表的ID，然后自增本字段的值。

Max Index ID ：InnoDB存储引擎中的所有的索引都对应一个唯一的ID，每次新建一个索引时，就会把本字 段的值作为该索引的ID，然后自增本字段的值。

Max Space ID ：InnoDB存储引擎中的所有的表空间都对应一个唯一的ID，每次新建一个表空间时，就会把 本字段的值作为该表空间的ID，然后自增本字段的值。

Mix ID Low(Unused) ：这个字段没啥用，跳过。 Root of SYS_TABLES clust index ：本字段代表 SYS_TABLES 表聚簇索引的根页面的页号。

Root of SYS_TABLE_IDS sec index ：本字段代表 SYS_TABLES 表为 ID 列建立的二级索引的根页面的页 号。

Root of SYS_COLUMNS clust index ：本字段代表 SYS_COLUMNS 表聚簇索引的根页面的页号。

Root of SYS_INDEXES clust index 本字段代表 SYS_INDEXES 表聚簇索引的根页面的页号。

Root of SYS_FIELDS clust index ：本字段代表 SYS_FIELDS 表聚簇索引的根页面的页号。

Unused ：这4个字节没用，跳过。

information_schema系统数据库

需要注意一点的是，用户是不能直接访问 InnoDB 的这些内部系统表的，除非你直接去解析系统表空间对应文件 系统上的文件。不过设计InnoDB的大叔考虑到查看这些表的内容可能有助于大家分析问题，所以在系统数据库 information_schema 中提供了一些以 innodb_sys 开头的表

image.png
在 information_schema 数据库中的这些以 INNODB_SYS 开头的表并不是真正的内部系统表（内部系统表就是我们 上边唠叨的以 SYS 开头的那些表），而是在存储引擎启动时读取这些以 SYS 开头的系统表，然后填充到这些以 INNODB_SYS 开头的表中。以 INNODB_SYS 开头的表和以 SYS 开头的表中的字段并不完全一样。

image.png

第10章条条大路通罗马-单表访问方法

mysql中查询分为两种

  • 全表扫描
  • 索引查询

mysql查询预警称为访问方法或访问类型，同一个查询语句可以使用不同的访问方法来执行，最后的结果虽然一致，但其查询的性能差距可能很大

const

表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数，如通过主键列来定位一条记录，其直接走聚族索引。

ref

通过普通的二级索引列与常数进行等值比较，这种方式如果数据量小会走索引树进行查询，如果数据量大可能会走全表扫描，其不是主键索引可能存在多条相同记录

ref_or_null

当我们想通过二级索引查询出索引值为某个常数且为NULL的值

range

利用索引进行范围查询

对于B+树索引只有索引列和常数使用 =、 <=>、 in、 not in、is null、is not null 、>、>=、<、<=、Between、!=或者like操作符连接起来可以产生一个区间

index

直接遍历二级索引可以得出数据结构的查询方式，无需回表查询聚族索引，因为聚族索引存储所谓的隐藏列，其成本比二级索引高

all

全表扫描，对于innodb来说也就是直接扫描聚族索引

第11章两个表的亲密接触- 连接的原理

连接的本质

两个表之间进行全表连接 N * N = N ^ 2 组成的笛卡尔积

连接的过程

  1. 选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方式来执行对驱动表的单表查询
  2. 对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查询匹配的记录

内连接与外连接

连接的原理

嵌套循环连接（Nested-Loop Join）

对于两表连接来说，驱动表只会被访问一次，被驱动表却要被访问好多遍

1
2
3
4
5
6
7
for each row in t1 {
	for each row in t2 {
    	for each row in t3 {
        	if row statisfies join conditions,send to client
		}
	}
}

基于块的嵌套循环连接（Block Nested-Loop Join）

当被驱动表的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会从内存中清除掉，然后再从驱动表中拿出另一条记录，再一次把驱动表的记录加载到内存中一遍，周而复始。如果我们可以一次性和多条驱动表记录进行匹配，那就大大减少了磁盘的IO，所以MySQL设计了join buffer的概念，join buffer 就是执行连接查询前申请一块固定大小的内存，把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表。

第12章谁最便宜就选谁-MySQL基于成本的优化

  • I/O成本
  • CPU成本

在执行一条SQL查询之前，MySQL的查询优化器会找出执行该语句所有可能的方案，对比之后找出成本最低的，这个成本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口执行真正的查询

  1. 根据搜索条件，找出所有可能使用的索引
  2. 计算全表扫描的代价
  3. 计算使用不同索引执行查询的代价
  4. 对比各种执行方案的代价，找出成本最低的那个

第13章兵马未动，粮草先行-InnoDB统计数据是如何收集的

  • 永久性的统计数据

    这种数据存储在磁盘上，也就是服务器重启之后这些统计数据还在

  • 非永久性的统计数据

    该数据存储在内存中，服务器关闭会被清除掉，等服务器重启，在某些适当的场景下进行重新收集

innodb_table_stats 存储了关于表的统计数据，每一条记录对应着一个表的统计数据。
innodb_index_stats 存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据。

第14章不好看就要多整容-MySQL基于规则的优化(内含关于

第15章查询优化的百科全书-Explain详解 (上)

1
explain [sql]

image.png

第16章查询优化的百科全书-Explain详解 (下)

使用 FORMAT=JSON可以以json方式查看数据

1
2
3
4
5
6
explain format=JSON
select sm.* from sys_user_of_role suor
    left join sys_role sr on user_uuid = '9df9de08354f456c97fae0cdb3df214f' and suor.role_uuid = sr.uuid
    left join sys_role_of_menu srom on sr.uuid = srom.role_uuid
    left join sys_menu sm on srom.menu_uuid = sm.uuid
            where sm.id is not null

在执行EXPLAIN后可以通过 show warinings 查看重写后SQL语句（code=1003）

第17章神兵利器- optimizer trace表的神器功效

optimizer trace用于帮助我们查看优化器生成执行计划的整个过程

查看是否已经开启

1
show variables like 'optimizer_trace'

开启

1
set optimizer_trace = "enabled=on"

执行SQL
查看对应的optimizer trace

1
select * from information_schema.OPTIMIZER_TRACE;

不需使用时关闭

1
set optimizer_trace = "enabled=off"

第18章调节磁盘和CPU的矛盾-InnoDB的Buffer Pool

第19章从猫爷被杀说起-事务简介

第20章说过的话就一定要办到-redo日志 (上)

redo log 是持久性的基础，当我们的数据在修改过程中还未flush到硬盘，服务器发生宕机导致内存数据全部丢失，在恢复时可以通过redo log来恢复

  • redo log 占用的空间非常小
  • redo 日志是顺序写入磁盘的

rodo log 格式

image.png
各个部分的详细释义如下：

  1. type ：该条 redo 日志的类型。 在 MySQL 5.7.21 这个版本中，设计 InnoDB 的 redo 日志设计了53种不同的类型。
  2. space ID ：表空间ID。
  3. page number ：页号。
  4. data ：该条 redo 日志的具体内容。

redo 日志中只需要记录一下在某个页面的某个偏移量处修改了几个字节的值，具体被修改的内容是啥 就好了，设计 InnoDB 把这种极其简单的 redo 日志称之为 物理日志 ，并且根据在页面中写入数据的多少，划分了几种不同的 redo 日志类型：

  • MLOG_1BYTE （ type 字段对应的十进制数字为 1 ）：表示在页面的某个偏移量处写入1个字节的 redo 日志类型。
  • MLOG_2BYTE （ type 字段对应的十进制数字为 2 ）：表示在页面的某个偏移量处写入2个字节的 redo 日志类型。
  • MLOG_4BYTE （ type 字段对应的十进制数字为 4 ）：表示在页面的某个偏移量处写入4个字节的 redo 日志类型。
  • MLOG_8BYTE （ type 字段对应的十进制数字为 8 ）：表示在页面的某个偏移量处写入8个字节的 redo 日志类型。
  • MLOG_WRITE_STRING （ type 字段对应的十进制数字为 30 ）：表示在页面的某个偏移量处写入一串数据。

redo日志的写入过程

**redo log block **
设计 InnoDB 的大叔为了更好的进行系统奔溃恢复，他们把通过 mtr 生成的 redo 日志都放在了大小为 512字节的 页 中。为了和我们前边提到的表空间中的页做区别，我们这里把用来存储 redo 日志的页称为 block （页和block的意思其实差）。一个 redo log block 的示意图如下：
image.png

第21章说过的话就一定要办到-redo日志 (下)

redo日志文件

刷入时机：

  1. log buffer 空间不足时

    log buffer 的大小是有限的（通过系统变量 innodb_log_buffer_size 指定），如果不停的往这个有限大小的 log buffer 里塞入日志，很快它就会被填满。设计 InnoDB 的大叔认为如果当前写入 log buffer 的 redo 日志量已经占满了 log buffer 总容量的大约一半左右，就需要把这些日志刷新到磁盘上。

  2. 事务提交时

    之所以使用 redo 日志主要是因为它占用的空间少，还是顺序写，在事务提交时可以不把修改过的 Buffer Pool 页面刷新到磁盘，但是为了保证持久性，必须要把修改这些页面对应的 redo 日志刷新到磁盘。

  3. 后台线程不停的刷刷刷

    后台有一个线程，大约每秒都会刷新一次 log buffer 中的 redo 日志到磁盘。

  4. 正常关闭服务器时

  5. 做所谓的 checkpoint 时

  6. 其他的一些情况

第22章后悔了怎么办-undo日志(上)

undo用于数据回滚，其事务的原子性就是依赖于undo log来实现

undo log 格式

image.png

第23章后悔了怎么办-undo日志(下)

第24章-条记录的多幅面孔-事务的隔离级别与MVCC

MVCC原理

版本链

trx_id ：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的 事务id 赋值给 trx_id 隐藏列。
roll_pointer ：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo日志 中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。
image.png

ReadView

核心问题是：需要判断一下版本链中的哪个版本是当前事务可见的

InnoDB 的大叔提出了一个 ReadView 的概念，这个 ReadView 中主要包含4个比较重要的内容：

  1. m_ids ：表示在生成 ReadView 时当前系统中活跃的读写事务的 事务id 列表。

  2. min_trx_id ：表示在生成 ReadView 时当前系统中活跃的读写事务中最小的 事务id ，也就是 m_ids 中的最小值。

  3. max_trx_id ：表示生成 ReadView 时系统中应该分配给下一个事务的 id 值。

    注意max_trx_id并不是m_ids中的最大值，事务id是递增分配的。比方说现在有id为1，2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，m_ids就包括1和2，mi n_trx_id的值就是1，max_trx_id的值就是4。

  4. creator_trx_id ：表示生成该 ReadView 的事务的 事务id 。

有了这个 ReadView ，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：

  1. 如果被访问版本的 trx_id 属性值与 ReadView 中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
  2. 如果被访问版本的 trx_id 属性值小于 ReadView 中的 min_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问。
  3. 如果被访问版本的 trx_id 属性值大于 ReadView 中的 max_trx_id 值，表明生成该版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问。
  4. 如果被访问版本的 trx_id 属性值在 ReadView 的 min_trx_id 和 max_trx_id 之间，那就需要判断一下trx_id 属性值是不是在 m_ids 列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。

第25章工作面试老大难-锁

image.png

一致性读（Consistent Reads） **
事务利用 MVCC 进行的读取操作称之为 一致性读 ，或者 一致性无锁读 ，有的地方也称之为 **快照读 。

第26章写作本书时用到的一些重 要的参考资料

PDF: MySQL是怎样运行的：从根儿上理解MySQL.pdf
源码：https://github.com/mysql/mysql-server
文档：https://dev.mysql.com/doc/

数据库组 > MySQL
#MySQL
MySQL篇 -《MySQL是怎样运行的》
https://mikeygithub.github.io/2021/07/04/yuque/MySQL篇-《MySQL是怎样运行的》/
作者
Mikey
发布于
2021年7月4日
许可协议