Mysql运行的整体架构简介 mysql是由两部分构成，一部分是服务器程序，一部分是客户端程序。 服务器程序又包括两部分： 第一部分server层包括连接器、查询缓存、分析器、优化器、执行器等。涵盖 Mysql 的大多数核心服务功能，以及
mysql是由两部分构成，一部分是服务器程序，一部分是客户端程序。
服务器程序又包括两部分：
第一部分server层包括连接器、查询缓存、分析器、优化器、执行器等。涵盖 Mysql 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等
第二部分是存储引擎层负责数据的存储和提取。存储引擎有多种选择，主要有InnoDB、MyISAM、Memory等。
要操作Mysql数据库，首先客户端要连接上mysql服务器程序。
连接器：** 负责跟客户端建立连接、获取权限、维持和管理连接。
连接命令：
mysql -h$ip -P$port -u$user -pMySQL采用的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存储引擎众多优点，支持索引，事务以及事务回滚等，成为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 '%表名%' ;
一条完整的记录其实可以被分为记录的额外信息和记录的真实数据两大部分
记录的额外信息
变长字段长度列表
存放所有变长字段的真实数据占用的字节长度，每个可变长字段的对应的长度按照列的顺序逆序存放；
变长字段中存储多少字节的数据是不固定的，故需要记录变长字段的真实数据占用的字节长度。
变长字段类型包括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) 类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表。
与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值标识标识占的一字节，真实数据还可使用65532字节。
utf8mb4字符集表示一个字符最多需要4个字节，那在该字符集下，M的最大取值就是16,383，就是说最多能存储16,383（也就是：65532/4）个字符。
行溢出处理
MySQL中磁盘和内存交互的基本单位是页，以页为基本单位来管理存储空间的，我们的记录都会被分配到某个页中存储。一个页的大小是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65532个字节，会出现一个页存放不了一条记录情况。
对于Compact和Reduntant行格式来说，如果某一列中的数据非常多的话，在本记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中，这个过程也叫做行溢出，存储超出768字节的那些页面也被称为溢出页。
Dynamic和Compressed行格式，这俩行格式和Compact行格式类似，在处理行溢出数据时有不同，它们不会在记录的真实数据处存储字段真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。
Compressed行格式和Dynamic不同的一点是，Compressed行格式会采用压缩算法对页面进行压缩，以节省空间。
页是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');
页中记录按照主键从小到大的顺序形成了一个单链表,通过next_record作为引用找到下一个节点记录，页中维护了两个初始节点记录，Infimum记录（也就是最小记录） 的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是 Supremum记录（也就是最大记录）,record_type区分用户记录（0或1）、最小记录（2）和最大记录（3）。min_rec_mask只有B+树的每层非叶子节点中的最小记录是1，其他记录都是0。heap_no标识记录位置，最小记录为0，依次是用户记录递增，最后是最大记录。
就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了。
Page Directory（页目录）为了快速在页中查找某条记录。
页目录构建规则：
将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。对每个分组中的记录条数是有规定的：对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。
每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录。
将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近页的尾部的地方，这个地方就是所谓的Page Directory，也就是页目录（此时应该返回头看看页面各个部分的图）。页面目录中的这些地址偏移量被称为槽（英文名：Slot），所以这个页面目录就是由槽组成的。
页中查找页的过程
通过二分法确定该记录所在的槽，并找到该槽中主键值最小的那条记录。
通过记录的next_record属性遍历该槽所在的组中的各个记录。
专门针对数据页记录的各种状态信息，记录的信息包括本页中已经存储了多少条记录，第一条记录的地址是什么，页目录中存储了多少个槽等等，这个部分占用固定的56个字节。
PAGE_DIRECTION
用来表示最后一条记录插入方向的状态，假如新插入的一条记录的主键值比上一条记录的主键值大，我们说这条记录的插入方向是右边，反之则是左边。
PAGE_N_DIRECTION
表示最后插入记录的方向的连续数量，最后插入方向与上一次插入方向不同，清零重新计算。
File Header针对各种类型的页都通用，它描述了一些针对各种页都通用的一些信息。比方说这个页的编号是多少，它的上一个页、下一个页是谁等， 这个部分占用固定的38个字节。
可以分成2个小部分：
为了更方便查找记录，我们把数据页存放到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+树只有一层，也就是只有根节点，该节点也就是数据页（也就是页中查找页的过程），查找过程：
如果B+树有两层及以上，只有最底层的节点类型是数据页，其他层的节点类型是目录页，查找过程：
得到
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，既一千亿条数据。
所有完整的用户记录都存放在这个聚簇索引的叶子节点处，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。
它有两个特点：
1.使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
页内的记录是按照主键的大小顺序排成一个单向链表。
各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
B+树的叶子节点存储的是完整的用户记录。
在非聚簇索引的叶子节点上存储的并不是真正的行数据，而是主键 +当前索引列。
按字段特性分类可分为：唯一索引、普通索引、前缀索引。
唯一索引
建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，列值中出现多个空值不会发生重复冲突。
普通索引
建立在普通字段上的索引被称为普通索引。
前缀索引
前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引，而不是在整个字段上建索引。前缀索引可以建立在类型为char、varchar、binary、varbinary的列上，可以大大减少索引占用的存储空间，也能提升索引的查询效率。
按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）。
单列索引
建立在单个列上的索引被称为单列索引。
联合索引（复合索引、组合索引）
建立在多个列上的索引被称为联合索引，又叫复合索引、组合索引。
回表
在非聚簇索引中查找到的最终结果是——主键 +当前索引列，当前索引列可能无法包含select的数据列（select的数据列能直接从二级索引中取得，称为覆盖索引）还需拿着主键去聚簇索引中再进行查询。聚簇索引中才包含
索引是个好东西，可不能乱建。一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。
准备数据：
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+树索引进行排序是一个道理，分组列的顺序也需要和索引列的顺序一致，也可以只使用索引列中左边的列进行分组
在使用索引时需要注意下面这些事项：
区的概念——连续的64个页就是一个区。
不管独立系统表空间还是独立表空间，都可以看成是由若干个区组成的。
每256个区又分一组。
**引入区的原因：**进行范围查找的时候，利用B+树直接定位到最左边记录和最右边记录，然后沿着页之间的双向链表，页内行之间的单向链表一直扫描，如果页之前的距离非常远，就会有随机I/O,这是非常慢的。区在物理位置上是连续的64页，这样在同一个区中查找就是顺序I/O,是非常快的。表中数据非常多时，甚至一次性分配多个物理位置上连续的区。
段的概念——段不对应表空间中某一个连续的物理空间，而是一个逻辑上的概念，由若干个零散的页以及一些完整的区组成。
考虑以完整的区为单位分配给某个段对于数据量较小的表太浪费存储空间的这种情况，InnoDB提出了一个碎片（fragment）区的概念。也就是在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页用于段A，有些页用于段B，有些页甚至哪个段都不属于。碎片区直属于表空间，并不属于任何一个段。所以此后为某个段分配存储空间的策略是这样的：
引入段的原因： 范围查找时，不区分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结构，这个结构记录了对应的区的一些属性。
XDES Entry链表
为了快速定位未使用的页用来插入数据。InnoDB给每个段中的区对应的XDES Entry结构建立了三个链表，通过List Node作为指针（这三个链表上的区都是直属某个段，既区的类型是FSEG（附属某个段的区））：
段中数据较少的时候，首先会查看表空间中是否有状态为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结构来记录一下段中的属性。
FSP_HDR页，是第一个组的第一个页，也是表空间的第一个页
重点来看看File Space Header和XDES Entry这两个部分（其它部分在页结构都介绍过）；
File Space Header部分
XDES Entry部分
256个区划分成一组，在每组的第一个页中存放256个XDES Entry结构，每个XDES Entry记录了对应的区的一些属性。结构已经介绍了。
这种类型的页里边记录了一些有关Change Buffer，后面再详细看。
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;在表中查找目标数据的过程，即是访问方法。
查询的执行方式大致分为两种：
相对全表扫描，使用索引可以加快查询执行时间。利用索引查找的种类有：
const
根据主键、普通唯一索引列等值匹配查询（is null除外），这种查询是很快的，查询速率认为是常数级别的，定义为const。
SELECT * FROM single_table WHERE id = 1438;
SELECT * FROM single_table WHERE key2 = 3841;
根据普通的二级索引等值匹配，或is null。（前面说的普通唯一索引列查询时 is null也是这种场景）。这种方式需要先根据普通索引匹配到多个主键，然后根据主键进行回表。
SELECT * FROM single_table WHERE key1 = 'abc';
根据普通的二级索引等值匹配并且条件里有or is null。
SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;
根据主键索引或普通索引（包含唯一索引）进行范围查找
SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);索引覆盖，你查询的列刚好是索引列，即使查询条件是联合索引的非最左索引列，查询的条件是联合索引中的列，也可能会走索引覆盖
SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';全表扫描，直接扫描主键索引，这种访问方式称为all。
除此之外，还会有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 
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 前的表作为驱动表。
嵌套循环连接过程
两个步骤都能通过索引进行优化查询，加快连接速度。
基于块的嵌套循环连接
采用嵌套循环连接算法的两表连接过程中，被驱动表可是要被访问好多次的，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表，这个I/O代价就非常大了，所以我们得想办法：尽量减少访问被驱动表的次数。
MySQL中引入join buffer，它是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价
对于使用InnoDB作为存储引擎的表来说，不管是用于存储用户数据的索引（包括聚簇索引和二级索引），还是各种系统数据，都是以页的形式存放在表空间中的，而所谓的表空间只不过是InnoDB对文件系统上一个或几个实际文件的抽象，也就是说我们的数据说到底还是存储在磁盘上的。
InnoDB存储引擎在处理客户端的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中，也就是说即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中。
InnoDB为了缓存磁盘中的页，在MySQL服务器启动的时候就向操作系统申请了一片连续的内存——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中。
当执行的sql是修改语句，修改了Buffer Pool中某个缓存页的数据，那它就和磁盘上的页不一致了，这样的缓存页也被称为脏页（英文名：dirty page）。flush链表记录了需要被刷新到磁盘上的缓存页，链表上的节点是修改过的缓存页对应的控制块。
Buffer Pool对应的内存大小毕竟是有限的，如果需要缓存的页占用的内存大小超过了Buffer Pool大小，也就是free链表中已经没有多余的空闲缓存页的时候岂不是很尴尬。
简单的LRU链表
LRU全称Least Recently Used(最近最少使用),LRU链表按照最近最少使用的原则去淘汰缓存页。
处理过程：
划分区域的LRU链表
简单的LRU链表在两种场景下存在问题：
预读本来是个好事儿，如果预读到Buffer Pool中的页成功的被使用到，那就可以极大的提高语句执行的效率。可是如果用不到呢？这些预读的页都会放到LRU链表的头部，但是如果此时Buffer Pool的容量不太大而且很多预读的页面都没有用到的话，这就会导致处在LRU链表尾部的一些缓存页会很快的被淘汰掉，会大大降低缓存命中率。
问题总结：
1.加载到Buffer Pool中的页不一定被用到。
2.如果非常多的使用频率偏低的页被同时加载到Buffer Pool时，可能会把那些使用频率非常高的页从Buffer Pool中淘汰掉。
问题解决：
引入划分区域的LRU链表，LRU链表按照一定比例分成两截
更进一步优化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本质是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详细描述见书上。
事务是一个抽象的概念，它其实对应着一个或多个数据库操作。
根据这些操作所执行的不同阶段把事务以下状态：
MySQL如何保证ACID?
后面会一个个介绍。
两种语句之一来开启一个事务
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语句时可以指定会滚到哪个点，而不是回到最初的原点
事务一组数据库操作sql执行后，首先只会更新Buffer Pool中的页面，并未更新到磁盘上。
假设在事务提交后突然发生了某个故障，导致内存中的数据都失效了，那么这个已经提交了的事务对数据库中所做的更改也就跟着丢失了。
直接更新到磁盘中存在的问题：
那如何保证事务持久性？引入redo日志
redo日志的功能就是在每次事务提交时，修改项记录永久记录在磁盘中，系统奔溃重启时需要按照所记录的步骤重新更新数据页。
redo日志的优点：
redo日志本质上就是把事务在执行过程中对数据库所做的所有修改都记录下来，在之后系统奔溃重启后可以把事务所做的任何修改都恢复出来。
通用结构
绝大部分类型的redo日志都有下面这种通用的结构：
简单的redo日志类型
只需要记录一下在某个页面的某个偏移量处修改了几个字节的值，具体被修改的内容是什么就好了，仅仅包含物理层面的含义。
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的redo日志的结构
这些类型的redo日志既包含物理层面的意思，也包含逻辑层面的意思，具体指：
语句在执行过程中可能修改若干个页面。上面示例对于row_id的隐藏列的更新，可能修改系统表空间页号为7的页面的Max Row ID属性（当然也可能更新别的系统页面，只不过我们没有都列举出来而已），还会更新聚簇索引和二级索引对应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日志
InnoDB把通过mtr生成的redo日志都放在了大小为512字节的页中。这种页简称为block。
block分为三个部分，log block header和log block trailer存储的是一些管理信息，log block body存储真正的redo日志。
log block header中属性：
log block trailer中属性
redo日志时也不能直接直接写到磁盘上，实际上在服务器启动时就向操作系统申请了一大片称之为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。
每个mtr都会产生一组redo日志，用示意图来描述一下这些mtr产生的日志情况：
mtr运行过程中产生的一组redo日志在mtr结束时会被复制到log buffer中，可是这些日志在内存里并未持久化，在一些情况下它们会被刷新到磁盘里：
MySQL的数据目录（使用SHOW VARIABLES LIKE 'datadir’查看）下默认有两个名为ib_logfile0和ib_logfile1的文件，log buffer中的日志默认情况下就是刷新到这两个磁盘文件中。
可以通过下面几个启动参数来调节：
该参数指定了redo日志文件所在的目录，默认值就是当前的数据目录。
该参数指定了每个redo日志文件的大小，在MySQL 5.7.21这个版本中的默认值为48MB，
该参数指定redo日志文件的个数，默认值为2，最大值为100
在将redo日志写入日志文件组时，是从ib_logfile0开始写，如果ib_logfile0写满了，就接着ib_logfile1写，同理，ib_logfile1写满了就去写ib_logfile2，依此类推。如果写到最后一个文件该咋办？那就重新转到ib_logfile0继续写。
log buffer本质上是一片连续的内存空间，被划分成了若干个512字节大小的block。将log buffer中的redo日志刷新到磁盘的本质就是把block的镜像写入日志文件中，所以redo日志文件其实也是由若干个512字节大小的block组成。
redo日志文件组中的每个文件大小都一样，格式也一样，都是由两部分组成：
前4个特殊block的格式:
| 属性名 | 长度（单位：字节） | 描述 |
|---|---|---|
| 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都有，我们不关心 |
| 属性名 | 长度（单位：字节） | 描述 |
|---|---|---|
| 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都有，我们不关心 |
Log Sequeue Number的全局变量，代表当前系统写入log buffer的日志量。初始的lsn值为8704（也就是一条redo日志也没写入时，lsn的值为8704）。
lsn的增长量，是按照实际写入的日志量加上占用的log block header和log block trailer来计算的
buf_free就是标记下一条redo日志应该写入到log buffer的位置的变量
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是相对位置偏移量。
在mtr结束时，会把这一组redo日志写入到log buffer中。除此之外，在mtr结束时还有一件非常重要的事情要做，就是把在mtr执行过程中可能修改过的页面加入到Buffer Pool的flush链表。flush链表插入脏页过程已经了解过。
当第一次修改某个缓存在Buffer Pool中的页面时，就会把这个页面对应的控制块插入到flush链表的头部，之后再修改该页面时由于它已经在flush链表中了，就不再次插入了。也就是说flush链表中的脏页是按照页面的第一次修改时间从大到小进行排序的。
在这个过程中会在缓存页对应的控制块中记录两个关于页面何时修改的属性：
flush链表中的脏页按照修改发生的时间顺序进行排序，也就是按照oldest_modification代表的LSN值进行排序，被多次更新的页面不会重复插入到flush链表中，但是会更新newest_modification属性的值。
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日志文件都有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的大佬还是想了一些办法加快这个恢复的过程
undo log是实现原子性的关键，当事务回滚时会撤销所有已经执行完毕的sql语句，但是需要记录回滚的日志信息。
回滚：
情况一：事务执行过程中可能遇到各种错误，比如服务器本身的错误，操作系统错误，甚至是突然断电导致的错误。
情况二：程序员可以在事务执行过程中手动输入ROLLBACK语句结束当前的事务的执行。
这两种情况都会导致事务执行到一半就结束，但是事务执行过程中可能已经修改了很多东西，为了保证事务的原子性，我们需要把东西改回原先的样子
每当我们要对一条记录做改动时（这里的改动可以指INSERT、DELETE、UPDATE），都需要留一手 —— 把回滚时所需的东西都给记下来
比方说：
你插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉就好了。
你删除了一条记录，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了。
你修改了一条记录，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了。
设计数据库的大佬把这些为了回滚而记录的这些东东称之为撤销日志，英文名为undo log
为了实现事务的原子性，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;当我们向表中插入一条记录时会有乐观插入和悲观插入的区分，但是不管怎么插入，最终导致的结果就是这条记录被放到了一个数据页中。
如果希望回滚这个插入操作，那么把这条记录删除就好了，也就是说在写对应的undo日志时，主要是把这条记录的主键信息记上。
类型为TRX_UNDO_INSERT_REC的undo日志:
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自动创建的列）的分配策略大抵相同。
插入到页面中的记录会根据记录头信息中的next_record属性组成一个单向链表，我们把这个链表称之为正常记录链表；
被删除的记录其实也会根据记录头信息中的next_record属性组成一个链表，只不过这个链表中的记录占用的存储空间可以被重新利用，所以也称这个链表为垃圾链表；
Page Header部分有一个称之为PAGE_FREE的属性，它指向由被删除记录组成的垃圾链表中的头节点。
假设现在我们准备使用DELETE语句把正常记录链表中的最后一条记录给删除掉，其实这个删除的过程需要经历两个阶段：
TRX_UNDO_DEL_MARK_REC类型的undo日志：
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语句时，InnoDB对更新主键和不更新主键这两种情况有截然不同的处理方案。
不更新主键的情况
在不更新主键的情况下，又可以细分为被更新的列占用的存储空间不发生变化和发生变化的情况。
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在聚簇索引中分了两步处理：
表空间其实是由许许多多的页面构成的，页面默认大小为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页面链表
在一个事务执行过程中，可能混着执行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和trx 2执行过程中，InnoDB共需为这两个事务分配5个Undo页面链表，画个图就是这样：
准备数据：
CREATE TABLE hero ( number INT, name VARCHAR(100), country varchar(100), PRIMARY KEY (number)) Engine=InnoDB CHARSET=utf8;INSERT INTO hero VALUES(1, '刘备', '蜀');如果一个事务修改了另一个未提交事务修改过的数据，那就意味着发生了脏写。
Session A和Session B各开启了一个事务，Session B中的事务先将number列为1的记录的name列更新为’关羽’，然后Session A中的事务接着又把这条number列为1的记录的name列更新为张飞。如果之后Session B中的事务进行了回滚，那么Session A中的更新也将不复存在，这种现象就称之为脏写。
一个事务读到了另一个未提交事务修改过的数据
Session A和Session B各开启了一个事务，Session B中的事务先将number列为1的记录的name列更新为’关羽’，然后Session A中的事务再去查询这条number为1的记录，如果du到列name的值为’关羽’，而Session B中的事务稍后进行了回滚，那么Session A中的事务相当于读到了一个不存在的数据，这种现象就称之为脏读。
一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值
在Session B中提交了几个隐式事务（注意是隐式事务，意味着语句结束事务就提交了），这些事务都修改了number列为1的记录的列name的值，每次事务提交之后，如果Session A中的事务都可以查看到最新的值，这种现象也被称之为不可重复读。
一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来
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关键字（在全局范围影响）：
对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列（row_id并不是必要的，我们创建的表中有主键或者非NULL的UNIQUE键时都不会包含row_id列）：
假设之后两个事务id分别为100、200的事务对这条记录进行UPDATE操作，操作流程如下：
每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表，所以现在的情况就像下图一样
该记录每次更新后，都会将旧值放到一条undo日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。每个版本中还包含生成该版本时对应的事务id，这个信息很重要，我们稍后就会用到。
对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录，也就是说假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。
设计InnoDB的大佬提出了一个ReadView的概念，这个ReadView中主要包含4个比较重要的内容：
有了这个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的执行过程
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的执行过程
使用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的执行过程:
# Transaction 200BEGIN;# 更新了一些别的表的记录...UPDATE hero SET name = '赵云' WHERE number = 1;UPDATE hero SET name = '诸葛亮' WHERE number = 1;此刻，表hero中number为1的记录得到的版本链表如下所示：
使用REPEATABLE READ隔离级别的事务在第一次查询开始时才生成一个独立的ReadView。同一个事务中前后查询同一条记录看到的数据是一样。
并发事务访问相同记录的情况大致可以划分为3种
解决脏读、不可重复读、幻读这些问题呢,有两种可选的解决方案：
方案一：读记录的历史版本和改动记录的最新版本本身并不冲突,读操作利用多版本并发控制（MVCC），写操作进行加锁。
方案二：读记录的历史版本和改动记录的最新版本本身冲突,这样也就意味着读操作和写操作也像写-写操作那样排队执行,读、写操作都采用加锁的方式。
比方在银行存款的事务中，你需要先把账户的余额读出来，然后将其加上本次存款的数额，最后再写到数据库中。在将账户余额读取出来后，就不想让别的事务再访问该余额，直到本次存款事务执行完成，其他事务才可以访问账户的余额。
事务利用MVCC进行的读取操作称之为一致性读，或者一致性无锁读，有的地方也称之为快照读。
所有普通的SELECT语句（plain SELECT）在READ COMMITTED、REPEATABLE READ隔离级别下都算是一致性读
示例：
SELECT * FROM t;SELECT * FROM t1 INNER JOIN t2 ON t1.col1 = t2.col2一致性读并不会对表中的任何记录做加锁操作，其他事务可以自由的对表中的记录做改动。
要使写-写、读-写或写-读情况中的操作相互阻塞,MySQL给锁分了个类：
共享锁，英文名：Shared Locks，简称S锁。在事务要读取一条记录时，需要先获取该记录的S锁。
独占锁，也常称排他锁，英文名：Exclusive Locks，简称X锁。在事务要改动一条记录时，需要先获取该记录的X锁。
SELECT ... LOCK IN SHARE MODE;SELECT语句后边加LOCK IN SHARE MODE，如果当前事务执行了该语句，那么它会为读取到的记录加S锁，这样允许别的事务继续获取这些记录的S锁（比方说别的事务也使用SELECT … LOCK IN SHARE MODE语句来读取这些记录），但是不能获取这些记录的X锁（比方说使用SELECT … FOR UPDATE语句来读取这些记录，或者直接修改这些记录）。如果别的事务想要获取这些记录的X锁，那么它们会阻塞，直到当前事务提交之后将这些记录上的S锁释放掉。
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锁是兼容的。
对于MyISAM、MEMORY、MERGE这些存储引擎来说，它们只支持表级锁，而且这些引擎并不支持事务，所以使用这些存储引擎的锁一般都是针对当前会话来说的。
示例：
在使用以上存储引擎的会话场景：
会话1，对表执行select操作，获取到表级别的S锁，会话2，对表执行update操作，要获取表的X锁，此时会话2会被阻塞，直到会话1释放S锁。
InnoDB中的表级锁
# InnoDB存储引擎会对表t加表级别的S锁LOCK TABLES t READ；# InnoDB存储引擎会对表t加表级别的X锁LOCK TABLES t WRITE;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，那么说明对该页面做修改的事务都已经提交了
否则，就需要在页面中定位到对应的二级索引记录，然后「回表」找到它对应的聚簇索引记录，然后再重复情景一的做法。
来源地址：https://blog.csdn.net/qq_44788518/article/details/130792213
--结束END--
本文标题: 《MySQL是怎么运行的》阅读笔记
本文链接: https://www.lsjlt.com/news/371527.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑，方便收藏和打印～
2024-05-12
2024-05-12
2024-05-12
2024-05-12
2024-05-12
2024-05-12
2024-05-12
2024-05-11
2024-05-11
2024-05-11
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0