MySQL架构体系、存储引擎和索引结构

MySQL是当今最通用的数据库软件之一，也是大部分人接触最多，时间最长的数据库软件之一。深入了解MySQL的架构和设计对于DBA，研发和运维都非常重要，能够帮助我们在日常工作中更好地理解和运用MySQL。

  • SQL语句在数据库底层的执行过程？
  • MySQL底层数据存储结构？
  • MySQL索引结构为什么使用b+树？
  • MySQL锁机制、种类和实现原理?
  • MySQL事务是如何实现的

一、MySQL架构图

首先先了解MySQL的架构图，如下所示

从上面MySQL的架构图，可以看出MySQL的架构大致可以分为网络连接层、数据库服务层、存储引擎层和系统文件层四大部分。

1.1 网络连接层

MySQL架构体系的最上层是网络连接层，主要是客户端连接器。提供与MySQL服务器建立连接，几乎所有主流的服务端语言都支持，包括C、C++、Java、php等，都是通过各自的API接口与MySQL建立连接。

1.2 数据库服务层

数据库服务层是MySQL数据库服务器的核心，主要包括了连接器、查询缓存(MySQL8已删除)、解析器、查询优化器和执行器等部分。所有跨引擎的功能也是在这一层。

1.2.1 连接器

主要负责客户端和服务器建立连接，校验用户名和密码，连接池会存储和管理客户端与数据库的连接信息，连接池里的一个线程负责管理一个客户端到数据库的连接信息。

1.2.2 查询缓存

当数据库执行完一条sql语句时，会缓存它的结果(通过query_cache_type参数开启查询缓存)。再次执行同一条查询语句时，会直接从缓存里查询。但是MySQL不推荐使用查询缓存，第一因为查询sql的语句必须完全相同，多一个空格，都会认为是一条不同的SQL语句，不命中缓存。第二是频繁失效，只要表结构或数据发生变化，缓存都会清空。所以MySQL 8.0版本中直接删掉了查询缓存。

1.2.3 解析器

如果查询缓存没有命中，接下来就需要进入正式的查询阶段了。客户端程序发送过来的请求事实上只是一段文本而已，所以MySQL服务器程序首先需要对这段文本做分析，判断请求的语法是否正确，然后从文本中将要查询的表、列和各种查询条件都提取出来，本质上是对一个SQL语句编译的过程，词法分析器分成6个主要步骤完成对sql语句的分析。

  1. 词法分析
  2. 语法分析
  3. 语义分析
  4. 构造执行树
  5. 生成执行计划
  6. 计划的执行

下图是SQL词法分析的过程步骤：

1. 词法分析

词法分析就是把一个完整的SQL语句分割成一个个的字符串，比如这条简单的SQL语句

select customer_id, first_name, last_name from customer where customer_id = 14;

会被分割成10个字符串

select, customer_id, first_name, last_name, from, customer, where, customer_id, =, 14

2. 语法分析

分析器的第二步是根据词法分析的结果，语法分析器会根据语法规则做语法检查，判断你输入的这个SQL语句是否满足MySQL语法。如果你的语句不对，就会收到"You have an error in your SQL syntax"的错误提醒，比如下面这个语句select少打了开头的字母"s"。

