MySQL库知识点汇总 Version 2

本文内容由YiWu整理，Thanks！

MySQL 体系结构

MySQL 是一个关系型数据库，MySQL 实例操作数据库文件以读写表、行记录。在 OS 看来，MySQL 实例就是一个拥有多个线程的单进程。单机情况下数据库与实例是一一对应的，集群情况下存在一个数据库被多个数据实例使用的情况。

数据库：物理操作系统文件或其他形式文件类型的集合，常见以 idb 结尾的文件；
实例：MySQL数据库由后台线程以及一个共享内存区组成，数据库实例才是真正用来操作数据库文件。

MySQL 架构包括客户端、Server 端和存储引擎三部分：

执行一条 SQL 语句的过程包括：

如果开启了 Query Cache 且通过哈希查找命中缓存，则将查询结果直接返回；
解析器对该 SQL 语句进行词法语法语义解析生成解析树；
优化器根据解析树生成执行计划；
执行引擎根据待查询表的存储引擎类型的不同调用不同的API得到查询结果；
由 MySQL Server 过滤后将查询结果返回给客户端且保存到 Query Cache 中。

其中在单表查询中，优化器生成执行计划的一般步骤如下：

根据搜索条件，找出所有可能使用的索引；
计算全表扫描的代价；
计算使用不同索引执行查询的代价；
对比各种执行方案的代价，找出成本最低的那一个；

对于 InnoDB 来说，读取一个页面的 IO 成本默认是 1.0，读取以及检测一条记录是否符合搜索条件的成本是 0.2（可调节的）在这个过程中需要依赖一些数据辅助判断，可能是通过访问索引对应的 B+ 树来获取数据（index dive）或者是直接依赖对表或索引的统计数据。

MySQL 的核心在存储引擎，主要有 Memory、InnoDB 和 MyISAM，其中 InnoDB 与 MyISAM区别在于：

	InnoDB	MyISAM
底层实现	B+ 树	堆
锁粒度	表锁、行锁	表锁
事务	支持 ACID 以及四种隔离级别，默认 RR	不支持

除此之外，InnoDB 还支持外键、自适应哈希索引等。

存储引擎 InnoDB

MySQL 5.6 使用 InnoDB 1.2 ，最新的 MySQL 出到 8.0+。InnoDB 架构中主要有各类后台线程和缓冲池两部分：

后台线程

InnoDB是多线程的模型，不同线程负责处理不同的任务，常见的有以下几种：

master thread：调度分发任务到其他线程；
purge thread：回收 undo 页；
page cleaner thread：刷出脏数据页；
redo log writer thread：写 redo log。

内存缓冲池

由于 CPU 和磁盘的读写速度差异过大，引入内存缓冲池来提供 InnoDB 的读写性能，内存缓冲池分为 redo log 缓冲池和一般缓冲池两种：

redo log 缓冲池，连续的内存区域，粒度是块（512B），用以存放 redo log 的缓冲，持久化到磁盘上顺序结构的 redo log 文件组；
一般缓冲池，随机的内存区域，粒度是页（16KB），用以存放索引页、数据页、undo 页等，持久化到磁盘上随机结构的日志文件；
哈希缓冲池，用以存放热点页，O(1) 而非 O(logN) 的查询效率。

为提供并发能力，InnoDB 允许有多个缓冲池实例，每个页根据求余散列分配到不同的缓冲池实例中。

TODO 待补充 free list | flush list | LRU list

数据组织方式

表空间

表空间由若干个连续的区组成。

系统表空间。默认情况下，InnoDB 会在数据目录（/usr/local/var/mysql/）下创建一个默认名为 ibdata1、初始大小为 12MB 的可自动扩展文件。与独立表空间相比，系统表空间的开头有许多记录整个系统属性的页面；
独立表空间。在 MySQl 5.6.6 开始，InnoDB 不再默认把各个表的数据存储到系统表空间中而是为每一个表建立一个独立表空间。由多少个表就会有多少个独立表空间；
其他表空间。如通用表空间、undo 表空间、临时表空间。

段

段是一个逻辑概念，是一些碎片区的页和一些专属于该段的完整区的集合。段这么设计的原因应该是为了减少随机 IO 而不至于让数据量少的表浪费空间。

为段分配存储空间的策略是在刚开始向表中插入数据时从碎片区以单个页面为单位来分配存储空间，当某个段已经占用了 32 个碎片区页面后，以完整区为单位来分配（原先占用的碎片区页面并不会被复制到新申请的完整区上）

段的类型有数据段（B+ 树叶子节点）、索引段（B+ 树非叶子节点）和回滚段 3 种等，每张表中每新建一个索引都会生成一个数据段和索引段。在系统表空间第 5 号页面中存储了指向 128 个回滚段的 128 个指针。

区

区是物理连续的 64 个页，默认大小是 1MB。

同一个的区的页面在物理磁盘上是连续的，不同区之间的页面不一定连续。

除了碎片区直属于表空间，其他的每个区都有所属的段。

页

页是 InnoDB 中管理磁盘空间以及磁盘和内存交互的基本单位，默认大小是 16KB。数据页之间以双向链表的方式串联起来。

File Header：各个类型的页如数据页、undo 页等都会有 File Header 结构，其中有字段：页类型、页号、上一个页页号、下一个页页号、页面被最后修改时对应 lSN、页所属表空间等信息；
Page Header：数据页中独有的，其中有字段：页目录中的槽数量、最后插入行记录的位置、该页中行记录数量、该页所属的索引 ID、该页在 B+ 树中所处的层级等信息；
Infimum + supermum：默认创建的行记录，虽然没有主键信息，但是被规定代表一个页面中最小的行记录和最大的行记录；
User Records：目前数据页中已有的行记录。
Free Space：待使用的空间；
Page Directory：页目录由多个槽组成。将所有行记录按最多 8 个分组，每个组中最后一条行记录在页面中的偏移量作为槽，Page Directory 按照索引键值顺序存放这些槽，便于二分查找目标行记录所在组；
File Tailer：包含与 File Header 中相对应的页检验和。如果页面刷盘只输出一部分，那么 File Tailer 中的校验和与 File Header 中的会不一致；

