《MySQL是怎样运行的》笔记

https://book-how-mysql-runs.netlify.app/#/

结构

• MySQL是 C/S 架构，一个服务端负责真正与储存的数据打交道，多个客户端连接服务端用于发送指令（例如SQL）

• mysql客户端连接服务端：mysql -h localhost -u root -p12345（注：-p后面不能有空格）

服务端处理客户端请求的过程：

1. 处理连接：客户端与服务器的连接方式有多种，例如TCP/IP连接、管道或共享内存、Unix域套接字

2. 解析与优化：查询缓存（查询系统表时不会走缓存，8.0后不使用缓存，避免太大开销） -> 语法解析 -> 语法优化

3. 存储引擎：InnoDB、MyISAM、Memory…，用于封装数据的存储和提取操作

通过上述内容可看出，mysql的架构也可以分为三层：客户端 -> MySQL server（负责管理客户端连接、缓存、语法解析和优化等操作） -> 存储引擎

查看MySQLServer支持的存储引擎：`show engines;`

存储引擎是针对表的，可以在表创建时指定，也能在创建后使用alter命令修改。

配置

在启动mysql服务端或客户端时，可以在命令后面跟上一些参数用于控制mysql的默认行为。

例如，启动mysql 服务端可以通过 mysqld -P3307 或 mysqld --port 3307 来指定端口

也可以通过修改配置文件的方式，配置文件的查找路径为：`/etc/my.cnf、/etc/mysql/my.cnf、SYSCONFDIR/my.cnf、$MYSQL_HOME/my.cnf …`

配置文件内容

类似于 .ini 文件的配置

[server]
option1 = value1
(具体启动选项。。。)
[client]
(具体启动选项。。。)
[mysqld]
(具体启动选项。。。)
。。。

不同的启动命令所能读取的配置组是不同的，例如使用mysqld启动服务端就会读取 [ mysqld]、

两个组，使用mysql 启动客户端就会读取

、[client ] 两个配置组

配置优先级

配置文件可以有多个，会一个一个加载，就像springboot一样，如果有多个配置文件都配置了同一个选项，则以最后一个为准

同一个配置文件在不同配置组中出现了相同的配置，则也以最后一个组为准

如果启动命令中有相应的配置参数，则以命令中的为准

环境变量

可以使用以下命令查看：

# 查看默认存储引擎
show variables like '%default_storage_engine';

环境变量可以通过上述的配置方式修改，也能在运行过程中通过 set [global | session] 环境变量名 = value 的方式进行修改，大部分参数的修改都能动态生效

状态变量

使用以下命令查看：

# 查看线程相关状态
show status like 'thread%';

InnoDB

以页为单位进行数据的存储和获取，默认一页为16kb。

InnoDB行格式

一行记录在页中的存储格式，有：Compact、Redundant、Dynamic、Compressed

指定行格式：

create table table_name (col1, col2...) ROW_FORMAT=Compact
alter table table_name ROW_FORMAT=Compact

Compact

顾名思义，为了节省空间，列数据被紧凑地放到一起，但显而易见有两个问题：

1. 对于变长的数据类型（例如 varchar），如何确定边界？

2. 如果列为空应该如何存储？

Redundant的做法是：记录每列数据的结尾在数据内容部分的位置（即记录一个数据偏移量），简单粗暴，相当于将Compact的变长字段长度列表和null值列表合并，但相比于 Compact 更占空间

Compact的做法是：分别使用 变长字段列表 和 NULL值列表 来记录变长字段长度和哪些列为空值：

• 变长字段长度列表：记录所有的变长字段的长度，以多少个字节记录取决于数据的最大长度和数据编码方式。例如如果有三列变长数据，则其内容可能为 031504，这里就是以一个字节存放一个长度，长度分别是3、15、4。

• NULL值列表：以01的方式记录每个可能为null的列是否为null，例如，有三列数据可能为空，则内容可能为 00000110（一位表示一列，不足一个字节前面补0），其中，1表示该列为null，0表示不为null，则这里的数据表明后两列为null（注：变长字段长度列表和null值列表都是倒序表示的）

记录头信息用于保存该记录的元信息，例如该列在堆中的位置、是否被删除、下条记录的相对位置等

数据内容部分也有值得注意的地方：每个表都有两个默认的列 事务ID（DB_TRX_ID）和 回滚指针（DB_ROLL_PTR），如果表没有设置主键也没有指定唯一索引，则还会生成一个默认的主键（DB_ROW_ID）。这三列无法使用select查询。

页溢出

Compact或Redundant格式下，一行记录最大存储65535个字节（不包含隐藏列和记录头信息），所以如果记录只有一列运行为null的varchar数据，则其长度最大为65532，因为其长度记录可能需要占2字节，null值列表需要占1字节

前文说，数据以页为单位进行存储，一页为16kb=16384字节，小于65535，所以对于大的数据就需要分页存储。这种情况下，数据内容部分就只记录部分数据内容，留下部分空间用于举例溢出页的地址（连起来就像一个链表）

MySQL默认行格式为Dynamic，它和Compact的区别在于，当发生行页溢出时，会将原本页内所有的数据都剪切到溢出页中，而仅仅记录溢出页的地址

可以通过以下命令查看页的行格式：

show table status where Name="table_name" \G

页

