4面试题--数据库（mysql）

执⾏⼀条 select / update 语句，在 MySQL 中发⽣了什么？

Server 层负责建⽴连接、分析和执⾏ SQL。MySQL ⼤多数的核⼼功能模块都在这实现，主要包括

连接器，查询缓存（8.0版本去除，因为每次更新将会清空该表缓存，缓存作⽤较低）、解析器、预处理器、优化器、执⾏器等。另外，所有的内置函数（如⽇期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。

存储引擎层负责数据的存储和提取。⽀持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的

存储引擎共⽤⼀个 Server 层。从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引

擎。索引数据结构就是由存储引擎层实现的，不同的存储引擎⽀持的索引类型也不相同，⽐如

InnoDB ⽀持索引类型是 B+树。

执⾏流程：

1. 连接MySQL：TCP 三次握⼿——连接器验证⽤户名和密码——连接器获取⽤户权限，然后后⾯的权限逻辑判断都基于此时读取到的权限（管理员中途修改⽤户权限重启该⽤户⽣效）【使⽤ show processlist 查看MySQL服务器被多少客户端连接】

2. 查询缓存：若有则直接返回数据。

3. 解析 SQL：⾸先是词法分析。根据输⼊的字符串识别出关键字，构建出 SQL 语法树，这样⽅便后⾯模块获取 SQL 类型、表名、字段名、 where 条件等等。之后是语法分析，语法解析器会根据语法规则，判断这个 SQL 语句是否满⾜ MySQL 语法。

4. 执⾏ SQL：⾸先是预处理阶段，预处理器检查 SQL 查询语句中的表或者字段是否存在，并将

select * 中的 * 符号，扩展为表上的所有列；然后优化器主要负责将 SQL 查询语句的执⾏⽅案确

定下来，⽐如在表⾥⾯有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使⽤哪个索

引。最后执⾏器就会和存储引擎交互了，交互是以记录为单位的，每查询到⼀条记录则返回给客户

端，再接着查下⼀条记录。⽽对于update，需要先写⼊ redo log 缓冲区中，然后再写⼊ undo 数

据⻚中，事务提交之后写⼊ binlog ⽇志缓存区内，所有缓存区数据会在合适时间进⾏磁盘写⼊。

4.1 三⼤范式

第⼀范式（确保每列保持原⼦性）

如果数据库表中的所有字段值都是不可分解的原⼦值，就说明该数据库表满⾜了第⼀范式。1NF 是关系型数据库最基本的条件，否则不能称之为关系型数据库

第⼆范式（确保表中的每列都和主键相关）

第⼆范式在第⼀范式的基础之上更进⼀层。第⼆范式需要确保数据库表中的每⼀列都和主键相关，⽽不能只与主键的某⼀部分相关（主要针对联合主键⽽⾔）。也就是说在⼀个数据库表中，⼀个表中只能保存⼀种数据，不可以把多种数据保存在同⼀张数据库表中，通俗讲就是说该分表的时候就分表，不要将多个表内容合并为⼀个表

第三范式（确保每列都和主键列直接相关，⽽不是间接相关）

第三范式需要确保数据表中的每⼀列数据都和主键直接相关，⽽不能间接相关。通俗讲就是说当涉及到其他表项内容时设置外键进⾏关联，⽽不要加⼊其他表项内容。

4.2 数据库引擎

Innodb

存储结构

InnoDB 的数据是按「数据⻚」为单位来读写的，默认数据⻚⼤⼩为 16 KB。每个数据⻚之间通过双向链表的形式组织起来，物理上不连续，但是逻辑上连续。数据⻚内包含⽤户记录，InnoDB 在查找某条记录时，并不能直接找到对应的⾏记录，⽽是只能获取到记录所在的⻚，然后将整个⻚⾯加载到内存中，在内存中遍历找到具体⾏。每个记录之间⽤单向链表的⽅式组织起来，为了在数据⻚内⾼效查询记录，设计了⼀个⻚⽬录，且主键值是有序的，因此可以通过⼆分查找法的⽅式进⾏检索从⽽提⾼效率。

Buffer Pool

Innodb 存储引擎设计了⼀个缓冲池（Buffer Pool），来提⾼数据库的读写性能。Buffer Pool ⾥有三种结构来管理数据。

