MySQL是怎样运行的：从根儿上理解MySQL

Mar 13, 2021 读书笔记 #数据库 #MySQL #TODO

0

1-3章是MySQL一些基础知识和相关操作，4-7章是索引相关，8-9章数据目录和表空间先大致了解即可，10-11章单表访问和多表连接，涉及到很多索引的内容，12-14章知道MySQL会利用统计信息进行基于成本和基于规则的查询优化即可，15-17章关于Explain命令和optimizer trace表的应用，在实际使用中可能会比较重要，18章是Buffer Pool，简单了解原理，19-23章是日志和故障恢复，24-25章是事务，MVCC，并发控制，锁等面试常考点。

1 MySQL概述

MySQL是C/S架构，客户端将查询语句发送给服务端，服务端处理一个请求大致需要经过三部分：连接管理，解析优化，存储引擎。
客户端和服务端的连接本质上也是进程通信，最常用的方法是TCP/IP套接字通信，而对于Linux本地连接，还可以使用Unix域套接字(可以简单理解为网络和本地的socket)。
解析优化包括对SQL语句进行词法分析，语法分析，语义分析之后生成查询树(查询计划)，之后对查询计划进行查询优化处理，得到实际的物理查询计划，再通过存储引擎查询数据。
存储引擎的作用是存储和管理实际数据，对外提供存取数据的接口，MySQL支持多个存储引擎，默认是InnoDB，此外比较常用的还有MyISAM和MEMORY，可以通过SHOW ENGINEs;查看支持的存储引擎，可以给不同数据库和不同表设置使用不同的存储引擎。

2 启动选项和系统变量

客户端最常用启动选项：-u(用户名)，-p(密码)，-h(主机名)，-P(端口)，注意短形式的选项后面可以不加空格(-p除外，必须加空格)，长形式的选项大多后面要加等号，且之间不能有空格。
将选项写入到配置文件，MySQL会按照一些路径顺序查找配置文件，通常(docker安装的MySQL)默认配置文件放在/etc/mysql/my.cnf里。
配置文件里可以分为不同的组，例如[server]，[client]，可以将配置应用于不同的MySQL程序。
多个配置文件的同一配置，以顺序最后的配置文件为准；同一配置文件多个组的同一配置，以最后出现的组为准；既出现在配置文件里又出现在启动选项里的配置，以启动选项为准；在启动时可以指定default-file设置只读取某个配置文件。
系统变量包括例如max_connections表示同时允许连接的最大客户端数量，例如default_storage_engine表示默认存储引擎，大多数系统变量可以通过启动选项设定，也可以在运行时修改。
大多数系统变量都具有GLOBAL和SESSION不同的作用域，修改系统变量时默认作用域是SESSION。
状态变量是描述数据库程序运行状态的变量，比如Threads_connected表示当前的客户端连接数，状态变量不允许自行设置。

3 字符集和比较规则

MySQL支持很多字符集，最常用的是utf8(阉割版utf8，只用1-3个字节表示一个字符)和utf8mb4，可以通过SHOW CHARSET [LIKE xxx]查看字符集，一个字符集包括多种比较规则，可以通过SHOW COLLATION [LIKE xxx]查看比较规则。
MySQL支持服务器，客户端，表，列四个级别的字符集和比较规则的设置。
在客户端和服务端通信的过程中，会进行多次字符集的转化，所以为了方便和避免乱码的产生，通常把character_set_client，character_set_connection和character_set_results这三个系统变量设置为同一个字符集，可以使用一条命令SET NAME 字符集名来设置。

4 InnoDB记录结构

InnoDB以页作为磁盘和内存交互的基本单位，页大小为16KB，一页包括多个行记录。
InnoDB有4种行记录格式，目前默认和通用的是COMPACT行格式。
一个InnoDB行包括额外数据和真实数据，额外数据包括变长字段的长度列表，NULL值列表和记录头信息。
变长字段的长度只保存非NULL(实际数据)的变长字段，逆序存放(因为遍历链表的指针会定位在额外数据和真实数据之间，逆序存放方便更快找到字段长度)，字段长度1(不超过127)或2个字节。
NULL值列表二进制位压缩保存允许NULL值的字段，同样是逆序存放。
记录头信息固定5个字节，包括删除标记，下一条记录(按主键排序而不是插入顺序)相对位置，记录类型(普通记录，页虚拟最大最小记录，B+树非叶子节点记录)，是否是非叶子节点的最小记录标记，记录所在分组的记录数，记录在堆中的位置编号。
记录的真实数据中除了用户定义的列，InnoDB还会自动添加transaction_id，roll_pointer和row_id(如果没有定义主键)三个列。
当一个列(VARCHAR，TEXT，BLOB等)存储了大量数据时，可能会发生行溢出(具体情况再分析)的现象。

