mysql运行的整体架构简介

Mysql是由两部分构成，一部分是服务器程序，一部分是客户端程序。
服务器程序又包括两部分：
第一部分server层包括连接器、查询缓存、分析器、优化器、执行器等。涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等
第二部分是存储引擎层负责数据的存储和提取。存储引擎有多种选择，主要有InnoDB、MyISAM、Memory等。

要操作Mysql数据库，首先客户端要连接上mysql服务器程序。
连接器：** 负责跟客户端建立连接、获取权限、维持和管理连接。
连接命令：

mysql -h$ip -P$port -u$user -p

MySQL采用的TCP/IP协议进行网络通信，客户端和服务端之间通过三次握手建立连接。

连接上数据库后，就可以执行sql语句了（以查询语句为例）。

sql查询语句命中缓存
查询缓存： 当sql是查询语句，MySQL 拿到这个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。这个查询请求能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。查询缓存前要校验用户对表是否有查询权限。

查询缓存往往弊大于利：
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空，对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。
通过设置参数 query_cache_type，选择是否使用查询缓存。
mysql8.0已经将查询缓存的整块功能删掉了。

没有命中查询缓存，就要开始真正执行语句了
分析器：分析器会做“词法分析”和“语法分析”以及“语义分析等，判断sql语句中的关键字，表，语法是否正确。

如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒，语法错误会提示第一个出现错误的位置，所以你要关注的是紧接“use near”，可以很方便检查sql语句。

优化器：语法解析之后，服务器程序获得到了需要的信息，比如要查询的列是哪些，表是哪个，搜索条件是什么等等。但光有这些是不够的，因为我们写的MySQL语句执行起来效率可能并不是很高，MySQL的优化程序会对我们的语句做一些优化，如外连接转换为内连接、表达式简化、子查询转为连接等。可以通过expllian语句来查看某个sql语句的执行计划。

执行器： MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。先是校验权限，要先判断一下你对这个表 T 有没有执行查询的权限，然后根据表的引擎定义，去使用这个引擎提供的接口。

存储引擎： 储存数据，并提供读写接口。

存储引擎的一些操作：

查看当前服务器程序支持的存储引擎：

SHOW ENGINES;

设置表的存储引擎

-- 创建表时指定存储引擎CREATE TABLE 表名(    建表语句;) ENGINE = 存储引擎名称;-- 修改表的存储引擎ALTER TABLE 表名 ENGINE = 存储引擎名称;

查看表使用的存储引擎

 SHOW CREATE TABLE  表名

字符集和比较规则

字符集：表示字符的范围以及编码规则，字符编码规则是指一种映射规则，根据这个映射规则可以将某个字符映射成其他形式的数据以便在计算机中存储和传输。

例如ASCII字符编码规定使用单字节中低位的7个比特去编码所有的字符，在这个编码规则下字母A的编号是65（ASCII码），用单字节表示就是0x41，因此写入存储设备的时候就是二进制的 01000001。以下是ASCLL字符编码规则以及字符范围(128个)。

一些常用的字符集
GB2312字符集

 收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。 其中收录汉字6763个，其他文字符号682个。 同时这种字符集又兼容ASCII字符集，所以在编码方式上显得有些奇怪：如果该字符在ASCII字符集中，则采用1字节编码。否则采用2字节编码，也就是变长编码。

GBK字符集

GBK字符集只是在收录字符范围上对GB2312字符集作了扩充，编码方式上兼容GB2312。

utf8字符集

收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容ASCII字符集，采用变长编码方式，编码一个字符需要使用1～4个字节

MySQL中支持的字符集
查看当前MySQL中支持的字符集

SHOW CHARSET;

MySQL中的utf8和utf8mb4区别

utf8mb3：阉割过的utf8字符集，只使用1～3个字节表示字符。
utf8mb4（mysql 5.5.3版本之后）：正宗的utf8字符集，使用1～4个字节表示字符。

某些中文生僻字和emoji表情，是四个字符的，只能使用utf8mb4编码

字符集的比较规则：既比较两个字符大小的规则，每种字符集对应若干种比较规则，每种字符集都有一种默认的比较规则。例如：不区分大小写，按照中文拼音顺序等。

查看MySQL中支持的比较规则的命令

SHOW COLLATION 

MySQL有4个级别的字符集和比较规则

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

服务器级别

-- 查看Mysql服务器的字符集SHOW VARIABLES LIKE 'character_set_server';-- 查看Mysql服务器的比较规则SHOW VARIABLES LIKE 'collation_server';-- 修改服务器的字符集和比较规则-- 可以在启动服务器程序时通过启动选项-- 或者在服务器程序运行过程中使用SET语句修改这两个变量的值。[server]character_set_server=gbkcollation_server=gbk_chinese_ci

数据库级别

-- 查看数据库的字符集 SHOW VARIABLES LIKE 'character_set_database';-- 查看数据库的比较规则SHOW VARIABLES LIKE 'collation_database';-- 创建数据库时指定字符集和比较规则CREATE DATABASE 数据库名    [[DEFAULT] CHARACTER SET 字符集名称]    [[DEFAULT] COLLATE 比较规则名称];-- 修改数据库指定字符集和比较规则ALTER DATABASE 数据库名    [[DEFAULT] CHARACTER SET 字符集名称]    [[DEFAULT] COLLATE 比较规则名称];

表级别

-- 查看表的字符集show create table <表名>;-- 查看表的比较规则show table status from 数据库名 like '%表名%‘ ;-- 创建表时指定字符集和比较规则CREATE TABLE 表名 (列的信息)    [[DEFAULT] CHARACTER SET 字符集名称]    [[DEFAULT] COLLATE 比较规则名称]]-- 修改表指定字符集和比较规则ALTER TABLE 表名    [[DEFAULT] CHARACTER SET 字符集名称]    [[DEFAULT] COLLATE 比较规则名称]

列级别

-- 查看列的字符集和比较规则select *  FROM information_schema.`COLUMNS`where TABLE_SCHEMA = '表名'-- 创建表时指定列的字符集和比较规则CREATE TABLE 表名(    列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],    其他列...);-- 修改列指定字符集和比较规则ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

在转换列的字符集时需要注意，如果转换前列中存储的数据不能用转换后的字符集进行表示，就会发生错误。比方说原先列使用的字符集是utf8，列中存储了一些汉字，现在把列的字符集转换为ascii的话就会出错，因为ascii字符集并不能表示汉字字符。

创建时规则：

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

修改时规则：

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

客户端和服务器通信中的字符集

• 客户端使用操作系统的字符集编码请求字符串，向服务器发送的是经过编码的一个字节串。

• 服务器将客户端发送来的字节串采用character_set_client代表的字符集进行解码，将解码后的字符串再按照character_set_connection代表的字符集进行编码。

• 如果character_set_connection代表的字符集和具体操作的列使用的字符集一致，则直接进行相应操作，否则的话需要将请求中的字符串从character_set_connection代表的字符集转换为具体操作的列使用的字符集之后再进行操作。

• 将从某个列获取到的字节串从该列使用的字符集转换为character_set_results代表的字符集后发送到客户端。

• 客户端使用操作系统的字符集解析收到的结果集字节串。

我们通常都把 character_set_client 、character_set_connection*、character_set_results*** 这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换。
相关语句：

-- 查看字符集show variables like 'character_set_%';-- 设置字符集set character_set_client = 字符集名；set character_set_connection =  字符集名；set character_set_results =  字符集名；

InnoDB记录结构

InnoDB存储引擎众多优点，支持索引，事务以及事务回滚等，成为mysql主流的存储引擎。下面都是以InnoDB作为存储引擎了解mysql中的数据存储。
数据页简介
InnoDB是一个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启我们的数据还是存在的。而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。而我们知道读写磁盘的速度非常慢，和内存读写差了几个数量级，所以当我们想从表中获取某些记录时，InnoDB存储引擎需要一条一条的把记录从磁盘上读出来么？不，那样会慢死，InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。
后面介绍——行记录、页结构、区概念、段概念、独立表空间和系统表空间

行记录

在mysql中，行记录是数据存储的基本单位，我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。InnoDB存储引擎有四种行格式，分别是Compact（紧凑的）、Redundant（冗余的）、Dynamic（动态的）和Compressed（压缩的）。虽有不同，但原理相同。

创建或修改表的语句中指定行格式

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

查看当前表指定的行格式

show table status from lottery like '%表名%' ;

Compact（紧凑的）行格式

一条完整的记录其实可以被分为记录的额外信息和记录的真实数据两大部分

记录的额外信息
变长字段长度列表
存放所有变长字段的真实数据占用的字节长度，每个可变长字段的对应的长度按照列的顺序逆序存放；
变长字段中存储多少字节的数据是不固定的，故需要记录变长字段的真实数据占用的字节长度。
变长字段类型包括VARCHAR(M)、VARBINARY(M)、各种TEXT类型，各种BLOB类型等。
NULL值列表
用于标识表中允许存储NULL的列，是否为空。也是按照列的顺序逆序排列
记录头信息
由固定的5个字节组成


记录的真实数据
除了用户自己定义的列的数据以外，MySQL会为每个记录默认的添加一些列，
row_id （唯一标识一条记录，占6字节，当表中有主键或唯一约束，无改字段）、transaction_id（事务ID，占6字节）、roll_pointer（回滚指针，占7字节）

数据演示：
准备表和表数据：

-- 创建表CREATE TABLE record_format_demo (c1 VARCHAR(10),c2 VARCHAR(10) NOT NULL,c3 CHAR(10),c4 VARCHAR(10)) CHARSET=ascii ROW_FORMAT=COMPACT;-- 插入表数据INSERT INTO record_format_demo(c1, c2, c3, c4) values('aaaa', 'bbb', 'cc', 'd'), ('eeee', 'fff', NULL, NULL);

当前表结构：

表record_format_demo使用的字符集是ascii,行格式是compact,可以得到表中两条记录的存储格式详情如下：

Compact（紧凑的）行格式对CHAR(M) 类型的处理
对于 CHAR(M) 类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表。

Redundant行格式

与Compact（紧凑的）行格式相比，记录字段的位置，通过字段长度偏移列表。字段长度偏移指的是从第一列的真实数据的开始的到当前列的真实数据的结尾。

Redundant（冗余）的记录头信息

与Compact（紧凑的）行格式的记录头信息相比
Redundant （冗余的）行格式多了 n_field 和 1byte_offs_flag 这两个属性。
Redundant 冗余的）行格式没有 record_type 这个属性。
当表record_format_demo使用的字符集是ascii,行格式是Redundant 冗余的）,可以得到表中两条记录的存储格式详情如下：

Redundant行格式对CHAR(M) 类型的处理
Redundant行格式不管该列使用的字符集是什么，只要是使用CHAR(M)类型，占用的真实数据空间就是该字符集表示一个字符最多需要的字节数和M的乘积。比方说使用utf8mb3字符集的CHAR(10)类型的列占用的真实数据空间始终为30个字节，使用gbk字符集的CHAR(10)类型的列占用的真实数据空间始终为20个字节。

行溢出数据
对于VARCHAR(M)类型的列最多可以占用65535个字节(MySQL设置的大小限制)，其中的M代表该类型最多存储的字符数量。这个65535个字节除了列本身的数据之外，还包括一些其他的数据（storage overhead），比如说以Compact（紧凑的）行格式为例，我们为了存储一个VARCHAR(M)类型的列，其实需要占用3部分存储空间：

• 真实数据
• 真实数据占用字节的长度
• NULL值标识（该列没有NOT NULL属性）

除去真实数据占用字节的长度占的两字节，NULL值标识标识占的一字节，真实数据还可使用65532字节。
utf8mb4字符集表示一个字符最多需要4个字节，那在该字符集下，M的最大取值就是16,383，就是说最多能存储16,383（也就是：65532/4）个字符。

行溢出处理
MySQL中磁盘和内存交互的基本单位是页，以页为基本单位来管理存储空间的，我们的记录都会被分配到某个页中存储。一个页的大小是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65532个字节，会出现一个页存放不了一条记录情况。
对于Compact和Reduntant行格式来说，如果某一列中的数据非常多的话，在本记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中，这个过程也叫做行溢出，存储超出768字节的那些页面也被称为溢出页。

Dynamic和Compressed行格式

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

InnoDB数据页结构

页是Innodb管理存储空间的基本单位，大小一般是16KB,InnoDB为了不同目的，有许多不同类型的页，比如存放表空间头部信息的页，存放Insert Buffer信息的页，存放INODE信息的页，存放undo日志信息的页等。我们聚焦的是那些存放我们表中记录的那种类型的页，称为数据页（索引（INDEX）页）。

数据页整体结构

数据页中的行记录（用户记录以及最大、最小记录）

行记录的格式已经了解了，现在重点看行记录中的记录头信息。
还是以Compact(紧凑的)行格式为例：


准备演示数据：