以上是表 t 中存有 (1, 100, 'aaa') ～(4, 400, 'dddd') 四条数据的情况

行记录

以 create table t(c1 INT, c2 INT, c3 VARCHAR(10000), primary key(c1)) charset=ascii row_format=compact; 为例，由于把 c1 列指定为主键，所以没有创建隐藏列 row_id，该表中的行记录格式如下：

delete_mask：标记该记录是否被删除；
heap_no：表示当前记录在页面堆中的相对位置；
record_type：表示当前记录的类型，0 是 B+ 树叶节点的普通行记录，1 是 B+ 树内节点的目录项记录，2 是 Infimum 记录，3 是 Supermum 记录；
next_record：表示下一跳记录的相对位置，通过 next_record 字段，将一个数据页中的所有行记录串联成一个单向链表，头节点是 Infimum 记录，尾节点是 Supermum 记录；
row_id：如果用户没有在表中定义主键以及不允许存储 NULL 值的 UNIQUE 键，会自动添加一个 row_id 的隐藏列用以构造聚簇索引；
trx_id：自动创建的隐藏列，记录上一次对该行记录改动的事务 id；
roll_pointer：自动创建的隐藏列，一个指向上一个版本的行数据对应的 undo log 所在位置的指针；

索引

索引是存储引擎用于快速查找记录的一种数据结构，需要占用一定的内存。设置合适的索引能够显著提供查询数据的性能，但由于在插入、删除、修改数据时也要更新索引结构占用一定的系统开销，所以适合读多写少的场景。

将数据页中行记录在竖着表示如下：

聚簇索引

聚簇索引根据行记录主键值的大小进行数据页、页中行记录的排序，内节点存放主键值+页号这两个列，叶节点存放完整的用户行记录。

聚簇索引以 B+ 树结构组织数据页，不论是存放用户行记录的叶节点层还是存放目录项行记录的内节点层，都是根据页中行记录主键大小顺序串联成一个双向链表，页内的行记录按主键大小串联成一个单向链表。

如果用户没有主动为表指定主键，InnoDB 会自动为该表中每个行记录创建 row_id 隐藏列，按照隐藏列的值维护一个 B+ 树结构并以这种方式存储在磁盘上。

查找目标用户行记录主键值为 20 的流程如下：

确定目录项行记录所在页。在页 33 中通过二分查找 1 < 20 < 320 发现页 30；
通过目录项行记录所在页确定目标用户行记录所在页。在页 30 中通过二分查找 12 < 20 < 209 发现页 9；
在该页中定为到具体的行记录。在页 9 中通过二分查找 12 < 20 < 100 发现主键为 20 的用户行记录。

二级索引

二级索引（辅助索引）根据行记录用户指定列如 c2 列的大小进行数据页、页中行记录的排序，内节点存放二级索引列值 + 主键值 +页号这三个列，页节点存放 c2 列 + 主键这两个列。

二级索引以 B+ 树结构组织数据页，不论是存放用户行记录的叶节点层还是存放目录项行记录的内节点层，都是根据页中行记录二级索引列大小顺序串联成一个双向链表，页内的行记录按二级索引列大小串联成一个单向链表。

当二级索引列值相同时，再按照主键值排序。

查找目标用户行记录 c2 列为 4 的流程如下：

确定目录项行记录所在页。在页 44 通过二分查找 2 < 4 < 9 发现页 42；
通过目录项行记录所在页确定目标用户行记录所在页。在页 42 中通过二分查找 2 < 4 <= 4 发现页 34 和页 35（c2 列并没有唯一性约束，所以 c2 列值为 4 的行记录可能分布在多个数据页中）；
在该页中定为到具体的行记录。在页 34 中通过二分查找 3 < 4 <= 4 发现主键为 1 的用户行记录，在页 35 中通过二分查找 4 <= 4 <= 4 发现主键为 4 和 10 的用户行记录；
回表。根据主键值去聚簇索引中再查找一遍完整的用户行记录。

MyISAM 的数据和索引是分开存储的，这种存储引擎中的索引全部是二级索引，叶节点存储的是列+行号（对于定长记录格式的记录来说）

联合索引

对表上的多个列进行索引。从本质上说，联合索引也是一颗 B+ 树，不同的是联合索引的键值数量大于等于 2。如果一张表上有联合索引 (a, b, c)，那么找 (a), (a, b), (a, b, c) 都会走联合索引，但是找 (b), (c), (b,c), (a,c) 无法避免额外排序。

索引使用

覆盖索引

从辅助索引中就能得到查询结果，而不需要查询聚集索引中的记录，从而减少大量的 IO 操作。常用于某些统计问题，如一张表上既有主键索引也有辅助索引，那么 SELECT COUNT(*) FROM t; 优先走辅助索引。通过 EXPLAIN 查看执行计划中的 Extra 字段，如果有 USING INDEX 代表使用了覆盖索引。

优化器选择不使用辅助索引的情况，SELECT * FROM t WHERE id > 100 and id < 1000; 这种需要行记录全部字段数据的情况，尽管走辅助索引数据是有序存放的，但是根据其叶指针查找的行记录则是无序的，因此变成了磁盘上的离散读操作，所以优化器会选择直接走主键索引。但如果访问的数据量很小的话，优化器会走辅助索引。

全文索引

底层实现为倒排索引，适用于长文本中单词检索的场景，全文索引结构为：

