MySQL常见问题|Klassterの工坊

字段类型

CHAR 和 VARCHAR 的区别是什么？

CHAR 和 VARCHAR 是最常用到的字符串类型，两者的主要区别在于：CHAR 是定长字符串，VARCHAR 是变长字符串。

CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；VARCHAR 在存储时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理。

CHAR 更适合存储长度较短或者长度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码。VARCHAR 类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等。

CHAR(M) 和 VARCHAR(M) 的 M 都代表能够保存的字符数的最大值，无论是字母、数字还是中文，每个都只占用一个字符。

VARCHAR(100)和 VARCHAR(10)的区别是什么？

VARCHAR(100)和 VARCHAR(10)都是变长类型，表示能存储最多 100 个字符和 10 个字符。因此，VARCHAR (100) 可以满足更大范围的字符存储需求，有更好的业务拓展性。而 VARCHAR(10)存储超过 10 个字符时，就需要修改表结构才可以。

虽说 VARCHAR(100)和 VARCHAR(10)能存储的字符范围不同，但二者存储相同的字符串，所占用磁盘的存储空间其实是一样的，这也是很多人容易误解的一点。

不过，VARCHAR(100) 会消耗更多的内存。这是因为 VARCHAR 类型在内存中操作时，通常会分配固定大小的内存块来保存值，即使用字符类型中定义的长度。例如在进行排序的时候，VARCHAR(100)是按照 100 这个长度来进行的，也就会消耗更多内存。

MySQL 架构

从上图可以看出， MySQL 主要由下面几部分构成：

连接器： 身份认证和权限相关(登录 MySQL 的时候)。
查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器： 按照 MySQL 认为最优的方案去执行。
执行器： 执行语句，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。InnoDB 是 MySQL 的默认存储引擎，绝大部分场景使用 InnoDB 就是最好的选择。

存储引擎

MyISAM 和 InnoDB 的区别

InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度。
InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别。MyISAM 不提供事务支持。
InnoDB 支持外键，而 MyISAM 不支持。
InnoDB 支持 MVCC，而 MyISAM 不支持。
InnoDB 引擎和 MyISAM 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。
InnoDB 支持数据库异常崩溃后的安全恢复，而 MyISAM 不支持。
InnoDB 的性能更强大。

日志

MySQL 日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中，比较重要的还要属二进制日志 bin log（归档日志）和事务日志 redo log（重做日志）和 undo log（回滚日志）。

1、错误日志（Error Log）：记录 MySQL 服务器启动、运行或停止时出现的问题。

2、慢查询日志（Slow Query Log）：记录执行时间超过 long_query_time 值的所有 SQL 语句。这个时间值是可配置的，默认情况下，慢查询日志功能是关闭的。

3、一般查询日志（General Query Log）：记录 MySQL 服务器的启动关闭信息，客户端的连接信息，以及更新、查询的 SQL 语句等。

4、二进制日志（Binary Log）：记录所有修改数据库状态的 SQL 语句，以及每个语句的执行时间，如 INSERT、UPDATE、DELETE 等，但不包括 SELECT 和 SHOW 这类的操作。

5、重做日志（Redo Log）：记录对于 InnoDB 表的每个写操作，不是 SQL 级别的，而是物理级别的，主要用于崩溃恢复。

6、回滚日志（Undo Log，或者叫事务日志）：记录数据被修改前的值，用于事务的回滚。

bin log

binlog 是一种二进制日志，会在磁盘上记录数据库的所有修改操作。

如果误删了数据，就可以使用 binlog 进行回退到误删之前的状态。如果要搭建主从复制，就可以让从库定时读取主库的 binlog。

binlog 属于 Server 层，关注的是逻辑变更的全局记录，与存储引擎无关，无法直接操作物理数据页。

redo log

redo log（重做日志）是 InnoDB 存储引擎独有的，它让 MySQL 拥有了崩溃恢复能力。

比如 MySQL 实例挂了或宕机了，重启时，InnoDB 存储引擎会使用 redo log 恢复数据，保证数据的持久性与完整性。

