Mysql总结

推荐你阅读
互联网大厂万字专题总结
Redis总结
JUC总结
操作系统总结
JVM总结
Mysql总结
互联网大厂常考知识点
什么是系统调用
CPU底层锁指令有哪些
AQS与ReentrantLock原理
旁路策略缓存一致性
Java通配符看这一篇就够

基础篇

Mysql 的一条语句是如何执行的

Server 层是上层，负责建立连接、分析和执行 SQL。底层存储引擎层负责数据的存储和提取，支持 InnoDB（默认存储引擎）、MyISAM、Memory 等多个存储引擎。

1. 客户端与 server 层建立 tcp 连接并校验用户名密码
2. 对传过来的 sql 进行解析，词法分析识别关键词，语法分析构建语法树。

3. 预处理 sql，检查表和字段是否存在，将 select *中的*扩展为全部字段
4. 确定 SQL 语句的执行计划，比如当有多个索引的时候，基于查询成本的考虑来决定选择使用哪个索引，我们可以在查询语句最前面加个 explain 命令来输出本条 sql 的执行计划。

5. 执行 sql，下面举几个例子

• 主键等值：把条件交给存储引擎，存储引擎直接返回一条
• 全表扫描：存储引擎返回第一条记录，执行器判断并返回客户端，存储引擎返回下一条记录

Mysql 一行记录是如何存储的

数据存储在哪个文件

每一个数据库有一个文件夹，当我们给这个数据库创建一个 t_order 表的时候，文件夹中会存在三个文件

• db.opt：用来存储当前数据库的默认字符集和字符校验规则。
• t_order.frm：存放表结构
• t_order.ibd：存放表数据

表空间文件的结构是怎么样的

InnoDB 存储引擎的逻辑存储结构大致如下图：

• 页是 InnoDB 存储引擎磁盘管理的最小单元，页的大小固定 16KB。数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。
• 区可以使得 B+树中每层双向链表相邻的页的物理位置也相邻，这样可以使用顺序 IO 提高速度

InnoDB 行格式 COMPACT

• 变长字段长度列表：用来存放当前记录所有变长字段的长度
• NULL 值列表：存放所有可以为 NULL 的字段是否为 NULL，每个字段用 1bit 表示
• row_id：隐式主键，如果建表时存在主键或者不存在主键但是存在非空唯一索引，这个字段就没有用了，否则会设置隐式自增主键
• trx_id：事务 id，表明这条记录是由哪个事务生成的
• roll_pointer：指向 undolog 上一个版本的指针

varchar(n) 中 n 最大取值为多少

Mysql 一行记录最大长度为 65535 字节，所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL 值列表所占用的字节数 <= 65535。

行溢出后，MySQL 是怎么处理的

如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。

索引篇

索引的定义就是帮助存储引擎快速查找数据的一种数据结构，形象的说就是索引是数据的目录。
所谓的存储引擎，说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。

从数据页的角度看 B+ 树

InnoDB 的数据是按「数据页」为单位来读写的，默认数据页大小为 16 KB。每个数据页之间通过双向链表的形式组织起来，物理上不连续，但是逻辑上连续。

一个数据页里面是什么样的

一个数据页里面存放了多条记录，数据页中的记录按照「主键」顺序组成单向链表。当按照主键进行查询的时候，由于是单链表的结构所以只能遍历整个链表。为提高查询效率引入了分组的方式，页目录中的每个槽都索引了分组的最后一条记录，因此每次查询时可以先用二分法查到相应的槽，然后再去槽对应的分组中遍历得到记录。

B+ 树是如何进行查询的

InnoDB 里的 B+ 树中的每个节点都是一个数据页，非叶子节点的数据页只存放索引不存放记录数据，叶子节点的数据页存放的是记录数据。
比如说我们要查找主键为 6 的记录，首先在根叶子节点二分法定位槽，然后遍历链表找到大于等于 1 且小于 7 对应页 30。在页 30 非叶子节点二分法定位槽，然后遍历链表找到大于等于 5 对应页 16。在页 16 叶子节点二分法定位槽，然后遍历链表找到主键为 6 的记录。

聚簇索引和二级索引

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

• 聚簇索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚簇索引的叶子节点；
• 二级索引的叶子节点存放的是主键值，而不是实际数据。

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

回表和索引覆盖

