MySQL 架构

客户端
服务端
- Server 层
  - 连接器
    - TCP 传输，建立连接后验证用户和密码
    - 可以通过 show processlist 命令查看当前 MySQL 的连接数
    - MySQL 定义了空闲连接的最大空闲时长 wait_timeout，默认是八小时，超过这个时间会主动断开连接
    - 最大连接数由 max_connection 参数控制，默认为 151 个，超过这个值，MySQL 会拒绝新的连接请求
    - MySQL 的连接分长连接和短连接，长连接占用内存高，长连接过多会导致服务异常重启，可以通过定期断开长连接或客户端主动重置连接来解决
  - 缓存
    - 缓存命中则直接返回，每次更新操作都会清除缓存，
    - 高并发更新的情况下，缓存反而会成为性能瓶颈，所以MySQL8.0 后已取消缓存机制
    - 如果想关闭查询缓存，可以通过将参数 query_cache_type 设置成 DEMAND
  - 解析器
    - 词法分析：识别出关键字（select from 等）
    - 语法分析：判断 SQL 语句是否有语法错误，如果没有就构建语法树，方便获取非关键字内容
  - 执行 SQL
    - 预处理器
      - 检查查询语句中的表或字段是否存在
      - 将 select * 语句中的 * 号扩展为表中的所有列
    - 优化器
      - 在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引
      - 要想知道优化器选择了哪个索引，可以在查询语句最前面加个 explain 命令，这样就会输出这条 SQL 语句的执行计划
    - 执行器
      - 主键索引查询
      - 全表扫描
      - 索引下推（类似覆盖索引，用于减少回表次数）
        
        例如对于联合索引 (age, reward)，执行 select * from user where age > 18 and reward = 1000，这会使用 age 索引（联合索引遇到范围查询就会停止匹配，所以无法利用 reward 的索引）
        
        如果不用索引下推，定位到数据后并获取主键值后会回表查询完整数据，然后 server 层判断该条记录是否满足 reward = 1000 的条件
        
        如果使用索引下推，则不必回表，因为 (age, reward) 索引的叶子节点已经包含了 reward 字段，可以直接判断是否符合条件
- 存储引擎（存储层）
  - 不同存储引擎之间的区别
  - Buffer Pool：缓存页数据，对 .ibd 文件直接 IO
    - Change Buffer：对于写操作，暂存脏页
    - Undo Log 页：对于写操作，会暂存旧数据，不定时刷盘到Undo Log
    - 自适应哈希索引：对于热点数据，将查询的目标作为键，其所在的数据页作为值，存入哈希索引缓存
  - Undo Log
  - Redo Log（顺序读写，用于加速持久化）
    - 对于写操作，会将操作记录到 Redo Log Buffer 中，事务提交时刷到磁盘的 Redo Log 中
- Bin Log
  - Bin Log 属于 MySQL 的组成部分而不是存储引擎提供的

MySQL 存储结构

文件存储结构

通过 show variables like 'datadir' 查看文件存储路径
存储路径下，每个数据库都有一个文件夹，该文件夹内存储若干文件
- db.opt：存储当前数据库的默认字符集和字符校验规则
- <table_name>.frm：存储表结构定义
- <table_name>.ibd：存储表数据，该文件也叫「表空间文件」

表空间文件的结构

自底向上的结构为：

行
- 除了 TEXT、BLOBs 这种大对象类型之外，所有列的数据 + 变长字段长度列表 + NULL值列表 占用的字节长度加起来不能超过 65535 个字节（64KB）
- 由此可以计算 varchar(n) 中 n 的最大取值（列数不同时，最大取值也不同）
- 如果行溢出了，Compact 行格式会存储一部分数据，然后将溢出的输出存储到「溢出页」，同时用 20 字节存储指向溢出页的地址，Compressed 和 Dynamic 行格式则会将所有数据都存到溢出页，只用 20 字节存储地址
页
- InnoDB 读写数据时以「页」为单位，每个页默认大小为 16KB
- 数据页的结构
区
- InnoDB 分配存储空间时以「区」为单位，每个区默认大小为 1MB（64 个页）
- 需要新空间时，直接申请一个区的空间
- 这使得每个区内的页在磁盘上的物理位置是连续的，范围查询时就可以顺序 IO 提高性能
段
- 多个区组成一个「段」，表空间由多个段组成，有多种段
  - 索引段：存放 B+树非叶子节点的区的集合
  - 数据段：存放 B+树叶子节点的区的集合
  - 回滚段：存放回滚数据的区的集合，MVCC 利用回滚段实现多版本查询数据

