总览

　　两年前就买了《高性能MySQL》这本书了，但是一直没有认真看，从今天开始阅读一遍本书，并做好笔记，这本书涵盖的是5.5版本的MySQL，当然更新版MySQL有 更多的知识值得学习，不过5.5已经足够学习重点内容了，希望自己可以掌握核心内容吧。在阅读本书的过程中，会辅导以掘金小册的《MySQL是怎样运行的：从跟上 理解MySQL》和淘宝丁奇的极客时间MySQL专栏一起学习，学习完以后，看一遍何登成的技术博客。

第一章

　　MySQL的并发控制是在两个层面的：服务器层和存储引擎层，回忆MySQL的架构，MySQL服务器层包括接收客户端的连接、查询缓存、查询解析器将查询语句构造为 解析树、优化器等；而存储引擎是一个单独的，可插拔的模块，可以在使用过程中进行切换的，每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。

　　这里提到，MySQL服务器本身会为类似于ALTER TABLE这样的语句使用表锁，而忽略存储引擎的锁机制。行级锁只在存储引擎层实现，MySQL服务器层并没有实现 行级锁。　　

　　谈到事务，就绕不开ACID四大特性（atomicity、consistency、isolation和durability）。这里说的隔离性，是指通常来说，一个事务所做的修改 在事务最终提交之前，对其他事务是不可见的。但事实上，这个与事务的隔离级别有很大关系；而对于持久性，也是一个比较模糊的概念，持久也分为很多的级别。 在《DDIA》一书中其实提到了，一致性严格来说并不是数据库的特性，这里的一致性主要指数据库处于应用程序锁期待的‘预期状态’，所以一致性往往需要 应用程序的逻辑来保证。

　　隔离级别：

• 1、READ UNCOMMITTED（未提交读），指事务中的修改即使还没有提交，对于其他事务也是可见的。也就是会产生脏读（Dirty Read），事务可以读取到未提交的 数据，这也是最低的隔离级别，实际中几乎不会用到。

• 2、READ COMMITTED（提交读），这是大多数数据库系统默认的隔离级别（但MySQL不是），这个就是满足了前面说的隔离性的定义，即一个事务从开始直到提交 之前，所做的操作对于其他事务是不可见的；这种隔离级别也叫不可重复读（nonrepeatable read），因为执行两次相同的查询，可能得到的结果不一样。

• 3、REPEATABLE READ（可重复读），这是MySQL的默认隔离级别，简称rr，rr保证了在同一个事务中多次读取同样记录的结果是一致的，注意这是和上面的不可 重复读的区别，但是解决不了幻读（Phantom Read）的问题，InnoDB通过多版本并发控制（MVCC，Multiversion Concurrency Control）来解决幻读问题。 实际上，是通过间隙锁（next-key locking）来解决幻读问题的。

• 4、SERIALIZABLE（可串行化），这就是强制事务串行，实际中也很少使用。

　　事务日志：存储引擎在修改表的数据的时候，只修改其内存拷贝，再把该修改行为记录到硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志 采用追加的方式，也就是写事务日志是在磁盘的一小块区域顺序I/O，而不是写真实数据那样的随机I/O（需要在磁盘的多个地方移动磁头），所以采用事务日志的方式要 快得多。事务日志持久化以后，内存中被修改的数据再慢慢刷盘，大多数存储引擎都是这么实现的，通常称之为预写式日志（Write-Ahead Logging），因此修改 数据需要写两次磁盘。很明显，事务日志可以帮助系统在崩溃时恢复修改的数据。

　　自动提交（AUTOCOMMIT）：MySQL的默认采用自动提交模式，也就是说，如果不显示的开启一个事务，则每个查询都被当作一个事务来执行。所以一般在dao框架 中，要开启一段事务操作时，都先setAutoCommit为false。修改AUTOCOMMIT对于非事务型的表（比如MyISAM，它并不支持事务）是没有意义的，这种表没有 commit和rollback的概念。所以需要为每张表选择合适的存储引擎，尽量不要在事务中混用事务型和非事务型的表。

　　多版本并发控制：可以理解成是一种轻量级的行级锁，它在大多数情况下避免加锁行为，从而提高并发度。MVCC是通过保存数据在某个时间点的快照来实现的，也就是， 不管事务需要执行多长时间，事务执行过程中看到的数据是一致的（即可重复读）；但是不同事务的开始时间可能不一样，因此不同事务在同一时刻看到的数据可能是不同的。

　　InnoDB的MVCC实现是在每一行记录后面加上两个系统版本号，分别是行的创建时间和过期时间，每开始一个新的事务，版本号都会递增。在SELECT/INSERT/DELETE 以及UPDATE的时候分别对版本号进行操作（细则后续再整理学习），最终使得select出来的记录是符合要求的。MVCC只在REPEATABLE READ和READ COMMITTED这两个隔离级别下工作，其他 两个隔离级别和MVCC不兼容，因为READ UNCOMMITTED总是读取最新的行，而不是符合当前事务版本的数据行。

创建高性能的索引

　　MySQL只能高效的使用索引的最左前缀，因此创建一个包含两列的索引和创建两个只包含一列的索引是完全不一 样的。在MySQL中，索引是在存储引擎层实现的，而不是服务器层，因此不同存储引擎支持的索引类型和工作方式并 不一样。

　　B-Tree索引：一般来说这就是最常见的索引类型，B-Tree索引使用B-Tree数据结构来存储索引数据（事实上大多数存储引擎使用的是B+树）。大多数存储引擎都支持这种索引。这里B-Tree是一个广泛的概念，因为有的存储引擎内部可能 使用的并不是B-Tree这种数据结构，但是也统称为B-Tree索引，典型的就是InnoDB使用B+树。

　　索引列的顺序非常重要。

　　哈希索引：基于哈希表实现，只有精确匹配索引中的所有列才能查询有效（即命中索引），对于每一行数据，存储引擎都会针对所有的索引列计算一个hash code，索引中存储的就是哈希码，哈希表中保存指向每一行数据的指针。目前只有Memory引擎支持哈希索引（当然它也支持B-Tree索引）。

　　InnoDB引擎有一个比较厉害的功能叫做"自适应哈希索引(adaptive hash index)"，当InnoDB注意到某些索引被使用的非常频繁的时候，它会在内存中基于 B-Tree之上再创建一个哈希索引，这样就相当于结合了两种索引的优点，不过这是一个完全自动的、内部的行为。

　　聚簇索引不是一种索引类型，它指的是一种数据存储方式，InnoDB的聚簇索引指的是在同一结构中保存了B-Tree索引和数据行。当表有聚簇索引时，它的数据行存放 在索引的叶子页（leaf page）上。“聚簇"往往表示的是数据行和相邻的键值存储在一起，一个表只能有一个聚簇索引。

　　比较快速的看完这本书的前六章，发现这本书是有一些难度的，决定从11月28号开始看《MySQL是怎样运行的》一书，补充下基础知识。

分界线，进入《MySQL是怎样运行的》书

第一篇

　　先介绍了怎么安装MySQL服务端，以及如何启动服务端，然后在客户端使用相关的命令连接服务端。关于客户端和服务端连接的过程以及方式值得注意一下，这个连接的过程本质上是一个进程间通信的过程，所以不只是通过TCP socket来连接 这一种方式，尽管这是我们最常用的方式。所以MySQL支持的客户端进程和服务端进程通信的方式主要有以下几种：

• 1、TCP/IP，其实也就是通过ip地址 + 端口号的方式，这是最常用的，因为往往生产环境中MySQL服务端和客户端是处于不同机器的；
• 2、命名管道和共享内存，这是Windows支持的通信方式，都是通过在启动服务端程序时加上某些特定参数来实现的，需要注意的是如果使用共享内存来通信的话，客户端和服务端进程必须在同一台Windows主机上。
• 3、Unix域套接字文件：Unix域套接字用于在同一台机器上运行的进程间的通信，MySQL支持这种通信方式也是在启动服务端和客户端程序时加上某些特殊命令来实现的，这个和因特网套接字（TCP socket）不同之处在于：Unix域套接字 效率更高（当然要求通信双方位于同一台机器），它只是复制数据，没有协议处理解析、计算校验和、不需要序列号确认号（反正就是TCP的那一整套）。关于Unix域套接字的全面学习需要参考书籍《Unix环境高级编程》。

　　服务器处理客户端请求时，往往需要经过三个部分：连接管理、解析与优化、存储引擎。服务端每收到一个新的客户端请求时，都会使用一个新的线程来专门处理与这个客户端的交互。这里你可以回顾一下并发现，MySQL服务端并不使用 reactor这种网络IO模型来处理连接，而是使用线程池，来为每一个连接分配一个线程干活，其实epoll出现的也比MySQL要晚，那你可以思考一下：

　　问题1：MySQL服务端可以使用epoll吗？如果使用epoll会比现在的线程池（one-connection-per-thread）更好吗？

　　解析与优化主要又包括了三个部分：查询缓存、语法解析（将你的sql语句构造为解析树）和查询优化。首先查询缓存功能在5.6的版本已经是默认关闭了，而且后面会被删除；查询缓存只有在两个查询请求完全一样的情况下才会命中（全部 字符），而且如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql 、information_schema、 performance_schema 数据库中的表，那这个请求就不会被缓存。一旦某张表的数据或者结构被修改，那么这 张表的所有缓存都会失效。show variables like ‘%query_cache%'; 这个命令可以查看缓存情况，目前umc是开启了查询缓存的。关于MySQL8.0以后关闭查询缓存，官方有一篇解释 文章MySQL8.0关闭查询缓存。

　　语法解析，就是将sql语句转换为内部的一棵解析树，本质上是一个代码编译过程，涉及到词法分析、语法分析、语义分析等。查询优化就是优化器会对我们的查询语句进行优化，提高查询效率。

　　存储引擎：MySQL服务器把数据存储和提取操作封装到了存储引擎这个模块里，我们看到的表和每一行数据记录是怎么在磁盘上存储的，是由存储引擎决定的，不同存储引擎的实现方式并不一样。为了管理方便，人们把连接管理、查询缓存、 语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL server的功能，把真实存取数据的功能划分为存储引擎的功能。不同的表可以使用不同的存储引擎。

第二篇：启动选项和配置文件

　　这一篇文章主要介绍的是服务端在启动MySQL时，可以指定的一些系统变量，看过一遍后基本都能理解，如果用得到可以作为查询章节使用。

第三篇：字符集和比较规则

　　字符集就是一堆字符的集合，不同的字符集包括的字符是不一样的，有的多一些，有的少一些；编码就是将字符转变为字节，解码就是逆过程。比较规则指的是如何对比两个字符的大小，同一种字符集可以有多种不同的比较规则，一些比较常见 的字符集如下：

• 1、ASCII字符集：共收录128个字符，包括键盘上的常见字符，使用1个字节来编码。
• 2、ISO 8859-1字符集：共收录256个字符，它是在ASCII字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母)，也可以使用1个字节来进行编码。这个字符集也有一个别名latin1。
• 3、GB2312字符集：收录了一些希腊字母、汉字等，兼容ASCII码的编码方式，使用1~2个字节来编码一个字符。
• 4、GBK字符集：在2312的基础上又增加了字符，编码方式仍然一样。
• 5、Unicode字符集：集兼容ASCII字符集，采用变长编码方式，编码一个字符需要使用的字节不等。有常见的utf-8、utf-16、utf-32等多种编码方案，utf-8使用1~4个字节编码一个字符，utf-16使用2~4个字节，utf-32使用4个字节。 MySQL中把这几种都统称为utf字符集。关于字符编码问题，有道云笔记上有几篇之前梳理过的文章。

　　MySQL中的utf8只使用了1~3个字节来表示字符，而utf8mb4是正宗的utf8字符集，使用的是1~4个字节，因此它可以存储emoji表情。MySQL支持的每一种字符集都有一个自己默认的比较规则，比如utf8对应的默认比较规则是 utf8_general_ci，它是一种通用的比较规则，后面的ci表示case insensitive，即不区分大小写。

　　MySQL有4个级别的字符集和比较规则，分别是服务器级别、数据库级别、表级别和列级别。可以通过一些命令或者配置文件来修改它们。

　　比较规则主要用于比较字符串的大小或者需要排序的时候，比如ORDER BY语句。

第四篇：InnoDB记录的存储结构

　　MySQL中每一行真实数据在不同存储引擎的存储格式是不一样的，这一篇文章主要研究的是InnoDB存储引擎下一行记录的存储格式。需要注意的是InnoDB是将数据划分为多个页的，磁盘和内存的数据交互都是以页为单位的，InnoDB中页 的大小一般是16KB，也就是一般情况下，一次从磁盘读取的数据大小至少是16KB，一次写磁盘也是16KB的数据。

　　每一行记录在磁盘上的存放方式叫做行格式，InnoDB存储引擎现在支持4种不同类型的行格式，分别是Compact、Redundant、Dynamic和Compressed行格式，目前的innodb_default_row_format是Dynamic，行格式可以在创建 或者修改表的时候指定ROW_FORMAT变量来设置。

Compact行格式

　　在Compact行格式中（实际上后面三种也是），一条完整的记录是由两部分组成的：记录的额外信息和记录的真实数据。记录的额外信息是服务器为了描述这条记录不得不添加的一些信息，额外信息又主要分为3类：分别是变长字段长度列表、 NULL值列表和记录头信息，下面分别看一下这3部分额外信息。

变长字段长度列表

　　MySQL支持一些变长的数据类型，如VARCHAR(M)、VARBINARY(M)、TEXT类型以及BLOB类型，变长字段实际存储多少字节的数据是不固定的，所以在存储真实数据的同时也需要把这些数据占用的字节数存储起来，在Compact行格式中， 所有变长字段的真实数据所占用的长度都存放在记录的开头，形成一个变长字段长度列表，各个变长字段数据所占用的字节数按照列的逆序存放，当然这里的列必须是可变长字段的列，固定长度的列就不会存放了。

　　变长字段的实际数据长度可能会比较长，比如大于256个字节了，那么其长度1个字节也无法表示，可能就会需要2个字节来表示真实数据的长度，对于变长类型VARCHAR(M)来说，这种类型表示能存储最多M个字符（注意是字符不是字节）， 另外，对于值为NULL的列的变长字段，这个列表里是不存放其长度的（长度为0）。如果某个表的所有列都不是变长的数据类型的话，那么就不需要变长字段列表了。

NULL值列表

　　Compact将所有值为NULL的列统一起来，存储到NULL值列表中，这样真实数据中就不用管NULL数据了（这里我们就会发现，尽量将列设为NOT NULL，这样每一行记录就不需要这个列表了）。首先统计表中允许存储NULL的列有哪些，其中 主键列、被NOT NULL修饰的列是不允许为空的，然后用一串二进制byte逆序表示某一列是否为空，1代表为NULL

记录头信息

　　记录头信息总共占5个字节，它比较复杂一点，涉及到B+树以及数据页等，暂时不细说。

记录的真实数据

　　事实上，记录的真实数据除了自己定义的那几列以外，MySQL会为每行记录添加一些默认列（隐藏列），具体的列如下：

　　InnoDB表对主键的生成策略是：优先使用用户自定义的主键作为主键，如果用户没有定义主键，则选取一个unique键作为主键，如果表中没有定义unique键的话，则InnoDB会为表默认添加一个名为DB_ROW_ID的隐藏列作为主键，所以 这一列是可选的（在没有自定义主键以及Unique键的情况下才会添加该列）。

CHAR(M)列的存储格式

　　前面提到在Compact行格式下，只会把变长类型的列的实际长度逆序存到变长字段列表中。但是如果我们采用变长的字符集（比如utf8而非是ASCII），也就是表示 一个字符需要的字节数不定，那么如果一个列是CHAR(M)类型的，它的实际长度也会被存储到变长字段长度列表中。也就是说，对于CHAR(M)类型的列来说，当列采 用的是定长字符集（注意字符集是有四个级别的，同一张表的不同列采用的字符集可以不一样），该列占用的字节数不会被存储到变长字段长度列表；而如果采用的是变长 字符集，该列所占用的字节数就会被加入到变长字段长度列表中。

　　还有一点还需要注意，变长字符集的CHAR(M)类型的列要求至少占用M个字节，而VARCHAR(M)却没有这个要求。比方说对于使用utf8字符集的CHAR(10)的列来说， 该列存储的数据字节长度的范围是10～30个字节。即使我们向该列中存储一个空字符串也会占用10个字节，这是怕将来更新该列的值的字节长度大于原有值的字节长度而 小于10个字节时，可以在该记录处直接更新，而不是在存储空间中重新分配一个新的记录空间，导致原有的记录空间成为所谓的碎片。

