MySQL

发表于 2024-04-26 更新于 2024-06-12 分类于数据库

MySQL 基础，包括索引、事务、锁、日志、BufferPool等

基础

SQL 查询语句执行过程

连接器：客户端与 Server 层建立连接，管理连接、校验用户身份；
查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行（MySQL 8.0 已删除该模块）
解析 SQL：通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
执行 SQL：
- 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列；
- 优化阶段：基于查询成本的考虑，选择查询成本最小的执行计划；查询优化器；
- 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

MySQL 数据文件

db.opt，用来存储当前数据库的默认字符集和字符校验规则。
table.frm ，存放表结构，在 MySQL 中建立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。
table.ibd，存放表数据，~~表数据既可以存在共享表空间文件（文件名：ibdata1）里~~，也可以存放在独占表空间文件（文件名：表名字.ibd）。这个行为是由参数 innodb_file_per_table 控制的，若设置了参数 innodb_file_per_table 为 1，则会将存储的数据、索引等信息单独存储在一个独占表空间，从 MySQL 5.6.6 版本开始，它的默认值就是 1 了，因此从这个版本之后， MySQL 中每一张表的数据都存放在一个独立的 .ibd 文件。

表空间文件的结构

InnoDB 行格式

共有 Redundant、Compact、Dynamic、Compressed 四种，下图时 Compact 行格式的结构

InnoDB 页格式

InnoDB 的数据是按「数据页」为单位来读写的，数据页的默认大小是 16KB

名称	说明
文件头	页的信息；两个指针指向上一个/下一个数据页
页头	页的状态信息
最小和最大记录	两个虚拟伪记录，分别表示页中的最小记录和最大记录
用户记录	储存所有的行记录
空闲空间	还没被用到的空间
页目录	用户记录相对位置，页中所有记录的索引
文件尾	校验页是否完整

数据页中的页目录存储各个槽，用户记录按照「主键」顺序组成单向链表，可以通过二分查找法的方式进行检索。

索引

索引字段特性分类

主键索引
唯一索引
前缀索引
普通索引

索引个数分类

单例索引
联合索引：最左匹配原则（MySQL 有查询优化器，所以字段在 where 子句的顺序并不重要）

最左匹配原则：按照最左优先的方式进行索引的匹配，范围查询的字段可以用到联合索引，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。

Innodb 采用 B+Tree 作为索引的优势

MySQL 底层实现的 B+ 树在原有的 B+ 树的基础上做了改动

叶子节点采用双向链表连接

所有节点都是 16KB 数据页

非叶子节点中有多少个子节点，就有多少个索引

nosql 的 MongoDB 采用 B 树实现，适用于大量的单个索引（而不是范围查询）查询的场景；

查询底层节点的磁盘 I/O次数少。非叶子节点只存放索引，相比 B 树，每个非叶子节点能存储更多的索引，树形结构更加矮胖；
插入和删除效率高。非叶子节点中的都是冗余索引，所有索引都在叶子节点中又存储了一次；
树层低，磁盘 I/O 次数少。若每行数据 1KB，三层最多可存放千万级数据量；
范围查询，磁盘 I/O 次数少。叶子节点采用双向有序链表连接；其他树结构需要中序遍历，磁盘 I/O 次数多；

B+Tree 索引结构

主键索引/聚簇索引：叶子节点存放实际数据、事务 id、用于事务和 MVCC 的回滚指针；
二级索引：叶子节点存放主键值；

Explain 执行计划

数据扫描类型 type

All：全表扫描；
index：全索引扫描；
index merge：索引合并。先将使用到的索引先进行求交集、并集之后在进行回表；
range：索引范围扫描。使用 < 、>、in、between 等关键词，只检索给定范围的行；
ref：非唯一索引等值匹配；
eq_ref：唯一索引多表联查等值匹配；
const：主键或唯一索引常量等值匹配；

额外信息 extra