InnoDB 的 Compact 行格式

额外信息
- 变长字段长度列表：存储这一行中每个变长字段的长度，用于读取对应长度的数据
- NULL 值列表
  - 每一位对应每一列的数据是否为 NULL，如果为 1 则对应这一列的数据为 NULL，反之则不为 NULL
  - 长度为 n 个字节，如果每一列都为 NOT NULL 则不存在 NULL 值列表
- 记录头信息（几个重要的字段）
  - delete_mask：标识该行数据是否被删除
  - next_record：下一条数据的位置
  - record_type：0 表示普通记录，1 表示 B+树的非叶子节点记录，2 表示最小记录，3 表示最大记录
真实数据
- 隐藏字段
  - row_id：只当没有主键和唯一约束列的时候存在该字段，占用 6 个字节
  - trx_id：标记生成这行数据的事务 id，必需，占用 6 个字节
  - roll_ptr：记录上一个版本的指针，必需，占用 6 个字节
- 每一列的数据

关于「变长字段长度列表」和「NULL值列表」的存储

变长字段长度列表和 NULL 值列表是逆序存放的（从右往左读才是对应列的顺序）
因为记录头信息中的 next_record 指向的是下一条数据的「记录头信息」和其「真实数据」之间的位置
这样往左读就是额外信息，往右读就是真实数据
同时位置靠前的真实数据和其长度信息可以在同一个 CPU Cache Line 中，可以提高 CPU Cache 的命中率

事务隔离级别

事务并发执行遇到的错误严重程度从高到底：

脏读：读取到其他事务未提交的数据
不可重复读：单次事务内多次读取同一个数据得到的值不一样
幻读：单次事务内多次条件查询得到的「记录数量」不一样解决并发问题的四种隔离级别：
读未提交：未解决任何问题
读已提交：解决了脏读的问题
可重复读：解决脏读和不可重复读的问题，InnoDB 的默认隔离级别，MySQL 在「可重复读」的隔离级别下，可以很大程度上避免幻读的发生
串行化：串行执行事务，不存在并发问题，串行化会影响性能，一般不使用

MVCC 工作机制

MVCC 的工作依靠两个数据：事务开启时创建的 ReadView、索引记录中隐藏列的 record_trx_id 和 roll_pointer

ReadView

creator_trx_id：创建 ReadView 的事务 id
m_ids：活跃事务（启动但未提交的事务）的 id 列表
min_trx_id：当前 ReadView 创建时活跃事务的最小 id
max_trx_id：下一个事务的 id 值（全局事务最大 id+1）

聚簇索引记录中的隐藏列

trx_id：每次事务对该记录进行修改时，都会将当前事务 id 写入此列
roll_pointer：每次修改该记录时，会在 Undo Log 中保留旧版本，并将旧版本的指针写入此列

事务访问数据的流程

对于 ReadView 和一个事务的 trx_id，需要确定的前提：

trx_id < min_trx_id 这个事务一定是已提交事务
min_trx_id <= trx_id < max_trx_id 这个事务有不一定是已提交事务，如果在 m_ids 中则是活跃事务，反之则是已提交事务
trx_id >= max_trx_id 这个事务是 ReadView 创建后才启动的

当一个事务创建 ReadView 并访问记录的 record_trx_id 时，经历以下流程：