Redundant行格式

　　这种格式比较老了，现在已经不再使用，比较浪费存储空间，不再记录笔记。

行溢出数据

　　VARCHAR(M)类型的列最多能够占用65535个字节，M代表最多可以存储的字符数量，所以这就取决于我们采用的字符集是什么了，如果是ASCII字符集，那么一个 字符采用一个字节来编码，但是实际上VARCHAR(65535)是不行的，因为MySQL对一条记录占用的最大存储空间是有限制的，除了TEXT和BLOB类型的列以外，其他 所有的列（不包括隐藏列和记录头信息）占用的字节长度之和不能超过65535字节，也就是这65535字节不仅包括真实数据，还需要包括真实数据所占用的字节长 度，以及NULL标识（如果该列被NOT NULL修饰可以没有这部分存储空间）。

　　那么对于ASCII字符，如果该VARCHAR类型的列没有NOT NULL，那么最多只能存储65532个字节的数据，因为真实数据长度位需要占用2个字节，NULL标识需要占用 1个字节；如果该VARCHAR类型的列有NOT NULL，那么最多可以存储65533个字节的数据，不需要NULL标识了。

　　如果这列用的是utf8字符，那么在值允许为NULL的情况下，最多存储的字符是65532/3 = 21844个字符。当然这些都是在表中只有一个字段的情况下说的，也就 是65535字节的限制是一个行中的所有列（不包括隐藏列和记录头信息）占用的字节长度之和。

　　前面提到，MySQL中磁盘和内存的交换单位是页，表中的每一行记录都会被分配到某个页中进行存储，一个页的大小一般是16KB，也就是16384字节，而一个 VARCHAR(M)类型的列最多可以存储65532个字节，也就是一个页都可能存不下一条记录。

　　在Compact和Redundant行格式中，对于占用空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在其他页中，然后在记录的 真实数据位置处用20个字节来指向这些分散页的地址（这20字节还包括分散在其他页中的数据所占用的字节数）。实际上对于这两种行格式，如果某一列的数据非常多的 话，在该记录的真实数据位置只会存储该列的前768个字节的数据和一个指向其他页的地址，然后将剩下的数据存储在其他页中，这个过程就叫做行溢出，存储那些 剩余数据的页叫做溢出页。当然不只是VARCHAR(M)类型的列，TEXT、BLOB类型的列在数据量比较大的情况下也会发生行溢出。

　　MySQL规定一个页至少存放两行记录，因此发生行溢出是有临界点的。

Dynamic和Compressed行格式

　　现在MySQL默认的行格式就是Dynamic，它们的存储方式和Compact很像。只不过在处理行溢出数据的时候有一些区别，它们不会在记录的真实数据处存放真实数据 的前768字节，而是把所有的字节都存放在其他页面中，只是在真实数据位置存放其他页面的地址，而Compressed行格式更进一步，它会采用压缩算法对页面进行压缩 来节省空间。

第五篇：InnoDB数据页的结构

　　前面提到，InnoDB管理存储空间的基本单位是页，一般是16KB；InnoDB设计了不同类型的页，有的页存放表空间头部信息，有的页存放INODE信息，有的页存放INSERT BUFFER等。存放表中记录的页叫做数据页，但其官方称呼是索引页。 这一篇主要是讲这种数据页的结构，以及存储的内容。

　　数据页这16KB的大小一般被划分为7个部分，具体内容如下：

User Records 和 Infimum + Supremum

　　在页的7个组成部分中，我们自己存储的记录会按照指定的行格式（Dynamic）存储到User Records部分。但是在一开始生成页的时候，是没有User Records这个部分的，每当我们插入一条记录，都是从Free Space部分申请一个记录 大小的空间划分到User Records部分，当Free Space的空间耗尽时，就意味着这一页使用完了，如果还有新的记录需要插入的话，就需要去申请新的页了。

　　InnoDB如何在页中去存储这些记录的呢？这里涉及到的就是上一章没有细讲的记录头信息，它一共使用5个字节，和实际数据以及隐藏列等一起存储在User Records部分，其具体含义如下：

• delete_mask：占用1bit，标识该记录是否被删除，因此即使你delete了某一条记录，但它实际上可能还在磁盘上，如果直接从磁盘上移除然后重排其他记录会比较消耗性能，因此只是打一个tag，所有被删除的记录会组成一个垃圾链 表，这个链表中的记录所占用的空间叫做可重用空间，之后如果有新的记录插入到表中，就可能将这些被删除的记录所占用的存储空间覆盖掉。这里值得注意的是，将delete_mask置位1和将被删除的记录加入到垃圾链表其实是两个过程。

• min_rec_mask：B+树的每层非叶子节点中的最小的记录都会添加这个标记。

• n_owned：后续再讲。

• heap_no：表示当前记录在当前页中的位置。一般第一条真实记录从2开始，因为heap_no值为0和1的两个记录分别就是最小记录和最大记录（位于Infimum + Supremum部分，不是User Records部分），InnoDB自动给每一页生成了这 两个虚拟记录，一个代表最小记录，一个代表最大记录。对于一条完整的记录来讲，比较记录的大小就是比较其主键的大小。这两条记录的结构是固定的，都是5字节大小的记录头信息加上8字节的一个固定部分。这两条记录并不存放在 User Records部分，它们单独存放在Infimum + Supremum部分。heap_no越小，代表当前记录越小。最小记录和最大记录的heap_no分别是0和1，也就是它们的位置最靠前。

• record_type：表示当前记录的类型，一共有4种。0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录。后续再详细解释record_type为1的情况。

• next_record：它表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量，本质上是个链表，可以通过当前记录链接到下一条记录，这里需要注意的是下一条记录指的是主键从小到大的下一条记录，而不是插入顺序的下一 条记录。而且规定了，Infimum（最小记录）的下一条记录是当前页中的真实记录中主键最小的记录，当前页中主键最大的记录的下一条记录是Supremum（最大记录），这样就把页中的每一条记录串起来了。也就是从最小记录开始形成了一个单链 表，一直到最大记录，最大记录的next_record为0，表示没有下一条记录了。

　　假设此时我们删除第2条记录，那么会发生下面几件事：

• 第2条记录并没有从存储空间中移除，而是其delete_mask被设置为1；
• 第2条记录的next_record被设置为0，表示被删除记录没有下一条记录了；
• 第1条记录的next_record指向了第3条记录，其实这两部就是从一个链表中删除一个节点的操作；
• 最大记录的n_owned会减1，这个后面再说n_owned的含义。

　　删除后，再将刚刚删除的数据插入，那么新插入的记录就会复用刚刚的存储空间。如果删除的记录增多后，这些被删除记录的next_record就会组织起来，构成一条垃圾链表，以备后续新插入记录重复使用。

Page Directory（页目录）

　　理解了上面User Records部分记录的存储方式以后，我们知道每一个页中的所有记录在逻辑上是一个单链表，按照主键的大小排序依次串起来的。现在假设我们有一条非常简单的sql语句：

SELECT * FROM page_demo WHERE c1 = 3;

　　最笨的办法就去从最小记录开始遍历这个链表，依次比较这一列的值是否符合where条件，但实际上，我们很容易就可以想到用一个二分查找来减少比较次数，但是很明显，链表不支持random access，是无法高效利用二分的，所以InnoDB 引入了一个页page directory的概念，制作了一个目录，过程如下：

• 1、将页中所有的记录（包括最小和最大记录，但是不包括被删除的记录）划分为几个组；
• 2、每个组内的最后一条记录（组内最大的那条记录）的记录头中的n_owned表示该组内共有几条记录；
• 3、将每个组内的最后一条记录的地址偏移量单独提取出来，按顺序存储到靠近页的尾部的地方，这个地方就是page directory，页目录中这些地址偏移量被称作槽(slot)，页面目录就是由一系列的槽组成的。也就是槽的数目就是这个 页中的组的数目。

　　关于如何划分分组，InnoDB有一些约束，规定了每个分组的记录条数：对于最小记录所在的分组，只能有一条记录（即自身）；最大记录所在的分组，拥有的记录条数在1~8之间；其他分组记录的条数在4~8之间。所以分组的步骤如下：

• 1、初识情况下，一个数据页中只有最小记录和最大记录两条，它们属于两个不同的分组，也就是页目录中只有两个分组，即两个槽。
• 2、之后每插入一条记录，都会从页目录中找到主键值比本记录的主键值大且差值最小的槽，然后将槽的n_owned值加1，表示本组内又添加了一行记录，直到该组内的记录数变为8。
• 3、在一个组内的记录数等于8个后，再插入一条记录时，会将组中的记录拆成两个组，一个组4条，另一个组5条记录，这会导致在页目录中新增一个槽来记录新的组中最大记录的偏移量。

　　现在有了页目录后，页目录中各个槽代表的记录的主键值都是从小到大排列的，这样就可以使用二分查找了。

Page Header（页面头部）

　　InnoDB在页面头部部分存储了一些这个页中记录的一些公共信息，比如本页中存储了多少条记录，第一条记录的地址是什么等，这个部分固定为56字节，具体每个字节的含义如下表：

　　PAGE_DIRECTION：假如新插入的记录的主键值比上一条记录的主键值大，则说这条记录的插入方向是右边，反之是左边；PAGE_DIRECTION就是记录最后一条记录插入的方向。

　　PAGE_N_DIRECTION：假设连续几次插入记录的方向都是一致的，InnoDB用这个变量来记录沿着同一方向插入记录的条数，当然如果方向改变，这个值就会清零。

File Header（文件头部）

　　Page Header是专门针对数据页中各个记录的信息，File Header则是针对各种类型的页都通用（不只是数据页），不同类型的页都会以File Header作为 第一个组成部分，它描述了一些针对各种页都通用的信息，这个部分固定占用38个字节，这里不再详细列出其每一项了，只挑几个重点的理解一下：

• FIL_PAGE_SPACE_OR_CHKSUM：代表当前页面的校验和，占用4字节，一个代表页面内容的相对短的字符串；

• FIL_PAGE_OFFSET：每一个页都有一个单独的页号id；

• FIL_PAGE_TYPE：表示页类型，不同的页的功能也不一样，前面介绍的都是InnoDB数据页（索引页），它是十六进制表示是0x45BF，比如Undo日志页是0x0002。

• FIL_PAGE_PREV和FIL_PAGE_NEXT：上一个页的页号和下一个页的页号。这个就相当于一个双向链表，将其他的页串联起来了，而这些夜并不需要物理位置相邻，值得注意的是，并不是所有类型的页都有这两个属性，但是数据页（索引页）， 也就是页类型为FIL_PAGE_INDEX的是有这两个属性的，也就是说所有的数据页构成了一个双向链表。

File Trailer（文件尾部）

　　和File Header一样，File Trailer也是针对所有类型的页都通用的。它的设计目的是：InnoDB存储引擎在磁盘和内存之间交换数据的单位是页，将数据以页为单位加载到内存中，然后修改，然后再后面的某个时间将页同步到磁盘中， 那么在这个同步的过程中，防止还没有同步完成就发生故障，也就是检测一页页是否完整，InnoDB在每个页的尾部增加了一个File Trailer，固定占用8字节，它也分为两个部分：

• 前面4字节：表示页的校验和，这个和File Header中的校验和是相对应的。每当一个页面在内存中修改了，在同步之前就要把它的校验和算出来，因为File Header在页的前面（页的第一个组成部分），而且校验和又位于File Header的第 一位置，那么文件头部中的校验和就是最先被同步到磁盘中的，当页完全写完的时候，尾部的校验和也写到磁盘中，如果完全同步成功，则页的首部和尾部的校验和应该是一致的。如果写了一半儿断电了，那么在File Header中的校验和就代表着已 经修改过的页，而在File Trialer中的校验和代表着原先的页，二者不同则意味着同步中间出了错。

• 后面4字节：代表页面被最后修改时对应的日志序列位置（LSN），这部分也是为了校验页的完整性的。

第六篇：B+树索引

　　上一篇讲了一个数据页的七大构成部分，我们也知道了在一个页中如何查找某个特定主键的记录；后面我们知道所有的数据页是通过双向链表串联起来的，数据页内部 的每一条记录会按照主键的大小构成一个单向链表，每个数据页内部都会为这些记录生成一个页目录，在通过主键查找某一条记录的时候，先在页目录中使用二分法快速定位 到记录所在的槽，然后遍历该槽对应的分组所有的记录（最多也就8条），一一对比最终找到记录。那么如何在海量的数据页中查找到特定记录呢？

简单索引

　　在不考虑索引的情况下，在一个页中进行查找，可以根据where条件列的不同分为两种情况：

• 以主键为搜索条件，就是前面描述的：先查找页目录，再遍历对应分组内的记录，一一对比；
• 以其他列作为搜索条件，前面我们知道，在数据页中，对于非主键列并没有建立所谓的页目录，那么无法找到对应的槽，只能从最小记录开始依次遍历，效率较低。

　　如果记录数比较多，需要多个页才能完全存储，那么这个问题就升级为如何在多个页中查找记录，可以分为两步：

• 1、定位到记录所在的页；
• 2、从所在的页中查找相应的记录。

　　第2步属于我们已知场景了，现在的问题在于第1步，数据页都是通过双向链表链接在一起的，我们不可能从第一页开始一直沿着双向链表去遍历， 但是如何才能快速定位到所在页呢？这就是索引的作用。

　　如果要快速定位到查询的记录在哪些数据页（注意，其实满足我们查询条件的记录可能有多条）中，那我们也可以模仿页目录的做法，针对多个数据页建立另一个目录，它必须满足下面的条件：

• 下一个数据页中用户记录的主键值必须大于上一个页中的用户记录的主键；这里配合着原文的举例更容易理解。这个约束表明，我们在对页中的记录进行增删改查的时候，可能会导致记录在页中的移动来维持这个状态，这种现象叫做页分裂

• 给所有的页建立一个目录（类似页中的page directory）：我们知道数据页之间并不是物理相邻的，它们通过两个指针串起来，因此数据页的页号也并不是连续的。当向一张表里连续插入多条记录后，可能存在的情况就是多个页，每个页内 有一些记录，这些页并不连续。现在就需要针对每个页建立一个目录项，每个目录项包含两个部分：

  • 页号，用page_no表示；
  • 页中的所有用户记录中最小的主键值，用key来表示。

　　现在每个页都有了一个目录项，我们只需要把这些目录项放在一起，就可以同样实现根据主键值快速查找某条记录的功能了（方法还是先二分，再去页内查找）。这个目录就叫做索引。需要明确的是，现在是一张表的所有记录保存在多个 数据页中，页之间通过指针相连，每个数据页都有一个目录项（如上面提到的）；每个页内包括一些分组，每个组内包括多条记录（最多8条），根据主键针对所有组建立了一个目录（page directory），这是整个对应关系。这里需要注意的是， 在页内查找记录时，如果不是根据主键，那么是无法利用到page directory的，只能依次去比较，这样比较的次数会大增（毕竟一个页中可以存储非常多的记录）。

InnoDB中的索引方案

　　上面这个简单的索引方案会有一个比较明显的问题：一个页只有16KB，而用户记录非常多的时候，页的数量是极其庞大的，对应的目录项也就会非常多，那么我们是无法找到连续的存储空间来放下所有的目录项的；而且，我们经常会针对记录 进行增删改查，如果删除了中间某一个页的所有记录，那么这个页对应的目录项也应该被删除，这样就要移动后面所有的目录项，这样操作非常消耗内存，是明显不太合理的。所以InnoDB采用了一种非常灵活的方式，来管理这些目录项。

　　InnoDB将这些目录项也看做是用户记录，因为它们其实长得是差不多的，只不过目录项的两列分别是页号和页中最小记录的主键而已，所以其实是可以复用之前存储用户记录的方式，来存储目录项的。为了区分开它们，把这些表示目录项的记录 称作目录项记录，这种记录的记录头里的record_type字段值就是1，表示目录项记录（即B+树非叶子节点记录，可以看到其实叶子节点存储的是用户记录本身），0表示普通用户记录。

　　既然每一个目录项也是一条"用户记录”，那么这些"用户记录"也可以正常放到数据页中，具体可以对比着原文的图来看。这样就可以新分配一个页来专门存储目录项 记录，这里需要再次注意一下目录项记录和普通的用户记录的不同点在于：