mysql > elect customer_id, first_name, last_name from customer where customer_id =14;
ERROR 1064(42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect customer_id, first_name, last_name from customer where customer_id = 14' at line 1

然后根据MySQL定义的语法规则，根据SQL语句生成一个数据结构。这个数据结构叫做解析树(select_lex)。

20210709225156.png

3. 语义分析

语义分析主要分析是否符合MySQL语法规则。

4. 预处理器

预处理器则会进一步去检查解析树是否合法，比如表名是否存在，语句中表的列是否存在等等，在这一步MySQL会检验用户是否有表的操作权限。预处理之后会得到一个新的解析树。

1.2.4 查询优化器

在MySQL中，如果“解析树”通过了解析器的语法检查，此时就会由优化器将其转化为执行计划，然后选择一种最优的执行计划与存储引擎进行交互，通过存储引擎与底层的数据文件进行交互。MySQL使用的是基于成本模型的优化器，哪种执行计划成本最低就使用哪种(MySQL选择它认为的成本小的，但成本小不意味着执行时间短)。

优化器都做了哪些优化呢？

  1. 当多个索引可用时，决定使用哪个索引；
  2. 重新定义表的关联顺序(多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序)；
  3. 提前终止查询(比如：使用Limit时，查找到满足数量的结果集后会立即终止查询)；
  4. 优化MIN()和MAX()函数(找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值)；
  5. 优化排序(在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多)。

1.2.5 执行器

MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，得到了一个查询计划。于是就进入了执行器阶段，开始执行语句。

(1). 开始执行的时候，要先判断一下你对这个表customer有没有执行查询的权限，如果没有，就会返回没有权限的错误。(在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证)
(2). 如果有权限，就使用指定的存储引擎打开表开始查询。执行器会根据表的引擎定义，去使用这个引擎提供的查询接口，提取数据。

1.3 存储引擎层

MySQL中的存储引擎层主要负责数据的写入和读取，与底层的文件进行交互。值得一提的是，MySQL中的存储引擎是插件式的，服务器中的查询执行引擎通过相关的接口与存储引擎进行通信，同时，接口屏蔽了不同存储引擎之间的差异。MySQL中，最常用的存储引擎就是InnoDB和MyISAM。

1.4 系统文件层

系统文件层主要包括MySQL中存储数据的底层文件，与上层的存储引擎进行交互，是文件的物理存储层。其存储的文件主要有：日志文件、数据文件、配置文件、MySQL的进行pid文件和socket文件等。

二、MySQL数据存储

MySQL在Linux中的数据索引文件和日志文件一般默认都在/var/lib/mysql目录下。

2.1 日志文件

日志可以分为二进制日志、错误日志、通用查询日志和慢查询日志。

  • 二进制日志(bin log)：该日志文件会以二进制的形式记录数据库的各种操作，但不记录查询语句。
  • 错误日志(error log)：该日志文件会记录MySQL服务器的启动、关闭和运行错误等信息。
  • 通用查询日志(general query log)：该日志记录MySQL服务器的启动和关闭信息、客户端的连接信息、更新、查询数据记录的SQL语句等。
  • 慢查询日志(slow query log)：记录执行事件超过指定时间的操作，通过工具分析慢查询日志可以定位MySQL服务器性能瓶颈所在。

关于回滚日志、重做日志请参考MySQL三大日志

2.1.1 错误日志

错误日志(error log)是MySQL中最常用的一种日志，主要记录MySQL服务器启动和停止过程中的信息、服务器在运行过程中发生的故障和异常情况等。

1. 启动和设置错误日志

在MySQL数据库中，默认开启错误日志功能。一般情况下，错误日志存储在MySQL数据库的数据文件夹下，默认名称为hostname.err。其中，hostname表示MySQL服务器的主机名。

在MySQL配置文件中，错误日志所记录的信息可以通过log-error和log-warnings来定义

  • log-err定义是否启用错误日志功能和错误日志的存储位置。
  • log-warnings定义是否将警告信息也记录到错误日志中。

将log_error选项加入到MySQL配置文件的[mysqld]组中，形式如下：

[mysqld]
# dir参数指定错误日志的存储路径
# filename参数指定错误日志的文件名，
# 省略参数时文件名默认为主机名，存放在Data目录中
# 修改参数需要重启MySQL服务
log-error=dir/{filename}

注意：错误日志中记录的并非全是错误信息，例如MySQL如何启动InnoDB的表空间文件、如何初始化自己的存储引擎等，这些也记录在错误日志文件中。

2. 查看错误日志

SHOW VARIABLES LIKE 'log_error';

3. 删除错误日志

在MySQL中，可以使用mysqladmin命令来开启新的错误日志，以保证MySQL服务器上的硬盘空间。

mysqladmin命令的语法如下：

mysqladmin -uroot -p flush-logs

2.1.2 二进制日志

二进制日志(Binary Log)或变更日志(Update Log)，主要用于记录数据库的变化情况，即SQL语句的DDL和DML语句，不包含数据记录查询操作。

如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。

默认情况下，二进制日志功能是关闭的。可以通过以下命令查看二进制日志是否开启，命令如下：

SHOW VARIABLES LIKE 'log_bin';

1. 启动和设置二进制日志

在MySQL中，可以通过在配置文件中添加log-bin选项来开启二进制日志，格式如下：

[mysqld]
# dir参数指定二进制文件的存储路径
# filename参数指定二进制文件的文件名
log-bin=dir/[filename]

filename其形式为filename.number，number的形式为000001、000002等。

每次重启MySQL服务后，都会生成一个新的二进制日志文件，这些日志文件的文件名中filename部分不会改变，number会不断递增。

如果没有dir和filename参数，二进制日志将默认存储在数据库的数据目录下，默认的文件名为hostname-bin.number，其中hostname表示主机名。

mysql-bin.000001
mysql-bin.000002
...
mysql-bin.000010

2. 查看二进制日志

# 查看二进制日志文件列表
SHOW binary logs;

# 查看当前正在写入的二进制日志文件
SHOW master status;

# 查看二进制日志文件内容
mysqlbinlog filename.number;

3. 删除二进制日志

# 删除所有二进制日志
RESET MASTER;

# 根据编号删除二进制日志
PURGE MASTER LOGS TO 'filename.number';

# 根据创建时间删除二进制日志
PURGE MASTER LOGS TO 'yyyy-mm-dd hh:MM:ss';

4. 暂时停止二进制日志

# 0表示暂停二进制日志功能
# 1表示开启二进制功能
SET SQL_LOG_BIN=0/1;

5. 二进制日志还原数据库

mysqlbinlog filename.number | mysql -u root -p

必须是编号(number)小的先还原。例如，mylog.000001必须在mylog.000002之前还原。

6. 拓展

my.ini中的[mysqld]组下面有几个设置参数是关于二进制日志的：

# MySQL清除过期日志的时间、二进制日志自动删除的天数。默认值为0，表示“没有自动删除”
expire_logs_days = 10

# 单个文件的大小限制
# 如果二进制日志写入的内容大小超出给定值，日志就会发生滚动(关闭当前文件，重新打开一个新的日志文件)。
# 不能将该变量设置为大于1GB或小于4096B(字节)，其默认值是1GB
max_binlog_size = 100M

2.1.3 通用查询日志

通用查询日志(General Query Log)，用来记录用户的所有操作，包括启动和关闭MySQL服务、更新语句和查询语句等。

默认情况下通用查询日志是关闭的，不建议开启。

1. 启动和设置通用查询日志

[mysqld]
log=dir/filename

2. 查看通用查询日志

SHOW VARIABLES LIKE '%general%';

3. 停止通用查询日志

通用查询日志启动后，可以通过两种方法停止该日志。一种是将MySQL配置文件中的相关配置注释掉，然后重启服务器，来停止通用查询日志。具体内容如下：

[mysqld]
#log=dir\filename

上述方法需要重启MySQL服务器，这在某些场景，比如有业务量访问的情况下是不允许的，这时可以通过另一种方法来动态地控制通用查询日志的开启和关闭。

设置MySQL的环境变量general_log为关闭状态可以停止该日志，示例如下：

SET GLOBAL general_log=off;

4. 删除通用查询日志

mysqladmin -uroot -p flush-logs

2.1.4 慢查询日志

慢查询日志用来记录在MySQL中执行时间超过指定时间的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率低，以便进行优化。

1. 启动和设置慢查询日志

可以通过log-slow-queries选项开启慢查询日志。通过long_query_time选项来设置时间值，时间以秒为单位。如果查询时间超过了这个时间值，这个查询语句将被记录到慢查询日志。

[mysqld]
log-slow-queries=dir\filename

# n”参数是设定的时间值，该值的单位是秒。如果不设置long_query_time选项，默认时间为10秒。
long_query_time=n

还可以通过以下命令启动慢查询日志、设置指定时间：

SET GLOBAL slow_query_log=ON/OFF;
SET GLOBAL long_query_time=n;

文件名如下：

mysql-slow.log

2. 查看慢查询日志

慢查询日志也是以文本文件的形式存储的，可以使用普通的文本文件查看工具来查看。SQL语句和执行过程如下：

mysql> USE test;
Database changed
mysql> SELECT * FROM tb_student;
+----+--------+
| id | name   |
+----+--------+
|  1 | Java   |
|  2 | MySQL  |
|  3 | Python |
+----+--------+
3 rows in set (0.08 sec)

相应的，慢查询日志的部分内容如下：

# Time: 2020-06-01T01:59:18.368780Z
# User@Host: root[root] @ localhost [::1] Id:     3
# Query_time: 0.006281  Lock_time: 0.000755 Rows_sent: 2 Rows_examined: 1034
use test;
SET timestamp=1590976758;
SHOW VARIABLES LIKE 'slow_query%';

2.1.5 重做日志(redo log)

默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件，这就是InnoDB的重做日志文件(redo log file)，它记录了对于InnoDB存储引擎的事务日志。

当InnoDB的数据存储文件发生错误时，重做日志文件就能派上用场。InnoDB存储引擎可以使用重做日志文件将数据恢复为正确状态，以此来保证数据的正确性和完整性。

用户可以使用innodb_log_file_size来设置重做日志文件的大小，这对InnoDB存储引擎的性能有着非常大的影响。

如果重做日志文件设置的太大，数据丢失时，恢复时可能需要很长的时间；另一方面，如果设置的太小，重做日志文件太小会导致依据checkpoint的检查需要频繁刷新脏页到磁盘中，导致性能的抖动。

2.1.6 回滚日志(undo log)

ibdata1

2.2 数据文件

存储引擎负责对表中数据的读取和写入，每个存储引擎会以自己的方式来保存表中的数据，在不同存储引擎中数据存放的方式一般是不同的。MySQL的数据文件存放在位置，可以通过参数datadir控制。

  • 查看MySQL数据文件：
SHOW VARIABLES LIKE '%datadir%';

2.2.1 InnoDB数据文件

  1. ibd文件：使用独享表空间存储表数据和索引信息，一张表对应一个.ibd文件。
  2. ibdata文件：使用共享表空间存储表数据和索引信息，所有表共同使用一个或者多个ibdata文件。

2.2.2 MyIsam数据文件

  1. myd文件：主要用来存储表数据信息。
  2. myi文件：主要用来存储表数据文件中任何索引的数据树。

2.2.3 表结构文件(frm文件)

MySQL数据的存储是基于表的，每个表都有一个对应的表结构文件。不论表使用的哪一种存储引擎，MySQL都会为表生成一个.frm为后缀名的文件，这个文件记录了这个表的表结构定义。

user_innodb.frm
user_innodb.ibd
user_myisam.frm
user_myisam.myd
user_myisam.myi

三、MySQL存储结构

InnoDB存储引擎逻辑存储结构可分为五级：表空间、段、区、页、行。

20210322171352507.png

3.1 表空间

表空间(Tablespace)是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、独占表空间、通用表空间、撤销表空间、临时表空间和Undo表空间等。

在InnoDB中存在两种表空间的类型：共享表空间和独立表空间。如果是共享表空间就意味着多张表共用一个表空间。如果是独立表空间，就意味着每张表有一个独立的表空间，也就是数据和索引信息都会保存在自己的表空间中。独立的表空间可以在不同的数据库之间进行迁移。可通过命令

show variables like 'innodb_file_per_table';

查看当前系统启用的表空间类型。目前最新版本已经默认启用独立表空间。

  • 如果开启了独立表空间innodb_file_per_table=1，每张表一个单独的.ibd文件。
  • 如果关闭了独立表空间innodb_file_per_table=0，所有基于InnoDB存储引擎的表数据都会记录到系统表空间，文件名为ibdata1。

3.2 段

段(Segment)由一个或多个区组成，区在文件系统是一个连续分配的空间(在InnoDB中是连续的64个页)，不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。

当我们创建数据表、索引的时候，就会创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。常见的段有数据段、索引段、回滚段等。

3.3 区

在InnoDB存储引擎中，一个区会分配64个连续的页。因为InnoDB中的页大小默认是16KB，所以一个区的大小是64*16KB=1MB。

在任何情况下每个区大小都为1MB，为了保证页的连续性，InnoDB存储引擎每次从磁盘一次申请4-5个区。默认情况下，InnoDB存储引擎的页大小为16KB，即一个区中有64个连续的页。

3.4 页

3.4.1 概述

页是InnoDB管理磁盘的最小单位，也是InnoDB中磁盘和内存交互的最小单位。每个页默认大小时是16KB，可以通过参数innodb_page_size将页的大小设置为4K、8K、16K。

若设置完成，则所有表中页的大小都为innodb_page_size，不可以再次对其进行修改，除非通过mysqldump导入和导出操作来产生新的库。

索引树上一个节点就是一个页，MySQL规定一个页上最少存储2个数据项。如果向一个页插入数据时，这个页已将满了，就会从区中分配一个新页。

如果向索引树叶子节点中间的一个页中插入数据，如果这个页是满的，就会发生页分裂。操作系统管理磁盘的最小单位是磁盘块，是操作系统读写磁盘最小单位，Linux中页一般是4K，可以通过命令查看。

# 默认4096(4K)
getconf page_size

innoDB存储引擎中，常见的页类型有：

  1. 数据页(B-tree Node)
  2. undo页(undo Log Page)
  3. 系统页(System Page)
  4. 事物数据页(Transaction System Page)
  5. 插入缓冲位图页(Insert Buffer Bitmap)
  6. 插入缓冲空闲列表页(Insert Buffer Free List)
  7. 未压缩的二进制大对象页(Uncompressed BLOB Page)
  8. 压缩的二进制大对象页(compressed BLOB Page)

3.4.2 页结构

先贴一张Innodb引擎中的Page完整的结构图

page.jpeg

上面的概念实在太多了，为了方便理解，可以按下面的分解一下Page的结构

pageStruct.jpg

每部分的意义
pageStruct2.png

innodbPageStruct.jpg

页结构整体上可以分为三大部分，分别为通用部分(文件头、文件尾)、存储记录空间、索引部分。

1. 通用部分

第一部分通用部分，主要指文件头和文件尾，将页的内容进行封装，通过文件头和文件尾校验的CheckSum方式来确保页的传输是完整的。

在文件头中有两个字段，分别是FIL_PAGE_PREV和FIL_PAGE_NEXT，它们的作用相当于指针，分别指向上一个数据页和下一个数据页。连接起来的页相当于一个双向的链表，如下图所示：

innodbPageStruct3.jpg

需要说明的是采用链表的结构让数据页之间不需要是物理上的连续，而是逻辑上的连续。

2. 记录部分

第二个部分是记录部分，页的主要作用是存储记录，所以“最小和最大记录”和“用户记录”部分占了页结构的主要空间。另外空闲空间是个灵活的部分，当有新的记录插入时，会从空闲空间中进行分配用于存储新记录，如下图所示：

innodbPageStruct2.jpg

一个页内必须存储2行记录，否则就不是B+tree，而是链表了。

3. 索引部分

第三部分是索引部分，这部分重点指的是页目录(PageDirectory)，示意图PageHeader中的s0-sn，它起到了记录的索引作用，因为在页中，记录是以单向链表的形式进行存储的。单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索，因此在页目录中提供了二分查找的方式，用来提高记录的检索效率。这个过程就好比是给记录创建了一个目录：

将所有的记录分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录。

  • 第1组，也就是最小记录所在的分组只有1个记录；
  • 最后一组，就是最大记录所在的分组，会有1-8条记录；
  • 其余的组记录数量在4-8条之间。

这样做的好处是，除了第1组(最小记录所在组)以外，其余组的记录数会尽量平分。在每个组中最后一条记录的头信息中会存储该组一共有多少条记录，作为n_owned字段。页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽(slot)，每个槽相当于指针指向了不同组的最后一个记录。如下图所示：

innodbPageDir.jpg

页目录存储的是槽，槽相当于分组记录的索引。我们通过槽查找记录，实际上就是在做二分查找。这里我以上面的图示进行举例，5个槽的编号分别为0，1，2，3，4，我想查找主键为9的用户记录，我们初始化查找的槽的下限编号，设置为low = 0，然后设置查找的槽的上限编号high=4，然后采用二分查找法进行查找。

首先找到槽的中间位置\(p = (low + high) / 2 = (0 + 4) / 2 = 2\)，这时我们取编号为2的槽对应的分组记录中最大的记录，取出关键字为8。因为9大于8，所以应该会在槽编号为[p,high]的范围进行查找

接着重新计算中间位置\(p = (p + high) / 2 = (2 + 4) / 2 = 3\)，我们查找编号为3的槽对应的分组记录中最大的记录，取出关键字为12。因为9小于12，所以应该在槽3中进行查找。

遍历槽3中的所有记录，找到关键字为9的记录，取出该条记录的信息即为我们想要查找的内容。

3.5 行

InnoDB的数据是以行为单位存储的，1个页中包含多个行。每个页存放的行记录也是有硬性定义的，最多允许存放16KB/2-200，即7992行记录。

InnoDB到现在为止设计了4种不同类型的行格式，分别是Compact、Redundant、Dynamic和Compressed行格式。

MySQL5.0之后的默认行格式为Compact，5.7之后的默认行格式为dynamic。

3.5.1 变长字段长度列表

MySQL支持一些变长的数据类型，比如varchar(m)、varbinary(m)、各种text类型，各种blob类型，我们也可以把拥有这些数据类型的列称为变长字段，变长字段中存储多少字节的数据是不固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来，这样才不至于把MySQL服务器搞懵，所以这些变长字段占用的存储空间分为两部分：

  • 真正的数据内容
  • 占用的字节数

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

举个例子：

一个表中有c1、c2、c3三列数据为varchar，其中有一列数据存储了(“1234”,“123”,“1”)，它们分别的字符长度就为04、03、01，若其使用ascii字符集存储，则每个的字节大小为，04、03、01(ascii用一字节表示一个字符，utf-8为3字节)，则这一行在”变长字段长度列表“中存储的则为”01 03 04“(实际存储为二进制且没有空格)

由于上面的字符串都比较短，也就是说内容占用的字节数比较小，用1个字节就可以表示，但是如果变长列的内容占用的字节数比较多，可能就需要用2个字节来表示。具体用1个还是2个字节来表示真实数据占用的字节数，InnoDB有它的一套规则，首先我们声明一下W、M和L的意思：

1.假设某个字符集中表示一个字符最多需要使用的字节数为W，比方说utf8字符集中的W就是1-3，ascii字符集中的W就是1。
2.对于varchar(M)来说，表示此列最多能储存M个字符，所以这个类型能表示的字符串最多占用的字节数就是M×W。(比如：对于一个字符串”aaa“使用ascii表示则占用13个字节，而对于utf-8则为33个字节)
3.假设某字符串实际占用的字节数是L。

基于以上的声明，则使用1字节还是2字节来表示变长字段长度的规则为：

  • 如果一个字段最长可以储存的字节数小于等于255B，即W*M <= 255: 使用一个字节表示
  • 如果W*M > 255B，则分为两种情况：
    • 若L <= 127B 则用1字节表示
    • 若L > 127B则用2字节表示

此外，InnoDB使用字节的第一位作为标志位，如果第一位为0，则此字节就是一个单独的字段长度。如果为1，则该字节为半个字段长度。

对于一些占用字节数非常多的字段，比方说某个字段长度大于了16KB，那么如果该记录在单个页面中无法存储时，InnoDB会把一部分数据存放到所谓的溢出页中(我们后边会唠叨)，在变长字段长度列表处只存储留在本页面中的长度，所以使用两个字节也可以存放下来。

另外需要注意的一点是，变长字段长度列表中只存储值为非null的列内容占用的长度，值为null的列的长度是不储存的。

字符集utf-8，英文字符占用1个字节，中文字符3字节，对于char类型来说，若使用utf-8字符集，则char也属于可变长字段

3.5.2 null值列表

我们知道表中的某些列可能存储null值，如果把这些null值都放到记录的真实数据中存储会很占地方，所以Compact行格式把这些值为null的列统一管理起来，存储到null值列表中，它的处理过程是这样的：

  1. 首先统计表中允许存储null的列有哪些。我们前边说过，主键列、被not null修饰的列都是不可以存储null值的，所以在统计的时候不会把这些列算进去。
  2. 如果表中没有允许存储null的列，则null值列表不存在。若允许，则将每个允许存储null的列对应一个二进制位，二进制位按照列的顺序逆序排列：二进制位的值为1时，代表该列的值为null。二进制位的值为0时，代表该列的值不为null。

MySQL规定NULL值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0。即若一个表有9个值允许为null，则这个记录null值列表的部分需要用2字节表示。

举个例子：若有一张表，有c1 c2 c3 c4四个字段，其中c2被not null修饰，则其null值列表表示如下：

3.5.3 记录头信息

1. 概述

记录头信息部分如下图所示：

名称 大小(bit) 描述
预留位1 1 没有使用
预留位2 1 没有使用
delete_mask 1 标记该记录是否被删除
min_rec_mask 1 B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned 4 表示当前记录拥有的记录数
heap_no 13 表示当前记录在记录堆的位置信息
record_type 3 表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录
next_record 16 表示下一条记录的相对位置

我们使用如下的sql语句插入几行数据：

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

则它们这几条数据记录在页的User Records部分为：

delete_mask

这个属性标记着当前记录是否被删除，占用1个二进制位，值为0的时候代表记录并没有被删除，为1的时候代表记录被删除掉了

被删除的记录还在页中。这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗，所以只是打一个删除标记而已，所有被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为所谓的可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉。

min_rec_mask

B+树的每层非叶子节点中的最小记录都会添加该标记。上方插入的四条记录的min_rec_mask值都是0，意味着它们都不是B+树的非叶子节点中的最小记录。

n_owned

当前组的最大记录，记录当前组有几个元素的字段。

heap_no

这个属性表示当前记录在本页中的位置，从图中可以看出来，我们插入的4条记录在本页中的位置分别是：2、3、4、5。

0和1被分配给了最小记录和最大记录。

2. 默认隐藏列信息

MySQL会为每个记录默认的添加一些列(也称为隐藏列)

列名 是否必须 占用空间 描述
row_id 否 6字节 行ID,唯一标识一条记录
transaction_id 是 6字节 事务ID
roll_pointer 是 7字节 回滚指针

实际上这几个列的真正名称其实是：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR，我们为了美观才写成了row_id、transaction_id和roll_pointer。

row_id是可选的，表中没有主键的，则选取一个Unique键作为主键。如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。

roll_pointer是一个指向记录对应的undo日志的一个指针。

3. 行溢出的数据

我们知道对于VARCHAR(M)类型的列最多可以占用65535个字节。其中的M代表该类型最多存储的字符数量，如果我们使用ascii字符集的话，一个字符就代表一个字节。但是实际上，创建一张表并设置一个字段为VARCHAR(65535)则会报错。

CREATE TABLE varchar_size_demo(
    c VARCHAR(65535)
) CHARSET=ascii ROW_FORMAT=Compact;

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not
counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to c
hange some columns to TEXT or BLOBs

从报错信息里可以看出，MySQL对一条记录占用的最大存储空间是有限制的，除了BLOB或者TEXT类型的列之外，其他所有的列(不包括隐藏列和记录头信息)占用的字节长度加起来不能超过65535个字节。所以MySQL服务器建议我们把存储类型改为TEXT或者BLOB的类型。这个65535个字节除了列本身的数据之外，还包括一些其他的数据(storage overhead)，比如说我们为了存储一个VARCHAR(M)类型的列，其实需要占用3部分存储空间：

  • 真实数据
  • 真实数据占用字节的长度
  • NULL值标识，如果该列有NOT NULL属性则可以没有这部分存储空间

如果该VARCHAR类型的列没有NOT NULL属性，那最多只能存储65532个字节的数据，因为真实数据的长度可能占用2个字节，NULL值标识需要占用1个字节
如果VARCHAR类型的列有NOT NULL属性，那最多只能存储65533个字节的数据，因为真实数据的长度可能占用2个字节，不需要NULL值标识

相应的，如果不使用ascii字符集，而使用utf-8的话，则要按照3个字节一个字符来计算。

另外，这里我们只讨论了一张表只有一个字段的情况，实际上是一行数据最多只能储存上面那些字节。

记录中的数据太多产生的溢出

我们知道，一页最大为16KB也就是16384字节，而一个varchar类型的列最多可以储存65532字节，这样就可能造成一张数据页放不了一行数据的情况。

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

对于Compact和Reduntant行格式来说，如果某一列中的数据非常多的话，在本记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中，这个过程也叫做行溢出，存储超出768字节的那些页面也被称为溢出页。

行溢出的临界点

首先，MySQL中规定一个页中至少存放两行记录。其次，以创建只有一个varchar(65532)字段的表为例，我们分析一下一个页面的空间是如何利用的：

  • 除了用户储存的真实信息外，储存文件头、文件尾、页面头等信息，需要136个字节。
  • 每条记录需要的额外信息是27字节，这27字节包括：
    • 2个字节用于存储真实数据的长度
    • 1个字节用于存储列是否是null值
    • 5个字节大小的头信息
    • 6个字节的row_id列
    • 6个字节的transaction_id列
    • 7个字节的roll_pointer列

假设一个列中存储的数据字节数为n，那么发生行溢出现象时需要满足这个式子：\(136 + 2×(27 + n) > 16384\)

求解这个式子得出的解是：n > 8098。也就是说如果一个列中存储的数据不大于8098个字节，那就不会发生行溢出，否则就会发生行溢出。

不过这个8098个字节的结论只是针对只有一个varchar(65532)列的表来说的，如果表中有多个列，那上边的式子和结论都需要改一改了，所以重点就是:不用关注这个临界点是什么，只要知道如果我们想一个行中存储了很大的数据时，可能发生行溢出的现象。

3.5.4 Dynamic和Compressed行格式

MySQL版本5.7之后默认行格式是Dynamic，这俩行格式和Compact行格式挺像，只不过在处理行溢出数据时不同，它们不会在记录的真实数据处存储字段真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址：

Compressed行格式和Dynamic不同的一点是，Compressed行格式会采用压缩算法对页面进行压缩，以节省空间。

四、MySQL索引结构

4.1 InnoDB索引简介

InnoDB索引-官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.htm

4.1.1 主键索引

主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。InnoDB要求表必须有一个主键索引(MyISAM可以没有)。

4.1.2 辅助索引

除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。

4.2 磁盘数据如何加载到InnoDB内存中

4.2.1 机械硬盘如何读取数据？

表中的数据是存储在磁盘文件上的，MySQL在处理数据时，需要先把数据从磁盘上读取到内存中。

1. 一个硬盘一般由多个盘片组成，盘片的数量一般都在5片以内。

20191123182101621.png

盘片的逻辑结构主要分为磁道、扇区和拄面。一个盘面被分为若干个磁道，每个磁道又被划分为多个扇区。扇区是磁盘存储的最小单位，大小是512字节。

下图显示的是一个盘面，盘面中一圈圈灰色同心圆环为一条条磁道，从圆心向外画直线，可以将磁道划分为若干个弧段，每个磁道上一个弧段被称之为一个扇区(图中绿色部分)，每一个盘面有300～1024个磁道。

20191123183720314.png

2. 如何读取数据？

磁头要想读取数据，必须先根据磁盘地址找到对应的磁道，然后再等磁盘转到数据对应扇区后才能读取数据，一般会有十几毫秒的延迟。

读取步骤

传统机械硬盘读取数据的过程：

  1. 磁头移动到数据所在磁道。
  2. 磁盘旋转,将数据所在的扇区移至磁头之下。
  3. 磁盘继续旋转,所有所需的数据都被磁头从扇区中读出。

磁盘读取响应时间

磁盘的工作机制，决定了它读取数据的速度。读写一次磁盘信息所需的时间可分解为：寻道时间、延迟时间、传输时间。磁盘读取数据花费的时间，是这三个操作步骤所需时间之和。

  1. 寻道时间：第一步花费的时间，称为寻道时间。
    寻道时间越短，I/O操作越快，目前磁盘的寻道时间一般都在10ms左右。
  2. 旋转延迟：第二步花费的时间，称为旋转延迟。
    旋转延迟取决于磁盘转速，这一步相比寻道时间来说，比较快，远远小于1ms。
    普通硬盘一般都是7200转/分，根据硬盘型号的不同，磁道离圆心的距离的不同，一个磁道包含几百个，几千个扇区，按100个扇区来算，旋转延迟为0.08ms(转一圈大约为8ms)。
  3. 数据传输时间：完成传输所请求的数据所需要的时间。

3. 操作系统读取硬盘以磁盘块为单位

扇区是硬盘读写的最小单位，由于扇区的数量比较小，在寻址时花费的时间比较长，操作系统认为紧邻这个扇区的数据随后也是会被使用到，操作系统一般是以4KB的单位读取磁盘，读取后数据会被缓存在内存，称这个操作为预读。

4. MySQL读取以页为单位

MySQL本质上是一个软件，MySQL需要读取数据时，MySQL会调用操作系统的接口，操作系统会调用磁盘的驱动程序将数据读取到内核空间，然后将数据从内核空间copy到用户空间，随后MySQL就能从用户空间中读取到数据。操作系统读取磁盘时，Linux读取的最小单位一般为4K。最小单位由操作系统决定，不同的操作系统可能会有所不同。
MySQL的InnoDB存储引擎的数据读取以页为单位，也大小由参数innodb_page_size控制，默认值是16k。

五、InnoDB内存结构

d829920649c27f35a88dde148795d1f8.jpg

InnoDB中的内存部分知识点主要分为以下几大块：缓冲池(Buffer Pool)、额外内存池(Addtional Memory Pool)、重做日志缓冲(Redo Log Buffer)、双写缓冲池(Double Write)以及涉及到内存数据落盘的逻辑和检查点机制(CheckPoint)。

5.1 缓冲池(Buffer Pool)

InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。但是由于CPU速度和磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池记录来提高数据库的的整体性能。

5.1.1 数据页和索引页(data page和index page)

上文中提到InnoDB中磁盘和内存交互的最小单位是页，当InnoDB存储引擎工作时，需要将每页数据分4次从磁盘中加载到内存中，与数据库相关的所有内容都存储在Page结构里。

Page分为几种类型，数据页(data page)和索引页(index page)就是其中最为重要的两种类型。

5.1.2 更新(插入)缓冲(insert buffer page)

主要针对次要索引的数据插入存在的问题而设计。

在InnoDB引擎上进行插入操作时，次要索引在插入时，数据页的存放还是按照主键进行顺序存放，但是对于次要索引叶节点的插入不再是顺序的了，这时就需要离散的访问次要索引页，由于随机读取的存在导致插入操作性能下降。

InnoDB为此设计了Change(insert)Buffer来进行插入优化。对于次要索引的插入或者更新操作，不是每一次都直接插入到索引页中，而是先判断插入的非主键索引是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Change Buffer中。看似数据库这个非主键的索引已经插到叶节点，而实际没有，这时存放在另外一个位置。然后再以一定的频率和情况进行Change Buffer和非聚簇索引页子节点的合并操作。这时通常能够将多个插入合并到一个操作中，这样就大大提高了对于非聚簇索引的插入性能。

5.1.3 自适应哈希索引(adaptive hash index)

InnoDB会根据访问的频率和模式，为热点页建立哈希索引，来提高查询效率。

自适应hash索引在实现上就是一个哈希表，InnoDB会根据访问的频率和模式，为热点页建立哈希索引，来提高查询效率。本身的原理并不复杂，首先哈希表不能太大，因为会有成本开销，太小又不能保证命中率，所以在索引创建上会比较讲究。

5.1.4 锁信息(lock info)

InnoDB存储引擎会在行级别上对表数据进行上锁。不过InnoDB也会在数据库内部其他很多地方使用锁，从而允许对多种不同资源提供并发访问。数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。

5.1.5 数据字典信息(data dictionary)

InnoDB有自己的表缓存，可以称为表定义缓存或者数据字典(Data Dictionary)。

当InnoDB打开一张表，就增加一个对应的对象到数据字典。主要包含数据库中的数据、库对象、表对象等的元信息(如表结构、数据库名或表名、字段的数据类型、视图、索引、表字段信息、存储过程、触发器等内容)

5.2 额外内存池(Addtional Memory Pool)

额外内存池是InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间，控制参数为：

innodb_additional_mem_pool_size

这个参数一般很少用，大部分都是使用默认值，通常只有在mysql实例内数据库对象比较多的时候才会调整参数大小，以确保所有数据都能存放在内存中提高访问效率。这个参数大小是否足够还是比较容易知道的，因为当过小的时候，MySQL会记录Warning信息到数据库的error log中，这时候你就知道该调整这个参数大小了。

注：此参数在MySQL 5.7中移除。

5.3 重做日志缓冲(redo log buffer)

20210710000601.png

如上图所示，InnoDB在缓冲池中变更数据时，会首先将相关变更写入重做日志缓冲中，然后再按时或者当事务提交时写入磁盘，这符合Force-log-at-commit原则；当重做日志写入磁盘后，缓冲池中的变更数据才会依据checkpoint机制择时写入到磁盘中，这符合WAL原则。

在checkpoint择时机制中，就有重做日志文件写满的判断，所以，如前文所述，如果重做日志文件太小，经常被写满，就会频繁导致checkpoint将更改的数据写入磁盘，导致性能抖动。

操作系统的文件系统是带有缓存的，当InnoDB向磁盘写入数据时，有可能只是写入到了文件系统的缓存中，没有真正的落盘。

InnoDB的innodb_flush_log_at_trx_commit属性可以控制每次事务提交时InnoDB的行为。

  • 当属性值为0时，事务提交时，不会对重做日志进行写入操作，而是等待主线程按时写入每秒写入一次；
  • 当属性值为1时，事务提交时，会将重做日志写入文件系统缓存，并且调用文件系统的fsync，将文件系统缓冲中的数据真正写入磁盘存储，确保不会出现数据丢失；
  • 当属性值为2时，事务提交时，也会将日志文件写入文件系统缓存，但是不会调用fsync，而是让文件系统自己去判断何时将缓存写入磁盘。

innodb_flush_log_at_commit是InnoDB性能调优的一个基础参数，涉及InnoDB的写入效率和数据安全。当参数值为0时，写入效率最高，但是数据安全最低；参数值为1时，写入效率最低，但是数据安全最高；参数值为2时，二者都是中等水平。一般建议将该属性值设置为1，以获得较高的数据安全性，而且也只有设置为1，才能保证事务的持久性。

5.4 双写缓冲池(double write)

如果说Insert Buffer给InnoDB存储引擎带来了性能上的提升，那么double write带给InnoDB存储引擎的是数据页的可靠性。

DoubleWrite.png

如上图所示，double write由两部分组成，一部分是内存中的double write buffer，大小为2MB，另一部分是物理磁盘上共享表空间连续的128个页，大小也为2MB。

在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是通过memcpy函数将脏页先复制到内存中的double write buffer区域，之后通过double write buffer再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免操作系统缓冲写带来的问题。在完成double write页的写入后，再讲double wirite buffer中的页写入各个表空间文件中。

如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB存储引擎可以从共享表空间中的double write中找到该页的一个副本，将其复制到表空间文件中，再应用重做日志。

5.5 数据落盘

169677120201113115748285797315423.png

InnoDB内存缓冲池中的数据page要完成持久化的话，是通过两个流程来完成的，一个是脏页落盘；一个是预写redo log日志。

当缓冲池中的页的版本比磁盘要新时，数据库需要将新版本的页从缓冲池刷新到磁盘。但是如果每次一个页发送变化，就进行刷新，那么性能开发是非常大的，于是InnoDB采用了Write AheadLog(WAL)策略和Force Log at Commit机制实现事务级别下数据的持久性。

WAL要求数据的变更写入到磁盘前，首先必须将内存中的日志写入到磁盘；Force-log-at-commit要求当一个事务提交时，所有产生的日志都必须刷新到磁盘上，如果日志刷新成功后，缓冲池中的数据刷新到磁盘前数据库发生了宕机，那么重启时，数据库可以从日志中恢复数据。

为了确保每次日志都写入到重做日志文件，在每次将重做日志缓冲写入重做日志后，必须调用一次fsync操作，将缓冲文件从文件系统缓存中真正写入磁盘。可以通过innodb_flush_log_at_trx_commit来控制重做日志刷新到磁盘的策略。

5.5.1 脏页落盘

在数据库中进行读取操作，将从磁盘中读到的页放在缓冲池中，下次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。

对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为CheckPoint的机制刷新回磁盘。

5.5.2 重做日志落盘

这里主要说一下Redo Log Buffer什么时候写入到redo log文件。

Redo Log Buffer写入磁盘的时机，由参数innodb_flush_log_at_trx_commit控制，默认是1，表示事务提交后立即落盘。

show VARIABLES like 'innodb_flush_log_at_trx_commit';

用户程序写入数据到磁盘文件时，需要调用操作系统的接口，操作系统本身是有缓冲区的，之后依赖操作系统机制不时的将缓存中刷新到磁盘文件中。用户程序可以执行fsync操作将操作系统缓冲区的数据刷入到磁盘文件中。

  • 0：MySQL每秒一次将数据从log buffer写入日志文件并同时fsync刷新到磁盘中。
    每次事务提交时，不会立即把log buffer里的数据写入到redo log日志文件的。如果MySQL崩溃或者服务器宕机，此时内存里的数据会全部丢失，最多会丢失1秒的事务。
  • 1：每次事务提交时，MySQL将数据将从log buffer写入日志文件并同时fsync刷新到磁盘中。
    该模式为系统默认，MySQL崩溃已经提交的事务不会丢失，要完全符合ACID，必须使用默认设置1。
  • 2：每次事务提交时，MySQL将数据从log buffer写入日志文件，MySQL每秒执行一次fsync操作将数据同步到磁盘中。
    每次事务提交时，都会将数据刷新到操作系统缓冲区，可以认为已经持久化磁盘，如果MySQL崩溃已经提交的事务不会丢失。但是如果服务器宕机或者意外断电，操作系统缓存内的数据会丢失，所以最多丢失1秒的事务。

只有设置为1是最安全但是性能消耗的方式，可以真正地保证事务的持久性，但是由于MySQL执行刷新操作fsync()是阻塞的，直到完成后才会返回，我们知道写磁盘的速度是很慢的，因此MySQL的性能会明显地下降。

0和2的性能最好的模式，综合安全性和性能的考虑，在业务中经常使用的2这种模式，在MySQL异常重启时不会丢失数据，只有在服务器意外宕机时才会丢失1秒的数据，这种情况几率是很低的，相对于性能来说，这时可以容忍的。

5.6 检查点机制(CheckPoint)

  • 当数据库发生宕机时，数据库不需要重做所有的日志，因为CheckPoint之前的页都已经刷新回磁盘。数据库只需对Checkpoint后的重做日志进行恢复，这样就大大缩短了恢复的时间。
  • 当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行Checkpoint，将脏页也就是页的新版本刷回磁盘。
  • 当重做日志出现不可用时，因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让其无限增大的。重做日志可以被重用的部分是指这些重做日志已经不再需要，当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用。如果重做日志还需要使用，那么必须强制Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

六、MySQL是如何实现事务的？

6.1 原子性，持久性和一致性

原子性，持久性和一致性主要是通过redo log、undo log、Force Log at Commit和DoubleWrite机制来完成的。

redo log用于在崩溃时恢复数据
undo log用于对事务回滚时进行撤销，也会用于隔离性的多版本控制。
Force Log at Commit机制保证事务提交后redo log日志都已经持久化。
Double Write机制用来提高数据库的可靠性，用来解决脏页落盘时部分写失效问题。

6.2 使用MVCC实现事务的隔离性

6.2.1 回滚段/undolog

根据行为的不同，undo log分为两种：insert undo log和update undo log

  • insert undo log：

    是在insert操作中产生的undo log。因为insert操作的记录只对事务本身可见，对于其它事务此记录是不可见的，所以insert undo log可以在事务提交后直接删除而不需要进行purge操作。

  • update undo log：

    是update或delete操作中产生的undo log。
    因为会对已经存在的记录产生影响，为了提供MVCC机制，因此update undo log不能在事务提交时就进行删除，而是将事务提交时放到入history list上，等待purge线程进行最后的删除操作。
    为了保证事务并发操作时，在写各自的undo log时不产生冲突，InnoDB采用回滚段的方式来维护undolog的并发写入和持久化。回滚段实际上是一种Undo文件组织方式。

6.2.2 ReadView

对于使用READUNCOMMITTED隔离级别的事务来说，直接读取记录的最新版本就好了。
对于使用SERIALIZABLE隔离级别的事务来说，使用加锁的方式来访问记录。
对于使用READCOMMITTED和REPEATABLEREAD隔离级别的事务来说，就需要用到我们上边所说的版本链了。

核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。所以设计InnoDB的设计者提出了一个ReadView的概念，这个ReadView中主要包含当前系统中还有哪些活跃的读写事务，把它们的事务id放到一个列表中，我们把这个列表命名为为m_ids。
这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本(版本链中的版本)是否可见：

  • 如果被访问版本的trx_id属性值小于m_ids列表中最小的事务id，表明生成该版本的事务在生成ReadView前已经提交，所以该版本可以被当前事务访问。
  • 如果被访问版本的trx_id属性值大于m_ids列表中最大的事务id，表明生成该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问。
  • 如果被访问版本的trx_id属性值在m_ids列表中最大的事务id和最小事务id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

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

在MySQL中，READCOMMITTED和REPEATABLEREAD隔离级别的一个非常大的区别就是它们生成ReadView的时机不同。

七、MySQL是如何加行锁的？

7.1 RR隔离级别下的加锁机制

133330210710001644.png

7.2 RC隔离级别下的加锁机制

间隙锁时为了解决幻读问题，在RC允许出现幻读现象所以RC隔离级别下行锁都加的是记录锁。只有在外键约束检查(foreign-key constraint checking)以及唯一键检查(duplicate-keychecking)时会使用间隙锁封锁区间。

八、拓展

8.1 查询语句的执行过程

一条sql语句是如何执行的呢？其实可以分为两种，一种是查询，一种是更新(增加，更新，删除)。我们先分析下查询语句，语句如下：

select * from T where id = 2;

结合上面的说明，我们分析下这个语句的执行流程：

  1. 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在MySQL8.0版本以前，会先查询缓存，以这条sql语句为key在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。
  2. 通过分析器进行词法分析，提取sql语句的关键元素，比如提取上面这个语句是查询select，提取需要查询的表名为T，需要查询所有的列，查询条件是这个表的id='2'。然后判断这个sql语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。
  3. 接下来就是优化器进行确定执行方案，优化器根据自己的优化算法进行选择执行效率最好的一个方案(优化器认为，有时候不一定最好)。那么确认了执行计划后就准备开始执行了。
  4. 进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

8.2 更新语句的执行流程

与查询流程不一样的是，更新流程还涉及两个重要的日志模块，redo log(重做日志)和binlog(归档日志)

以上就是一条查询sql的执行流程，那么接下来我们看看一条更新语句如何执行的呢？sql语句如下：

update T set a = 1 where id = 2;

其实也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志啦，这就会引入日志模块了，MySQL自带的日志模块式binlog(归档日志)，所有的存储引擎都可以使用，我们常用的InnoDB引擎还自带了一个日志模块redo log(重做日志)，我们就以InnoDB模式下来探讨这个语句的执行流程。流程如下：

  1. 先查询到张三这一条数据，如果有缓存，也是会用到缓存。
  2. 然后拿到查询的语句，把id改为2，然后调用引擎API接口，写入这一行数据。
  3. InnoDB引擎把数据保存在内存中，同时记录redolog，此时redolog进入prepare状态，然后告诉执行器，执行完成了，随时可以提交。
  4. 执行器收到通知后记录binlog，并把binlog写入磁盘
  5. 执行器调用引擎提交事务接口，提交redo log为提交状态，更新完成。

这里肯定有同学会问，为什么要用两个日志模块，用一个日志模块不行吗?

这是因为最开始MySQL并没与InnoDB引擎(InnoDB引擎是其他公司以插件形式插入MySQL的)，MySQL自带的引擎是MyISAM，但是我们知道redo log是InnoDB引擎特有的，其他存储引擎都没有，这就导致会没有crash-safe的能力(crash-safe的能力即使数据库发生异常重启，之前提交的记录都不会丢失)，binlog日志只能用来归档。

并不是说只用一个日志模块不可以，只是InnoDB引擎就是通过redo log来支持事务的。那么，又会有同学问，我用两个日志模块，但是不要这么复杂行不行，为什么redo log要引入prepare预提交状态？这里我们用反证法来说明下为什么要这么做？

  • 先写redo log直接提交，然后写binlog，假设写完redo log后，机器挂了，binlog日志没有被写入，那么机器重启后，这台机器会通过redo log恢复数据，但是这个时候binlog并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
  • 先写binlog，然后写redo log，假设写完了binlog，机器异常重启了，由于没有redo log，本机是无法恢复这一条记录的，但是binlog又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

如果采用redo log两阶段提交的方式就不一样了，写完binglog后，然后再提交redo log就会防止出现上述的问题，从而保证了数据的一致性。那么问题来了，有没有一个极端的情况呢？假设redo log处于预提交状态，binlog也已经写完了，这个时候发生了异常重启会怎么样呢？这个就要依赖于MySQL的处理机制了，MySQL的处理过程如下：

  • 判断redo log是否完整，如果判断是完整的，就立即提交。
  • 如果redo log只是预提交但不是commit状态，这个时候就会去判断binlog是否完整，如果完整就提交redo log，不完整就回滚事务。

这样就解决了数据一致性的问题。

参考文章

posted @ 2022-07-29 18:59  夏尔_717  阅读(165)  评论(0)  编辑  收藏  举报