record_trx_id < min_trx_id || (min_trx_id <= record_trx_id < max_trx_id && record_trx_id not in m_ids) 说明修改这条记录的事务已提交，该记录对当前事务可见，读取数据并结束访问
min_trx_id <= record_trx_id < max_trx_id && record_trx_id is in m_ids 说明修改这条记录的事务未提交，该记录对当前事务不可见，查询该记录的 roll_pointer 指向的旧版本，并回到步骤 1
record_trx_id >= max_trx_id 说明修改这条记录的事务是在 ReadView 创建后才启动的，该记录对当前事务不可见，查询该记录的 roll_pointer 指向的旧版本，并回到步骤 1
注意
如果顺着 roll_pointer 一直找到尽头，所有历史版本都不可见，则应该返回空，仿佛这条记录不存在一样

不同隔离级别中 ReadView

在「读提交」的隔离级别下，事务开启后每次读取数据都会创建一个 ReadView 创建 ReadView 的间隙如果有其他事务开启并结束，就会发生「不可重复读」
在「可重复度」的隔离级别下，事务开启到结束都使用同一个 ReadView 对于快照读，事务期间其他事务插入的数据是不可见的，所以不会发生幻读对于当前读，会在读取的数据行插入临键锁（间隙锁+记录锁），避免大部分的幻读

临键锁

「临键锁」的作用等效于「间隙锁 + 记录锁」，作用的区间是左开右闭的，例如多列的数据为 $[1, 2, 5, 10]$ ，想要将区间 $(2, 10]$ 锁住，就会在 $(2, 5]$ 和 $(5, 10]$ 上分别加临键锁 临键锁并不代表两个单独的锁，而是一个完整的锁单元

「当前读」会发生幻读的场景

事务 A 开启，然后执行 select * from stu where id = 5 得到结果为空
事务 B 开启，然后执行 insert into stu values(5, "v1hz") 并提交
事务 A 执行 update stu set name = "wxy" where id = 5（这种情况确实很少见）
事务 A 执行 select * from stu where id = 5 查到 id 为 5 的记录（发生幻读）

为什么？因为 update 是「当前读」，会读到事务 B 插入的记录，所以能够更新成功，此时 id 为 5 的记录的 record_trx_id 就是事务 A 的 trx_id 导致第二次查询时记录对事务 A 是可见的，于是第二次查询能查到第一次查不到的数据

第二种情况：

事务 A 开启，然后执行 select * from stu where id = 5 得到结果为空
事务 B 开启，然后执行 insert into stu values(5, "v1hz") 并提交
事务 A 执行 select * from stu where id = 5 for update 查到 id 为 5 的记录（发生幻读）

这种情况则是因为新的查询是当前读，肯定能读到新插入的数据避免的方法就是尽量在开启事务后立即执行 select ... for update 语句，这样就会立即加上临键锁，防止增删操作造成幻读

索引

按物理存储分类

聚簇索引（主键索引）
- 树的叶子节点存储完整的行数据（索引和数据长在同一颗树上）
- 一张表只能有一个聚簇索引（主键索引），如果没有主键，会生成一个 row_id 作为聚簇索引
二级索引（非聚簇索引）
- 数的叶子节点存储索引列的值 + 主键 ID
- 要查询完整数据需要根据主键 ID 进行一次回表（再次查询）
- 如果要查询的数据就是该索引列的值，则会发生覆盖索引，无需回表 ^4d4fe4
  - 例如对于普通索引 name 执行 select id from person where name = "alice"，要查询的内容在二级索引就有，无需走主键索引（扫全表）

按逻辑功能分类

主键索引
唯一索引（不是主键就是二级索引）
普通索引（二级索引）
联合索引
- 多个列共同组成一个索引，例如 INDEX(a, b, c)
- 遵循 最左前缀原则
  - 查询时必须从索引的最左边列开始匹配，不能跳过
  - 例：对于索引 (name, age)
    - 能用的情况：where name = "alice" where name = "alice" and age = 18
    - 不能用的情况：where age = 18
全文索引

索引失效