• （1）普通用户记录的record_type为0，目录项记录的record_type为1；
• （2）目录项记录只有页号和主键（页中用户记录中最小的主键）两个列；而普通用户记录的列是自定义的，当然还包括InnoDB自动添加的隐藏列；
• （3）记录头信息中除了record_type以外，还有一个min_rec_mask字段，只有在存储目录项记录的页中主键值最小的目录项记录的min_rec_mask值为1， 其他别的记录这个值都为0。

　　除了上面三条以外，目录项记录和普通的用户记录就没有什么区别了，它们都是使用数据页（即页面类型都是0x45BF，这个属性位于File Header中），页面的组成 结构还是前面提到的7大部分，而且都会为主键值生成page directory（页目录），从而在按照主键进行查找时都可以使用二分查找来加快速度。

　　这里衍生出的新的问题是：现在即使目录项记录只存储了主键值和对应的页号，但是由于一个页只有16KB，所以目录项记录还是会有非常多，那么一个页也会放不下 所有的目录项记录，所以我们可能需要多个页来存放目录项记录。比如下面的一种情况：

　　新插入一条记录，然而原来的四个存储用户记录的数据页已经存满了，那么现在首先就需要新增加一个数据页，来存放这条用户记录；但是原先存放目录项记录的数据 页也满了，因为现在多了一个页，也就多了一个目录项，所以我们还需要再生成一个数据页，来存放这个新增的目录项。注意此时存放目录项记录的两个页，仍然会通 过一个指针分别指向对方（满足我们的双向链表要求，对比着原图来看）。现在存储目录项记录的页已经不止一个了，那么我们再次根据主键查找一条用户记录时，大致 需要3个步骤：

• 1、确定目录项记录页；
• 2、通过目录项所在的页来确立用户记录真实所在的页：通过二分查找（利用page directory）；
• 3、在用户记录真实所在的页中查找一条具体的记录：还是二分查找。

　　上面提到的第一步是需要定位到存储目录项记录的页，但是如果我们有非常多的存储目录项记录的页，它们往往也并不是连续的，那我们就需要为这些存储目录项记录 的页再生成一个更高级的目录，也就是再生成一个页来存储这些目录项记录页，这样一直嵌套下去。最终这样的一种组织数据的方式，这样的数据结构就叫做B+树。

　　经过上面的过程，不论是存储用户真实记录的页，还是存储目录项记录的页，都存储在B+树这个数据结构中了，可以看出，实际的用户记录都是存储在了B+树的最底层 节点上，即叶子节点；存放目录项记录的的节点就是非叶子节点，最顶层叫做根节点。这样的一棵B+树是非常扁平的，也就是可以存放的记录数是非常多的。一般情况下， B+树都不会超过4层，通过主键值去查找某一条记录的时候，最多只需要做4个页面内的查找（查找3个目录项页和一个用户记录页），而且每个页面内部都有page directory，所以在页面内部都可以使用二分查找来快速定位。

聚簇索引

　　前面的B+树本身就是一个目录，或者说一个索引，它有两个特点：

• 1、使用记录主键值的大小进行记录排序、页排序，它的含义是：

  • 页内的记录是按照主键的大小顺序构成了一个单向链表；
  • 各个存放用户记录的页也是按照页中用户记录的主键大小顺序构成了一个双向链表；
  • 存放目录项记录的页分为不同层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序构成了一个双向链表。

• 2、B+树的叶子节点存储的是完整的用户记录（所有列的值，包括隐藏列）。

　　满足这两个特性的索引，或者说满足这两个特性的B+树，就叫做聚簇索引。所有完整的用户记录都存放在聚簇索引的叶子节点处，这种聚簇索引并不需要我们 显示创建，InnoDB存储引擎会自动为我们创建聚簇索引。其实在InnoDB中，聚簇索引就是数据存储的方式（索引即数据，数据即索引）。

二级索引

　　前面介绍的聚簇索引，只能在搜索条件是主键时发挥作用，因为这棵树的数都是按照主键大小进行排序的，如果想以别的列作为搜索条件，那么就可以多建立几棵B+ 树，不同的B+树中采用不用的比较规则，比如针对c2列（非主键列）构建了一棵B+树，这棵树和前面的聚簇索引会有一些如下的区别：

• 1、使用c2列的大小进行排序的
• 2、B+数的叶子节点存储的并不是完整的用户记录，而是c2列 + 主键这两个列的值。
• 3、目录项记录中也不再是主键 + 页号了，而是c2列 + 页号。

　　所以现在如果想通过c2列的值查找某些记录的话，还是和之前的步骤一样，但是你在这棵B+树中，你最后定位到的叶子节点中的"真实用户记录"只存储了c2列 + 主 键，也就是说必须通过主键值去聚簇索引中再查找一遍完整的用户记录，这个过程称为回表，也就是根据c2列的值查询一条完整的用户记录需要查找2棵B+树。

　　这里需要考虑为什么这棵B+树的叶子节点中只存放对应列和主键，如果把完整的用户记录存储到叶子节点中，是不是就不用再回表了？答案是对的，但是这样做 非常耗费存储空间，相当于每建立一棵B+树就需要把所有的用户记录再拷贝一遍。因为这种按照非主键列建立的B+树需要一次回表操作才可以定位到完整的用户记录，所 以这种B+树也称为二级索引，这棵B+树也称为为c2列建立的索引。

联合索引

　　也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引。比方说我们想让B+树按照c2和c3列的大小进行排序，这个包含两层含义：

• 先把各个记录和页按照c2列进行排序。
• 在记录的c2列相同的情况下，采用c3列进行排序。

　　为c2和c3建立的联合索引，这棵B+树值得注意的有下面几点：

• 每条目录项记录都是由c2、c3、页号这三个部分组成，各条记录先按照c2列进行排序，如果c2列的值相同，则按照c3列进行排序；
• B+树的叶子节点存储的是c2、c3和主键列。

　　以c2和c3列大小作为排序规则建立的B+树称为联合索引，当然它也是一个二级索引。它和分别为c2和c3列建立索引的表述是不一样的，分别建立是2棵树。

InnoDB的B+树索引注意事项

　　前面我们学习的过程一直是自底向上的，先有用户记录，再有目录项，再一直向上，实际上B+树的形成过程是这样的：

• 每当为表创建一个B+树索引（聚簇索引不需要用户创建，默认会生成）的时候，都会为这个索引创建一个根节点页面（16KB的数据页），最开始表中没有数据的时候， 每个B+树索引对应的根节点也是空的，没有用户记录，也没有目录项记录。
• 向表中插入记录，就先把用户记录存储到根节点中。
• 当根节点页面的可用空间用完的时候，继续插入记录，此时会将根节点中的所以记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂操作，得到 一个新页b，这时新插入的记录根据键值（聚簇索引中的主键值，二级索引中对应索引列的值，取决于这棵B+树是什么类型的）的大小就会被分配到a或者b中，而根节点 就升级为存储目录项记录的页。也就是只要有超过一个数据页，那么就会产生一个目录项记录页。

　　这个过程值得注意的是：一棵B+树索引的根节点自诞生之日开始，就不会再移动。这样只要我们对某个表建立一个索引，那么这个索引对应的根节点的页 号便会被记录到某个地方，凡是后续InnoDB存储引擎需要使用到这个索引的时候，都会从这个固定的地方取出根节点的页号，从而来访问这个索引。事实上，这个存储 某个索引根节点在哪个页面中的信息就是数据字典的一部分。

内节点中目录项记录的唯一性：这个问题在二级索引中非常重要，前面我们提到二级索引中非叶子节点（也就是存储目录项的那些页）存储的是索引列 + 页号， 但是c2列的值是可能重复的，这样就导致我们在插入一条记录的时候，对比一下发现不知道插入到哪个页中，所以这里需要修改一下。我们需要保证在B+树的同一层内 节点的目录项记录除页号这个字段以外是唯一的，所以我们把主键也加进去，也就是实际上二级索引中目录项存储的是 索引列 + 页号 + 主键。

　　InnoDB规定了一个数据页至少存放两条记录。前面在说行格式的时候也提到了这个规定，依据这个结论推导了在表中只有一列的时候，该列在不发生行溢出的情况下 最多可以存储多少个字节。

MyISAM的索引方案

　　InnoDB中索引即数据，也就是说聚簇索引的那棵B+树的叶子节点已经包含了所有完整的用户记录了，而MyISAM的索引方案虽然也使用树形结构，但是却把数据和索 引分开存储了。

• 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。 我们可以通过行号而快速访问到一条记录。

• 使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不 是完整的用户记录，而是主键值 + 行号的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录！这一点和InnoDB是完全不相同的，在InnoDB存储引 擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级 索引！

• 如果有需要的话，我们也可以对其它的列分别建立索引或者建立联合索引，原理和InnoDB中的索引差不多，不过在叶子节点处存储的是相应的列 + 行号。这些索引 也全部都是二级索引。

MySQL中创建和删除索引

　　InnoDB和MyISAM都会自动为主键或者声明为unique的列去建立B+树索引，只有主键是聚簇索引（也就是记录本身只存在一棵树中），对于其他列建立索引 需要手动建立。

第七篇：B+树索引的使用

　　上一篇比较详细的介绍了InnoDB的B+树索引，回顾一下有下面几条结论：

• 每个索引都对应一棵B+树，B+树分为好多层，最下边一层是叶子节点，其余的是内节点。所有用户记录都存储在B+树的叶子节点，所有目录项记录都存储在内节点。

• InnoDB存储引擎会自动为主键（如果没有它会自动帮我们添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。

• 我们可以为自己感兴趣的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的 用户记录。

• B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前 边的列排序，如果该列值相同，再按照联合索引后边的列排序。

• 通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory（页目录），所以在这些页面中的查找非常快。

　　必须要明确的一个问题是，索引是有代价的。空间上每建立一个索引都会建立一棵B+树，每一棵B+树的每一个节点都是一个16KB大小的数据页，所以会占用比较大的存储空间；时间上在针对用户记录进行增删改时，都可能涉及到索引的变化，可 能涉及到页分裂、页回收等各种消耗性能的操作。所以，索引并不是越多越好的。并不是所有的查询语句都能用到我们建立的索引，针对B+树索引适用的条件，认真阅读这篇文章，这里不再详细记录。

回表的代价

　　 　　回表的代价在于，根据二级索引查找到记录，这部分是顺序IO，会比较快；但是二级索引中对应的主键并不一定是有序的了，这次再去聚簇索引中查找完整记录时，就可能会去物理上不相邻的各个页面上查找，那么这部分查找就是随机IO，随机 IO是比较慢的。因此，需要回表的记录数越多，使用二级索引的效率就越低，甚至MySQL会宁愿使用全表扫描，也就是直接去扫描聚簇索引而不是二级索引。比方说name值在Asa～Barlow之间的用户记录数量占全部记录数量90%以上，那么 如果使用idx_name_birthday_phone_number索引的话，有90%多的id值需要回表，这不是吃力不讨好么，还不如直接去扫描聚簇索引（也就是全表扫描）。

　　那什么时候采用全表扫描的方式，什么时候使用采用二级索引 + 回表的方式去执行查询呢？这个就是传说中的查询优化器做的工作，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要 回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。当然优化器做的分析工作不仅仅是这么简单，但是大致上是个这个过程。一般情况下，限制查询获取较少的记录数会让优化器更倾向于选择 使用二级索引 + 回表的方式进行查询，因为回表的记录越少，性能提升就越高，比方说可以在查询上增加limit限制。

　　或者说，在查询列表中只包含索引列（即覆盖索引），不要select *，那么也就只在二级索引中查找即可，不需要再回表去聚簇索引中查找。 这就是不鼓励使用select * 的主要原因！！！

第八篇：MySQL的数据目录

　　这一篇也就是看MySQL中的所有数据（不只是用户记录，还有运行时的各种数据）是怎么存放在磁盘上的，或者说怎么和文件系统打交道的，很明显不同存储引擎的实现方式并不一样。

　　MySQL的数据目录可以用下面这个命令查看，即数据目录是放在了datadir这个变量里的。

SHOW VARIABLES LIKE 'datadir';

　　每新建一个数据库的时候，就会在数据目录下新建一个子目录（和数据库名同名），也就是不同database对应着不同的子目录；与此同时，还会在和数据库名同名的子目录下创建一个名为db.opt的文件，这个文件包含了数据库的各种属性，比 如数据库的字符集等。

　　一般来说，除了我们手动创建的数据库以外，MySQL还会自带一些系统数据库，分别是：mysql、performance_schema、information_schema等

表在文件系统中的表示

　　每个表的信息其实包括两个部分：一是表结构的定义（表中有多少列，每一列的数据类型是什么，这个表的约束以及索引，表使用的字符集以及比较规则等）；二是表中的数据（用户记录）。

• 为了保存表结构的信息，不论是InnoDB还是MyISAM都在数据目录对应的数据库子目录下创建了一个专门的文件，文件名是表名.frm，也就是不同存储引擎的实现方式是一样的。
• 但是对于表中的数据的存储，InnoDB和MyISAM的存储方式就不太一样了。

　　对于InnoDB，它是使用页为基本单位来管理存储空间的，为了更好的管理这些页，InnoDB提出了一个表空间或者文件空间（英文名：table space或者file space）的概念，这个表空间是一个抽象的概念，它可以对应文件系统上一个或多 个真实文件（不同表空间对应的文件数量可能不同）。每一个表空间可以被划分为很多很多很多个页，我们的表数据就存放在某个表空间下的某些页里。表空间又分为几种不同的类型，这里参考原文章即可。

　　对于MyISAM，不像InnoDB的索引和数据是一个，在MyISAM中的索引全部都是二级索引，该存储引擎的数据和索引是分开存放的。所以在文件系统中也是使用不同的文件来存储数据文件和索引文件。而且和InnoDB不同的是，MyISAM并没有什 么所谓的表空间一说，表数据都存放到对应的数据库子目录下。假如test表使用MyISAM存储引擎的话，那么在它所在数据库对应的数据库目录下会为test表创建这三个文件：

• test.frm
• test.MYD
• test.MYI 　　 　　其中test.MYD代表表的数据文件，也就是我们插入的用户记录；test.MYI代表表的索引文件，我们为该表创建的索引都会放到这个文件中。

视图在文件系统中的表示

　　MySQL中视图就是虚拟的表，也就是查询语句的一个别名而已，所以在存储视图的时候是不需要存储真实数据的，只需要把它的结构存储起来就可以了。和表一样，描述视图结构的文件也会被存储到所属数据库对应目录的下边，对应一个 视图名.frm的文件。

其他的文件

　　除了上面的用户自己存储的数据以外，数据目录下还有一些其他文件，如服务器进程文件，服务器日志文件（错误日志、二进制日志、redo日志等）。

第九篇：InnoDB的表空间

　　这一章的内容非常多，而且比较繁杂，要一次记住并不容易，下面进行梳理核心内容。

　　其实上一章有一个知识点，在旧版本的MySQL中，MySQL所有表的数据都会被存储到系统表空间中，它可能对应文件系统中一个或者多个实际的文件；在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空 间中，而是为每一个表建立一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间。使用独立表空间来存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同，只不过添加了 一个.ibd的扩展名而已，所以完整的文件名称是这样：表名.ibd。

　　对于一个表空间来讲，它们本质上是由海量的页组成，但是这样的划分太粗糙了，会有一些问题，因此这一章主要讲的就是表空间和页的对应关系，在表空间下还有几个其他的层级，最后才是16KB大小的页。

　　前面提到，InnoDB是以页为单位管理存储空间的，前面我们说的聚簇索引以及二级索引都是以B+树的形式保存到表空间的，B+树的节点都是数据页，页类型是FIL_PAGE_TYPE（因为都是存放索引，官方名称是索引页）。除了这种存放数据索 引的页面类型以外，InnoDB也还有一些其他类型的页面。一个16KB的任意类型页，除了38字节的File Header和8字节的File Trailer通用部分以外，剩下的16338字节根据页类型的不同而不同，数据页剩下的就是那5个部分，其他类型的页 则不一定了。

　　File Header有一个页号部分（FIL_PAGE_OFFSET），占4字节，表空间中每一个页都对应一个页号，也就是一个表空间最多可以拥有2^32个页，按照一个页16KB大小来计算，一个表空间最多支持64TB的数据。表空间的第一个页的页号为 0，之后的页号分别是1，2，3…依此类推。某些类型的页可以组成链表，链表中的页可以不按照物理顺序存储，而是根据FIL_PAGE_PREV和FIL_PAGE_NEXT来存储上一个页和下一个页的页号。需要注意的是，这两个字段主要是为了INDEX类 型的页，也就是我们之前一直说的数据页建立B+树后，为每层节点建立双向链表用的，一般类型的页是不使用这两个字段的。