MySQL以页为单位存储数据，页又分为存放数据的页、存放索引的页、存放undolog的页等等。想想和一本书挺像的，书也是以页为单位，不同的页存放不同的内容，例如写有目录的页一般不会再写正文，有些页专门写附录等等。

每页都是一个数据结构：有头信息、数据、填充等：

一个页在创建后，绝大多数都是 Free Space，当插入数据记录时，会从FreeSpace中抽取部分存储数据，也就成为了UserRecords区域。

每页都可以存储多行记录，这些记录会依照主键大小形成一个链表，而链表的指针就存储在 记录头信息 中。值得注意的是，每页都会自动生成头尾两个虚拟记录（用于记录该页最小/大主键），存储在 Infirmum+supremum 空间中。这两个记录用于数据检索，例如如果遍历数据时，由于不知道真正的最小数据保存位置，就可以从最小虚拟记录开始遍历。另外，这俩虚拟记录也被用于创建跳跃表索引

如何在页中按照主键寻找一行记录，显然由于记录以链表的形式保存，故可以遍历整个链表，但这样效率很低。一个解决方法是使用跳跃表的方式。这也是Page Directory部分存储的内容，它会存储跳跃表的索引位置。这样一来，当根据主键查找时，就能先找到大致的位置，然后做很少的遍历即可。

FileHeader保存页的编号、上下页的位置、页类型等信息，即保存页间的信息

PageHeader用于记录该页的记录数、首条记录位置、跳跃表索引数等信息，即保存页内的信息

MySQL对数据页的修改都是发生在内存中的，假如某页在修改后刷回磁盘过程中，刷写一半断电了，那我该如何值得数据是否完整写回了呢？mysql是这样处理的：分别在FileHeader和FileTailer中保存了数据的校验和信息，如果页内数据发生修改，则需要修改这两处的校验和数据，而数据刷回磁盘时，FileHeader总是先刷回去，所以，检测页是否完整，只需要校验FileHeader中的校验和数据与FileTailer中的数据是否一致即可

一个页就算一个容器，它里面可以保存不同类型的数据，数据类型不同，页的类型也就不同，页类型保存在FileHeader中。需要注意的是，一个页的类型是可以变化的，例如某页本来是存储数据用的，后来数据用完了，它就空了，后续它也可以用来存放索引、undolog等变成其他类型的页，页的类型主要有：

• 未使用（FIL_PAGE_TYPE_ALLOCATED）

• undolog页（FIL_PAGE_UNDO_LOG）

• 索引页（FIL_PAGE_INDEX）

• 段信息页（FIL_PAGE_INODE）

• blob页（FIL_PAGE_TYPE_BLOB）

• 表空间头部页（FIL_PAGE_TYPE_FSP_HDR）

• 。。。

FileHeader中还存储了当前页号，上下页的页号（所以页之间就会形成双向链表），校验和信息，所属表空间

B+树索引

叶子节点的页保存数据，非叶子节点的页保存叶子节点页数据的最小主键

索引记录其实和数据记录本质上是一个格式，包括页的那些内容，例如索引页也有page directory，只是记录类型不同，索引记录有两个字段：页最小值、页位置，

多个索引页使用指针相连接就会形成B+树索引结构

如果一条记录所有的列数据都存储在索引的叶子节点上，则称之为聚簇索引

需要注意的点：

1. 索引的根节点位置不变：数据量少时根节点可以保存数据，数据量大后会升级为索引节点并将数据复制到其他节点。好处是每次查询都可以从一个固定的节点开始，而不用维护节点移动位置关系。

2. 对于唯一索引来说，索引记录保存两列数据：孩子节点的最小id和孩子节点的位置。但是，如果是非唯一索引还这样做就会出现问题，也就是同一个索引值指向了两个不同的节点，如果再插入数据时就会不知道该放到哪个节点中去。所以InnoDB会在非唯一索引中增加一列记录最小主键，如此一来，如果索引值相同就会去比较主键的大小。联合索引同理。

3. 一个页中最少存放两条记录，如果存放一条记录，不管是索引还是数据，都会导致索引层级变高

4. InnoDB和MyISAM会自动为 unique 列创建索引

MyISAM索引

InnoDB使用聚簇索引，所以主键索引不用回表，而MyISAM则将数据和索引分为两个文件存储。

数据按照插入顺序存放到数据文件，类似于顺写日志的方式。而索引则在叶子节点保存 索引值 + 数据行号 表示。所以对于非主键索引来说，MyISAM甚至比InnoDB更快，因为它直接定位到数据位置，而InnoDB则需要回表再次查询一遍主键。尤其是使用索引进行顺序查找但是没有用到覆盖索引的情况，此时二级索引使用顺序查找（因为是范围查找）非常高效，但回表则需要每条数据进行单独的随机访问，很慢。

MySQL的数据目录

要区分mysql的安装目录和数据目录，查看数据目录的方法：

show variables like 'datadir';

每个数据库都会对应数据目录下的一个子文件夹，每个数据库文件夹下都是各种 .ibd 表文件（表空间），用于存储表数据和索引

系统数据库

• mysql：核心，记录了mysql用户的账户信息、账户权限、存储过程、事件定义、日志信息等

• information_schema：数据库的元信息，例如MySQL有哪些表、哪些列、哪些视图、哪些触发器、索引等