5 InnoDB数据页结构

InnoDB设计了多种不同的页，通常所说的数据页其实是索引(INDEX)页。
一个数据页包括7部分的内容，分别是File Header和File Trailer(不同类型的页面都有)，Page Header和Page Directory(数据页专有信息)，Infimum和Supremum(最小和最大虚拟记录)，User Records(用户实际记录)和Free Space(空闲空间)。
File Header38字节，包括页面校验和，页号，逻辑上下一个页号，页类型等，所属表空间以及LSN相关的属性。
File Trailer8个字节，包括页面校验和和LSN，当页面刷回磁盘中断导致页面不完整，因为File Header肯定是先写入磁盘，所以可以根据两者的校验和来判断同步是否出错。
Page Header56个字节，包括Page Directory的slot数量，Free Space的起始地址，记录总数(包含和不包含已删除和虚拟记录)，删除链表首地址，插入方向(主键增加为右)，一个方向连续插入数量，B+树索引的相关信息等。
Page Directory的每个slot记录记录分组(规定最小记录单独一个组，最大记录分组记录数1-8，其他分组记录数4-8)之后，每个组的最大记录地址偏移量，因此可以通过二分法定位到记录所在分组，再通过记录链表遍历查找。
User Records中将记录按主键大小排序，通过每个记录的next_record属性组织成一个单链表，并且维护了一个已删除的记录链表。

6 B+树索引

简单来说，索引通常指B+树索引，就是将页面组织成B+树的形式，其中非叶子节点页面类似Page Directory，但是指向的是页面而不是记录(即页面中的记录不是实际数据记录而是保存了索引列，主键列和对应页号)，而叶子节点页面是存放实际数据，当定位到某个叶子节点，通过二分查找Page Directory定位到某个记录组，再顺序遍历记录组中的记录链表，查找到具体数据。
聚簇索引是指记录数据按照主键大小顺序，完整地保存在B+树叶子节点中，InnoDB会自动创建聚簇索引，且该索引其实也是正式InnoDB数据的存储组织方式，也就是所谓的"索引即数据，数据即索引"。
二级索引是指叶子节点只保存记录的主键，因此还需要一次"回表"，通过聚簇索引得到实际数据。
联合索引是指按多个列建立索引，按顺序先按第一个列排序，相同的值再按第二个列排序，以此类推。
B+树索引的根节点自诞生起便不会再移动位置。

7 B+树索引的使用

索引不易过多，使用索引在时间(维护B+树的平衡，有序，双向链表的维护，页面分裂和回收等)和空间(一个页面16KB，一棵B+树索引由很多个页面组成)上都需要代价。
全值匹配是指搜索条件都是等于，且所有列都是索引列，顺序不影响是否使用索引(查询优化器会处理)。
对于联合索引，搜索条件各个列必须是索引中从左连续的列，因为联合索引是按顺序一列一列排序的。
对于字符串类型的列，搜索前缀也可以用到索引快速定位。
对于范围的搜索，只有当搜索条件列是索引最左列才有效(其他索引列是在左边索引列值相同的情况下才排序)，或者左边的列全值匹配，再范围搜索右边的列(这些列必须是连续的，注意联合索引的定义)。
索引本身就是一个排序结构，所以可以用于ORDER BY子句，这里顺序会影响是否使用索引。
当ASC，DESC混用时不会使用索引。
带有非索引列的搜索加上ORDER BY，不会使用索引(因为必须先WHERE搜索后才能排序)。
排序列如果使用了函数或者其他表达式，也不会使用索引。
类似于排序，索引也能用于分组。
使用二级索引+回表，有时候效率反而不如全表扫描，例如当要回表的数据特别多，就需要在访问聚簇索引时使用大量随机IO。
综上，通常只对需要搜索，排序，分组的列建立索引，且要考虑列的值域，值域太小建立索引的效果也不好，同时列的类型也要尽量小，如果可以的话，让数据库自动生成自增主键，避免叶子节点过多的分裂和移位造成性能损耗。