独立表空间结构

　　独立表空间和系统表空间的结构比较相似，但是系统表空间多存放了一些关于整个系统的信息，因此独立表空间要相对简单一些，先来研究一下。

区（extent）的概念

　　表空间中的页实在是太多了，为了更好地管理这些页，InnoDB提出了区的概念，对于16KB的页来说，连续64个页就是一个区，也就是一个区默认占用1MB空间大小。不论是独立表空间还是系统表空间，都可以看做是若干个区组成的，每 256个区又被划分为1组。也就是现在的层级概念是 表空间—>组—>区—>页。

　　现在表空间就被划分为若干个组了，每个组包含256个区，每个区又包含连续的64个页。这些组的头几个页面的类型是类似的：

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

段（segment）的概念

　　为什么表空间要划分这么多级，其实从理论上讲，不引入区的概念只使用页的概念对存储引擎的运行没有影响。但是考虑到数据页非常多的时候，双向链表上相邻的两个 页可能物理位置上隔得比较远，这样在范围查找时，就会造成大量的随机IO。所以我们应该尽量让链表中相邻的页在物理位置上也相邻，尽量利用顺序IO。

　　基于上面的考虑，我们才引入了区（extent）的概念。一个区就是在物理位置上连续的64个页，在表中的数据量比较大的时候，为某个索引分配空间就不再是按照页 为单位了，而是按照区为单位，甚至一次可以分配多个连续的区，尽管这样可能会造成一些空间浪费。

　　更进一步的说，前面提到的范围查询，其实是对B+树叶子节点中的记录进行顺序扫描，而如果不区分叶子节点和非叶子节点，统统把节点代表的页面放到申请到的区中 的话，进行范围扫描的效果就大打折扣了。所以InnoDB对B+树的叶子节点和非叶子节点进行了区别对待，也就是说叶子节点有自己独有的区，非叶子节点也有自己独 有的区。存放叶子节点的区的集合就算是一个段（segment），存放非叶子节点的区的集合也算是一个段。也就是说一个索引会生成2个段，一个叶子节点段，一个 非叶子节点段。

　　下面思考一个问题，默认情况下一个使用InnoDB存储引擎的表只有一个聚簇索引，一个索引会生成2个段，而段是以区为单位申请存储空间的，一个区默认占用 1MB空间，所以默认情况下，一个只存了几条记录的小表也要占用2MB的存储空间么？以及以后每次添加一个新的索引都要多申请2MB的存储空间吗？如果这样做还是会比较 浪费存储空间。

　　上面问题的症结在于我们假设区是非常"纯粹"的，就是一个区被整个分配给一个段，也就是一个区中的所有页都是为了存储同一个段的数据而存在的，即使段的数 据填不满这个区的所有页面，剩下的页面也不能用作其他。为了解决这个问题，InnoDB提出了一个碎片区（fragment） 的概念，在一个碎片区中，并不是所有的页 面都是为了存储同一个段的数据而存在的，这些页面可以用于不同目的；碎片区直属于表空间，并不属于任何一个段。所以现在为某个段分配空间的策略是：

• 1、在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的；

• 2、当某个段已经占用32个碎片区页面时，就会以完整的区为单位来分配存储空间。

　　所以现在段不能仅定义为是某些区的集合，更精确的应该是某些零散的页面以及一些完整的区的集合。除了索引的叶子节点段和非叶子节点段之外，InnoDB中 还有为存储一些特殊的数据而定义的段，比如回滚段。

区的分类

　　现在我们知道，表空间是由若干个区组成的，这些区大体上可以分为4种类型：

• （1） 空闲的区：现在还没有用到这个区的任何页面；
• （2） 有剩余空间的碎片区：表示碎片区中还有可用的页面；
• （3） 无剩余空间的碎片区：表示碎片区中的所有页面都被使用了；
• （4） 附属于某个段的区：每一个索引都可以分为叶子节点段和非叶子节点段，除此之外还有一些特殊作用的段，在这些段中的数据量很大时将使用区来作为基本的分配 单位。

　　这4种类型的区也可以被称为区的4种状态，定义如下：

　　也就是说，处于FREE、FREE_FRAG以及FULL_FRAG状态的区是独立的，直属于表空间；而处于FSEG状态的区是附属于某个段的。为了方便管理这些区，InnoDB设计了一个XDES Entry（Extent Descriptor Entry）的数据 结构，记录区的一些属性，它一共占40字节，大致可以分为4个部分：

• Segment ID（8字节）：每个段都有一个唯一的编号，这个字段表示该区所在的段的段号。我理解这个字段只有对FSEG状态的区才有意义，对于碎片区和空闲区，应该是没有意义的。

• List Node（12字节）：这个部分将若干个XDES Entry串联起来构成一个链表，如果我们想定位表空间的某一个位置的话，只需要指定页号以及该位置在指定页的偏移即可，所以它的结构包括：

  • Pre Node Page Number（4字节）和 Pre Node Offset（2字节）：组合起来就是指向前一个XDES Entry的指针；
  • Next Node Page Number（4字节）和 Next Node Offset（2字节）：组合起来就是指向后一个XDES Entry的指针；

• State（4字节）：表明区的状态，就是前面那4个；

• Page State Bitmap（16字节）：一共占用128个比特位，一个区包含64个页，那么这128个比特位被划分为64个部分，每个页对应两个bit，比如Page State Bitmap部分的第1和第2个比特位对应着区中的第1个页面，第3和第4个比 特位对应着区中的第2个页面，依此类推，Page State Bitmap部分的第127和128个比特位对应着区中的第64个页面。这两个比特位的第一个位表示对应的页是否是空闲的，第二个比特位还没有用。

XDES Entry链表

　　现在除了页以外，又提出了一堆新的概念，包括区、段、组、碎片区、附属于段的区等。其实提出这一堆概念的目的很简单，就是为了高效的向表中插入数据的同时，尽量减少存储空间的浪费。向表中插入数据的本质就是向表中的各个叶子节 点段和非叶子节点段插入数据，不同的区也有不同的状态，现在回顾一下向某个段插入数据的过程：

• （1）当段中的数据较少时，首先查看表空间中是否有FREE_FRAG的区，即找有空闲空间的碎片区，如果找到了，就从这个区中取一些零散的页将数据插入；如果没有找到，则到表空间中申请一个FREE的区，即空闲区，修改该区的状态为 FREE_FRAG，然后从该新申请的区中取一些零碎的页将数据插入。之后不同的段使用零碎页的时候都会从该区中取，直到该区中没有空闲空间，然后该区的状态就变成了FULL_FRAG。

　　一个问题是，如何知道表空间中的区的状态呢？因为数据量比较大的时候，区的数量也非常多。所以这里是通过XDES Entry中的List Node指针分别将FREE、FREE_FRAG、FULL_FRAG的状态的区连接起来，这里注意连接的是XDES Entry 部分，这样就分别构成了一个FREE链表，FREE_FRAG链表，FULL_FRAG链表。注意这三个链表是直属于表空间的。

　　这样每当我们想找一个FREE_FRAG状态的区时，就直接把FREE_FRAG链表的头节点拿出来，从这个节点中取一些零碎的页来插入数据，当这个节点对应的区用完时，就修改一下这个节点的State字段的值，然后从FREE_FRAG链表中移到 FULL_FRAG链表中。同理，如果FREE_FRAG链表中一个节点都没有，那么就直接从FREE链表中取一个节点移动到FREE_FRAG链表的状态，并修改该节点的STATE字段值为FREE_FRAG，然后从这个节点对应的区中获取零碎的页就好了。

• （2）当段中的数据已经占满了零散的32个页时，就直接申请完整的区来插入数据了。

　　对于同一个段中的三个状态的区，也会分别构建三个链表，只不过这里的三个链表直属于某个段。而且这里是每个段都有自己的三个链表。再次强调一遍，每一个索引都对应两个段，每个段都会维护上述的3个链表。

　　这个表t共有两个索引，一个聚簇索引，一个二级索引idx_c2，所以这个表共有4个段，每个段都会维护上述3个链表，总共是12个链表，加上我们上边说过的直属于表空间的3个链表，整个独立表空间共需要维护15个链表。所以段在数据量比较 大时插入数据的话，会先获取NOT_FULL链表的头节点，直接把数据插入这个头节点对应的区中即可，如果该区的空间已经被用完，就把该节点移到FULL链表中。

链表基节点

　　前面介绍了一堆的链表，可是怎么找到这些链表呢？或者说怎么找到某个链表的头节点或者尾节点的位置呢？这里就需要一个叫做List Base Node的结构，这个链表的基节点中包含了链表的头尾节点的指针，以及这个链表中包含了多少个节点 的信息，每一个链表都对应了一个List Base Node结构，主要包括：

• List Length：表明该链表一共包括多少个节点，即多少个XDES Entry，也就是多少个extent；
• First Node Page Number和First Node Offset表明该链表的头节点在表空间中的位置。
• Last Node Page Number和Last Node Offset表明该链表的尾节点在表空间中的位置。

　　一般我们把某个链表对应的List Base Node结构放置在表空间中固定的位置，这样想找定位某个链表就变得非常迅速了。综上所述，表空间是由若干个区组成的，每个区都对应一个XDES Entry的结构，直属于表空间的区对应的XDES Entry 结构可以分成FREE、FREE_FRAG和FULL_FRAG这3个链表；每个段可以附属若干个区，每个段中的区对应的XDES Entry结构可以分成FREE、NOT_FULL和FULL这3个链表。每个链表都对应一个List Base Node的结构，这个结构里记录了 链表的头、尾节点的位置以及该链表中包含的节点数。正是因为这些链表的存在，管理这些区才变成了一件比较容易的事情。

段的结构

　　段其实并不对应表空间中的某一连续的物理区域，它是一个逻辑上的概念。由若干零散的页面以及一些完整的区组成，每个区都有一个XDES Entry来记录这个区中的一些属性，每个段也有一个INODE Entry结构来记录段的一些信息，共计192 字节，包括：

• Segment ID（8字节），就是指这个INODE Entry结构对应的段的编号（ID）。

• NOT_FULL_N_USED（4字节）：这个字段指的是在NOT_FULL链表中已经使用了多少个页面。下次从NOT_FULL链表分配空闲页面时可以直接根据这个字段的值定位到。而不用从链表中的第一个页面开始遍历着寻找空闲页面。

• 3个List Base Node（共48字节）：分别为段的FREE链表、NOT_FULL链表、FULL链表定义了List Base Node，这样我们想查找某个段的某个链表的头节点和尾节点的时候，就可以直接到这个部分找到对应链表的List Base Node。

• Magic Number（4字节）：标记这个INODE Entry是否已经被初始化了（初始化的意思就是把各个字段的值都填进去了）。

• Fragment Entry Array（共32个，每个4字节）：前边强调过无数次段是一些零散页面和一些完整的区的集合，每个Fragment Array Entry结构都对应着一个零散的页面，这个结构一共4个字节，表示一个零散页面的页号。

小总结

　　现在为止，我们梳理了一遍区、段、表空间、XDES Entry、INODE Entry还有各种XDES Entry为节点的链表，但是这些东西到底存在表空间的什么位置呢？或者说这些东西都怎么用呢？之前说过连续256个区构成一个组，每个组开头的 页面都是相同类型的，现在我们再来梳理。

FSP_HDR类型：第一个组的第一个页面，也就是表空间的第一个页面（表空间只有一个这个页），页号为0，存储了一些表空间的整体属性以及第一个组内的256个区对应的XDES Entry结构，对比着原文发的图来看。一个完整的FSP_HDR 类型的页面大致由5个部分组成：

　　下面重点来看看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结构， 建立各种链表等，在该字段表示的页号之前的区都被初始化了，之后的区尚未被初始化。

• Next Unused Segment ID：为某个表创建一个新的索引时，就会创建两个新的段，那么段号就直接从这个字段中取就可以了。

• Space Flags：存储了一些表空间的属性，一共32bit，这里不再列出其每一部分的含义了，可以看原文章。

• List Base Node for SEG_INODES_FULL List和List Base Node for SEG_INODES_FREE List：每个段对应的INODE Entry结构会集中存放到一个 类型为INODE的页中，如果表空间中的段非常多，则会有多个INODE Entry结构，那么一个页可能放不下，这些INODE类型的页会组成两种列表：

  • SEG_INODES_FULL链表，该链表中的INODE类型的页面都已经被INODE Entry结构填充满了，没空闲空间存放额外的INODE Entry了。
  • SEG_INODES_FREE链表，该链表中的INODE类型的页面仍有空闲空间来存放INODE Entry结构。

　　紧接着File Space Header部分的就是XDES Entry部分了，

这一篇文章暂停笔记了，非常细节，内容太多，实际中应该几乎不会用到

第十篇：单表访问方法——MySQL如何执行单表查询

　　对于单个表的查询来说，MySQL把查询的执行方式大致划分为两种：

1、 全表扫描查询；

2、 使用索引进行查询，又可以细分为：

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

　　MySQL执行查询语句的方式叫做访问方法，下面看一下各种访问方法：

1 const

　　通过主键列来定位一条记录，或者说，通过唯一二级索引来定位一条记录，这种访问方法定义为const，意思是常数级别的，代价可以忽略不计。但是前提必须是只能在主键列或者唯一二级索引列进行等值比较。

　　对于唯一二级索引列，查询该列为NULL值的情况比较特殊，比如：

SELECT * FROM single_table WHERE key2 IS NULL;

　　因为唯一二级索引列并不限制 NULL 值的数量，所以上述语句可能访问到多条记录，也就是说 上边这个语句不可以使用const访问方法来执行。

2 ref

　　对某个普通的二级索引列（非唯一）与常数进行等值比较，为key1列建立了二级索引，但是key1列并不是unique的，比如这样：

SELECT * FROM single_table WHERE key1 = 'abc';

　　对于上面这种查询，可以选择全表扫描，一次比较每一条记录；也可以先使用二级索引查找到对应的主键id值，再回表到聚簇索引中查找到完整的用户记录；由于普通二级索引并不限制索引列值的唯一性，所以可能找到多条对应的记录，也就是 说使用二级索引来执行查询的代价取决于等值匹配到的二级索引记录条数。如果匹配的记录较少，则回表的代价还是比较低的，所以MySQL可能选择使用索引而不是全表扫描的方式来执行查询。把这种搜索条件为二级索引列与常 数等值比较，采用二级索引来执行查询的访问方法称为：ref。

　　对于普通的二级索引来说，通过索引列进行等值比较后可能匹配到多条连续的记录，而不是像主键或者唯一二级索引那样最多只能匹配1条记录，所以这种ref访问方法比const差了那么一丢丢，但是在二级索引等值比较时匹 配的记录数较少时的效率还是很高的（如果匹配的二级索引记录太多那么回表的成本就太大了）。不过需要注意下边两种情况：

• 二级索引列值为NULL的情况：不论是唯一二级索引，还是普通二级索引，采用key IS NULL的方式来搜索都可能得到多条结果，所以我们采用key IS NULL这种形式的搜索条件最多只能使用ref的访问方法，而不是const的访问方法。
• 对于某个包含多个索引列的二级索引来讲（联合索引），只要是最左边的连续索引列是与常数的等值比较就可能采用ref的访问方法。但是如果最左边的连续索引列并不全部是等值比较的话，它的访问方法就不能称为ref了。

3 ref_or_null

　　有时候，我们的查找不仅是要找到某个二级索引列值等于某个常数的记录，还想把该值为NULL的记录也找到，比如：

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

　　当使用二级索引而不是全表扫描的方式执行该查询时，这种类型的查询使用的访问方法就称为ref_or_null，这也是需要回表的。

4 range

　　前面介绍的几种访问方法都是在对索引列与某一个常数进行等值比较的时候才可能使用到（包括IS NULL也是），但是更多时候我们还需要进行范围查询，比如

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

　　我们当然还可以使用全表扫描的方式来执行这个查询，不过也可以使用二级索引 + 回表的方式执行，如果采用二级索引 + 回表的方式来执行的话，那么此时的搜索条件就不只是要求索引列与常数的等值匹配了，而是索引列需要匹配某个或某些 范围的值，这种利用索引进行范围匹配的访问方法称之为：range。这里的索引可能是聚簇索引，也可能是二级索引。

5 index

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