• performance_schema：性能监控信息，例如最近执行的语句，执行过程每个阶段耗时等

• sys：将information_schema和performance_schema两者结合形成更易读的视图便于监控

InnoDB表空间

在数据库文件夹下，每张表都对应一个 .ibd 文件，称之为一个表空间，因为它实际上并不是一个物理文件，而是逻辑文件，它可能由多个文件构成。一个表空间可以理解为一个页池，数据存放在表空间实际是存放在表空间里的页上面。

段组区页关系

这四者的关系简单说是上下级关系，一个表空间分为多个段、一个段分为多个区、一个区分为多个页（这里还没组）

但是，如果细看的话，它们的关系可能更复杂一些。事实上，它们的关系应该从下向上去理解：

64个连续的页（1M）被划分为一个区

一般来说，给数据分配存储空间，相同类型的数据最好分配到同一个区，例如对于索引树来说，它的索引节点和数据节点就会放到不同的区中，而如果数据量较大，往往会用到多个区，则对存放相同类型的区的集合就称之为段，例如对于索引树来讲，就分为索引段和数据段，对于一些存放其他类型的区的集合也有对应的段，例如回滚段等

在此基础上，MySQL有进一步优化，如果数据量不大，例如索引和数据只有几个，那么再以区为单位申请内存就比较浪费空间，故MySQL会在小数据量的情况下，段下面直接是一些页，数据量起来后才会以区为单位申请内存。这些被段直接管理的页来自于一些特殊的区，称之为碎片区。所以，一个段中不仅管理的有区，还有一些页

为什么要有区的概念？

需要强调的是，一个区是物理上连续的64个页的集合。前面说过，页之间通过双向链表进行连接形成一个大文件，问题在于如果两个页在物理层面相隔太远，那么随机io的效率也是较低的，所以得让它们尽可能的靠近，即同类型数据申请页时在当前区中申请即可。

所以MySQL申请内存时是以区（1M）为单位进行申请的

为什么要有段的概念？

在进行数据遍历时，其本质是遍历索引的叶子节点，这些叶子节点组成一个一个的区，想象一下如果这些区在内存空间杂乱无章地摆放，那么对其遍历肯定还是有一些影响的，所以可以将它们放到一起。其基本思想和区的想法是一样的。

需要注意的是，段是逻辑概念，并不是连续内存区域，但也能一定程度提升查询效率，因为段本身会保存内部区的一些信息

什么是组？

表空间的页被物理划分为一个一个的区，为了细化管理，会将它们每256个划分为一组，每组的前两个区用来存储该组中的区信息

区也会组成链表

区是相同类型页的集合，也是内存分配的基本单位，多个区之间会通过双向链表形成一个段，但其实并不是这么简单

一个索引在创建时会先创建两个段：索引段和数据段，每个段在存放数据时会先以页为单位申请空间，当申请的页超过32个时（这些页不一定在一个碎片区），后面就之间以区为单位申请空间。需要注意的是，页都是存放在区中的，而不同的段肯定是向不同的区申请空间。

由此可以看出，一个区存在四种状态：空闲、部分使用、全部使用、段申请。区的状态会保存在区的头部

注意，前三种状态的区并不属于某个段，它们直接属于表空间，即前文说的碎片区

那么问题来了，假如段需要申请一个页或者一个区，那它需要遍历整个表空间所有的区来找可用的区吗？显然是不可能的，效率太低。

所以MySQL会在每一个段中维护三个区的双向链表（例如两个索引四个段就有12个链表）：空闲区链表、已部分使用区链表、已全部使用区链表

如此一来，如果需要申请页，则直接去已部分使用区链表获取即可，如果需要申请区，则去空闲区链表中获取即可。一个区根据状态不同会在三个链表中移动

这部分内容太杂太细，没记了

https://book-how-mysql-runs.netlify.app/#/mysql/09-%E5%AD%98%E6%94%BE%E9%A1%B5%E7%9A%84%E5%A4%A7%E6%B1%A0%E5%AD%90-InnoDB%E7%9A%84%E8%A1%A8%E7%A9%BA%E9%97%B4

索引查询

覆盖索引

一个值得关注的点是，覆盖索引的使用是与查询条件相关还是与查询字段相关？

例如我对user表中的name创建了索引，下面的查询会不会用到索引呢？

select * from user where name="zh";
select name from user;

答案是两者都会用到索引，前者是使用索引查询，后者则是遍历索引。

另一种情况是，如果我对两个字段创建了联合索引，然后我查询这两个字段，显然是符合上述第二种情况的，会使用索引遍历，但此时我添加一个where条件会不会继续使用索引呢？得分两种情况：

1. 如果where条件不是联合索引的字段则不会走索引

2. 如果是联合索引的任意字段，则还是会走索引

NULL

不管是普通索引还是唯一索引，都允许多个null值存在。

正常情况下，对于唯一索引而言会使用等值查询，也就是查询类型为const，但是，如果查询条件是 is null，则会回退为ref，因为可能有多个null的存在。普通索引都是使用ref类型，如果查询条件是 is not null，则回退为 range

ref VS eq_ref

在单表中使用非唯一索引查询会使用ref类型，使用主键或唯一索引查询则是const类型