● Free Page（空闲⻚），表示此⻚未被使⽤，位于 Free 链表；

● Clean Page（⼲净⻚），表示此⻚已被使⽤，但是⻚⾯未发⽣修改，位于LRU 链表。

● Dirty Page（脏⻚），表示此⻚「已被使⽤」且「已经被修改」，其数据和磁盘上的数据已经不⼀致。当脏⻚上的数据写⼊磁盘后，内存数据和磁盘数据⼀致，那么该⻚就变成了⼲净⻚。

简单的 LRU 算法并没有被 MySQL 使⽤，因为其⽆法避免下⾯这两个问题：

● 预读失效：MySQL 在加载数据⻚时，会提前把它相邻的数据⻚⼀并加载进来，⽬的是为了减少磁盘 IO。但是这些被提前加载进来的数据⻚，可能并没有被访问，相当于这个预读是⽩做了，这就是预读失效。MySQL 改进了 LRU 算法，将 LRU 划分了 2 个区域：old 区域和 young 区域。

young 区域在 LRU 链表的前半部分，old 区域则是在后半部分。young 区域占整个 LRU 链表⻓度

的⽐例可以通过参数来设置，⼀般 young 区域为 old 区域的 2 倍⼤⼩。划分这两个区域后，预读

的⻚就只需要加⼊到 old 区域的头部，当⻚被真正访问的时候，才将⻚插⼊ young 区域的头部。

如果预读的⻚⼀直没有被访问，就会从 old 区域移除，这样就不会影响 young 区域中的热点数

据。

● Buffer Pool 污染：当某⼀个 SQL 语句扫描⼤量的数据时，在 Buffer Pool 空间⽐较有限的情况

下，可能会将 Buffer Pool ⾥的所有⻚都替换出去，导致⼤量热数据被淘汰，等这些热数据⼜被再

次访问的时候，由于缓存未命中，就会产⽣⼤量的磁盘 IO，MySQL 性能就会急剧下降，这个过程

被称为 Buffer Pool 污染。解决⽅案是只有同时满⾜「被访问」与「在 old 区域停留时间超过 N

秒」两个条件，才会被插⼊到 young 区域头部，这样就解决了 Buffer Pool 污染的问题。另外，

MySQL 针对 young 区域其实做了⼀个优化，为了防⽌ young 区域节点频繁移动到头部。young

区域前⾯ 1/4 被访问不会移动到链表头部，只有后⾯的 3/4被访问了才会。

MyISAM

存储结构

MyISAM 的数据是顺序存储的。索引的 B+ 树叶节点存放数据记录的地址，可以直接定位到数据，因此查找速度很快。

4.3 数据库索引

先查询⼆级索引（⼜叫⾮聚集索引，⼆级索引的 B+Tree 的叶⼦节点存放的是主键值，⽽不是实际数据）中的 B+Tree 的索引值，找到对应的叶⼦节点，然后获取主键值，之后再通过主键索引中的 B+Tree 树查询到对应的叶⼦节点，然后获取整⾏数据。这个过程叫「回表」，也就是说要查两个B+Tree 才能查到数据。
在⼆级索引的 B+Tree 就能查询到结果的⽅式就叫「覆盖索引」，也就是只需要查⼀个 B+Tree 就能找到数据。
全⽂索引 https://blog.csdn.net/mrzhouxiaofei/article/details/79940958

索引分类

根据底层数据结构划分

索引是提⾼查询效率的数据结构，⽽MySQL中⽤到了B+Tree和散列表（Hash表）作为索引的底层数据结构（其实也⽤到了跳表实现全⽂索引，但这不是重要考点）。

1. hash索引：MySQL并没有显式⽀持Hash索引，⽽是作为内部的⼀种优化。具体在Innodb存储引擎⾥，会监控对表上⼆级索引的查找，如果发现某⼆级索引被频繁访问，⼆级索引成为热数据，就之建⽴hash索引。

2. B+树索引：这个是MySQL索引的基本实现⽅式。读取⼀个节点相当于⼀次磁盘 I/O 操作。

B+Tree 相⽐于⼆叉树来说，最⼤的优势在于查询效率很⾼，因为即使在数据量很⼤的情况，查询

⼀个数据的磁盘 I/O 依然维持在 3-4次。