　　上面这个查询语句，key_part2不是联合索引idx_key_part的最左索引列，因此无法使用ref或者range访问方法来执行这个语句，我们之前说的比较笼统，就说这个查询用不到索引，但实际上观察下可以发现，这个查询的列表只有3列， 恰好这3列都在索引idx_part_key中，因为联合索引就是针对这3列来建立的，搜索条件的key_part2也包含在联合索引中。

　　所以可以直接遍历联合索引idx_part_key的叶子节点的记录来比较key_part2 = ‘abc’这个条件是否成立，由于二级索引记录比聚簇索记录小的多（聚簇索引记录要存储所有用户定义的列以及所谓的隐藏列，而二级索引记录只需要存放 索引列和主键），而且这个过程也不用进行回表操作，所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多，就把这种采用遍历二级索引记录的执行方式称之为：index。

6 all

　　最直接的查询执行方式就是我们已经提了无数遍的全表扫描，对于InnoDB表来说也就是直接扫描聚簇索引，把这种使用全表扫描执行查询的方式称之为：all。

第十一篇：连接的原理

　　这一章主要讲MySQL中的连接查询，即在你的from后面跟着多张表，这也是之前的薄弱点，需要认真做笔记。

　　t1表和t2表连接起来的过程就是：将t1表中的记录和t2表中的记录连起来组成更大的记录，这种查询方式就叫做连接查询。连接查询的结果集中包含一个表中 的每一条记录与另一个表中的每一条记录相互匹配的结果，这样的结果集也称之为笛卡尔积，假设t1表有m条记录，t2表有n条记录，两个表连接之后的笛卡尔积就 有m*n条记录。连接查询的语法页比较简单，直接在from后面跟多个表名即可，比如：

SELECT * FROM t1, t2;

连接过程简介

　　在连接的过程中，往往都需要过滤掉一些组合，因为笛卡尔积是非常爆炸的，数据量会非常多。通常在连接查询的过滤条件可以分为两种：

• 只涉及单表的条件：比如t1.m1 > 1是只针对t1表的过滤条件，t2.n2 < ’d’是只针对t2表的过滤条件
• 涉及两表的条件：比如t1.m1 = t2.m2、t1.n1 > t2.n2等

　　携带过个过滤条件的查询，其查询过程大致如下：

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

• 首先确定第一个需要查询的表，也叫作驱动表，假设使用t1作为驱动表，那么首先需要先去t1表中查找满足t1.m1 > 1的记录（这时使用的是单表查询的某种访问方法）。
• 针对上一步骤从驱动表中得到的结果集中的每一条记录，分别去t2表查找匹配的记录，t2表也叫作被驱动表。
• 我们发现，驱动表会访问1次，被驱动表则可能会访问多次，具体次数则取决于驱动表的过滤条件得到的记录数（即满足t1.m1 > 1的记录数）。

内连接和外连接

　　这一部分对比着原文章的举例来阅读，效果更好，因为例子中创建了两个现实中比较常见的表。

• 内连接：对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，则该记录不会被加入到结果集中，前面介绍的都是内连接；

• 外连接：对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，该记录也会被加入到结果集中。根据驱动表的选取不同，外连接又可以分为

  • 左（外）连接：选取左侧的表为驱动表。
  • 右（外）连接：选取右侧的表为驱动表。

　　但是即使是在外连接中，我们也会有这样一个需求——并不想把驱动表的全部记录都加入到最后的结果集，这就是ON这个关键字出现的意义，所以又产生了下面的规则：

• WHERE子句中的过滤条件：不论是内连接还是外连接，凡是不符合WHERE子句中的过滤条件的记录都不会加入到结果集中；
• ON子句中的过滤条件：对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。但是对于内连接来说，ON等同于 WHERE，也就是记录不会被加入到结果集中。所以这就是ON这个关键字出现的意义。
• 一般情况下，我们都把只涉及单表的过滤条件放到WHERE子句中，把涉及两表的过滤条件都放到ON子句中，我们也一般把放到ON子句中的过滤条件也称之为连接条件。

左连接的语法

　　比如，我们现在要将表t1和表t2进行左连接，语法就是：

SELECT  * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件]

　　其中OUTER是可以省略的，因为左连接就等于左外连接，对于LEFT JOIN类型的连接来说，放在左边的表称之为外表（驱动表），放在右边的表称之为内表（被驱动表）。需要注意的是，对于左（外）连接和右（外）连接来说，必 须使用ON子句来指出连接条件。你如果使用了LEFT JOIN或者RIGHT JOIN，连接条件却使用了where，则会报错。

右连接的语法

　　和左连接是一样的，只不过RIGHT JOIN右边的表是驱动表，左边的才是被驱动表。也就是说，左连接和右连接是可以互相转化的，即下面这两条sql是等价的：

SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1 LEFT JOIN score AS s2 ON s1.number = s2.number;
SELECT s1.number, s1.name, s2.subject, s2.score FROM score AS s2 RIGHT JOIN student AS s1 ON s1.number = s2.number;

内连接的语法

　　内连接和外连接的根本区别在于驱动表中的记录不符合ON子句中的连接条件时，是否会将驱动表中这些记录加入到最终的结果集中，之前只提到了内连接的最简单的一种语法，即直接将表名都放到FROM子句后面，它的本质语法如下：

SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件]

　　也就是说，下面几种内连接的写法都是等价的：

• SELECT * FROM t1 JOIN t2;
• SELECT * FROM t1 INNER JOIN t2;
• SELECT * FROM t1 CROSS JOIN t2;
• SELECT * FROM t1, t2;

　　推荐INNER JOIN的形式书写内连接（因为INNER JOIN语义很明确，可以和LEFT JOIN和RIGHT JOIN轻松区分）。这里值得注意的是，在内连接中ON子句和WHERE子句是等价的，所以内连接中不要求强制写明ON子句。

　　回顾前面提到的连接的本质——把一张表中的每一条记录都依次与被驱动表中的记录进行连接，然后加入到结果集中。不论哪个表作为驱动表，产生的笛卡尔积结果都是一致的（当然列顺序可能不一样）。对于内连接来说，凡是不符合ON子句或 者WHERE子句的条件都会被过滤掉，其实也就相当于是从两表的笛卡尔积中把不符合过滤条件的记录都移除，因此对于内连接来说，驱动表和被驱动表是可以互换的，不影响其最终结果；但是很明显对于外连接是不可以互换的。不过这里需要 注意的是，对于内连接交换驱动表不影响最终结果，但是对于执行效率可能会有所不同（当然查询优化器会选择真正合适的表作为驱动表）。

连接的原理

　　MySQL采用什么样的算法来进行表与表之间的连接，主要有两种：

• （1） 嵌套循环连接（Nested-Loop Join）

　　我们前边说过，对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍，具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数。对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的， 也就是说左（外）连接的驱动表就是左边的那个表，右（外）连接的驱动表就是右边的那个表。我们上边已经大致介绍过t1表和t2表执行内连接查询的大致过程：

• 步骤1：选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。

• 步骤2：对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。

　　这种驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为嵌套循环连接（Nested-Loop Join），这是最简单，也是最笨拙的一种连接查询算法。

　　这种在被驱动表中进行查找，也是可能利用到索引的，因为本质上这是一个单表查询。

• （2） 基于块的嵌套循环连接（Block Nested-Loop Join）

　　扫描一个表的过程就是先把这个表的数据从磁盘加载到内存中，然后再进行匹配；如果被驱动表要访问多次，而且其数据量比较大的时候，内存中可能并不能完全存放下表中的所有记录，这样在扫描前面记录的时候，后面的记录可能还在磁盘 上，那么对于前面的嵌套循环连接算法，就会出现一个现象：被驱动表会从磁盘上被load很多次，这样的I/O代价非常大，所以需要尽量减少被驱动表被访问的次数。

　　当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把 被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从磁盘上加载到内存中多少次。所以可以在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复 从磁盘上加载被驱动表的代价了。所以MySQL提出了一个join buffer的概念，join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱 动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价。

　　最好的情况是join buffer足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。MySQL把这种加入了join buffer的嵌套循环连接算法称之为基于块的嵌套连接（Block Nested-Loop Join）算法。

　　值得注意的是，驱动表的记录并不是所有列都会被放到join buffer中，只有查询列表中的列和过滤条件中的列才会被放到join buffer中，所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了， 这样还可以在join buffer中放置更多的记录。它的默认大小为262144字节（也就是256KB），最小可以设置为128字节。

第十二篇：基于成本的优化

　　MySQL有一个查询优化器，它每一次都会对我们的查询语句进行优化，以选择一个成本最小的执行计划，这一章就是讲查询优化器如何计算一次查询中多种可能的查询方式所对应的成本，再和全表扫描的成本相比较，最终使用成本最小的作为 查询计划，包括是否可以使用到索引，查询条件中包含多个二级索引时，是否能将两个不同的索引进行联合查询等，估算一个查询代价。

第十三篇：Innodb统计数据是如何收集的

第十八篇：Innodb的Buffer Pool

　　对于使用InnoDB作为存储引擎的表来说，不管是存储用户记录的索引（包括聚簇索引和二级索引），还是各种系统数据，都是以页的形式存放在表空间中的，而表空间就是InnoDB对文件系统上一个或多个实际文件的抽象，所以存储引擎在 处理客户端请求时，当需要访问某个页的数据时，就会把完整的页加载到内存中，即使你只需要访问这个页中的一条记录，也是需要加载一个页的数据到内存中的；当访问结束后，将这个页缓存起来，以省去下一次访问本页的磁盘IO开销。

　　MySQL服务器在启动时向操作系统申请了一篇连续的内存，叫做Buffer Pool（缓存池），默认情况下大小是128M，也可以通过修改innodb_buffer_pool_size参数的值将其调大一些。Buffer Pool中默认的缓存页大小和在磁盘上 的页大小是一样的，都是16KB，为了更方便的管理这些缓存页，InnoDB为每一个缓存页都创建了一个控制信息，包括页所属的表空间编号、页号、缓存页在Buffer Pool中的地址以及一些锁信息等，每个缓存页对应的控制信息叫做一个控制块。 控制块所占用的内存大小都是一样的，并且和缓存页是一一对应的，也被存放到了Buffer Pool中，控制块放在缓存池的前面，缓存页放在后面。每个控制块大约占用缓存页大小的5%，在MySQL5.7.21这个版本中，每个控制块占用的大小是 808字节。而我们设置的innodb_buffer_pool_size并不包含这部分控制块占用的内存空间大小，也就是说InnoDB在为Buffer Pool向操作系统申请连续的内存空间时，这片连续的内存空间一般会比innodb_buffer_pool_size的值 大5%左右。

free链表

　　最初MySQL服务器启动时，需要初始化Buffer Pool，即首先向操作系统申请一块连续的内存空间，然后将其划分为若干对（pair）控制块和缓存页，此时并没有真正的磁盘页被缓存到Buffer Pool中，随着程序的运行，不断的有磁盘上 的页被缓存到Buffer Pool中。所以当从磁盘上读取一个页后，应该放到哪个缓存页中呢？这就是free链表的作用了。

　　将所有空闲缓冲区对应的控制块作为一个节点放入free链表中，代表当前空闲的缓冲页，刚刚完成初始化的Buffer Pool中所有的缓存页都是空闲的，所以每一个缓存页对应的控制块都会被加入到free链表（双向）中。并且为了管理 这个free链表，还为这个链表定义了一个基节点，里面包含free链表的头尾节点地址，以及链表包含的节点数等，值得注意的是，基节点占用的内存空间并不在Buffer Pool之内，而是单独存放的。目前这个基节点只占用40字节。现在每 当需要从磁盘中加载一个页到Buffer Pool中时，就从free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上（就是该页所在的表空间、页号之类的信息），然后把该缓存页对应的free链表节点从链表中移除，表示该缓存页 已经被使用了。

缓存页是否命中

　　另外，当访问某个页的数据时，我们是怎么判断是否缓存命中的呢？即怎么知道访问的页在不在Buffer Pool中的？这里我们建立了一个哈希表，key为表空间号 + 页号，value就是对应的缓存页，访问时，先查哈希表，如果命中则直接 使用即可，如果没有再从free链表中选择一个空闲的缓存页，再把磁盘上对应的页加载到该缓存页的位置。

flush链表

　　如果修改了某个缓存页中的数据，则现在缓存页的数据就和磁盘上的页不一致了，这样的缓存页也被称作脏页，MySQL并不会在每发生一次页中数据的修改就将数据立即同步到磁盘页上，这样比较耗费性能，而是在未来的某个时间再进 行同步。这里又会创建一个脏页的链表，凡是修改过的缓存页对应的控制块都会被加入到这个链表中，这个链表里节点对应的缓存页都是需要被刷新到磁盘中的，也叫作flush链表，它的构造和free链表是差不多的，也有一个基节点。

简单LRU链表

　　前面其实说过，每次缓存不命中就从free链表中找一个新的缓存页来存放磁盘页中的数据，那么迟早会有free链表用完的时候，即每个缓存页都被用了，这时候就需要淘汰一些旧的缓存页，也就是释放掉它们占用的内存空间，来存放新的从 磁盘上加载的磁盘页。这里涉及到的是各类淘汰算法，MySQL中使用的是LRU（Least Recently Used）算法，淘汰的是最近最少使用的页，这里又创建了一个LRU链表，当需要访问某个页时，这样处理LRU链表：

• 如果该页不在Buffer Pool中，即没有命中缓存，那么把该页从磁盘加载到Buffer Pool中的缓存页时，将该缓存页对应的控制块作为节点加到LRU链表头部；
• 如果该页在Buffer Pool中，则直接把该页对应的控制块移动到链表头部。

　　也就是说，只要使用到某个缓存页，就把该缓存页对应的控制块调整到LRU链表头部，那么LRU链表尾部的节点就是最近最少使用的缓存页了。当Buffer Pool中的缓存页使用完了后，就可以淘汰LRU链表尾部对应的缓存页了。

划分区域的LRU链表

　　简单的LRU链表可以解决很多问题了，但是还会有一些尴尬的情况发生，比如：

• 预读（read ahead），就是InnoDB认为执行当前的请求可能之后会读取某些页面，就预先把它们加载到Buffer Pool中，根据细则不同又分为线性预读和随机预读，这里不用太care细则。
• 全表扫描。

　　这两种情况都会导致缓存命中率降低，一种预读导致加载到Buffer Pool中的缓存页不一定被用到，另一种全表扫描导致非常多使用频率低的页淘汰了那些使用频率比较高的页。因为这两种情况的存在，所以InnoDB把这个LRU链表 按照一定比例分成两截，分别是：

• 一部分存储使用频率比较高的缓存页，叫做热数据，也叫作young区域；
• 另一部分存储使用频率不是很高的缓存页，叫做冷数据，也叫作old区域；

　　这里值得注意的是，是按照某个比例来划分冷热数据的，对于某个节点来说，它所属的区域是可能变化的。可以通过查看系统变量innodb_old_blocks_pct的值来确定old区域在LRU链表中所占的比例。这个值一般是37，也就是默认 情况下，old区域在LRU链表中占的比例是37%，这个参数也可以手动修改。通过针对冷热数据的一些策略，减少了预读和全表扫描带来的缓存命中率降低问题，这些策略细则这里也并不记录了，可以参考原文章，也还是比较容易理解的。主要 是：用不到的预读页面以及全表扫描的页面都只会被放到old区域，而不影响young区域中的缓存页。

更进一步优化的LRU链表

　　事实上，对于young链表来说，每次访问一个缓存页都要将其移动到链表的头部，频繁的移动链表头部的数据是不太合适的，因为young区域中存储的都是比较热的数据，所以还有一些其他的优化策略，比如只有被访问的缓存页 位于young区域的1/4的后边，才会被移动到LRU链表头部，这样就可以降低调整LRU链表的频率，从而提升性能（也就是说如果某个缓存页对应的节点在young区域的1/4中，再次访问该缓存页时也不会将其移动到LRU链表头部）。

刷新脏页到磁盘

　　后台有专门的线程负责将脏页刷新到磁盘中，主要也有两种刷新途径：

• 从LRU链表的冷数据中刷新一部分页面到磁盘中，定时从LRU链表尾部开始扫描一些缓存页，扫描的页面数量可以通过系统变量innodb_lru_scan_depth来指定，如果从这里发现脏页，会把它们刷新到磁盘。这种刷新页面的方式被 称之为BUF_FLUSH_LRU。
• 从flush链表中刷新一部分页面到磁盘，这种刷新页面的方式被称之为BUF_FLUSH_LIST。

多个Buffer Pool实例