工作机制：

当事务启动时，MySQL 会为该事务分配一个唯一标识符。

在事务执行过程中，每次对数据进行修改，MySQL 都会生成一条 Redo Log，记录修改前后的数据状态。

这些 Redo Log 首先会被写入内存中的 Redo Log Buffer。当事务提交时，MySQL 再将 Redo Log Buffer 中的记录刷新到磁盘上的 Redo Log 文件中。只有当 Redo Log 成功写入磁盘，事务才算真正提交成功。刷屏时机：Redo Log Buffer 的空间不足时，事务提交时，触发 Checkpoint 时，后台线程定期刷盘时。

bin log 与 redo log 的区别

binlog 记录的是逻辑日志，包括原始的 SQL 语句或者行数据变化，例如“将 id=2 这行数据的 age 字段+1”。

redo log 记录物理日志，即数据页的具体修改，例如“将 page_id=123 上 offset=0x40 的数据从 18 修改为 26”。

binlog 是追加写入的，文件写满后会新建文件继续写入，不会覆盖历史日志，保存的是全量操作记录；redo log 是循环写入的，空间是固定的，写满后会覆盖旧的日志，仅保存未刷盘的脏页日志，已持久化的数据会被清除。

另外，为保证两种日志的一致性，innodb 采用了两阶段提交策略，redo log 在事务执行过程中持续写入，并在事务提交前进入 prepare 状态；binlog 在事务提交的最后阶段写入，之后 redo log 会被标记为 commit 状态。

undo log

每一个事务对数据的修改都会被记录到 undo log ，当执行事务过程中出现错误或者需要执行回滚操作的话，MySQL 可以利用 undo log 将数据恢复到事务开始之前的状态。

undo log 属于逻辑日志，会记录反向的 SQL 语句，比如说事务执行一条 DELETE 语句，那 undo log 就会记录一条相对应的 INSERT 语句。

同时，undo log 的信息也会被记录到 redo log 中，因为 undo log 也要实现持久性保护。

SQL优化

慢SQL

首先，需要找到那些比较慢的 SQL，可以通过启用慢查询日志，记录那些超过指定执行时间的 SQL 查询。

然后，使用 EXPLAIN 查看慢 SQL 的执行计划，看看有没有用索引，大部分情况下，慢 SQL 的原因都是因为没有用到索引。

最后，根据分析结果，通过添加索引、优化查询条件、减少返回字段等方式进行优化。

语句优化

SQL 优化的方法非常多，但本质上就一句话：尽可能少地扫描、尽快地返回结果。

常见做法：

避免不必要的列，不要select *
分页优化：延迟关联（先子查询LIMIT筛选需要的数据再关联）、添加书签（如：id > last_idx，直接从上次最后id开始查询，避免OFFSET）
索引优化：覆盖索引、适当使用前缀索引、避免在使用函数运算、遵循最左前缀原则
JOIN优化：优化代替子查询、小表驱动大表、适当增加冗余字段、避免join太多表
排序优化：利用索引扫描做排序
UNION优化：条件下推、UNION ALL 代替 UNION

EXPLAIN

可以使用 EXPLAIN 命令来分析 SQL 的 执行计划 ，这样就知道语句是否命中索引了。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。

EXPLAIN 并不会真的去执行相关的语句，而是通过 查询优化器 对语句进行分析，找出最优的查询方案，并显示对应的信息。

列名	含义
id	SELECT 查询的序列标识符
select_type	SELECT 关键字对应的查询类型
table	用到的表名
partitions	匹配的分区，对于未分区的表，值为 NULL
type	表的访问方法
possible_keys	可能用到的索引
key	实际用到的索引
key_len	所选索引的长度
ref	当使用索引等值查询时，与索引作比较的列或常量
rows	预计要读取的行数
filtered	按表条件过滤后，留存的记录数的百分比
Extra	附加信息

type

查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

常见的几种类型具体含义如下：

system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。
const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。
ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。
range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。
index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
ALL：全表扫描。性能最差，需要优化。

extra