根据数据与索引的存储关联性划分

索引⼜可以分成聚簇索引和⾮聚簇索引（⼆级索引），它们区别就在于叶⼦节点存放的是什么数据：

● 聚簇索引的叶⼦节点存放的是实际数据，所有完整的⽤户记录都存放在聚簇索引的叶⼦节点；

● ⼆级索引的叶⼦节点存放的是主键值，⽽不是实际数据。

因为表的数据都是存放在聚簇索引的叶⼦节点⾥，所以 InnoDB 存储引擎⼀定会为表创建⼀个聚簇索引，且由于数据在物理上只会保存⼀份，所以聚簇索引只能有⼀个。

InnoDB 在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：

● 如果有主键，默认会使⽤主键作为聚簇索引的索引键；

● 如果没有主键，就选择第⼀个不包含 NULL 值的唯⼀列作为聚簇索引的索引键；

● 在上⾯两个都没有的情况下，InnoDB 将⾃动⽣成⼀个隐式⾃增 id 列作为聚簇索引的索引键；

唯⼀索引、主键的区别

1. 主键是⼀种约束，是逻辑键，实际不存在；唯⼀索引是⼀种索引，是物理键，实际存在

2. 主键创建后⼀定包含唯⼀索引；唯⼀索引并不⼀定是主键

3. 唯⼀索引列允许空值；⽽主键列不允许为空值

4. 主键可以被其他表引⽤为外键；⽽唯⼀索引不能

5. ⼀个表有且只能创建⼀个主键；但可以创建多个唯⼀索引

6. 主键和唯⼀索引都可以有多列

索引优缺点

索引的优点：

● 加快数据的检索速度

● 减少查询中分组和排序的时间

● 通过创建主键索引，可以保证数据库表中每⼀⾏数据的唯⼀性

● 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在⼀起）

索引的缺点：

● 占⽤额外空间

● ⼀开始增加索引时，需要创建索引表

● 新增或删除数据时，需要维护索引表

索引使⽤场景

索引的适⽤场景：

● 等值查询

● 范围查询

● 匹配最左前缀（联合索引）

适合建⽴索引：

● 经常作为查询条件的字段，且列上的不同值较多；

● 频繁进⾏排序或分组（即进⾏ group by 或 order by 操作）的列。建⽴索引之后在 B+Tree 中的记录都是排序好的

● 如果待排序的列有多个，可以在这些列上建⽴联合索引

不适合建⽴索引：

● 更新频繁的字段

● 不会作为查询条件的字段

● 表记录很少的时候

优化索引⽅法

使⽤覆盖索引

假设只需要查询商品的名称、价格，可以建⽴⼀个联合索引，即「商品ID、名称、价格」作为⼀个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从⽽避免回表，也减少了⼤量的 I/O操作。主键索引最好是⾃增的

InnoDB 创建主键索引默认为聚簇索引，数据被存放在了 B+Tree 的叶⼦节点上。如果使⽤⾃增主键，那么每次插⼊的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当⻚⾯写满，就会⾃动开辟⼀个新⻚⾯，因此这种插⼊数据的⽅法效率⾮常⾼。

如果使⽤⾮⾃增主键，由于每次插⼊主键的索引值都是随机的，因此每次插⼊新的数据时，就可能会插⼊到现有数据⻚中间的某个位置，这将不得不移动其它数据来满⾜新数据的插⼊，甚⾄需要从⼀个⻚⾯复制数据到另外⼀个⻚⾯，通常将这种情况称为⻚分裂。⻚分裂可能会造成⼤量的内存碎⽚，导致索引结构不紧凑，从⽽影响查询效率。

索引最好设置为 NOT NULL索引列存在 NULL 会导致优化器在做索引选择的时候难以优化，⽐如进⾏索引统计时，count 会忽略值为 NULL 的⾏。NULL 值是⼀个没意义的值，但是它会占⽤物理空间。防⽌索引失效

索引失效情况：

● 以 % 开头的 like 查询

● 对索引列进⾏函数运算，正则表达式

● 联合索引的情况下，不满⾜最左原则

● MySQL 估计使⽤索引⽐全表扫描更慢的情况

● ⽤ or 分割开的条件，如果 or 前的条件中的列有索引，⽽后⾯的列中没有索引，那么涉及的索引都