但是在使用join进行多表查询时，如果被驱动表的查询条件也是主键或唯一索引（例如 t1 join t2 on t1.c=t2.d 中，t2.d 是唯一索引或主键），则被驱动表的查询类型为 eq_ref，其等同于单表查询中的const类型

JOIN

• 为什么一定要在被驱动表的连接条件上创建索引？

驱动表中查出来多少条数据，被驱动表就要被访问多少次，所以需要给被驱动表创建查询索引

• 为什么需要JOIN？

事实上，如果仅仅需要等值连接去查询两个表，不用join也能完成：

select * from t1, t2 where t1.c1=t2.c1
# 这种情况等价于inner/cross 连接

但是如此一来，只会查出t1和t2中符合等值查询条件的列，有些情况下，我想查出某个表所有的列，不管子表中是否存在对应的等值查询条件，此时就需要用到join了。

• join语句中，使用on和where作为过滤条件是等价的。但一般情况下，会在连接条件上使用on

join buffer

连接查询的本质是将驱动表查出来的数据一条一条再去查询被驱动表，这样就有一个问题：如果被驱动表没有添加索引的话，驱动表查询的每个数据记录都需要将被驱动表加载到内存进行匹配，如果内存不足以容纳整个被驱动表，则还需要反复多次加载同样的数据，造成极大的性能损耗

MySQL的做法是：使用被驱动表的数据去匹配驱动表查出来的数据，和上述过程正好相反，好处在于，只用加载一次被驱动表数据，大大减小了磁盘io。

具体做法为：连接查询前，先申请一块内存成为join buffer，然后将部分驱动表查询出来的数据放到里面。每次加载被驱动表数据时，就将该数据与join buffer中的数据进行一一比较。

由此产生了MySQL优化上的一个问题：如果joinbuffer只能存放较少的驱动表数据，则仍然需要多次加载被驱动表的数据。joinbuffer越大，这个io就越少。如果joinbuffer足够存放所有的驱动表数据，则被驱动表只需要加载一次就够了。所以可以尽量让joinbuffer大一些。当然，最好的方法还是在被驱动表上加索引

SQL的自动优化

• 什么是执行计划？

sql语句只是告诉mysql我要查询的东西，而具体怎么查，是由MySQL查询优化器决定的。MySQL查询优化器在接到查询任务后，会找到很多种查询的方法，它会选择自己认为最优的方法调用存储引擎来执行，这个最优方法就是执行计划

寻找执行计划大致过程为：

1. 找到所有的查询条件

2. 估算全表扫描代价

3. 估算使用不同索引的代价

4. 选择成本最低的方案

所谓的代价分为 io成本 和 cpu成本

• 如何计算代价？

MySQL内部有一个数学公式计算代价，例如它规定读取一个页的代价为1，读取一行记录的代价为0.2，它们称之为成本常量，存储在mysql.server_cost这个数据表中，由此我们只需要计算全表扫描或使用索引分别会加载多少个页和记录即可

幸运的是，MySQL会在系统表中记录每张表的数据量（不准确）和数据长度，由此便可计算全表扫描大概需要扫描多少页和记录，而对于使用索引的代价，则使用另一种较为复杂方法，例如它规定索引的范围查找代价等同于读取页，还有其他一些计算规则，总之也是计算一个差不多的代价值。然后找出代价较低的方案。

另外，MySQL也会为每个索引记录一些统计信息，尤其是一个基数信息较为重要。它表示非唯一索引可能重复的数量，越小越好。这个基数信息也会被用在代价计算上

查看某个执行计划的代价可以在explain后面跟上 format=json 参数

explain format=json select * from user \G

• 子查询中in的优化

子查询的意思是 查询语句中包含另一个查询语句，例如：select * from user where name in (select name from class1)

这个子查询称为不相关子查询，意思是 子查询语句中没有使用到父查询语句中的表信息，与之相反的是相关子查询，例如 select * from user where name in (select name from class1 where user.c=class1.c)

不相关子查询执行方法很简单，先查出子查询语句，然后执行父查询，但这里就涉及一个优化问题：如果子查询出来的数据量很大，父查询再使用in关键字搜索就很慢。

MySQL的优化方法是：将子查询出来的数据保存在一张表中（这一过程称之为物化），表的存储引擎为memory，并为查出来的数据创建唯一索引（因为是给in关键字用的，所以可以去重）或hash索引。这样一来，就能将in类型的布尔查询优化为内连接查询。

进一步的，这个过程还能被优化为 semi-join，我没记，我也没看

另外还有很多其他布尔查找的优化，也是类似将一种查询转换为另一种查询。例如将 <any(select xxx from xx) 优化为 <(select max(xxx) from xx) 等

Explain

没记

Buffer Pool

MySQL数据的读取是以页为单位的，就算只是读取一条记录，MySQL也会将该记录所在的页全都加载进内存。InnoDB在启动时会向操作系统申请一片连续的内存（由于后续MySQL的升级允许动态修改BP大小，所以后面BP也不再是连续空间了，而是由多个chunk组成），称之为Buffer Pool，就是用来存放加载的数据页。

buffer pool的容量可以在启动时或配置文件中指定，通过 show variables like 'innodb_buffer_pool_size' 可以查看容量大小。