id	word	ilist（documentId，position）
1	cold	(1:6), (4:8)
2	days	(3:2), (6:2)
3	hot	(1:3), (4:4)

以 cold 为例，其出现在第 1 个文档的第 6 个位置，第 4 个文档的第 8 个位置。

哈希索引

InnoDB 为热点页在 B+ 树索引的基础上建立哈希索引，哈希函数为除法散列，除数的取值为略大于 2 倍的哈希缓冲池中页数量的质数，哈希冲突时采用拉链法。

InnoDB 事务

事务执行流程

一个包含写操作的事务的正常执行流程是：

BEGIN; 指令
从磁盘中加载待更新的行记录到内存，并加写锁；
记录内容是 undo log 的 redo log 写入 redo log 缓存区；
当前事务的 undo log 写入一般缓冲区；
记录内容是数据变更的 redo log 写入 redo log 缓存区；
数据页更新，形成脏页；
COMMIT; 触发 redo log 刷盘。（TODO 补充二者刷盘时机）

如果在第 6 步之后第 7 步执行之前，3、5 步的 redo log 已经落盘，此时用户主动回滚或系统宕机重启，执行以下流程进行事务回滚：

根据 redo log 进行记录和 undo log 两部分的恢复；
查看 undo log 的状态发现事务还未提交，使用 undo log 进行事务回滚。

ACID 特性

事务遵循 ACID 四大特性：

原子性（atomicity）：一个事务的所有操作，要么全部完成，要么全部不完成，通过 undo log 完成；
一致性（consistency）：由约束一致性（创建表时所指定的外键、唯一索引等）和数据一致性（由其他三个特性共同保证的结果）保证，通过 undo log 实现；
隔离性（isolation）：一个事务提交前的任何操作，其他事务都无法看见，通过锁 / MVCC 实现；
持久性（durability）：一个事务一旦提交，其结果就是永久性的，通过 redo log 实现。

读问题

脏读

与在缓冲池中已经被修改但还没有刷盘的脏页不同，脏读读的是一般缓冲池中被事务修改但还没提交的脏数据。举个例子，在 RU 级别下，一张表 t 中只有 1 个行记录 a = 1，执行如下操作：

时间	会话 A	会话 B
1		BEGIN;
2	BEGIN;	SELECT * FROM t ; # a = 1;
3	INSERT INTO t SELECT 2;
4		SELECT * FROM t # a = 1, 2;

在会话 A 中事务并没有提交，但是在会话 B 中的两次 select 操作取得不同的结果，也就是产生了脏读，违反了事务的隔离性。

不可重复读

不可重复读指的是在一个事务内多次读同一个行记录，会得到不一样的结果。与在当前事务内会读取到未提交数据的脏读不同，不可重复读只会在当前事务内读取到其他事务已经提交的数据。举个例子，在 RU 级别下，一张表 t 中只有 1 个行记录 a = 1，执行如下操作：

时间	会话 A	会话 B
1	BEGIN;	BEGIN;
2	SELECT * FROM t ; # a = 1;
3		INSERT INTO t SELECT 2;
4		COMMIT;
5	SELECT * FROM t # a = 1, 2;

在以上场景仅采用 Record Lock，也就是在时间 2 仅锁住列 a 等于 1 的索引加上 Record Lock 即范围为 a∈[1]，允许行记录 2 的插入，于是在会话 A 中一个事务的两次读取结果因为另一个事务的修改提交而不一致，即产生了不可重复读，违反了事务的一致性。

不可重复读一般来说也是可以接受的，在 Oracle、MySQL Server 的隔离级别默认是 RC，即允许不可重复读的现象。

幻读

在 MySQL 官方文档中将不可重复读定义为幻读，也就是说他们本质是一类问题，即当前事务还未结束前读取到了其他事务提交之后的结果。非要强行说区别的话（如果面试官的知识体系跟我们不一样的话），那么不可重复读强调 update, delete，而幻读强调 insert 带来的不可重复读。

InnoDB 采用 Next-Key Lock 算法避免出现幻读，对于不可重复读中提供的例子，时间 2 会在范围 (-∞，+∞) 上加 X 锁，因此时间 3 会话 B 中执行的插入操作会被阻塞。

丢失更新

丢失更新指的是一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据不一致。

在当前数据库的任何隔离级别下即使是 RU，对于行的 DML 操作需要对行或其他粒度级别的对象加锁，因此理论上不会出现丢失更新的情况。但考虑多用户计算机系统环境的场景，若出现以下情况就会发生丢失更新：

时间	会话 A	会话 B
1	BEGIN;
2	查询余额 a = 1000，放入本地内存，显示给终端用户 A	BEGIN;
3	UPDATE a = a - 100;	查询同一行记录 a = 1000，放入本地内存，显示给终端用户 B
4	COMMIT;	UPDATE a = a - 500;
5		COMMIT;

结果是两个事务都提交成功，但是最终的余额却不是1000 - 100 - 500 = 400，出现了丢失更新的现象，违反了事务的一致性。要避免丢失更新发生，需要让事务串行化执行，即在查询时对用户读取的记录加上一个排他锁，会话B 必须等待会话 A 执行完成之后才可执行。

事务隔离级别

四种读问题对应以下四种事务隔离级别，隔离级别越低，事务请求的锁越少或保持锁的时间越短。用 set @@tx_isolation = 'READ-COMMITTED' 可以手动设定某个事务的隔离级别。

读未提交 RU

Read Uncommitted，没有应用锁机制，一个事务可以读到另一个事务未提交的结果，存在脏读和不可重复读问题。

读提交 RC

Read Committed，一个事务的执行操作只有在提交后它的更新结果才会被其他事务看见，可以解决脏读，存在不可重复读问题。