使用左或左右模糊匹配：索引是根据前缀进行排序的，只要有左模糊匹配就只能全表扫描
对索引使用函数或表达式：索引本身是有序的，但是函数/表达式计算后的值就不一定有序了
索引的隐式类型转换：当字符串和数字进行比较时，MySQL 会自动将字符串转为数字，所以如果是 id 为整型时执行 where id = "1"，相当于 where id = CAST("1" as signed int)，会正确使用索引，而当 id 为字符串时执行 where id = 1，相当于 where CAST(id as signed int) = 1，对索引使用了函数，索引就会失效
联合索引的非最左匹配：以联合索引 (a, b, c) 为例，只有 a 是全局有序的，b 要在 a 相等时才有序，c 要在 b 相等时才有序，b 和 c 都是局部有序，所以无法单独对 b c 利用索引搜索
WHERE 中的 OR：如果 OR 前后有一个不是索引列，就会失效

`count()` 中的索引行为

count(主键) count(*) count(1) 有什么区别？哪个性能更好？

count(主键)：读取记录中的主键的值，如果不为 NULL 则 count+1 如果有二级索引则会使用二级索引（因为只需要主键），如果没有才会走主键索引
count(*) 和 count(1)： count(*) 就是 count(0)，所以和 count(1) 没有区别，二者都不会读取具体的字段值，1 和 0 都不为 NULL，所以每条记录都会 count+1 由于少一个读取字段值的步骤，所以性能比 count(主键) 如果有二级索引则会使用二级索引（因为只需要记录），如果没有才会走主键索引

获取记录数量的优化方法

explain select count(*) from <table> 或 show table status 不会真的去查询，rows 字段是对表记录数的估算值
单独维护一个计数字段

MyISAM 的 rows 字段

MyISAM 引擎在表头维护了总行数，所以它的 rows 字段是精确值

`LIMIT` 分页中的索引行为

SELECT * FROM page ORDER BY id LIMIT <offset>, <row>
SELECT * FROM page ORDER BY id LIMIT <row> OFFSET <offset>

这会跳过前 <offset> 条记录，返回接下来的 row 条记录如果 offset 过大，会导致读取大量无用数据

对于基于主键索引的查询，会走主键索引
对于基于二级索引的查询如果 offset 较小，会通过二级索引快速定位然后回表返回数据但如果 offset 很大，MySQL 可能会选择直接全表扫描，因为大量回表的成本可能反而超过全表扫描

性能优化策略

游标分页：基于上一页最后一条记录的 id 进行下一页的查询 SELECT * FROM page WHERE id > last_seen_id ORDER BY id LIMIT size;
减少不必要的列查询：只选择必要的列可以减少 I/O 开销
子查询优化：先找出目标范围的起始 id 再进行查询，减少需要处理的数据量 SELECT * FROM page WHERE id >= (SELECT id FROM page ORDER BY id LIMIT 6000000, 1) ORDER BY id LIMIT 10; 这个语句性能更好是因为子查询只会读取 id 值，大大减少 I/O 开销

锁

锁类型

全局锁

flush tables with read lock
unlock tables

加全局锁会让整个数据库变为只读，常用于全库逻辑备份对于支持「可重复读」事务隔离级别的存储引擎，可以在备份前开启事务，

表级锁

读锁时「共享锁」（S 锁），读锁是「排他锁」（X 锁）

表锁
- lock tables <table> read：当前线程对表只读，同时不允许访问其他表，阻塞其他线程对该表的写
- lock tables <table> write：当前线程能对表读写，同时不允许访问其他表，阻塞其他线程对该表的读写
- unlock tables：释放表锁（直接退出会话也可以释放表锁）
元数据锁（MDL）
- 对表进行 CRUD 时，自动加「MDL 读锁」
- 对表结构进行更改时，自动加「MDL 写锁」
- MDL 在事务提交后才会释放
意向锁
- 意向锁的目的是为了快速判断表里是否有记录被加锁
- 加「表共享锁」前要检查是否存在「行排他锁」，加「表排他锁」前要检查是否存在「行锁」，所以为了避免在加表锁前遍历所有数据来查询锁，在加行锁前要先加加对应的表级意向锁，这样在加表锁时只需查看是否有互斥的意向锁就行
AUTO-INC 锁：TODO

关于表锁

