本文是原书第1章的摘抄，大部分概念还是熟悉的，重点还是 InnoDB 本身。

MySQL 逻辑架构

flowchart TB
	c[客户端] --> conn[连接/线程处理]
	
	subgraph server
	conn --> p[解析器]
	p --> o[优化器]
	end
	
	subgraph storage
	o --> s[存储引擎]
	end

连接管理与安全性

• 线程池
• 身份验证
• TLS

优化与执行

• 重写查询
• 表的读取顺序
• 选择合适的索引
• hint
• 从MySQL 5.7.20版本开始，查询缓存已经被官方标注为被弃用的特性，并在8.0版本中被完全移除。

并发控制

读写锁

• 共享锁（shared lock）/读锁（read lock）
• 排他锁（exclusive lock）/写锁（write lock）

锁的粒度

• 表锁（table lock）
• 行锁（row lock）
• 元数据锁，用于修改表名或者schema
• 8.0还引入了应用程序级别的锁

事务

原子性（atomicity）

一个事务必须被视为一个不可分割的工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。

一致性（consistency）

数据库总是从一个一致性状态转换到下一个一致性状态。如果事务最终没有提交，该事务所做的任何修改都不会被保存到数据库中。

隔离性（isolation）

通常来说（涉及隔离级别），一个事务所做的修改在最终提交以前，对其他事务是不可见的。

持久性（durability）

一旦提交，事务所做的修改就会被永久保存到数据库中。

• 持久性也分很多不同的级别。
• 不可能有100%的持久性保障。

隔离级别

READ UNCOMMITTED（未提交读）

在READ UNCOMMITTED级别，在事务中可以查看其他事务中还没有提交的修改。这个隔离级别会导致很多问题，从性能上来说，READ UNCOMMITTED不会比其他级别好太多，却缺乏其他级别的很多好处，除非有非常必要的理由，在实际应用中一般很少使用。

读取未提交的数据，也称为脏读（dirty read）。

READ COMMITTED（提交读）

READ COMMITTED满足前面提到的隔离性的简单定义：一个事务可以看到其他事务在它开始之后提交的修改，但在该事务提交之前，其所做的任何修改对其他事务都是不可见的。这个级别仍然允许不可重复读（nonrepeatable read），这意味着同一事务中两次执行相同语句，可能会看到不同的数据结果。

大多数数据库系统的默认隔离级别是READ COMMITTED。

REPEATABLE READ（可重复读）

REPEATABLE READ解决了READ COMMITTED级别的不可重复读问题，保证了在同一个事务中多次读取相同行数据的结果是一样的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读（phantom read）的问题。

所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（phantom row）。InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读的问题。

REPEATABLE READ是MySQL默认的事务隔离级别。

SERIALIZABLE（可串行化）

SERIALIZABLE是最高的隔离级别。该级别通过强制事务按序执行，使不同事务之间不可能产生冲突，从而解决了前面说的幻读问题。简单来说，SERIALIZABLE会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际应用中很少用到这个隔离级别，除非需要严格确保数据安全且可以接受并发性能下降的结果。

ANSI SQL 的隔离级别

隔离级别	脏读	不可重复读	幻读	加锁读
READ UNCOMMITTED	是	是	是	否
READ COMMITTED	否	是	是	否
REPEATABLE READ	否	否	是	否
SERIALIZABLE	否	否	否	是

死锁

死锁是指两个或多个事务相互持有和请求相同资源上的锁，产生了循环依赖。当多个事务试图以不同的顺序锁定资源时会导致死锁。当多个事务锁定相同的资源时，也可能会发生死锁。

sql
-- 事务1
START TRANSACTION;
UPDATE StockPrice 
SET close = 45.50 WHERE stock_id = 4 and date = '2020-05-01';UPDATE StockPrice 
SET close = 19.80 WHERE stock_id = 3 and date = '2020-05-02';COMMIT;

-- 事务2
START TRANSACTION;
UPDATE StockPrice 
SET high = 20.12 WHERE stock_id = 3 and date = '2020-05-02';UPDATE StockPrice 
SET high = 47.20 WHERE stock_id = 4 and date = '2020-05-01';COMMIT;

InnoDB 检测到循环依赖后会立即返回错误，或者超过锁等待超时时间限制后直接终止查询（回滚）。应用程序在设计时需要考虑处理死锁，大多数情况下只需要重新从头开始执行被回滚的事务即可。

事务日志

事务日志用于提高事务的效率。

存储引擎更新数据的顺序是：

graph LR;
	mem[内存] --> tlog[事务日志] --> hdd[磁盘];

存储引擎只需要更改内存中的数据副本，而不用每次修改磁盘中的表，这会非常快。然后再把更改的记录写入事务日志中，事务日志会被持久化保存在硬盘上。

因为事务日志采用的是追加写操作，是在硬盘中一小块区域内的顺序I/O，而不是需要写多个地方的随机I/O，所以写入事务日志是一种相对较快的操作。

最后会有一个后台进程在某个时间去更新硬盘中的表。因此，大多数使用这种技术（write-ahead logging，预写式日志）的存储引擎修改数据最终需要写入磁盘两次。

AUTOCOMMIT

默认情况下，单个INSERT、UPDATE或DELETE语句会被隐式包装在一个事务中并在执行成功后立即提交，这称为自动提交（AUTOCOMMIT）模式。通过禁用此模式，可以在事务中执行一系列语句，并在结束时执行COMMIT提交事务或ROLLBACK回滚事务。