这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：

Using filesort：无法利用索引完成排序，需要额外内存或磁盘排序，常见于 ORDER BY 未使用索引，或字段顺序不一致。
Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于子查询、 ORDER BY 和 GROUP BY。
Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
Using join buffer (Block Nested Loop)：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。
- 当 Extra 列包含 Using filesort 或 Using temporary 时，MySQL 的性能可能会存在问题，需要尽可能避免。
  如何避免filesort：
  1. ORDER BY 顺序与索引一致
  2. 避免混用ASC和DESC
  3. ORDER BY + LIMIT 配索引
  如何避免temporary：
  1. GROUP BY 顺序与索引一致
  2. 减少子查询
  3. 使用覆盖索引，避免回表

锁

锁分类

MySQL 中有多种类型的锁，可以从不同维度来分类，按锁粒度划分的话，有表锁、行锁。

按照加锁机制划分的话，有乐观锁和悲观锁。按照兼容性划分的话，有共享锁和排他锁。

表锁：锁定整个表，资源开销小，加锁快，但并发度低，不会出现死锁；适合查询为主、少量更新的场景（如 MyISAM 引擎）。
- 表共享读锁（S锁）：允许多个事务同时读，但阻塞写操作；
- 表独占写锁（X锁）：独占表，阻塞其他事务的读写。
行锁：锁定单行或多行，开销大、加锁慢，可能出现死锁，但并发度高（InnoDB 默认支持）。底层是通过给索引加锁实现的，这就意味着只有通过索引条件检索数据时，InnoDB 才能使用行级锁；否则InnoDB 会进行全表扫描，在扫描过程中，它会给表里的每一行记录都加上锁，会退化为表锁。
- 记录锁（Record Lock）：锁定索引中的具体记录；
- 间隙锁（Gap Lock）：锁定索引记录之间的间隙，防止幻读；
- 临键锁（Next-Key Lock）：结合记录锁和间隙锁，锁定一个左开右闭的区间（如 (5, 10]）。

共享锁（S锁/读锁），允许多个事务同时读取数据，但阻塞写操作。语法：SELECT ... LOCK IN SHARE MODE
排他锁（X锁/写锁），独占数据，阻塞其他事务的读写。语法：SELECT ... FOR UPDATE。

索引

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

索引的优点：

加快查询速度 (主要目的)：通过索引，数据库可以大幅减少需要扫描的数据量，直接定位到符合条件的记录，从而显著加快数据检索速度，减少磁盘 I/O 次数。
保证数据唯一性：通过创建唯一索引 (Unique Index)，可以确保表中的某一列（或几列组合）的值是独一无二的，比如用户 ID、邮箱等。主键本身就是一种唯一索引。
加速排序和分组：如果查询中的 ORDER BY 或 GROUP BY 子句涉及的列建有索引，数据库往往可以直接利用索引已经排好序的特性，避免额外的排序操作，从而提升性能。

索引的缺点：

创建和维护耗时：创建索引本身需要时间，特别是对大表操作时。更重要的是，当对表中的数据进行增、删、改 (DML 操作) 时，不仅要操作数据本身，相关的索引也必须动态更新和维护，这会降低这些 DML 操作的执行效率。
占用存储空间：索引本质上也是一种数据结构，需要以物理文件（或内存结构）的形式存储，因此会额外占用一定的磁盘空间。索引越多、越大，占用的空间也就越多。
可能被误用或失效：如果索引设计不当，或者查询语句写得不好，数据库优化器可能不会选择使用索引（或者选错索引），反而导致性能下降。

大多数情况下，合理使用索引确实比全表扫描快得多。但也有例外：

数据量太小：如果表里的数据非常少（比如就几百条），全表扫描可能比通过索引查找更快，因为走索引本身也有开销。
查询结果集占比过大：如果要查询的数据占了整张表的大部分（比如超过 20%-30%），优化器可能会认为全表扫描更划算，因为通过索引多次回表（随机 I/O）的成本可能高于一次顺序的全表扫描。

索引为什么快