lock tables <table> read/write 是 MySQL Server 层提供的表锁命令，不是 InnoDB 自己实现的
MDL 也是 MySQL Server 层的锁
意向锁是 InnoDB 引擎实现的，只是信号锁，不会阻塞普通的 DML
AUTO-INC 是 InnoDB 引擎实现的，不会阻塞普通的 DML

行锁

记录锁（Record Lock）：把一条记录锁上，有 S 锁和 X 锁之分例如 for update 语句就会对记录加 X 锁
间隙锁（Gap Lock）：锁住一个区间，间隙锁的 S 和 X 效果都是一样的，都是为了阻止指定区间的插入行为
临键锁（Next-Key Lock）：记录锁和间隙锁的组合，锁住一个区间，以及区间内的记录
插入意向锁：一种特殊的间隙锁，但与同区间的普通间隙锁互斥，仅在执行插入操作时生成，例如对于现有记录 $[2, 5]$ ，一个事务想要插入 $3$ ，就会加上一个 $(2, 5)$ 的插入意向锁，如果检查到该区间存在间隙锁，则会进入等待

事务中的锁

事务中的生成的所有锁会在事务提交时全部释放

关于插入意向锁

插入意向锁之间是互相兼容的，例如对于现有记录 $[2, 5]$ ，事务 A 想要插入 $3$ ，就会在 $(2, 5)$ 的插入意向锁，事务 B 想要插入 $4$ 也会加上一个 $(2, 5)$ 的插入意向锁，事务 A 和事务 B 中的插入都会执行成功

如果插入位置重复了怎么办？ 例如事务 A 和 B 都要插入 $3$ 意向插入锁不会检查是否位置重复（主键或唯一键冲突），默认其他所有的插入意向锁的位置与自己不同，InnoDB 会在唯一性检查阶段进行检测，如果存在冲突，会进入串行化等待：

如果事务 A 操作成功，事务 B 再次检查，发现 $3$ 已存在，报 Dupulicate Key 错误
如果事务 A 操作失败回滚，则事务 B 继续插入，成功

当前读的错误操作导致锁全表

当执行 insert delete update select ... for update 这种会加临键锁的语句时，如果 where 筛选中没有索引，或者优化器经过判断后没使用索引，就会导致全表扫描的时候每一行记录都被加锁，造成了锁全表

解决方案

EXPLAIN 确认 UPDATE 走索引（type ≠ ALL）
WHERE 条件字段必须有合适索引
开启 sql_safe_updates = 1
大批量更新用 LIMIT 分批处理
避免在索引列上使用函数、计算、模糊前缀
必要时用 FORCE INDEX 强制走索引

AI 回答的大厂解决方案

SQL 审核平台：所有 DML 必须通过审核，禁止无索引 UPDATE
影子库/测试环境验证：上线前在影子库跑 EXPLAIN，确认执行计划
DBProxy 拦截：在数据库中间件层（如 ShardingSphere、Atlas）拦截危险 SQL
权限控制：生产账号只允许执行带主键/唯一索引的 UPDATE
监控告警：检测慢 SQL、全表扫描、大事务，实时告警

行锁的加锁规则

如果只用记录锁或间隙锁就能避免幻读，就不会使用临键锁（临键锁退化）

唯一索引的等值查询

如果记录存在，会加记录锁
如果不存在，会加间隙锁

唯一索引的范围查询

如果是大于等于或小于等于，临界点的记录如果存在则会为记录加记录锁，如果不存在则会加间隙锁，小于等于的时候记录锁会和左侧的间隙锁构成临键锁，
如果是大于或小于，如果临界点的记录不存在则会在左右两侧加间隙锁，如果存在则会加单侧的间隙锁（根据大于或者小于判断是哪一侧）大于的时候间隙锁会和右侧的记录锁构成临键锁

临键锁并不代表两个单独的锁

虽然上面说“间隙锁会和记录锁”构成临键锁，但是临键锁并不代表两个单独的锁，临键锁是一个完整的锁单元，作用范围是左开右闭的，“构成临键锁”只是方便理解