　　一个Buffer Pool可能太大了，多线程环境下访问Buffer Pool的各种链表都存在竞争或者加锁情况，因此可以在Buffer Pool非常大的时候，将其拆分为多个，每个Buffer Pool单独作为一个实例，拥有自己的各个链表。 可以在服务器启动的时候通过设置innodb_buffer_pool_instances的值来修改Buffer Pool实例的个数。值得注意的是：当innodb_buffer_pool_size的值小于1G的时候设置多个实例是无效的，InnoDB会默认把 innodb_buffer_pool_instances 的值修改为1。而我们鼓励在Buffer Pool大小或等于1G的时候设置多个Buffer Pool实例。目前，我们现在线上一个服务的innodb_buffer_pool_size为2G，总共设置了8个Buffer Pool 实例，即innodb_buffer_pool_instances为8。

第十九篇：事务简介

　　我们往往说的事务指的是一组操作，不过这里你需要明白的，即使看起来的一个单独的update语句，执行后也并不一定就是成功的，因为从前面我们可以得知，修改了某个页面也不会立即将页面刷新到磁盘，这中间是有一个gap的，也就 是这段时间也可能发生断电宕机等事情。

　　前面《高性能MySQL》以及《数据密集型应用设计》书中都提到了，ACID中的consistency，即一致性严格来说并不是数据库保证的，而是业务逻辑的一个"一致的状态"。这里文章中的一些举例还是可以理解的。数据库中的数据必须符合 现实世界（或者说程序业务逻辑）的一些约束，就称数据库满足一致性。如何保证数据库中数据的一致性（就是符合所有现实世界的约束）呢？这其实靠两方面的努力：

• 数据库本身可以保证一部分一致性需求：比如某个列是unique的，那么当插入重复值时就会报错，当然还有一些check语句，不过MySQL仅支持check语法，实际上没有用到。也可以通过定义触发器的方式来自定义一些约束条件以 保证数据库中数据的一致性。

• 更多的一致性需要业务代码来保证，现实生活中复杂的一致性需求比比皆是，而由于性能问题把一致性需求交给数据库去解决这是不现实的。比方说account表，也可以不建立触发器，只要编写业务的程序员在自己的业务代码里判断一下，当 某个操作会将balance列的值更新为小于0的值时，就不执行该操作。

　　把需要保证原子性、隔离性、一致性和持久性的一个或多个数据库操作称之为一个事务（英文名是：transaction）。

开启事务

　　开启一个事务的两种语句是 BEGIN 或者 START TRANSACTION，不同之处在于，START TRANSACTION语句后边可以跟随几个修饰符：

• READ ONLY：标识当前事务是一个只读事务，这个事务里的操作只能读取数据而不能修改数据，这里的"不能修改"指的是不能修改那些其他事务也能访问到的表数据，其他还是可以修改的。
• READ WRITE：当前事务是一个读写事务，既可以读也可以写。
• WITH CONSISTENT SNAPSHOT：启动一致性读。

　　一个事务的访问模式不能同时既设置为只读的也设置为读写的，所以我们不能同时把READ ONLY和READ WRITE放到START TRANSACTION语句后边。另外，如果我们不显式指定事务的访问模式，那么该事务的访问模式就是读写模式。

提交、回滚事务

　　提交事务的语句是 COMMIT。回滚事务的语句是 ROLLBACK，这里需要强调一下，ROLLBACK语句是程序员手动的去回滚事务时才去使用的，如果事务在执行过程中遇到了某些错误而无法继续执行的话，事务自身会自动的回滚。

　　InnoDB存储引擎支持事务，而MyISAM并不支持。

自动提交、隐式提交

　　MySQL默认情况下会把每一条语句都当作一个独立的事务来执行。如果想关闭默认提交功能，可以把系统变量autocommit的值设置为OFF，或者以BEGIN来显示开启一个事务（仅针对本次提交有效，暂时的）。所以我们在一些dao框架 包中经常可以看到，在开启一个事务操作时，往往是先把autocommit设置为off状态，然后再使用START TRANSACTION来显示开启一段事务逻辑（但是不设置为off也没有影响）。

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

• 定义或者修改数据库对象的数据定义语言，数据库对象就是指的数据库、表、视图、存储过程等，当我们使用CREATE、ALTER、DROP等语句去修改这些所谓的数据库对象时，就会隐式的提交前边语句所属于的事务。

• 隐式使用或者修改MySQL数据库的表：当我们使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD等语句时也会隐式的提交前边语句所属于的事务。

• 事务控制或关于锁定的语句：当我们在一个事务还未提交或者回滚时又使用BEGIN开启了另一个事务时，会隐式提交上一个事务。

• 这里还有非常多，就不一一列举了，可以参考原文章。

保存点

　　当我们开启了一个执行语句比较多的事务，可以在事务语句中打几个点，在调用ROLLBACK的时候可以指定回滚到某个SAVEPOINT，而不是回到事务最开始的样子。这个概念也不是MySQL特有的，像flink这种大数据处理框架中也用到了。

第二十篇：redo日志详解

redo日志定义

　　InnoDB存储引擎是以页为单位来管理存储空间的，所有的增删改查操作其实本质上都是在访问页面（包括读页面、写页面、创建新页面等操作）。在真正访问页面之前，需要把磁盘上的页缓存到内存中的Buffer Pool中之后才可以访问。 但是对于事务的持久性来讲，对于一个已经提交的事务，在事务提交后即使系统发生了崩溃，这个事务对数据库中所做的更改也不能丢失。但是如果只是在内存的Buffer Pool中修改了页面，假设在事务提交后发生了故障宕机了，那么数据还 没有刷新到磁盘中，这样就不满足持久性了。一个比较简单的做法就是在事务提交之前就把事务所修改的页面全部刷新到磁盘，但是这个做法会有一些问题：

• 1、刷新一个完整的页太浪费了：有时候只修改了页面中的一个字节，但是不得不把一个完整的页从内存刷新到磁盘中，太浪费了。

• 2、随机IO刷新会比较慢：如果一个事务中包含了多条更新语句，或者说一条更新语句也可能更新的是多个页面，这些页在物理上并不一定相邻，这就意味着在刷新时可能伴随着大量的随机IO，会非常慢。

　　这里有一个思想：没有必要每次都将所有修改的页面刷新到磁盘中，只需要把修改了哪些数据记录下来即可，这部分数据就比较小了，不会像一个整页16KB那么大，这就是redo log。这样就只需要在事务提交时，将redo log 刷新到磁盘中，这样即使我们修改的内存页还没有刷新到磁盘上就宕机了，只需要在重启之后按照redo log记录的步骤重新更新一下数据页即可，也就满足了持久性的要求。当然这里又有一个新的问题：redo log要是还没有写到磁盘上就挂 了，那就没有办法了。所以redo log的刷新到磁盘上一定是更及时的（相对于内存中的数据页来讲）。与在事务提交时将所有修改过的内存中的页面刷新到磁盘中相比，只将该事务执行过程中产生的redo log刷新到磁盘的好处是：

• 1、redo log占用的空间非常小；

• 2、redo log是顺序写入到磁盘中的，也就意味着使用的是顺序IO。

redo日志格式

　　redo log本质上就是记录了一下事务对数据库有哪些修改，InnoDB针对事务对数据库修改的不同场景定义了不同类型的redo log，绝大部分redo log都有通用结构：

• type：redo log的类型
• space ID：表空间ID
• page number：页号
• data：该条redo log的具体内容

　　原文章介绍了几种类型的redo log，有简单的又复杂的，并不需要了解的太细节，这里并没有过多记录。

Mini-Transaction

　　语句在执行过程中可能修改多个页面，这些页面可能是数据页，也可能是系统页面，不论如何都需要记录redo log。在执行过程中产生的redo log 被InnoDB划分为若干个不可分割的组，这里的不可分割的含义是原子性：

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

　　以向某个索引对应的B+树插入一条记录为例，在向B+树中插入记录之前，需要先定位到这条记录应该被插入到哪个叶子节点代表的数据页中，定位到具体的数据页以后，有下面两种情况：

• （1） 该数据页的剩余空间充足，可以直接将记录插入到数据页中，记录一条类型为MLOG_COMP_REC_INSERT的redo日志即可，这种情况叫做乐观插入，可以对比原文章的图来学习。

• （2） 该数据页剩余的空闲空间不足，这样就会进行页分裂操作，也就是会新建一个叶子节点等，这个过程会对多个页面进行修改，因此会产生多条redo log，这种情况叫做悲观插入。

　　InnoDB认为向某个索引对应的B+树插入一条记录的过程必须是原子的，也就是这多条redo log不能只执行一部分，这里InnoDB是通过向redo log的结构中增加一些标识来实现的，实现原理并不复杂。InnoDB把对底层页面的一次 原子访问过程称之为一个Minni-Transaction，简称mtr，比如说前面提到的修改一次Max Row ID的值算作是一个mtr，向某个索引对应的B+树插入一条记录的过程也是一个mtr。一个mtr可能包含一组redo log，在进行崩溃恢复 时这一组redo日志作为一个不可分割的整体。最终，一个事务可能包含多条语句，一个语句可能包含多个mtr，每一个mtr又可能包含多条redo log。

redo log的写入过程

　　InnoDB把通过mtr生成的redo log都放在了大小为512字节的页中，这种页的类型就不是前面常见的索引页（数据页）了，用来存储redo log的页叫做block。一个redo log block包括12字节的log block header和4字节的 log block trailer，中间的496字节log block body用来存储真正的redo log。

　　前提到，InnoDB为了解决磁盘速度过慢的问题而引入了Buffer Pool。同理，写入redo log时也不能直接直接写到磁盘上，实际上在服务器启动时就向操作系统申请了一大片称之为redo log buffer的连续内存空间， 也就是redo log缓冲区，简称为log buffer。这片内存空间被划分成若干个连续的redo log block。可以通过启动参数innodb_log_buffer_size来指定log buffer的大小，目前umc测试环境为32M。

　　向log buffer中写入redo log是顺序的，先写前面的block，空间用完后再写下一个block。InnoDB提供了一个称之为buf_free的全局变量，该变量指明后续写入的redo日志应该写入到log buffer中的哪个位置。

　　前面说过，一个mtr对应的是多条redo log，这些redo log是一个不可分割的组，所以并不是每生成一条redo log，就将其插入到log buffer中，而是每个mtr运行过程中产生的redo log先暂时存到一个地方，当该mtr 结束时，将产生的一组redo log全部复制到log buffer中，

　　　　这里需要注意一个问题：在事务执行期间redo log 是存在redo log buffer中的（即内存中），事务提交后就落盘了。

redo log刷盘时机

　　mtr运行过程中产生的一组redo日志在mtr结束时会被复制到log buffer中，这些日志在一些情况下会被刷新到磁盘里，比如：

• 1、log buffer空间不足：InnoDB认为当log buffer中redo log量站总容量一半后，就需要把这些redo log 刷新到磁盘中了。
• 2、事务提交时：为了保证持久性，在事务提交时可以不把修改过的Buffer Pool页面刷新到磁盘，但是需要把修改页面对应的redo log刷新到磁盘。
• 3、后台线程刷新：后台线程每隔一段时间就会刷新一次log buffer中的redo log到磁盘。
• 4、正常关闭服务器时。
• 5、做checkpoint时。

redo log文件组

　　MySQL的数据目录下默认有两个名为ib_logfile0和ib_logfile1的文件，log buffer中的redo log默认就是刷新到这两个磁盘文件中。也可以通过 一些参数来调整。基本上，磁盘上的redo log文件不止一个，而是以一个日志文件组的形式出现的，这些文件以 ib_logfile数字（数字可以是0、1、2. ..）的形式进行命名。在将redo日志写入日志文件组时，是从ib_logfile0开始写，如果ib_logfile0写满了，就接着ib_logfile1写，依此类推。如果写到 最后一个文件时，那就重新转到ib_logfile0继续写。

redo log文件格式

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

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

　　前边所说的循环使用redo日志文件，其实是从每个日志文件的第2048个字节开始算。redo log文件的前4个block分别是：

• log file header：描述该redo log文件的一些整体属性，这里不再研究其具体内容了，可以参考原文章。
• checkpoint1：记录关于checkpoint的一些属性
• 第三个block未使用，忽略
• checkpoint2：结构和checkpoint1一样。

Log Sequence Number

　　MySQL系统开始运行，就不断的在修改页面，也就意味着会不断的生成redo日志。redo日志的量在不断的递增，永远不可能缩减了。InnoDB为记录已经写 入的redo日志量，设计了一个称之为Log Sequence Number的全局变量，即日志序列号，简称lsn。InnoDB规定初始的lsn值为8704（也就是一条redo 日志也没写入时，lsn的值为8704）。注意这个LSN只是指产生的redo log 量，所以是log buffer中的量，而不是刷盘的。

flushed_to_disk_lsn

　　redo日志是首先写到log buffer中，之后才会被刷新到磁盘上的redo日志文件。所以InnoDB提出了一个称之为buf_next_to_write的全局变量， 标记当前log buffer中已经有哪些日志被刷新到磁盘中了。这个变量和前面提到的buf_free是对应的，buf_free标识下一个redo log被写入到log buffer 的位置。

　　前面提到的lsn是表示当前系统产生的redo log量，是不考虑刷盘的。相应的，InnoDB也设计了一个表示刷新到磁盘中的redo日志量的全局变量，称之为 flushed_to_disk_lsn。系统第一次启动时，该变量的值和初始的lsn值是相同的，都是8704。随着系统的运行，redo日志被不断写入log buffer，但是 并不会立即刷新到磁盘，lsn的值就和flushed_to_disk_lsn的值拉开了差距。

　　当有新的redo日志写入到log buffer时，首先lsn的值会增长，但flushed_to_disk_lsn不变，随后随着不断有log buffer中的日志被刷新到磁盘上， flushed_to_disk_lsn的值也跟着增长。当两者的值相同时，说明log buffer中的所有redo日志都已经刷新到磁盘中了。

　　attention： 应用程序向磁盘中写入文件是先写入到了操作系统的磁盘缓冲区中的，如果要强制操作系统也将数据写入到磁盘中，需要显示调用操作 系统的fsync函数。事实上，其实只有当系统执行了fsync函数后，flushed_to_disk_lsn的值才会跟着增长，当仅仅把log buffer中的日志写入到操作系统 缓冲区却没有显式的刷新到磁盘时，另外的一个称之为write_lsn的值跟着增长。不过这里并没有严格区分开flushed_to_disk_lsn和write_lsn的概念。

flush链表中的LSN

　　一个mtr代表一次对底层页面的原子访问，在mtr结束的时候，会把这一组redo log写入到log buffer中，除此以外还会将mtr执行过程中修改过的页面加入到Buffer Pool中的flush链表。当第一次修改某个缓存在 Buffer Pool中的页面时，就会把这个页面对应的控制块插入到flush链表的头部，之后再修改该页面时由于它已经在flush链表中了，就不再次插入了。也就是说flush链表中的脏页是按照页面的第一次修改时间从大到小进行排序的。 在这个过程中会在缓存页对应的控制块中记录两个关于页面何时修改的属性：

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

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

innodb_flush_log_at_trx_commit

　　为了保证事务的持久性，用户线程在事务提交时需要将该事务执行过程中产生的所有redo日志都刷新到磁盘上。这个要求会很明显的降低数据库性能。如果有的场景对事务的持久性要求不是那么强烈的话，可以选择修改一个称为 innodb_flush_log_at_trx_commit的系统变量的值，该变量有3个可选的值：

• 0 ：表示在事务提交时不立即向磁盘中同步redo日志，这个任务是交给后台线程做的。这样很明显会加快请求处理速度，但是如果事务提交后服务器挂了，后台线程没有及时将redo日志刷新到磁盘，那么该事务对页面的修改会丢失。

• 1 ：表示在事务提交时需要将redo日志同步到磁盘，可以保证事务的持久性。默认情况都是1。

• 2： 表示在事务提交时需要将redo日志写到操作系统的缓冲区中，但并不需要保证将日志真正的刷新到磁盘（没有调用fsync）。这种情况下如果数据库挂了，操作系统没挂的话，事务的持久性还是可以保证的，但是操作 系统也挂了的话，那就不能保证持久性了。

第二十一篇：undo日志详解

　　前面大篇幅的redo log其实是解决了持久性问题，这一篇的undo log解决的就是另一个问题了——原子性。事务需要保证原子性，也就是事务中的操作要么全部完成，要么什么也不做，所以为了支持回滚操作，那么 必须记录下来做过改动的操作，为了回滚而记录的日志就叫做undo日志。