8 数据目录

可以通过SHOW VARIABLES LIKE 'datadir';查看数据目录。
每一个数据库都在数据目录中对应了一个文件夹，在MySQL8.0之前，每个表会有对应一个.frm文件描述表结构，MySQL8.0之后，表结构和数据统一放在.ibd文件里(具体细节参考MySQL8.0的新版本特性)。
表空间是一个逻辑上的概念，一个表空间包含多个页，在物理层面上可能包括一个或多个文件。
系统表空间默认对应一个名为ibdata1，大小为12M的文件，包括了一些系统表数据，例如数据字典，日志，事务等相关信息。
每个表对应一个独立表空间，也就是数据库目录下的一个.ibd文件。
MySQL四个主要的系统数据库，mysql存储了MySQL的用户账户，权限信息，存储过程定义，帮助信息和时区信息等；information_schema存储了所有其他数据库的元数据，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引等；performance_schema存储了MySQL运行过程中的一些状态信息，比如统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等；sys通过视图的形式将information_schema和performance_schema结合起来，更方便了解MySQL服务器的一些性能信息。

9 InnoDB表空间

TODO

这一章实在无能为力，暂时只粗略看了一遍。

一个区(extent)就是物理上连续的64个页，按区为单位分配，虽然会出现比较多的碎片造成空间浪费(MySQL通过其他方法尽量解决)，但可以消除很多随机IO。
一个段(segment)是逻辑上区的集合，比如一个索引会生成两个段，叶子段和非叶子段。

10 单表访问方法

访问方法/访问类型是指执行查询语句的方式，包括const，ref，ref_or_null，range，index，all等。
const: 主键列或者Unique二级索引列和常量(非NULL，NULL不是唯一值)进行等值比较。
ref: 普通二级索引列和常量(包括NULL)进行等值比较，当索引是联合索引，不需要所有索引列，只要最左连续索引列是与常量比较就可以。
ref_or_null: 二级索引列和常量进行等值比较或者判断是否NULL。
range: 利用索引进行范围匹配，当搜索条件中出现>，<，BETWEEN，IN(看成多个单点区间)，IS NULL等。
index: 遍历二级索引，但是不需要回表，直接将索引列的值加入结果集。
all: 全表扫描，对于InnoDB也就是直接扫描聚簇索引(数据)。
考虑一种情况，假设对key2列建立二级索引，对于查询SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';，先不考虑common_field，而是根据key2的索引，range查询得到一个数据范围，再遍历查找第二个条件；而对于SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';，无法使用索引(因为不满足索引列key2的条件的数据仍可能满足第二个条件)，只能遍历所有数据。
人肉判断使用哪个索引好的一个方法：先找出所有可能使用的索引，对于每个索引，将复杂的查询条件简化，也就是将无关的条件替换为True或者False(除了矛盾条件，其他都为True)，化简得到的查询条件就是使用该二级索引第一步能查到的数据，如果为True，说明需要扫描整个索引。
在某些特殊情况下会对多个二级索引进行合并，比如多个条件(二级索引等值匹配(只有等值匹配才能让主键集合有序)或者主键索引范围匹配)AND或者OR，且条件列都可以是二级索引列，可以分别查出主键，求交集/并集之后再统一回表查找聚簇索引。
对于二级索引不是等值匹配的情况(此时查到的主键集合无序的)，采用Sort-Union的索引合并方法，将查出来的主键集合排序，再合并回表。

11 表连接