将页加载进 buffer pool 后，还会为它单独创建一个 控制块，用于存储该页的编号、页号、在bp中的地址等信息，两者是一一对应的，且控制块位于BP的头部，而加载的页位于BP的尾部

需要注意的是，上文中提到的 innodb_buffer_pool_size 单纯指的是存放缓存页的容量，所以 BP 实际的容量要比innodb_buffer_pool_size的容量更大（大于多5%用于存放控制块）

另外，由于对BP的访问可能涉及到加锁操作（例如对下文中一些链表的访问），为了提升多线程下的效率，所以BP可以有多个。

关于BP的信息可以通过命令 show engine innodb status \G 进行查看

又是链表

可以认为BP空间申请后，其格局就算固定的了，控制块和缓存页都已经有了，当要加载数据页时，就去找一个空闲的缓存页加载，然后修改对应控制块的信息。

• 链表一

那么问题来了，我怎么知道哪些缓存页是空闲的？

InnoDB的做法是将所有空闲缓存页的控制块连成一个链表，当需要加载数据页时，就从这个链表中取，淘汰缓存页时就将该控制块加到链表中

• 链表二

数据的修改发生在BP中，然后经过一定的策略刷回磁盘。那么问题来了，InnoDB如何知道哪些页是脏页呢？

InnoDB的做法是，维护一个脏页控制块链表，称之为 flush链表

• 链表三

当BP满了只会又需要加载新的数据页，那就要涉及淘汰机制了。InnoDB的做法是将所有正在使用的缓存页控制块构建一个 LRU链表，最近使用的缓存页始终放到链表头部，每次淘汰就直接淘汰尾部的缓存页。大致思想是这样的，但事情还不是这么简单：

这种简单的LRU链表并不足以对付全表扫描或者预读（MySQL会推测以后可能会读取的页），它们会使得 LRU链表 失效。

InnoDB的解决方法是：将整个 LRU链表分为两段（就简单理解为两个链表），young区和old区，old区就和上述简单LRU链表效果一样，首次读取的页控制块就放在old区。如果old区中某缓存页在1s后又被访问了，则晋升到young区。这里有两个疑问：

1. 为什么要等1s？因为一个页中不止一条记录，当在做全表扫描时，一个页可能在短时间内多次访问，但一般1s肯定是能访问完的。这样能避免其虚假晋升到young区

2. 为什么是1s？这里是假设像全表扫描这种操作并不是很频繁，例如1s内最多发生一次。试想一下如果1s内发生多次全表扫描，young区同样会沦陷。这个值也是可以设置的

事实上，LRU链表还有很多其他的优化方法，但其目的都是尽可能让热点数据留在缓存，提升命中率。

事务

需要保证ACID的数据库操作称之为事务

1. 原子性：

一个事务可能有多条sql语句组成，并且，就算只有一条sql，那它的执行也由多步组成（修改buffer poll，刷回磁盘等），这其中任意一个节点都可能出问题。所谓原子性的意思就是，对于一个事务来说，要么都成功，要么都失败

2. 隔离性：

事务与事务之间的交互关系，即四种隔离级别。类似于在多线程条件下，对共享资源的线程安全问题。

例如脏读就是在一个事务中读取到另一个事务还未提交的数据，就类似于多线程下的 i++ 操作

3. 一致性：

在使用数据库时会制定一系列的约束或规定，例如主键必须唯一，规定用户的年龄必须大于0，一个账户扣除的金额必然在另一个账户上有增加等等。

数据库在使用运行过程中，一直遵循上述约束或者规定，即为一致性（如果将数字世界当作现实世界的一个映射，由于现实世界有很多规定或公理，那么数字世界就需要和现实世界保持一致，也遵循这些规定或公理）

原子性和隔离性就是维护一致性的一些手段。但也只是部分手段

4. 持久性：

状态能够长久地保留下来

MySQL中的事务

注：事务中的语句即使还没提交，也是在真正执行并修改数据的。但是由于存在MVCC和undolog等机制，使得它看起来更像是 先将事务的sql暂存到某个地方，commit只后再一起执行。

• 如何开启事务？

使用 begin [work] 或者 start transaction [read only | read write | with consistent snapshop]

两者的不同点在于，后者支持添加修饰符，用于设置事务的读写模式

• 如何提交事务？

commit [work]

• 如何终止（回滚）事务？

rollback [work]

注：此为手动回滚事务，如果事务在执行过程中出错，还会自动回滚

• 如果一个事务中用到了不支持事务的表（例如表的存储引擎为MyISAM），回滚时会发生什么？

支持事务的表会回滚，不支持的则保持原状。

• 自动提交

show variables like 'autocommit';
set autocommit=OFF/ON;

• 隐式提交

当使用 begin 开启一个事务后，即使不显式执行 commit，某些情况下该事务还是会被自动提交：

1. 事务中执行了DDL语句，例如创建或修改表结构、视图、存储过程等

2. 修改数据库系统表数据，例如给数据库添加新用户、修改密码等

3. 事务过程中又使用begin开启一个新的事务

4. 使用 load 指令加载数据、手动执行主从复制相关的语句等

总之，事务中最好只是对业务数据进行操作，其他的一些操作都可能引起事务的自动提交

• 保存点

可以在事务代码中加入一些保存点，执行时就能通过rollback指令短路这部分代码