如果某个查询语句使用了二级索引，但是查询的数据不是主键值，这时在二级索引找到主键值后，需要去聚簇索引中获得数据行，这个过程就叫作「回表」，也就是说要查两个 B+ 树才能查到数据。不过，当查询的数据是主键值时，因为只在二级索引就能查询到，不用再去聚簇索引查，这个过程就叫作「索引覆盖」，也就是只需要查一个 B+ 树就能找到数据。

为什么 MySQL 采用 B+ 树作为索引

磁盘读写的最小单位是扇区，扇区的大小只有 512B 大小，操作系统一次会读写多个扇区，所以操作系统的最小读写单位是块（Block）。Linux 中的块大小为 4KB，也就是一次磁盘 I/O 操作会直接读写 8 个扇区。
MySQL 的索引结构应该满足以下两点：

• 能在尽可能少的磁盘 I/O 次数下查找记录
• 既能高效查找某条记录，也能高效查找某个范围内的记录

可能的索引结构

首先我们需要明确，数据页在存储上并不是连续存储的。

• 二分法：把每个数据页地址组成一个连续存储的数组，并按照主键 id 从小到大排列，然后就可以二分查找了。二分法查找单条记录或者范围记录的时间复杂度都是 O(log(n))，远差于 B+树。
• 平衡二叉搜索树（AVL）：把每个数据页地址组成一个 AVL 树，AVL 树的查找、插入、删除时间复杂度都是 O(log(n))，而二分法的插入、删除时间复杂度是 O(n)。但 AVL 树无法高效范围查找。
• B 树：相当于平衡多叉搜索树，相比于 AVL 树查询效率得到提升，IO 次数更少，但仍然无法高效范围查找（只能遍历树）。
• B+树：和 B 树的区别在于，记录只存在叶子结点，并且每层节点之间由双向链表连接。相比于 B 树，因为非叶子节点不存记录数据，所以非叶子节点可以存更多的索引，因此整体结构更加扁平，查询的磁盘 IO 次数更少，且支持高效范围查找。

MySQL 单表不要超过 2000W 行

MySQL 单表不要超过多少行这个问题和表结构有哪些字段以及字段类型有关。核心问题是希望聚簇索引的 B+树层数不要超过 3 层，否则太高的磁盘 IO 次数会导致查询效率降低。
我们假设：

• 非叶子节点内指向其他页的数量为 x
• 叶子节点内能容纳的数据行数为 y
• B+ 数的层数为 z

则行数total=x的z-1次方乘y，一个数据页大概能存 1280 个索引，我们假设一行数据占 1KB 大小，那么一个数据页大概能存 15 行记录。算下来，当 B+树层数为 3 时最多能存约 2450w 行记录。

索引失效有哪些

• 对索引使用左或者左右模糊匹配：也就是 like %xx% 或者 like %xx% 这两种方式
• 对索引使用函数或对索引进行表达式计算：如果查询条件中对索引字段使用函数，就会导致索引失效。B+树是按照索引字段原来的值构建的，经过函数映射后当然不再适用。
• 对索引隐式类型转换：比如下面的例子，phone 是 varchar 类型，自动转换为数字

select * from t_user where phone = 1300000001;

• 联合索引非最左匹配：单字段索引在排序时直接按单字段排序即可，而联合索引在排序时先按第一个字段排序，第一个字段相同时再按第二个字段排序，以此类推。因此联合索引要能正确使用需要遵循最左匹配原则，以下情况会走联合索引：

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；

以下情况会全表扫描：

where b=2；
where c=3；
where b=2 and c=3；

以下情况会部分走联合索引的索引下推：a = 1会走联合索引，在查到一条记录后不会回表查整行记录再判断c=3，而是先判断 c，如果c=3才会回表查整行数据并返回。

where a = 1 and c = 3；

• WHERE 子句中的 OR：id 字段是主键，age 字段没有索引。那么下面这条 sql 走全表扫描，这是因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。

select * from t_user where id = 1 or age = 18;

count(*) 和 count(1) 有什么区别

server 层会循环向 InnoDB 读取一条记录，如果 count 函数指定的参数不为 NULL，那么就会将变量 count 加 1，直到符合查询的全部记录被读完，就退出循环。最后将 count 变量的值发送给客户端。