不会被⽤到

● 使⽤负向查询（not ，not in， not like ，<> ,!= ,!> ,!< ）不会使⽤索引

数据库为什么⽤ B+ 树做索引

要设计⼀个 MySQL 的索引数据结构，不仅仅考虑数据结构增删改的时间复杂度，更重要的是要考虑磁盘 I/0 的操作次数。因为索引和记录都是存放在硬盘，硬盘是⼀个⾮常慢的存储设备，在查询数据的时候，最好能⽤尽可能少的磁盘 I/0 的操作完成。

● ⼆分查找树虽然是⼀个天然的⼆分结构，能很好的利⽤⼆分查找快速定位数据，但是它存在⼀种极端的情况，每当插⼊的元素都是树内最⼤的元素，就会导致⼆分查找树退化成⼀个链表，此时查询复杂度就会从 O(logn) 降低为 O(n)。

● 为了解决⼆分查找树退化成链表的问题，就出现了⾃平衡⼆叉树，保证查询操作的时间复杂度⼀直维持在 O(logn) 。但是它本质上还是⼀个⼆叉树，每个节点只能有 2 个⼦节点，随着元素的增多，树的⾼度会越来越⾼。⽽树的⾼度决定于磁盘 I/O 操作的次数。

● B 树和 B+树都是通过多叉树的⽅式，将树的⾼度变矮，所以这两个数据结构⾮常适合检索存于磁盘中的数据。

B 与 B+ 树差异

● 单点查询

B 树进⾏单个索引查询时，最快可以在 O(1) 的时间代价内就查到，从平均时间代价来看，会⽐ B+ 树稍快⼀些。但是 B 树的查询波动会⽐较⼤，因为每个节点既存索引⼜存记录，所以有时候访问到了⾮叶⼦节点就可以找到索引，⽽有时需要访问到叶⼦节点才能找到索引。

B+ 树的⾮叶⼦节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相⽐既存索引⼜存记录的 B 树，B+树的⾮叶⼦节点可以存放更多的索引，因此 B+ 树可以⽐ B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。

● 范围查询

因为 B+ 树所有叶⼦节点间还有⼀个双向链表进⾏连接，这种设计对范围查找⾮常有帮助。⽽ B 树没有将所有叶⼦节点⽤链表串联起来的结构，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

● 插⼊和删除效率

B+ 树有⼤量的冗余节点，当删除⼀个节点的时候，直接从叶⼦节点中删除，甚⾄可以不动⾮叶⼦节点，这样删除⾮常快。B+ 树的插⼊也是⼀样，有冗余节点，插⼊可能存在节点的分裂（如果节点饱和），但是最多只涉及树的⼀条路径。⽽且 B+ 树会⾃动平衡，不需要更多复杂的算法。因此，B+ 树的插⼊和删除效率更⾼。因此，存在⼤量范围检索的场景，适合使⽤ B+树，⽐如mysql。⽽对于⼤量的单个索引查询的场景，可以考虑 B 树，⽐如 nosql 的MongoDB。

联合索引与最左匹配原则

如果频繁地使⽤相同的⼏个字段查询，就可以考虑建⽴这⼏个字段的联合索引来提⾼查询效率。⽐如对于联合索引 test_col1_col2_col3，实际建⽴了 (col1)、(col1, col2)、(col, col2, col3) 三个索引。联合索引的主要优势是减少结果集数量：如果根据 col1、col2、col3 的单列索引进⾏查询，需要分别得到num[i] 个结果，然后再取交集；⽽如果使⽤联合索引查询，只会得到很少的⼀段数据。

最左匹配原则：这些索引能够被包含 col1、(col1 col2)、(col1 col2 col3) 的查询利⽤到，但是不能够被 col2、(col2、col3) 的等值查询利⽤到。这与底层实现有关。联合索引的最左匹配原则，在遇到范围查询（>、<、between、like 包括like '林%'这种）的时候，就会停⽌匹配，也就是范围列可以⽤到联合索引，但是范围列后⾯的列⽆法⽤到联合索引。但是如果是 >=、<= 时可以继续⾛索引。

最左匹配实践：

count(...) 查询效率

（都是对记录进⾏逐条判断，

后⾯两个有额外判断是否为NULL的步骤）