begin;
# 只有这部分代码生效
...
savepoint xxx;
# 发现下面这部分代码可能有些问题，需要丢弃
...
rollback to xxx;

redo log

为了防止操作数据库过程中发生系统崩溃，所以每次对数据库的操作记录到redolog中（不仅是sql操作，凡是操作buffer poll中的页都会记录redolog），这样即使是系统崩溃，也能通过回放 redolog 来完成操作。如此就会产生几个问题：

• 事务还没提交会产生 redo log 吗？

会，事务中的每一条语句其实都是在真正修改数据记录，但由于存在undolog和回滚机制，导致看起来并非如此。

• 我之间将数据刷回磁盘不是更好吗？为什么要先将redolog刷回？

前文提到，MySQL对数据记录的操作都是以页为单位的，一页中可能有很多数据，所以就算只修改了一个字节也必须完整地读取和持久化整个页，这个过程比较容易出问题。相较而言，如果只记录一下修改的内容，这个过程就显得容易多了，也更不容易出问题，由此，使用redolog的两个好处：

1. 因为日志需要被持久化的内容更短，所以记录redolog过程相较于直接修改数据更不容易出问题

2. 日志一般都是顺序写入的，而数据页的修改一般是随机写入，当然日志的记录更快

日志格式

redolog的格式并不唯一，针对不同的操作会使用不同的格式，但大多格式都遵循以下基本格式：

一种简单的格式

MySQL会为没有设置主键和唯一索引的表添加一个隐藏的主键 row_id，但是每次数据库启动时它怎么知道上次 row_id 用到哪了呢？MySQL会在表空间的第7个页中保存一个全局 row_id，MAX ROW ID，每次用一次就会 +1（具体过程比这个要复杂，但基本思想是这样的）。那么每次对该 row_id 的修改就会被记录到 redolog 中。这种日志相对比较简单，只需要记录一下 在哪个页的哪个偏移量位置修改了什么内容 即可。所以它的格式可以是：

这种格式称为 ”MLOG_WRITE_STRING“，由于某些数据具有固定的字节数，为了节省空间，对于那些固定长度数据的修改，mysql会提供专门的格式类型，并省去len字段。例如 MLOG_1BYTE 表示1字节数据、MLOG_4BYTE 表示4字节数据等

一种复杂的格式

对数据的修改可能涉及到对很多页的修改，例如多个索引页、数据页，也可能涉及到页的增加和减少（页合并与分裂），即使是只针对单个页上的数据做修改，也可能修改页内多个地方的数据，例如页内的数据都是按主键大小连成链表的，删除一条数据就需要修改前后链表的指针，还需要对 Page Header 中页内数据的统计信息做修改，对 Page Direction 中页内索引做修改等等。

最简单的做法是将上述要修改的地方都使用简单格式的redolog记录下来，但这样并不好，很有可能记录的日志数据量比原本修改的数据量大得多，且回放时很容易出现问题。

所以InnoDB采用另一种方式：页内不是零零散散要修改很多地方吗，我就将开始修改的地方和结束修改的地方看作一个整体，当作一整个要修改的区域即可。当然，这也仅仅是对行格式为Compact、Dynamic等设计的一种格式，还有很多其他的格式。上述格式为 MLOG_COMP_REC_INSERT

但这种格式也不会记录例如page header上统计信息的修改，因为在做回放时会自动更新这部分数据。

那么，如果向上面所说，出现了对多个页进行的修改该怎么做呢？

InnoDB将一个任务产生的多个redolog合成一个组，要么组里面的日志全部能解析，要么丢弃。体现了原子性。向B+树索引添加数据项就是一个redolog组。

InnoDB如何区分独立的redolog和redolog组？

每个redolog都有一个type字段，而type字段的第一位表示该redolog是否是属于一个组，若为1，则是独立的redolog，直接解析执行即可，若为0，则表示属于一个组。不管是独立的还是一个组，都可以称之为一个 Mini-Transaction，简称 mtr

InnoDB如何知道一个组是否能完整呢？

会在redolog组最后加上一个类型为 MLOG_MULTI_REC_END 的标记redolog，由于它在最后，只有正确解析到它，才能说明该redolog组是完整的

redo log 的写入

redolog并不是写在页中，而是写在另一种相似的内存块中，它和页看起来差不多，都是一个一个固定大小为512字节的内存块，也有 header 和 tailer。header中存放该block使用了多少字节、mtr的第一个redolog偏移量、checkpoint序号等，tailer记录着校验和。

redolog 缓冲区

和 buffer poll 一样，redolog也并不是直接写到磁盘的，而是先写入一个redolog buffer，默认16M。

它是由连续的redo log block组成（连续内存空间），写日志时就一个一个往后写，就像一本记事本一样

注：一个mtr可能由多个redolog组成，它们要向buffer写的时候，必须要一起写。比如在执行某个sql时产生了一个redolog组，那么在产生redolog过程中，它会先临时记录在某个地方，当一个组完整后才会写入buffer。

redolog buffer 刷盘时机

1. buffer空间不足时。实际上当占用一半时就会刷盘

2. 事务提交时。buffer poll可以不刷盘，但redolog buffer必须要，更稳妥，可以设置关闭

3. 后台线程定时刷盘。

4. 正常关闭MySQL服务器

5. 做checkpoint时