• count(主键字段) ：全扫描聚簇索引或二级索引，判断主键字段是否为 NULL，count 加一
• count(1)：全扫描聚簇索引或二级索引，count 加一
• count(*)：等价于 count(0)
• count(字段)：如果字段是普通字段则全表扫描，如果是索引字段则全扫描索引

全扫描索引和全表扫描有什么区别

全扫描二级索引可以得到该索引字段的所有值，全扫描聚簇索引可以得到主键id的所有值，全表扫描也是扫描聚簇索引但是会把所有叶子节点的数据页都读出来，可以得到所有记录的全字段数据。

为什么要通过遍历的方式来计数

一方面在有 where 条件限制的时候，肯定要遍历。另一方面在没有 where 条件限制的时候，因为 innodb 存储引擎有着事务隔离的 MVCC 机制，因此不能用一个量来衡量整个表的行数，也需要遍历。

事务篇

事务的 ACID 特性

事务是由 MySQL 的引擎来实现的，我们常见的 InnoDB 引擎它是支持事务的。

• 原子性（Atomicity）：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。
• 一致性（Consistency）：一致性（Consistency）：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。
• 隔离性（Isolation）：数据库允许多个并发事务同时读写数据，但每个事务内要满足数据一致性。
• 持久性（Durability）：事务结束后对数据的修改就是永久的，即便 mysql 进程崩溃重启或者断电重启也不会丢失。

InnoDB 引擎通过什么技术来保证事务的这四个特性的呢？

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

脏读、不可重复读、幻读

• 脏读：一个事务读到了另外一个未提交事务的数据，未提交事务回滚后已经读到的就变成了脏数据。插入、删除和修改都可以导致脏读。

• 不可重复读：在一个事务内多次读取同一个数据，如果出现同一行记录前后两次读到的数据不一样的情况，就意味着发生了「不可重复读」现象。只有修改可以导致不可重复读，插入和删除不会导致不可重复读。

• 幻读：在一个事务内多次查询符合某个查询条件的所有记录，如果出现前后两次查询的结果条目并不是一一对应的，就意味着发生了「幻读」现象。插入、删除和修改都可以导致幻读，其中修改是修改一些字段使其不满足查询条件。

隔离级别

• 读未提交：指一个事务还没提交时，它做的变更就能被其他事务看到。
• 读已提交：指一个事务提交之后，它做的变更才能被其他事务看到
• 可重复读：指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别
• 串行化：会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行，因为只有事务结束时才会释放所有的读锁/写锁

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象

四种隔离级别是如何实现的

• 「读未提交」：直接读取最新的数据就好了
• 「串行化」：每行记录都要加读写锁
• 「读已提交」和「可重复读」：它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同。「读已提交」是在查询前生成一个 Read View，「可重复读」是在开启事务时生成一个 Read View。

并发版本控制协议 MVCC

注：Mysql是事务提交时才会生成事务id的
Read View 有四个重要的字段：

• creator_trx_id：指的是创建该 Read View 的事务的事务 id
• m_ids：指的是在创建 Read View 时，已开启但还未提交的事务id
• min_trx_id：m_ids 的最小值
• max_trx_id ：创建 Read View 时当前数据库中应该给下一个事务的 id 值

聚簇索引记录中的两个隐藏列：

• trx_id：当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里
• roll_pointer：指向当前记录上一个版本的 undo log，每次对聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo log 中

在创建 Read View 后，我们可以将记录中的 trx_id 划分这三种情况：

一个事务去访问记录的时候，先看最新记录数据，如果最新记录数据对当前事务不可见（即属于ReadView中的已启动但未提交的事务或还没有开始的事务），则沿着 undo log 链向下追溯，直到找到当前事务可见的版本记录。
在事务中，自己的更新记录总是可见的，生成 Read View 之前已提交事务的记录数据是可见的，生成 Read View 之前已开启但未提交或未开启的事务的记录数据都是不可见的。

• 读已提交是在查询前生成一个 Read View，所以读已提交隔离级别下的事务中，每次都读的是当前时刻已提交的最新记录数据
• 可重复读是在开启事务时生成一个 Read View，所以可重复读隔离级别下的事务中，相当于在事务开始时生成了一份数据快照，并且只有当前事务可以对它进行修改

MySQL 可重复读隔离级别，完全解决幻读了吗？

快照读