一致性读中 MVCC 使用每个事务中的每条 SELECT 语句执行时都会生成一个 ReadView；
锁定读中除了唯一性的约束检查和外键约束检查需要 Gap Lock，其他全是用 Record Lock。

可重复读 RR

Repeatable Read，InnoDB 默认级别。在一个事务中对于同一份数据的读取结果总是相同的，无论是否有其他事务提交了对这份数据的修改操作，可以解决脏读、不可重复读/幻读。

一致性读中 MVCC 实现中每个事务中的第一条 SELECT 语句执行时会生成一个 ReadView；
锁定读中基本都是用 Next-Key Lock，除了当查询的索引是唯一索引时退化成 Record Lock。

可串行化 Serialiable

Serialiable，最高隔离级别，此时单机不再具有并发性，常用于分布式事务。

锁实现中当系统变量 autocommit = 0 时对每个 SELECT 语句后自动加上 LOCK IN SHARE MODE，即为每个读取操作加一个共享锁。

MySQL 日志文件

binlog

binlog 是一种不论存储引擎是否是 InnoDB 都会记录数据库变更的日志，用以记录数据库的变更历史、主从复制和数据恢复。相较于 InnoDB 中 redo log 的数据恢复，binlog 执行粒度更粗，恢复周期更长。

当 MyISAM 中每一条 SQL 语句执行前或 InnoDB 中每一个事务 commit 前，都会往 binlog 中写入会修改数据的 SQL 语句的二进制形式。

可以用指令 show binlog events in 'mysql-bin.00005'; 来查看 binlog 中的内容

statement 格式，存在问题，from now

row 格式，存每一行之前和之后的状态，全部 +1，从库放大

没有字段去标识事务的先后关系

global transaction id 在事务 commit 时

但可能出现死锁问题，目前流行的row模式可以避免很多冲突甚至死锁问题，即将二进制文件由statement模式（记录SQL语句）改成row模式（记录物理页变化），用Row+RC模式的隔离级别，可以很大程度上提高数据库的读写并行度。

redo log

redo log 是用来进行事务重做所需的文件。为了保证事务的一致性，最简单的做法是在每次事务提交的时候将该事务涉及修改的数据页全部刷新到磁盘中，但是这么做会有严重的性能问题，主要体现在两个方面：

因为 Innodb 是以页 16KB 为单位跟磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷盘的话，太浪费资源了！
一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，随机 IO 写入性能太差。

因此引入 redo log，具体来说就是在修改数据之前先将数据页物理更改情况写入 redo log，只要 redo log 落盘了，数据变更是一定可以保证生效的。相较于直接更改数据页并刷盘，记录 redo log 并刷盘的方式写入数据量小、顺序写入快，将数据库的性能瓶颈由写数据页的 IO 转换成写 redo log 的 IO。

组织方式

不论是 redo log 文件组还是 redo log 缓冲池都是内存/磁盘连续的一块区域，以 redo log block（512 Byte）为最小粒度管理，redo log 文件组的前 4 个 block 用来存储一些管理信息。

redo log block 包含 12B 的日志块头和 4B 的日志块尾，中间部分 496B 的 log block body 以不可分割的组（Mini-Rransaction）的形式写入 redo log，图中 mtr_t1_1 和 mtr_t1_2 对应事务 t1 的两个 mtr，mtr_t2_1 和 mtr_t2_2 对应事务 t2 的两个 mtr。

以表示插入一条使用紧凑行格式的类型为 MLOG_COMP_REC_INSERT 的 redo log 为例，介绍 redo log 的格式：

大体来说每条 redo log 包括 type|space ID|page number|data 共四个字段，比如将第 0 号表空间的第 100 号页面中偏移量为 1000 处的 field1 的值更新为 2。redo log 具有幂等性，即使多次执行同一个 redo log，结果始终一致。

刷盘时机

redo log buffer 空间不足时。redo log buffer 占用的内存毕竟是有限的；
事务提交时。当 MySQL 中的系统变量 innodb_flush_log_at_trx_commit 是默认的级别 1 时，会在每次事务提交时都将对应的 redo log 落盘；

级别 0，事务提交时仅将 redo log 写到 redo log buffer，由后台线程来异步刷出到磁盘，所以如果 MySQL 奔溃是会丢数据的；级别 2，事务提交时需要将 redo log 写到 OS 缓冲区，由 OS 后台线程来异步刷出到磁盘，所以如果机器突然断电是会丢数据的。
LRU 链表 old 区域的脏数据页和 flush 链表中的脏数据页被刷出到磁盘时。数据页落盘要求对应的 redo log 页一定也要落盘；

刷脏页和 checkpoint 是在不同的线程上执行的，并不是说每次有脏页刷新就要去执行一次 checkpoint
checkpoint 时。checkpoint 线程修改 flushed_to_disk_lsn 就代表有新的 redo log 落盘，其实是间接驱动脏数据页刷盘；
redo log 写线程定时触发时。redo log 后台写线程将 redo log buffer 中的 redo log 每秒一次刷盘；
关闭 MySQL 时。正常关闭时 MySQL 会保证所有缓存落盘再退出。

checkpoint

由于我们需要循环使用容量有限的 redo log 文件组，所以需要 checkpoint 后台线程定时更新当前哪些 redo log 可以被覆盖写。checkpoint 线程执行一次 checkpoint 可以分为两个步骤：

计算 checkpoint_lsn 的值，这是一个记录当前有多少 redo log 记录的数据变更落盘的变量（checkpoint_lsn 的更新一定发生在脏页刷出线程执行之后，但跟 redo log 什么时候刷盘无关。）；
将 checkpoint_lsn 与对应的 redo log 文件组偏移量以及 checkpoint_id 写到 redo log 文件组的管理信息中。