buffer内容会写到数据目录下的 ib_logfile[ k ] 文件中，可以有多个文件，k表示文件序号，它们形成一个日志文件组。这些文件也是一个写满写下一个，如果都满了就会从头重新开始形成一个循环。使用checkpoint来避免循环时的覆盖问题。

日志文件组就是用来存放redolog buffer的内容的，所以它们基本是一样的。不同点在于，文件组中每个文件有一个header和两个checkpoint信息，剩下的部分就是用于存放redolog block的。

• 所谓刷盘，是直接将buffer中的block都写到磁盘后再清空buffer吗？

不是。简单地说，buffer会维护两个指针，一个指针用来表示当前buffer已经用到哪了，一个指针用来表示当前buffer已经刷到哪了

当然，这俩只是针对整个buffer来说，系统另外还会维护两个全局变量用于保存总共的redolog数据量（LSN，log sequence number）和已经刷回磁盘的数据量（flushed_to_disk_lsn）

redolog本质上是记录页的修改，所以如果产生了redolog，必然是产生了脏页。前文说到，buffer poll会维护一条flush链表，用户记录所有的脏页。所以在mtr持久化到redolog同时，还要将脏页添加到flush链表头部

checkpoint

由于redolog文件组容量有限，所以需要循环覆盖之前的redolog，就需要淘汰掉那些过时的redolog block。如何判断哪些是过时的block呢？也很简单，redolog和脏页是相互绑定的，如果脏页已经刷回磁盘，那redolog也就没用了。

而checkpoint的作用就是标记当前哪些mtr对应的脏页刷回了磁盘。又由于redolog是连续记录的，所以checkpoint就是一个指针，它前面的就是过时的redolog。所以，整个redolog文件组可以表示为：

在做崩溃恢复时，只需要重放checkpoint后面的redolog即可（有一些优化方法，但基本思想就是这样）

undolog

用于回滚，例如插入一条数据时需要记录该数据的id以便回滚时删除，修改一条数据时，需要记录被修改前的内容等。不同的数据操作对应的undo日志类型是不同的，结构也就有所区别，但是从大的方向上看，undo日志分为两大类，insert和update，一个undo页只能存放其中的一个大类。

一般来说，对记录的一次改动就对应着一条（有些情况下是两条）undo日志。undo日志存放在类型为 FIL_PAGE_UNDO_LOG 的页中（和数据页相同，类型不同），页来源于表空间或者undo日志专用的表空间

而前文说，InnoDB会为每条记录生成两个隐藏字段 事务id 和 回滚指针。事务id就是生成该条记录的事务id，而回滚指针就是指向 undolog 页中，数据修改时记录的undo日志的位置。

数据的删除过程

前文说过，数据的删除并不是直接做物理删除，而是在记录的header中有个字段维护删除状态，当该字段为1时表示记录被删除。

而在页的内部维护着两个链表：正常记录的链表和已删除记录链表

在一个事务中删除记录时，会将该记录的删除标记位置为1，为了实现MVCC，只有当事务提交时才会有专门的线程将该记录移除正常链表再加入删除链表。

在修改删除标记位前，会记录一条undo日志，里面会记录该记录之前的事务id和回滚指针，如此一来就形成了一条数据修改的版本链。

数据的更新过程

分两种情况：

如果不更新主键，则细分为：

1. 如果更新前后数据大小完全一样，则执行就地更新，即直接在原来位置上做修改

2. 如果更大或更小，则先彻底删除（加入到删除链表中）原来的数据，再添加新的数据

如果更新主键，则只会修改原来记录的删除标记（为了MVCC），并重新添加一条记录

当然，对于更新来说，它可能是由删除+添加完成的，那么它就会记录两条undo日志。也就是说，每对一条记录做的修改，都会记录一个undo日志

undo页面链表

一个事务过程中可能产生很多undo日志，可能一个页面都放不下。此时就会像数据页一样产生undo日志链表

但是，前文说，undo日志分为两大类，insert和update，同一个页面只能放一个大类的undo日志，并且，InnoDB要求普通表和临时表的undo日志也得分开。所以其实undo日志页面最多有四个链表，并且每个事务都有几个独立的链表。

段

• undo日志段

前文也提到了段的概念，例如一个索引占用的空间资源可以分为索引段和数据段，而每个段都管理着一些页和区的资源，所以段可以理解为一些空间资源的集合，只不过它们是逻辑集合，并不是真正的连续内存块。

undo日志段也是一样，一个段就表示一系列undo页的集合，当需要写undo日志时，就先去段中获取一个空的页，将其加入到undo日志链表中，然后向其中写undo日志。

每个事务都会单独分配一个段

每个undo日志链表的第一个页除了正常作为undo日志页外，还需要记录该链表属于哪个段的

• 回滚段

每个事务都可能有几个undo链表，为了统一维护管理这些链表，InnoDB设计了回滚段的概念。

前文说段是页的集合，但回滚段比较特殊，它目前只包含一个页，称为 Rollback Segment Header。

一个回滚段或者回滚段的页中包含所有undo链表的头节点指针，所以大致可以表示为（简化画法，其中应该还有页的头尾等信息）：

一个回滚段中可以包含1024个undo链表，如果满了还有新的事务创建undo日志的话就会报错。此时就可以等待其他事务提交后释放undo链表后重新执行事务。当然，回滚段可以有多个，默认是128个。