Using filesort ：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候，这时不得不选择相应的排序算法进行；
Using temporary：使了用临时表保存中间结果，如排序 order by 和分组查询 group by；
Using where：存储引擎层返回行以后在 MySQL 服务层应用 where 过滤条件；
Using index：所需数据可以直接在二级索引值和主键值中得到，采用覆盖索引，不回表；
Using index condition 索引下推：可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数；

是否需要索引

需要索引的情况

唯一性字段
范围查询 WHERE
排序查询 GROUP BY 、 ORDER BY
覆盖索引，全索引扫描优于全表扫描

不需要索引的情况

不需要范围查询、排序查询
大量重复数据
频繁修改
数据量少

索引优化

前缀索引：减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。

覆盖索引：联合索引和主键值中包括了查询数据，避免回表

主键索引自增：追加记录时不需要移动数据；避免页分裂造成内存碎片；

主键字段长度尽可能短：聚簇索引非叶子节点可以装下更多的索引；二级索引叶子节点可以装下更多的主键值，占用空间更小

索引 NOT NULL：简化索引统计、值比较过程；减少 1 字节的行格式长度；

联合索引区分度大的在前：建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。

如果索引的区分度很小，假设字段的值分布均匀，那么无论搜索哪个值都可能得到一半的数据（惯用的百分比界线是"30%"）MySQL 查询优化器，会忽略索引，进行全表扫描

索引失效

左或者左右模糊匹配
查询条件中对索引列做了
- 计算
- 函数
- 类型转换操作
联合索引未遵循最左匹配原则
条件语句中 OR 前后子句一个有索引一个没索引

Count 计算

遍历索引，优先遍历 key_len 最小的二级索引，只有当没有二级索引的时候，才会采用主键索引来进行统计。

事务

事务是由存储引擎实现的，InnoDB 支持事务

特性

持久性是通过 redo log （重做日志）保证的；
原子性是通过 undo log（回滚日志）保证的；
隔离性是通过 MVCC（多版本并发控制）或锁机制保证的；
一致性则是通过持久性+原子性+隔离性保证的；

并行事务会引发的问题

脏读：一个事务读到了另一个未提交事务修改过的数据
不可重复读：一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样
幻读：一个事务内多次查询某个查询条件的记录数量不同

隔离级别

隔离级别（是否会出现xx问题）	脏读	不可重复读	幻读
READ-UNCOMMITTED 读未提交	√	√	√
READ-COMMITTED 读已提交	×	√	√
REPEATABLE-READ 可重复读	×	×	√
SERIALIZABLE 可串行化	×	×	×

读未提交：允许读取尚未提交的数据变更。直接读取最新的数据
读已提交：允许读取并发事务已经提交的数据。「每个 SQL 语句执行前」都会重新生成一个 Read View
可重复读：对同一字段的多次读取结果都是一致的。「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View
可串行化：所有的事务依次逐个执行，完全服从 ACID 的隔离级别。采用加读写锁的方式来避免并行访问

虽然 InnoDB 默认采用可重复读隔离级别，但基本不会出现幻读，因为其采用 next-key lock（记录锁+间隙锁）和 MVCC 来避免幻读的发生

快照读（select）：采用 MVCC 方式解决了幻读。事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，保证事务隔离性；
- 在执行第一个查询语句后，会创建一个 Read View，后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的
当前读/共享锁（select for update）：通过 next-key lock（记录锁+间隙锁）方式解决了幻读，读到的记录是最新的。如果有其他事务在 Next-Key Lock 范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入；
- update、insert、delete 都是当前读，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作；
- 如果 update 是已经被锁住的记录，就生成插入意向锁并进入阻塞状态；
- 当前读给某记录加锁了，那么对该记录已修改但未提交的事物就无法提交这个修改了，直到锁被释放；
- Next-Key Lock 对索引记录加上行锁（Record Lock），对索引记录两边的间隙加上间隙锁（Gap Lock），是针对事务隔离级别为可重复读或以上级别而设计的；