非唯一索引的等值查询

如果记录存在，则会在非唯一索引上记录的左侧加临键锁（防止插入重复数据），右侧加间隙锁，并在记录的主键索引上加记录锁
如果记录不存在，则会在非唯一索引的记录的两侧加间隙锁

非唯一索引的范围查询

会对所有扫描到的记录，在二级索引上加临键锁，在主键索引上加记录锁

可能的优化

其实对于 where age <= 22，右侧只需要加 $(22, 39)$ 的间隙锁就能避免幻读，但是 MySQL 还是选择了加 $(22, 39]$ 的临键锁

没加索引的查询

如果执行没有加索引的查询，会对全表进行扫描，如果语句会进行加锁操作（update/delete/insert/select-for-update），就会导致锁全表，属于严重事故！

死锁

死锁场景

假设对于 order_no 现有数据为 [1000， 1005， 1006]

事务 A 执行 SELECT id FROM t_order WHERE order_no = 1001 FOR UPDATE; 对 $(1000, 1005)$ 区间加「间隙锁」（临键锁退化）
事务 B 执行 SELECT id FROM t_order WHERE order_no = 1002 FOR UPDATE; 对 $(1000, 1005)$ 区间加「间隙锁」
事务 A 执行 INSERT INTO t_order(order_no, craeate_date) values(1001, now()) 生成 $(1000, 1005)$ 区间的「插入意向锁」，被事务 B 的间隙锁阻塞
事务 B 执行 INSERT INTO t_order(order_no, craeate_date) values(1002, now()) 生成 $(1000, 1005)$ 区间的「插入意向锁」，被事务 A 的间隙锁阻塞，事务 A、B 构成死锁

避免死锁

在系统层面：

设置 innodb_lock_wait_timeout = 50 默认为 50 秒
设置 innodb_deadlock_detect = ON MySQL 默认开启

在业务层面：

统一加锁顺序
减小事务粒度：事务越短，持锁时间越短，越不容易死锁
查询使用索引：防止锁全表
利用唯一索引避免重复

日志

Undo Log

Undo Log 的两个作用

实现回滚
- 插入时，存下插入记录的主键值，回滚时根据主键删除
- 更新时，存下被更新列的旧值，回滚时将对应列更新为旧值
- 删除时，存下整条记录，回滚时插入记录
与 ReadView 实现 MVCC
- 每次更新操作生成的 undolog 都会包含一个 roll_pointer 指针和一个 trx_id 事务 id
- trx_id 用于表示记录是由哪个事务更改的
- roll_pointer 将 undolog 串成一个链表，被称为版本链

删除和更新操作的特殊处理

DELETE 并不会直接删除，而是会将目标对象打上 delete flag，标记为删除，最终的删除操作是由 purge 线程完成的
UPDATE 的列如果是主键则会删除目标行然后插入新行，如果不是则会正常执行

Buffer Pool

在 MySQL 启动时，InnoDB 会申请一段连续的内存作为 BufferPoll，然后会按照 16KB 的大小将空间划分为多个页，还会有一部分空间用于存储自适应哈希索引 当读取数据时，如果数据存在于 BufferPool 中，客户端会直接从 BufferPool 获取数据，如果没有再去磁盘读取，并将其加载到 BufferPool 中 当修改数据时，如果目标数据存在于 BufferPool 中，会直接修改 BufferPool 中的数据，并将目标页标记为脏页，后续再将脏页写入磁盘

数据页：存储聚簇索引的叶子节点
索引页：存储二级索引的节点（包括叶子节点和非叶子节点）
Undo 页：作为 Undo Log 的缓冲区，事务修改数据时，旧值先写入 undo 页，然后再刷到磁盘
ChangeBuffer 页：对于非唯一二级索引，当有要增改的数据，如果目标二级索引页不在 BufferPool 内，则先将变更记录写到 ChangeBuffer，后续目标二级索引页加载到 BufferPool 中时再合并
自适应哈希索引：对热点数据建立哈希索引，加速等值查询 TODO

BufferPool 和磁盘的 IO 方式