借助于 MVCC、Read View、undo log 这一套机制，快照读大部分情况下可以避免幻读，但也有一些特殊情况。

第一个发生幻读现象的场景

第二个发生幻读现象的场景

• T1 时刻：事务 A 先执行「快照读语句」：select * from t_test where id > 100 得到了 3 条记录。
• T2 时刻：事务 B 往插入一个 id= 200 的记录并提交；
• T3 时刻：事务 A 再执行「当前读语句」 select * from t_test where id > 100 for update 就会得到 4 条记录，此时也发生了幻读现象。

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select … for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

当前读

当前读读的是所有记录已提交的最新数据。Innodb 引擎借助于间隙锁、记录锁和 next key 锁保证了当前读不会出现幻读问题。

锁篇

备份数据库

全局锁

全局锁会锁住整个数据库变成只读状态，任何对数据的增删改和对表结构的修改都会阻塞，用全局锁备份数据库会导致业务停滞。
要使用全局锁，则要执行这条命令：

flush tables with read lock

如果要释放全局锁，则要执行这条命令：

unlock tables

MVCC 可重复读

在可重复读隔离级别下，先开启一个事务，再去 select 查询并备份，这样也能保证数据一致性，并且业务可以正常增删改查。

表级锁

表锁

果我们想对学生表（t_student）加表锁，可以使用下面的命令：

//表级别的共享锁，也就是读锁；
lock tables t_student read;

//表级别的独占锁，也就是写锁；
lock tables t_stuent write;

要释放表锁，可以使用下面这条命令，会释放当前会话的所有表锁：

unlock tables

元数据锁

我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

• 对一张表进行 CRUD 操作时，加的是 MDL 读锁
• 对一张表做结构变更操作的时候，加的是 MDL 写锁

MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

意向锁

• 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」
• 在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」

意向共享锁、意向独占锁、共享表锁、独占表锁之间构成读写锁关系（意向锁之间不会发生冲突），这样就能保证比如行级别的独占记录锁和独占表锁之间会互斥。而如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

行级锁

记录锁

记录锁针对于一个行记录，有 S 锁和 X 锁之分，S 锁是共享锁， X 锁是独占锁。

间隙锁

只存在于可重复读隔离级别。X 型间隙锁和 S 型间隙锁没有区别，且间隙锁与间隙锁之间是兼容的，在间隙锁区间内的插入意向锁要等待间隙锁释放。

Next-Key 锁

Next-Key 锁称为临键锁，是间隙锁和记录锁的组合

插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁，如果有的话会生成一个插入意向锁，并设置状态为等待，直到事务结束间隙锁释放。

各种加锁情况（在事务中）

下面这条语句会在 id = 1 上加一个插入意向锁。具体过程如下：首先走聚簇索引查看是否主键冲突，然后加一个插入意向锁并根据间隙锁情况判断该插入意向锁是否应该等待，插入新的行数据到聚簇索引的 B+树相应的数据页中，并设置行记录的事务 id 为当前事务。

insert into table values(x1,x2) where id = 1

下面这两条语句会在 id = 1 上加一个 X 型记录锁。更新操作的具体过程如下：首先走聚簇索引查看是否有这条记录，然后加一个 X 型记录锁并判断该记录锁是否应该等待（该行记录是否已经存在 X 型或 S 型记录锁），将更新的数据放入 undo log 链的首部，并设置行记录的事务 id 为当前事务。直到事务结束时释放 X 型记录锁。
需要注意的是，update和delete相当于当前读，具体 sql 会上哪些锁直接参考当前读的各种情况即可，比如下面两条其实对应的就是唯一索引等值查询且记录存在的情况，加的是 X 型记录锁。

update table .... where id = 1;

delete from table where id = 1;

下面这条语句是快照读，快照读是无锁的

select * from table where ...

当前读的各种情况

当前读会加哪些锁取决于加哪些锁就可以避免幻读

唯一索引等值查询

当查询记录存在时，加的是 X 型记录锁，这样直到当前事务结束，其他事务都不可以 update 和 delete 这条记录，因为 update 和 delete 都会上 X 型记录锁。
当查询记录不存在时，会在比查询记录更大的下一条记录上加间隙锁（在某条记录上加间隙锁，间隙锁的作用范围就是这条记录到上一条记录），这样直到当前事务结束，其他事务都不可以 insert 查询记录，因为 insert 会产生插入意向锁。