MVCC 多版本并发控制

InnoDB 事务是通过 Read View 和聚簇索引中的两个隐藏列 实现 MVCC 的

Read View 字段

聚簇索引中的两个隐藏列

trx_id，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
roll_pointer，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。

MVCC 如何实现可重复读？

事务 A 读某字段时，会先读取该字段的 trx_id，如果 Read View 中记录的该 trx_id 是活跃事务，则沿着 roll_pointer（即 undo log 版本链）寻找旧版本字段；如果 Read View 中记录的该 trx_id 是当前事务 或 已提交事务，则返回该记录。【注意，Read View 字段不随活跃事务的提交而改变，它是该事务开启时的数据库事务状态快照】

可重复读下发生幻读

事务 A 先对某块记录执行快照读
事务 B 插入数据到该块记录中，并提交事务 B
事务 A 再对该块记录执行当前读或更新记录
会读取到事务 B 插入的数据，因为当前读会读取最新的记录

如何避免？

若事务中会对数据采取当前读，则在事务刚开始时就执行，通过 next-key lock 避免其他事物再插入数据。

锁

MySQL 加锁时，先生成锁结构，然后设置锁的状态，即初始化锁再竞争锁；如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁；

锁的种类

全局锁
表级锁
- 表锁
- 意向表：用于快速判断表里是否有记录被加锁。意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突
- 元数据表 MDL 锁：更表结构时需要申请 MDL 写锁
- AUTO-INC 锁：配置文件 innodb_autoinc_lock_mode = 2, binlog_format = row，既能提升并发性，又不会出现数据一致性问题
行级锁
- Record Lock 记录锁，区分读锁和写锁，锁定存在的记录；
- Gap Lock 间隙锁，不区分读锁和写锁；间隙锁用来锁一个范围，间隙锁之间是兼容的；
- Next-Key Lock 临键锁，区分读锁和写锁；
- 插入意向锁，特殊的间隙锁，用于不同事务的并发控制和冲突检测；插入意向锁和不同事物的间隙锁之间是冲突的，当某个事务的插入语句遇到间隙锁时，会产生插入意向锁；
隐式锁
- 延迟加锁机制，特点是只有在可能发生冲突时才加锁
- 使用场景：事务 B 插入记录的索引 == 事务 A 已插入且未提交的索引，事务 A 插入的记录上的「隐式锁」会变「显示锁」且锁类型为 X 型的记录锁，所以事务 B 想获取 ~~S 型~~next-key 锁时会遇到锁冲突，事务 B 进入阻塞状态。

行级锁实现

行级锁时 Innodb 实现高效事务的关键

加锁是加在索引上的【加行级锁时还要加表锁：X 类型的意向锁】

全表当前读，遍历聚簇索引加临键锁（update 未走索引的情况）
全索引当前读，遍历当前索引加临键锁

二级索引加锁后，还要在查询到的记录的主键索引项加记录锁

Insert 语句，加行级锁

记录被间隙锁，阻塞
- 插入意向锁
唯一键冲突，执行失败
- 如果主键索引重复，插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加 S 型记录锁。
- 如果唯一二级索引重复，插入新记录的事务都会给已存在的二级索引列值重复的二级索引记录添加 S 型 next-key 锁。

临键锁退化成记录锁或间隙锁的情况

唯一索引查询

等值查询
- （条件值）存在，该索引加记录锁
- （条件值）不存在，后一索引加间隙锁
范围查询
- 条件值在表中，非等值查询
  - 小于，该索引加间隙锁
  - 大于，后一索引加临键锁
- 条件值在表中且条件是等值查询
  - 小于等于，该索引加临键锁
  - 大于等于，该索引加记录锁

非唯一索引查询