BufferPool 对 .ibd 文件的读写使用 Direct I/O，对日志文件的读写使用 Buffered I/O

自适应哈希索引

自适应哈希索引不是 16KB 的页，而是 BufferPool 内部的数据结构

Undo 页的刷盘

Undo 页和 Redo Log 不一样，Undo 页是通过「直接 IO」写入磁盘的，不会接触文件缓存（Page Cache）

Redo Log

Redo Log 是什么

Redo Log 是为了实现 ACID 中的持久性（Durability）而设计的，用于防止 MySQL 异常重启导致 BufferPool 中还未刷入磁盘的脏页和 undo 页丢失每一条记录更新时，InnoDB 先更新 BufferPool 中的页，标记为脏页，然后将本次对页的修改（包括 Undo 页）写到 Redo Log Buffer，后续由后台线程将脏页和 Redo Log Buffer 刷入磁盘，这就是 WAL（Write-Ahead Logging）技术

为什么要写入 RedoLog 而不是直接写入数据

数据的写入是随机写，而 RedoLog 的写入是顺序写，顺序写比随机写高效很多，因此 RedoLog 写入磁盘的开销更小这也变相地将 MySQL 写操作从「随机写」优化为了「顺序写」，提高了执行性能

Redo Log Buffer 的大小

默认为 16MB，可以通过 innodb_log_buffer_size 调整大小，增加其大小可以让 MySQL 在处理「大事务」时不必写入磁盘，从而提升 I/O 性能

Redo Log 的刷盘机制

一定会刷盘的时机

后台线程每秒自动刷一次（触发 write + fsync）
Redo Log Buffer 使用量超过一半时
MySQL 关闭时

通过 innodb_flush_log_at_trx_commit 控制刷盘时机

0：提交时不作任何操作，只靠后台线程每秒刷盘，可能会丢失一秒的数据
1：默认值，提交时直接刷入磁盘，安全性较高
2：提交时写入 OS 的 Page Cache（文件缓存）即只 write 不 fsync，由后台线程每秒 fsync 刷入磁盘，只要操作系统不崩溃就不会丢失数据

如果 Redo Log 写满了

当 BufferPool 中的脏页刷到了磁盘中，Redo Log 中的记录就没用了，应该擦除如图为 Redo Log 结构，由两个文件组成一个循环，write_pos 表示当前记录写到的位置，checkpoint 表示要擦除的位置，蓝色区域表示 Redo Log 中还未落盘的脏页记录（已使用区域），红色区域用来记录新的更新操作如果 write_pos 追上了 checkpoint，意味着 Redo Log 满了，此时所有新的更新操作会被阻塞，将 BufferPool 中的脏页刷新到磁盘，然后擦除旧的记录后 MySQL 才会恢复正常运行

Bin Log

MySQL 完成更新操作后，Server 层还会生成一条 binlog，事务提交时，会将事务执行过程中生成的所有 binlog 写入 Bin Log 文件
Bin Log 记录了所有的数据库表结构和表数据的更新操作，不会记录查询操作

Bin Log 和 Redo Log 的区别

适用对象不同： Bin Log 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以用 Redo Log 是 InnoDb 引擎实现的日志
文件格式不同： Bin Log 是逻辑日志，记录每一条修改数据的 SQL Redo Log 是物理日志，记录某个数据页做了什么修改
写入方式不同： Bin Log 是追加写，如果写满一个文件，就建一个新文件继续写 Redo Log 是循环写，日志空间大小固定
用途不同： Bin Log 用于主从复制、备份恢复 Redo Log 用于故障恢复

主从复制的实现

主库在收到客户端提交事务的请求后，先写入 binlog，然后提交事务
从库创建一个 I/O 线程，向主库发送复制请求，主库创建 log dump 线程发送 binlog，从库将接收的 binlog 写入 relay log（中继日志）然后返回“复制成功”的响应
从库创建一个 SQL 线程，读取 relay log 然后回放 binlog 实现更新存储引擎中的数据，最终做到主从的数据一致性

主从复制的用法