唯一索引范围查询

参见小林 coding

非唯一索引等值查询

参见小林 coding

非唯一索引范围查询

参见小林 coding

全表扫描查询

如果当前读、update 或 delete，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。
因此，在线上在执行 update、delete、select … for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

Mysql中的死锁

下面是一个死锁的例子

CREATE TABLE `t_order` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_no` int DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_order` (`order_no`) USING BTREE
) ENGINE=InnoDB ;

其中 id 是主键，order_no 是非唯一索引

我们要插入订单，为了保证消息幂等性，需要先开启事务然后再做一个当前读，如果没有这条订单，那么就插入然后结束事务，如果已有这条订单，那么就直接结束事务。

• 为什么不选择不开启事务时读呢？因为不开启事务时读是读的最新数据，因为没有锁，所以并不满足幂等性。
• 为什么不选择开启事务后的快照读呢？一方面快照读无锁且读的不是最新数据，所以不满足幂等性。

上面这种方式可能会出现死锁，事务 A 先当前读没读到并加一个（1006, 正无穷）的间隙锁，然后事务 B 再当前读没读到并加一个（1006, 正无穷）的间隙锁，然后事务 A insert 主键 id 为 1007 的记录，事务 A 的插入意向锁和事务 B 的间隙锁冲突，事务 A 阻塞并等待事务 B 的间隙锁释放，然后事务 B insert 主键 id 为 1007 的记录，事务 B 的插入意向锁和事务 A 的间隙锁冲突，事务 B 阻塞并等待事务 A 的间隙锁释放…

如何避免死锁

打破循环等待条件的角度

• 设置事务等待锁的超时时间：当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。
• 开启主动死锁检测：主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。

业务的角度
设置 order_no 字段为唯一索引，每次直接 insert 就好，但要记得捕获异常。

日志篇

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

undo log

我们在执行一条增删改语句的时候，虽然没有输入 begin 开启事务和 commit 提交事务，但是 MySQL 会隐式开启事务来执行增删改语句，执行完就自动提交事务，这样就保证了执行完增删改语句后，我们可以及时在数据库表看到增删改的结果了。
每当 InnoDB 引擎对一条记录进行操作（修改、删除、新增）时，要把回滚时需要的信息都记录到 undo log 里，比如：

• 在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；
• 在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了；
• 在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了。

undo log 版本链如下图：

Buffer Pool

Buffer Pool 是 Innodb 存储引擎设计的一个缓冲池，用于提高数据页读写效率

• 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
• 当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘 I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。

Buffer Pool 是一块连续的内存，存放着一个个大小为 16KB 的缓存页，其中我们主要关注数据页和 undo 页

redo log

InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging）技术。
redo log 用于解决断电导致 Buffer Pool 中还未落盘的脏页数据丢失问题，当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来。

redo log 是物理日志，记录了某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了 AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。
在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新的状态。
具体来说，事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务保证事务的原子性，Buffer Pool 脏数据和redo log buffer数据直接丢失即可。事务提交之后发生了崩溃，则redo log已全部持久化，重启后通过 redo log 恢复 Buffer Pool 中的脏数据。
所以有了 redo log，再通过 WAL 技术，InnoDB 就可以保证即使数据库发生异常重启，之前已提交的记录都不会丢失，即保证了事务四大特性中的持久性。

redo log 要写到磁盘，数据也要写磁盘，为什么要多此一举？

写入 redo log 的方式使用了追加操作， 所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。磁盘的「顺序写 」比「随机写」 高效的多。

redo log 刷盘

redo log 在刚产生时会存放于 redo log buffer 内存缓存中，一般会在每隔一秒以及事务提交时刷入磁盘。

redo log 文件写满了怎么办

一共有两个 redo log 文件，它们构成一个环形以循环写的方式工作，write pos 表示 redo log 当前记录写到的位置，用 checkpoint 表示当前要擦除的位置，如果 buffer pool 中的脏页已经刷新到了磁盘，那么脏页对应的 redo log 就可以擦除。
如果 write pos 追上了 checkpoint，就意味着 redo log 文件满了，此时 Mysql 会阻塞并将 buffer pool 中的脏页不断刷新到磁盘，然后擦除相应的 redo log。