如果设置了AUTOCOMMIT=0，则当前连接总是会处于某个事务中，直到发出COMMIT或者ROLLBACK，然后MySQL会立即启动一个新的事务。

还有一些命令，当在活动的事务中发出时，会导致MySQL在事务的所有语句执行完毕前提交当前事务。这些通常是进行重大更改的DDL命令，如ALTER TABLE，但LOCK TABLES和其他一些语句也具有同样的效果。

sql
-- 设置 autocommit
SET SESSION AUTOCOMMIT=0;
SET GLOBAL AUTOCOMMIT=0

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

在事务中混合使用存储引擎

MySQL不在服务器层管理事务，事务是由下层的存储引擎实现的。所以在同一个事务中，混合使用多种存储引擎是不可靠的。

隐式锁定和显式锁定

InnoDB使用两阶段锁定协议（two-phase locking protocol）。在事务执行期间，随时都可以获取锁，但锁只有在提交或回滚后才会释放，并且所有的锁会同时释放。前面描述的锁定机制都是隐式的。InnoDB会根据隔离级别自动处理锁。

显示锁定

sql
SELECT ... FOR SHARE -- MySQL8.0的新语句
-- 取代以前的 SELECT...LOCK IN SHARE MODE
SELECT ... FOR UPDATE

MySQL还支持LOCK TABLES和UNLOCK TABLES命令，这些命令在服务器级别而不在存储引擎中实现。建议除了在禁用AUTOCOMMIT的事务中可以使用之外，其他任何时候都不要显式地执行LOCK TABLES，不管使用的是什么存储引擎。

多版本并发控制

MySQL 的大多数事务型存储引擎都不是简单的使用行级锁，而是和 MVCC 结合使用。MVCC 的实现机制各不相同。MVCC 可以视为行级锁的变种，开销更低，实现了非阻塞的读操作，写操作也只锁定必要的行。

MVCC的工作原理是使用数据在某个时间点的快照来实现的。MVCC 的实现变体包括乐观并发控制和悲观并发控制

MVCC仅适用于REPEATABLE READ和READ COMMITTED隔离级别。READ UNCOMMITTED与MVCC不兼容，是因为查询不会读取适合其事务版本的行版本，而是不管怎样都读最新版本。SERIALIZABLE与MVCC也不兼容，是因为读取会锁定它们返回的每一行。

复制

flowchart TB
	s[源节点] --二进制日志--> r1[副本节点1]
	s --二进制日志--> r2[副本节点2]

MySQL提供了一种原生方式来将一个节点执行的写操作分发到其他节点，这被称为复制。对于在生产环境中运行的任何数据，都应该使用复制并至少有三个以上的副本，理想情况下应该分布在不同的地区（在云托管环境中，称为region）用于灾难恢复计划。

多年来，MySQL中的复制变得十分复杂。全局事务标识符、多源复制、副本上的并行复制和半同步复制是一些主要的更新。

数据文件结构

在8.0版本中，MySQL将表的元数据重新设计为一种数据字典，包含在表的.ibd文件中。这使得表结构上的信息支持事务和原子级数据定义更改。

在操作期间，我们不再仅仅依赖information_schema来检索表定义和元数据，而是引入了字典对象缓存，减少 I/O。每个表的.ibd和.frm文件被替换为已经被序列化的字典信息（.sdi）。

InnoDB引擎

InnoDB是MySQL的默认事务型存储引擎，它是为处理大量短期事务而设计的。

• InnoDB将数据存储在一系列的数据文件中，这些文件统被称为表空间（tablespace）。
• InnoDB使用MVCC来实现高并发性，并实现了所有4个SQL标准隔离级别。InnoDB默认为REPEATABLE READ隔离级别。
• 通过间隙锁（next-key locking）策略来防止在这个隔离级别上的幻读：InnoDB不只锁定在查询中涉及的行，还会对索引结构中的间隙进行锁定，以防止幻行被插入。

InnoDB 内部做的优化

• 从磁盘预取数据的可预测性预读
• 能够自动在内存中构建哈希索引以进行快速查找的自适应哈希索引（adaptive hash index）
• 用于加速插入操作的插入缓冲区（insert buffer）

作为事务型存储引擎，InnoDB可以通过一些机制和工具支持真正的在线“热”备份，包括Oracle专有的MySQL Enterprise Backup和开源的Percona XtraBackup。

从MySQL 5.6开始，InnoDB引入了在线DDL，它最初只支持有限的使用场景，但在5.7和8.0版本中引入了就地更改schema机制，允许在不使用完整表锁和外部工具的情况下进行特定的表更改操作，这大大提高了MySQL InnoDB表的可操作性。

JSON 文档支持

JSON类型在5.7版本被首次引入InnoDB，它实现了JSON文档的自动验证，并优化了存储以允许快速读取。

InnoDB还引入了SQL函数来支持在JSON文档上的丰富操作。MySQL 8.0.7的进一步改进增加了在JSON数组上定义多值索引的能力。将常用访问模式匹配到可以映射JSON文档值的函数这一特性可以进一步加快对JSON类型的读取访问查询。

数据字典的变化

MySQL 8.0删除了基于文件的表元数据存储，并将其转移到使用InnoDB表存储的数据字典中。这给所有类似修改表结构这样的操作带来了InnoDB的崩溃恢复事务的好处。

原子 DDL

MySQL 8.0引入了原子数据定义更改。这意味着数据定义语句现在要么全部成功完成，要么全部失败回滚。这是通过创建DDL特定的Undo日志和Redo日志来实现的

参考

• 17.7 InnoDB Locking and Transaction Model