CREATE TABLE page_demo( c1 INT, c2 INT,c3 VARCHAR(10000), PRIMARY KEY (c1))CHARSET=ascii ROW_FORMAT=Compact;INSERT INTO page_demo VALUES(1, 100, 'aaaa'), (2, 200, 'bbbb'), (3, 300, 'cccc'), (4, 400, 'dddd');

• delete_mask
  标记着当前记录是否被删除，占用1个二进制位，值为0的时候代表记录并没有被删除，为1的时候代表记录被删除掉了。
  这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗，所以只是打一个删除标记而已，所有被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为所谓的可重用空间（介绍事务的时候会详细介绍删除操作的详细过程）
• min_rec_mask
  B+树的每层非叶子节点中的最小记录都会添加该标记，我们插入的四条记录的min_rec_mask值都是0，意味着它们都不是B+树的非叶子节点中的最小记录。
• n_owned
  当前记录拥有的记录数。
• heap_no
  示当前记录在本页中的位置
• record_type
  表示当前记录的类型，一共有4种类型的记录，0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录。
• next_record
  表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。

页中记录按照主键从小到大的顺序形成了一个单链表,通过next_record作为引用找到下一个节点记录，页中维护了两个初始节点记录，Infimum记录（也就是最小记录） 的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是 Supremum记录（也就是最大记录）,record_type区分用户记录（0或1）、最小记录（2）和最大记录（3）。min_rec_mask只有B+树的每层非叶子节点中的最小记录是1，其他记录都是0。heap_no标识记录位置，最小记录为0，依次是用户记录递增，最后是最大记录。

数据页中的Free Space（空闲空间）

就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了。

数据页中的Page Directory（页目录）

Page Directory（页目录）为了快速在页中查找某条记录。
页目录构建规则：

• 将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。对每个分组中的记录条数是有规定的：对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。

• 每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录。

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

• 通过二分法确定该记录所在的槽，并找到该槽中主键值最小的那条记录。

• 通过记录的next_record属性遍历该槽所在的组中的各个记录。

数据页中的Page Header（页面头部）

专门针对数据页记录的各种状态信息，记录的信息包括本页中已经存储了多少条记录，第一条记录的地址是什么，页目录中存储了多少个槽等等，这个部分占用固定的56个字节。

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

• PAGE_N_DIRECTION
  表示最后插入记录的方向的连续数量，最后插入方向与上一次插入方向不同，清零重新计算。

数据页中的File Header（文件头部）

File Header针对各种类型的页都通用，它描述了一些针对各种页都通用的一些信息。比方说这个页的编号是多少，它的上一个页、下一个页是谁等， 这个部分占用固定的38个字节。

• FIL_PAGE_SPACE_OR_CHKSUM
  这个代表当前页面的校验和（checksum），快速比较页是否相同。
• FIL_PAGE_OFFSET
  每一个页都有一个单独的页号，InnoDB通过页号来可以唯一定位一个页。
  -FIL_PAGE_TYPE
  上面介绍的其实都是存储记录的数据页，其实还有很多别的类型的页
  
• FIL_PAGE_PREV和FIL_PAGE_NEXT
  FIL_PAGE_PREV和FIL_PAGE_NEXT就分别代表本页的上一个和下一个页的页号。所有的数据页其实是一个双链表。
  

数据页中的File Trailer（文件尾部）

可以分成2个小部分：

• 前4个字节代表页的校验和
  这个部分是和File Header中的校验和相对应的。每当一个页面在内存中修改了，在同步之前就要把它的校验和算出来，File Header在页面的前面，校验和会被首先同步到磁盘，当完全写完时，校验和也会被写到页的尾部，如果完全同步成功，则页的首部和尾部的校验和应该是一致的。
• 后4个字节代表页面被最后修改时对应的日志序列位置（LSN）
  这个部分也是为了校验页的完整性的。

B+树索引

InnoDB中的索引方案

为了更方便查找记录，我们把数据页存放到B+树这个数据结构中的最底层的节点上，这些节点也被称为叶子节点或叶节点。B+树的非叶子节点是都是目录页。

目录项记录和普通的用户记录的不同点：

• 目录项记录的record_type值是1，而普通用户记录的record_type值是0。

• 目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列，另外还有InnoDB自己添加的隐藏列。

• 记录头信息的min_rec_mask的属性，只有在存储目录项记录的页中的主键值最小的目录项记录的min_rec_mask值为1，其他别的记录的min_rec_mask值都是0。

select * from 表名 where 主键 = 1；
查找过程：
1.如果B+树只有一层，也就是只有根节点，该节点也就是数据页（也就是页中查找页的过程），查找过程：

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

如果B+树有两层及以上，只有最底层的节点类型是数据页，其他层的节点类型是目录页，查找过程：

• 从根节点出发，通过二分法确定该记录所在的槽，并找到该槽中主键值最小的那条记录。
  递归查找下一层节点（所在的槽分组中的节点数最多8条）。
• 当到达最底层，找到数据页节点，还是通过二分查找定该记录所在的槽，再通过记录的next_record属性遍历该槽所在的组中的各个记录。
  设B+树为节点上的记录数为m,一共有n个节点：
  访问的节点数量 ≈ \approx ≈ B+树深度 = log m n logm^n logmn
  每个节点访问时间复杂度=在槽中二分查找下一层节点时间复杂度
  ≈ \approx ≈ O( log 2 m log2^m log2m)

得到
B+树中查找一个记录时间复杂度=访问的节点数量×每个节点访问时间复杂度= l o g mn logm^n logmn×O( l o g 2m log2^m log2m )
B+树的搜索过程中的IO次数 = 搜索过程中访问节点的数量 ≈ \approx ≈ B+树的深度 = l o g mn logm^n logmn

B+树都不会超过4层，数据页中用户记录最多存放100条记录，目录页中目录记录最多存放1000条，如果B+树是4层，也就是100×1000×1000×1000=100000000000，既一千亿条数据。

B+树索引的分类

聚簇索引

所有完整的用户记录都存放在这个聚簇索引的叶子节点处，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。
它有两个特点：
1.使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

• 页内的记录是按照主键的大小顺序排成一个单向链表。

• 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。

• 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

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

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

非聚簇索引（二级索引）

在非聚簇索引的叶子节点上存储的并不是真正的行数据，而是主键 +当前索引列。
按字段特性分类可分为：唯一索引、普通索引、前缀索引。

• 唯一索引
  建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突。

• 普通索引
  建立在普通字段上的索引被称为普通索引。

• 前缀索引
  前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引，而不是在整个字段上建索引。前缀索引可以建立在类型为char、varchar、binary、varbinary的列上，可以大大减少索引占用的存储空间，也能提升索引的查询效率。

按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）。

• 单列索引
  建立在单个列上的索引被称为单列索引。

• 联合索引（复合索引、组合索引）
  建立在多个列上的索引被称为联合索引，又叫复合索引、组合索引。

回表
在非聚簇索引中查找到的最终结果是——主键 +当前索引列，当前索引列可能无法包含select的数据列（select的数据列能直接从二级索引中取得，称为覆盖索引）还需拿着主键去聚簇索引中再进行查询。聚簇索引中才包含

B+树索引的使用

索引的代价

索引是个好东西，可不能乱建。一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。

• 空间上的代价
  每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，是很大的一片存储空间
• 时间上的代价
  每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。

B+树索引适用的情况

准备数据：

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

该表拥有主键索引 key（id）和组合索引idx_name_birthday_phone_number (name, birthday, phone_number)

全值匹配

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

组合索引idx_name_birthday_phone_number (name, birthday, phone_number)，按照name，birthday, phone_number顺序排序，查询条件中三个字段都是等值比较。索条件中的列和索引列一致的话，这种情况为全值匹配。

匹配左边的列

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

搜索语句中也可以不用包含全部联合索引中的列，只包含左边的就行。

匹配列前缀

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

B+树中的数据页和记录通过该列的字符集和比较规则进行排序的，这些字符串的前n个字符，也就是前缀都是排好序的，所以对于字符串类型的索引列来说，我们只匹配它的前缀也是可以快速定位记录的。

匹配范围值

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';-- 如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';

由于B+树中的数据页和记录是先按name列排序的,name列相同再按birthday列排序,birthday列相同再按照phone_number列排序。

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

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

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

用于排序

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

因为这个B+树索引本身就是按照上述规则排好序的，所以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了。

用于分组

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

和使用B+树索引进行排序是一个道理，分组列的顺序也需要和索引列的顺序一致，也可以只使用索引列中左边的列进行分组

在使用索引时需要注意下面这些事项：

• 只为用于搜索、排序或分组的列创建索引
• 为列的基数大(某列不重复的个数)的列创建索引
• 索引列的类型尽量小
• 可以只对字符串值的前缀建立索引
• 只有索引列在比较表达式中单独出现才可以适用索引
• 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性。
• 定位并删除表中的重复和冗余索引
• 尽量使用覆盖索引进行查询，避免回表带来的性能损耗。

InnoDB的表空间

区的概念——连续的64个页就是一个区。
不管独立系统表空间还是独立表空间，都可以看成是由若干个区组成的。
每256个区又分一组。
**引入区的原因：**进行范围查找的时候，利用B+树直接定位到最左边记录和最右边记录，然后沿着页之间的双向链表，页内行之间的单向链表一直扫描，如果页之前的距离非常远，就会有随机I/O,这是非常慢的。区在物理位置上是连续的64页，这样在同一个区中查找就是顺序I/O,是非常快的。表中数据非常多时，甚至一次性分配多个物理位置上连续的区。

段的概念——段不对应表空间中某一个连续的物理空间，而是一个逻辑上的概念，由若干个零散的页以及一些完整的区组成。
考虑以完整的区为单位分配给某个段对于数据量较小的表太浪费存储空间的这种情况，InnoDB提出了一个碎片（fragment）区的概念。也就是在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页用于段A，有些页用于段B，有些页甚至哪个段都不属于。碎片区直属于表空间，并不属于任何一个段。所以此后为某个段分配存储空间的策略是这样的：

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

引入段的原因： 范围查找时，不区分B+树叶子节点和非叶子节点，都放在同一个区中，范围扫描效果还是不行的，叶子节点和非叶子节点可以交错存放在一个区中，还是会导致随机I/O。一个段对应“一个索引叶子节点的区的集合”或者“非叶子节点的区的集合”。故一个索引对应两个段。
区的分类

• 空闲的区：现在还没有用到这个区中的任何页。

• 有剩余空间的碎片区：表示碎片区中还有可用的页。

• 没有剩余空间的碎片区：表示碎片区中的所有页都被使用，没有空闲页。

• 附属于某个段的区。每一个索引都可以分为叶子节点段和非叶子节点段，除此之外InnoDB还会另外定义一些特殊作用的段，在这些段中的数据量很大时将使用区来作为基本的分配单位。

独立表空间结构和系统表空间

表空间结构整体图：

第一个组最开始的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类型：同上。

XDES Entry的结构
为了方便管理这些区，设计InnoDB的大佬设计了一个称为XDES Entry的结构（全称就是Extent Descriptor Entry），每一个区都对应着一个XDES Entry结构，这个结构记录了对应的区的一些属性。

• Segment ID（8字节）
  该区所在的段Id
• List Node（12字节）
  Pre Node Page Number和Pre Node Offset的组合就是指向前一个XDES Entry的指针
  Next Node Page Number和Next Node Offset的组合就是指向后一个XDES Entry的指针。
• State（4字节）
  表明区的状态。FREE（空闲的区）、FREE_FRAG（有剩余空间的碎片区）、FULL_FRAG（没有剩余空间的碎片区）和FSEG（附属某个段的区）。
• Page State Bitmap（16字节）
  共占用16个字节，也就是128个比特位，区中一共有64个页，每两个比特对应一个页，标识页是否空闲。

XDES Entry链表

为了快速定位未使用的页用来插入数据。InnoDB给每个段中的区对应的XDES Entry结构建立了三个链表，通过List Node作为指针（这三个链表上的区都是直属某个段，既区的类型是FSEG（附属某个段的区））：

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

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

只要拿到这三个链表的基节点（头节点），也就可以拿到这三种状态的区。

链表基节点

• List Length表明该链表一共有多少节点，

• First Node Page Number和First Node Offset表明该链表的头节点在表空间中的位置。

• Last Node Page Number和Last Node Offset表明该链表的尾节点在表空间中的位置。

INODE Entry结构

每个段都定义了一个INODE Entry结构来记录一下段中的属性。

• 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。
• Magic Number：
    这个值是用来标记这个INODE Entry是否已经被初始化了（初始化的意思就是把各个字段的值都填进去了）。
• Fragment Array Entry
    我们前面强调过无数次：段是一些零散页和一些完整的区的集合，每个Fragment Array Entry结构都对应着一个零散的页，这个结构一共4个字节，表示一个零散页的页号。

各类型页详细情况