binlog

MySQL 在完成一条增删改操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事务执行过程中产生的所有 binlog 统一写入 binlog 文件。binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作。

binlog 的 3 种格式类型

• STATEMENT：每一条增删改的 SQL 都会被记录到 binlog 中，相当于记录了所有的逻辑操作，主从复制中 slave 端再根据 SQL 语句重现。但如果用了 uuid 或者 now 这些函数，则主库从库执行的结果不一致。
• ROW：记录每行数据的变化结果，不会出现 STATEMENT 下动态函数的问题，缺点是文件比较大。
• MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式。

binlog 和 redo log 的不同点

binlog 在 server 层实现，redo log 在 innodb 存储引擎实现。binlog 用来做备份和主从复制，redo log 用来做 buffer pool 脏数据页恢复。binlog 是追加写，写满一个文件就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
redo log 是循环写，日志空间大小是固定。

主从复制

主从复制过程如下图，可以看到无论是主库的 binlog 日志发送还是从库的中继日志回放都是异步过程。

有了主从复制机制，主库从库之间就存在了延迟数据同步。客户端可以在写数据时只写主库，在读数据时只读从库，这样每台数据库的压力会变小，并且即使写请求会锁表或者锁记录，也不会影响读请求的执行。但缺点是延迟问题和单点问题（一旦主库宕机就会出现数据丢失）。

主从复制的其他模型

• 同步复制：性能很低、可用性很差
• 异步复制：单点问题
• 半同步复制：只要一部分从库返回复制成功的响应，就成功提交事务，保证即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

binlog 刷盘

事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。每个线程有自己 binlog cache，但是最终都写到同一个 binlog 文件。

一条 update 语句的执行过程

UPDATE t_user SET name = 'xiaolin' WHERE id = 1;

1. 优化器分析出成本最小的执行计划。
2. 通过聚簇索引搜索 id = 1 这一行记录，如果 id=1 这一行所在的数据页不在 buffer pool 中，就将数据页从磁盘读入到 buffer pool。
3. 唯一索引等值查询且数据存在，上 X 型记录锁。
4. 开启事务，生成一条存放该行旧值的 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面并将该页面标记为脏数据，生成一条用于恢复脏 undo 页的 redo log。
5. 更新 buffer pool 中对应数据页并标记为脏页，生成一条用于恢复脏数据页的 redo log。脏页会通过 WAL 技术由后台线程选择一个合适的时机写入到磁盘。
6. 记录该语句对应的 binlog，并保存到 binlog cache。
7. 提交事务，释放锁，两阶段提交（binlog 刷入磁盘、redo log 刷入磁盘）。

两阶段提交

两阶段提交本质是通过断电重启后的检测并自动回滚机制，实现了redo log和binlog两者持久化的原子性。

为什么需要两阶段提交

事务提交后，redo log 和 binlog 都要写入磁盘，但是这两个是独立的逻辑，可能出现半成功的状态，这样就造成两份日志之间的逻辑不一致。

• 如果 redo log 写入磁盘后断电重启，binlog 没来得及写入：主库是新值，从库是旧值。
• 如果 binlog 写入磁盘后断电重启，redo log 没来得及写入：从库是新值，主库是旧值。

两阶段提交过程

从图中可看出，事务的提交分为 prepare 和 commit 两个阶段：

• prepare 阶段：设置 redo log 的 XID，设置 redo log 的事务状态为 prepare，将 redo log 写入磁盘。
• commit 阶段：设置 binlog 的 XID，将 binlog 写入磁盘，最后调用引擎的提交事务接口，将磁盘中 redo log 状态设置为 commit。

不管是时刻 A（redo log 已经写入磁盘， binlog 还没写入磁盘），还是时刻 B （redo log 和 binlog 都已经写入磁盘，还没写入 commit 标识）崩溃，此时的 redo log 都处于 prepare 状态。
在 MySQL 重启后会按顺序扫描 redo log 文件，碰到处于 prepare 状态的 redo log，就拿着 redo log 中的 XID 去 binlog 查看是否存在此 XID：

• 如果 binlog 中没有此 XID，说明 redo log 完成刷盘，但是 binlog 还没有刷盘，则回滚事务。
• 如果 binlog 中有此 XID，说明 redolog 和 binlog 都已经完成了刷盘，则提交事务。