● count(1)、 count(*)、 count(主键字段) 在执⾏的时候，如果表⾥存在⼆级索引，优化器就会选择

⼆级索引进⾏扫描。所以，如果要执⾏ count(1)、 count(*)、 count(主键字段) 时，尽量在数据表

上建⽴⼆级索引，这样优化器会⾃动采⽤ key_len 最⼩的⼆级索引进⾏扫描，相⽐于扫描主键索引

效率会⾼⼀些。

● 不要使⽤ count(字段) 来统计记录个数，因为它的效率是最差的，会采⽤全表扫描的⽅式来统计。使⽤ MyISAM 引擎时，执⾏ count 函数只需要 O(1 )复杂度，这是因为每张 MyISAM 的数据表都有⼀个 meta 信息存储了row_count值，由表级锁保证⼀致性，所以直接读取 row_count 值就是 count 函数的执⾏结果。

⽽ InnoDB 存储引擎是⽀持事务的，同⼀个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少⾏”也是不确定的，所以⽆法像 MyISAM⼀样，只维护⼀个 row_count 变量。⽽当带上 where 条件语句之后，MyISAM 跟 InnoDB 都需要扫描表来进⾏记录个数的统计。

如何优化 count(*)？

● 近似值：可以使⽤ show table status 或者 explain 命令来进⾏估算。执⾏ explain 命令效率是很

⾼的，因为它并不会真正的去查询。

● 额外表保存计数值：如果是想精确的获取表的记录总数，我们可以将这个计数值保存到单独的⼀张计数表中。当我们在数据表插⼊⼀条记录的同时，将计数表中的计数字段 + 1。也就是说，在新增和删除操作时，我们需要额外维护这个计数表。

4.4 关系型数据库、⾮关系型数据库

关系型数据库（SQL）

关系型数据库是使⽤关系模型（⼆维表格模型）来组织数据的数据库。

常⻅关系型数据库

1. Oracle

2. MySql

3. Microsoft SQL Server

4. SQLite

优势

1. 采⽤⼆维表结构容易理解，全部由表结构组成，⽂件格式⼀致；

2. ⽀持通⽤的SQL（结构化查询语⾔）语句。可以在多个表之间做繁杂的查询；

3. 提供对事务的⽀持，同时提供事务的回滚、并发控制和死锁；

4. 数据存储在磁盘中，安全可靠。

不⾜

1. ⾼并发读写能⼒差。⼀台数据库的最⼤连接数有限，且硬盘 I/O 有限，不能同时满⾜很多⼈连接；

2. 海量数据情况下读写效率低。对⼤数据量的表进⾏读写操作时，响应时间⻓；

3. 可扩展性不⾜。⽆法通过简单的添加硬件和服务节点来拓展性能和负荷能⼒；

4. 数据模型灵活度低。关系型数据库的数据模型定义严格，⽆法快速容纳新的数据类型

⾮关系型数据库（NOSQL）

⾮关系型数据库被称为 NoSQL。数据通常以对象的形式存储在数据库中，⽽对象之间的关系通过每个对象⾃身的属性来决定，常⽤于存储⾮结构化的数据。

常⻅的NOSQL数据库

1. 键值数据库：Redis

2. 列族数据库：HBase

3. ⽂档数据库：MongoDB

4. 图形数据库：Neo4j

优势

1. 存储数据的格式可以是 key-value 形式、⽂档形式、图⽚形式等。⽽关系型数据库只⽀持基础类

型；

2. 速度快，效率⾼。 NoSQL 可以使⽤硬盘或者随机存储器作为载体，⽽关系型数据库只能使⽤硬

盘；

3. 海量数据的维护和处理简单，成本低；

4. 具有扩展简单、⾼并发、⾼稳定性、成本低廉的优势；

5. 可以实现数据的分布式处理。

不⾜

1. 暂时不提供 SQL ⽀持，学习和使⽤成本较⾼；

2. 没有事务处理，⽆法保证数据的完整性和安全性。适合处理海量数据，但是不⼀定安全；

3. 复杂表关联查询不易实现。

4.5 数据库连接池

好处：

1. 资源重⽤。在内部对象池中，维护⼀定数量的数据库连接，并对外暴露数据库连接的获取和返回⽅法，由于数据库连接得到重⽤，避免了频繁创建、释放连接引起的⼤量性能开销。