FSP_HDR类型

FSP_HDR页，是第一个组的第一个页，也是表空间的第一个页

重点来看看File Space Header和XDES Entry这两个部分（其它部分在页结构都介绍过）；
File Space Header部分

• 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部分
• FRAG_N_USED
  表示FREE_FRAG链表中已经使用的页数量，方便之后在链表中查找空闲的页。
• FREE Limit
  在该字段表示的页号之前的区都被初始化了，之后的区尚未被初始化
  表空间都对应着具体的磁盘文件，一开始我们创建表空间的时候对应的磁盘文件中都没有数据，所以我们需要对表空间完成一个初始化操作，包括为表空间中的区建立XDES Entry结构，为各个段建立INODE Entry结构，建立各种链表等等的各种操作。一开始就为表空间申请一个特别大的空间，但是实际上有绝大部分的区是空闲的，我们可以选择把所有的这些空闲区对应的XDES Entry结构加入FREE链表，也可以选择只把一部分的空闲区加入FREE链表
• Next Unused Segment ID
  该字段表明当前表空间中最大的段ID的下一个ID，方便创建新段的时候赋予新段一个唯一的ID值
• Space Flags
  略。
• List Base Node for SEG_INODES_FULL List和List Base Node for SEG_INODES_FREE List
  代表两个基结点
  SEG_INODES_FULL链表，该链表中的INODE类型的页都已经被INODE Entry结构填充满了，没空闲空间存放额外的INODE Entry了。SEG_INODES_FREE链表，该链表中的INODE类型的页都已经仍有空闲空间来存放INODE Entry结构。

XDES Entry部分
256个区划分成一组，在每组的第一个页中存放256个XDES Entry结构，每个XDES Entry记录了对应的区的一些属性。结构已经介绍了。

IBUF_BITMAP类型

这种类型的页里边记录了一些有关Change Buffer，后面再详细看。

INODE类型

INODE类型的页就是为了存储INODE Entry结构而存在的。INODE Entry结构已经详细了解了，重点关注List Node for INODE Page List。
List Node for INODE Page List存储上一个INODE页和下一个INODE页的指针，用来构建SEG_INODES_FULL链表（该链表中的INODE类型的页中已经没有空闲空间来存储额外的INODE Entry结构）和SEG_INODES_FREE链表（该链表中的INODE类型的页中还有空闲空间来存储额外的INODE Entry结构了）。

Segment Header 结构的运用

其中的PAGE_BTR_SEG_LEAF和PAGE_BTR_SEG_TOP都占用10个字节，它们其实对应一个叫Segment Header的结构，该结构图示如下：

因为一个索引只对应两个段，所以只需要在索引的根页中记录这两个结构即可。

系统表空间额外存储的页
系统表空间和独立表空间的前三个页（页号分别为0、1、2，类型分别是FSP_HDR、IBUF_BITMAP、INODE）的类型是一致的，只是页号为3～7的页是系统表空间特有的

除了这几个记录系统属性的页之外，系统表空间的extent 1和extent 2这两个区，也就是页号从64~191这128个页被称为Doublewrite buffer，也就是双写缓冲区。大部分知识都涉及到了事务和多版本控制的问题，这些问题我们会放在后边的章节集中介绍。

系统表空间详细图示：

表的访问方法

单表访问方法

熟悉过记录结构、数据页结构以及索引的部分，在来看MySQL是怎么执行单表查询的。
准备数据：

CREATE TABLE single_table (    id INT NOT NULL AUTO_INCREMENT,    key1 VARCHAR(100),    key2 INT,    key3 VARCHAR(100),    key_part1 VARCHAR(100),    key_part2 VARCHAR(100),    key_part3 VARCHAR(100),    common_field VARCHAR(100),    PRIMARY KEY (id),    KEY idx_key1 (key1),    UNIQUE KEY idx_key2 (key2),    KEY idx_key3 (key3),    KEY idx_key_part(key_part1, key_part2, key_part3)) Engine=InnoDB CHARSET=utf8;

访问方法（access method）概念

在表中查找目标数据的过程，即是访问方法。

查询的执行方式大致分为两种：

• 使用全表扫描进行查询
  把表的每一行记录都扫一遍，过滤中目标数据
• 使用索引进行查询

相对全表扫描，使用索引可以加快查询执行时间。利用索引查找的种类有：

• 针对主键或唯一二级索引的等值查询
• 针对普通二级索引的等值查询
• 针对索引列的范围查询
• 直接扫描整个索引

const
根据主键、普通唯一索引列等值匹配查询（is null除外），这种查询是很快的，查询速率认为是常数级别的，定义为const。

SELECT * FROM single_table WHERE id = 1438;

SELECT * FROM single_table WHERE key2 = 3841;

ref

根据普通的二级索引等值匹配，或is null。（前面说的普通唯一索引列查询时 is null也是这种场景）。这种方式需要先根据普通索引匹配到多个主键，然后根据主键进行回表。

SELECT * FROM single_table WHERE key1 = 'abc';![请添加图片描述](https://img-blog.csdnimg.cn/d46f492892a44cbca653768c24d8ca28.png)

ref_or_null

根据普通的二级索引等值匹配并且条件里有or is null。

SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;

range

根据主键索引或普通索引（包含唯一索引）进行范围查找

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

index

索引覆盖，你查询的列刚好是索引列，即使查询条件是联合索引的非最左索引列，查询的条件是联合索引中的列，也可能会走索引覆盖

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

all

全表扫描，直接扫描主键索引，这种访问方式称为all。

index merge

除此之外，还会有index merge（索引合并），针对一些and、or的操作，单纯的回表可能速度会慢一些，如果先将使用到的索引先进行求 交集、并集之后在进行回表，会更加高效。

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

两张表连接的原理

连接的本质

准备数据：

CREATE TABLE t1 (m1 int, n1 char(1));CREATE TABLE t2 (m2 int, n2 char(1));INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c');INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd');

连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户.
t1和t2两个表连接起来的过程如下图:

连接过程

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

首先确定第一个需要查询的表，这个表称之为驱动表。再通过单表访问，查询目标结果集。确定以t1表为驱动表，通过all方式访问该表。

2.针对上一步骤中从驱动表产生的结果集中的每一条记录,分别需要到t2表中查找匹配的记录，所谓匹配的记录，指的是符合过滤条件的记录，这个过程还是单表访问。

内连接和外连接

准备数据：

CREATE TABLE student (    number INT NOT NULL AUTO_INCREMENT COMMENT '学号',    name VARCHAR(5) COMMENT '姓名',    major VARCHAR(30) COMMENT '专业',    PRIMARY KEY (number)) Engine=InnoDB CHARSET=utf8 COMMENT '学生信息表';CREATE TABLE score (    number INT COMMENT '学号',    subject VARCHAR(30) COMMENT '科目',    score TINYINT COMMENT '成绩',    PRIMARY KEY (number, score)) Engine=InnoDB CHARSET=utf8 COMMENT '学生成绩表';

内连接
对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，我们上面提到的连接都是所谓的内连接。

select student.*, score.*  from student join score where student.number ='123';

外连接
对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

• 左外连接（或左连接）

select * from student left join score on  Student .number = score .number 

- 右外连接（或右连接）

select * from student right join score on  Student .number = score .number 

• 完全外连接（或完全连接）（mysql中不支持,可以union，UNION 操作， 会合并掉重复的）

select * from student left join score on  Student .number = score .number unionselect * from student right join score on  Student .number = score .number 

WHERE和ON的区别
ON 是连接查询中的连接条件，就是驱动表中的数据去被驱动表中进行匹配的一种规则（匹配条件）。对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。
WHERE 是对查询出来的结果集进行条件筛选，是先查询出所有的结果集然后再使用where来进行筛选的；不论是内连接还是外连接，凡是不符合WHERE子句中的过滤条件的记录都不会被加入最后的结果集。

在内连接中where和on效果是等价的，但是还是不建议写where；

连接的原理

嵌套循环连接（Nested-Loop Join）
对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍，具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数。这种连接执行方式称之为嵌套循环连接。

对于内连接，sql语句中不能决定驱动表，而是优化器根据执行计划选取的。
对于外连接，左外连接把sql中left join 前的表作为驱动，右外连接把sql中right join 前的表作为驱动表。
嵌套循环连接过程

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

两个步骤都能通过索引进行优化查询，加快连接速度。

基于块的嵌套循环连接
采用嵌套循环连接算法的两表连接过程中，被驱动表可是要被访问好多次的，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表，这个I/O代价就非常大了，所以我们得想办法：尽量减少访问被驱动表的次数。
MySQL中引入join buffer，它是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价

InnoDB的BufferPool

对于使用InnoDB作为存储引擎的表来说，不管是用于存储用户数据的索引（包括聚簇索引和二级索引），还是各种系统数据，都是以页的形式存放在表空间中的，而所谓的表空间只不过是InnoDB对文件系统上一个或几个实际文件的抽象，也就是说我们的数据说到底还是存储在磁盘上的。
InnoDB存储引擎在处理客户端的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中，也就是说即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中。

InnoDB为了缓存磁盘中的页，在MySQL服务器启动的时候就向操作系统申请了一片连续的内存——Buffer Pool（缓存池）。

Buffer Pool内部组成

Buffer Pool中默认的缓存页大小和在磁盘上默认的页大小是一样的，都是16KB。
 每个缓存页对应的控制信息占用的内存大小是相同的，我们就把每个页对应的控制信息占用的一块内存称为一个控制块吧，控制块和缓存页是一一对应的，它们都被存放到 Buffer Pool 中，其中控制块被存放到 Buffer Pool 的前面，缓存页被存放到 Buffer Pool 后边。
 控制信息包括该页所属的表空间编号、页号、缓存页在Buffer Pool中的地址、链表节点信息、一些锁信息以及LSN信息等等。
### free链表的管理

free链表记录了Buffer Pool中哪些缓存页是可用的，链表上的节点是所有空闲的缓存页对应的控制块。
每当需要从磁盘中加载一个页到Buffer Pool中时，就从free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上（就是该页所在的表空间、页号之类的信息），然后把该缓存页对应的free链表节点从链表中移除。

缓存页的哈希处理

Buffer Pool通过哈希表存储已经缓存的页，key是（表空间号+页号），value是缓冲页。从而快速确认一个页是否已经存在于Buffer Pool中。

flush链表的管理

当执行的sql是修改语句，修改了Buffer Pool中某个缓存页的数据，那它就和磁盘上的页不一致了，这样的缓存页也被称为脏页（英文名：dirty page）。flush链表记录了需要被刷新到磁盘上的缓存页，链表上的节点是修改过的缓存页对应的控制块。

LRU链表的管理

Buffer Pool对应的内存大小毕竟是有限的，如果需要缓存的页占用的内存大小超过了Buffer Pool大小，也就是free链表中已经没有多余的空闲缓存页的时候岂不是很尴尬。

简单的LRU链表
LRU全称Least Recently Used(最近最少使用),LRU链表按照最近最少使用的原则去淘汰缓存页。
处理过程：

• 如果该页不在Buffer Pool中，在把该页从磁盘加载到Buffer Pool中的缓存页时，就把该缓存页对应的控制块作为节点塞到链表的头部。
• 如果该页已经缓存在Buffer Pool中，则直接把该页对应的控制块移动到LRU链表的头部。

划分区域的LRU链表
简单的LRU链表在两种场景下存在问题：

• 情况一：InnoDB提供了预读（英文名：read ahead）。所谓预读，就是InnoDB认为执行当前的请求可能之后会读取某些页面，就预先把它们加载到Buffer Pool中。
  1.线性预读
  InnoDB提供了一个系统变量innodb_read_ahead_threshold，如果顺序访问了某个区（extent）的页面超过这个系统变量的值，就会触发一次异步读取下一个区中全部的页面到Buffer Pool的请求。
  2.随机预读
  InnoDB提供了一个系统变量innodb_random_read_ahead(它的默认值为OFF)，如果Buffer Pool中已经缓存了某个区的13个连续的页面，不论这些页面是不是顺序读取的，都会触发一次异步读取本区中所有其的页面到Buffer Pool的请求。

预读本来是个好事儿，如果预读到Buffer Pool中的页成功的被使用到，那就可以极大的提高语句执行的效率。可是如果用不到呢？这些预读的页都会放到LRU链表的头部，但是如果此时Buffer Pool的容量不太大而且很多预读的页面都没有用到的话，这就会导致处在LRU链表尾部的一些缓存页会很快的被淘汰掉，会大大降低缓存命中率。

• 情况二：会写一些需要扫描全表的查询语句（比如没有建立合适的索引或者没有WHERE子句的查询）。
  扫描全表意味着将访问到该表所在的所有页！假设这个表中记录非常多的话，那该表会占用特别多的页，当需要访问这些页时，会把它们统统都加载到Buffer Pool中，这也就意味着Buffer Pool中的所有页都被换了一次血，会大大降低缓存命中率。