mtr_1 的 redo log 和数据页都落盘，mtr_2 的 redo log 落盘但对应的数据还没落盘，redo log 中各个 lsn 变量的意义：

lsn：当前产生了多少 redo log（可能落盘可能还在缓冲区）
flushed_to_disk_lsn：当前有多少 redo log 落盘
checkpoint_lsn：当前有多少 redo log 记录的数据变更落盘

分别对应三个阶段：redo log 还没落盘、redo log 落盘但对应的数据还没落盘、redo log 和数据页都落盘。不考虑循环结构时 lsn >= flushed_to_disk_lsn >= checkpoint_lsn。

如果没有引入 redo log，所有 lsn 值都没有意义，那 checkpoint 也没有意义，所以 checkpoint 是 redo log 中的概念。

崩溃恢复

恢复起点是 checkpoint_lsn，恢复终点是从 checkpoint_lsn 开始扫描直到第一个 redo log block 没有填满的地方。去掉不需要恢复的（redo log 中的 checkpoint_lsn < 待恢复物理页的 lsn），聚合需要恢复的（根据 redo log 中的 space ID 和 page number 把对同一个页面进行修改的 redo log 都在一起执行），按照 redo log 中记录的内容恢复物理页。

undo log

undo log 是用来事务回滚时所需的文件。为了实现事务的原子性，InnoDB 在进行增删改（无查）操作时，都需要先把对应的 undo log 记录下来，这样在发生错误时，就能回滚到事务之前的数据状态。

日志格式

以表 create table t (id int not null, key1 varchar(100), col varchar(100), primary key(id), key idx_key1(key1)) Engine=InnoDB charset=utf8; 为例说明各类操作对应的 undo log 格式，这是一个 id 列为聚簇索引，key1 列为二级索引的表。

Insert undo log

SQL 语句 insert into t(id, key1, col) values (1, 'AWM', '狙击枪'); insert into t(id, key1, col) values (2, 'M416', '步枪'); 第一条对应的 insert undo log 为：

该事务对应的 undo log no 是 0，主键占用的存储空间长度是 4，真实值是 1。因为插入的反向操作是删除，所以只需要这些信息就够了。

delete undo log

跟上边同一个事物中再执行一条 SQL 语句 delete from t where id = 1; 对应的 delete undo log 为：

因为跟插入是同一个事务，所以trx_id 都是 100；
因为是这个事务产生的第 3 条 undo log，所以 undo no 是 2；
因为是做删除，所以 roll_pointer 指向最近一次对该记录做改动的 redo log；
因为表 t 有聚簇索引和二级索引两个索引，所以要记录 2 行（pos, len, value），代表第 pos 列占用 len 字节大小实际值是 value；

被删除的行记录一开始只是把 deleted_flag 标记为 1，之后会被 purge 线程在满足系统中最早产生的 ReadView 不再访问时会从 History 链表移动到垃圾链表，此时意味着真正的删除，这个行记录可以被覆盖写了。

update undo log

增删改对二级索引的操作与聚簇索引基本一致，所以以下按键值包含主键和二级索引键

不更新键值且就地更新

各个被更新的列在更新前后占用的存储空间是一样大的，所以这样的语句可以执行就地更新，也就是直接在原记录的基础上修改对应列的值。如 update t set key1 = 'm249', col = '机枪' where id = 2; 对应的 delete undo log 为：

由于本条 update 语句中更新了索引列key1的值，所以需要记录一下索引列各列信息部分，也就是把 id 列和 key1列更新前的信息填入。

不更新键值且先删再插

只要不满足就地更新的条件，都要先把旧的行记录从聚簇索引页面中由用户线程真正删除（而非标记之后由 purge 线程删除），然后再根据更新后列的值创建一条新的行记录插入到页面

更新键值

在聚簇索引中，记录是按照主键值的大小连成了一个单向链表的。如果更新了某条记录的主键值，那么这条记录在聚簇索引中的位置将会发生改变，所以需要先对旧记录标记删除再在聚簇索引上合适的位置插入新记录，这个 update 操作生成 2 条 redo log。

日志类型

undo log 页按是否可以被立马删除分为两大类：

insert undo log page。由 INSERT 或 UPDATE 语句中有主键更新的情况产生。因为此类操作的记录只对事务本身可见，对其他事务不可见（这是事务隔离性的要求），故该 undo log 可以在事务提交后直接删除；
update undo log page。除了 insert undo log page 的其他 undo log，由 DELETE 或 UPDATE 语句产生。因为该 undo log 可能要在 MVCC 中链成版本链，所以在事务提交后会放入 history 链表，满足条件后被 purge 线程删除。

不同大类的 undo log 不能混着存储，所以一个事务的执行过程中需要 2 个 undo 页链表——insert undo 页链表和 update undo 页链表，对应 2 个 undo log 段。

其实一个事务还会有临时表对应的 2 个 undo 页链表，总共 4 个 undo 页链表，区分临时表与普通表的原因事临时表的事务写 undo 页的时候不需要先写对应的 redo 页，而普通表需要。

在系统表空间的第 5 号页中存储了 128 个回滚段地址，分别指向只有一个 Rollback Segment Header 页的 128 个回滚段，每个 Rollback Segment Header 页支持 1024 个 undo slot，也就是 1024 个 undo 页面链表。理论上一个 MySQL 最多同时支持 128 * 1024 / 4 = 32768 个事务同时进行。

以事务改动普通表记录为例，执行流程如下（不考虑 cache）：

到系统表空间第 5 号页中遍历寻找一个可用的回滚段；
到该回滚段的 Rollback Segment Header 页上遍历寻找一个可用的 undo slot 分配给当前事务；
分配一个 undo log segment 并从中申请一个页作为 undo 页链表的 first undo page；
事务把 undo log 写到分配给它的 undo 页链表。

purge