事务id

　　一个事务可以是一个只读或者读写事务，通过START TRANSACTION后面的修饰符来指定。如果在事务的执行过程中对某个表进行了增、删、改操作，那么InnoDB 就会为这个事务分配一个唯一id，分配方式如下：

• 对于只读事务来说，只有在它第一次对某个临时表（因为在只读事务中不可以对普通的表（其他事务也能访问到的表）进行增、删、改操作）进行增、删、改操作时，才会为这个事务分配一个事务id，否则是不会的。

• 对于读写事务来说，只有在它第一次对某个表执行增、删、改操作时才会为这个事务分配一个事务id，否则也是不分配事务id的。因此即使开启了一个读写事务，但是全是查询语句，那么是不会分配事务id的。默认开启的都是读写事务。

　　事务id是一个数字，它的分配策略和对隐藏列row_id（当用户没有为表定义主键和unique键时，InnoDB自动创建的列）的分配策略是相同的，具体策略如下：

• 服务器会在内存中维护一个全局变量，每当需要为某个事务分配一个事务id时，就会把该变量的值当作事务id分配给该事务，并且把该变量自增1。

• 每当这个变量变为256的倍数时，就会将该变量的值刷新到系统表空间的页号为5的页面中，一个称之为Max Trx ID的属性处，这个属性占用8字节的空间。注意这里实际上就是修改了内存页，所以会产生相应的redo 日志。

• 当MySQL服务器下一次启动时，会将Max Trx ID属性加载到内存中，也就是读5号页，将这个值加上256然后赋值给全局变量，后续再分配给其他事务，这样保证事务id一直递增。

　　前面说过每一行记录除了自定义的那些列以外，还有InnoDB生成的隐藏列，分别是trx_id和roll_pointer，如果表没有定义主键或者unique列，还会添加一个row_id的隐藏列。其中trx_id就是对这行记录做修改的语句所在 的事务对应的事务id 　　

undo日志格式

　　为了保证事务的原子性，InnoDB在实际执行增删改记录时，都是先把对应的undo 日志记录下来，对一条记录的改动可能会对应多条undo 日志，那么一个事务在执行过程中就会产生很多条undo log。这些undo 日志是被记录到 类型为FIL_PAGE_UNDO_LOG的页中（对比前面redo log的页类型是block，区别于最常见的INDEX页），这些页面可以从系统表空间中分配，也可以从一种专门存放undo日志的表空间，也就是所谓的undo tablespace中分配。

　　attention：增删改分别对应的undo log是不一样的，所以需要分开讨论。

INSERT操作对应的undo log

　　上一章提到了，向一个表中插入一条记录时有乐观插入和悲观插入的区别，但是最终结果都是数据页中多了一条记录，如果需要回滚，那么就需要删除这条记录，也就是在记录写对应的undo log时，需要记录下当前记录的主键，因此 InnoDB设计了一个类型为TRX_UNDO_INSERT_REC的undo日志，格式如下，对比着原文章的图来阅读更好：

• end of record：本条undo 日志结束，下一条开始时在页面中的地址
• undo type：undo 日志类型，也就是TRX_UNDO_INSERT_REC
• undo no：本条undo日志对应的编号，在一个事务中是从0开始递增的，也就是说只要事务没有提交，每生成一条undo 日志，编号就+1。
• table id：本条undo 日志对应记录所在的表的id，可以从系统数据库里查到。
• 主键各列信息：<len, value>列表：主键的每个列占用的存储空间大小和真实值。如果记录中的主键只包含一个列，那么在类型为TRX_UNDO_INSERT_REC的undo日志中只需要把该列占用的存储空间大小和真实值记录下来，如果记录 中的主键包含多个列，那么每个列占用的存储空间大小和对应的真实值都需要记录下来（len就代表列占用的存储空间大小，value就代表列的真实值）。
• start of record：上一条undo 日志的结束，本条日志开始时在页面中的地址

　　attention： 当我们向某个表中插入一条记录时，实际上需要向聚簇索引和所有的二级索引都插入一条记录。不过记录undo日志时，只需要考虑向聚簇索引插入记录时的情况就好了，因为其实聚簇索引记录和二级索引 记录是一一对应的，在回滚插入操作时，只需要知道这条记录的主键信息，然后根据主键信息做对应的删除操作，做删除操作时就会顺带着把所有二级索引中相应的记录也删除掉。后边说到的DELETE操作和UPDATE操作对应的undo日志也都是 针对聚簇索引记录而言的。

roll_pointer隐藏列的含义

　　roll_pointer这个隐藏列，本质上就是指向当前记录对应的undo 日志的指针，这一点非常重要，一定要充分理解，后续再详细解释。

DELETE操作对应的undo log

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

　　假设此时我们有一个删除语句，准备DELETE正常链表中的一条记录，这个删除的过程其实是需要两个阶段的：

• （1） 仅仅将记录的delete_mask标识位设置成1，其他不修改（实际上会修改记录的trx_id、roll_pointer这些隐藏列），InnoDB把这个阶段称之为delete mask；这时正常记录链表中的最后一条记录的delete_mask 值被设置为1，但是并没有被加入到垃圾链表。也就是此时记录处于一个中间状态。在删除语句所在的事务提交之前，被删除的记录一直都处于这种所谓的中间状态。这个中间状态主要是为了实现MVCC而出现的。

• （2） 当该删除语句对应的事务提交以后，会有专门的后台线程来将记录从正常链表中移除，并加入到垃圾链表的头部，然后调整一些页面的信息，比如页面中的用户记录数量PAGE_N_RECS、上次插入记录的位置PAGE_LAST_INSERT、 垃圾链表头节点的指针PAGE_FREE（因为头节点变成最新这个删除的记录了）、页面中可重用的字节数量PAGE_GARBAGE、还有页目录的一些信息等等。InnoDB把这个阶段称之为purge。

　　也就是说，在删除语句所在的事务提交之前，只会经历阶段一，即delete mark阶段（提交之后我们就不用回滚了，所以只需考虑对删除操作的阶段一做的影响进行回滚）。InnoDB为此设计了一种称之为TRX_UNDO_DEL_MARK_REC 类型的undo日志，它的格式如下：

• end of record：本条undo 日志结束，下一条开始时在页面中的地址
• undo type：undo 日志类型，也就是TRX_UNDO_INSERT_REC
• undo no：本条undo日志对应的编号，在一个事务中是从0开始递增的，也就是说只要事务没有提交，每生成一条undo 日志，编号就+1。
• table id：本条undo 日志对应记录所在的表的id，可以从系统数据库里查到。
• info bits：记录头信息的前4个bit的值以及record_type的值。
• old trx_id：记录旧的trx_id值。
• old roll_pointer：记录旧的roll_pointer值。
• 主键各列信息：<len, value>列表：主键的每个列占用的存储空间大小和真实值。
• index_col_info len：索引列各列信息占用的存储空间大小
• 索引列各列信息：<pos, len, value>，凡是被索引的列的各列信息
• start of record：上一条undo 日志的结束，本条日志开始时在页面中的地址

　　在对一条记录进行delete mask操作前，需要先把该记录的旧的trx_id和roll_pointer记录到undo 日志中，也就是可以通过undo日志的old roll_pointer找到记录在修改之前对应的undo日志。那就是可以通过undo日志 的old roll_pointer找到记录在修改之前对应的undo日志。比方说在一个事务中，我们先插入了一条记录，然后又执行对该记录的删除操作。

　　执行完delete mask操作以后，这个操作产生的undo log就和INSERT操作对应的undo log构成了一个链表，这个链表就称之为版本链。

　　与类型为TRX_UNDO_INSERT_REC的undo日志不同，类型为TRX_UNDO_DEL_MARK_REC的undo日志还多了一个索引列各列信息的内容，也就是说如果某个列被包含在某个索引中，那么它的相关信息就应该被记录到这个索引列各列 信息部分，所谓的相关信息包括该列在记录中的位置（用pos表示），该列占用的存储空间大小（用len表示），该列实际值（用value表示）。所以索引列各列信息存储的内容实质上就是<pos, len, value>的一个列表。这部分信息 主要是用在事务提交后，对该中间状态记录做真正删除的阶段二，也就是purge阶段中使用的。

　　对照着原图来看这一个删除操作的undo log，主要需要关注的就是索引列各列信息部分，删除一条记录，因为可能包含多个索引，本质上是需要把这些索引都删除的，所以这里记录了一下。一定要对照原图来看。

UPDATE操作对应的undo log

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

####### 不更新主键的情况

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

• 就地更新（in-place update）：更新记录时，对于被更新的每个列来说，如果更新后的列的值和原值占用的存储空间一样大，就可以就地更新，也就是直接在原记录的基础上修改对应列的值。再次强调一边，是被更新的每个 列在更新前后占用的存储空间一样大，有任何一个被更新的列更新前比更新后占用的存储空间大，或者更新前比更新后占用的存储空间小都不能进行就地更新。

• 先删除掉旧记录，再插入新记录：在不更新主键的情况下，如果有任何一个被更新的列更新前和更新后占用的存储空间大小不一致，那么就需要先把这条旧的记录从聚簇索引页面中删除掉，然后再根据更新后列的值创建一条新的记录 插入到页面中。值得注意的是，这里的从页面中删除不是delete mask操作，而是真正的删除。也就是把这条记录从正常记录链表中移除并加入到垃圾链表中，并且修改页面中相应的统计信息（比如PAGE_FREE、PAGE_GARBAGE等 这些信息）。不过这里做真正删除操作的线程并不是做purge操作时使用的另外专门的线程，而是由用户线程同步执行真正的删除操作，真正删除之后紧接着就要根据各个列更新后的值创建的新记录插入。

　　这里如果新创建的记录占用的存储空间大小不超过旧记录占用的空间，那么可以直接重用被加入到垃圾链表中的旧记录所占用的存储空间，否则的话需要在页面中新申请一段空间以供新记录使用，如果本页面内已经没有可用的空间的话， 那就需要进行页面分裂操作，然后再插入新记录。

　　针对UPDATE不更新主键的情况（包括上面的就地更新和先删除旧记录再插入新记录），InnoDB的设计了一种类型为TRX_UNDO_UPD_EXIST_REC的undo日志，它的完整结构和前面的TRX_UNDO_DEL_MARK_REC很相似，不同在于：

• n_updated属性表示本条UPDATE语句执行后将有几个列被更新，后边跟着的<pos, old_len, old_value>分别表示被更新列在记录中的位置、更新前该列占用的存储空间大小、更新前该列的真实值。
• 如果在UPDATE语句中更新的列包含索引列，那么也会添加索引列各列信息这个部分，否则的话是不会添加这个部分的。这个和DELETE 的undo log是一样的。

####### 更新主键的情况

　　在聚簇索引中，记录是按照主键值的大小连成了一个单向链表的，如果我们更新了某条记录的主键值，意味着这条记录在聚簇索引中的位置将会发生改变，比如你将记录的主键值从1更新为10000，如果还有非常多的记录的主键值分布 在1 ~ 10000之间的话，那么这两条记录在聚簇索引中就有可能离得非常远，甚至中间隔了好多个页面。针对UPDATE语句中更新了记录主键值的这种情况，InnoDB在聚簇索引中分了两步处理：

• （1） 将旧的记录进行delete mask 操作，也就是说在UPDATE语句所在的事务提交前，对旧记录只做一个delete mark操作，在事务提交后才由专门的线程做purge操作，把它加入到垃圾链表中。这里一定要和我们上边所说的在 不更新记录主键值时，先真正删除旧记录，再插入新记录的方式区分开。之所以只对旧记录做delete mark操作，是因为别的事务同时也可能访问这条记录，如果把它真正的删除加入到垃圾链表后，别的事务就访问不到了。这个功能就是 所谓的MVCC。

• （2） 根据更新后各列的值创建一条新的记录，并将其插入到聚簇索引中，需要重新从聚簇索引中定位这条记录所在的位置。

　　针对UPDATE语句更新记录主键值的这种情况，在对该记录进行delete mark操作前，会记录一条类型为TRX_UNDO_DEL_MARK_REC的undo日志；之后插入新记录时，会记录一条类型为TRX_UNDO_INSERT_REC的undo日志，也就 是说每对一条记录的主键值做改动时，会记录2条undo日志。

FIL_PAGE_UNDO_LOG页面（简称undo页面）

　　前边讲述表空间的时候说过，表空间其实是由许许多多的页面构成的，页面默认大小为16KB。这些页面有不同的类型，比如类型为FIL_PAGE_INDEX的页面用于存储聚簇索引以及二级索引，类型为FIL_PAGE_TYPE_FSP_HDR的页 面用于存储表空间头部信息的，还有其他各种类型的页面，其中有一种称之为FIL_PAGE_UNDO_LOG类型的页面是专门用来存储undo日志的，这种类型的页面的通用结构如下（以默认的16KB大小为例）：

• File Header和File Trailer：38字节 + 8字节，这是各种页面都有的通用结构
• Undo Page Header：18字节，这是undo页（存储undo日志的页）特有的
• 剩下的空间用来存储真正的undo 日志。

　　其中，Undo Page Header中的各个属性如下：

• TRX_UNDO_PAGE_TYPE：占用2字节，表示本页面存储什么类型的undo log，前面介绍的几种undo log，可以被划分为两大类，也就是这个TRX_UNDO_PAGE_TYPE可选的就是这两类，用来标记本页面用于存储哪个大类的undo日 志，不同大类的undo日志不能混着存储，比如一个Undo页面的TRX_UNDO_PAGE_TYPE属性值为TRX_UNDO_INSERT，那么这个页面就只能存储类型为TRX_UNDO_INSERT_REC的undo日志，其他类型的undo日志就不能放到此页面中了。

  • TRX_UNDO_INSERT（使用十进制1表示）：类型为TRX_UNDO_INSERT_REC的undo日志属于此大类，一般由INSERT语句产生，或者在UPDATE语句中有更新主键的情况也会产生此类型的undo日志。
  • TRX_UNDO_UPDATE（使用十进制2表示），除了类型为TRX_UNDO_INSERT_REC的undo日志，其他类型的undo日志都属于这个大类，比如我们前边说的TRX_UNDO_DEL_MARK_REC、TRX_UNDO_UPD_EXIST_REC啥的，一 般由DELETE、UPDATE语句产生的undo日志属于这个大类。

　　attention： 之所以把undo日志分成两个大类，是因为类型为TRX_UNDO_INSERT_REC的undo日志在事务提交后可以直接删除掉，而其他类型的undo日志还需要为所谓的MVCC服务，不能直接删除掉，对它们的处理需要区分。

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

• TRX_UNDO_PAGE_FREE：与上面的TRX_UNDO_PAGE_START对应，表示当前页面中存储的最后一条undo日志结束时的偏移量，或者说从这个位置开始，可以继续写入新的undo日志。当然，在最初一条undo日志也没写入的情况下， TRX_UNDO_PAGE_START和TRX_UNDO_PAGE_FREE的值是相同的。

• TRX_UNDO_PAGE_NODE：代表一个List Node结构（链表的普通节点）

undo页面链表

　　因为一个事务可能包含多个语句，而且一个语句可能对若干条记录进行改动，而对每条记录进行改动前，都需要记录1条或2条的undo日志，所以在一个事务执行过程中可能产生很多undo日志，这些日志可能一个页面放不下，需要 放到多个页面中，这些页面就通过我们上边介绍的TRX_UNDO_PAGE_NODE属性连成了链表：

　　Innodb把链表中的第一个Undo页面叫做first undo page，其余的Undo页面称之为normal undo page，这是因为在first undo page中除了记录Undo Page Header之外，还会记录其他的一些管理信息。在一个事务执行 过程中，可能混着执行INSERT、DELETE、UPDATE语句，也就意味着会产生不同类型的undo日志。同一个Undo页面要么只存储TRX_UNDO_INSERT大类的undo日志，要么只存储TRX_UNDO_UPDATE大类的undo日志，不能混着存，所 以在一个事务执行过程中就可能需要2个Undo页面的链表，一个称之为insert undo链表，另一个称之为update undo链表。另外，InnoDB规定对普通表和临时表的记录改动时产生的undo日志要分别记录，所以在一个事务中最多有4个 以Undo页面为节点组成的链表。

　　当然，并不是在事务一开始就会为这个事务分配这4个链表，刚刚开启事务时，一个Undo页面链表也不分配。按需分配，啥时候需要啥时候再分配，不需要就不分配。