问题总结：
1.加载到Buffer Pool中的页不一定被用到。
2.如果非常多的使用频率偏低的页被同时加载到Buffer Pool时，可能会把那些使用频率非常高的页从Buffer Pool中淘汰掉。

问题解决：
引入划分区域的LRU链表，LRU链表按照一定比例分成两截

• young区域，存储使用频率非常高的缓存页
• old区域，存储使用频率不是很高的缓存页
• 针对预读的页面可能不进行后续访情况的优化
  当磁盘上的某个页面在初次加载到Buffer Pool中的某个缓存页时，该缓存页对应的控制块会被放到old区域的头部。
• 当磁盘上的某个页面在初次加载到Buffer Pool中的某个缓存页时，该缓存页对应的控制块会被放到old区域的头部。
  在进行全表扫描时，虽然首次被加载到Buffer Pool的页被放到了old区域的头部，但是后续会被马上访问到，每次进行访问的时候又会把该页放到young区域的头部，这样仍然会把那些使用频率比较高的页面给顶下去。InnoDB的大佬规定每次去页面中读取一条记录时，都算是访问一次页面，而一个页面中可能会包含很多条记录，也就是说读取完某个页面的记录就相当于访问了这个页面好多次。
  在对某个处在old区域的缓存页进行第一次访问时就在它对应的控制块中记录下来这个访问时间，如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该页面就不会被从old区域移动到young区域的头部，否则将它移动到young区域的头部。
  InnoDB提供了一个系统变量innodb_old_blocks_time(默认值是1000，单位毫秒)，也就意味着对于从磁盘上被加载到LRU链表的old区域的某个页来说，如果第一次和最后一次访问该页面的时间间隔小于1s（很明显在一次全表扫描的过程中，多次访问一个页面中的时间不会超过1s），那么该页是不会被加入到young区域的。

更进一步优化LRU链表
对于young区域的缓存页来说，我们每次访问一个缓存页就要把它移动到LRU链表的头部，这样开销是不是太大啦，在young区域的缓存页都是热点数据，也就是可能被经常访问的，这样频繁的对LRU链表进行节点移动操作是不是不太好啊，其实我们还可以提出一些优化策略，比如只有被访问的缓存页位于young区域的1/4的后边，才会被移动到LRU链表头部，这样就可以降低调整LRU链表的频率，从而提升性能（也就是说如果某个缓存页对应的节点在young区域的1/4中，再次访问该缓存页时也不会将其移动到LRU链表头部）。
当然还有其他优化策略，当然有啊，你要是好好学，写篇论文，写本书都不是问题，可是这毕竟是一个介绍MySQL基础知识的文章，再说多了篇幅就受不了了，也影响大家的阅读体验，所以适可而止。

其他的一些链表

为了更好的管理Buffer Pool中的缓存页，除了我们上面提到的一些措施，设计InnoDB的大佬们还引进了其他的一些链表，比如unzip LRU链表用于管理解压页，zip clean链表用于管理没有被解压的压缩页，zip free数组中每一个元素都代表一个链表，它们组成所谓的伙伴系统来为压缩页提供内存空间等等。

刷新脏页到磁盘

后台有专门的线程每隔一段时间负责把脏页刷新到磁盘，这样可以不影响用户线程处理正常的请求。

• 从LRU链表的冷数据中刷新一部分页面到磁盘。

• 从flush链表中刷新一部分页面到磁盘。

有时候系统特别繁忙时，也可能出现用户线程批量的从flush链表中刷新脏页的情况，很显然在处理用户请求过程中去刷新脏页是一种严重降低处理速度的行为（毕竟磁盘的速度满的要死），后边介绍redo日志的checkpoint时说了。

设置Buffer Pool实例和调整Buffer Pool大小

Buffer Pool本质是InnoDB向操作系统申请的一块连续的内存空间，在多线程环境下，访问Buffer Pool中的各种链表都需要加锁处理什么的，在Buffer Pool特别大而且多线程并发访问特别高的情况下，单一的Buffer Pool可能会影响请求的处理速度。所以在Buffer Pool特别大的时候，我们可以把它们拆分成若干个小的Buffer Pool，每个Buffer Pool都称为一个实例，它们都是独立的，独立的去申请内存空间，独立的管理各种链表，独立的等等，所以在多线程并发访问时并不会相互影响，从而提高并发处理能力。
可以在服务器启动的时候通过设置innodb_buffer_pool_instances的值来修改Buffer Pool实例的个数

[server]innodb_buffer_pool_instances = 2

在MySQL 5.7.5之前，Buffer Pool的大小只能在服务器启动时通过配置innodb_buffer_pool_size启动参数来调整大小，在服务器运行过程中是不允许调整该值的。不在5.7.5以及之后的版本中支持了在服务器运行过程中调整Buffer Pool大小的功能。

查看Buffer Pool的状态信息

 SHOW ENGINE INNODB STATUS

详细描述见书上。

事务简介

事务是一个抽象的概念，它其实对应着一个或多个数据库操作。

事务的状态

根据这些操作所执行的不同阶段把事务以下状态：

• 活动的（active）
  事务对应的数据库操作正在执行过程中时。
• 部分提交的（partially committed）
  当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时。
• 失败的（failed）
  当事务处在活动的或者部分提交的状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行。
• 中止的（aborted）
  如果事务执行了半截而变为失败的状态，换句话说，就是要撤销失败事务对当前数据库造成的影响。把这个撤销的过程称之为回滚。当回滚操作执行完毕时，也就是数据库恢复到了执行事务之前的状态。
• 提交的（committed）
  当一个处在部分提交的状态的事务将修改过的数据都同步到磁盘上之后。

事务的基本要素

• 原子性（Atomicity）
  整个事务中的所有操作，要么全部完成，要么全部不完成，不能停滞在中间某个环节。事务执行过程中发生错误，会被回滚到事务开始前的状态。
• 一致性（Consistency）
  在事务开始之前和事务结束后，数据库的完整性约束没有被破坏。
• 隔离性（Isolation）
  一个事务的执行不会被其他事务干扰。即一个事务内部的操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不会互相干扰。
• 持久性（Durability）
  在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库中，不会被回滚。

MySQL如何保证ACID?

• 从数据库层面上说，一致性是最终目的，数据库通过原子性、隔离性、持久性来实现一致性。
• Innodb中的undo log可以是实现原子性的关键，当事务回滚时会撤销所有已经执行完毕的sql语句，但是需要记录回滚的日志信息。
• innodb中的redo log可以保证持久性。Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。
• Mysql利用锁和MVCC多版本并发控制(Multi Version Concurrency Control)来保证隔离性。

后面会一个个介绍。

MySQL中事务的语法

开启事务

两种语句之一来开启一个事务

• BEGIN [WORK];
• START TRANSACTION;
  不过比BEGIN语句牛逼一点儿的是，可以在START TRANSACTION语句后边跟随几个修饰符。
  READ ONLY：标识当前事务是一个只读事务，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。
  READ WRITE：标识当前事务是一个读写事务，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。

WITH CONSISTENT SNAPSHOT：启动一致性读（先不用关心什么是个一致性读，后边的章节才会介绍）。

提交事务

COMMIT [WORK]

手动中止事务

ROLLBACK [WORK]

事务提交方式

自动提交
MySQL中有一个系统变量autocommit， 可以看到它的默认值为ON，也就是说默认情况下，如果我们不显式的使用START TRANSACTION或者BEGIN语句开启一个事务，那么每一条语句都算是一个独立的事务，这种特性称之为事务的自动提交。
下面这两条语句就相当于放到两个独立的事务中去执行：
UPDATE account SET balance = balance - 10 WHERE id = 1;
UPDATE account SET balance = balance + 10 WHERE id = 2;

当然，如果我们想关闭这种自动提交的功能，可以使用下面两种方法之一：

显式的的使用START TRANSACTION或者BEGIN语句开启一个事务。

这样在本次事务提交或者回滚前会暂时关闭掉自动提交的功能。

把系统变量autocommit的值设置为OFF，就像这样：

SET autocommit = OFF;
Copy to clipboardErrorCopied
  这样的话，我们写入的多条语句就算是属于同一个事务了，直到我们显式的写出COMMIT语句来把这个事务提交掉，或者显式的写出ROLLBACK语句来把这个事务回滚掉。

隐式提交
当我们使用START TRANSACTION或者BEGIN语句开启了一个事务，或者把系统变量autocommit的值设置为OFF时，事务就不会进行自动提交，但是如果我们输入了某些语句之后就会悄悄的提交掉，就像我们输入了COMMIT语句了一样，这种因为某些特殊的语句而导致事务提交的情况称为隐式提交
这些会导致事务隐式提交的语句包括：

定义或修改数据库对象的数据定义语言（Data definition language，缩写为：DDL）。

所谓的数据库对象，指的就是数据库、表、视图、存储过程等等这些东西。当我们使用CREATE、ALTER、DROP等语句去修改这些所谓的数据库对象时，就会隐式的提交前面语句所属于的事务，就像这样：

BEGIN;

SELECT … # 事务中的一条语句
UPDATE … # 事务中的一条语句
… # 事务中的其它语句

CREATE TABLE … # 此语句会隐式的提交前面语句所属于的事务
Copy to clipboardErrorCopied
隐式使用或修改mysql数据库中的表

当我们使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD等语句时也会隐式的提交前面语句所属于的事务。

事务控制或关于锁定的语句

当我们在一个事务还没提交或者回滚时就又使用START TRANSACTION或者BEGIN语句开启了另一个事务时，会隐式的提交上一个事务，比如这样：

BEGIN;

SELECT … # 事务中的一条语句
UPDATE … # 事务中的一条语句
… # 事务中的其它语句

BEGIN; # 此语句会隐式的提交前面语句所属于的事务
Copy to clipboardErrorCopied
  或者当前的autocommit系统变量的值为OFF，我们手动把它调为ON时，也会隐式的提交前面语句所属的事务。

或者使用LOCK TABLES、UNLOCK TABLES等关于锁定的语句也会隐式的提交前面语句所属的事务。

加载数据的语句

比如我们使用LOAD DATA语句来批量往数据库中导入数据时，也会隐式的提交前面语句所属的事务。

关于MySQL复制的一些语句

使用START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等语句时也会隐式的提交前面语句所属的事务。

其它的一些语句

使用ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、 LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET等语句也会隐式的提交前面语句所属的事务。

保存点

如果你开启了一个事务，并且已经敲了很多语句，忽然发现上一条语句有点问题，你只好使用ROLLBACK语句来让数据库状态恢复到事务执行之前的样子，然后一切从头再来，总有一种一夜回到解放前的感觉。Innodb提出了一个保存点（英文：savepoint）的概念，就是在事务对应的数据库语句中打几个点，我们在调用ROLLBACK语句时可以指定会滚到哪个点，而不是回到最初的原点

redo日志

redo日志引入

事务一组数据库操作sql执行后，首先只会更新Buffer Pool中的页面，并未更新到磁盘上。
假设在事务提交后突然发生了某个故障，导致内存中的数据都失效了，那么这个已经提交了的事务对数据库中所做的更改也就跟着丢失了。
直接更新到磁盘中存在的问题：

• 刷新一个完整的数据页太浪费了
  InnoDB中是以页为单位来进行磁盘IO的，当仅仅修改了某个页面中的一个字节，，就要刷新16KB的数据到磁盘上，显然是太浪费了。
• 随机IO刷起来比较慢
  将某个事务修改的Buffer Pool中的页面刷新到磁盘时，需要进行很多的随机IO，随机IO比顺序IO要慢好几个数量级。

那如何保证事务持久性？引入redo日志
redo日志的功能就是在每次事务提交时，修改项记录永久记录在磁盘中，系统奔溃重启时需要按照所记录的步骤重新更新数据页。

redo日志的优点：

• redo日志占用的空间非常小
  存储表空间ID、页号、偏移量以及需要更新的值所需的存储空间是很小的
• redo日志是顺序写入磁盘的
  redo日志是顺序IO写入磁盘的，速度很快。

redo日志格式

redo日志本质上就是把事务在执行过程中对数据库所做的所有修改都记录下来，在之后系统奔溃重启后可以把事务所做的任何修改都恢复出来。
通用结构
绝大部分类型的redo日志都有下面这种通用的结构：

• type：该条redo日志的类型。
• space ID：表空间ID。
• page number：页号。
• data：该条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）：表示在页面的某个偏移量处写入一串数据。

MLOG_8BYTE的redo日志结构如下所示：

MLOG_WRITE_STRING的redo日志结构如下所示：