当一个事务提交后，就会把这个事务执行过程中产生的 update undo log page 插入到所属回滚段的 Undo Log Header 中的 History 链表头部，等待被 purge 线程回收。

而 purge 线程清理 undo log 的准则是只清理那些不再被最早生成的 ReadView 访问的 undo 日志以及打了标记删除的记录

具体来说，在每个事务提交的时候都会生成一个 trx_no，在每个 ReadView 生成的时候都会把当前系统中最大的事务 no 值 +1 赋值给 ReadView.trx_no。如果回滚段中的 History 链表中的 undo log 事务 no 值小于当前系统中最早生成的 ReadView.trx_no 或者该 undo log 被标记删除，那么就会从 History 链表中移除并释放对应的空间。

其他日志

错误日志，记录MySQL运行错误、警告信息，可以通过这个来优化数据库参数配置；
慢查询日志，记录运行时间超过某个阈值的所有 SQL 语句，通过这个来做 SQL 层面的优化。

MVCC

MySQL 默认使用 MVCC 实现事务隔离级别。

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 InnoDB 实现 RC 和 RR 这两种隔离级别的一种具体方式。RU 总是读取最新的数据行，无需使用 MVCC，而 Serialiable 需要对所有读取的行都加锁。由当前行记录与 undo log 组成的版本链如下：

RC 级别中事务的针对每一个表的每一次 SELECT 操作前都会生成一个 ReadView，而 RR 只在第一次生成，之后重复使用同一个 ReadView。

一个 ReadView 中包含 4 个信息：

m_ids 表示在生成 ReadView 时当前系统中活跃的读写事务的事务 id 列表；
min_trx_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 后才开启，所以不可访问；
若 ReadView.min_trx_id <= trx_id <= ReadView.max_trx_id 且 trx_id 在 m_ids，表明生成该版本的事务在当前事务生成 ReadView 时还活跃，所以不可访问；
若 ReadView.min_trx_id <= trx_id <= ReadView.max_trx_id 且 trx_id 不在 m_ids，表明生成该版本的事务在当前事务生成 ReadView 前已经提交，所以可访问。

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上面的步骤判断可见性，依此类推直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。

锁

锁类型

行级锁

行级共享锁（S Lock），允许事务读一行记录。
行级排他锁（X Lock），允许事务删除或更新一行记录。

表级锁

表级共享锁（S Lock），用读锁锁表，会阻塞其他事务修改该表数据；
表级排他锁（X Lock），用写锁锁表，会阻塞其他事务对该表的读和写；
意向共享锁（IS Lock），事务想要获得一张表中某几行的行共享锁；
意向排他锁（IX Lock），事务想要获得一张表中某几行的行排他锁。

在为行记录加行级共享/排他锁之前要先拿到该行记录所在数据表的表级锁，而要获取表级锁可以通过意向共享/排他锁判断是否已被其他事务加了表级锁（这是 InnoDB 做的，用户也无法手动操作意向锁）。

意向锁的提出使得在加表级共享/排他锁时可以快速判断当前表是否已被其他事务加了表级锁，避免遍历查看表中各个行记录有没有行级锁。由于只是一个用来表明加表锁的意向，所以意向锁之间是兼容的。如果不同事务之间的锁兼容，则当前加锁事务可以持有锁，如果有冲突则会等待其他事务的锁释放。

锁的兼容性	表级排他锁(X)	意向排他锁(IX)	表级共享锁(S)	意向共享锁(IS)
表级排他锁(X)	N	N	N	N
意向排他锁(IX)	N	Y	N	Y
表级共享锁(S)	N	N	Y	Y
意向共享锁(IS)	N	Y	Y	Y

InnoDB 锁

表级锁

InnoDB 实现了细粒度的行级锁，在对某个表执行 SELECT | INSERT | DELETE | UPDATE 语句时是不会为这个表添加表级别的共享/排他锁。不建议用户主动使用表级锁，不过在用户主动加行锁之前，InnoDB 会去申请表锁以及表意向锁。

在 InnoDB 中有一个表级别的自增锁。在为表中某个列添加 AUTO_INCREMENT 属性之后插入行记录可以不指定该列的值而由系统自动为它赋予递增的值。当 INSERT 语句在执行前无法确定具体要插入多少条行记录如使用 INSERT ... SELECT 或 LOAD DATA 等插入语句时，默认使用自增锁为 AUTO_INCREMENT 修饰的列生成对应的值，一个事务在持有自增锁的过程中其他事务针对同一张表的 INSERT 语句都会被阻塞，直到当前事务的 INSERT 语句执行完成后。（自增锁在 INSERT 语句执行完就释放而非整个事务执行完才释放）。

如果在 INSERT 语句执行前就知道插入多少条行记录，那么会采用一个轻量级锁，会在该列递增之后就释放掉而不需要等整个 INSERT 语句执行完。

行级锁

InnoDB 中的行锁是通过对索引数据上的记录加锁实现的，有 3 种行锁算法，分别是：

Record Lock（记录锁）：单个行记录的锁，锁数据，不锁 Gap；
Gap Lock（间隙锁）：锁定一个行记录范围，不锁数据，仅锁数据前面的 Gap；
Next-key Lock：行锁与间隙锁的结合，既锁数据又锁 Gap；
Insert Intention Lock：等待 Gap Lock 时会生成一个插入意向锁，但他并不会阻止其他事务获取任意类型的锁，所以还挺鸡肋的。

Gap Lock 的提出仅仅只是为了避免插入幻影记录，如果对一条记录加上 Gap Lock 并不会限制其他事务对这条记录加 Record Lock 或继续加 Gap Lock。