完成主从复制后，就可以做到读写分离，写时只写主库，读时只读从库但是从库不是越多越好，因为主库要创建同样多的 log dump 线程来处理复制请求实际使用，中一个主库一般跟 2~3 个从库

主从复制模型

主从复制有三种模型：

同步复制：主库提交事务的线程要等待所有从库的复制响应成功才会返回结果给客户端，实际项目中基本没法用
异步复制：主库提交事务的线程不会等待从库的响应，就直接返回结果给客户端，这种模式下，一旦主库宕机就会造成数据丢失
半同步复制：折中策略，只要数据成功复制到任意一个从库上，主库的事务线程就会返回结果给客户端

Bin Log 的刷盘机制

每个线程都有一个单独的 binlog cache，事务执行时 binlog 先写入 binlog cache，事务提交时将完整事务写入 binlog 文件（write 到 Page Cache） MySQL 通过 sync_binlog 控制 fsync 的时机：

0：由操作系统决定何时刷盘，性能最好，风险也最大
1：每次提交都 fsync，最安全，最多只丢失一个事务的 binlog
n(>=2)：每 n 个事务 fsync 一次，如果能容忍少量事务的 binlog 丢失的风险，一般会将 sync_binlog 设置为 100~1000 之间的值

两阶段提交

Redo Log 和 Bin Log 必须完全一致，否则会导致主从不一致，由此提出两阶段提交，事务提交被拆为两个阶段：

Prepare 阶段 InnoDB 将 Redo Log 写入磁盘，状态标记为 PREPARE
Commit 阶段 Server 层将 Bin Log 写入磁盘 InnoDB 将 Redo Log 状态改为 COMMIT 事务提交的成功以 Bin Log 的写入成功为标志

崩溃后的恢复

如果发现某事务处于 PREPARE 阶段，就回到 Bin Log 查询该事务：

Bin Log 中有该事务，提交该事务
Bin Log 中没有该事务，回滚

事务未提交时，Redo Log 也会刷入磁盘吗

Redo Log 可以在事务提交之前持久化到磁盘，但是 Bin Log 必须在事务提交之后才可以持久化到磁盘
如果 MySQL 崩溃，还没提交事务的 Redo Log 已被持久化到磁盘，而 Bin Log 还没有，Bin Log 中查不到该事务，就会进行回滚操作

两阶段提交的问题

I/O 次数高：当 sync_binlog 和 innodb_flush_log_at_trx_commit 都为 1 的时候，每次事务提交都会至少调用 2 次刷盘操作（Redo Log 和 Bin Log 刷盘），这会成为性能瓶颈
锁竞争激烈：早期 MySQL 版本中，通过使用 prepare_commit_mutex 锁来保证事务提交顺序，一个事务拿到锁后才嫩刚进入「Prepare 阶段」，直到「Commit 阶段」释放锁，下一个事务才可以继续

优化策略：组提交

TODO

一条 SQL 语句的执行过程

TODO

参考资料：

小林 coding
小白 debug

MySQL 架构

MySQL 架构

MySQL 存储结构

文件存储结构

表空间文件的结构

InnoDB 的 Compact 行格式

事务隔离级别

MVCC 工作机制

ReadView

聚簇索引记录中的隐藏列

事务访问数据的流程

不同隔离级别中 ReadView

索引

按物理存储分类

按逻辑功能分类

索引失效

count() 中的索引行为

LIMIT 分页中的索引行为

锁

锁类型

全局锁

表级锁

行锁

行锁的加锁规则

唯一索引的等值查询

唯一索引的范围查询

非唯一索引的等值查询

非唯一索引的范围查询

死锁

死锁场景

避免死锁

日志

Undo Log

Buffer Pool

Redo Log

Redo Log 是什么

Redo Log 的刷盘机制

Bin Log

Bin Log 和 Redo Log 的区别

主从复制的实现

Bin Log 的刷盘机制

两阶段提交

崩溃后的恢复

两阶段提交的问题

优化策略：组提交

一条 SQL 语句的执行过程

`count()` 中的索引行为

`LIMIT` 分页中的索引行为