示例：
表中既没定义primary key也没有定义Unique键，那么InnoDB会自动的为表添加一个称之为row_id的隐藏列作为主键。服务器会在内存中维护一个全局变量，每当向包含隐藏的row_id列的表中插入一条记录，就会把该变量的值当作新记录的row_id列的值，并且把该变量自增1，每当这个变量的值为256的倍数时，就会将该变量的值刷新到系统表空间的页号为7的页面中一个称之为Max Row ID的属性处，会向系统表空间页号为7的页面的相应偏移量处写入8个字节的值。这个写入过程就会产生一条MLOG_8BYTE类型的redo日志。

复杂一些的redo日志类型
同一条sql,对同一个页面可以产生非常多修改,比如Page Directory中的槽信息、Page Header中的各种页面统计信息、更新上一条记录的记录头信息中的next_record甚至还可能导致索引对应的B+树产生页分裂产生的一系列日志等等,。如果还是一个一个记录修改redo日志，可能日志存储量比本身的数据还多！
InnoDB采用的方案是：
把整个页面的第一个被修改的字节到最后一个修改的字节之间所有的数据当成是一条物理redo日志中的具体数据。

• MLOG_COMP_REC_INSERT（对应的十进制数字为38）：表示插入一条使用紧凑行格式的记录时的redo日志类型。
• MLOG_COMP_PAGE_CREATE（type字段对应的十进制数字为58）：表示创建一个存储紧凑行格式记录的页面的redo日志类型。
• MLOG_COMP_REC_DELETE（type字段对应的十进制数字为42）：表示删除一条使用紧凑行格式记录的redo日志类型。
• MLOG_COMP_LIST_START_DELETE（type字段对应的十进制数字为44）：表示从某条给定记录开始删除页面中的一系列使用紧凑行格式记录的redo日志类型。
• MLOG_COMP_LIST_END_DELETE（type字段对应的十进制数字为43）：与MLOG_COMP_LIST_START_DELETE类型的redo日志呼应，表示删除一系列记录直到MLOG_COMP_LIST_END_DELETE类型的redo日志对应的记录为止。
• MLOG_ZIP_PAGE_COMPRESS（type字段对应的十进制数字为51）：表示压缩一个数据页的redo日志类型。
• ······还有很多很多种类型

-MLOG_COMP_REC_INSERT的redo日志的结构

这些类型的redo日志既包含物理层面的意思，也包含逻辑层面的意思，具体指：

• 物理层面看，这些日志都指明了对哪个表空间的哪个页进行了修改。
• 逻辑层面看，在系统奔溃重启时，并不能直接根据这些日志里的记载，将页面内的某个偏移量处恢复成某个数据，而是需要调用一些事先准备好的函数，执行完这些函数后才可以将页面恢复成系统奔溃前的样子。

以组的形式写入redo日志(Mini-Transaction)

语句在执行过程中可能修改若干个页面。上面示例对于row_id的隐藏列的更新，可能修改系统表空间页号为7的页面的Max Row ID属性（当然也可能更新别的系统页面，只不过我们没有都列举出来而已），还会更新聚簇索引和二级索引对应B+树中的页面。
在执行语句的过程中产生的redo日志被划分成了若干个不可分割的组。

• 更新Max Row ID属性时产生的redo日志是不可分割的。
• 向聚簇索引对应B+树的页面中插入一条记录时产生的redo日志是不可分割的。
• 向某个二级索引对应B+树的页面中插入一条记录时产生的redo日志是不可分割的。
• 等等

乐观插入和悲观插入
乐观插入： 认为数据页的剩余的空闲空间充足，足够容纳这一条待插入记录，直接把记录插入到这个数据页中，记录一条类型为MLOG_COMP_REC_INSERT的redo日志。
悲观插入： 认为数据页剩余的空闲空间不足，遇到这种情况要进行所谓的页分裂操作，也就是新建一个叶子节点，然后把原先数据页中的一部分记录复制到这个新的数据页中，然后再把记录插入进去，把这个叶子节点插入到叶子节点链表中，最后还要在内节点中添加一条目录项记录指向这个新创建的页面。对于悲观插入来说，由于需要新申请数据页，还需要改动一些系统页面，比方说要修改各种段、区的统计信息信息，各种链表的统计信息（比如什么FREE链表、FSP_FREE_FRAG链表等等。反正总共需要记录的redo日志有二、三十条

Innodb规定在执行这些需要保证原子性的操作时必须以组的形式来记录的redo日志，在进行系统奔溃重启恢复时，针对某个组中的redo日志，要么把全部的日志都恢复掉，要么一条也不恢复。
该组中的最后一条redo日志后边加上一条特殊类型的redo日志，该类型名称为MLOG_MULTI_REC_END，type字段对应的十进制数字为31，该类型的redo日志结构很简单，只有一个type字段。

Mini-Transaction（简称mtr）即对底层页面中的一次原子访问的过程称。一个所谓的mtr可以包含一组redo日志，在进行奔溃恢复时这一组redo日志作为一个不可分割的整体。
一个事务可以包含若干条语句，每一条语句其实是由若干个mtr组成，每一个mtr又可以包含若干条redo日志

redo日志的写入过程

redo log block

InnoDB把通过mtr生成的redo日志都放在了大小为512字节的页中。这种页简称为block。

block分为三个部分，log block header和log block trailer存储的是一些管理信息，log block body存储真正的redo日志。
log block header中属性：

• LOG_BLOCK_HDR_NO：每一个block都有一个大于0的唯一标号，本属性就表示该标号值。
• LOG_BLOCK_HDR_DATA_LEN：表示block中已经使用了多少字节，初始值为12（因为log block body从第12个字节处开始）。随着往block中写入的redo日志越来也多，本属性值也跟着增长。如果log block body已经被全部写满，那么本属性的值被设置为512。
• LOG_BLOCK_FIRST_REC_GROUP：一条redo日志也可以称之为一条redo日志记录（redo log record），一个mtr会生产多条redo日志记录，这些redo日志记录被称之为一个redo日志记录组（redo log record group）。LOG_BLOCK_FIRST_REC_GROUP就代表该block中第一个mtr生成的redo日志记录组的偏移量（其实也就是这个block里第一个mtr生成的第一条redo日志的偏移量）。
• LOG_BLOCK_CHECKPOINT_NO：表示所谓的checkpoint的序号，checkpoint是我们后续内容的重点，现在先不用清楚它的意思，稍安勿躁。

log block trailer中属性

• LOG_BLOCK_CHECKSUM：表示block的校验值，用于正确性校验

redo日志缓冲区

redo日志时也不能直接直接写到磁盘上，实际上在服务器启动时就向操作系统申请了一大片称之为redo log buffer的连续内存空间，翻译成中文就是redo日志缓冲区，我们也可以简称为log buffer。

redo日志写入log buffer

一个mtr执行过程中可能产生若干条redo日志，这些redo日志是一个不可分割的组，所以其实并不是每生成一条redo日志，就将其插入到log buffer中，而是每个mtr运行过程中产生的日志先暂时存到一个地方，当该mtr结束的时候，将过程中产生的一组redo日志再全部复制到log buffer中。

不同的事务可能是并发执行的，所以T1、T2之间的mtr可能是交替执行的。每当一个mtr执行完成时，伴随该mtr生成的一组redo日志就需要被复制到log buffer中，也就是说不同事务的mtr可能是交替写入log buffer。

• 事务T1的两个mtr分别称为mtr_T1_1和mtr_T1_2。
• 事务T2的两个mtr分别称为mtr_T2_1和mtr_T2_2。

每个mtr都会产生一组redo日志，用示意图来描述一下这些mtr产生的日志情况：

redo日志刷盘时机

mtr运行过程中产生的一组redo日志在mtr结束时会被复制到log buffer中，可是这些日志在内存里并未持久化，在一些情况下它们会被刷新到磁盘里：

• log buffer空间不足时
  log buffer的大小（通过系统变量innodb_log_buffer_size指定）是有限的，
• 事务提交时
  在事务提交时可以不把修改过的Buffer Pool页面刷新到磁盘，但是为了保证持久性，必须要把修改这些页面对应的redo日志刷新到磁盘。
• 后台线程不停的刷刷刷
• 正常关闭服务器时
• 做所谓的checkpoint时（后面介绍）
• 其它情况

redo日志文件组

MySQL的数据目录（使用SHOW VARIABLES LIKE 'datadir’查看）下默认有两个名为ib_logfile0和ib_logfile1的文件，log buffer中的日志默认情况下就是刷新到这两个磁盘文件中。
可以通过下面几个启动参数来调节：

• innodb_log_group_home_dir

该参数指定了redo日志文件所在的目录，默认值就是当前的数据目录。

• innodb_log_file_size

该参数指定了每个redo日志文件的大小，在MySQL 5.7.21这个版本中的默认值为48MB，

• innodb_log_files_in_group

该参数指定redo日志文件的个数，默认值为2，最大值为100

在将redo日志写入日志文件组时，是从ib_logfile0开始写，如果ib_logfile0写满了，就接着ib_logfile1写，同理，ib_logfile1写满了就去写ib_logfile2，依此类推。如果写到最后一个文件该咋办？那就重新转到ib_logfile0继续写。

redo日志文件格式

log buffer本质上是一片连续的内存空间，被划分成了若干个512字节大小的block。将log buffer中的redo日志刷新到磁盘的本质就是把block的镜像写入日志文件中，所以redo日志文件其实也是由若干个512字节大小的block组成。
redo日志文件组中的每个文件大小都一样，格式也一样，都是由两部分组成：

• 前2048个字节，也就是前4个block是用来存储一些管理信息的。
• 从第2048字节往后是用来存储log buffer中的block镜像的。

前4个特殊block的格式:

• log file header：描述该redo日志文件的一些整体属性，看一下它的结构：
  

属性名	长度（单位：字节）	描述
LOG_HEADER_FORMAT	4	redo日志的版本，在MySQL 5.7.21中该值永远为1
LOG_HEADER_PAD1	4	做字节填充用的，没什么实际意义，忽略～
LOG_HEADER_START_LSN	32	标记本redo日志文件文件偏移量，初始为2048字节
LOG_HEADER_CREATOR	8	一个字符串，标记本redo日志文件的创建者是谁。正常运行时该值为MySQL的版本号，比如：“MySQL 5.7.21”，使用mysqlbackup命令创建的redo日志文件的该值为"ibbackup"和创建时间。
LOG_BLOCK_CHECKSUM	4	本block的校验值，所有block都有，我们不关心

• checkpoint1：记录关于checkpoint的一些属性，看一下它的结构：

属性名	长度（单位：字节）	描述
LOG_CHECKPOINT_NO	8	服务器做checkpoint的编号，每做一次checkpoint，该值就加1。
LOG_CHECKPOINT_LSN	8	服务器做checkpoint结束时对应的LSN值，系统奔溃恢复时将从该值开始。
LOG_CHECKPOINT_OFFSET	8	上个属性中的LSN值在redo日志文件组中的偏移量
LOG_CHECKPOINT_LOG_BUF_SIZE	8	服务器在做checkpoint操作时对应的log buffer的大小
LOG_BLOCK_CHECKSUM	4	本block的校验值，所有block都有，我们不关心

• checkpoint2：结构和checkpoint1一样。

redo日志写入log buffer和刷新到磁盘相关变量

Log Sequeue Number

Log Sequeue Number的全局变量，代表当前系统写入log buffer的日志量。初始的lsn值为8704（也就是一条redo日志也没写入时，lsn的值为8704）。
lsn的增长量，是按照实际写入的日志量加上占用的log block header和log block trailer来计算的

buf_free就是标记下一条redo日志应该写入到log buffer的位置的变量

flushed_to_disk_lsn

buf_next_to_write的全局变量，标记当前log buffer中已经有哪些日志被刷新到磁盘中了

lsn是表示当前系统中写入的redo日志量，这包括了写到log buffer而没有刷新到磁盘的日志。提出了一个表示刷新到磁盘中的redo日志量的全局变量，称之为flushed_to_disk_lsn。系统第一次启动时，该变量的值和初始的lsn值是相同的，都是8704。随着系统的运行，redo日志被不断写入log buffer，但是并不会立即刷新到磁盘，lsn的值就和flushed_to_disk_lsn的值拉开了差距。
那buf_next_to_write和flushed_to_disk_lsn有什么区别，我的理解buf_next_to_write指向最后日志被刷新到磁盘的位置引用，flushed_to_disk_lsn是相对位置偏移量。

flush链表中的LSN

在mtr结束时，会把这一组redo日志写入到log buffer中。除此之外，在mtr结束时还有一件非常重要的事情要做，就是把在mtr执行过程中可能修改过的页面加入到Buffer Pool的flush链表。flush链表插入脏页过程已经了解过。

 当第一次修改某个缓存在Buffer Pool中的页面时，就会把这个页面对应的控制块插入到flush链表的头部，之后再修改该页面时由于它已经在flush链表中了，就不再次插入了。也就是说flush链表中的脏页是按照页面的第一次修改时间从大到小进行排序的。
 在这个过程中会在缓存页对应的控制块中记录两个关于页面何时修改的属性：