2. 更快的系统响应速度。数据库连接池在初始化过程中，往往已经创建了若⼲数据库连接于池内备

⽤。此时连接池的初始化操作均已完成，对于业务请求⽽⾔，直接利⽤现有可⽤连接，避免了数据

库连接初始化和释放过程的时间开销，从⽽缩短了系统整体响应时间。

3. 统⼀的连接管理，避免数据库连接泄漏。在较为完备的数据库连接池中，可根据预先的连接超时设定，强制收回被占⽤的连接，从⽽避免了常规数据库连接操作中可能出现的资源泄漏。

4.6 事务

事务的定义：⼀个事务是⼀组对数据库中数据操作的集合。⽆论集合中有多少操作，对于⽤户来说，只

是对数据库状态的⼀个原⼦改变。

ACID 特性与实现

1. 原⼦性（Atomicity）：指⼀个事务中的操作，要么全部成功，要么全部失败，如果失败就回滚到

事务开始前的状态

2. ⼀致性（Consistency）：指事务必须使数据库从⼀个⼀致性状态变换到另⼀个⼀致性状态。⽐如

转账，A账户和B账户相互转账，⽆论如何操作，A、B账户的总⾦额都必须是不变的

3. 隔离性（Isolation）：指当多个⽤户并发的访问数据库时，事务之间的并发是隔离的。⽐如两个并

发的事务T1和T2，T1要么在T2开始前执⾏，要么在T2结束后执⾏。

4. 持久性（Durability）：指事务⼀旦被提交，数据库中数据的改变就是永久性的。

回滚⽇志：发⽣错误或者需要回滚的事务能够成功回滚（原⼦性）

Innodb 存储引擎层⽣成的⽇志，当事务尝试对数据进⾏修改时，会先记录到回滚⽇志 undo log 中，然

后再对数据库中的对应⾏进⾏写⼊。在异常发⽣时，对已经执⾏的操作进⾏回滚。主要⽤于事务回滚和

MVCC。

重做⽇志：在事务提交后，数据没来得及写回磁盘就宕机时，能够成功恢复数据（持久性）

Innodb 存储引擎层⽣成的⽇志，重做⽇志由两部分组成，⼀是内存中的重做⽇志缓冲区，另⼀个就是在磁盘上的重做⽇志⽂件。当事务尝试对数据进⾏修改时，会先将数据从磁盘读⼊内存，并更新内存中缓存的数据，然后⽣成⼀条记录并写⼊重做⽇志缓存，当事务真正提交时，会将重做⽇志缓存中的内容刷新到重做⽇志⽂件，再将内存中的数据更新到磁盘上。

在 InnoDB 中，重做⽇志都是以 512 字节的块的形式进⾏存储的，同时因为块的⼤⼩与磁盘扇区⼤⼩相同，所以重做⽇志的写⼊可以保证原⼦性，不会由于机器断电导致重做⽇志仅写⼊⼀半⽽留下脏数据。除了所有对数据库的修改会产⽣重做⽇志，因为回滚⽇志也是需要持久存储的，它们也会创建对应的重做⽇志，在发⽣错误后，数据库重启时会从重做⽇志中找出未被更新到数据库磁盘中的⽇志重新执⾏以满⾜事务的持久性。

binlog （归档⽇志）：是 Server 层⽣成的⽇志，binlog ⽂件是记录了所有数据库表结构变更和表数据修改的⽇志，不会记录查询的操作。主要⽤于数据备份和主从复制。

redo log 和 binlog 有什么区别？

● 适⽤对象不同：binlog 是 Server 层实现的⽇志，所有存储引擎都可以使⽤；redo log 是 Innodb

存储引擎实现的⽇志；

● ⽂件格式不同：binlog 有 3 种格式类型，分别是 STATEMENT（默认格式，记录操作）、ROW

（记录数据⾏）、 MIXED（⾃动混合使⽤）；redo log 记录的是在某个数据⻚做了什么修改

● 写⼊⽅式不同：binlog 是追加写，写满⼀个⽂件，就创建⼀个新的⽂件继续写，不会覆盖以前的⽇志，保存的是全量的⽇志。redo log 是循环写，⽇志空间⼤⼩是固定的，全部写满就从头开始，保存未被刷⼊磁盘的脏⻚⽇志。