两个表的连接普通做法: 根据单表条件查找得到第一个表的部分数据作为驱动表(外表)，另一个表作为被驱动表(内表)，对于驱动表的每个记录，都在被驱动表中找到符合多表条件的记录，加入结果集。
连接根据"驱动表的记录在被驱动表中找不到匹配记录时是否加入结果集"分为外连接和内连接，在MySQL中，外连接又根据"以左侧或者右侧的表作为驱动表"分为左外连接和右外连接。
外连接中使用ON 条件作为计算得到中间表的条件，使用WHERE 条件作为最后对中间表的过滤条件，在内连接中ON和WHERE等同。
外连接语法: SELECT * FROM t1 LEFT/RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 过滤条件];。
内连接语法: SELECT * FROM t1(可由逗号分割多个表) [INNER/CROSS] JOIN t2 ON/WHERE(可同时使用，含义相同) 连接条件;。
嵌套循环连接: 最简单粗暴的连接方式，遍历驱动表，然后多次在被驱动表中查找匹配的记录。
可以使用索引加快对被驱动表的查找。
基于块的循环嵌套连接: 将驱动表按块读取到内存(join buffer)，之后遍历被驱动表的每一条记录，都和块中的所有驱动表记录匹配，最好的情况是join buffer可以容纳整个驱动表，那么只需要读取一次驱动表，遍历一次被驱动表即可。
MySQL8.0之后才支持Hash Join，所以本书中没有提到，Hash Join简单来说就是先对外表简历HashMap，然后只要遍历一次内表，就能从HashMap中找到匹配的数据。

12 基于成本的查询优化

13 InnoDB统计数据

14 基于规则的查询优化

条件化简: 包括移除不必要的括号，常量传递，等值传递，移除永真或永假的条件，表达式计算，合并HAVING和WHERE子句等。
外连接消除: 如果查询中对被驱动表进行空值拒绝(NULL-reject)，这样外连接就能和内连接互相转换，查询优化器就可以评估各种不同连接顺序的成本，选择最优的连接顺序。简单来说，就是把一些可以转化为内连接的外连接转化为内连接。
子查询优化: TODO

15 16 Explain命令

17 optimizer_trace表

18 Buffer Pool

Buffer Pool默认大小128M，可以在启动时通过innodb_buffer_pool_size选项设置。
Buffer Pool对于每个缓存页，都会有对应的一个控制块保存页对应的一些控制信息，控制块按顺序放在Buffer Pool的前边，缓存页放在后边。
控制块构成一个free链表，维护空闲的缓存页。
维护一个哈希表，key为表空间+页号，value为缓存页，可以快速判断页面是否在Buffer Pool中。
当修改了缓存页的数据，就产生了脏页(dirty page)，出于性能考虑无法立即同步，所以类似的维护一个控制块的flush链表表示脏页，再定义刷回磁盘。
Buffer Pool的空间是有限的，当已经没有空闲的缓存页时，就需要把其中某些页从Buffer Pool中移除，把新的页放进去，通常采用LRU算法维护一个LRU缓存链表。
简单的LRU链表原理: 当访问某一个缓存页面，就把该页对应的控制块移到LRU链表头部(可能是添加或者移动)，这样当空闲缓存页不足时，就从LRU链表尾部淘汰即可。
上面的LRU链表存在两个问题: InnoDB会对周围的内存页进行预读(read ahead)，可能出现大量的预读页面会把LRU链表的其他热门页面挤到尾部；偶尔一次全表扫描的情况会导致Buffer Pool的页面全换了一遍。
InnoDB将LRU链表分为热区(young)和冷区(old)，old区所占比例可以通过系统变量innodb_old_blocks_pct来设置。然后规定，页面初次加载到Buffer Pool时，控制块是加到链表old区的头部，而不影响young区的热点数据；而对于权标扫描，通过设置系统变量innodb_old_blocks_time(默认1000ms)，对于一个在old区的页面，当后续访问时间和第一次访问时间相差不超过这个系统变量时间时，该页面就不会被移动到young区，因为全表扫描的次数不多，而在一次全表扫描中，通常对同一个页面第一次和最后一次访问相差时间也不会超过1000ms。
LRU还有很多很多其他的优化策略，但无论如何优化，最终目的都是要优化Buffer Pool的缓存命中率。
后台线程脏页刷回磁盘主要有两种方式，一种是从old区尾部开始扫描，遇到脏页就刷回磁盘，一种是直接遍历flush链表。如果后台线程刷新脏页速度较慢，就会导致需要用户线程同步去刷新，这会大大降低性能。
可以通过设置innodb_buffer_pool_instances选项来设置多个Buffer Pool，各个Buffer Pool之间相互独立，独立管理各种链表，多线程访问时不冲突。
通过SHOW ENGINE INNODB STATUS;可以查看Buffer Pool状态信息。