• oldest_modification：如果某个页面被加载到Buffer Pool后进行第一次修改，那么就将修改该页面的mtr开始时对应的lsn值写入这个属性。
• newest_modification：每修改一次页面，都会将修改该页面的mtr结束时对应的lsn值写入这个属性。也就是说该属性表示页面最近一次修改后对应的系统lsn值。

flush链表中的脏页按照修改发生的时间顺序进行排序，也就是按照oldest_modification代表的LSN值进行排序，被多次更新的页面不会重复插入到flush链表中，但是会更新newest_modification属性的值。

checkpoint

redo日志文件组容量是有限的，我们不得不选择循环使用redo日志文件组中的文件，但是这会造成最后写的redo日志与最开始写的redo日志追尾，redo日志只是为了系统奔溃后恢复脏页用的，如果对应的脏页已经刷新到了磁盘，也就是说即使现在系统奔溃，那么在重启后也用不着使用redo日志恢复该页面了，所以该redo日志也就没有存在的必要了，那么它占用的磁盘空间就可以被后续的redo日志所重用。也就是说：判断某些redo日志占用的磁盘空间是否可以覆盖的依据就是它对应的脏页是否已经刷新到磁盘里。

之后随着系统的运行，如果页a被刷新到了磁盘，那么它对应的控制块就会从flush链表中移除

 这样mtr_1生成的redo日志就没有用了，它们占用的磁盘空间就可以被覆盖掉了。
InnoDB的大佬提出了一个全局变量checkpoint_lsn来代表当前系统中可以被覆盖的redo日志总量是多少，这个变量初始值也是8704。

比方说现在页a被刷新到了磁盘，mtr_1生成的redo日志就可以被覆盖了，所以我们可以进行一个增加checkpoint_lsn的操作，我们把这个过程称之为做一次checkpoint。做一次checkpoint其实可以分为两个步骤：

• 步骤一：计算一下当前系统中可以被覆盖的redo日志对应的lsn值最大是多少。
   redo日志可以被覆盖，意味着它对应的脏页被刷到了磁盘，只要我们计算出当前系统中被最早修改的脏页对应的oldest_modification值，那凡是在系统lsn值小于该节点的oldest_modification值时产生的redo日志都是可以被覆盖掉的，我们就把该脏页的oldest_modification赋值给checkpoint_lsn。
• 步骤二：将checkpoint_lsn和对应的redo日志文件组偏移量以及此次checkpoint的编号写到日志文件的管理信息（就是checkpoint1或者checkpoint2）中。
• 设计InnoDB的大佬维护了一个目前系统做了多少次checkpoint的变量checkpoint_no，每做一次checkpoint，该变量的值就加1。我们前面说过计算一个lsn值对应的redo日志文件组偏移量是很容易的，所以可以计算得到该checkpoint_lsn在redo日志文件组中对应的偏移量checkpoint_offset，然后把这三个值都写到redo日志文件组的管理信息中。

每一个redo日志文件都有2048个字节的管理信息，但是上述关于checkpoint的信息只会被写到日志文件组的第一个日志文件的管理信息中。不过我们是存储到checkpoint1中还是checkpoint2中呢？设计InnoDB的大佬规定，当checkpoint_no的值是偶数时，就写到checkpoint1中，是奇数时，就写到checkpoint2中。

批量从flush链表中刷出脏页
Buffer Pool的时候说过，一般情况下都是后台的线程在对LRU链表和flush链表进行刷脏操作，这主要因为刷脏操作比较慢，不想影响用户线程处理请求。但是如果当前系统修改页面的操作十分频繁，这样就导致写日志操作十分频繁，系统lsn值增长过快。如果后台的刷脏操作不能将脏页刷出，那么系统无法及时做checkpoint，可能就需要用户线程同步的从flush链表中把那些最早修改的脏页（oldest_modification最小的脏页）刷新到磁盘，这样这些脏页对应的redo日志就没用了，然后就可以去做checkpoint了。

崩溃恢复

在服务器不挂的情况下，redo日志简直就是个大累赘，不仅没用，反而让性能变得更差。但是万一，我说万一啊，万一数据库挂了，那redo日志可是个宝了，看下恢复过程是什么样

确定恢复的起点

heckpoint_lsn之前的redo日志都可以被覆盖，也就是说这些redo日志对应的脏页都已经被刷新到磁盘中了，既然它们已经被刷盘，我们就没必要恢复它们了。对于checkpoint_lsn之后的redo日志，它们对应的脏页可能没被刷盘，也可能被刷盘了，我们不能确定，所以需要从checkpoint_lsn开始读取redo日志来恢复页面。
只要把checkpoint1和checkpoint2这两个block中的checkpoint_no值读出来比一下大小，哪个的checkpoint_no值更大，说明哪个block存储的就是最近的一次checkpoint信息。

确定恢复的终点

LOG_BLOCK_HDR_DATA_LEN的属性，该属性值记录了当前block里使用了多少字节的空间。对于被填满的block来说，该值永远为512。如果该属性的值不为512，那么就是它了，它就是此次奔溃恢复中需要扫描的最后一个block。

怎么恢复

确定了需要扫描哪些redo日志进行奔溃恢复之后，接下来就是怎么进行恢复了。

由于redo 0在checkpoint_lsn后边，恢复时可以不管它。我们现在可以按照redo日志的顺序依次扫描checkpoint_lsn之后的各条redo日志，按照日志中记载的内容将对应的页面恢复出来。这样没什么问题，不过设计InnoDB的大佬还是想了一些办法加快这个恢复的过程

• 使用哈希表
  根据redo日志的space ID和page number属性计算出散列值，把space ID和page number相同的redo日志放到哈希表的同一个槽里，如果有多个space ID和page number都相同的redo日志，那么它们之间使用链表连接起来，按照生成的先后顺序链接起来的，如图所示：
  
   之后就可以遍历哈希表，因为对同一个页面进行修改的redo日志都放在了一个槽里，所以可以一次性将一个页面修复好（避免了很多读取页面的随机IO），这样可以加快恢复速度。同一个页面的redo日志是按照生成时间顺序进行排序的，所以恢复的时候也是按照这个顺序进行恢复，如果不按照生成时间顺序进行排序的话，那么可能出现错误。
• 跳过已经刷新到磁盘的页面
  checkpoint_lsn之前的redo日志对应的脏页确定都已经刷到磁盘了，但是checkpoint_lsn之后的redo日志我们不能确定是否已经刷到磁盘，主要是因为在最近做的一次checkpoint后，可能后台线程又不断的从LRU链表和flush链表中将一些脏页刷出Buffer Pool。这些在checkpoint_lsn之后的redo日志，如果它们对应的脏页在奔溃发生时已经刷新到磁盘，那在恢复时也就没有必要根据redo日志的内容修改该页面了。
    那在恢复时怎么知道某个redo日志对应的脏页是否在奔溃发生时已经刷新到磁盘了呢？这还得从页面的结构说起，我们前面说过每个页面都有一个称之为File Header的部分，在File Header里有一个称之为FIL_PAGE_LSN的属性，该属性记载了最近一次修改页面时对应的lsn值（其实就是页面控制块中的newest_modification值）。如果在做了某次checkpoint之后有脏页被刷新到磁盘中，那么该页对应的FIL_PAGE_LSN代表的lsn值肯定大于checkpoint_lsn的值，凡是符合这种情况的页面就不需要重复执行lsn值小于FIL_PAGE_LSN的redo日志了，所以更进一步提升了奔溃恢复的速度。

Undo日志

undo log是实现原子性的关键，当事务回滚时会撤销所有已经执行完毕的sql语句，但是需要记录回滚的日志信息。

回滚：
情况一：事务执行过程中可能遇到各种错误，比如服务器本身的错误，操作系统错误，甚至是突然断电导致的错误。
情况二：程序员可以在事务执行过程中手动输入ROLLBACK语句结束当前的事务的执行。
这两种情况都会导致事务执行到一半就结束，但是事务执行过程中可能已经修改了很多东西，为了保证事务的原子性，我们需要把东西改回原先的样子

每当我们要对一条记录做改动时（这里的改动可以指INSERT、DELETE、UPDATE），都需要留一手 —— 把回滚时所需的东西都给记下来

比方说：

你插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉就好了。
你删除了一条记录，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了。
你修改了一条记录，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了。

设计数据库的大佬把这些为了回滚而记录的这些东东称之为撤销日志，英文名为undo log

undo日志的格式

为了实现事务的原子性，InnoDB存储引擎在实际进行增、删、改一条记录时，都需要先把对应的undo日志记下来。这些undo日志是被记录到类型为FIL_PAGE_UNDO_LOG。
 准备数据：

CREATE TABLE undo_demo (    id INT NOT NULL,    key1 VARCHAR(100),    col VARCHAR(100),    PRIMARY KEY (id),    KEY idx_key1 (key1))Engine=InnoDB CHARSET=utf8;

INSERT操作对应的undo日志

当我们向表中插入一条记录时会有乐观插入和悲观插入的区分，但是不管怎么插入，最终导致的结果就是这条记录被放到了一个数据页中。
如果希望回滚这个插入操作，那么把这条记录删除就好了，也就是说在写对应的undo日志时，主要是把这条记录的主键信息记上。
类型为TRX_UNDO_INSERT_REC的undo日志:

• undo no
  在一个事务中是从0开始递增的，也就是说只要事务没提交，每生成一条undo日志，那么该条日志的undo no就增1。
• 主键各列信息
  把该列占用的存储空间大小和真实值记录下来，如果记录中的主键包含多个列，那么每个列占用的存储空间大小和对应的真实值都需要记录下来
  示例：
  往表中插入数据：

BEGIN;  # 显式开启一个事务，假设该事务的id为100# 插入两条记录INSERT INTO undo_demo(id, key1, col)     VALUES (1, 'AWM', '狙击枪'), (2, 'M416', '步枪');

第一条undo日志的undo no为0，记录主键占用的存储空间长度为4，真实值为1

第二条undo日志的undo no为1，记录主键占用的存储空间长度为4，真实值为2

roll_pointer隐藏列的含义
本质上就是一个指向记录对应的undo日志的一个指针。
记录被存储到了类型为FIL_PAGE_INDEX的页面中（数据页），undo日志被存放到了类型为FIL_PAGE_UNDO_LOG的页面中。

trx_id隐藏列
 其中的trx_id列其实还蛮好理解的，就是某个对这个聚簇索引记录做改动的语句所在的事务对应的事务id而已（此处的改动可以是INSERT、DELETE、UPDATE操作）。
聚簇索引的记录除了会保存完整的用户数据以外，而且还会自动添加名为trx_id、roll_pointer的隐藏列，如果用户没有在表中定义主键以及UNIQUE键，还会自动添加一个名为row_id的隐藏列。
事务id是怎么生成的
事务id本质上就是一个数字，它的分配策略和我们前面提到的对隐藏列row_id（当用户没有为表创建主键和UNIQUE键时InnoDB自动创建的列）的分配策略大抵相同。

DELETE操作对应的undo日志

插入到页面中的记录会根据记录头信息中的next_record属性组成一个单向链表，我们把这个链表称之为正常记录链表；
被删除的记录其实也会根据记录头信息中的next_record属性组成一个链表，只不过这个链表中的记录占用的存储空间可以被重新利用，所以也称这个链表为垃圾链表；
Page Header部分有一个称之为PAGE_FREE的属性，它指向由被删除记录组成的垃圾链表中的头节点。

假设现在我们准备使用DELETE语句把正常记录链表中的最后一条记录给删除掉，其实这个删除的过程需要经历两个阶段：

• 阶段一：仅仅将记录的delete_mask标识位设置为1，其他的不做修改（其实会修改记录的trx_id、roll_pointer这些隐藏列的值）。设计InnoDB的大佬把这个阶段称之为delete mark。
  
  -阶段二：当该删除语句所在的事务提交之后，会有专门的线程后来真正的把记录删除掉。所谓真正的删除就是把该记录从正常记录链表中移除，并且加入到垃圾链表中，然后还要调整一些页面的其他信息，比如页面中的用户记录数量PAGE_N_RECS、上次插入记录的位置PAGE_LAST_INSERT、垃圾链表头节点的指针PAGE_FREE、页面中可重用的字节数量PAGE_GARBAGE、还有页目录的一些信息等等。设计InnoDB的大佬把这个阶段称之为purge。
  
  在删除语句所在的事务提交之前，只会经历阶段一，也就是delete mark阶段（提交之后我们就不用回滚了，所以只需考虑对删除操作的阶段一做的影响进行回滚）。

TRX_UNDO_DEL_MARK_REC类型的undo日志：

• old trx_id和old roll_pointer
  该记录的旧的trx_id和roll_pointer隐藏列的值都给记到对应的undo日志
  可以通过undo日志的old roll_pointer找到记录在修改之前对应的undo日志
  