● ⽤途不同：binlog ⽤于数据备份、主从复制；redo log ⽤于断电等故障恢复。

MVCC（多版本并发控制）或锁机制保证隔离性，⽽⼀致性则是通过持久性+原⼦性+隔离性来保证；

两阶段提交

在持久化 redo log 和 binlog 这两份⽇志的时候，如果出现半成功的状态，就会造成主从环境的数据不⼀致性。这是因为 redo log 影响主库的数据，binlog 影响从库的数据，所以 redo log 和 binlog 必须保持⼀致才能保证主从数据⼀致。MySQL 为了避免出现两份⽇志之间的逻辑不⼀致的问题，使⽤了「两阶段提交」来解决，两阶段提交其实是分布式事务⼀致性协议，它可以保证多个逻辑操作要么全部成功，要么全部失败，不会出现半成功的状态。两阶段提交把单个事务的提交拆分成了 2 个阶段，分别是分别是「准备（Prepare）阶段」和「提交（Commit）阶段」，每个阶段都由协调者（Coordinator）和参与者（Participant）共同完成。注意，不要把提交（Commit）阶段和 commit 语句混淆了，commit 语句执⾏的时候，会包含提交（Commit）阶段。在 MySQL 的 InnoDB 存储引擎开启 binlog 的情况下，MySQL 会同时维护 binlog ⽇志与 redo log，当客户端执⾏ commit 语句或者在⾃动提交的情况下，为了保证这两个⽇志的⼀致性，MySQL 内部开启⼀个 XA 事务，分两阶段来完成 XA 事务的提交，就是将 redo log 的写⼊拆成了两个步骤：prepare 和 commit，中间再穿插写⼊binlog，具体如下：

● prepare 阶段：将 XID（内部 XA 事务的 ID）写⼊到 redo log，同时将 redo log 对应的事务状态

设置为 prepare，然后将 redo log 刷新到硬盘；

● commit 阶段：把 XID 写⼊到 binlog，然后将 binlog 刷新到磁盘，接着调⽤存储引擎的提交事务

接⼝，将 redo log 状态设置为 commit异常发⽣时⽐较 redolog 和 binlog ⽇志中的 XID，若⼀致则提交事务，否则回滚事务。两阶段提交虽然保证了两个⽇志⽂件的数据⼀致性，但是性能很差，主要有两个⽅⾯的影响：

● 磁盘 I/O 次数⾼：每个事务提交都会进⾏两次 fsync（刷盘），⼀次是 redo log 刷盘，另⼀次是

binlog 刷盘。

● 锁竞争激烈：两阶段提交虽然能够保证「单事务」两个⽇志的内容⼀致，但在「多事务」的情况

下，却不能保证两者的提交顺序⼀致，因此，在两阶段提交的流程基础上，还需要加⼀个锁来保证

提交的原⼦性，从⽽保证多事务的情况下，两个⽇志的提交顺序⼀致。

组提交 MySQL 引⼊了 binlog 组提交（group commit）机制，当有多个事务提交的时候，会将多个 binlog 刷盘操作合并成⼀个，从⽽减少磁盘 I/O 的次数，如果说 10 个事务依次排队刷盘的时间成本是 10，那么将这 10 个事务⼀次性⼀起刷盘的时间成本则近似于 1。

引⼊了组提交机制后，prepare 阶段不变，只针对 commit 阶段，将 commit 阶段拆分为三个过程：

● flush 阶段：多个事务按进⼊的顺序将 binlog 从 cache 写⼊⽂件（不刷盘）；

● sync 阶段：对 binlog ⽂件做 fsync 操作（多个事务的 binlog 合并⼀次刷盘）；

● commit 阶段：各个事务按顺序做 InnoDB commit 操作；

上⾯的每个阶段都有⼀个队列，每个阶段有锁进⾏保护，因此保证了事务写⼊的顺序，第⼀个进⼊队列的事务会成为 leader，leader领导所在队列的所有事务，全权负责整队的操作，完成后通知队内其他事务操作结束。对每个阶段引⼊了队列后，锁就只针对每个队列进⾏保护，不再锁住提交事务的整个过程，可以看的出来，锁粒度减⼩了，这样就使得多个阶段可以并发执⾏，从⽽提升效率。