19 事务

事务指能满足ACID特性的一个或多个数据库操作。
事务的状态包括active(还在执行)，partially committed(事务所有操作完成，还没刷回磁盘)，failed(当处于以上两种情况，遇到某些错误或者人为停止)，aborted(failed状态下执行回滚，恢复到事务执行之前的状态)，committed(所有修改数据都同步到磁盘)。
MySQL有一个系统变量autocommit默认为ON，表示如果不显式地开启事务，那么每条语句都会当做一个事务并自动提交。
在事务中，虽然没有输入COMMIT，当如果输入了某些特殊语句也会导致隐式提交，包括：修改数据库对象的DDL，事务控制或者锁表解锁的语句等。
在事务中定义保存点(save point)，在ROLLBACK回滚时可以指定回滚到哪一个保存点，相关语句分别是SAVEPOINT xx，ROLLBACK TO xx和RELEASE SAVEPOINT xx。
事务只有在第一次真正修改记录(INSERT，UPDATE，DELETE)时才会分配一个事务id，而不是事务开启，并且初始化为0。

20 21 redo日志

redo(重做)日志是为了保证事务的持久性，记录下对数据进行的修改，及时系统发生了故障导致Buffer Pool的数据修改没能刷回磁盘，也能够根据redo日志重新修改数据。注意，这些redo日志就一定要及时刷回磁盘，比起将数据修改的页面刷回磁盘，这样做的好处一是redo日志占用空间小，二是redo日志是顺序写入磁盘。
redo日志的基本结构是类型，表空间ID，页号和具体内容。
简单的redo日志: 例如MLOG_1BYTE(type为1)表示在某个页面的某个偏移量写入(无论是插入还是更新，本质都是二进制数据的写入)1字节，在上面提到的基本结构中的具体内容里就需要保存偏移量和写入的数据；MLOG_2BYTE(type为2)同理，MLOG_WRITE_STRING(type为30)表示在某个页面的某个偏移量写入某个长度的数据等等。这种日志也叫物理日志，保存的是物理上实际的数据，恢复时直接写入即可。
其他redo日志: 有时候一个语句会影响到很多个页面的修改，比如插入一条记录，可能会导致相关的索引页发生大量的修改，这时候采用另一种类型的redo日志，例如MLOG_REC_INSERT(type为9)表示插入一条非紧凑行格式的记录，MLOG_COMP_REC_INSERT(type为10)表示插入一条紧凑行格式的记录等等。这类日志称为逻辑日志，保存的是一个逻辑的动作，恢复时要根据其中保存的一些信息执行一些对应的函数才能恢复。
Mini-Transaction: 有些操作(比如插入一条记录)可能会产生多个redo日志，为了保证原子性，必须将这些redo日志作为一个组，恢复时要不全执行，要不全不执行，这就是一个Mini-Transaction(mtr)，具体实现上，对于单个redo日志的Mini-Transaction，为了节省空间，使用了日志记录中的一个比特位来表示，对于多个redo日志，在最后额外添加一个MLOG_MULTI_REC_END的特殊日志。
redo日志存放在专门的页里，又称为block，一个block512字节，包括了16字节(12+4)的一些元数据和校验和。
redo日志有一块专门的缓冲区redo log buffer，分为多个block，可以通过选项innodb_log_buffer_size来指定buffer大小，默认为16MB(MySQL5.7)。
同一个mtr的redo日志组会先暂存在一个地方，当mtr结束，才将整个日志组插入到buffer中。
log buffer刷回磁盘的时机大概包括log buffer空间不足时，事务提交时(数据页修改可以不立即刷回磁盘，但日志要马上刷回磁盘)，后台线程定时刷，关闭服务器时，做checkpoint时。
磁盘上的redo日志包括一个文件组(ib_logfile[数字])，循环写，当最后一个文件写满，又会重新转到第一个文件写，通过checkpoint的方式保证日志不会被覆盖。
日志序列号(Log Sequence Number，lsn)是一个全局变量，初始值为8704。每次写入一组redo日志，lsn的增长要考虑log buffer block的结构，比如第一组redo日志写入，lsn就增加一个block的头部字节数(12)，再加上日志的字节数，如果日志大小超过一个block，还需要增加适当的block尾部字节数。
数据库维护了两个全局变量，buf_next_to_write(write_lsn)表示目前刷回磁盘的lsn，而flushed_to_disk_lsn表示完成fsync，真正写入到文件的lsn。
flush链表中，每个控制块会维护页面第一次修改时的LSN和最新一次修改的LSN，然后根据old_lsn从最近到最远排序(修改已经在flush链表中的页面不会再次移动，只会更新new_lsn)。
redo日志是为了保证系统崩溃会能将脏页刷回磁盘，如果脏页已经刷回磁盘了，那么对应的redo日志就可以不用了。
维护一个checkpoint_lsn表示可以覆盖的redo日志lsn，初始值也是8704，当一个脏页被刷回磁盘，就需要重新计算checkpoint_lsn，该值也等于当前flush链表里最早修改的脏页(表头)对应的old_lsn(在此之前的所有redo日志都可以覆盖了，因此直接让checkpoint_lsn等于该值)。同时每做一次checkpoint，还要将checkpoint_lsn，对应redo日志文件组(ib_logfile)的偏移量和checkpoint编号写入到日志文件的管理信息里(ib_logfile前4个block)。
各个lsn之间的简单关系: checkpoint_lsn之前是脏页已经刷回磁盘的，这部分log file可以被覆盖，checkpoint_lsn到flushed_to_disk_lsn之间是日志已经刷回磁盘log file的，flushed_to_disk_lsn和lsn之间是log buffer还没刷回log file的日志。
崩溃时如何恢复: 首先确定恢复的起点就是最近的checkpoint_lsn，终点就是最后一个没写满的block(通过元数据确定)，恢复就是按照日志顺序执行redo操作，可以有一些优化包括把对同一个页面的修改日志先用一个哈希表存起来，再一起做，减少很多页面随机IO，还有就是对于一个页面，如果File Header中的最近一次被修改的LSN(FIL_PAGE_LSN)大于checkpoint_lsn(注意这里说的页面都是指磁盘页面，而不是Buffer Pool，都崩溃了，肯定只能根据已经在磁盘的数据来恢复)，说明是在checkpoint之后被写入磁盘的，那么相当于就可以把checkpoint_lsn更新到FIL_PAGE_LSN了，在此之前的redo日志不需要再次redo。