• 索引列各列信息
  所谓的相关信息包括该列在记录中的位置（用pos表示），该列占用的存储空间大小（用len表示），该列实际值（用value表示）,所以索引列各列信息存储的内容实质上就是的一个列表。这部分信息主要是用在事务提交后，对该中间状态记录做真正删除的阶段二，也就是purge阶段中使用的
  示例：
  往表中删除数据：

BEGIN;  # 显式开启一个事务，假设该事务的id为100# 插入两条记录INSERT INTO undo_demo(id, key1, col)     VALUES (1, 'AWM', '狙击枪'), (2, 'M416', '步枪'); # 删除一条记录    DELETE FROM undo_demo WHERE id = 1; 

这个delete mark操作对应的undo日志的结构就是这样：

UPDATE操作对应的undo日志

在执行UPDATE语句时，InnoDB对更新主键和不更新主键这两种情况有截然不同的处理方案。

不更新主键的情况
在不更新主键的情况下，又可以细分为被更新的列占用的存储空间不发生变化和发生变化的情况。

• 就地更新（in-place update）
  更新记录时，对于被更新的每个列来说，如果更新后的列和更新前的列占用的存储空间都一样大，那么就可以进行就地更新，也就是直接在原记录的基础上修改对应列的值

UPDATE undo_demo     SET key1 = 'M249', col = '机枪'     WHERE id = 2;

由于各个被更新的列在更新前后占用的存储空间是一样大的，所以这样的语句可以执行就地更新

• 先删除掉旧记录，再插入新记录
   在不更新主键的情况下，如果有任何一个被更新的列更新前和更新后占用的存储空间大小不一致，那么就需要先把这条旧的记录从聚簇索引页面中删除掉，然后再根据更新后列的值创建一条新的记录插入到页面中。

针对UPDATE不更新主键的情况（包括上面所说的就地更新和先删除旧记录再插入新记录），设计InnoDB的大佬们设计了一种类型为TRX_UNDO_UPD_EXIST_REC的undo日志

BEGIN;  # 显式开启一个事务，假设该事务的id为100# 插入两条记录INSERT INTO undo_demo(id, key1, col)     VALUES (1, 'AWM', '狙击枪'), (2, 'M416', '步枪');    # 删除一条记录    DELETE FROM undo_demo WHERE id = 1; # 更新一条记录UPDATE undo_demo    SET key1 = 'M249', col = '机枪'    WHERE id = 2;

更新主键的情况
在聚簇索引中，记录是按照主键值的大小连成了一个单向链表的，如果我们更新了某条记录的主键值，意味着这条记录在聚簇索引中的位置将会发生改变.
针对UPDATE语句中更新了记录主键值的这种情况，InnoDB在聚簇索引中分了两步处理：

• 将旧记录进行delete mark操作(还没经过perge操作，不是真正的删除，事务提交才会有专门的线程镜像purge操作，加入垃圾链表中)
• 根据更新后各列的值创建一条新记录，并将其插入到聚簇索引中（需重新定位插入的位置）。

FIL_PAGE_UNDO_LOG页面

表空间其实是由许许多多的页面构成的，页面默认大小为16KB。FIL_PAGE_UNDO_LOG类型的页面是专门用来存储undo日志的，这种类型的页面的通用结构如下图所示：

Undo Page Header是Undo页面所特有的，我们来看一下它的结构：

• TRX_UNDO_PAGE_TYPE
  本页面存储什么种类的undo日志，可以是TRX_UNDO_INSERT（一般由INSERT语句产生，或者在UPDATE语句中有更新主键的情况也会产生此类型的undo日志。）和TRX_UNDO_UPDATE（一般由DELETE、UPDATE语句产生的undo日志属于这个大类）

• TRX_UNDO_PAGE_START
  表示在当前页面中是从什么位置开始存储undo日志的，或者说表示第一条undo日志在本页面中的起始偏移量。

• TRX_UNDO_PAGE_FREE
  与上面的TRX_UNDO_PAGE_START对应，表示当前页面中存储的最后一条undo日志结束时的偏移量，或者说从这个位置开始，可以继续写入新的undo日志。
  

• TRX_UNDO_PAGE_NODE
  代表一个List Node结构

Undo页面链表

单个事务中的Undo页面链表
在一个事务执行过程中，可能混着执行INSERT、DELETE、UPDATE语句，也就意味着会产生不同类型的undo日志。
同一个Undo页面要么只存储TRX_UNDO_INSERT大类的undo日志，要么只存储TRX_UNDO_UPDATE大类的undo日志，反正不能混着存，所以在一个事务执行过程中就可能需要2个Undo页面的链表，一个称之为insert undo链表，另一个称之为update undo链表

设计InnoDB的大佬规定对普通表和临时表的记录改动时产生的undo日志要分别记录（我们稍后阐释为什么这么做），所以在一个事务中最多有4个以Undo页面为节点组成的链表：

多个事务中的Undo页面链表

为了尽可能提高undo日志的写入效率，不同事务执行过程中产生的undo日志需要被写入到不同的Undo页面链表中。

• trx 1对普通表做了DELETE操作，对临时表做了INSERT和UPDATE操作。
  InnoDB会为trx 1分配3个链表，分别是：
  针对普通表的update undo链表
  针对临时表的insert undo链表
  针对临时表的update undo链表。
• trx 2对普通表做了INSERT、UPDATE和DELETE操作，没有对临时表做改动。
  InnoDB会为trx 2分配2个链表，分别是：
  针对普通表的insert undo链表
  针对普通表的update undo链表。

综上所述，在trx 1和trx 2执行过程中，InnoDB共需为这两个事务分配5个Undo页面链表，画个图就是这样：

undo日志具体写入过程

事务隔离级别于MVVC

准备数据：

CREATE TABLE hero (    number INT,    name VARCHAR(100),    country varchar(100),    PRIMARY KEY (number)) Engine=InnoDB CHARSET=utf8;INSERT INTO hero VALUES(1, '刘备', '蜀');

事务并发执行遇到的问题

脏写（Dirty Write）

如果一个事务修改了另一个未提交事务修改过的数据，那就意味着发生了脏写。

Session A和Session B各开启了一个事务，Session B中的事务先将number列为1的记录的name列更新为’关羽’，然后Session A中的事务接着又把这条number列为1的记录的name列更新为张飞。如果之后Session B中的事务进行了回滚，那么Session A中的更新也将不复存在，这种现象就称之为脏写。

脏读（Dirty Read）

一个事务读到了另一个未提交事务修改过的数据
Session A和Session B各开启了一个事务，Session B中的事务先将number列为1的记录的name列更新为’关羽’，然后Session A中的事务再去查询这条number为1的记录，如果du到列name的值为’关羽’，而Session B中的事务稍后进行了回滚，那么Session A中的事务相当于读到了一个不存在的数据，这种现象就称之为脏读。

不可重复读（Non-Repeatable Read）

一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值

在Session B中提交了几个隐式事务（注意是隐式事务，意味着语句结束事务就提交了），这些事务都修改了number列为1的记录的列name的值，每次事务提交之后，如果Session A中的事务都可以查看到最新的值，这种现象也被称之为不可重复读。

幻读（Phantom）

一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来

Session A中的事务先根据条件number > 0这个条件查询表hero，得到了name列值为’刘备’的记录；之后Session B中提交了一个隐式事务，该事务向表hero中插入了一条新记录；之后Session A中的事务再根据相同的条件number > 0查询表hero，得到的结果集中包含Session B中的事务新插入的那条记录，这种现象也被称之为幻读。

这些问题按照严重性来排一下序：
脏写 > 脏读 > 不可重复读 > 幻读

事务隔离级别

设立一些隔离级别，隔离级别越低，越严重的问题就越可能发生。SQL标准设立了4个隔离级别：

隔离级别	脏写	脏读	不可重复读	幻读
READ UNCOMMITTED（未提交读）	✔	×	×	×
READ COMMITTED（已提交读）	✔	✔	×	×
REPEATABLE READ （可重复读）	✔	✔	✔	×
SERIALIZABLE（可串行化）	✔	✔	✔	✔
如何设置事务的隔离级别

# 修改事务的隔离级别SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;# 查看事务的隔离级别SELECT @@GLOBAL.transaction_isolation;SELECT @@SESSION.transaction_isolation;

使用GLOBAL关键字（在全局范围影响）：

• 只对执行完该语句之后产生的会话起作用。
• 当前已经存在的会话无效。
  使用SESSION关键字（在会话范围影响）：
• 对当前会话的所有后续的事务有效
• 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务。
• 如果在事务之间执行，则对后续的事务有效。

MVCC原理

版本链

对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列（row_id并不是必要的，我们创建的表中有主键或者非NULL的UNIQUE键时都不会包含row_id列）：

• trx_id：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务id赋值给trx_id隐藏列。
• roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。
  还是以表herow为例
   假设插入该记录的事务id为80，那么此刻该条记录的示意图如下所示：
  

假设之后两个事务id分别为100、200的事务对这条记录进行UPDATE操作，操作流程如下：

每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表，所以现在的情况就像下图一样

该记录每次更新后，都会将旧值放到一条undo日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。每个版本中还包含生成该版本时对应的事务id，这个信息很重要，我们稍后就会用到。

ReadView

对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录，也就是说假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。

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

• m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
• min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。
• max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。
• creator_trx_id：表示生成该ReadView的事务的事务id。

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

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

READ COMMITTED —— 每次读取数据前都生成一个ReadView

示例演示：

现在系统里有两个事务id分别为100、200的事务在执行：

# Transaction 100BEGIN;UPDATE hero SET name = '关羽' WHERE number = 1;UPDATE hero SET name = '张飞' WHERE number = 1;

# Transaction 200BEGIN;# 更新了一些别的表的记录...

此刻，表hero中number为1的记录得到的版本链表如下所示

第三个事务，假设事务id是 0,使用READ COMMITTED隔离级别查找这个number为1的记录

# 使用READ COMMITTED隔离级别的事务BEGIN;# SELECT1：Transaction 100、200均未提交SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备'# SELECT2：Transaction 100提交，Transaction 200未提交SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'张飞'

SELECT1的执行过程

• 在执行SELECT语句时会先生成一个ReadView，ReadView的m_ids列表的内容就是[100, 200]，min_trx_id为100，max_trx_id为201，creator_trx_id为0。
• 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是’张飞’，该版本的trx_id值为100，在m_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本。
• 下一个版本的列name的内容是’关羽’，该版本的trx_id值也为100，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。
• 下一个版本的列name的内容是’刘备’，该版本的trx_id值为80，小于ReadView中的min_trx_id值100，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为’刘备’的记录。

SELECT1执行完成后，Transaction 100 提交事务，Transaction 200新增操作

# Transaction 200BEGIN;# 更新了一些别的表的记录...UPDATE hero SET name = '赵云' WHERE number = 1;UPDATE hero SET name = '诸葛亮' WHERE number = 1;

表hero中number为1的记录的版本链：

SELECT2的执行过程

• 在执行SELECT语句时会又会单独生成一个ReadView，该ReadView的m_ids列表的内容就是[200]（事务id为100的那个事务已经提交了，所以再次生成快照时就没有它了），min_trx_id为200，max_trx_id为201，creator_trx_id为0。
• 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是’诸葛亮’，该版本的trx_id值为200，在m_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本。
• 下一个版本的列name的内容是’赵云’，该版本的trx_id值为200，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。
• 下一个版本的列name的内容是’张飞’，该版本的trx_id值为100，小于ReadView中的min_trx_id值200，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为’张飞’的记录。

使用READ COMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的ReadView。同一个事务中前后查询同一条记录看到的数据可能不一样。
REPEATABLE READ —— 在第一次读取数据时生成一个ReadView

示例演示：

现在系统里有两个事务id分别为100、200的事务在执行：

# Transaction 100BEGIN;UPDATE hero SET name = '关羽' WHERE number = 1;UPDATE hero SET name = '张飞' WHERE number = 1;

# Transaction 200BEGIN;# 更新了一些别的表的记录...

此刻，表hero中number为1的记录得到的版本链表如下所示

第三个事务，事务id是 0,使用REPEATABLE READ隔离级别查找这个number为1的记录

# 使用REPEATABLE READ隔离级别的事务BEGIN;# SELECT1：Transaction 100、200均未提交SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备'# SELECT2：Transaction 100提交，Transaction 200未提交SELECT * FROM hero WHERE number = 1; # 得到的列name的值仍为'刘备'

SELECT1的执行过程:

• 在执行SELECT语句时会先生成一个ReadView，ReadView的m_ids列表的内容就是[100, 200]，min_trx_id为100，max_trx_id为201，creator_trx_id为0。
• 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是’张飞’，该版本的trx_id值为100，在m_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本。
• 下一个版本的列name的内容是’关羽’，该版本的trx_id值也为100，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。
• 下一个版本的列name的内容是’刘备’，该版本的trx_id值为80，小于ReadView中的min_trx_id值100，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为’刘备’的记录。
  SELECT1执行完成后，Transaction 100 提交事务，Transaction 200新增操作