为了节省空间，undo日志页面是可以重用的，所以每个回滚段中还会维护一个cache链表，用于保存那些已经提交的事务且可以重用的undo链表，一般来说，insert类型的undo日志链表可以重用。那些不可重用的链表则会直接被释放掉，并且让出回滚段位置，而对于update类型的undo日志链表则不行，这种undo日志链表在事务提交后会不会被销毁，而是放到 History链表中，用于MVCC。

前文说回滚段可以有128个，但这些段也不是随意分配的，例如某些段只能用于存放临时表的undo日志，为什么要这样做？

因为undo日志是写在页上的，而写在页上的东西就有有redo日志来记录，但是对于临时表来说，它一般都是不需要故障回放的，所以它可以不用undo日志，但如果仍然需要，还是最好将其分开存放。

MVCC

用于实现事务的隔离级别，其实就是指在读已提交和可重复读两种隔离级别下事务执行select语句时访问版本链的过程，这个过程是由readview作为判断依据的

也能用于提升并发访问性能。例如使用锁其实就能避免各种隔离级别下出现的问题，但是使用MVCC就能在不使用锁的情况下解决一些并发问题，例如可以解决幻读问题

隔离级别

• 读未提交。脏读、不可重复读、幻读

• 读已提交。不可重复读

• 可重复读。幻读

• 可串行化。

MySQL默认是可重复读级别，但是它通过临键锁解决了幻读的问题。

修改隔离级别的方法：

set transaction isolation level {repeatable read | read committed | read uncommitted | serializable}

版本链

对一条记录的修改会记录一个undo日志，而如果对记录做了多次修改（一个或多个事务），则undo日志就会形成一个版本链。

需要注意的是，添加数据的undo日志一般在事务提交后要么被重用，要么被删除，而修改记录的undo日志则会被保留下来用于MVCC

当然，随着数据的修改，版本链也会越来越长，但是MySQL会有专门的线程对版本链做清除操作。例如，如果undo日志不被其他事务依赖，那么这个undo日志就可以被清除掉。

ReadView

读已提交和可重复读区别在于什么时候生成ReadView。

读已提交在每次读记录时都生成readview，可重复读是在第一次读的时候就生成，后续直接复用

readview有四个关键属性：

• m_ids：创建readview时，还没提交的事务id

• creator_trx_id：创建readview的事务id

• min_trx_id：还没提交的最小事务id

• max_trx_id：还没提交的最大事务id+1

InnoDB根据readview就能判断某条记录是否可读（就是比较各个事务id，判断记录的事务是否已经提交），如果不能访问该事务对应的记录，则顺着版本链向下找。

锁

没记多少，另外的文章记得比较详细 MySQL笔记

所谓加行锁，其实就是对某行记录关联一个锁的数据结构，如果加了很多的行锁，则在一定条件下会使用一个数据结构一起记录这些锁，用于节省空间

对意向锁的理解：如果需要对整个表加X锁，前提是表中所有的记录都没有加行锁，如果一行一行去检查太慢了。所以在加行锁的同时会给整个表加上意向锁，再给表加锁时就只需要检查表有没有意向锁就行了

意向锁也分为意向读（IS）和意向写锁（IX），如果要对记录加读锁，则给表加意向读锁，反之则加意向写锁

需要注意的是，意向读和意向写是兼容的，道理很简单，意向锁只是对行锁的体现，行记录有些加写锁有些加读锁是不冲突的。

表锁

InnoDB支持行锁，所以表锁应尽量避免。使用方式为：

给表加 S/X 锁，应尽量避免，尽量使用行锁
lock tables xxx read/write;

给记录加锁是也会自动给表加意向锁

还有一个 auto-inc 锁，用于给自增字段获取值，当要获取一个自增字段时加锁，获取完后就释放，并不需要等待事务提交或回滚

行锁

除了行锁、间隙锁、next-key锁，还有插入意向锁、隐式锁等

插入意向锁：当某个间隙被加了间隙锁后，其他事务想向其中插入数据就会处于阻塞状态，但仍然会给该间隙加上插入意向锁，但实际上，这个锁比较鸡肋，它仅仅作为一个要插入数据的标记，它并不会阻止其他事务对该记录和间隙加任何锁，也就是即使该间隙加了插入意向锁，其他事务也能成功加上间隙锁等。

隐式锁：当一个事务添加了某条记录，另一个事务立即去读取记录会怎么样？答案是会阻塞。虽然第一个事务创建记录时没有对该记录加锁，但第二个事务通过readview和事务id发现该记录没有被提交，就会帮助第一个事务创建一个锁，然后自己阻塞等待（好像是这么个意思，待验证）

注：事务内修改记录时，如果修改条件包含了索引（不管有没有非索引字段），才会加行锁，否则加的是表锁。

死锁

两个事务对某条记录持有锁后，又妄想得到对方的写锁

在设置 innodb_deadlock_detect=ON 的情况下，MySQL会自动检测死锁。否则死锁发生后就只能等待超时了。

解决方法：

保证加锁顺序正确、减少资源对锁的占用时间

使用乐观锁和mvcc机制，例如 CAS，mvcc对select操作不会加S锁，因为它直接读取版本链的内容

使用更大粒度的悲观锁