22 23 undo日志

undo(撤销)日志是为了保证事务的原子性，当事务执行到一半出现错误或者手动rollback时，可以根据undo日志执行逆操作进行回滚。
(基本上)每个事务都会分配一个事务id，对应改动的记录的隐藏列transaction_id，生成算法类似row_id，可以保证递增，另外一个隐藏列roll_pointer则是指向对应undo日志(保存了该记录的旧版本)的一个指针。
INSERT操作对应的undo日志: 日志类型为TRX_UNDO_INSERT_REC；其中最主要是保存了该记录主键每个列的存储空间大小和实际值，有了主键信息，回滚时直接删除该记录即可，此时记录roll_pointer指向该日志记录。
DELETE操作对应的undo日志: 日志类型为TRX_UNDO_DEL_MARK_REC；记录删除包括两个阶段，第一个阶段是将页面中记录链表的记录打上一个删除标记，第二阶段是当事务提交之后，会有专门的线程将记录真正删除(移到删除链表里)。所以删除操作只需要考虑第一阶段对应的undo日志(事务提交后就肯定不需要回滚了，如果出故障没写入硬盘，那是redo日志的事了)。其中主要包括old_trx_id和old_roll_pointer表示旧记录的trx_id和roll_pointer(方便找到旧记录对应的undo日志，比如该记录是先插入，再删除，那么这个old_roll_pointer就指向了插入的undo日志，这样子这一系列的undo日志就构成了一个版本链)，主键各列信息和索引各列信息(用于第二阶段对记录真正的删除)。
UPDATE操作对应的undo日志: 对于不更新主键的情况，日志类型为TRX_UNDO_UPD_EXIST_REC，类似删除的undo日志，多了一个属性记录被更新列更新前的值(用于undo，而对于上面DELETE操作，只需要根据主键在删除链表中找到即可undo)。对于更新主键的情况，等同于做一次删除(TRX_UNDO_DEL_MARK_REC)和一次插入(TRX_UNDO_INSERT_REC)。
undo日志基本上有两个作用: 回滚和MVCC(多版本并发控制)。