InnoDB 所有的行锁算法都是基于索引实现的，锁定的也都是索引或索引区间。在默认的 RR 级别锁实现中，InnoDB 会自动为增删改操作的行加 X 锁，读不加锁（只有在 Serializable 级别会为读加 S 锁），并在事务提交或回滚后自动释放锁。

锁内存结构

锁所在的事务信息：一个指向事务的指针；
索引信息：对于行锁需要知道加锁的行记录是属于哪个索引的，聚簇索引或二级索引；
Space ID：行记录所在表空间；
Page Number：行记录所在页号；
n_bits：用来记录一个页面中哪些行记录上了锁；
type_mode｜lock_mode：表明锁的模式，有共享锁、排他锁、意向共享锁、意向排他锁、自增锁五种；
type_mode｜lock_type：表明锁的类型，有表级锁或行级锁两种；
type_mode｜rec_lock_type：表明行锁的具体类型，有记录锁、间隙锁、Next-key Lock、插入意向锁等，在第 9 个 bit 记录当前事物是获取到锁还是在等待。

加锁规则

Only limit in MySQL version 5.7, innoDB engine and REPEATED READ isolation level. Firstly describe a table for the convenience of the following example. The id column is a primary key, c column is a no unique index and no index on d column as shown below

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values (0,0,0), (5,5,5), (10,10,10), (15,15,15), (20,20,20), (25,25,25);

Principle 1: Consider adding next-key locks firstly.
Principle 2: Locks only exist on scanned rows.
Optimize 1: The next-key locks will weaken to record locks when querying on unique index with equivalent condition.

There is an example ex 1. If query table t with statement select * from table t where id = 10 for update, it will lock index id with record lock 10. please note that this is because there is a row with id 10 existing in table, if not the origin next-key lock according principle 1 will not weaken to record lock.
Optimize 2: The next-key locks will weaken to gap locks when the right boundary of next-key locks is not equal to the value in equivalent condition.

There is an example ex 2. If query table t with statement select * from t where c = 12 for update, it will lock index c with next-key lock (10, 12] and gap lock (12, 15) which is a weakness from next-key lock (12, 15] according to principle 1.

There is another example ex 3. If query table with statement select * from t where id = 7 for update, it will lock index id with gap lock (5, 10) which is a weakness from next-key lock (5,10] according principle 1 because 7 is not equal to 10. Please compare this with ex 1.
Bug 1: Access until the first value of the condition is not met when a range query on a unique index.

Copied from jingtao.fun

SELECT 语句分析

在默认的 RR 隔离级别下，InnoDB 对于行记录的查询会加 Next-key Lock。比如一个索引有 10，11，13，20 这四个值，那么该索引可能被 Next-Key Lock 的区间为 (-∞, 10], (10, 11], (11, 13], (13, 20], (20, +∞)

根据查询方式的不同，采用的锁也不同：

当查询的索引是辅助索引或是多列唯一索引中的某一列，用 Next-key Lock 进行锁定。举个例子，一张表 z 中，列 a 是主键，列 b 是一般键即辅助索引，已有行记录 (1, 1)，(3, 1)，(5, 3)，(7, 6)，(10, 8)，数据特点是按列 a 有序的基础上按列 b 有序，执行如下操作：

时间	会话A	会话B	会话C
1	begin;
2	select * from t where b = 3 for update;	begin;	begin;
3		select * from z where a = 5 lock in share mode; -- 可执行	insert into z select 8, 6; -- 阻塞
4		insert into z select 4, 2;	insert into z select 2, 0;
5		insert into z select 6, 5;	insert into z select 6, 7;

在时间 2 往后，会话 B 中 SQL 语句全部阻塞，会话 C 中全部可以立即执行。原因是会话 A 中通过辅助索引列 b 进行查询，会对两个索引分别按下面的方式锁定：

对于聚集索引列 a，其仅对列 a 等于 5 的索引加上 Record Lock，即范围为 a∈[5]；
对于辅助索引列 b，对键值 3 加上作用范围 (1, 3] 的 Next-Key Lock，且对下一个键值 6，加上作用范围 (3, 6] 的 Gap Lock，又因为 3 != 6，所以总范围为 b∈(1, 6)；

当查询的索引是唯一索引时，InnoDB 会将 Next-key Lock 降级为 Record Lock。举个例子，在一张表 t 中，列 a 是主键，已有行记录 1, 2, 5，执行如下操作：

时间会话A 会话B

1 begin;

2 select * from t where a = 5 for update;

3 begin;

4 insert into t select 4;

5 commit; --成功，不需要等待

6 commit;

在会话 A 中首先对 a = 5 进行 X 锁定，而由于 a 是主键且唯一，因此锁定的仅是 5 这个值，而不是 (2, 5) 这个区间，这样在会话 B 中插入值 4 不会阻塞，可以立即插入并返回。
当查询不使用索引时，对主键索引全表加上 Next-key Lock，相当于锁表了。

Record Lock/Gap Lock/Next-key Lock 都是在索引上做的，如果不用索引，这些行锁无法发挥作用。

读方式

一致性读

对应乐观锁 MVCC 的快照读。在事务隔离级别 RC 和 RR 下，InnoDB 使用非锁定的一致性读，也就是即使要访问的行记录上有 X 锁也可以读一个快照数据。举个例子，在一张表 t 中现在只有一条 id = 1 的行记录，执行如下操作：

时间	会话 A	会话 B
1	begin;
2	select * from parent where id = 1;
3		begin;
4		update parent set id = 3 where id = 1;
5	select * from parent where id = 1;
6		commit;
7	select * from parent where id = 1;
8	commit;

在 RC 级别下读取被锁定行的最新一份快照数据，即步骤 5 查询结果非空，步骤 7 查询结果为空；

在 RR 级别下读取事务开始时的行记录版本，即步骤 5 查询结果非空，步骤 7 查询结果非空；

锁定读