索引之所以快，核心原因是它大大减少了磁盘 I/O 的次数。

它的本质是一种排好序的数据结构，就像书的目录，让我们不用一页一页地翻（全表扫描）。

在 MySQL 中，这个数据结构是B+树。B+树结构主要从两方面做了优化：

B+树的特点是“矮胖”，一个千万数据的表，索引树的高度可能只有 3-4 层。这意味着，最多只需要3-4 次磁盘 I/O，就能精确定位到我想要的数据，而全表扫描可能需要成千上万次，所以速度极快。
B+树的叶子节点是用链表连起来的。找到开头后，就能顺着链表顺序读下去，这对磁盘非常友好，还能触发预读。

为什么不使用哈希作为索引的数据结构

哈希索引的底层是哈希表。它的优点是，在进行精确的等值查询时，理论上时间复杂度是 O(1) ，速度极快。比如 WHERE id = 123。

但是，它有几个对于通用数据库来说是致命的缺点：

不支持范围查询: 这是最主要的原因。哈希函数的一个特点是它会把相邻的输入值（比如 id=100 和 id=101）映射到哈希表中完全不相邻的位置。这种顺序的破坏，使得我们无法处理像 WHERE age > 30 或 BETWEEN 100 AND 200这样的范围查询。要完成这种查询，哈希索引只能退化为全表扫描。
不支持排序: 同理，因为哈希值是无序的，所以我们无法利用哈希索引来优化 ORDER BY 子句。
不支持部分索引键查询: 对于联合索引，比如(col1, col2)，哈希索引必须使用所有索引列进行查询，它无法单独利用 col1 来加速查询。
哈希冲突问题: 当不同的键产生相同的哈希值时，需要额外的链表或开放寻址来解决，这会降低性能。

鉴于数据库查询中范围查询和排序是极其常见的操作，一个不支持这些功能的索引结构，显然不能作为默认的、通用的索引类型。

为什么不使用B树作为索引的数据结构

B 树和 B+树都是优秀的多路平衡搜索树，非常适合磁盘存储，因为它们都很“矮胖”，能最大化地利用每一次磁盘 I/O。

但 B+树是 B 树的一个增强版，它针对数据库场景做了几个关键优化：

I/O 效率更高：在 B+树中，只有叶子节点才存储数据（或数据指针），而非叶子节点只存储索引键。因为非叶子节点不存数据，所以它们可以容纳更多的索引键。这意味着 B+树的“扇出”更大，在同样的数据量下，B+树通常会比 B 树更矮，也就意味着查找数据所需的磁盘 I/O 次数更少。
查询性能更稳定：在 B+树中，任何一次查询都必须从根节点走到叶子节点才能找到数据，所以查询路径的长度是固定的。而在 B 树中，如果运气好，可能在非叶子节点就找到了数据，但运气不好也得走到叶子，这导致查询性能不稳定。
对范围查询极其友好：这是 B+树最核心的优势。它的所有叶子节点之间通过一个双向链表连接。当我们执行一个范围查询（比如 WHERE id > 100）时，只需要通过树形结构找到 id=100 的叶子节点，然后就可以沿着链表向后顺序扫描，而无需再回溯到上层节点。这使得范围查询的效率大大提高。

B 树也称 B- 树，全称为 多路平衡查找树，B+ 树是 B 树的一种变体。B 树和 B+ 树中的 B 是 Balanced（平衡）的意思。

目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B 树& B+ 树两者有何异同呢？

B 树的所有节点既存放键(key)也存放数据(data)，而 B+ 树只有叶子节点存放 key 和 data，其他内节点只存放 key。
B 树的叶子节点都是独立的；B+ 树的叶子节点有一条引用链指向与它相邻的叶子节点。
B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+ 树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+ 树的范围查询，只需要对链表进行遍历即可。

综上，B+ 树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

适合创建索引的字段

不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。
被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

索引失效的情况

创建了组合索引，但查询条件未遵守最左匹配原则
在索引列上进行计算、函数、类型转换等操作
以 % 开头的 LIKE 查询比如 LIKE '%abc';
查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到
IN 的取值范围较大时会导致索引失效，走全表扫描(NOT IN 和 IN 的失效场景相同);
发生隐式转换