24 事务隔离级别与MVCC

事务的并发执行如果不加以控制，就会出现一些问题: 脏写(Dirty Write，一个未提交事务修改的数据被另一个事务的修改所覆盖)，脏读(Dirty Read，读了另一个未提交事务修改的数据，然后该事务回滚了，读到的就是脏数据)，不可重复读(Non-Repeatable Read，在一次事务中多次读了一个数据，且该数据被其他事务修改并提交，导致多次读取到不同的值)，幻读(Phantom，类似不可重复读，不同在于其他事务对数据进行了插入(强调是插入，如果是删除或者修改都属于不可重复读)而不是修改)。
对应的，SQL标准里定义了四种隔离级别，不同的隔离级别可以防止不同的事务并发所导致的问题发生。为了方便记忆，我将问题和隔离级别放在一起: 脏写 | (未提交读) | 脏读 | (已提交读) | 不可重复读 | (可重复读) | 幻读 | (可串行化)，括号内是隔离级别，左边表示该级别下不会发生的问题，右边表示会发生的问题。在满足ACID的RDBMS中，脏写是绝对不允许发生的，无论是什么隔离级别。
实际上不同RDBMS对隔离级别的支持是不同的，并且一些隔离级别的实现并不同于SQL标准，比如MySQL的默认隔离级别是可重复读，并且是可以防止幻读的发生的。
在一条记录经过多个事务的多次更新后，产生了一个版本链，头结点就是当前记录，然后roll_pointer依次指向前一个版本的undo日志，记住每个undo日志里也保存了生成该日志的事务id。
对于已提交读和可重复读，必须保证只能读到已提交的事务修改的数据，而不一定是数据的最新版本，因此核心问题是要能够判断版本链中哪个版本是当前事务可见的。
ReadView: 当一个事务生成一个ReadView，对应维护了4个值，包括m_ids表示生成时刻活跃(未提交)的事务id，min_trx_id表示m_ids的最小值，max_trx_id表示下一个系统分配的事务id(全局维护的值)，creator_trx_id表示生成该ReadView的事务id(注意区分事务开启的时间和生成ReadView的时间，可能出现事务id是1(如果该事物还没进行修改操作，为0)，下一个分配事务id是100)。
这样在访问某一条记录时，只需要根据以下规则判断记录的某个版本是否可见，而如果不可见，就根据版本链回到上一个版本继续检查: 如果该版本事务id等于creator_trx_i，说明是访问当前事务自身修改过的记录，可以访问；如果该版本事务id小于min_trx_id，说明在生成ReadView前已提交，可以访问；如果该版本事务id大于等于max_trx_id，说明该版本事务在生成ReadView之后才开启，不可以访问；如果该版本事务id大于等于min_trx_id而小于max_trx_id，需要判断是否在m_ids中，如果是，说明创建ReadView时该事务还处于活跃状态，不能访问，否则已提交可以访问。
在MySQL中，已提交读和可重复读两种不同隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。已提交读是在每一次查询数据前都独立生成一个ReadView(显然不可重复读，两个ReadView状态可能不同)，可重复读是在第一次读取数据前生成一个ReadView。
MVCC处理的只是事务并发的读-写问题，可以避免加锁，提升系统性能。
insert undo在事务提交之后就可以删除了(即insert undo只在事务中的回滚起作用，因为insert undo对应的是删掉这个记录，而MVCC要的是找到记录的旧版本，而insert undo对应的就已经是最旧版本了，指向的undo日志已经不是记录了)，而update undo和delete undo还需要支持MVCC，不能立即删除。

25 锁