等值查询
- 存在，扫描到的索引记录加的是 next-key 锁，而对于第一个不符合条件的索引记录，该索引的 next-key 锁会退化成间隙锁；
- 不存在，扫描到第一条不符合条件的索引记录，该索引的 next-key 锁会退化成间隙锁；
范围查询
- 为确保条件值前后都锁住，加锁时不存在临建锁退化为间隙锁和记录锁的情况

死锁

两个事务分别加了间隙锁（前提）
两个事务分别向对方的间隙锁插入数据，于是都产生插入意向锁
由于插入意向锁和间隙锁冲突，双方都为了获取插入意向锁，会等待对方事务的间隙锁释放，陷入循环等待，造成死锁

插入意向锁是对不同事务才产生作用的，对于本身事务的间隙锁是兼容的。

解决方法

设置事务等待锁的超时时间 innodb_lock_wait_timeout
开启主动死锁检测 innodb_deadlock_detect

日志

目前，很多数据库采用的是 steal（没提交的事务写的脏页也可以马上落盘）+ no force（已提交的事务可以不马上落盘）模式。MySQL 也参考了这个模式，undo log 变更持久性和数据一样，由 redo log 保证，对 undo log 还没落盘的修改也会写入 redo log。这样不管怎么样基于 undo log 做事务回滚或者做 MVCC都可以保证原子和一致性。

undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC；
redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
bin log （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

回滚日志与重做日志的区别

redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；防止已提交事务的修改丢失；
undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；防止未提交事务的修改无法回滚；

重做日志和归档日志的区别

bin log 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

回滚日志 Undo log

实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。
实现 MVCC（多版本并发控制）关键因素之一。MVCC 是通过 Read View + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

持久化方法：开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，对 undo 页的修改也都会记录到 redo log。redo log 会每秒刷盘，提交事务时也会刷盘，数据页和 undo 页都是靠这个机制保证持久化的。

重做日志 Redo log

防止 Buffer Pool 中的脏页丢失

实现事务的持久性，让 MySQL 有 crash-safe 的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失；
将脏页的写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能；

WAL（Write-Ahead Logging）：MySQL 的写操作并不是立刻写到磁盘上，而是先写 redo log 日志，然后在合适的时间再将脏页写到磁盘上。

redo log 是物理日志，记录了某个数据页做了什么修改，即所有未还没持久化的脏页修改。在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。

redo log 持久化操作：每当产生一条 redo log 时，会先写入到 redo log buffer，为了在 MySQL 处理大事务时尽可能少写入磁盘，进而提升写 IO 性能。redo log buffer 写入磁盘的时机：

MySQL 正常关闭时；
当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时；
InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘；
- 参数为 0 时，事务提交不会主动触发写入磁盘的操作；MySQL 进程崩溃会导致上一秒钟所有事务数据的丢失；
- 参数为 1 时，每次事务提交都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘；
- 参数为 2 时，每次事务提交都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件（写入到 redo log 文件并不意味着写入到了磁盘，而是存在操作系统里的文件系统中的 Page Cache）；操作系统崩溃或者系统断电会导致上一秒钟所有事务数据的丢失；

重做日志文件组

redo log 是为了防止 Buffer Pool 中的脏页丢失而设计的，只记录未被刷入磁盘的数据的物理日志。随着系统运行，Buffer Pool 的脏页刷新到了磁盘中，redo log 对应的记录也就没用了，这时候我们需要擦除这些旧记录，以腾出空间记录新的更新操作。重做日志文件组由有 2 个 redo log 文件组成，以循环写的方式工作，从头开始写，写到末尾就又回到开头，相当于一个环形。

write pos ～ checkpoint 之间的部分（图中红色部分）：记录新的更新操作；
check point ～ write pos 之间的部分（图中蓝色部分）：待落盘的脏数据页记录；

将 Buffer Pool 中的脏页刷新到磁盘中，然后标记 redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，checkpoint 就会往后移动（图中顺时针）。一次 checkpoint 的过程就是脏页刷新到磁盘中变成干净页，然后标记 redo log 哪些记录可以被覆盖的过程。