# Transaction 200BEGIN;# 更新了一些别的表的记录...UPDATE hero SET name = '赵云' WHERE number = 1;UPDATE hero SET name = '诸葛亮' WHERE number = 1;

此刻，表hero中number为1的记录得到的版本链表如下所示：

• 因为当前事务的隔离级别为REPEATABLE READ，而之前在执行SELECT1时已经生成过ReadView了，所以此时直接复用之前的ReadView，之前的ReadView的m_ids列表的内容就是[100, 200]，min_trx_id为100，max_trx_id为201，creator_trx_id为0。
• 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是’诸葛亮’，该版本的trx_id值为200，在m_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本。
• 下一个版本的列name的内容是’赵云’，该版本的trx_id值为200，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。
• 下一个版本的列name的内容是’张飞’，该版本的trx_id值为100，而m_ids列表中是包含值为100的事务id的，所以该版本也不符合要求，同理下一个列name的内容是’关羽’的版本也不符合要求。继续跳到下一个版本。
• 下一个版本的列name的内容是’刘备’，该版本的trx_id值为80，小于ReadView中的min_trx_id值100，所以这个版本是符合要求的，最后返回给用户的版本就是这条列c为’刘备’的记录。

使用REPEATABLE READ隔离级别的事务在第一次查询开始时才生成一个独立的ReadView。同一个事务中前后查询同一条记录看到的数据是一样。

MySQL中的锁

解决并发事务带来问题的两种基本方式

并发事务访问相同记录的情况大致可以划分为3种

• 读-读情况：即并发事务相继读取相同的记录。
  读操作不对记录产生任何影响，允许发生。
• 写-写情况：即并发事务相继对相同的记录做出改动。
  在这种情况下会发生脏写的问题，任何一种隔离级别都不允许这种问题的发生。
  在多个未提交事务相继对一条记录做改动时，需要让它们排队执行，这个排队的过程其实是通过锁来实现的。
• 读-写或写-读情况：也就是一个事务进行读取操作，另一个进行改动操作。
  可能发生脏读、不可重复读、幻读的问题

解决脏读、不可重复读、幻读这些问题呢,有两种可选的解决方案：

• 方案一：读记录的历史版本和改动记录的最新版本本身并不冲突,读操作利用多版本并发控制（MVCC），写操作进行加锁。

• 方案二：读记录的历史版本和改动记录的最新版本本身冲突,这样也就意味着读操作和写操作也像写-写操作那样排队执行,读、写操作都采用加锁的方式。
  比方在银行存款的事务中，你需要先把账户的余额读出来，然后将其加上本次存款的数额，最后再写到数据库中。在将账户余额读取出来后，就不想让别的事务再访问该余额，直到本次存款事务执行完成，其他事务才可以访问账户的余额。

一致性读（Consistent Reads）

事务利用MVCC进行的读取操作称之为一致性读，或者一致性无锁读，有的地方也称之为快照读。
所有普通的SELECT语句（plain SELECT）在READ COMMITTED、REPEATABLE READ隔离级别下都算是一致性读
示例：

SELECT * FROM t;SELECT * FROM t1 INNER JOIN t2 ON t1.col1 = t2.col2

一致性读并不会对表中的任何记录做加锁操作，其他事务可以自由的对表中的记录做改动。

锁定读（Locking Reads）

共享锁和独占锁

要使写-写、读-写或写-读情况中的操作相互阻塞,MySQL给锁分了个类：
共享锁，英文名：Shared Locks，简称S锁。在事务要读取一条记录时，需要先获取该记录的S锁。
独占锁，也常称排他锁，英文名：Exclusive Locks，简称X锁。在事务要改动一条记录时，需要先获取该记录的X锁。

锁定读的语句

• 对读取的记录加S锁：

SELECT ... LOCK IN SHARE MODE;

SELECT语句后边加LOCK IN SHARE MODE，如果当前事务执行了该语句，那么它会为读取到的记录加S锁，这样允许别的事务继续获取这些记录的S锁（比方说别的事务也使用SELECT … LOCK IN SHARE MODE语句来读取这些记录），但是不能获取这些记录的X锁（比方说使用SELECT … FOR UPDATE语句来读取这些记录，或者直接修改这些记录）。如果别的事务想要获取这些记录的X锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的S锁释放掉。

• 对读取的记录加X锁：

SELECT ... FOR UPDATE;

普通的SELECT语句后边加FOR UPDATE，如果当前事务执行了该语句，那么它会为读取到的记录加X锁，这样既不允许别的事务获取这些记录的S锁（比方说别的事务使用SELECT … LOCK IN SHARE MODE语句来读取这些记录），也不允许获取这些记录的X锁（比方也说使用SELECT … FOR UPDATE语句来读取这些记录，或者直接修改这些记录）。如果别的事务想要获取这些记录的S锁或者X锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的X锁释放掉。

写操作

写操作无非是DELETE、UPDATE、INSERT这三种：

• DELETE：
  对一条记录做DELETE操作的过程其实是先在B+树中定位到这条记录的位置，然后获取一下这条记录的X锁，然后再执行delete mark操作。我们也可以把这个定位待删除记录在B+树中位置的过程看成是一个获取X锁的锁定读。

• UPDATE：
  1.未修改该记录的键值并且被更新的列占用的存储空间在修改前后未发生变化，则先在B+树中定位到这条记录的位置，然后再获取一下记录的X锁，最后在原记录的位置进行修改操作。其实我们也可以把这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读。
  2.未修改该记录的键值并且至少有一个被更新的列占用的存储空间在修改前后发生变化，则先在B+树中定位到这条记录的位置，然后获取一下记录的X锁，将该记录彻底删除掉（就是把记录彻底移入垃圾链表），最后再插入一条新记录。这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读，新插入的记录由INSERT操作提供的隐式锁进行保护。
  3.修改了该记录的键值，则相当于在原记录上做DELETE操作之后再来一次INSERT操作，加锁操作就需要按照DELETE和INSERT的规则进行了

• INSERT：

一般情况下，新插入一条记录的操作并不加锁，设计InnoDB的大佬通过一种称之为隐式锁的东东来保护这条新插入的记录在本事务提交前不被别的事务访问

多粒度锁

提到的锁都是针对记录的，也可以被称之为行级锁或者行锁，对一条记录加锁影响的也只是这条记录而已，我们就说这个锁的粒度比较细；其实一个事务也可以在表级别进行加锁，自然就被称之为表级锁或者表锁，对一个表加锁影响整个表中的记录，我们就说这个锁的粒度比较粗。给表加的锁也可以分为共享锁（S锁）和独占锁（X锁）：

IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录，也就是说其实IS锁和IX锁是兼容的，IX锁和IX锁是兼容的。

MySQL中的行锁和表锁

其他存储引擎中的锁

对于MyISAM、MEMORY、MERGE这些存储引擎来说，它们只支持表级锁，而且这些引擎并不支持事务，所以使用这些存储引擎的锁一般都是针对当前会话来说的。
 示例：
 在使用以上存储引擎的会话场景：
 会话1，对表执行select操作，获取到表级别的S锁，会话2，对表执行update操作，要获取表的X锁，此时会话2会被阻塞，直到会话1释放S锁。

InnoDB存储引擎中的锁

InnoDB中的表级锁

• 表级别的S锁、X锁
  在对某个表执行 SELECT、INSERT、DELETE、UPDATE 语句时，InnoDB 存储引擎是不会为这个表添加表级别的 S锁 或者 X锁 的。
  在系统变量 autocommit=0，innodb_table_locks = 1 时，手动获取 InnoDB 存储引擎提供的表级别的 S锁 或者 X锁 方法如下

#  InnoDB存储引擎会对表t加表级别的S锁LOCK TABLES t READ；# InnoDB存储引擎会对表t加表级别的X锁LOCK TABLES t WRITE;

• 表级别的IS锁、IX锁
  InnoDB存储引擎的表某些记录加S锁之前，会给表级别加个IS锁；
  InnoDB存储引擎的表某些记录加X锁之前，会给表级别加个IX锁；
  IS锁和IX锁的作用就是判断表中是后已经有加锁的记录。
• 表级别的AUTO-INC锁
  表中字段有AUTO_INCREWMENT属性，之后插入记录，该列可以不指定值，系统会自动为它赋上递增的值。
  系统实现自动给AUTO_INCREWMENT修饰的列递增赋值的原理有两个：
  1.采用AUTO-INC锁
  在执行插入语句时在表级别加一个AUTO-INC锁，然后再给该列分配递增的值，执行完成后，再把AUTO_INC锁释放掉。
  使用场景：无法预计插入记录的数量，如使用insert…select插入语句。
  2.采用轻量级锁
  在执行插入语句前，为插入语句生成该列的值时获取一下这个轻量级锁，生成完成后，就把锁释放。
  使用场景：可以预计插入记录的数量，如insert…into语句
• 元数据锁（MDL ）
  MySQL 中，在 server 层使用提供了「元数据锁（Metadata Locks，简称 MDL）」，用于下述场景
  一个事务 T1 对表执行诸如 ALTER TABLE、DROP TABLE 这类的 DDL 语句(对元数据锁加写锁)
  另一个事务 T2 对表执行SELECT、INSERT、DELETE、UPDATE 语句(对元数据锁加读锁)
  事务 T1 和事务 T2 若并发执行，会发生阻塞，这个阻塞就是通过元数据锁实现的
  MDL 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。MDL 读写锁之间、MDL写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

InnoDB中的行级锁

• 记录锁
  记录锁（Record Lock），官方的类型名称为 LOCK_REC_NOT_GAP，是单个记录上的锁。
  记录锁有S锁和X锁之分。

• 间隙锁
  间隙锁（Gap Lock）仅仅是为了防止插入幻影记录而提出的。对一条记录加了 GAP 锁（不论是共享 GAP 锁还是独占 GAP 锁），并不会限制其他事务对这条记录加记录锁或者继续加 GAP 锁。

• 临键锁
  临键锁（Next-Key Lock），官方的类型名称为 LOCK_ORDINARY，即 Gap Lock + Record Lock）
  临键锁的本质就是一个记录锁和一个间隙锁的合体（Gap Lock + Record Lock），它既能保护该条记录，又能阻止别的事务将新记录插入被保护记录前边的间隙。

• 插入意向锁
  一个事务在执行 INSERT 操作时，如果即将插入的间隙已经被其他事务加了 GAP 锁，那么本次 INSERT 操作会阻塞，并且当前事务会在该间隙上加一个插入意向锁（Insert Intention Locks）
  向某条记录添加插入意向锁后，不会阻止别的事务继续获取该记录上任何类型的锁。

• 隐式锁
  一个事务在执行 INSERT 操作时，如果即将插入的间隙已经被其他事务加了 GAP 锁，那么本次 INSERT 操作会阻塞，并且当前事务会在该间隙上加一个插入意向锁。否则，一般情况下 INSERT 操作是不加锁的。
  那如果一个事务首先插入了一条记录（此时并没有与该记录关联的锁结构），然后另一个事务

立即修改这条记录，这会造成「脏写」问题
立即读这条记录（具体包括下面两种场景），这会造成「脏读」问题

立即使用 SELECT … LOCK IN SHARE MODE 语句读取这条记录，也就是在要获取这条记录的 S锁
立即使用 SELECT … FOR UPDATE 语句读取这条记录，也就是要获取这条记录的 X锁

如何避免上述问题呢？这个时候可以用到「事务ID」来解决该问题

情景一：对于聚簇索引记录来说，有一个 trx_id 隐藏列，该隐藏列记录着最后改动该记录的事务ID

在当前事务中新插入一条聚簇索引记录后，该记录的 trx_id 隐藏列代表的就是当前事务的事务ID
如果其他事务此时想对该记录添加S锁或者X锁时，首先会看一下该记录的 trx_id 隐藏列代表的事务是否是当前的活跃事务
如果是的话，那么就帮助当前事务创建一个X锁（也就是为当前事务创建一个锁结构，is_waiting 属性是 false），然后自己进入等待状态（也就是为自己也创建一个锁结构，is_waiting 属性是 true）

情景2：对于二级索引记录来说，本身并没有 trx_id 隐藏列，但是在二级索引页面的 Page Header 部分有一个 PAGE_MAX_TRX_ID 属性，该属性代表对该页面做改动的最大的事务ID

如果 PAGE_MAX_TRX_ID 属性值小于当前最小的活跃事务ID，那么说明对该页面做修改的事务都已经提交了
否则，就需要在页面中定位到对应的二级索引记录，然后「回表」找到它对应的聚簇索引记录，然后再重复情景一的做法。

InnoDB锁的内存结构

来源地址：https://blog.csdn.net/qq_44788518/article/details/130792213