事务并发执行可能带来各种问题，可以分为三类: 读-读，读-写和写-写。
读-读: 同时读不会有影响，允许这种操作。
写-写: 会发生脏写的问题，所以需要通过锁来实现让多个事务顺序执行，锁是一个内存结构，简单来说，当一个事务想修改一条记录，先查看内存中是否有和该记录关联的锁结构，如果没有，生成一个锁(比如包括事务信息和is_waiting表示是否等待中)与记录关联，加锁成功，如果此时另一个事务也要修改该记录，查看发现已经有锁，也生成一个锁与记录关联，但is_waiting属性为true，加锁失败，只能等待。
读-写: 不同的隔离级别可能会产生脏读，不可重复读，幻读的问题，有两种解决方案: 一种是读操作利用MVCC读旧版本，写操作利用加锁写最新版本，这种方案性能更高；另一种操作当业务需求必须读最新版本数据，那么读和写都要采用加锁的方式。
MySQL中的一致性读: 也叫快照读，或者一致性无锁读，采用MVCC进行读操作，无需加锁。所有普通的SELECT操作在已提交读和可重复的的隔离级别下都是采用一致性读。
MySQL中的锁定读: 包括共享锁(S锁)和独占锁(X锁)，S和S是兼容的，其他都不兼容，通过在SELECT语句后加LOCK IN SHARE MODE或FOR UPDATE可以显式S锁和X锁。
多粒度锁: 对记录加的锁称为行(级)锁，也可以对表加锁，称为表(级)锁。表锁和行锁也是满足锁的兼容要求的，例如给一个表加了读锁，就不能给表或表的某些行加读锁，给一个表加了写锁，就不能给表或表的某些行加任何的锁。表锁粒度大，实现简单，占用资源少，行锁粒度小，可以实现更精准的并发控制，但会影响性能。
意向锁: 包括意向共享锁(IS锁)和意向排他锁(IX锁)，当给某一行加S锁时，就要给对应的表加一个IS锁，IS锁和X锁不兼容，因此只要看到表有IS锁，就不能给这个表加X锁，而不需要遍历表的每一行；同理IX锁和S锁X锁都不兼容，那么如果给某一行加了X锁，就会给对应的表加IX锁，这样就不能再给这个表加S锁或者X锁。
MySQL中的表级锁: 在对某个表执行增删改查操作时，InnoDB并不会为这个表添加表锁，手动获取表锁的语句是LOCK TABLES t READ/WRITE;，对应的释放锁是UNLOCK TABLES;，一般不用。对于自增列，如何保证生成的数据严格自增不重复，系统有两种选择，一种是使用AUTO-INC锁，在插入语句开始时获取一个表锁，语句结束后释放(不是事务结束后)，使用于不确定插入记录数量的情况；一种是使用更轻量的锁，在生成自增id前加锁，生成后就释放锁，适用于确定要插入记录数量的情况，可以避免锁定表，提升插入性能。
MySQL中的行级锁: InnoDB支持行级锁，且有多种行锁类型，包括Record Lock(LOCK_REC_NOT_GAP)，即普通的记录锁，也有读锁和写锁之分；Gap Locks(LOCK_GAP)，用来在可重复读隔离级别下解决幻读问题的一种方案(另一种是采用MVCC，如果事务中两次读到的都是同一个ReadView，那就肯定不会出现幻读)，对某个记录加Gap锁，就相当于禁止在该记录和前一个记录之间(聚簇索引排序)插入新的记录，这样通过适当地加Gap锁，就可以避免幻读问题的发生；Next-Key Locks(LOCK_ORDINARY)，本质上一个普通记录锁和一个Gap锁的结合，即保护了该记录，又禁止在该记录之前插入新记录；Insert Intention Locks(LOCK_INSERT_INTENTION): 插入意向锁，当要插入一条新纪录而发现已经存在Gap锁，需要插入这个锁结构进行等待，类似于获取锁失败；隐式锁: 通常一个事务插入一条新纪录是不加锁的，这时候另一个事务如果对该记录进行读取或修改就可能产生脏读或脏写的问题，为了解决这个问题，采用隐式锁的方式，即当前插入的记录不加锁，当别的事务想要对该记录加S锁或者X锁时，先查该记录的trx_id是否是当前活跃事务，如果是，帮助该事务给该记录插入一个X锁，然后自己再插入一个等待的锁结构。
内存中的锁结构包括事务信息，索引信息(行锁)，表锁/行锁信息(例如对哪个表加的锁，记录所在表空间，页号)，锁类型等。且在同一个事务中，可能会根据情况，对锁进行复用，并不一定是每一次加锁都创建一个新的锁结构。

// TODO 做一些实验?