归档日志 Bin log

记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。MySQL 在完成数据库表结构变更或表数据修改（增删改）时，Server 层会生成一条 binlog，等之后事务提交的时候，会将该事物执行过程中产生的所有 binlog 统一写入 binlog 文件。

binlog 日志只能用于数据备份和主从复制，没有 crash-safe 的能力；
binlog 是全量日志，保存了所有数据变更的情况，可以恢复整个数据库所有数据；

主从复制

写入 Binlog：数据修改 -> 主库写 binlog 日志 -> commit 事务 -> 持久化本地数据 -> 提交事务成功；
同步 Binlog：从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。
回放 Binlog：读 relay log 中继日志，回放 binlog，并更新从库中的数据。

主从复制模型

同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果；
异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果；
半同步复制：只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端；

binlog 刷盘

图中的 write，把日志写入到 binlog 文件（文件系统的 page cache 里），但是并没有把数据持久化到磁盘；
图中的 fsync，把数据持久化到磁盘的操作，这里就会涉及磁盘 I/O，所以频繁的 fsync 会导致磁盘的 I/O 升高；

MySQL 提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上的频率：

sync_binlog = 0 的时候，表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘；
sync_binlog = 1 的时候，表示每次提交事务都会 write，然后马上执行 fsync；
sync_binlog =N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

两阶段提交

binlog 主从复制和 redo log 崩溃恢复需要保持一致，不然会出现主从数据不一致、备份数据与真实数据不一致等问题。MySQL 采用两阶段提交来避免两份日志之间的逻辑不一致，两阶段提交是一种分布式事务一致性协议。

两阶段提交：把单个事务的提交拆分成了 2 个阶段，分别是准备阶段和提交阶段（不是 commit 语句），每个阶段都由协调者和参与者共同完成。

内部 XA 事务

为了保证这两个日志的一致性，MySQL 使用了内部 XA 事务。内部 XA 事务由 binlog 作为协调者，存储引擎是参与者，可以实现跨数据库事务。将 redo log 的写入拆成了两个状态：prepare 和 commit，中间再穿插写入binlog。

如果 binlog 中没有当前内部 XA 事务的 XID，说明 redolog 完成刷盘，但是 binlog 还没有刷盘，则回滚事务。对应时刻 A 崩溃恢复的情况；
如果 binlog 中有当前内部 XA 事务的 XID，说明 redolog 和 binlog 都已经完成了刷盘，则提交事务。对应时刻 B 崩溃恢复的情况；

对于处于 prepare 阶段的 redo log，即可以提交事务，也可以回滚事务，这取决于是否能在 binlog 中查找到与 redo log 相同的 XID，以 binlog 写成功为事务提交成功的标识。

组提交

采用组提交策略，减少 redolog 和 binlog 两次刷盘导致的 I/O 次数高和锁竞争激烈的问题。

多事务场景下，两阶段提交会导致锁竞争激烈，因为为保证两个日志保存的数据库状态一致，需要加锁操作保证两阶段提交的原子性。

binlog 组提交机制：当有多个事务提交的时候，会将多个 binlog 刷盘操作合并成一个，从而减少磁盘 I/O 的次数。prepare 阶段不变，只针对 commit 阶段，将 commit 阶段拆分为三个阶段：

flush 阶段：多个事务按进入的顺序将 binlog 从 cache 写入文件（不刷盘）；5.7 之后在该阶段增加了 redo log 的组提交，即多个事务 redo log 统一刷盘；
- 类似于 XA 事务的 Prepare 阶段，如果在这一步完成后数据库崩溃，由于 binlog 中没有该组事务的记录，所以 MySQL 会在重启后回滚该组事务；
sync 阶段：对 binlog 文件做 fsync 操作（多个事务的 binlog 合并一次刷盘）；
- 类似于 XA 事务的 Commit 阶段，如果在这一步完成后数据库崩溃，由于 binlog 中已经有了事务记录，MySQL会在重启后通过 redo log 刷盘的数据继续进行事务的提交。
commit 阶段：各个事务按顺序做 InnoDB commit 操作；