对应悲观锁的当前读。在转账交易等需要保证数据强一致性的情况下，用户需要显式地对数据库读取操作加锁以保证数据逻辑的一致性，这要求数据库支持加锁语句，即使是 SELECT 这样的只读操作。InnoDB 对于 SELECT 支持两种一致性等待锁定读：

SELECT ... LOCK IN SHARE MODE，对读取的行记录加一个 S 锁，其他事务也可以读；
SELECT ... FROM UPDATE，对读取的行记录加一个 X 锁，其他事务读阻塞。

分布式 MySQL

CAP/Base 理论

CAP 理论指的是对于一个分布式系统来说，当设计读写操作时，只能能同时满足以下三点中的两个：

一致性（Consistence） : 所有节点访问同一份最新的数据副本；
可用性（Availability）: 非故障的节点在合理的时间内返回合理的响应而不是错误或者超时的响应；
分区容错性（Partition tolerance） : 分布式系统出现网络分区的时候，仍然能够对外提供服务。网络分区指的是分布式系统中因为某些故障导致某些节点之间不连通，整个网络分成了若干区域。

分布式系统理论上不可能选择 CA 架构。举个例子，若系统出现“分区”，系统中的某个节点在进行写操作。为了保证 C，必须要禁止其他节点的读写操作，这就和 A 发生冲突了。如果为了保证 A，其他节点的读写操作正常的话，那就和 C 发生冲突了。

实际开发时要根据不同场景选择不同的策略，对于强一致性的场景如银行转账则选择 CP，对于 CDN 或对访问速度有要求的场景选择 AP。

BASE 理论是基于 CAP 定理演化而来，是对 CAP 中一致性和可用性权衡的结果，其核心思想是即使无法做到强一致性，但每个应用可以根据自身业务特点，采用适当的方式使系统达到最终一致性。

基本可用（Basically Available）：系统出现不可预知的故障时，允许损失部分可用性来保证核心可用，但不等价于不可用。比如搜索引擎拉长对查询结果的响应时间，在线商城显示降级页面；
软状态（Soft state）：允许系统存在中间状态，并且该中间状态不会影响系统整体可用性。即允许系统在不同节点间副本同步的时候存在延时；
最终一致性（Eventually consistent）：系统中所有数据副本，在经过一段时间的同步后，数据最终能够达到一致性的状态。

分布式事务

2PC

3PC

分区

对于访问数据库的应用来说，逻辑上只有一个表或一个索引，在物理上可能由数十个物理分区组成。在 MySQL 中仅支持水平分区（切分），不支持垂直分区。且其分区是局部分区索引，即一个分区中既存数据又存索引。当前 MySQL 支持的分区：

RANGE 分区：行数据基于一个给定连续区间的列值来分区；
LIST 分区：根据一个给定的离散列值来分区；
HASH 分区：根据用户自定义的表达式返回值来分区；
KEY 分区：根据 MySQL 数据库提供的哈希函数来分区。

对于 OLAP（在线分析处理），该应用大多需要频繁查询一张很大的表，分区可以很好的提升查询性能；对于OLTP（在线事务处理），该应用大多只是通过索引返回几条记录，而分区与否其对应的 B+ 树高基本还是 2-3，也就是都需要 2-3 次 IO 操作，因此使用分区不一定能带来性能上的提升。

主从复制

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

binlog 线程：负责将主服务器上的数据变更写入 binlog 中；
I/O 线程：负责从主服务器上读取 binlog，并写入从服务器的 relay log 中；
SQL 线程：负责读取中继日志并重放其中的 SQL 语句。

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。读写分离能提高性能的原因在于：

主从服务器负责各自的读和写，极大程度缓解了锁的争用；
从服务器可以使用 MyISAM 存储引擎，提升查询性能以及节约系统开销；
增加冗余，提高可用性。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

一致性哈希

对于分布式缓存，由于不同机器上存储不同对象的数据，一般通过除法散列来实现这些缓存机器的负载均衡。但是当系统做扩容或者缩容时，如果不迁移改变了映射关系的数据，会出现查询不到数据的问题。

为了避免分布式系统在扩容/缩容时发生过多数据迁移的问题，人们发明了一致性哈希算法。

与普通哈希算法对节点数量进行取模运算不同，一致性哈希算法是对一个固定的值（2^32）进行取模运算，将存储节点和数据都映射到一个头尾相连的哈希环上（比如用存储节点 IP 和数据 ID 作为 key），数据经过哈希之后的值往顺时针方向找到的第一个节点，就是存储该数据的节点。如果增加或者移除一个存储节点，仅影响该节点在哈希环上顺时针相邻的后继节点，其它数据不会受到影响（图 2 中只有 key-02 需要被迁移到节点 D，图 3 中只有 key-01 需要被迁移到节点 B）。

但是在一致性哈希算法下节点在哈希环上的分布不一定均匀，这样就会导致有大量情况集中在一个节点上的问题。并且当系统扩容的时候，新节点仅仅只能分担它的后继节点的负载，对整个系统的提升较小。

为此，引入虚拟节点来解决一致性哈希负载不均衡问题。具体做法是，不再将真实节点映射到哈希环上，而是将虚拟节点映射到哈希环上，并将虚拟节点映射到实际节点，所以这里有「两层」映射关系。如果有访问请求哈希到「A-01」这个虚拟节点，通过再一次映射就可以到真实节点 A。

当某个真实节点下线，会移除它对应的所有虚拟节点而这些虚拟节点按顺时针方向的下一个虚拟节点，可能会对应不同的真实节点，这样就可以让更多节点共同分担节点下限导致的压力。

时间	会话A	会话B
1	begin;
2	select * from t where a = 5 for update;
3		begin;
4		insert into t select 4;
5		commit; --成功，不需要等待
6	commit;