　　InnoDB规定，每一个Undo页面链表都对应着一个段，称之为Undo Log Segment。也就是说链表中的页面都是从这个段里边申请的，所以他们在Undo页面链表的第一个页面，也就是上边提到的first undo page中设计了一个 称之为Undo Log Segment Header的部分，这个部分中包含了该链表对应的段的segment header信息以及其他的一些关于这个段的信息，所以Undo页面链表的第一个页面比普通页面多了个Undo Log Segment Header， 它的结构如下：

• TRX_UNDO_STATE：本Undo页面链表处在什么状态，也就是这个undo log segment的状态，包括：
  • TRX_UNDO_ACTIVE：活跃状态，也就是一个活跃的事务正在往这个段里边写入undo日志。
  • TRX_UNDO_CACHED：被缓存的状态。处在该状态的Undo页面链表等待着之后被其他事务重用。
  • TRX_UNDO_TO_FREE：对于insert undo链表来说，如果在它对应的事务提交之后，该链表不能被重用，那么就会处于这种状态。
  • TRX_UNDO_TO_PURGE：对于update undo链表来说，如果在它对应的事务提交之后，该链表不能被重用，那么就会处于这种状态。
  • TRX_UNDO_PREPARED：包含处于PREPARE阶段的事务产生的undo日志。事务的PREPARE阶段是在分布式事务中才出现的。

第二十二篇：事务的隔离性和MVCC

　　前面两章分别花了很大篇幅讲解redo log和undo log，它们分别是为了保证事务的持久性和原子性而出现的，现在需要进入的是事务的隔离性。先来看一下多个事务并发执行时可能遇到的一些问题：

• 脏写（dirty write）：一个事务修改了另一个事务未提交的数据，这就是脏写，这种情况是不可接受的。

• 脏读（dirty read）：一个事务读到了另一个未提交事务修改过的数据，那就意味着发生了脏读。

• 不可重复读（Non-Repeatable Read）： 又叫做读已提交，如果一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那就意味着发生了不可重复读。 与此同时，可重复读指的就是一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。

• 幻读（Phantom）：如果一个事务根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，把新插入的记录也能读取出来，这就发生了幻读。 幻读强调的是一个事务按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录。因此如果后续事务是删除了某些记录，而原先事务没有查询到，这种 并不算幻读。而是算作不可重复读。

SQL标准的4个隔离级别

　　注意，4个隔离级别不是MySQL定义的，而是SQL标准中设定的。隔离级别越低，越严重的问题就可能发生。如下：

　　上面的表也说明了，不论是哪种隔离级别，都不允许脏写的发生。InnoDB使用锁来保证不会有脏写情况的发生，也就是在第一个事务更新了某条记录后，就会给这条记录加锁，另一个事务再次更新时就需要等待第一个事务提交了， 把锁释放之后才可以继续更新。

　　不同的数据库厂商对SQL标准中规定的四种隔离级别支持不一样，比方说Oracle就只支持READ COMMITTED和SERIALIZABLE隔离级别。MySQL虽然支持4种 隔离级别，但与SQL标准中所规定的各级隔离级别允许发生的问题却有些出入，MySQL在REPEATABLE READ隔离级别下，是可以禁止幻读问题的发生的。MySQL的 默认隔离级别为REPEATABLE READ，也可以手动修改事务的隔离级别。

MVCC原理

版本链

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

• trx_id：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务id赋值给trx_id隐藏列。

• roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

　　每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），对该记录每次更新后，都会将旧值放到一条undo日志中， 就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务id。

ReadView

　　前面提到了，对于使用READ UNCOMMITTED隔离级别的事务来说，由于可以读取到未提交事务修改过的记录，所以直接读取记录的最新版本就可以了。对于使用SERIALIZABLE隔离级别的事务来说，InnoDB规定使用加锁的 方式来访问记录。对于READ COMMITTED和REPEATABLE READ，都必须保证读取到已经提交了的事务修改过的记录。它俩的区别只是在于是否可重复读。 也就是说假如一个事务已经修改了记录但是尚未提交，本事务是 不能直接读取最新版本记录的，核心问题就是：需要判断版本链中哪个版本是当前事务可见的。为此InnoDB设计了一个ReadView的概念，它主要包括4个比较重要的内容：

• m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表；活跃”指的就是，启动了但还没提交的事务。即BEGIN后的。

• min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，即m_ids中的最小值；

• max_trx_id：表示生成ReadView时系统中应该分配的下一个事务id值。注意max_trx_id并不是m_ids中的最大值，事务id是递增分配的。比方说现在有id为1，2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务 在生成ReadView时，m_ids就包括1和2，min_trx_id的值就是1，max_trx_id的值就是4。

• creator_trx_id：表示生成该ReadView的事务的事务id。

　　有了ReadView后，在访问某一条记录时，只需要按照下面的步骤来判断记录的某个版本是否可见（注意每个版本都记录了生成该版本的trx_id）：

• 如果被访问版本的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：每次读取数据前（只有SELECT）都生成一个ReadView，记住如果是在一个只是select的事务中，是不生成事务id的，也就是这个select语句生成的 ReadView的creator_trx_id是0。然后按照前面说的规则来依次查询版本链即可。

• REPEATABLE READ：只在第一次读取数据时生成ReadView，之后的查询不会重复生成了。但这里我认为是针对的两次相同的查询，如果是不同的查询语句， 还是会生成不同的ReadView的。

　　综上，所谓的MVCC（Multi-Version Concurrency Control， 多版本并发控制），就是在READ COMMITTED和REPEATABLE READ的隔离级别下的 事务在执行普通select操作时访问版本链的过程，这样来支持不同事务的并发读写。之前说执行DELETE语句或者更新主键的UPDATE语句并不会立即把 对应的记录完全从页面中删除，而是执行一个所谓的delete mark操作，相当于只是对记录打上了一个删除标志位，这主要就是为MVCC服务的。

第二十三篇：锁

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

• 读-读情况：多个事务同时读取同一条记录并不会有什么影响，这是被直接允许的。

• 写-写情况：并发事务对相同的记录同时进行写操作，前面提到过，这种就是脏写，任何一种隔离级别都不允许脏写的发生，因此在多个未提交事务对同一条记录 做改动时，需要让他们排队执行，这个过程就是通过锁来实现的。锁就是内存中的一种结构，当一个事务想对这条记录做改动时，首先会看看内存中有没 有与这条记录关联的锁结构，当没有的时候就会在内存中生成一个锁结构与之关联。比方说事务T1要对这条记录做改动，就需要生成一个锁结构与之关联，锁结构里 有两个主要的信息：

  • trx信息：代表生成该锁结构的事务；
  • is_waiting：代表当前事务是否在等待；

• 读-写或写-读情况：也就是一个事务读取，另一个事务进行修改操作。这种情况，可能发生脏读、不可重复读和幻读的问题。当然，幻读问题的产生是因为某个事 务读了一个范围的记录，之后别的事务在该范围内插入了新记录，该事务再次读取该范围的记录时，可以读到新插入的记录，所以幻读问题准确的说并不是因为读取 和写入一条相同记录而产生的，这里需要注意一下。与SQL标准不同的一点就是，MySQL在REPEATABLE READ隔离级别实际上就已经解决了幻读问题。 怎么解决脏读、不可重复读、幻读这些问题呢？一般有两种可选的解决方案：

  • 读操作利用MVCC，写操作加锁：MVCC就是通过生成一个ReadView找到符合条件的记录版本（历史版本在undo log中），查询语句只能读取到在生成 ReadView之前已经提交的事务的更改，在生成ReadView之前未提交的事务或者之后才开启的事务所做的更改是看不见的；写操作都是写一个最新版本的记录， 因此采用MVCC时，读写并不冲突。

  回顾一下就知道，普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用到MVCC读取记录。在READ COMMITTED隔离级别下， 一个事务在执行过程中每次执行SELECT操作时都会生成一个ReadView，ReadView的存在本身就保证了事务不可以读取到未提交的事务所做的更改，也就 是避免了脏读现象；REPEATABLE READ隔离级别下，一个事务在执行过程中只有第一次执行SELECT操作才会生成一个ReadView，之后的SELECT操作都复 用这个ReadView，这样也就避免了不可重复读和幻读的问题。

  • 读、写操作都采用加锁的方式：这样的性能一般会比较低，读的时候也进行加锁。

一致性读（Consistent Reads）

　　事务利用MVCC进行读取操作称之为一致性读，或者一致性无锁读，也叫作快照读。所有普通的SELECT语句在READ COMMITTED、REPEATABLE READ隔 离级别下都算是一致性读，一致性读并不会对表中的任何记录做加锁操作，其他事务可以对记录进行更改。

锁定读（Locking Reads）

　　除了MVCC方式，还可以通过加锁的方式来解决读-写和写-写情况，在使用加锁方式时，由于既需要允许读-读，又要使读-写、写-读、写-写相互阻塞，所以MySQL 将锁进行了分类：

• 共享锁：Shared Locks，简称S锁，在事务要读取一条记录时，需要先获取该记录的S锁。

• 独占锁：又称排他锁，Exclusive Locks，简称X锁，在事务需要改动一条记录时，需要先获取该记录的X锁。其中S和S锁是兼容的，S和X是不兼容的。

锁定读的语句

　　前面提到，采用加锁的方式解决脏读、不可重复读和幻读等问题时，读取一条记录需要获取该记录的S锁，这其实是不严谨的，有些时候我们在读取记录时 就想获取记录的X锁，来禁止别的事务读写该记录，因为X锁排斥所有的锁，为此MySQL设计了两种比较特殊的select语句：

• 对读取的记录加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：对一条记录进行DELETE操作，首先在B+树中定位到这条记录的位置，然后获取一下这条记录的X锁，再执行delete mask操作。

• UPDATE：对一条记录进行UPDATE又分为三种情况：

  • 如果未修改该记录的主键值并且被更新的列占用的存储空间在修改前后未发生变化，则先在B+树中定位到这条记录的位置，然后再获取一下记录的X锁，最后 在原记录的位置进行修改操作。其实我们也可以把这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读。

  • 如果未修改该记录的主键值但是至少有一个被更新的列占用的存储空间在修改前后发生变化，也就是不能原地修改了。则先在B+树中定位到这条记录的位置， 然后获取一下记录的X锁，将该记录彻底删除掉（就是把记录彻底移入垃圾链表），最后再插入一条新记录。这个定位待修改记录在B+树中位置的过程看成是一个 获取X锁的锁定读，新插入的记录由INSERT操作提供的隐式锁进行保护。

  • 如果修改了该记录的主键值，相当于在原记录上做DELETE操作后再来一次INSERT操作，加锁操作就需要按照DELETE和INSERT的规则进行了。

• INSERT：一般情况下，新插入一条记录的操作并不加锁，InnoDB通过一种隐式锁来保护这条新插入的记录在本事务提交前不被别的事务访问。当然，在一些 特殊情况下INSERT操作也是会获取锁的。

多粒度锁

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

　　表级锁比较粗糙，这里强烈建议看原文章的举例，比较容易理解。反正InnoDB提出了一种意向锁（Intention Lock） 的概念：

• 意向共享锁（Intention Shared Lock），简称IS锁，当事务准备在某条记录加S锁时，需要先在表级别加上IS锁。

• 意向独占锁（Intention Exclusive Lock），简称IX锁，当事务准备在某条记录加X锁时，需要先在表级别上加IX锁。

　　IS、IX是表级锁，它们的提出仅仅是为了在之后加表级别的S锁和X锁时，可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录， 因为假如我需要给表加一个S锁，那么我需要先知道表中的记录是不是有X锁。也就是说IS锁和IX锁是兼容的，IX和IX锁也是兼容的。如下表：

InnoDB存储引擎中的锁

　　首先，对于其他存储引擎来说，比如MyISAM、MEMORY、MERGE，它们只支持表级锁，而且这些引擎并不支持事务，所以使用这些存储引擎的锁一般都是针对当 前会话来说的。比方说在Session 1中对一个表执行SELECT操作，就相当于为这个表加了一个表级别的S锁，如果在SELECT操作未完成时，Session 2中对这个 表执行UPDATE操作，相当于要获取表的X锁，此操作会被阻塞，直到Session 1中的SELECT操作完成，释放掉表级别的S锁后，Session 2中对这个表执行 UPDATE操作才能继续获取X锁，然后执行具体的更新语句。

　　InnoDB存储引擎既支持表锁，也支持行锁。表锁实现简单，占用资源较少，不过粒度很粗，有时候你仅仅需要锁住几条记录，但使用表锁的话相当于为表中的所 有记录都加锁，所以性能比较差。行锁粒度更细，可以实现更精准的并发控制。

InnoDB中的表级锁

　　InnoDB支持的表级锁非常鸡肋，只会在一些特殊情况（崩溃恢复）过程中用到。在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储 引擎是不会为这个表添加表级别的S锁或者X锁的。另外，在对某个表执行一些诸如ALTER TABLE、DROP TABLE这类的DDL语句时，其他事务对这个表并发执 行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞，同理，某个事务中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，在其他会话中对 这个表执行DDL语句也会发生阻塞。这个过程其实是通过在server层使用一个称之为元数据锁（英文名：Metadata Locks，简称MDL）来实现的，一般情况下也不会 使用InnoDB存储引擎自己提供的表级别的S锁和X锁。

　　表级别的AUTO-INC锁：在使用MySQL过程中，我们可以为表的某个列添加AUTO_INCREMENT属性，之后在插入记录时，可以不指定该列的值，系统会自动为它 赋上递增的值，它的原理参考原文章。

InnoDB中的行级锁（重点）

　　InnoDB的行级锁是重点，它将行锁分成了各种类型，对同一条记录加不同类型的行锁作用不同。主要类型有：

• Record Locks：前面提到的记录锁就是这种类型，也就是仅仅把一条记录锁上，官方的名称是：LOCK_REC_NOT_GAP

• Gap Locks：MySQL在REPEATABLE READ隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用MVCC方案解决，也可以采用加锁方案解决。但是在使 用加锁方案解决时有个大问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些幻影记录加上LOCK_REC_NOT_GAP。这里InnoDB提出了 一种称之为Gap Locks的锁，官方的类型名称为：LOCK_GAP。

　　在原图中，如果我们给number值为8的记录加上了LOCK_GAP，意味着不允许别的事务在number值为8的前面的间隙插入新的记录，也就是number列的值 （3，8）这个区间的新记录是不允许立即插入的。比方说有另外一个事务再想插入一条number值为4的新记录，它定位到该条新记录的下一条记录的number值为8，而 这条记录上又有一个gap锁，所以就会阻塞插入操作，直到拥有这个gap锁的事务提交了之后，number列的值在区间(3, 8)中的新记录才可以被插入。

　　这个gap锁的提出仅仅是为了防止插入幻影记录而提出的，虽然有共享gap锁和独占gap锁这样的说法，但是它们起到的作用都是相同的。而且如果你对一条记录加 了gap锁（不论是共享gap锁还是独占gap锁），并不会限制其他事务对这条记录加Record Locks或者继续加gap锁，再强调一遍，gap锁的作用仅仅是为了防止插入 幻影记录的而已。

　　这里有一个新的问题了，给一条记录加了gap锁只是不允许其他事务往这条记录前边的间隙插入新记录，那对于最后一条记录之后的间隙，也就是表中 number值为20的记录之后的间隙该咋办呢？也就是说给哪条记录加gap锁才能阻止其他事务插入number值在(20, +∞)这个区间的新记录呢？这时候应该想起我们在 前边唠叨数据页时介绍的两条伪记录了：Infimum记录，表示该页面中最小的记录以及Supremum记录，表示该页面中最大的记录。为了实现阻止其他事务插入 number值在(20, +∞)这个区间的新记录，我们可以给索引中的最后一条记录，也就是number值为20的那条记录所在页面的Supremum记录加上一个gap锁。

• Next-Key Locks：有时候我们既想锁住某条记录，又想阻止其他事务在该记录的前面的间隙插入新的记录，所以InnoDB就提出了一种Next-Key Locks锁。

• Insert Intention Locks：我一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了所谓的gap锁（next-key锁也包含gap锁），如果有 的话，插入操作需要等待，直到拥有gap锁的那个事务提交。但是InnoDB规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新 记录，但是现在在等待。InnoDB就把这种类型的锁命名为Insert Intention Locks，官方的类型名称为：LOCK_INSERT_INTENTION，也可以称为插入意向锁。

• 隐式锁：

　　这一章还有比较大段的内容介绍InnoDB锁的内存结构的，比较细则，可以先不深入研究。