每个阶段都有一个队列，锁就只针对每个队列进行保护，不再锁住提交事务的整个过程。减小了锁粒度，这样就使得多个阶段可以并发执行，从而提升效率。

总结模拟一次更新流程

MySQL 执行器检查记录是否在 buffer pool 中
- 若不在，将数据页从磁盘读入到 buffer pool；
- 若更新前与更新后数据不同，调用存储引擎执行更新操作；
开启事务，记录 undo log，同时将 Undo 页的修改记录存在 redo log 中；
存储引擎更新数据，并标记数据页为脏页，同时将数据页的修改记录存在 redo log 中；
- 为了减少磁盘I/O，脏页选择合适时间持久化到硬盘，持久化后删除对应的 redo log；
记录更新完毕，记录 binlog 至 binlog cache；
事务提交，将 redolog binlog 持久化到硬盘（两阶段提交；组提交）
- prepare 阶段
- commit 阶段

脏页刷盘的时机

Redo log 满了；
BufferPool满了；
MySQL空闲；
后台线程定时 checkpoint；
事务提交时（一般不选）；

内存

Buffer Pool 缓存的内容包括：索引页、数据页、Undo 页、插入缓存页、自适应哈希索引、锁信息等。innodb_buffer_pool_size 默认 128MB。

为了管理这些在 Buffer Pool 中的缓存页，InnoDB 为每一个缓存页都创建了一个控制块，控制块信息包括：缓存页的表空间、页号、缓存页地址、链表节点等等。用三种链表管理不同状态的缓存块：

Free List （空闲页链表），管理空闲页；
Flush List （脏页链表），管理脏页；
LRU List，管理脏页+干净页，将经常查询或可能将要查询的数据缓存在其中，而不常查询的数据就淘汰出去；

Free 链表：使用链表结构，快速找到空闲的缓存页；

每当需要从磁盘中加载一个页到 Buffer Pool 中时，就从 Free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的控制块从 Free 链表中移除

Flush 链表：使用链表结构，快速找到脏页；（结构类似于 Free 链表）

脏页落盘时，后台线程遍历 Flush 链表

提高缓存命中率

Buffer Pool 有三种页和链表用来管理数据

Free Page（空闲页），表示此页未被使用，位于 Free 链表；
Clean Page（干净页），表示此页已被使用，但是页面未发生修改，位于LRU 链表。
Dirty Page（脏页），表示此页「已被使用」且「已经被修改」，其数据和磁盘上的数据已经不一致。当脏页上的数据写入磁盘后，内存数据和磁盘数据一致，那么该页就变成了干净页。脏页同时存在于 LRU 链表和 Flush 链表。

普通的LRU 算法会出现以下两个问题

预读失效：加载访问数据页时，提前加载进来的相邻数据页，即预读页，但后续并没有访问预读页；
Buffer Pool 污染：某一个 SQL 语句扫描了大量的数据时，将 Buffer Pool 里的所有页都替换出去，导致大量热数据被淘汰了，后续热数据被访问时会产生大量磁盘 I/O；
- 索引失效批量扫描的时候会出现污染

解决办法

将 LRU 划分了 2 个区域：old 区域和 young 区域。划分这两个区域后，预读的页就只需要加入到 old 区域的头部，当页被真正访问的时候，才将页插入 young 区域的头部。
只有同时满足被访问与在 old 区域停留时间超过 innodb_old_blocks_time 秒两个条件，才会被插入到 young 区域头部
young 区域优化：为了防止 young 区域节点频繁移动到头部。young 区域前面 1/4 被访问不会移动到链表头部，只有后面的 3/4被访问了才会

优化

磁盘IO、索引、数据分页（查询大量数据）、锁竞争、内存使用下·