索引下推

索引下推（Index Condition Pushdown，简称 ICP） 是 MySQL 5.6 版本中提供的一项索引优化功能，它允许存储引擎在索引遍历过程中，执行部分 WHERE 字句的判断条件，直接过滤掉不满足条件的记录，从而减少回表次数和存储引擎与MySQL服务层的数据传输量，提高查询效率。

MySQL 可以简单分为 Server 层和存储引擎层这两层。Server 层处理查询解析、分析、优化、缓存以及与客户端的交互等操作，而存储引擎层负责数据的存储和读取。

索引下推的下推其实就是指将部分上层（Server 层）负责的事情，交给了下层（存储引擎层）去处理。

使用索引的建议

频繁更新的字段应慎重建立索引
限制索引数量，每张表最好不要超过5个
考虑建立联合索引，避免单列索引
避免冗余的索引，如(a,b)和(a)，第一个可以覆盖第二个，就不要建立第二个

适合建立索引的字段：

不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0、1、true、false 这样语义较为清晰的短值或短字符作为替代。
被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。
被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
被经常频繁用于连接的字段：对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

事务

何为事务？一言蔽之，事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账 1000 元，这个转账会涉及到两个关键操作，这两个操作必须都成功或者都失败。

将小明的余额减少 1000 元
将小红的余额增加 1000 元。

事务会把这两个操作就可以看成逻辑上的一个整体，这个整体包含的操作要么都成功，要么都要失败。这样就不会出现小明余额减少而小红的余额却并没有增加的情况。

数据库事务特性ACID

原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。A、I、D 是手段，C 是目的。

原子性

事务的原子性体现在一系列的操作，要么全部都执行，要都不执行。想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，而在 MySQL 中，恢复机制是通过回滚日志（undo log）实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后在对数据库中的对应行进行写入。

回滚日志（undo log）：

为了能够在发生错误时撤销之前的全部操作，肯定是需要将之前的操作都记录下来的，这样在发生错误时才可以回滚。

回滚日志除了能够在发生错误或者用户执行 ROLLBACK 时提供回滚相关的信息，它还能够在整个系统发生崩溃、数据库进程直接被杀死后，当用户再次启动数据库进程时，还能够立刻通过查询回滚日志将之前未完成的事务进行回滚，这也就需要回滚日志必须先于数据持久化到磁盘上，是我们需要先写日志后写数据库的主要原因。

回滚日志并不能将数据库物理地恢复到执行语句或者事务之前的样子；它是逻辑日志，保存反向逻辑，当回滚日志被使用时，它只会按照日志逻辑地将数据库中的修改撤销掉看，可以理解为，我们在事务中使用的每一条 INSERT 都对应了一条 DELETE，每一条 UPDATE 也都对应一条相反的 UPDATE 语句。

持久性

事务的持久性体现在，一旦事务被提交，那么数据一定会被写入到数据库中并持久存储起来。MySQL 使用重做日志（redo log）实现事务的持久性。

重做日志（redo log）：

重做日志由两部分组成，一是内存中的重做日志缓冲区，因为重做日志缓冲区在内存中，所以它是易失的，另一个就是在磁盘上的重做日志文件，它是持久的。

当我们在一个事务中尝试对数据进行修改时，它会先将数据从磁盘读入内存，并更新内存中缓存的数据，然后生成一条重做日志并写入重做日志缓存，当事务真正提交时，MySQL 会将重做日志缓存中的内容刷新到重做日志文件，再将内存中的数据更新到磁盘上，图中的第 4、5 步就是在事务提交时执行的。

隔离性

数据库对于隔离级别的实现就是使用并发控制机制对在同一时间执行的事务进行控制，限制不同的事务对于同一资源的访问和更新。

锁（解决写-写）：

锁（Record Lock, Gap Lock, Next-Key Lock）是一种最为常见的并发控制机制，在一个事务中，我们并不会将整个数据库都加锁，而是只会锁住那些需要访问的数据项， MySQL 和常见数据库中的锁都分为两种，共享锁（Shared）和互斥锁（Exclusive），前者也叫读锁，后者叫写锁。

读锁保证了读操作可以并发执行，相互不会影响；而写锁保证了在更新数据库数据时不会有其他的事务访问或者更改同一条记录造成不可预知的问题。

多版本和快照隔离（解决读-写）：

通过维护多个版本的数据，数据库可以允许事务在数据被其他事务更新时对旧版本的数据进行读取，很多数据库都对这一机制进行了实现；因为所有的读操作不再需要等待写锁的释放，所以能够显著地提升读的性能，MySQL 和 PostgreSQL 都对这一机制进行自己的实现，也就是MVCC。

事务隔离级别

SQL 标准定义了四种事务隔离级别，用来平衡事务的隔离性（Isolation）和并发性能。级别越高，数据一致性越好，但并发性能可能越低。这四个级别是：

READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。这种级别在实际应用中很少使用，因为它对数据一致性的保证太弱。
READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。这是大多数数据库（如 Oracle, SQL Server）的默认隔离级别。
REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。MySQL InnoDB 存储引擎的默认隔离级别正是 REPEATABLE READ。并且，InnoDB 在此级别下通过 MVCC（多版本并发控制）和 Next-Key Locks（间隙锁+行锁）机制，在很大程度上解决了幻读问题。
SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别	脏读	不可重复读	幻读
RU	❌	❌	❌
RC	✅	❌	❌
RR	✅	✅	❓
Serializable	✅	✅	✅

幻读

幻读是指在同一个事务中，多次执行相同的范围查询，结果却不同。这种现象通常发生在其他事务在两次查询之间插入或删除了符合当前查询条件的数据。

避免幻读：

MySQL 在可重复读隔离级别下，通过 MVCC 和临键锁可以在一定程度上避免幻读。

比如说在查询时显式加锁，利用临键锁锁定查询范围，防止其他事务插入新的数据。

START TRANSACTION;
SELECT * FROM user_info WHERE id > 1 FOR UPDATE; -- 加临键锁
COMMIT;

其他事务在插入数据时，会被阻塞，直到当前事务提交或回滚。

如果查询语句中包含显式加锁（如 FOR UPDATE），InnoDB 会使用当前读，直接读取最新的数据，并加锁。

在范围查询时，InnoDB 不仅会对符合条件的记录加行锁，还会对相邻的索引间隙加间隙锁，从而形成临键锁。

MVCC

MVCC即多版本并发控制（Multi Version Concurrency Control），是InnoDB的一种并发控制机制，用于在多个并发事务同时读写数据库时保持数据的一致性和隔离性。它是通过在每个数据行上维护多个版本的数据来实现的。当一个事务要对数据库中的数据进行修改时，MVCC 会为该事务创建一个数据快照，而不是直接修改实际的数据行。

解决问题：高并发读写时，读操作就必须等写操作完成后再进行，效率很低。MVCC就是解决这个高并发读写事务性能问题的。

当前读和快照读

快照读：快照读是 InnoDB 通过 MVCC 实现的一种非阻塞读方式。简单的 select 语句就是快照读。

当前读：读取最新数据。需要加锁，如 select ... for update，update等都是当前读。

InnoDB实现

MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改

隐藏字段：InnoDB每行数据添加了三个隐藏字段：

DB_TRX_ID（6字节）：表示最后一次插入或更新该行的事务 id。此外，delete 操作在内部被视为更新，只不过会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除
DB_ROLL_PTR（7字节） 回滚指针，指向该行的 undo log 。如果该行未被更新，则为空
DB_ROW_ID（6字节）：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引

Read View：进行当前读时会创建一个事务快照，主要是用来做可见性判断，里面保存了 当前对本事务不可见的其他活跃事务。简单来说：大于等于max的不可见，小于min的（说明已提交）可见；存在活跃队列中的不可见，不存在的（说明已提交）可见。