一、事务基础知识

1、数据库事务概述

1.1 基本概念

SHOW ENGINES`命令来查看当前 MySQL 支持的存储引擎都有哪些，以及这些存储引擎是否支持事务

**事务：**一组逻辑操作单元，使数据从一种状态变换到另一种状态。
**事务处理的原则：**保证所有事务都作为 一个工作单元 来执行，即使出现了故障，都不能改变这种执行方 式。当在一个事务中执行多个操作时，要么所有的事务都被提交( commit )，那么这些修改就 永久 地保 存下来；要么数据库管理系统将 放弃 所作的所有 修改 ，整个事务回滚( rollback )到最初状态。

1.2 事物的ACID特性

• 原子性（atomicity）

原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。即要么转账成功，要么转账失败，是不存在中间的状态。如果无法保证原子性会怎么样？就会出现数据不一致的情形，A账户减去100元，而B账户增加100元操作失败，系统将无故丢失100元。

• 一致性（consistency）

根据定义，一致性是指事务执行前后，数据从一个 合法性状态 变换到另外一个 合法性状态 。这种状态是 语义上 的而不是语法上的，跟具体的业务有关。那什么是合法的数据状态呢？满足 预定的约束 的状态就叫做合法的状态。通俗一点，这状态是由你自己来定义的（比如满足现实世界中的约束）。满足这个状态，数据就是一致的，不满足这个状态，数据就 是不一致的！如果事务中的某个操作失败了，系统就会自动撤销当前正在执行的事务，返回到事务操作 之前的状态。

**举例1：**A账户有200元，转账300元出去，此时A账户余额为-100元。你自然就发现此时数据是不一致的，为什么呢？因为你定义了一个状态，余额这列必须>=0。

**举例2：**A账户有200元，转账50元给B账户，A账户的钱扣了，但是B账户因为各种意外，余额并没有增加。你也知道此时的数据是不一致的，为什么呢？因为你定义了一个状态，要求A+B的总余额必须不变。

**举例3：**在数据表中我们将姓名字段设置为唯一性约束，这时当事务进行提交或者事务发生回滚的时候，如果数据表的姓名不唯一，就破坏了事物的一致性要求。

• 隔离型（isolation）

事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰。

• 持久性（durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是 永久性的 ，接下来的其他操作和数据库 故障不应该对其有任何影响。持久性是通过 事务日志 来保证的。日志包括了 重做日志 和 回滚日志 。当我们通过事务对数据进行修改 的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。这样做 的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执 行，从而使事务具有持久性。

1.3 事务的状态

• 活动的（active）

  事务对应的数据库操作正在执行过程中时，我们就说该事务处在 活动的 状态。

• 部分提交的（partially committed）

  当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并 没有刷新到磁盘 时，我们就说该事务处在 部分提交的 状态。

• 失败的（failed）

  当事务处在 活动的 或者 部分提交的 状态时，可能遇到了某些错误（数据库自身的错误、操作系统 错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，我们就说该事务处在 失 败的 状态。

• 中止的（aborted）

  如果事务执行了一部分而变为 失败的 状态，那么就需要把已经修改的事务中的操作还原到事务执 行前的状态。换句话说，就是要撤销失败事务对当前数据库造成的影响。我们把这个撤销的过程称之为 回滚 。当 回滚 操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，我们就说该事 务处在了 中止的 状态。

• 提交的（committed）

  当一个处在 部分提交的 状态的事务将修改过的数据都 同步到磁盘 上之后，我们就可以说该事务处在了 提交的 状态。

2、如何使用事务

使用事务有两种方式，分别为 显式事务 和 隐式事务

2.1 显式事务

-- 步骤1：START TRANSACTION 或者 BEGIN ，作用是显式开启一个事务
BEGIN;
START TRANSACTION;
-- START TRANSACTION`语句相较于BEGIN特别之处在于，后边能跟随几个 修饰符
-- READ ONLY：标识当前事务是一个 只读事务，也就是属于该事务的数据库操作只能读取数据，而不能修改数据
-- READ WRITE ：标识当前事务是一个 读写事务 ，也就是属于该事务的数据库操作既可以读取数据， 也可以修改数据
-- WITH CONSISTENT SNAPSHOT ：启动一致性读。
START TRANSACTION READ ONLY; -- 开启一个只读事务
START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT -- 开启只读事务和一致性读
START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT -- 开启读写事务和一致性读

• READ ONLY和READ WRITE是用来设置所谓的事物访问模式的，就是以只读还是读写的方式来访问数据库中的数据，一个事务的访问模式不能同时即设置为只读的也设置为读写的，所以不能同时把READ ONLY和READ WRITE放到START TRANSACTION语句后边。
• 如果我们不显式指定事务的访问模式，那么该事务的访问模式就是读写模式

-- 步骤2:一系列事务中的操作（主要是DML，不含DDL）
-- 步骤3：提交事务 或 中止事务（即回滚事务）
-- 提交事务。当提交事务后，对数据库的修改是永久性的
COMMIT;
-- 将事务回滚到某个保存点
ROLLBACK TO [SAVEPOINT]-- 其中关于SAVEPOINT相关操作有
-- 在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。
SAVEPOINT 保存点名称;
-- 删除某个保存点
RELEASE SAVEPOINT 保存点名称;

2.2 隐式事务

MySQL中有一个系统变量 autocommit ：SHOW VARIABLES LIKE 'autocommit';当然，如果我们想关闭这种 自动提交 的功能，可以使用下边两种方法之一：

• 显式的的使用 START TRANSACTION 或者 BEGIN 语句开启一个事务。这样在本次事务提交或者回滚前会暂时关闭掉自动提交的功能。
• 把系统变量 autocommit 的值设置为 OFF ，就像这样：

SET autocommit = OFF;
SET autocommit = 0;

2.3 隐式提交数据的情况

• 数据定义语言（Data definition language，缩写为：DDL）。数据库对象，指的就是数据库、表、视图、存储过程等结构。当我们CREATE、ALTER、DROP等语句去修改数据库对象时，就会隐式的提交前边语句所属于的事物。
• 隐式使用或修改mysql数据库中的表。当我们使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD等语句时也会隐式的提交前边语句所属于的事务。
• 事务控制或关于锁定的语句
  • 当我们在一个事务还没提交或者回滚时就又使用 START TRANSACTION 或者 BEGIN 语句开启了另一个事务时，会隐式的提交上一个事务。
  • 当前的 autocommit 系统变量的值为 OFF ，我们手动把它调为 ON 时，也会 隐式的提交前边语句所属的事务。
  • 使用 LOCK TABLES 、 UNLOCK TABLES 等关于锁定的语句也会 隐式的提交 前边语句所属的事务。
• 加载数据的语句。使用LOAD DATA语句来批量往数据库中导入数据时，也会隐式的提交前边语句所属的事务。
• 关于MySQL复制的一些语句。使用START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等语句会隐式的提交前边语句所属的事务
• 其他的一些语句。使用ANALYZE TABLE、CACHE INDEX、CAECK TABLE、FLUSH、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET等语句也会隐式的提交前边语句所属的事务。

2.4 使用举例

BEGIN;
UPDATE account SET balance = balance - 100 WHERE NAME = '张三';
UPDATE account SET balance = balance - 100 WHERE NAME = '张三';
SAVEPOINT s1; # 设置保存点
UPDATE account SET balance = balance + 1 WHERE NAME = '张三';
ROLLBACK TO s1; # 回滚到保存点

3、事务隔离级别

MySQL是一个 客户端／服务器 架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每 个客户端与服务器连接上之后，就可以称为一个会话（ Session ）。每个客户端都可以在自己的会话中 向服务器发出请求语句，一个请求语句可能是某个事务的一部分，也就是对于服务器来说可能同时处理多个事务。事务有 隔离性 的特性，理论上在某个事务 对某个数据进行访问 时，其他事务应该进行排队 ，当该事务提交之后，其他事务才可以继续访问这个数据。但是这样对 性能影响太大 ，我们既想保持事务的隔离性，又想让服务器在处理访问同一数据的多个事务时 性能尽量高些 ，那就看二者如何权衡取 舍了

3.1 数据准备

CREATE TABLE student (studentno INT,name VARCHAR(20),class varchar(20),PRIMARY KEY (studentno)
) Engine=InnoDB CHARSET=utf8;INSERT INTO student VALUES(1, '小谷', '1班');
select * from student;

3.2 数据并发问题

1、脏写，对于两个事务 Session A、Session B，如果事务Session A 修改了 另一个 未提交 事务Session B 修改过 的数据，那就意味着发生了 脏写

**2、脏读，**对于两个事务 Session A、Session B，Session A 读取 了已经被 Session B 更新 但还 没有被提交 的字段。 之后若 Session B 回滚 ，Session A 读取的内容就是 临时且无效 的

**3、不可重复读（ Non-Repeatable Read ），**对于两个事务Session A、Session B，Session A 读取了一个字段，然后 Session B 更新了该字段。 之后 Session A 再次读取 同一个字段， 值就不同 了。

4、**幻读（ Phantom ），**对于两个事务Session A、Session B, Session A 从一个表中 读取 了一个字段, 然后 Session B 在该表中 插 入 了一些新的行。 之后, 如果 Session A 再次读取 同一个表, 就会多出几行。

3.3 SQL中的四种隔离级别

上面介绍了几种并发事务执行过程中可能遇到的一些问题，这些问题有轻重缓急之分，我们给这些问题 按照严重性来排一下序：脏写 > 脏读 > 不可重复读 > 幻读

SQL标准 中设立了4个 隔离级别

• READ UNCOMMITTED ：读未提交，在该隔离级别，所有事务都可以看到其他未提交事务的执行结 果。不能避免脏读、不可重复读、幻读。
• READ COMMITTED ：读已提交，它满足了隔离的简单定义：一个事务只能看见已经提交事务所做 的改变。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。可以避免脏读，但不可 重复读、幻读问题仍然存在。
• REPEATABLE READ ：可重复读，事务A在读到一条数据之后，此时事务B对该数据进行了修改并提 交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读，但幻读问题仍 然存在。这是MySQL的默认隔离级别。
• SERIALIZABLE ：可串行化，确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止 其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免，但性能十分低下。能避 免脏读、不可重复读和幻读。

脏写怎么没涉及到？因为脏写这个问题太严重了，不论是哪种隔离级别，都不允许脏写的情况发生。不同的隔离级别有不同的现象，并有不同的锁和并发机制，隔离级别越高，数据库的并发性能就越差，4 种事务隔离级别与并发性能的关系如下：

3.4 MySQL支持的四种隔离级别

不同的数据库厂商对sQL标准中规定的四种隔离级别支持不一样。比如，Oracle就只支持 READ COMMITTED（默认隔离级别）和SERIALIZABLE隔离级别。MySQL虽然支持4种隔离级别，但与SQL标准中所规定的各级隔离级别允许发生的问题却有些出入，MySQL在REPEATABLE READ隔离级别下，是可以禁止幻读问题的发生的

-- 查看隔离级别，MySQL 5.7.20的版本之前
SHOW VARIABLES LIKE 'tx_isolation';
--  MySQL 5.7.20版本之后，引入transaction_isolation来替换tx_isolation
-- 查看隔离级别，MySQL 5.7.20的版本及之后
SHOW VARIABLES LIKE 'transaction_isolation';
-- 或者不同MySQL版本中都可以使用的：
SELECT @@transaction_isolation;

对于事物隔离级别的设置

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 隔离级别;
-- 其中，隔离级别格式：
-- READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ/SERIALIZABLE-- 或者
SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '隔离级别'
-- READ-UNCOMMITTED/READ-COMMITTED/REPEATABLE-READ/SERIALIZABLE-- 使用 GLOBAL 关键字（在全局范围影响）
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET GLOBAL TRANSACTION_ISOLATION = 'SERIALIZABLE';
-- 当前已经存在的会话无效
-- 只对执行完该语句之后产生的会话起作用-- 使用 SESSION 关键字（在会话范围影响）
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET SESSION TRANSACTION_ISOLATION = 'SERIALIZABLE';
-- 对当前会话的所有后续的事务有效
-- 如果在事务之间执行，则对后续的事务有效
-- 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务

如果在服务器启动时想改变事务的默认隔离级别，可以修改启动参数transaction_isolation的值。比如，在启动服务器时指定了transaction_isolation=SERIALIZABLE，那么事务的默认隔离界别就从原来的REPEATABLE-READ变成了SERIALIZABLE

4、事务的常见分类

• 扁平事务（Flat Transactions）
• 带有保存点的扁平事务（Flat Transactions with Savepoints）
• 链事务（Chained Transactions）
• 嵌套事务（Nested Transactions）
• 分布式事务（Distributed Transactions）

二、MySQL事务日志

1、概述

事务有4种特性：原子性、一致性、隔离性和持久性。那么事务的四种特性到底是基于什么机制实现呢？

• 事务的隔离性由 锁机制 实现。
• 而事务的原子性、一致性和持久性由事务的 redo 日志和undo 日志来保证。
  • REDO LOG 称为 重做日志，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。
  • UNDO LOG 称为 回滚日志 ，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。

有的DBA或许会认为 UNDO 是 REDO 的逆过程，其实不然。REDO 和 UNDO都可以视为是一种 恢复操作，但是：

• redo log: 是存储引擎层 (innodb) 生成的日志，记录的是"物理级别"上的页修改操作，比如页号xxx，偏移量yyy写入了’zzz’数据。主要为了保证数据的可靠性。
• undo log: 是存储引擎层 (innodb) 生成的日志，记录的是 逻辑操作 日志，比如对某一行数据进行了INSERT语句操作，那么undo log就记录一条与之相反的DELETE操作。主要用于 事务的回滚 (undo log 记录的是每个修改操作的 逆操作) 和 一致性非锁定读 (undo log 回滚行记录到某种特定的版本——MVCC，即多版本并发控制)。

2、redo日志

InnoDB存储引擎是以页为单位来管理存储空间的。在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。所有的变更都必须先更新缓冲池中的数据，然后缓冲池中的脏页会以一定的频率被刷入磁盘 (checkPoint机制)，通过缓冲池来优化CPU和磁盘之间的鸿沟，这样就可以保证整体的性能不会下降太快

2.1 为什么需要REDO日志

一方面，缓冲池可以帮助我们消除CPU和磁盘之间的鸿沟，checkpoint机制可以保证数据的最终落盘，然 而由于checkpoint 并不是每次变更的时候就触发 的，而是master线程隔一段时间去处理的。所以最坏的情 况就是事务提交后，刚写完缓冲池，数据库宕机了，那么这段数据就是丢失的，无法恢复。另一方面，事务包含 持久性 的特性，就是说对于一个已经提交的事务，在事务提交后即使系统发生了崩溃，这个事务对数据库中所做的更改也不能丢失。

那么如何保证这个持久性呢？ 一个简单的做法 ：在事务提交完成之前把该事务所修改的所有页面都刷新 到磁盘，但是这个简单粗暴的做法有些问题:

• 修改量与刷新磁盘工作量严重不成比例
  有时候我们仅仅修改了某个页面中的一个字节，但是我们知道在InnoDB中是以页为单位来进行磁盘IO的，也就是说我们在该事务提交时不得不将一个完整的页面从内存中刷新到磁盘，我们又知道一个默认页面时16KB大小，只修改一个字节就要刷新16KB的数据到磁盘上显然是小题大做了。
• 随机IO刷新较慢
  一个事务可能包含很多语句，即使是一条语句也可能修改许多页面，假如该事务修改的这些页面可能并不相邻，这就意味着在将某个事务修改的Buffer Pool中的页面刷新到磁盘时，需要进行很多的随机IO，随机IO比顺序IO要慢，尤其对于传统的机械硬盘来说。

另一个解决的思路 ：我们只是想让已经提交了的事务对数据库中数据所做的修改永久生效，即使后来系 统崩溃，在重启后也能把这种修改恢复出来。所以我们其实没有必要在每次事务提交时就把该事务在内 存中修改过的全部页面刷新到磁盘，只需要把 修改 了哪些东西 记录一下 就好。比如，某个事务将系统 表空间中 第10号 页面中偏移量为 100 处的那个字节的值 1 改成 2 。我们只需要记录一下：将第0号表 空间的10号页面的偏移量为100处的值更新为 2。

InnoDB引擎的事务采用了WAL技术 (Write-Ahead Logging)，这种技术的思想就是先写日志，再写磁盘，只有日志写入成功，才算事务提交成功，这里的日志就是redo log。当发生宕机且数据未刷到磁盘的时候，可以通过redo log来恢复，保证ACID中的D，这就是redo log的作用。

2.2 REDO日志的好处、特点

好处

• redo日志降低了刷盘频率
• redo日志占用的空间非常小

存储表空间ID、页号、偏移量以及需要更新的值，所需的存储空间是很小的，刷盘快。

特点

• redo日志是顺序写入磁盘的
  在执行事务的过程中，每执行一条语句，就可能产生若干条redo日志，这些日志是按照产生的顺序写入磁盘的，也就是使用顺序ID，效率比随机IO快。
• 事务执行过程中，redo log不断记录
  redo log跟bin log的区别，redo log是存储引擎层产生的，而bin log是数据库层产生的。假设一个事务，对表做10万行的记录插入，在这个过程中，一直不断的往redo log顺序记录，而bin log不会记录，直到这个事务提交，才会一次写入到bin log文件中。

2.3 redo的组成

Redo log可以简单分为以下两个部分：

• 重做日志的缓冲 (redo log buffer) ，保存在内存中，是易失的。在服务器启动时就会向操作系统申请了一大片称之为 redo log buffer 的 连续内存 空间，翻译成中文就是redo日志缓冲区。这片内存空间被划分为若干个连续的redo log block。一个redo log block占用512字节大小。

**参数设置：innodb_log_buffer_size，**redo log buffer 大小默认 16M ，最大值是4096M，最小值为1M

show variables like '%innodb_log_buffer_size%';

• 重做日志文件 (redo log file)，保存在硬盘中，是持久的。例如ib_logfile0和ib_logfile1即为REDO日志

2.4 redo的整体流程

以一个更新事务为例，redo log 流转过程，如下图所示：

第1步：先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝

第2步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值

第3步：当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式

第4步：定期将内存中修改的数据刷新到磁盘中

Write-Ahead Log(预先日志持久化)：在持久化一个数据页之前，先将内存中相应的日志页持久化

2.5 redo log的刷盘策略

redo log的写入并不是直接写入磁盘的，InnoDB引擎会在写redo log的时候先写redo log buffer，之后以一 定的频率刷入到真正的redo log file 中。这里的一定频率怎么看待呢？这就是我们要说的刷盘策略。

注意，redo log buffer刷盘到redo log file的过程并不是真正的刷到磁盘中去，只是刷入到 文件系统缓存 （page cache）中去（这是现代操作系统为了提高文件写入效率做的一个优化），真正的写入会交给系统自己来决定（比如page cache足够大了）。那么对于InnoDB来说就存在一个问题，如果交给系统来同 步，同样如果系统宕机，那么数据也丢失了（虽然整个系统宕机的概率还是比较小的)。

针对这种情况，InnoDB给出 innodb_flush_log_at_trx_commit 参数，该参数控制 commit提交事务 时，如何将 redo log buffer 中的日志刷新到 redo log file 中。它支持三种策略：

• 设置为0 ：表示每次事务提交时不进行刷盘操作。（系统默认master thread每隔1s进行一次重做日 志的同步） 第1步：先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝 第2步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值 第3步：当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加 写的方式 第4步：定期将内存中修改的数据刷新到磁盘中
• 设置为1 ：表示每次事务提交时都将进行同步，刷盘操作（ 默认值 ）
• 设置为2 ：表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由os自 己决定什么时候同步到磁盘文件。

-- 查看自己的刷盘策略
show variables like 'innodb_flush_log_at_trx_commit' ;

另外，InnoDB存储引擎有一个后台线程，每隔1秒，就会把redo log buffer中的内容写到文件系统缓存(page cache)，然后调用刷盘操作

也就是说，一个没有提交事务的redo log记录，也可能会刷盘。因为在事务执行过程 redo log 记录是会写入 redo log buffer中，这些redo log 记录会被后台线程刷盘。

除了后台线程每秒1次的轮询操作，还有一种情况，当redo log buffer占用的空间即将达到innodb_log_buffer_size（这个参数默认是16M)的一半的时候，后台线程会主动刷盘

2.6 不同刷盘策略演示

-- 比较innodb_flush_log_at_trx_commit对事务的影响
CREATE TABLE test_load(
a INT,
b CHAR(80)
)ENGINE=INNODB;DELIMITER//
CREATE PROCEDURE p_load(COUNT INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c CHAR(80) DEFAULT REPEAT('a',80);
WHILE s<=COUNT DO
INSERT INTO test_load SELECT NULL, c;
COMMIT;
SET s=s+1;
END WHILE;
END //
DELIMITER;-- 向表中插入3w条记录，并执行3万次的fsync操作。
CALL p_load(30000);-- 修改参数innodb_flush_log_at_trx_commit，进行分别测试
set global innodb_flush_log_at_trx_commit = 0;
-- 0最快2其次1最慢

2.7 写入redo log buffer 过程

1、补充概念：Mini-Transaction

MySQL把对底层页面中的一次原子访问过程称之为一个Mini-Transaction，简称mtr，比如，向某个索引对应的B+树中插入一条记录的过程就是一个Mini-Transaction。一个所谓的mtr可以包含一组redo日志，在进行崩溃恢复时这一组redo日志可以作为一个不可分割的整体。

一个事务可以包含若干条语句，每一条语句其实是由若干个 mtr 组成，每一个 mtr 又可以包含若干条 redo日志，画个图表示它们的关系就是这样：

2、 redo 日志写入log buffer

向log buffer中写入redo日志的过程是顺序的，也就是先往前边的block中写，当该block的空闲空间用完之后再往下一个block中写。当我们想往log buffer中写入redo日志时，第一个遇到的问题就是应该写在哪个block的哪个偏移量处，所以InnoDB的设计者特意提供了一个称之为buf_free的全局变量，该变量指明后续写入的redo日志应该写入到log buffer中的哪个位置，如图所示:

一个mtr执行过程中可能产生若干条redo日志，这些redo日志是一个不可分割的组，所以其实并不是每生成一条redo日志，就将其插入到log buffer中，而是每个mtr运行过程中产生的日志先暂时存到一个地方，当该mtr结束的时候，将过程中产生的一组redo日志再全部复制到log buffer中。我们现在假设有两个名为T1、T2的事务，每个事务都包含2个mtr，我们给这几个mtr命名一下:

• 事务T1的两个mtr分别称为mtr_T1_1和mtr_T1_2
• 事务T2的两个mtr分别称为mtr_T2_1和mtr_T2_2

每个mtr都会产生一组redo日志，不同的事务可能是 并发 执行的，所以 T1 、 T2 之间的 mtr 可能是 交替执行 的。没当一个mtr执行完成时，伴随该mtr生成的一组redo日志就需要被复制到log buffer中，也就是说不同事务的mtr可能是交替写入log buffer的，我们画个示意图（为了美观，我们把一个mtr中产生的所有redo日志当做一个整体来画）

3、redo log block的结构

一个redo log block是由日志头、日志体、日志尾组成。日志头占用12字节，日志尾占用8字节，所以一个block真正能存储的数据是512-12-8=492字节。

这个和磁盘的扇区有关，机械磁盘默认的扇区就是512字节，如果你要写入的数据大于512字节，那么要写入的扇区肯定不止一个，这时就要涉及到盘片的转动，找到下一个扇区，假设现在需要写入两个扇区A和B，如果扇区A写入成功，而扇区B写入失败，那么就会出现非原子性的写入，而如果每次只写入和扇区的大小一样的512字节，那么每次的写入都是原子性的。

[外链图片转存中…(img-ddcRq6e8-1685002854316)]

2.8 redo log file

1、相关参数设置

• innodb_log_group_home_dir ：指定 redo log 文件组所在的路径，默认值为 ./ ，表示在数据库 的数据目录下。MySQL的默认数据目录（ var/lib/mysql）下默认有两个名为 ib_logfile0 和 ib_logfile1 的文件，log buffer中的日志默认情况下就是刷新到这两个磁盘文件中。此redo日志 文件位置还可以修改。
• innodb_log_files_in_group：指明redo log file的个数，命名方式如：ib_logfile0，iblogfile1… iblogfilen。默认2个，最大100个。 show variables like 'innodb_log_files_in_group';
• innodb_flush_log_at_trx_commit：控制 redo log 刷新到磁盘的策略，默认为1。
• innodb_log_file_size：单个 redo log 文件设置大小，默认值为 48M 。最大值为512G，注意最大值 指的是整个 redo log 系列文件之和，即（innodb_log_files_in_group * innodb_log_file_size ）不能大 于最大值512G

show variables like 'innodb_log_file_size';
-- 根据业务修改其大小，以便容纳较大的事务。编辑my.cnf文件并重启数据库生效
vim /etc/my.cnf
innodb_log_file_size=200M
-- 在数据库实例更新比较频繁的情况下，可以适当加大 redo log 数组和大小。
-- 但也不推荐 redo log 设置过大，在MySQL崩溃时会重新执行REDO日志中的记录。

2、日志文件组

从上边的描述中可以看到，磁盘上的redo日志文件不只一个，而是以一个日志文件组的形式出现的。这些文件以**ib_logfile[数字]（数字可以是0、1、2…)**的形式进行命名，每个的redo日志文件大小都是一样的。在将redo日志写入日志文件组时，是从ib_logfile0开始写，如果ib_logfile0写满了，就接着ib_logfile1写。同理，ib_logfile1写满了就去写ib_logfile2，依此类推。如果写到最后一个文件该咋办?那就重新转到ib_logfile0继续写

总共的redo日志文件大小其实就是： innodb_log_file_size × innodb_log_files_in_group 。采用循环使用的方式向redo日志文件组里写数据的话，会导致后写入的redo日志覆盖掉前边写的redo日志？当然！所以InnoDB的设计者提出了checkpoint的概念。

3、checkpoint

在整个日志文件组中还有两个重要的属性，分别是 write pos、checkpoint

• write pos是当前记录的位置，一边写一边后移
• checkpoint是当前要擦除的位置，也是往后推移

每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。每次MySQL加载日志文件组恢复数据时，会清空加载过的 redo log 记录，并把check point后移更新。write pos 和 checkpoint 之间的还空着的部分可以用来写入新的 redo log 记录。

如果 write pos 追上 checkpoint ，表示日志文件组满了，这时候不能再写入新的 redo log记录，MySQL 得 停下来，清空一些记录，把 checkpoint 推进一下

2.9 redo log 小结

3、Undo日志

redo log是事务持久性的保证，undo log是事务原子性的保证。在事务中 更新数据 的 前置操作 其实是要先写入一个 undo log

3.1 Undo日志概述

事务需要保证 原子性，也就是事务中的操作要么全部完成，要么什么也不做。但有时候事务执行到一半会出现一些情况，比如：

• 情况一：事务执行过程中可能遇到各种错误，比如服务器本身的错误 ， 操作系统错误 ，甚至是突然 断电 导致的错误。
• 情况二：程序员可以在事务执行过程中手动输入 ROLLBACK 语句结束当前事务的执行。

以上情况出现，我们需要把数据改回原先的样子，这个过程称之为 回滚 ，这样就可以造成一个假象：这 个事务看起来什么都没做，所以符合 原子性 要求。

每当我们要对一条记录做改动时(这里的改动可以指INSERT、DELETE、UPDATE)，都需要"“留一手”—-把回滚时所需的东西记下来。比如:

• 插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉就好了。(对于每个INSERT,InnoDB存储引擎会完成一个DELETE)
• 删除了一条记录，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了。(对于每个DELETE,lnnoDB存储引擎会执行一个INSERT)
• 修改了一条记录，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了。(对于每个UPDATE，InnoDB存储引擎会执行一个相反的UPDATE，将修改前的行放回去)

MySQL把这些为了回滚而记录的这些内容称之为撤销日志或者回滚日志(即undo log)。注意，由于查询操作( SELECT）并不会修改任何用户记录，所以在查询操作执行时，并不需要记录相应的undo日志。此外，undo log 会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护

3.2 Undo日志的作用

作用1：回滚数据

用户对undo日志可能有误解:undo用于将数据库物理地恢复到执行语句或事务之前的样子。但事实并非如此。undo是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。这是因为在多用户并发系统中，可能会有数十、数百甚至数千个并发事务。数据库的主要任务就是协调对数据记录的并发访问。比如，一个事务在修改当前一个页中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。

作用2：MVCC

undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录以及被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。

3.3 undo的存储结构

1、回滚段与undo页

InnoDB对undo log的管理采用段的方式，也就是 回滚段（rollback segment） 。每个回滚段记录了 1024 个 undo log segment ，而在每个undo log segment段中进行 undo页 的申请

• 在InnoDB1.1版本之前 （不包括1.1版本），只有一个rollback segment，因此支持同时在线的事务限制为 1024 。虽然对绝大多数的应用来说都已经够用。
• 从1.1版本开始InnoDB支持最大 128个rollback segment ，故其支持同时在线的事务限制提高到 了 128*1024

show variables like 'innodb_undo%';
show variables like 'innodb_undo_logs';

虽然InnoDB1.1版本支持了128个rollback segment，但是这些rollback segment都存储于共享表空间ibdata中。从InnoDB1.2版本开始，可通过参数对rollback segment做进一步的设置。这些参数包括:

• innodb_undo_directory:设置rollback segment文件所在的路径。这意味着rollback segment可以存放在共享表空间以外的位置，即可以设置为独立表空间。该参数的默认值为“./"，表示当前InnoDB存储引擎的目录
• innodb_undo_logs :设置rollback segment的个数，默认值为128。在InnoDB1.2版本中，该参数用来替换之前版本的参数innodb_rollback_segments
• innodb_undo_tablespaces :设置构成rollback segment文件的数量，这样rolback segment可以较为平均地分布在多个文件中。设置该参数后，会在路径innodb_undo_directory看到undo为前缀的文件，该文件就代表rollback segment文件

undo log相关参数一般很少改动，针对uno页的重用
当我们开启一个事务需要写undo log的时候，就得先去undo log segment中去找到一个空闲的位置，当有空位的时候，就去申请undo页，在这个申请到的undo页中进行undo log的写入。我们知道mysql默认一页的大小是16k。为每一个事务分配一个页，是非常浪费的（除非你的事务非常长)，假设你的应用的TPS(每秒处理的事务数目)为1000，那么1s就需要1000个页，大概需要16M的存储，1分钟大概需要1G的存储。如果照这样下去除非MySQL清理的非常勤快，否则随着时间的推移，磁盘空间会增长的非常快，而且很多空间都是浪费的。

于是undo页就被设计的可以重用了，当事务提交时，并不会立刻删除undo页。因为重用，所以这个undo页可能混杂着其他事务的undo log。undo log在commit后，会被放到一个链表中，然后判断undo页的使用空间是否小于3/4，如果小于3/4的话，则表示当前的undo页可以被重用，那么它就不会被回收，其他事务的undo log可以记录在当前undo页的后面。由于undo log是离散的，所以清理对应的磁盘空间时，效率不高。

2、回滚段与事务

• 每个事务只会使用一个回滚段，一个回滚段在同一时刻可能会服务于多个事务。
• 当一个事务开始的时候，会制定一个回滚段，在事务进行的过程中，当数据被修改时，原始的数 据会被复制到回滚段
• 在回滚段中，事务会不断填充盘区，直到事务结束或所有的空间被用完。如果当前的盘区不够 用，事务会在段中请求扩展下一个盘区，如果所有已分配的盘区都被用完，事务会覆盖最初的盘 区或者在回滚段允许的情况下扩展新的盘区来使用。
• 回滚段存在于undo表空间中，在数据库中可以存在多个undo表空间，但同一时刻只能使用一个 undo表空间。 undo log的数量，最少为2，undo log的truncate操作有purge协调线程发起。在truncate某个undo log表空间的过程中，保证有一个可用的undo log可用
• 当事务提交时，InnoDB存储引擎会做以下两件事情：将undo log放入列表中，以供之后的purge操作；判断undo log所在的页是否可以重用，若可以分配给下个事务使用

3、回滚段中的数据分类

• 未提交的回滚数据(uncommitted undo information)：该数据所关联的事务并未提交，用于实现读一致性，所以该数据不能被其他事务的数据覆盖。
• 已经提交但未过期的回滚数据(committed undo information)：该数据关联的事务已经提交，但是仍受到undo retention参数的保持时间的影响。
• 事务已经提交并过期的数据(expired undo information)：事务已经提交，而且数据保存时间已经超过 undo retention参数指定的时间，属于已经过期的数据。当回滚段满了之后，就优先覆盖“事务已经提交并过期的数据"。

事务提交后不能马上删除undo log及undo log所在的页。这是因为可能还有其他事务需要通过undo log来得到行记录之前的版本。故事务提交时将undo log放入一个链表中，是否可以最终删除undo log以undo log所在页由purge线程来判断

3.4 undo的类型

在InnoDB存储引擎中，undo log分为：

• insert undo log。insert undo log是指insert操作中产生的undo log。因为insert操作的记录，只对事务本身可见，对其他事务不可见（这是事务隔离性的要求），故该undo log可以在事务提交后直接删除。不需要进行purge操作。
• update undo log。update undo log记录的是对delete和update操作产生的undo log。该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。

3.5 undo log的生命周期

1、简要生成过程
以下是undo+redo事务的简化过程，假设有两个数值，分别为A=1和B=2，然后将A修改为3，B修改为4

start transaction;
记录A=1到undo log;
update A = 3;
记录A=3 到redo log:
记录B=2到undo log:
update B = 4;
记录B =4到redo log;
将redo log刷新到磁盘
commit

• 在1-8步骤的任意一步系统宕机，事务未提交，该事务就不会对磁盘上的数据做任何影响
• 如果在8-9之间宕机，恢复之后可以选择回滚，也可以选择继续完成事务提交，因为此时redo log已经持久化
• 若在9之后系统宕机，内存映射中变更的数据还来不及刷回磁盘，那么系统恢复之后，可以根据redo log把数据刷回磁盘。

2、详细生成过程

对应更新的操作会产生update undo log，并且会分更新主键和不更新主键的，对于不更新主键的，这时会把老的记录写入新的undo log，让回滚指针指向新的undo log，它的undo no是1，并且新的undo log会指向老的undo log（undo no=0）

对于更新主键的操作，会先把原来的数据deletemark标识打开，这时并没有真正的删除数据，真正的删除会交给清理线程去判断，然后在后面插入一条新的数据，新的数据也会产生undo log，并且undo log的序号会递增

可以发现每次对数据的变更都会产生一个undo log，当一条记录被变更多次时，那么就会产生多条undo log，undo log记录的是变更前的日志，并且每个undo log的序号是递增的，那么当要回滚的时候，按照序号依次向前推，就可以找到我们的原始数据了

3、undo log是如何回滚的

以上面的例子来说，假设执行rollback，那么对应的流程应该是这样：

• 通过undo no=3的日志把id=2的数据删除
• 通过undo no=2的日志把id=1的数据的deletemark还原成0
• 通过undo no=1的日志把id=1的数据的name还原成Tom通过undo no=0的日志把id=1的数据删除

4、undo log的删除

• 针对于insert undo log。因为insert操作的记录，只对事务本身可见，对其他事务不可见。故该undo log可以在事务提交后直接删除，不需要进行purge操作。
• 针对于update undo log。该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。

3.6 小结

• undo log是逻辑日志，对事务回滚时，只是将数据库逻辑地恢复到原来的样子
• redo log是物理日志，记录的是数据页的物理变化，undo log不是redo log的逆过程

三、锁

1、概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。在程序开发中会存在多线程同步的问题，当多个线程并发访问某个数据的时候，尤其是针对一些敏感的数据（比如订单、金额等)，我们就需要保证这个数据在任何时刻最多只有一个线程在访问，保证数据的完整性和一致性。在开发过程中加锁是为了保证数据的一致性，这个思想在数据库领域中同样很重要。

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的 资源。为保证数据的一致性，需要对 并发操作进行控制 ，因此产生了 锁 。同时 锁机制 也为实现MySQL 的各个隔离级别提供了保证。 锁冲突 也是影响数据库 并发访问性能 的一个重要因素。所以锁对数据库而言显得尤其重要，也更加复杂。

2、MySQL并发事务访问相同记录

2.1 读-读情况

读-读情况，即并发事务相继读取相同的记录。读取操作本身不会对记录有任何影响，并不会引起什么问题，所以允许这种情况的发生

2.2 写-写情况

写-写 情况，即并发事务相继对相同的记录做出改动，在这种情况下会发生 脏写 的问题，任何一种隔离级别都不允许这种问题的发生。所以在多个未提交事务相继对一条记录做改动时，需要让它们 排队执行 ，这个排队的过程其实是通过 锁 来实现的。这个所谓的锁其实是一个内存中的结构 ，在事务执行前本来是没有锁的，也就是说一开始是没有 锁结构 和记录进 行关联的，当一个事务想对这条记录做改动时，首先会看看内存中有没有与这条记录关联的 锁结构 ，当没有的时候 就会在内存中生成一个 锁结构 与之关联。比如，事务T1 要对这条记录做改动，就需要生成一个 锁结构 与之关联

在锁结构里有很多信息，为了简化理解，只把两个比较重要的属性拿了出来

• trx信息：代表这个锁结构是哪个事务生成的。
• is_waiting：代表当前事务是否在等待

在事务T1改动了这条记录后，就生成了一个锁结构与该记录关联，因为之前没有别的事务为这条记录加锁，所以is_waiting属性就是false，我们把这个场景就称值为获取锁成功，或者加锁成功，然后就可以继续执行操作了。在事务T1提交之前，另一个事务T2也想对该记录做改动，那么先看看有没有锁结构与这条记录关联，发现有一个锁结构与之关联后，然后也生成了一个锁结构与这条记录关联，不过锁结构的is_waiting属性值为true，表示当前事务需要等待，我们把这个场景就称之为获取锁失败，或者加锁失败

在事务T1提交之后，就会把该事务生成的锁结构释放掉，然后看看还有没有别的事务在等待获取锁，发现了事务T2还在等待获取锁，所以把事务T2对应的锁结构的is_waiting属性设置为false，然后把该事务对应的线程唤醒，让它继续执行，此时事务T2就算获取到锁了

小结

• 不加锁，意思就是不需要在内存中生成对应的 锁结构 ，可以直接执行操作。
• 获取锁成功，或者加锁成功，意思就是在内存中生成了对应的 锁结构 ，而且锁结构的 is_waiting 属性为 false ，也就是事务 可以继续执行操作。
• 获取锁失败，或者加锁失败，或者没有获取到锁，意思就是在内存中生成了对应的 锁结构 ，不过锁结构的 is_waiting 属性为 true ，也就是事务 需要等待，不可以继续执行操作。

2.3 读-写或写-读情况

读-写 或 写-读，即一个事务进行读取操作，另一个进行改动操作。这种情况下可能发生 脏读 、 不可重 复读 、 幻读 的问题。各个数据库厂商对 SQL标准 的支持都可能不一样。比如MySQL在 REPEATABLE READ 隔离级别上就已经解决了 幻读 问题

2.4 并发问题的解决方案

方案一：读操作利用多版本并发控制（ MVCC ，下章讲解），写操作进行 加锁
所谓的MVCC，就是生成一个ReadView，通过ReadView找到符合条件的记录版本（历史版本由undo日志构建)。查询语句只能读到在生成ReadView之前已提交事务所做的更改，在生成ReadView之前未提交的事务或者之后才开启的事务所做的更改是看不到的。而写操作肯定针对的是最新版本的记录，读记录的历史版本和改动记录的最新版本本身并不冲突，也就是采用MVCC时，读-写操作并不冲突。
普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用到MVCC读取记录。

• 在 READ COMMITTED 隔离级别下，一个事务在执行过程中每次执行SELECT操作时都会生成一 个ReadView，ReadView的存在本身就保证了事务不可以读取到未提交的事务所做的更改 ，也就是避免了脏读现象；
• 在 REPEATABLE READ 隔离级别下，一个事务在执行过程中只有 第一次执行SELECT操作 才会生成一个ReadView，之后的SELECT操作都 复用 这个ReadView，这样也就避免了不可重复读和幻读的问题。

方案二：读、写操作都采用 加锁 的方式

如果我们的一些业务场景不允许读取记录的旧版本，而是每次都必须去读取记录的最新版本。比如，在银行存款的事务中，你需要先把账户的余额读出来，然后将其加上本次存款的数额，最后再写到数据库中。在将账户余额读取出来后，就不想让别的事务再访问该余额，直到本次存款事务执行完成，其他事务才可以访问账户的余额。这样在读取记录的时候就需要对其进行加锁操作，这样也就意味着读操作和写操作也像写-写操作那样排队执行。

脏读的产生是因为当前事务读取了另一个未提交事务写的一条记录，如果另一个事务在写记录的时候就给这条记录加锁，那么当前事务就无法继续读取该记录了，所以也就不会有脏读问题的产生了。

不可重复读的产生是因为当前事务先读取一条记录，另外一个事务对该记录做了改动之后并提交之后，当前事务再次读取时会获得不同的值，如果在当前事务读取记录时就给该记录加锁，那么另一个事务就无法修改该记录，自然也不会发生不可重复读了。

幻读问题的产生是因为当前事务读取了一个范围的记录，然后另外的事务向该范围内插入了新记录，当前事务再次读取该范围的记录时发现了新插入的新记录。采用加锁的方式解决幻读问题就有一些麻烦，因为当前事务在第一次读取记录时幻影记录并不存在，所以读取的时候加锁就有点尴尬（因为你并不知道给谁加锁)。

小结对比发现

• 采用 MVCC 方式的话， 读-写 操作彼此并不冲突， 性能更高 。
• 采用 加锁 方式的话， 读-写 操作彼此需要 排队执行 ，影响性能。

一般情况下我们当然愿意采用 MVCC 来解决 读-写 操作并发执行的问题，但是业务在某些特殊情况下，要求必须采用 加锁的方式执行。下面就讲解下MySQL中不同类别的锁。

3、锁的不同角度分类

3.1 从数据操作的类型划分：读锁、写锁

对于数据库中并发事务的读-读情况并不会引起什么问题。对于写-写、读-写或写-读这些情况可能会引起一些问题，需要使用MVCC或者加锁的方式来解决它们。在使用加锁的方式解决问题时，由于既要允许读-读情况不受影响，又要使写-写、读-写或写-读情况中的操作相互阻塞，所以MySQL实现一个由两种类型的锁组成的锁系统来解决。这两种类型的锁通常被称为共享锁(Shared Lock，SLock)和排他锁（Exclusive Lock，XLock)，也叫读锁(readlock)和写锁(write lock)。

• 读锁 ：也称为 共享锁 、英文用 S 表示。针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，相互不阻塞的。
• 写锁 ：也称为 排他锁 、英文用 X 表示。当前写操作没有完成前，它会阻断其他写锁和读锁。这样 就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源

需要注意的是对于 InnoDB 引擎来说，读锁和写锁可以加在表上，也可以加在行上

1、锁定读

2、写操作

[外链图片转存中…(img-6Ewp4nej-1685002854319)]

3.2 从数据操作的粒度划分：表级锁、页级锁、行锁

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好,理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取、检查、释放锁等动作)。因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度(Lock granularity)”的概念。

对一条记录加锁影响的也只是这条记录而已，我们就说这个锁的粒度比较细;其实一个事务也可以在表级别进行加锁，自然就被称之为表级锁或者表锁，对一个表加锁影响整个表中的记录，我们就说这个锁的粒度比较粗。锁的粒度主要分为表级锁、页级锁和行锁。

1、表锁（Table Lock）

该锁会锁定整张表，它是MySQL中最基本的锁策略，并不依赖于存储引擎〈不管你是MysQL的什么存储引擎，对于表锁的策略都是一样的)，并且表锁是开销最小的策略（因为粒度比较大)。由于表级锁一次会将整个表锁定，所以可以很好的避免死锁问题。当然，锁的粒度大所带来最大的负面影响就是出现锁资源争用的概率也会最高，导致并发率大打折扣。

①表级别的S锁、X锁

在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎是不会为这个表添加表级别的 S锁 或者 X锁 的。在对某个表执行一些诸如 ALTER TABLE 、 DROP TABLE 这类的 DDL 语句时，其 他事务对这个表并发执行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞。同理，某个事务中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，在其他会话中对这个表执行 DDL 语句也会 发生阻塞。这个过程其实是通过在 server层使用一种称之为 元数据锁 （英文名： Metadata Locks ， 简称 MDL ）结构来实现的。

一般情况下，不会使用InnoDB存储引擎提供的表级别的 S锁 和 X锁 。只会在一些特殊情况下，比方说 崩溃恢复 过程中用到。比如，在系统变量 autocommit=0，innodb_table_locks = 1 时， 手动 获取 InnoDB存储引擎提供的表t 的 S锁 或者 X锁 可以这么写：

• LOCK TABLES t READ ：InnoDB存储引擎会对表 t 加表级别的 S锁。
• LOCK TABLES t WRITE ：InnoDB存储引擎会对表 t 加表级别的 X锁 。

不过尽量避免在使用InnoDB存储引擎的表上使用 LOCK TABLES 这样的手动锁表语句，它们并不会提供 什么额外的保护，只是会降低并发能力而已。InnoDB的厉害之处还是实现了更细粒度的 行锁 ，关于 InnoDB表级别的 S锁 和X锁 大家了解一下就可以了。

-- 下面我们讲解MyISAM引擎下的表锁
CREATE TABLE mylock(
id INT NOT NULL PRIMARY KEY auto_increment,
NAME VARCHAR(20)
)ENGINE myisam;
-- 插入一条数据
INSERT INTO mylock(NAME) VALUES('a');
-- 查询表中所有数据
SELECT * FROM mylock;
-- 查看表上加过的锁
SHOW OPEN TABLES; -- 主要关注In_use字段的值
-- 或者
SHOW OPEN TABLES where In_use > 0;
-- 手动增加表锁命令
LOCK TABLES t READ; -- 存储引擎会对表t加表级别的共享锁。共享锁也叫读锁或S锁（Share的缩写）
LOCK TABLES t WRITE; -- 存储引擎会对表t加表级别的排他锁。排他锁也叫独占锁、写锁或X锁（exclusive的缩写）
-- 释放表锁
UNLOCK TABLES;-- MyISAM在执行查询语句（SELECT）前，会给涉及的所有表加读锁，在执行增删改操作前，会给涉及的表加写锁。InnoDB存储引擎是不会为这个表添加表级别的读锁和写锁的。
-- MySQL的表级锁有两种模式：（以MyISAM表进行操作的演示）
-- 表共享读锁（Table Read Lock）
-- 表独占写锁（Table Write Lock）

② 意向锁 （intention lock）

InnoDB 支持 多粒度锁（multiple granularity locking） ，它允许 行级锁 与 表级锁 共存，而意向锁就是其中的一种 表锁

1. 意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁和行锁）的锁并存。
2. 意向锁是一种不与行级锁冲突表级锁，这一点非常重要。
3. 表明“某个事务正在某些行持有了锁或该事务准备去持有锁”

意向锁分为两种：

• 意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁），SELECT column FROM table ... LOCK IN SHARE MODE;
• 意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁），SELECT column FROM table ... FOR UPDATE;

意向锁要解决的问题：

在数据表的场景中，如果我们给某一行数据加上了排它锁，数据库会自动给更大一级的空间，比如数据页或数据表加上意向锁，告诉其他人这个数据页或数据表已经有人上过排它锁了，这样当其他人想要获取数据表排它锁的时候，只需要了解是否有人已经获取了这个数据表的意向排他锁即可。

• 如果事务想要获得数据表中某些记录的共享锁，就需要在数据表上添加意向共享锁
• 如果事务想要获得数据表中某些记录的排他锁，就需要在数据表上添加意向排他锁

这时，意向锁会告诉其他事务已经有人锁定了表中的某些记录。

意向锁的并发性：

意向锁不会与行级的共享 / 排他锁互斥！正因为如此，意向锁并不会影响到多个事务对不同数据行加排他锁时的并发性。（不然我们直接用普通的表锁就行了）

总结：

1. InnoDB 支持 多粒度锁 ，特定场景下，行级锁可以与表级锁共存。
2. 意向锁之间互不排斥，但除了 IS 与 S 兼容外， 意向锁会与 共享锁 / 排他锁 互斥 。
3. IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。
4. 意向锁在保证并发性的前提下，实现了 行锁和表锁共存 且 满足事务隔离性 的要求

③ 自增锁（AUTO-INC锁）

可以参考：https://mp.weixin.qq.com/s/XAPgLnqbUNO_WSPcYfJXGw

1. “Simple inserts” （简单插入）

可以 预先确定要插入的行数 （当语句被初始处理时）的语句。包括没有嵌套子查询的单行和多行INSERT…VALUES()和 REPLACE 语句。比如我们上面举的例子就属于该类插入，已经确定要插入的行 数。

2. “Bulk inserts” （批量插入）

事先不知道要插入的行数 （和所需自动递增值的数量）的语句。比如 INSERT … SELECT ， REPLACE … SELECT 和 LOAD DATA 语句，但不包括纯INSERT。 InnoDB在每处理一行，为AUTO_INCREMENT列

3. “Mixed-mode inserts” （混合模式插入）

这些是“Simple inserts”语句但是指定部分新行的自动递增值。例如 INSERT INTO teacher (id,name) VALUES (1,‘a’), (NULL,‘b’), (5,‘c’), (NULL,‘d’); 只是指定了部分id的值。另一种类型的“混合模式插入”是 INSERT … ON DUPLICATE KEY UPDATE 。

MySQL中采用了自增锁的方式来实现，AUTO-INC锁是当向使用含有AUTO_INCREMENT列的表中插入数据时需要获取的一种特殊的表级锁，在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTo_INCREMENT修饰的列分配递增的值，在该语句执行结束后，再把AUTO-INC锁释放掉。一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。也正因为此，其并发性显然并不高，当我们向一个有AUTO_INCREMENT关键字的主键插入值的时候，每条语句都要对这个表锁进行竞争，这样的并发潜力其实是很低下的，所以innodb通过innodb_autoinc_lock_mode的不同取值来提供不同的锁定机制，来显著提高SQL语句的可伸缩性和性能。innodb_autoinc_lock_mode有三种取值，分别对应与不同锁定模式：

（1）innodb_autoinc_lock_mode = 0(“传统”锁定模式)

在此锁定模式下，所有类型的insert语句都会获得一个特殊的表级AUTO-INC锁，用于插入具有 AUTO_INCREMENT列的表。这种模式其实就如我们上面的例子，即每当执行insert的时候，都会得到一个 表级锁(AUTO-INC锁)，使得语句中生成的auto_increment为顺序，且在binlog中重放的时候，可以保证 master与slave中数据的auto_increment是相同的。因为是表级锁，当在同一时间多个事务中执行insert的 时候，对于AUTO-INC锁的争夺会 限制并发 能力。

（2）innodb_autoinc_lock_mode = 1("连续"锁定模式)

在 MySQL 8.0 之前，连续锁定模式是 默认 的。在这个模式下，“bulk inserts”仍然使用AUTO-INC表级锁，并保持到语句结束。这适用于所有INSERT … SELECT，REPLACE … SELECT和LOAD DATA语句。同一时刻只有一个语句可以持有AUTO-INC锁。
对于“Simple inserts”（要插入的行数事先已知），则通过在 mutex（轻量锁） 的控制下获得所需数量的自动递增值来避免表级AUTO-INC锁， 它只在分配过程的持续时间内保持，而不是直到语句完成。不使用表级AUTO-INC锁，除非AUTO-INC锁由另一个事务保持。如果另一个事务保持AUTO-INC锁，则“Simple inserts”等待AUTO-INC锁，如同它是一个“bulk inserts”。

（3）innodb_autoinc_lock_mode = 2(“交错”锁定模式)

从 MySQL 8.0 开始，交错锁模式是 默认 设置。在此锁定模式下，自动递增值 保证 在所有并发执行的所有类型的insert语句中是 唯一 且 单调递增 的。但是，由于多个语句可以同时生成数字（即，跨语句交叉编号），为任何给定语句插入的行生成的值可能不是连续的。
如果执行的语句是“simple inserts"，其中要插入的行数已提前知道，除了"Mixed-mode inserts"之外，为单个语句生成的数字不会有间隙。然后，当执行"bulk inserts"时，在由任何给定语句分配的自动递增值中可能存在间隙。

④ 元数据锁（MDL锁）

MySQL5.5引入了meta data lock，简称MDL锁，属于表锁范畴。MDL 的作用是，保证读写的正确性。比 如，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个 表结构做变更 ，增加了一 列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。因此，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删查改。读写锁之间、写锁之间都是互斥的，用来保证变更表结构操作的安全性，解决了DML和DDL操作之间的一致性问题。不需要显式使用，在访问一个表的时候会被自动加上。

2、InnoDB中的行锁

行锁（Row Lock）也称为记录锁，顾名思义，就是锁住某一行（某条记录 row）。需要注意的是，MySQL服务器层并没有实现行锁机制，行级锁只在存储引擎层实现。

**优点：**锁定力度小，发生锁冲突概率低，可以实现的并发度高。

**缺点：**对于锁的开销比较大，加锁会比较慢，容易出现死锁情况。

InnoDB与MyISAM的最大不同有两点：一是支持事物（TRANSACTION）；二是采用了行级锁。

① 记录锁（Record Locks）

记录锁也就是仅仅把一条记录锁，官方的类型名称为：LOCK_REC_NOT_GAP。记录锁是有S锁和X锁之分的，称之为 S型记录锁 和 X型记录锁 。

• 当一个事务获取了一条记录的S型记录锁后，其他事务也可以继续获取该记录的S型记录锁，但不可以继续获取X型记录锁；
• 当一个事务获取了一条记录的X型记录锁后，其他事务既不可以继续获取该记录的S型记录锁，也不可以继续获取X型记录锁。

② 间隙锁（Gap Locks）

MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方 案解决，也可以采用 加锁方案解决。但是在使用加锁方案解决时有个大问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些 幻影记录 加上 记录锁 。InnoDB提出了一种称之为 Gap Locks 的锁，官方的类型名称为：LOCK_GAP ，我们可以简称为 gap锁

**gap锁的提出仅仅是为了防止插入幻影记录而提出的。**虽然有共享gap锁和独占gap锁这样的说法，但是它们起到的作用是相同的。而且如果对一条记录加了gap锁（不论是共享gap锁还是独占gap锁），并不会限制其他事务对这条记录加记录锁或者继续加gap锁。

③ 临键锁（Next-Key Locks）

有时候我们既想 锁住某条记录 ，又想 阻止 其他事务在该记录前边的 间隙插入新记录 ，所以InnoDB就提 出了一种称之为 Next-Key Locks 的锁，官方的类型名称为： LOCK_ORDINARY ，我们也可以简称为 next-key锁 。Next-Key Locks是在存储引擎 innodb 、事务级别在 可重复读 的情况下使用的数据库锁， innodb默认的锁就是Next-Key locks。

next-key锁的本质就是一个记录锁和一个gap锁的合体，它既能保护该条记录，又能阻止别的事务将新记录插入被保护记录前边的间隙

④ 插入意向锁

begin;
select * from student where id <=8 and id > 3 for update;

我们说一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了gap锁( next-key锁也包含gap锁），如果有的话，插入操作需要等待，直到拥有gap锁的那个事务提交。但是InnoDB规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在在等待。InnoDB就把这种类型的锁命名为Insert Intention Locks，官方的类型名称为:LOCK_INSERT_INTENTION，我们称为插入意向锁。插入意向锁是一种Gap锁，不是意向锁，在insert操作时产生。

插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁。该锁用以表示插入意向，当多个事务在同一区间(gap)插入位置不同的多条数据时，事务之间不需要互相等待。假设存在两条值分别为4和7的记录，两个不同的事务分别试图插入值为5和6的两条记录，每个事务在获取插入行上独占的(排他）锁前，都会获取(4，7)之间的间隙锁，但是因为数据行之间并不冲突，所以两个事务之间并不会产生冲突(阻塞等待)。总结来说，插入意向锁的特性可以分成两部分:

• 插入意向锁是一种特殊的间隙锁—间隙锁可以锁定开区间内的部分记录。
• 插入意向锁之间互不排斥，所以即使多个事务在同一区间插入多条记录，只要记录本身（(主键、唯一索引）不冲突，那么事务之间就不会出现冲突等待。

注意，虽然插入意向锁中含有意向锁三个字，但是它并不属于意向锁而属于间隙锁，因为意向锁是表锁而插入意向锁是行锁。

3、页锁

页锁就是在 页的粒度 上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我 们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

每个层级的锁数量是有限制的，因为锁会占用内存空间， 锁空间的大小是有限的 。当某个层级的锁数量 超过了这个层级的阈值时，就会进行 锁升级 。锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如 InnoDB 中行锁升级为表锁，这样做的好处是占用的锁空间降低了，但同时数据的并发度也下降了。

3.3 从对待锁的态度划分:乐观锁、悲观锁

1、悲观锁（Pessimistic Locking）

悲观锁是一种思想，顾名思义，就是很悲观，对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性。

悲观锁总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 阻塞 直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞， 用完后再把资源转让给其它线程）。比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，当其他线程想要访问数据时，都需要阻塞挂起。Java中 synchronized 和 ReentrantLock 等独占锁就是悲观锁思想的实现。

select .... for update是MySQL中悲观锁。此时在items表中，id为1001的那条数据就被我们锁定了，其他的要执行select quantity from items where id = 1001 for update;语句的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。注意，当执行select quantity from items where id = 1001 for update;语句之后，如果在其他事务中执行selectquantity from items where id = 1001;语句，并不会受第一个事务的影响，仍然可以正常查询出数据。注意: select … for update语句执行过程中所有扫描的行都会被锁上，因此在MySQL中用悲观锁必须确定使用了索引，而不是全表扫描，否则将会把整个表锁住。
悲观锁不适用的场景较多，它存在一些不足，因为悲观锁大多数情况下依靠数据库的锁机制来实现，以保证程序的并发访问性，同时这样对数据库性能开销影响也很大，特别是长事务而言，这样的开销往往无法承受，这时就需要乐观锁。

2、乐观锁（Optimistic Locking）

乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用 版本号机制 或者 CAS机制 实现。乐观锁适用于多读的应用类型， 这样可以提高吞吐量。在Java中java.util.concurrent.atomic 包下的原子变量类就是使用了乐观锁的一种实现方式：CAS实现的

乐观锁的版本号机制

在表中设计一个 版本字段 version ，第一次读的时候，会获取 version 字段的取值。然后对数据进行更新或删除操作时，会执行 UPDATE … SET version=version+1 WHERE version=version 。此时 如果已经有事务对这条数据进行了更改，修改就不会成功。这种方式类似我们熟悉的SVN、CVS版本管理系统，当我们修改了代码进行提交时，首先会检查当前版本号与服务器上的版本号是否一致，如果一致就可以直接提交，如果不一致就需要更新服务器上的最新代码，然后再进行提交。

乐观锁的时间戳机制

时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行 比较，如果两者一致则更新成功，否则就是版本冲突。你能看到乐观锁就是程序员自己控制数据并发操作的权限，基本是通过给数据行增加一个戳（版本号或 者时间戳），从而证明当前拿到的数据是否最新。

3、两种锁的适用场景

• 乐观锁 适合 读操作多 的场景，相对来说写的操作比较少。它的优点在于 程序实现 ， 不存在死锁 问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。
• 悲观锁 适合 写操作多 的场景，因为写的操作具有 排它性 。采用悲观锁的方式，可以在数据库层 面阻止其他事务对该数据的操作权限，防止 读 - 写 和 写 - 写 的冲突。

3.4 按加锁的方式划分：显式锁、隐式锁

1、隐式锁

一个事务在执行INSERT操作时，如果即将插入的间隙已经被其他事务加了gap锁，那么本次INSERT操作会阻塞，并且当前事务会在该间隙上加一个插入意向锁，否则一般情况下INSERT操作是不加锁的。那如果一个事务首先插入了一条记录（此时并没有在内存生产与该记录关联的锁结构)，然后另一个事务:

• 立即使用SELECT ... LOCK IN SHARE MODE语句读取这条记录，也就是要获取这条记录的S锁，或者使用SELECT ... FOR UPDATE语句读取这条记录，也就是要获取这条记录的X锁，怎么办?如果允许这种情况的发生，那么可能产生脏读问题。
• 立即修改这条记录，也就是要获取这条记录的X锁，怎么办?如果允许这种情况的发生，那么可能产生脏写问题。

这时候我们前边提过的事务id又要起作用了。我们把聚簇索引和二级索引中的记录分开看一下:

• 情景一：对于聚簇索引记录来说，有一个 trx_id 隐藏列，该隐藏列记录着最后改动该记录的 事务 id 。那么如果在当前事务中新插入一条聚簇索引记录后，该记录的trx_id隐藏列代表的的就是 当前事务的 事务id ，如果其他事务此时想对该记录添加 S锁 或者 X锁 时，首先会看一下该记录的 trx_id 隐藏列代表的事务是否是当前的活跃事务，如果是的话，那么就帮助当前事务创建一个 X 锁 （也就是为当前事务创建一个锁结构， is_waiting 属性是 false ），然后自己进入等待状态 （也就是为自己也创建一个锁结构， is_waiting 属性是 true ）。
• 情景二：对于二级索引记录来说，本身并没有 trx_id 隐藏列，但是在二级索引页面的 Page Header 部分有一个 PAGE_MAX_TRX_ID 属性，该属性代表对该页面做改动的最大的 事务id ，如 果 PAGE_MAX_TRX_ID 属性值小于当前最小的活跃 事务id ，那么说明对该页面做修改的事务都已 经提交了，否则就需要在页面中定位到对应的二级索引记录，然后回表找到它对应的聚簇索引记 录，然后再重复 情景一 的做法。

即:一个事务对新插入的记录可以不显式的加锁（生成一个锁结构)，但是由于事务id的存在，相当于加了一个隐式锁。别的事务在对这条记录加s锁或者x锁时，由于隐式锁的存在，会先帮助当前事务生成一个锁结构，然后自己再生成一个锁结构后进入等待状态。隐式锁是一种延迟加锁的机制，从而来减少加锁的数量。隐式锁在实际内存对象中并不含有这个锁信息。只有当产生锁等待时,隐式锁转化为显式锁。

SELECT * FROM performance_schema.data_lock_waits\G;

隐式锁的逻辑过程如下：

• InnoDB的每条记录中都一个隐含的trx_id字段，这个字段存在于聚簇索引的B+Tree中
• 在操作一条记录前，首先根据记录中的trx_id检查该事务是否是活动的事务(未提交或回滚)。如果是活动的事务，首先将 隐式锁 转换为 显式锁 (就是为该事务添加一个锁)
• 检查是否有锁冲突，如果有冲突，创建锁，并设置为waiting状态。如果没有冲突不加锁，跳到E
• 等待加锁成功，被唤醒，或者超时
• 写数据，并将自己的trx_id写入trx_id字段

2、显式锁

-- 通过特定的语句进行加锁，我们一般称之为显示加锁
-- 显示加共享锁
select .... lock in share mode
-- 显示加排它锁
select .... for update

3.5 其它锁之：全局锁

全局锁就是对 整个数据库实例 加锁。当你需要让整个库处于 只读状态 的时候，可以使用这个命令，之后 其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结 构等）和更新类事务的提交语句。全局锁的典型使用 场景 是：做 全库逻辑备份

Flush tables with read lock

3.6 其它锁之：死锁

两个事务都持有对方需要的锁，并且在等待对方释放，并且双方都不会释放自己的锁

1、产生死锁的必要条件

• 两个或者两个以上事务
• 每个事务都已经持有锁并且申请新的锁
• 锁资源同时只能被同一个事务持有或者不兼容
• 事务之间因为持有锁和申请锁导致彼此循环等待

2、如何处理死锁

• 等待，直到超时（innodb_lock_wait_timeout=50s)
• 使用死锁检测处理死锁程序

方式1检测死锁太过被动，innodb还提供了wait-for graph算法来主动进行死锁检测，每当加锁请求无法立即满足需要并进入等待时，wait-for graph算法都会被触发。这是一种较为主动的死锁检测机制，要求数据库保存锁的信息链表和事物等待链表两部分信息。一旦检测到回路、有死锁，这时候InnoDB存储引擎会选择回滚undo量最小的事务，让其他事务继续执行（innodb_deadlock_detect=on表示开启这个逻辑）

缺点：每个新的被阻塞的线程，都要判断是不是由于自己的加入导致了死锁，这个操作时间复杂度是O(n)。如果100个并发线程同时更新同一行，意味着要检测100*100=1万次，1万个线程就会有1千万次检测。

如何解决？

• 方式1：关闭死锁检测，但意味着可能会出现大量的超时，会导致业务有损。
• 方式2：控制并发访问的数量。比如在中间件中实现对于相同行的更新，在进入引擎之前排队，这样在InnoDB内部就不会有大量的死锁检测工作。

进一步的思路：

可以考虑通过将一行改成逻辑上的多行来减少锁冲突。比如，连锁超市账户总额的记录，可以考虑放到多条记录上。账户总额等于这多个记录的值的总和。

3、如何避免死锁

• 合理设计索引，使业务SQL尽可能通过索引定位更少的行，减少锁竞争
• 调整业务逻辑SQL执行顺序，避免update/delete长时间持有锁的SQL在事务前面
• 避免大事务，尽量将大事务拆成多个小事务来处理，小事务缩短锁定资源的时间，发生锁冲突的几率也更小。
• 在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如select …for update语句，如果是在事务里运行了start transaction或设置了autocommit等于0，那么就会锁定所查找到的记录
• 降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁

4、锁的内部结构

4.1 概述

我们前边说对一条记录加锁的本质就是在内存中创建一个锁结构与之关联，那么是不是一个事务对多条记录加锁，就要创建多个锁结构呢，比如SELECT * FROM user LOCK IN SHARE MODE;

理论上创建多个锁结构没问题，但是如果一个事务要获取10000条记录的锁，生成10000个锁结构也太崩溃了！所以决定在对不同记录加锁时，如果符合下边这些条件的记录会放在一个锁结构中。

• 在同一个事务中进行加锁操作
• 被加锁的记录在同一个页面中
• 加锁的类型是一样的
• 等待状态是一样的

InnoDB 存储引擎中的 锁结构 如下：

4.2 结构解析

1、锁所在的事务信息

不论是 表锁 还是 行锁 ，都是在事务执行过程中生成的，哪个事务生成了这个锁结构 ，这里就记录这个 事务的信息。此 锁所在的事务信息 在内存结构中只是一个指针，通过指针可以找到内存中关于该事务的更多信息，比方说事务id等。

2、索引信息

对于 行锁 来说，需要记录一下加锁的记录是属于哪个索引的。这里也是一个指针。

3、表锁／行锁信息

表锁结构 和 行锁结构 在这个位置的内容是不同的：

• 表锁：
  记载着是对哪个表加的锁，还有其他的一些信息。
• 行锁，记载了三个重要的信息：
  • Space ID ：记录所在表空间
  • Page Number ：记录所在页号
  • n_bits：对于行锁来说，一条记录就对应着一个比特位，一个页面中包含很多记录，用不同 的比特位来区分到底是哪一条记录加了锁。为此在行锁结构的末尾放置了一堆比特位，这个n_bis属性代表使用了多少比特位。n_bits的值一般都比页面中记录条数多一些。主要是为了之后在页面中插入了新记录后 也不至于重新分配锁结构

4、type_mode
这是一个32位的数，被分成了 lock_mode 、 lock_type 和 rec_lock_type 三个部分，如图所示：

• 锁的模式（ lock_mode ），占用低4位，可选的值如下：
  • LOCK_IS （十进制的 0 ）：表示共享意向锁，也就是 IS锁
  • LOCK_IX （十进制的 1 ）：表示独占意向锁，也就是 IX锁
  • LOCK_S （十进制的 2 ）：表示共享锁，也就是 S锁
  • LOCK_X （十进制的 3 ）：表示独占锁，也就是 X锁
  • LOCK_AUTO_INC （十进制的 4 ）：表示 AUTO-INC锁

在InnoDB存储引擎中，LOCK_IS，LOCK_IX，LOCK_AUTO_INC都算是表级锁的模式，LOCK_S和 LOCK_X既可以算是表级锁的模式，也可以是行级锁的模式。

• 锁的类型（ lock_type ），占用第5～8位，不过现阶段只有第5位和第6位被使用：
  • LOCK_TABLE （十进制的 16 ），也就是当第5个比特位置为1时，表示表级锁
  • LOCK_REC（十进制的 32 ），也就是当第6个比特位置为1时，表示行级锁
• 行锁的具体类型（ rec_lock_type ），使用其余的位来表示。只有在 lock_type 的值为 LOCK_REC 时，也就是只有在该锁为行级锁时，才会被细分为更多的类型：
  • LOCK_ORDINARY （十进制的 0 ）：表示 next-key锁
  • LOCK_GAP （十进制的 512 ）：也就是当第10个比特位置为1时，表示 gap锁
  • LOCK_REC_NOT_GAP （十进制的 1024 ）：也就是当第11个比特位置为1时，表示正经 记录锁
  • LOCK_INSERT_INTENTION （十进制的 2048 ）：也就是当第12个比特位置为1时，表示插入意向锁。其他的类型：还有一些不常用的类型我们就不多说了
• is_waiting 属性呢？基于内存空间的节省，所以把 is_waiting 属性放到了 type_mode 这个32 位的数字中：
  • LOCK_WAIT （十进制的 256 ） ：当第9个比特位置为 1 时，表示 is_waiting 为 true ，也 就是当前事务尚未获取到锁，处在等待状态；当这个比特位为 0 时，表示 is_waiting 为 false ，也就是当前事务获取锁成功。

5、其他信息

为了更好的管理系统运行过程中生成的各种锁结构而设计了各种哈希表和链表。

**6、一堆比特位 **

如果是 行锁结构 的话，在该结构末尾还放置了一堆比特位，比特位的数量是由上边提到的 n_bits 属性 表示的。InnoDB数据页中的每条记录在 记录头信息 中都包含一个 heap_no 属性，伪记录 Infimum 的 heap_no 值为 0 ， Supremum 的 heap_no 值为 1 ，之后每插入一条记录， heap_no 值就增1。 锁结 构 最后的一堆比特位就对应着一个页面中的记录，一个比特位映射一个 heap_no ，即一个比特位映射 到页内的一条记录

5、锁监控

5.1 监控

-- 关于MySQL锁的监控，我们一般可以通过检查 InnoDB_row_lock 等状态变量来分析系统上的行锁的争夺情况
show status like 'innodb_row_lock%';

• Innodb_row_lock_current_waits：当前正在等待锁定的数量；
• Innodb_row_lock_time ：从系统启动到现在锁定总时间长度；（等待总时长）
• Innodb_row_lock_time_avg ：每次等待所花平均时间；（等待平均时长）
• Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
• Innodb_row_lock_waits ：系统启动后到现在总共等待的次数；（等待总次数）

5.2 其他监控方法

MySQL把事务和锁的信息记录在了 information_schema 库中，涉及到的三张表分别是 INNODB_TRX 、 INNODB_LOCKS 和 INNODB_LOCK_WAITS 。MySQL5.7及之前 ，可以通过information_schema.INNODB_LOCKS查看事务的锁情况，但只能看到阻塞事 务的锁；如果事务并未被阻塞，则在该表中看不到该事务的锁情况。

MySQL8.0删除了information_schema.INNODB_LOCKS，添加了 performance_schema.data_locks ，可以通过performance_schema.data_locks查看事务的锁情况，和MySQL5.7及之前不同，performance_schema.data_locks不但可以看到阻塞该事务的锁，还可以看到该事务所持有的锁。同时，information_schema.INNODB_LOCK_WAITS也被 performance_schema.data_lock_waits 所代 替。

-- 查询正在被锁阻塞的sql语句
SELECT * FROM information_schema.INNODB_TRX\G;
--查询锁等待情况 
SELECT * FROM performance_schema.data_lock_waits\G;
-- 查询锁的情况
SELECT * from performance_schema.data_locks\G;

6、举例与实战

6.1 间隙锁加锁规则（共11个案例）

间隙锁是在可重复读隔离级别下才会生效的： next-key lock 实际上是由间隙锁加行锁实现的，如果切换 到读提交隔离级别 (read-committed) 的话，就好理解了，过程中去掉间隙锁的部分，也就是只剩下行锁 的部分。而在读提交隔离级别下间隙锁就没有了，为了解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row 。也就是说，许多公司的配置为：读提交隔离级别加binlog_format=row。业务不 需要可重复读的保证，这样考虑到读提交下操作数据的锁范围更小（没有间隙锁），这个选择是合理的。
**next-key lock的加锁规则，**总结的加锁规则里面，包含了两个"原则"、两个"优化"和一个"bug"

• 原则 1 ：加锁的基本单位是 next-key lock 。 next-key lock 是前开后闭区间
• 原则 2 ：查找过程中访问到的对象才会加锁。任何辅助索引上的锁，或者非索引列上的锁，最终 都要回溯到主键上，在主键上也要加一把锁
• 优化 1 ：索引上的等值查询，给唯一索引加锁的时候， next-key lock 退化为行锁。也就是说如果 InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁
• 优化 2 ：索引上（不一定是唯一索引）的等值查询，向右遍历时且最后一个值不满足等值条件的 时候， next-keylock 退化为间隙锁。
• 一个 bug ：唯一索引上的范围查询会访问到不满足条件的第一个值为止

-- 首先准备数据
CREATE TABLE `test` (
`id` int(11) NOT NULL,
`col1` int(11) DEFAULT NULL,
`col2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`col1`)
) ENGINE=InnoDB;
insert into test values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

6.2 11个案例详解

案例一：唯一索引等值查询间隙锁
由于表 test 中没有 id=7 的记录，根据原则 1 ，加锁单位是 next-key lock ， session A 加锁范围就是 (5,10] ； 同时根据优化 2 ，这是一个等值查询 (id=7) ，而 id=10 不满足查询条件， next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)

sessionA	sessionB	sessionc
begin;
update test set col2 = col2+1where id=7;
	insert into test values(8,8,8)(blocked)
		update test set col2 =col2+1 whereid=10;
(Query OK)

案例二：非唯一索引等值查询锁
这里 session A 要给索引 col1 上 col1=5 的这一行加上读锁。

1. 根据原则 1 ，加锁单位是 next-key lock ，左开右闭，5是闭上的，因此会给 (0,5] 加上 next-key lock 。
2. 要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的（可能有col1=5的其他记 录），需要向右遍历，查到c=10 才放弃。根据原则 2 ，访问到的都要加锁，因此要给 (5,10] 加 next-key lock 。
3. 但是同时这个符合优化 2 ：等值判断，向右遍历，最后一个值不满足 col1=5 这个等值条件，因此退化成间隙锁 (5,10) 。
4. 根据原则 2 ， 只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成。

但 session C 要插入一个 (7,7,7) 的记录，就会被 session A 的间隙锁 (5,10) 锁住 这个例子说明，锁是加在索引上的。执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。如果你要用 lock in share mode来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，因为覆盖索引不会访问主键索引，不会给主键索引上加锁

sessionA	sessionB	sessionc
begin;
select id from test where col1 = 5 lock inshare mode;
	update test col2 =col2+1 where id=5;
(Query OK)
		insert into test values(7,7,7)(blocked)

案例三：主键索引范围查询锁

-- 上面两个例子是等值查询的，这个例子是关于范围查询的，也就是说下面的语句
select * from test where id=10 for update
select * from tets where id>=10 and id<11 for update;
-- 这两条查语句肯定是等价的，但是它们的加锁规则不太一样

• 开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10] 。 根据优化 1 ，主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。
• 它是范围查询， 范围查找就往后继续找，找到 id=15 这一行停下来，不满足条件，因此需要加 next-key lock(10,15] 。

session A 这时候锁的范围就是主键索引上，行锁 id=10 和 next-key lock(10,15] 。首次 session A 定位查找 id=10 的行的时候，是当做等值查询来判断的，而向右扫描到 id=15 的时候，用的是范围查询判断。

sessionA	sessionB	sessionc
begin;
sclcct * from test where id>= 10 and id<11 for update;
	insert into test values(8,8,8)(Query OK)
insert into test values(13,13,13);(blocked)
		update test set clo2=col2+1where id=15;
(blocked)

案例四：非唯一索引范围查询锁

与案例三不同的是，案例四中查询语句的 where 部分用的是字段 c ，它是普通索引。这两条查语句肯定是等价的，但是它们的加锁规则不太一样。

此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-keylock 。这里需要扫描到 col1=15 才停止扫描，是合理的，因为 InnoDB 要扫到 col1=15 ，才知道不需要继续往后找了。

sessionA	sessionB	sessionc
begin;
select *from test where col1>= 10 andcol1<11 for update;
	insert into test values(8,8,8)(blocked)
		update test set clo2=col2+1where id=15;
(blocked)

案例五：唯一索引范围查询锁 bug

session A 是一个范围查询，按照原则 1 的话，应该是索引 id 上只加 (10,15] 这个 next-key lock ，并且因 为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。

但是实现上， InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20 。而且由于这是个范围扫描，因此索引 id 上的 (15,20] 这个 next-key lock 也会被锁上。照理说，这里锁住 id=20 这一行的行为，其实是没有必要的。因为扫描到 id=15 ，就可以确定不用往后再找了。

sessionA	sessionB	sessionc
begin;
select * from test where id> 10 and id<=15 for update;
	update test set clo2=col2+1where id=20;
(blocked)
		insert into test values(16,16,16);(blocked)

案例六：非唯一索引上存在"等值"的例子

这里，我给表 t 插入一条新记录：insert into t values(30,10,30);也就是说，现在表里面有两个col1=10的行，但是它们的主键值 id 是不同的（分别是 10 和 30 ），因此这两个col1=10 的记录之间，也是有间隙的。

sessionA	sessionB	sessionc
begin;
delete from test where col1=10;
	insert into test values(12,12,12);(blocked)
		update test set col2=col2+1 where col1=15;
(blocked)

这次我们用 delete 语句来验证。注意， delete 语句加锁的逻辑，其实跟 select … for update 是类似的， 也就是我在文章开始总结的两个原则 、两个优化和一个bug。这时， session A 在遍历的时候，先访问第一个 col1=10 的记录。同样地，根据原则 1 ，这里加的是 (col1=5,id=5) 到 (col1=10,id=10) 这个 next-key lock 。

由于c是普通索引，所以继续向右查找，直到碰到 (col1=15,id=15) 这一行循环才结束。根据优化 2 ，这是 一个等值查询，向右查找到了不满足条件的行，所以会退化成 (col1=10,id=10) 到 (col1=15,id=15) 的间隙锁。

这个 delete 语句在索引 col1 上的加锁范围，就是上面图中蓝色区域覆盖的部分。这个蓝色区域左右两边都 是虚线，表示开区间，即 (col1=5,id=5) 和 (col1=15,id=15) 这两行上都没有锁

案例七： limit 语句加锁

sessionA	sessionB
begin;
delete from test where col1=10 limit 2;
	insert into test values(12,12,12);(Query OK)

session A 的 delete 语句加了 limit 2 。你知道表 t 里 col1=10 的记录其实只有两条，因此加不加 limit 2 ，删除的效果都是一样的。但是加锁效果却不一样。这是因为，案例七里的 delete 语句明确加了 limit 2 的限制，因此在遍历到 (col1=10, id=30) 这一行之后， 满足条件的语句已经有两条，循环就结束了。因此，索引 col1 上的加锁范围就变成了从（ col1=5,id=5) 到（ col1=10,id=30) 这个前开后闭区间

案例八：一个死锁的例子

• session A 启动事务后执行查询语句加 lock in share mode ，在索引 col1 上加了 next-keylock(5,10] 和 间隙锁 (10,15) （索引向右遍历退化为间隙锁）；
• session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，进入锁等待； 实际上分成了两步， 先是加 (5,10) 的间隙锁，加锁成功；然后加 col1=10 的行锁，因为sessionA上已经给这行加上了读 锁，此时申请死锁时会被阻塞
• 然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁， InnoDB 让 session B 回滚

sessionA	sessionB
begin;
select id from test where col1=10 lock in share mode;
	update test set col2=col2+1 where col1=10;(blocked)
insert into test values(8,8,8);
	ERROR 1213(40001):Deadlock found when trying togetlock;try restarting transaction

案例九：order by索引排序的间隙锁1

begin;
select * from test where id>9 and id<12 order by id desc for update;

• 首先这个查询语句的语义是 order by id desc ，要拿到满足条件的所有行，优化器必须先找到 “ 第 一个 id<12 的值 ”
• 这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到 id=12 的这个值，只是最终 没找到，但找到了 (10,15) 这个间隙。（ id=15 不满足条件，所以 next-key lock 退化为了间隙锁 (10, 15) ）
• 然后向左遍历，在遍历过程中，就不是等值查询了，会扫描到 id=5 这一行，又因为区间是左开右 闭的，所以会加一个next-key lock (0,5] 。 也就是说，在执行过程中，通过树搜索的方式定位记录 的时候，用的是 “ 等值查询 ” 的方法。

案例十：order by索引排序的间隙锁2

• 由于是 order by col1 desc ，第一个要定位的是索引 col1 上 “ 最右边的 ”col1=20 的行。这是一个非唯一索引的等值查询：左开右闭区间，首先加上 next-key lock (15,20] 。 向右遍历，col1=25不满足条件，退化为间隙锁 所以会 加上间隙锁(20,25) 和 next-key lock (15,20]
• 在索引 col1 上向左遍历，要扫描到 col1=10 才停下来。同时又因为左开右闭区间，所以 next-key lock 会加到 (5,10] ，这正是阻塞session B 的 insert 语句的原因。
• 在扫描过程中， col1=20 、 col1=15 、 col1=10 这三行都存在值，由于是 select * ，所以会在主键 id 上加三个行锁。 因此， session A 的 select 语句锁的范围就是：
  • 索引 col1 上 (5, 25) ；
  • 主键索引上 id=15 、 20 两个行锁。

sessionA	sessionB
begin;
select * from test where col1>=15 and col1<=20 order by col1 desc lock in share mode;
	insert into testvalues(6,6,6);(blocked)

案例十一：update修改数据的例子-先插入后删除

sessionA	sessionB
begin;
select col1 from test where col1>5 lock in share mode;
	update test set col1=1 where col1=5(Query OK)
update test set col1=5 where col1=1;(blocked)

注意：根据 col1>5 查到的第一个记录是 col1=10 ，因此不会加 (0,5] 这个 next-key lock 。session A 的加锁范围是索引 col1 上的 (5,10] 、 (10,15] 、 (15,20] 、 (20,25] 和(25,supremum] 。之后 session B 的第一个 update 语句，要把 col1=5 改成 col1=1 ，你可以理解为两步：

• 插入 (col1=1, id=5) 这个记录；
• 删除 (col1=5, id=5) 这个记录

通过这个操作， session A 的加锁范围变成了图所示的样子:

接下来 session B 要执行 update t set col1 = 5 where col1 = 1 这个语句了，一样地可以拆成两步：

• 插入 (col1=5, id=5) 这个记录；
• 删除 (col1=1, id=5) 这个记录。 第一步试图在已经加了间隙锁的 (1,10) 中插入数据，所以就被堵住了

四、多版本并发控制

1、什么是MVCC

MVCC （Multiversion Concurrency Control），多版本并发控制。顾名思义，MVCC 是通过数据行的多个版本管理来实现数据库的 并发控制。这项技术使得在InnoDB的事务隔离级别下执行 一致性读 操作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样 在做查询的时候就不用等待另一个事务释放锁。

MVCC没有正式的标准，在不同的DBMS中MVCC的实现方式可能是不同的，也不是普遍使用的（大家可以参考相关的DBMS文档）。这里讲解InnoDB中MVCC的实现机制（MySQL其他的存储引擎并不支持它）。

2、快照读与当前读

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理 读-写冲突 ，做到 即使有读写冲突时，也能做到 不加锁 ， 非阻塞并发读 ，而这个读指的就是 快照读 , 而非 当前读 。当前 读实际上是一种加锁的操作，是悲观锁的实现。而MVCC本质是采用乐观锁思想的一种方式

2.1 快照读

快照读又叫一致性读，读取的是快照数据。不加锁的简单的 SELECT 都属于快照读，即不加锁的非阻塞 读

SELECT * FROM player WHERE ...

之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于MVCC，它在很多情况下， 避免了加锁操作，降低了开销。既然是基于多版本，那么快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。 快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读。

2.2 当前读

当前读读取的是记录的最新版本（最新数据，而不是历史版本的数据），读取时还要保证其他并发事务 不能修改当前记录，会对读取的记录进行加锁。加锁的 SELECT，或者对数据进行增删改都会进行当前 读。比如：

SELECT * FROM student LOCK IN SHARE MODE; -- 共享锁
SELECT * FROM student FOR UPDATE; -- 排他锁
INSERT INTO student values ... -- 排他锁
DELETE FROM student WHERE ... -- 排他锁
UPDATE student SET ... -- 排他锁

3、前瞻回顾

3.1 再谈隔离级别

我们知道事务有 4 个隔离级别，可能存在三种并发问题

在MySQL中，默认的隔离级别是可重复读，可以解决脏读和不可重复读的问题，如果仅从定义的角度来看，它并不能解决幻读问题。如果我们想要解决幻读问题，就需要采用串行化的方式，也就是将隔离级别提升到最高，但这样一来就会大幅降低数据库的事务并发能力。MVCC可以不采用锁机制，而是通过乐观锁的方式来解决不可重复读和幻读问题!它可以在大多数情况下替代行级锁，降低系统的开销。

3.2 隐藏字段、Undo Log版本链

回顾一下undo日志的版本链，对于使用 InnoDB 存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列

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

insert undo只在事务回滚时起作用，当事务提交后，该类型的undo日志就没用了，它占用的Undo Log Segment也会被系统回收（也就是该undo日志占用的Undo页面链表要么被重用，要么被释放）。

每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个 roll_pointer 属性 （ INSERT 操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些 undo日志 都连起来，串成一个链表：

对该记录每次更新后，都会将旧值放到一条 undo日志 中，就算是该记录的一个旧版本，随着更新次数 的增多，所有的版本都会被 roll_pointer 属性连接成一个链表，我们把这个链表称之为 版本链 ，版 本链的头节点就是当前记录最新的值。每个版本中还包含生成该版本时对应的事务id。

4、MVCC实现原理之ReadView

MVCC 的实现依赖于：隐藏字段、Undo Log、Read View

4.1 什么是ReadView

在MVCC机制中，多个事务对同一个行记录进行更新会产生多个历史快照，这些历史快照保存在Undo Log里。如果一个事务想要查询这个行记录，需要读取哪个版本的行记录呢?这时就需要用到ReadView了，它帮我们解决了行的可见性问题。

ReadView就是事务在使用MVCC机制进行快照读操作时产生的读视图。当事务启动时，会生成数据库系统当前的一个快照，InnoDB为每个事务构造了一个数组，用来记录并维护系统当前活跃事务的ID（"活跃"指的就是，启动了但还没提交)。

4.2 设计思路

使用 READ UNCOMMITTED 隔离级别的事务，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了。使用 SERIALIZABLE 隔离级别的事务，InnoDB规定使用加锁的方式来访问记录。

使用 READ COMMITTED 和 REPEATABLE READ 隔离级别的事务，都必须保证读到 已经提交了的 事务修改过的记录。假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是需要判断一下版本链中的哪个版本是当前事务可见的，这是ReadView要解决的主要问题。

这个ReadView中主要包含4个比较重要的内容，分别如下：

• creator_trx_id ，创建这个 Read View 的事务 ID。 说明：只有在对表中的记录做改动时（执行INSERT、DELETE、UPDATE这些语句时）才会为 事务分配事务id，否则在一个只读事务中的事务id值都默认为0
• trx_ids ，表示在生成ReadView时当前系统中活跃的读写事务的 事务id列表
• up_limit_id ，活跃的事务中最小的事务 ID
• low_limit_id ，表示生成ReadView时系统中应该分配给下一个事务的 id 值。low_limit_id 是系 统最大的事务id值，这里要注意是系统中的事务id，需要区别于正在活跃的事务ID

注意：low_limit_id并不是trx_ids中的最大值，事务id是递增分配的。比如，现在有id为1， 2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时， trx_ids就包括1和2，up_limit_id的值就是1，low_limit_id的值就是4。

4.3 ReadView的规则

有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见

• 如果被访问版本的trx_id属性值与ReadView中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问
• 如果被访问版本的trx_id属性值小于ReadView中的 up_limit_id 值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问
• 如果被访问版本的trx_id属性值大于或等于ReadView中的 low_limit_id 值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问
• 如果被访问版本的trx_id属性值在ReadView的 up_limit_id 和 low_limit_id 之间，那就需要判断一下trx_id属性值是不是在 trx_ids 列表中
  • 如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问
  • 如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问

4.4 MVCC整体操作流程

当查询一条记录的时候，系统如何通过MVCC找到它：

• 首先获取事务自己的版本号，也就是事务 ID；
• 获取 ReadView；
• 查询得到的数据，然后与 ReadView 中的事务版本号进行比较；
• 如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；
• 最后返回符合规则的数据。

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。

InnoDB中，MVCC是通过Undo Log + Read View进行数据读取，Undo Log保存了历史快照，而Read View规则帮我们判断当前版本的数据是否可见。

在隔离级别为读已提交（Read Committed）时，一个事务中的每一次 SELECT 查询都会重新获取一次 Read View。注意，此时同样的查询语句都会重新获取一次 Read View，这时如果 Read View 不同，就可能产生不可重复读或者幻读的情况

当隔离级别为可重复读的时候，就避免了不可重复读，这是因为一个事务只在第一次 SELECT 的时候会获取一次 Read View，而后面所有的 SELECT 都会复用这个 Read View

5、举例说明

假设现在student表中只有一条由事务id为8的事务插入的一条记录:SELECT * FROM student;MVCC只能在READ COMMITTED和REPEATABLE READ两个隔离级别下工作。接下来看一下READ COMMNITTED和REPEATABLE READ所谓的生成ReadView的时机不同到底不同在哪里。

5.1 READ COMMITTED隔离级别下

READ COMMITTED ：每次读取数据前都生成一个ReadView

-- 现在有两个 事务id 分别为 10、 20的事务在执行
-- Transaction 10
BEGIN;
UPDATE student SET name="李四" WHERE id=1;
UPDATE student SET name="王五" WHERE id=1;
-- Transaction 20
BEGIN;
-- 更新了一些别的表的记录
...
-- 事务执行过程中，只有在第一次真正修改记录时（比如使用INSERT、DELETE、UPDATE语句），才会被分配一个单独的事务id，这个事务id是递增的。
-- 所以我们才在事务2中更新一些别的表的记录，目的是让它分配事务id。

此刻，表student 中 id 为 1 的记录得到的版本链表如下所示：

[外链图片转存中…(img-4NZqeDJG-1685002854321)]

假设现在有一个使用 READ COMMITTED 隔离级别的事务开始执行：

-- 使用READ COMMITTED隔离级别的事务
BEGIN;-- SELECT1：Transaction 10、20未提交
SELECT * FROM student WHERE id = 1; -- 得到的列name的值为'张三'

这个SELECT1的执行过程如下:

• 步骤1:在执行SELECT语句时会先生成一个ReadView ，ReadView的trx_ids列表的内容就是[10，20]，up_limit_id为10，low_limit_id为21, creator_trx_id为0
• 步骤2:从版本链中挑选可见的记录，从图中看出，最新版本的列name的内容是'王五'，该版本的trx_id值为10，在trx_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本。
• 步骤3:下一个版本的列name的内容是’李四’，该版本的trx_id值也为10，也在trx_ids列表内，所以也不符合要求，继续跳到下一个版本
• 步骤4∶下一个版本的列name的内容是’张三’，该版本的trx_id值为8，小于ReadView 中的up_limit_id值10，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为’张三’的记录

之后，我们把 事务id 为 10 的事务提交一下：

-- Transaction 10
BEGIN;
UPDATE student SET name="李四" WHERE id=1;
UPDATE student SET name="王五" WHERE id=1;
COMMIT;-- 然后再到 事务id为 20的事务中更新一下表 student中 id为 1的记录
-- Transaction 20
BEGIN;
-- 更新了一些别的表的记录
...
UPDATE student SET name="钱七" WHERE id=1;
UPDATE student SET name="宋八" WHERE id=1;

此刻，表student中 id 为 1 的记录的版本链就长这样：

[外链图片转存中…(img-kCedobtw-1685002854321)]

然后再到刚才使用 READ COMMITTED 隔离级别的事务中继续查找这个 id 为 1 的记录，如下：

-- 使用READ COMMITTED隔离级别的事务
BEGIN;-- SELECT1：Transaction 10、20均未提交
SELECT * FROM student WHERE id = 1; -- 得到的列name的值为'张三'-- SELECT2：Transaction 10提交，Transaction 20未提交
SELECT * FROM student WHERE id = 1; -- 得到的列name的值为'王五'

这个SELECT2的执行过程如下:

• 步骤1:在执行SELECT 语句时会又会单独生成一个ReadView，该ReadView的trx_ids列表的内容就是[20]，up_limit_id为20，low_limit_id为21, creator_trx_id为0。|
• 步骤2:从版本链中挑选可见的记录，从图中看出，最新版本的列name的内容是’宋八’，该版本的trx_id值为20，在trx_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本。
• 步骤3:下一个版本的列name的内容是’钱七’，该版本的trx_id值为20，也在trx_ids列表内，所以也不符合要求，继续跳到下一个版本。
• 步骤4:∶下一个版本的列name的内容是’王五’，该版本的trx_id值为10，小于ReadView 中的up_limit_id值20，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为’王五’的记录。

以此类推，如果之后事务id为20的记录也提交了，再次在使用READ COMMITTED隔离级别的事务中查询表student中id值为1的记录时，得到的结果就是’宋八’了，具体流程我们就不分析了。

强调:使用READ COMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的ReadView

5.2 REPEATABLE READ隔离级别下

使用 REPEATABLE READ 隔离级别的事务来说，只会在第一次执行查询语句时生成一个 ReadView ，之后的查询就不会重复生成了

比如，系统里有两个 事务id 分别为 10 、 20 的事务在执行

-- Transaction 10
BEGIN;
UPDATE student SET name="李四" WHERE id=1;
UPDATE student SET name="王五" WHERE id=1;
-- Transaction 20
BEGIN;
-- 更新了一些别的表的记录
...-- 假设现在有一个使用 `REPEATABLE READ` 隔离级别的事务开始执行：
-- 使用REPEATABLE READ隔离级别的事务
BEGIN;
-- SELECT1：Transaction 10、20未提交
SELECT * FROM student WHERE id = 1; -- 得到的列name的值为'张三'

这个SELECT1的执行过程如下:

• 步骤1:在执行SELECT语句时会先生成一个ReadView ，ReadView的trx_ids列表的内容就是[10，20]，up_limit_id为10，low_limit_id为21, creator_trx_id为0
• 步骤2:从版本链中挑选可见的记录，从图中看出，最新版本的列name的内容是’王五’，该版本的trx_id值为10，在trx_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本。
• 步骤3:下一个版本的列name的内容是’李四’，该版本的trx_id值也为10，也在trx_ids列表内，所以也不符合要求，继续跳到下一个版本
• 步骤4∶下一个版本的列name的内容是’张三’，该版本的trx_id值为8，小于ReadView 中的up_limit_id值10，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为’张三’的记录

之后，我们把 事务id 为 10 的事务提交一下

-- Transaction 10
BEGIN;
UPDATE student SET name="李四" WHERE id=1;
UPDATE student SET name="王五" WHERE id=1;COMMIT;
-- 然后再到 `事务id` 为 `20` 的事务中更新一下表 `student` 中 `id` 为 `1` 的记录
-- Transaction 20
BEGIN;
-- 更新了一些别的表的记录
...
UPDATE student SET name="钱七" WHERE id=1;
UPDATE student SET name="宋八" WHERE id=1;-- 然后再到刚才使用 `REPEATABLE READ` 隔离级别的事务中继续查找这个 `id` 为 `1` 的记录
-- 使用REPEATABLE READ隔离级别的事务
BEGIN;
-- SELECT1：Transaction 10、20均未提交
SELECT * FROM student WHERE id = 1; -- 得到的列name的值为'张三'
-- SELECT2：Transaction 10提交，Transaction 20未提交
SELECT * FROM student WHERE id = 1; -- 得到的列name的值仍为'张三'

SELECT2的执行过程如下:

• 步骤1∶因为当前事务的隔离级别为REPEATABLE READ，而之前在执行SELECT1时已经生成过ReadView了，所以此时直接复用之前的ReadView，之前的ReadView的trx_ids列表的内容就是[10，20]，up_limit_id为10, low_limit_id为21, creator_trx_id为0
• 步骤2:然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是’宋八’，该版本的trx_id值为20，在trx_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本。
• 步骤3:下一个版本的列name的内容是’钱七’，该版本的trx_id值为20，也在trx_ids列表内，所以也不符合要求，继续跳到下一个版本。
• 步骤4∶下一个版本的列name的内容是’王五，该版本的trx_id值为10，而trx_ids列表中是包含值为10的事务id的，所以该版本也不符合要求，同理下一个列name的内容是’李四’的版本也不符合要求。继续跳到下一个版本。
• 步骤5:下一个版本的列name的内容是’张三’，该版本的trx_id值为80，小于ReadView 中的up_limit_id值10，所以这个版本是符合要求的，最后返回给用户的版本就是这条列c为‘张三’的记录。

这次SELECT查询得到的结果是重复的，记录的列c值都是张三，这就是可重复读的含义。如果我们之后再把事务id为20的记录提交了，然后再到刚才使用REPEATABLE READ隔离级别的事务中继续查找这个id为1的记录，得到的结果还是张三，具体执行过程大家可以自己分析一下。

5.3 如何解决幻读

接下来说明InnoDB 是如何解决幻读的。假设现在表 student 中只有一条数据，数据内容中，主键 id=1，隐藏的 trx_id=10，它的 undo log 如下图所示。

[外链图片转存中…(img-jXozTsEP-1685002854322)]

假设现在有事务 A 和事务 B 并发执行，事务 A 的事务 id 为 20 ， 事务 B 的事务 id 为 30 。

步骤1：事务 A 开始第一次查询数据，查询的 SQL 语句如下：select * from student where id >= 1;在开始查询之前，MySQL 会为事务 A 产生一个 ReadView，此时 ReadView 的内容如下： trx_ids= [20,30] ， up_limit_id=20 ， low_limit_id=31 ， creator_trx_id=20 。

由于此时表 student 中只有一条数据，且符合 where id>=1 条件，因此会查询出来。然后根据 ReadView 机制，发现该行数据的trx_id=10，小于事务 A 的 ReadView 里 up_limit_id，这表示这条数据是事务 A 开启之前，其他事务就已经提交了的数据，因此事务 A 可以读取到。结论：事务 A 的第一次查询，能读取到一条数据，id=1。

步骤2：接着事务 B(trx_id=30)，往表 student 中新插入两条数据，并提交事务。

insert into student(id,name) values(2,'李四');
insert into student(id,name) values(3,'王五');

步骤3：接着事务 A 开启第二次查询，根据可重复读隔离级别的规则，此时事务 A 并不会再重新生成 ReadView。此时表 student 中的 3 条数据都满足 where id>=1 的条件，因此会先查出来。然后根据 ReadView 机制，判断每条数据是不是都可以被事务 A 看到。

• 首先 id=1 的这条数据，前面已经说过了，可以被事务 A 看到
• 然后是 id=2 的数据，它的 trx_id=30，此时事务 A 发现，这个值处于 up_limit_id 和 low_limit_id 之 间，因此还需要再判断 30 是否处于 trx_ids 数组内。由于事务 A 的 trx_ids=[20,30]，因此在数组内，这表 示 id=2 的这条数据是与事务 A 在同一时刻启动的其他事务提交的，所以这条数据不能让事务 A 看到
• 同理，id=3 的这条数据，trx_id 也为 30，因此也不能被事务 A 看见。

[外链图片转存中…(img-5nQaHOfv-1685002854322)]

结论：最终事务 A 的第二次查询，只能查询出 id=1 的这条数据。这和事务 A 的第一次查询的结果是一样 的，因此没有出现幻读现象，所以说在 MySQL 的可重复读隔离级别下，不存在幻读问题。

6、总结

这里介绍了 MVCC 在 READ COMMITTD 、 REPEATABLE READ 这两种隔离级别的事务在执行快照读操作时 访问记录的版本链的过程。这样使不同事务的 读-写 、 写-读 操作并发执行，从而提升系统性能。核心点在于 ReadView 的原理， READ COMMITTD 、 REPEATABLE READ 这两个隔离级别的一个很大不同 就是生成ReadView的时机不同：

• READ COMMITTD 在每一次进行普通SELECT操作前都会生成一个ReadView
• REPEATABLE READ 只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复 使用这个ReadView就好了

说明:我们之前说执行DELETE语句或者更新主键的UPDATE语句并不会立即把对应的记录完全从页面中删除,而是执行一个所谓的delete mark操作，相当于只是对记录打上了一个删除标志位，这主要就是为MVCC服务的。

通过MVCC我们可以解决:

• 读写之间阻塞的问题。通过MVcC 可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事务并发处理能力
• 降低了死锁的概率。这是因为MVCC 采雨了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁定必要的行
• 解决快照读的问题。当我们查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果

五、其他数据库日志

官网：https://dev.mysql.com/doc/refman/8.0/en/server-logs.html

1、MySQL支持的日志

1.1 日志类型

MySQL有不同类型的日志文件，用来存储不同类型的日志，分为 二进制日志 、 错误日志 、 通用查询日志 和 慢查询日志 ，这也是常用的4种。MySQL 8又新增两种支持的日志： 中继日志 和 数据定义语句日志 。使 用这些日志文件，可以查看MySQL内部发生的事情。这6类日志分别为：

• 慢查询日志：记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。
• 通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令， 对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。
• 错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的 状态，从而对服务器进行维护。
• 二进制日志：记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故 障时数据的无损失恢复。
• 中继日志：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。 从服务器通过读取中继日志的内容，来同步主服务器上的操作。
• 数据定义语句日志：记录数据定义语句执行的元数据操作。

除二进制日志外，其他日志都是 文本文件 。默认情况下，所有日志创建于 MySQL数据目录 中。

1.2 日志的弊端

• 日志功能会 降低MySQL数据库的性能 。例如，在查询非常频繁的MySQL数据库系统中，如果开启了通用查询日志和慢查询日志，MySQL数据库会花费很多时间记录日志。
• 日志会 占用大量的磁盘空间 。对于用户量非常大，操作非常频繁的数据库，日志文件需要的存储空间设置比数据库文件需要的存储空间还要大

2、通用查询日志(general query log)

慢查询日志(slow query log)前面章节《性能分析工具的使用》已经详细讲述

通用查询日志用来 记录用户的所有操作 ，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止 时间、发给 MySQL 数据库服务器的所有 SQL 指令等。当我们的数据发生异常时，查看通用查询日志， 还原操作时的具体场景，可以帮助我们准确定位问题。

2.1 查看当前状态

SHOW VARIABLES LIKE '%general%';

说明1∶系统变量general_log的值是OFF，即通用查询日志处于关闭状态。在MySQL中，这个参数的默认值是关闭的。因为一旦开启记录通用查询日志，MySQL会记录所有的连接起止和相关的SQL操作，这样会消耗系统资源并且占用磁盘空间。我们可以通过手动修改变量的值，在需要的时候开启日志。

说明2:通用查询日志文件的名称是atguigu01.log。存储路径是/var/lib/mysql/，默认也是数据路径。这样我们就知道在哪里可以查看通用查询日志的内容了

2.2 启动/停止日志

-- ===================永久性方式====================
-- 修改my.cnf或者my.ini配置文件来设置。在[mysqld]组下加入log选项，并重启MySQL服务
[mysqld]
general_log=ON
general_log_file=[path[filename]] #日志文件所在目录路径，filename为日志文件
-- 如果不指定目录和文件名，通用查询日志将默认存储在MySQL数据目录中的hostname.log文件中， hostname表示主机名。
-- 关闭的话直接选off即可-- ====================临时性方式=================
SET GLOBAL general_log=on; -- 开启通用查询日志
SET GLOBAL general_log_file='path/filename'; -- 设置日志文件保存位置
SET GLOBAL general_log=off; -- 关闭通用查询日志
SHOW VARIABLES LIKE 'general_log%';

2.3 查看日志

通用查询日志是以 文本文件 的形式存储在文件系统中的，可以使用 文本编辑器 直接打开日志文件。每台 MySQL服务器的通用查询日志内容是不同的。

• 在Windows操作系统中，使用文本文件查看器；
• 在Linux系统中，可以使用vi工具或者gedit工具查看；
• 在Mac OSX系统中，可以使用文本文件查看器或者vi等工具查看。

从 SHOW VARIABLES LIKE 'general_log%'; 结果中可以看到通用查询日志的位置。在通用查询日志里面，我们可以清楚地看到，什么时候开启了新的客户端登陆数据库，登录之后做了什么 SQL 操作，针对的是哪个数据表等信息。

2.4 删除\刷新日志

SHOW VARIABLES LIKE 'general_log%';
-- 使用如下命令重新生成查询日志文件，具体命令如下。
-- 刷新MySQL数据目录，发现创建了新的日志文 件。前提一定要开启通用日志。
-- 开启一个新的文件
mysqladmin -uroot -p flush-logs-- 备份旧的通用查询日志
cd mysql-data-directory -- 输入自己的通用日志文件所在目录
mv mysql.general.log mysql.general.log.old -- 指定旧的文件名 以及 新的文件名
mysqladmin -uroot -p flush-logs

3、错误日志(error log)

错误日志记录了MysQL服务器启动、停止运行的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示等。通过错误日志可以查看系统的运行状态，便于即时发现故障、修复故障。如果MySQL服务出现异常，错误日志是发现问题、解决故障的首选。

3.1 启动日志

在MySQL数据库中，错误日志功能是 默认开启 的。而且，错误日志 无法被禁止 。默认情况下，错误日志存储在MySQL数据库的数据文件夹下，名称默认为 mysqld.log （Linux系统）或 hostname.err （mac系统）。如果需要制定文件名，则需要在my.cnf或者my.ini中做如下配置：

[mysqld]
log-error=[path/[filename]] #path为日志文件所在的目录路径，filename为日志文件名

修改配置项后，需要重启MySQL服务以生效。

3.2 查看日志

MySQL错误日志是以文本文件形式存储的，可以使用文本编辑器直接查看

SHOW VARIABLES LIKE 'log_err%';
-- 执行结果中可以看到错误日志文件是mysqld.log，位于MySQL默认的数据目录下

3.3 删除\刷新日志

对于很久以前的错误日志，数据库管理员查看这些错误日志的可能性不大，可以将这些错误日志删除， 以保证MySQL服务器上的 硬盘空间 。MySQL的错误日志是以文本文件的形式存储在文件系统中的，可以 直接删除

-- 第一步（方式1）：删除操作
-- 在运行状态下删除错误日志文件后，MySQL并不会自动创建日志文件
rm -f /var/lib/mysql/mysqld.log
-- 第一步（方式2）：重命名文件
mv /var/log/mysqld.log /var/log/mysqld.log.old
-- 第二步：重建日志
mysqladmin -uroot -p flush-logs
-- 可能报错mysqladmin: refresh failed; error: 'Could not open file '/var/log/mysqld.log' for error logging.'
-- 补充 install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log

**flush-logs **指令操作:

• MySQL 5.5.7以前的版本，flush-logs将错误日志文件重命名为filename.err_old，并创建新的日志文件
• 从MySQL 5.5.7开始，flush-logs只是重新打开日志文件，并不做日志备份和创建的操作。
• 如果日志文件不存在，MySQL启动或者执行flush-logs时会自动创建新的日志文件。重新创建错误日志，大小为0字节。

4、二进制日志(bin log)

4.1 概述

binlog可以说是MySQL中比较 重要 的日志了，在日常开发及运维过程中，经常会遇到。binlog即binary log，二进制日志文件，也叫作变更日志（update log）。它记录了数据库所有执行的 DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、 show等）。它以事件形式记录并保存在二进制文件中。通过这些信息，我们可以再现数据更新操作的全过程。

如果想要记录所有语句（例如，为了识别有问题的查询），需要使用通用查询日志。

binlog主要应用场景：

• 一是用于数据恢复，如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。
• 二是用于数据复制，由于日志的延续性和时效性，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。

可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

4.2 查看默认情况

查看记录二进制日志是否开启：在MySQL8中默认情况下，二进制文件是开启的。

show variables like '%log_bin%';

• log_bin_basename:是binlog日志的基本文件名，后面会追加标识来表示每一个文件
• log_bin_index:是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录
• log_bin_trust_function_creators :限制存储过程，前面我们已经讲过了，这是因为二进制日志的一个重要功能是用于主从复制，而存储函数有可能导致主从的数据不一致。所以当开启二进制日志后，需要限制存储函数的创建、修改、调用
• log_bin_use_v1_row_events 此只读系统变量已弃用。ON表示使用版本1二进制日志行，OFF表示使用版本2二进制日志行(MySQL 5.6的默认值为2)。

4.3 日志参数设置

# 永久性方式
[mysqld]
#启用二进制日志
log-bin=atguigu-bin
binlog_expire_logs_seconds=600
max_binlog_size=100M

• log-bin=mysql-bin 打开日志(主机需要打开)，这个mysql-bin也可以自定义，这里也可以加上路径,如:/home/www/mysql_bin_log/mysql-bin
• binlog_expire_logs_seconds:此参数控制二进制日志文件保留的时长，单位是秒，默认2592000 30天（14400 4小时;86400 1天;259200 3天）
• max_binlog_size:控制单个二进制日志大小，当前日志文件大小超过此变量时，执行切换动作。此参数
  的最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，可能不做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束。一般情况下可采取默认值。

-- 重新启动MySQL服务，查询二进制日志的信息
show variables like '%log_bin%';
-- 设置带文件夹的bin-log日志存放目录
-- 如果想改变日志文件的目录和名称，可以对my.cnf或my.ini中的log_bin参数
[mysqld]
log-bin="/var/lib/mysql/binlog/atguigu-bin"
-- 注意：新建的文件夹需要使用mysql用户，使用下面的命令即可
chown -R -v mysql:mysql binlog
-- 数据库文件最好不要与日志文件放在同一个磁盘上!
-- 这样，当数据库文件所在的磁盘发生故障时，可以使用日志文件恢复数据。

如果不希望通过修改配置文件并重启的方式设置二进制日志的话，还可以使用如下指令，需要注意的是 在mysql8中只有 会话级别 的设置，没有了global级别的设置

-- session级别
-- 为OFF时，临时不记录binlog开关（增量恢复）某个时间点某些语句不记录binlog
SET sql_log_bin=0;

4.4 查看日志

当MySQL创建二进制日志文件时，先创建一个以“filename”为名称、以“.index”为后缀的文件，再创建一 个以“filename”为名称、以“.000001”为后缀的文件。MySQL服务 重新启动一次 ，以“.000001”为后缀的文件就会增加一个，并且后缀名按1递增。即日志文件的 个数与MySQL服务启动的次数相同；如果日志长度超过了 max_binlog_size 的上限（默认是1GB），就会创建一个新的日志文件。

-- 查看当前的二进制日志文件列表及大小
SHOW BINARY LOGS;

然后可以查看相关bin log日志

# 开始查看binlog
mysqlbinlog "/var/lib/mysql/binlog/atguigu-bin.0000002"
# 执行结果可以看到，这是一个简单的日志文件，日志中记录了用户的一些操作，这里并没有出现具体的SQL语句，这是因为binlog关键字后面的内容是经过编码后的二进制日志。
# 这里一个update语句包含如下事件
# Query事件负责开始一个事务(BEGIN)
# Table_map事件负责映射需要的表
# Update_rows事件负责写入数据
# Xid事件负责结束事务# 下面命令将行事件以伪SQL的形式表现出来
mysqlbinlog -v "/var/lib/mysql/binlog/atguigu-bin.000002"
# 前面的命令同时显示binlog格式的语句，使用如下命令不显示它
mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog/atguigu-bin.000002"# ===============其他命令=======================
# 可查看参数帮助
mysqlbinlog --no-defaults --help
# 查看最后100行
mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |tail -100
# 根据position查找
mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |grep -A 20 '4939002'# 上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];* `IN 'log_name'` ：指定要查询的binlog文件名（不指定就是第一个binlog文件）　 
# IN 'log_name' ：指定要查询的binlog文件名（不指定就是第一个binlog文件）　 
# FROM pos ：指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算） 
# LIMIT [offset] ：偏移量(不指定就是0) 
# row_count :查询总条数（不指定就是所有行）
mysql> show binlog events in 'atguigu-bin.000002';# flush logs命令的作用就是关闭当前使用的binary log，然后打开一个新的binary log文件，binlog文件的序号递增加1
flush logs

[外链图片转存中…(img-D8B7fTbZ-1685002854322)]

上面我们讲了这么多都是基于binlog的默认格式，binlog格式查看：show variables like 'binlog_format';

• Statement。每一条会修改数据的sql都会记录在binlog中。优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
• Row。5.1.5版本的MySQL才开始支持row level的复制，它不记录sql语句上下文相关信息，仅保存哪条记录被修改。优点：row level 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下 的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。
• Mixed。从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。

4.5 使用日志恢复数据

如果MySQL服务器启用了二进制日志，在数据库出现意外丢失数据时，可以使用MySQLbinlog工具从指定的时间点开始（例如，最后一次备份）直到现在或另一个指定的时间点的日志中回复数据。mysqlbinlog恢复数据的语法如下：

mysqlbinlog [option] filename|mysql –uuser -ppass;

这个命令可以这样理解：使用mysqlbinlog命令来读取filename中的内容，然后使用mysql命令将这些内容恢复到数据库中。

• filename ：是日志文件名。
• option ：可选项，比较重要的两对option参数是–start-date、–stop-date 和 --start-position、-- stop-position。
  • --start-date 和--stop-date ：可以指定恢复数据库的起始时间点和结束时间点。
  • --start-position和--stop-position ：可以指定恢复数据的开始位置和结束位置。

注意：使用mysqlbinlog命令进行恢复操作时，必须是编号小的先恢复，例如atguigu-bin.000001必须在atguigu-bin.000002之前恢复。

-- 例如按照位置
show binlog events in 'atguigu-bin.000002';
/usr/bin/mysqlbinlog --start-position884 --stop-position=1729 --database=atguigudb3 /var/lib/mysql/atguigu-bin.00005 | /user/bin/mysql -uroot -proot -v atguigu3-- 按照时间恢复
mysqlbinlog ' /var/lib/mysql/atgligu-bin.000002'/usr/bin/mysqlbinlog --start-datetime="2022-01-85 15:39:22" --stop-datetime="2022-01-35 15:40:1" --database=atguigu14 /var/lib/mysql/binlog/atguigu-bin.000085 | /usr/bin/mysql -uroot -pabc123 -v atguigu14

4.6 删除二进制日志

MySQL的二进制文件可以配置自动删除，同时MySQL也提供了安全的手动删除二进制文件的方法。 PURGE MASTER LOGS 只删除指定部分的二进制日志文件， RESET MASTER 删除所有的二进制日志文件

-- =========================1. PURGE MASTER LOGS：删除指定日志文件
PURGE {MASTER | BINARY} LOGS TO '指定日志文件名'
PURGE {MASTER | BINARY} LOGS BEFORE '指定日期'
-- 举例
-- 多次重新启动MysQL服务，便于生成多个日志文件。然后用SHOW语句显示二进制日志文件列表
SHOW BINARY LOGS;
-- 执行PURGE MASTER LOGS语句删除创建时间比binlog.000005早的所有日志
PURGE MASTER LOGS To "binlog.000005";
-- 执行mysqlbinlog命令查看二进制日志文件binlog.000005的内容
mysqlbinlog --no-defaults "/var/lib/mysql/binlog/atguigu-bin.008005"
-- 使用PURGE MASTER LOGS语句删除2022年1月05日前创建的所有日志文件
PURGE MASTER LOGS before "20220105";-- =========================2. RESET MASTER: 删除所有二进制日志文件
-- 使用RESET MASTER语句，清空所有的binlog日志。MySQL会重新创建二进制文件，新的日志文件扩展名将重新从000001开始编号。慎用!
RESET MASTER;

4.7 其它场景

二进制日志可以通过数据库的 全量备份 和二进制日志中保存的 增量信息 ，完成数据库的 无损失恢复 。 但是，如果遇到数据量大、数据库和数据表很多（比如分库分表的应用）的场景，用二进制日志进行数据恢复，是很有挑战性的，因为起止位置不容易管理。

在这种情况下，一个有效的解决办法是 配置主从数据库服务器 ，甚至是 一主多从 的架构，把二进制日志文件的内容通过中继日志，同步到从数据库服务器中，这样就可以有效避免数据库故障导致的数据异常等问题

5、深入二进制日志(binlog)

5.1 写入机制

binlog的写入时机也非常简单，事务执行过程中，先把日志写到 binlog cache ，事务提交的时候，再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。binlog日志刷盘流程如下：

[外链图片转存中…(img-wavmWTYP-1685002854322)]

• 上图的write，是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快
• 上图的fsync，才是将数据持久化到磁盘的操作

write和fsync的时机，可以由参数 sync_binlog 控制，默认是 0 。为0的时候，表示每次提交事务都只 write，由系统自行判断什么时候执行fsync。虽然性能得到提升，但是机器宕机，page cache里面的 binglog 会丢失。

为了安全起见，可以设置为 1 ，表示每次提交事务都会执行fsync，就如同redo log 刷盘流程一样。 最后还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync。在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。同样的，如果机器宕机，会丢失最近N个事务的binlog日志。

5.2 binlog与redolog对比

• redo log 它是 物理日志 ，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎层产生的。
• 而 binlog 是 逻辑日志 ，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于 MySQL Server 层。
• 虽然它们都属于持久化的保证，但是侧重点不同。
  • redo log让InnoDB存储引擎拥有了崩溃恢复能力。
  • binlog保证了MySQL集群架构的数据一致性。

5.3 两阶段提交

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的 写入时机 不一样。

[外链图片转存中…(img-M0ty0xTC-1685002854322)]

**redo log与binlog两份日志之间的逻辑不一致，会出现什么问题？**由于binlog没写完就异常，这时候binlog里面没有对应的修改记录。因此，之后用binlog日志恢复数据时，就会少这一次更新，恢复出来的最终数据不一致。

为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。原理很简单，将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交。

使用两阶段提交后，写入binlog时发生异常也不会有影响，因为MySQL根据redo log日志恢复数据时，发现redo log还处于prepare阶段，并且没有对应binlog日志，就会回滚该事务。

[外链图片转存中…(img-IfSxIYka-1685002854323)]

另一个场景，redo log设置commit阶段发生异常，那会不会回滚事务呢？答案并不会回滚事务，它会执行上图框住的逻辑，虽然redo log是处于prepare阶段，但是能通过事务id找到对应的binlog日志，所以MySQL认为是完整的，就会提交事务恢复数据。

6、中继日志(relay log)

6.1 介绍

中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入 本地的日志文件 中，这个从服务器本地的日志文件就叫 中继日志 。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主 从服务器的 数据同步 。

搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。文件名的格式是：从服务器名 -relay-bin.序号 。中继日志还有一个索引文件：从服务器名 -relaybin.index ，用来定位当前正在使用的中继日志。

6.2 查看中继日志

中继日志与二进制日志的格式相同，可以用 mysqlbinlog 工具进行查看

SET TIMESTAMP=1618558728/*!*/;
BEGIN
/*!*/;
# at 950
#210416 15:38:48 server id 1 end_log_pos 832 CRC32 0xcc16d651 Table_map:
`atguigu`.`test` mapped to number 91
# at 1000
#210416 15:38:48 server id 1 end_log_pos 872 CRC32 0x07e4047c Delete_rows: table id
91 flags: STMT_END_F -- server id 1 是主服务器，意思是主服务器删了一行数据
BINLOG '
CD95YBMBAAAAMgAAAEADAAAAAFsAAAAAAAEABGRlbW8ABHRlc3QAAQMAAQEBAFHWFsw=
CD95YCABAAAAKAAAAGgDAAAAAFsAAAAAAAEAAgAB/wABAAAAfATkBw==
'/*!*/;
# at 1040

这一段的意思是，主服务器（“server id 1”）对表 atguigu.test 进行了 2 步操作：定位到表 atguigu.test 编号是 91 的记录，日志位置是 832；删除编号是 91 的记录，日志位置是 872

6.3 恢复的典型错误

如果从服务器宕机，有的时候为了系统恢复，要重装操作系统，这样就可能会导致你的 服务器名称 与之前 不同 。而中继日志里是 包含从服务器名 的。在这种情况下，就可能导致你恢复从服务器的时候，无法 从宕机前的中继日志里读取数据，以为是日志文件损坏了，其实是名称不对了。解决的方法也很简单，把从服务器的名称改回之前的名称。

六、主从复制

1、主从复制概述

1.1 如何提升数据库并发能力

在实际工作中，我们常常将Redis作为缓存与MySQL配合来使用，当有请求的时候，首先会从缓存中进行查找，如果存在就直接取出。如果不存在再访问数据库，这样就提升了读取的效率，也减少了对后端数据库的访问压力。Redis的缓存架构是高并发架构中非常重要的一环。

此外，一般应用对数据库而言都是“ 读多写少 ”，也就说对数据库读取数据的压力比较大，有一个思路就是采用数据库集群的方案，做 主从架构 、进行 读写分离 ，这样同样可以提升数据库的并发处理能力。但并不是所有的应用都需要对数据库进行主从架构的设置，毕竟设置架构本身是有成本的。

如果我们的目的在于提升数据库高并发访问的效率，那么首先考虑的是如何 优化SQL和索引 ，这种方式 简单有效；其次才是采用 缓存的策略 ，比如使用 Redis将热点数据保存在内存数据库中，提升读取的效率；最后才是对数据库采用 主从架构 ，进行读写分离。
按照上面的方式进行优化，使用和维护的成本是由低到高的。

1.2 主从复制的作用

**第1个作用：读写分离。**我们可以通过主从复制的方式来同步数据，然后通过读写分离提高数据库并发处理能力。其中一个是Master主库，负责写入数据，我们称之为：写库。其他都是Slave从库，负责读取数据，我们称之为：读库。当主库进行更新的时候，会自动将数据复制到从库中，而我们在客户端读取数据的时候，会从从库进行读取。

面对“读多写少”的需求，采用读写分离的方式，可以实现更高的并发访问。同时，我们还能对从服务器进行负载均衡，让不同的读请求按照策略均匀地分发到不同的从服务器上，让读取更加顺畅。读取顺畅的另一个原因，就是减少了锁表的影响，比如我们让主库负责写，当主库出现写锁的时候，不会影响到从库进行SELECT的读取。

**第2个作用就是数据备份。**我们通过主从复制将主库上的数据复制到从库上，相当于一种热备份机制，也就是在主库正常运行的情况下进行的备份，不会影响到服务。

**第3个作用是具有高可用性。**数据备份实际上是一种冗余的机制，通过这种冗余的方式可以换取数据库的高可用性，也就是当服务器出现故障或宕机的情况下，可以切换到从服务器上，保证服务的正常运行。

关于高可用性的程度，我们可以用一个指标衡量，即正常可用时间/全年时间。比如要达到全年99.999%的时间都可用，就意味着系统在一年中的不可用时间不得超过3652460* (1-99.999%) =5.256分钟（含系统崩溃的时间、日常维护操作导致的停机时间等)，其他时间都需要保持可用的状态

2、主从复制的原理

Slave 会从 Master 读取 binlog 来进行数据同步

2.1 原理剖析

实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于 3 个线程 来操 作，一个主库线程，两个从库线程

[外链图片转存中…(img-u4nvGD6J-1685002854323)]

二进制日志转储线程 （Binlog dump thread）是一个主库线程。当从库线程连接的时候， 主库可以将二进 制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上 加锁 ，读取完成之后，再将锁释放掉。从库 I/O 线程 会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 （Relay log）。从库 SQL 线程 会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

[外链图片转存中…(img-cnUG05BV-1685002854323)]

注意：不是所有版本的MySQL都默认开启服务器的二进制日志。在进行主从同步的时候，我们需要先检查服务器是否已经开启了二进制日志。除非特殊指定，默认情况下从服务器会执行所有主服务器中保存的事件。也可以通过配置，使从服务器执行特定的事件。

复制三步骤
步骤1： Master 将写操作记录到二进制日志（ binlog ）。

步骤2： Slave 将 Master 的binary log events拷贝到它的中继日志（ relay log ）；

步骤3： Slave 重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，而且重启后从 接入点 开始复制。

复制的问题

复制的最大问题： 延时

2.2 复制的基本原则

• 每个 Slave 只有一个 Master
• 每个 Slave 只能有一个唯一的服务器ID
• 每个 Master 可以有多个 Slave

3、一主一从架构搭建

台 主机 用于处理所有 写请求 ，一台 从机 负责所有 读请求 ，架构图如下:

[外链图片转存中…(img-RqnJqCGs-1685002854323)]

3.1 准备工作

• 准备 2台 CentOS 虚拟机
• 每台虚拟机上需要安装好MySQL (可以是MySQL8.0 )

注意：克隆的方式需要修改新克隆出来主机的：① MAC地址 ② hostname ③IP 地址 ④ UUID 。此外，克隆的方式生成的虚拟机（包含MySQL Server），则克隆的虚拟机MySQL Server的UUID相同，必须修改，否则在有些场景会报错。比如： show slave status\G

# 修改MySQL Server 的UUID方式：
vim /var/lib/mysql/auto.cnf
systemctl restart mysqld

3.2 主机配置文件

建议mysql版本一致且后台以服务运行，主从所有配置项都配置在 [mysqld] 节点下，且都是小写字母

# =============必选============
#[必须]主服务器唯一ID
server-id=1
#[必须]启用二进制日志,指名路径。比如：自己本地的路径/log/mysqlbin
log-bin=atguigu-bin# ===============可选============
#[可选] 0（默认）表示读写（主机），1表示只读（从机）
read-only=0
#设置日志文件保留的时长，单位是秒
binlog_expire_logs_seconds=6000
#控制单个二进制日志大小。此参数的最大和默认值是1GB
max_binlog_size=200M
#[可选]设置不要复制的数据库
binlog-ignore-db=test
#[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
binlog-do-db=需要复制的主数据库名字
#[可选]设置binlog格式
binlog_format=STATEMENT

3.3 从机配置文件

要求主从所有配置项都配置在 my.cnf 的 [mysqld] 栏位下，且都是小写字母

#[必须]从服务器唯一ID
server-id=2
#[可选]启用中继日志
relay-log=mysql-relay
# 重启后台mysql服务，使配置生效

3.4 主机：建立账户并授权

-- 在主机MySQL里执行授权主从复制的命令
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'从机器数据库IP' IDENTIFIED BY 'abc123'; -- 注意：如果使用的是MySQL8，需要如下的方式建立账户，并授权slave:
CREATE USER 'slave1'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';
-- 此语句必须执行。否则见下面。
ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;-- 最后查询Master的状态，并记录下File和Position的值
show master status;
-- 注意：执行完此步骤后**不要再操作主服务器MySQL**，防止主服务器状态值变化。

3.5 从机：配置需要复制的主机

-- 步骤1：从机上复制主机的命令
CHANGE MASTER TO
MASTER_HOST='主机的IP地址',
MASTER_USER='主机用户名',
MASTER_PASSWORD='主机用户名的密码',
MASTER_LOG_FILE='mysql-bin.具体数字',
MASTER_LOG_POS=具体值;-- 举例
CHANGE MASTER TO MASTER_HOST='192.168.1.150',MASTER_USER='slave1',MASTER_PASSWORD='123456',MASTER_LOG_FILE='atguigu-bin.000007',MASTER_LOG_POS=154;-- 步骤2：启动slave同步
START SLAVE;-- 如果报错，可以执行如下操作，删除之前的relay_log信息。然后重新执行 CHANGE MASTER TO ...语句即可
reset slave;
-- 查看同步状态，如果Slave_IO_Running和Slave_SQL_Running都是yes说明成功
SHOW SLAVE STATUS\G;

如果失败，可以尝试从以下找原因

网络不通
账户密码错误
防火墙
mysql配置文件问题
连接服务器时语法
主服务器mysql权限

3.6 停止主从同步

• 停止主从同步命令：stop slave;

-- 从库如果停止从服务器复制功能，再使用需要重新配置主从
stop slave;
-- 主库删除Master中所有的binglog文件，并将日志索引文件清空，重新开始所有新的日志文件(慎用)
reset master; 

3.7 binlog格式详解

1、STATEMENT模式 （基于SQL语句的复制(statement-based replication, SBR)）

binlog_format=STATEMENT

每一条会修改数据的sql语句会记录到binlog中。这是默认的binlog格式

• SBR 的优点：
  • 历史悠久，技术成熟
  • 不需要记录每一行的变化，减少了binlog日志量，文件较小
  • binlog中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况
  • binlog可以用于实时的还原，而不仅仅用于复制
  • 主从版本可以不一样，从服务器版本可以比主服务器版本高
• SBR 的缺点：
  • 不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候
• 使用以下函数的语句也无法被复制：LOAD_FILE()、UUID()、USER()、FOUND_ROWS()、SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)
  • INSERT … SELECT 会产生比 RBR 更多的行级锁
  • 复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时，需要比 RBR 请求更多的行级锁
  • 对于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 语句会阻塞其他 INSERT 语句
  • 对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发 生变化的记录产生影响
  • 执行复杂语句如果出错的话，会消耗更多资源
  • 数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错

2、ROW模式（基于行的复制(row-based replication, RBR)）

binlog_format=ROW

5.1.5版本的MySQL才开始支持，不记录每条sql语句的上下文信息，仅记录哪条数据被修改了，修改成什么样了

• RBR 的优点：
  • 任何情况都可以被复制，这对复制来说是最 安全可靠 的。（比如：不会出现某些特定情况下 的存储过程、function、trigger的调用和触发无法被正确复制的问题）
  • 多数情况下，从服务器上的表如果有主键的话，复制就会快了很多
  • 复制以下几种语句时的行锁更少：INSERT … SELECT、包含 AUTO_INCREMENT 字段的 INSERT、 没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句
  • 执行 INSERT，UPDATE，DELETE 语句时锁更少
  • 从服务器上采用 多线程 来执行复制成为可能
• RBR 的缺点：
  • binlog 大了很多
  • 复杂的回滚时 binlog 中会包含大量的数据
  • 主服务器上执行 UPDATE 语句时，所有发生变化的记录都会写到 binlog 中，而 SBR 只会写一次，这会导致频繁发生 binlog 的并发写问题
  • 无法从 binlog 中看到都复制了些什么语句

3、MIXED模式（混合模式复制(mixed-based replication, MBR)）

binlog_format=MIXED

从5.1.8版本开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。在Mixed模式下，一般的语句修改使用statment格式保存binlog。如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog。

MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

3.8 其他

搭建主从复制：双主双从

一个主机m1用于处理所有写请求，它的从机s1和另一台主机m2还有它的从机s2负责所有读请求。当m1主机宕机后，m2主机负责写请求，m1、m2互为备机。结构图如下：
[外链图片转存中…(img-EXDKGb7D-1685002854324)]

4、同步数据一致性问题

4.1 问题介绍

主从同步的要求：

• 读库和写库的数据一致(最终一致)；
• 写数据必须写到写库；
• 读数据必须到读库(不一定)；

4.2 主从延迟问题

进行主从同步的内容是二进制日志，它是一个文件，在进行 网络传输 的过程中就一定会 存在主从延迟（比如 500ms），这样就可能造成用户在从库上读取的数据不是最新的数据，也就是主从同步中的 数据不一致性问题。

举例：导致主从延迟的时间点主要包括以下三个:主库A执行完成一个事务，写入binlog，我们把这个时刻记为T1；之后传给从库B，我们把从库B接收完这个binlog的时刻记为T2；从库B执行完成这个事务，我们把这个时刻记为T3。在网络正常的时候，日志从主库传给从库所需的时间是很短的，即T2-T1的值是非常小的。即，网络正常情况下，主备延迟的主要来源是备库接收完binlog和执行完这个事务之间的时间差。

**主备延迟最直接的表现是，从库消费中继日志（relay log）的速度，比主库生产binlog的速度要慢。**造成原因：

• 从库的机器性能比主库要差
• 从库的压力大
• 大事务的执行

**举例1：**一次性用delete语句删除太多数据

结论：后续再删除数据的时候，要控制每个事务删除的数据量，分成多次删除。

**举例2：**一次性用insert…select插入太多数据

**举例3：**大表DDL，比如在主库对一张500W的表添加一个字段耗费了10分钟，那么从节点上也会耗费10分钟。

4.3 如何减少主从延迟

• 降低多线程大事务并发的概率，优化业务逻辑
• 优化SQL，避免慢SQL， 减少批量操作 ，建议写脚本以update-sleep这样的形式完成。
• 提高从库机器的配置 ，减少主库写binlog和从库读binlog的效率差。
• 尽量采用 短的链路 ，也就是主库和从库服务器的距离尽量要短，提升端口带宽，减少binlog传输的网络延时。
• 实时性要求的业务读强制走主库，从库只做灾备，备份

4.4 解决一致性问题方法

如果操作的数据存储在同一个数据库中，那么对数据进行更新的时候，可以对记录加写锁，这样在读取的时候就不会发生数据不一致的情况。但这时从库的作用就是 备份 ，并没有起到 读写分离 ，分担主库 读压力 的作用。
读写分离情况下，解决主从同步中数据不一致的问题， 就是解决主从之间 数据复制方式 的问题，如果按照数据一致性 从弱到强 来进行划分，有以下 3 种复制方式。

方法 1：异步复制

异步模式就是客户端提交 COMMIT 之后不需要等从库返回任何结果，而是直接将结果返回给客户端，这样做的好处是不会影响主库写的效率，但可能会存在主库宕机，而Binlog还没有同步到从库的情况，也就是此时的主库和从库数据不一致。这时候从从库中选择一个作为新主，那么新主则可能缺少原来主服务器中已提交的事务。所以，这种复制模式下的数据一致性是最弱的。
[外链图片转存中…(img-HJ9hoO4E-1685002854324)]

方法 2：半同步复制

MySQL5.5版本之后开始支持半同步复制的方式。原理是在客户端提交COMMIT之后不直接将结果返回给客户端，而是等待至少有一个从库接收到了Binlog，并且写入到中继日志中，再返回给客户端。这样做的好处就是提高了数据的一致性，当然相比于异步复制来说，至少多增加了一个网络连接的延迟，降低了主库写的效率。

在MySQL5.7版本中还增加了一个rpl_semi_sync_master_wait_for_slave_count参数，可以对应答的从库数量进行设置，默认为1，也就是说只要有1个从库进行了响应，就可以返回给客户端。如果将这个参数调大，可以提升数据一致性的强度，但也会增加主库等待从库响应的时间。

[外链图片转存中…(img-4r5f4waz-1685002854324)]

方法 3：组复制

异步复制和半同步复制都无法最终保证数据的一致性问题，半同步复制是通过判断从库响应的个数来决定是否返回给客户端，虽然数据一致性相比于异步复制有提升，但仍然无法满足对数据一致性要求高的场景，比如金融领域。MGR 很好地弥补了这两种复制模式的不足。组复制技术，简称 MGR（MySQL Group Replication）。是 MySQL 在 5.7.17 版本中推出的一种新的数据复制技术，这种复制技术是基于 Paxos 协议的状态机复制。

MGR 是如何工作的

首先我们将多个节点共同组成一个复制组，在 执行读写（RW）事务 的时候，需要通过一致性协议层 （Consensus 层）的同意，也就是读写事务想要进行提交，必须要经过组里“大多数人”（对应 Node 节 点）的同意，大多数指的是同意的节点数量需要大于 （N/2+1），这样才可以进行提交，而不是原发起方一个说了算。而针对 只读（RO）事务 则不需要经过组内同意，直接 COMMIT 即可。在一个复制组内有多个节点组成，它们各自维护了自己的数据副本，并且在一致性协议层实现了原子消 息和全局有序消息，从而保证组内数据的一致性。

[外链图片转存中…(img-qQR4lxPb-1685002854324)]

MGR 将 MySQL 带入了数据强一致性的时代，是一个划时代的创新，其中一个重要的原因就是MGR 是基 于 Paxos 协议的。Paxos 算法是由 2013 年的图灵奖获得者 Leslie Lamport 于 1990 年提出的，有关这个算法的决策机制可以搜一下。事实上，Paxos 算法提出来之后就作为 分布式一致性算法 被广泛应用，比如 Apache 的 ZooKeeper 也是基于 Paxos 实现的。

5、知识延伸

在主从架构的配置中，如果想要采取读写分离的策略，我们可以自己编写程序 ，也可以通过 第三方的中间件 来实现。

• 自己编写程序的好处就在于比较自主，我们可以自己判断哪些查询在从库上来执行，针对实时性要 求高的需求，我们还可以考虑哪些查询可以在主库上执行。同时，程序直接连接数据库，减少了中间件层，相当于减少了性能损耗。
• 采用中间件的方法有很明显的优势，功能强大 ， 使用简单 。但因为在客户端和数据库之间增加了 中间件层会有一些 性能损耗 ，同时商业中间件也是有使用成本的。我们也可以考虑采取一些优秀的开源工具。

[外链图片转存中…(img-K20bv6PZ-1685002854324)]

七、数据库备份与恢复

1、概述

1.1 备份简介

在任何数据库环境中，总会有不确定的意外情况发生，比如例外的停电、计算机系统中的各种软硬件故障、人为破坏、管理员误操作等是不可避免的，这些情况可能会导致数据的丢失、服务器瘫痪等严重的后果。存在多个服务器时，会出现主从服务器之间的数据同步问题。
为了有效防止数据丢失，并将损失降到最低，应定期对MySQL数据库服务器做备份。如果数据库中的数据丢失或者出现错误，可以使用备份的数据进行恢复。主从服务器之间的数据同步问题可以通过复制功能实现。

1.2 物理备份与逻辑备份

**物理备份：**备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比较大，MySQL中可以用 xtrabackup 工具来进行物理备份。
**逻辑备份：**对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空间小，更灵活。MySQL 中常用的逻辑备份工具为 mysqldump 。逻辑备份就是 备份sql语句 ，在恢复的 时候执行备份的sql语句实现数据库数据的重现。

2、mysqldump实现逻辑备份

2.1 备份数据库

mysqldump命令执行时，可以将数据库备份成一个文本文件，该文件中实际上包含多个CREATE和INSERT语句，使用这些语句可以重新创建表和插入数据。

• 查出需要备份的表的结构，在文本文件中生成一个CREATE语句
• 将表中的所有记录转换为一条INSERT语句

# =====================备份一个数据库=======================
# 基本语法
mysqldump –u 用户名称 –h 主机名称 –p密码 待备份的数据库名称[tbname, [tbname...]]> 备份文件名称.sql
# 备份的文件并非一定要求后缀名为.sql，例如后缀名为.txt的文件也是可以的
mysqldump -uroot -p atguigu>atguigu.sql #备份文件存储在当前目录下
mysqldump -uroot -p atguigudb1 > /var/lib/mysql/atguigu.sql# =====================备份全部数据库========================
# 若想用mysqldump备份整个实例，可以使用 --all-databases 或 -A 参数
mysqldump -uroot -pxxxxxx --all-databases > all_database.sql
mysqldump -uroot -pxxxxxx -A > all_database.sql# ======================备份部分数据库=========================
# 使用 `--databases` 或 `-B` 参数了，该参数后面跟数据库名称，多个数据库间用空格隔开。如果指定 databases参数，备份文件中会存在创建数据库的语句，如果不指定参数，则不存在
mysqldump –u user –h host –p --databases [数据库的名称1 [数据库的名称2...]] > 备份文件名称.sql
mysqldump -uroot -p --databases atguigu atguigu12 >two_database.sql
mysqldump -uroot -p -B atguigu atguigu12 > two_database.sql

2.2 备份数据表

# ====================备份部分表=================================
mysqldump –u user –h host –p 数据库的名称 [表名1 [表名2...]] > 备份文件名称.sql
mysqldump -uroot -p atguigu book> book.sql# =====================备份单表的部分数据===========================
# 有些时候一张表的数据量很大，我们只需要部分数据。这时就可以使用 --where 选项了
mysqldump -uroot -p atguigu student --where="id < 10 " > student_part_id10_low_bak.sql# ========================排除某些表的备份===========================
# 如果我们想备份某个库，但是某些表数据量很大或者与业务关联不大，这个时候可以考虑排除掉这些表，同样的，选项 `--ignore-table` 可以完成这个功能
mysqldump -uroot -p atguigu --ignore-table=atguigu.student > no_stu_bak.sql
# 通过如下指定判定文件中没有student表结构
grep "student" no_stu_bak.sql

2.3 只备份结构或只备份数据

只备份结构的话可以使用 --no-data 简写为 -d 选项；只备份数据可以使用 --no-create-info 简写为 -t选项

# ======================只备份结构===================
mysqldump -uroot -p atguigu --no-data > atguigu_no_data_bak.sql
#使用grep命令，没有找到insert相关语句，表示没有数据备份。
grep "INSERT" atguigu_no_data_bak.sql# ======================只备份数据=====================
mysqldump -uroot -p atguigu --no-create-info > atguigu_no_create_info_bak.sql
#使用grep命令，没有找到create相关语句，表示没有数据结构。
grep "CREATE" atguigu_no_create_info_bak.sql

2.4 备份中包含存储过程、函数、事件

mysqldump备份默认是不包含存储过程，自定义函数及事件的。可以使用 --routines 或 -R 选项来备份存储过程及函数，使用 --events 或 -E 参数来备份事件

# 查看当前库有哪些存储过程或者函数
SELECT SPECIFIC_NAME,ROUTINE_TYPE,ROUTINE_SCHEMA FROM information_schema.Routines WHERE ROUTINE_SCHEMA="atguigu";
# 备份
mysqldump -uroot -p -R -E --databases atguigu > fun_atguigu_bak.sql
# 查询备份文件中是否存在函
grep -C 5 "rand_num" fun_atguigu_bak.sql

2.5 mysqldump常用选项

行帮助命令 mysqldump --help ，可以获得特定版本的完整选项列表。提示 如果运行mysqldump没有–quick或–opt选项，mysqldump在转储结果前将整个结果集装入内 存。如果转储大数据库可能会出现问题，该选项默认启用，但可以用–skip-opt禁用。如果使用最 新版本的mysqldump程序备份数据，并用于恢复到比较旧版本的MySQL服务器中，则不要使用–opt 或-e选项

--add-drop-database # 在每个CREATE DATABASE语句前添加DROP DATABASE语句。
--add-drop-tables # 在每个CREATE TABLE语句前添加DROP TABLE语句。
--add-locking # 用LOCK TABLES和UNLOCK TABLES语句引用每个表转储。重载转储文件时插入得更快。
--all-database, -A # 转储所有数据库中的所有表。与使用--database选项相同，在命令行中命名所有数据库。
--comment[=0|1] # 如果设置为0，禁止转储文件中的其他信息，例如程序版本、服务器版本和主机。--skip-comments与--comments=0的结果相同。默认值为1，即包括额外信息。
--compact # 产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、--skip-disable-keys和--skip-add-locking选项。
--compatible=name # 产生与其他数据库系统或旧的MySQL服务器更兼容的输出，值可以为ansi、MySQL323、MySQL40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_table_options或者no_field_options。
--complete_insert, -c # 使用包括列名的完整的INSERT语句。
--debug[=debug_options], -[debug_options] # 写调试日志。
--delete，-D # 导入文本文件前清空表。
--default-character-set=charset # 使用charsets默认字符集。如果没有指定，就使用utf8。
--delete-master-logs # 在主复制服务器上，完成转储操作后删除二进制日志。该选项自动启用-master-data。
--extended-insert，-e # 使用包括几个VALUES列表的多行INSERT语法。这样使得转储文件更小，重载文件时可以加速插入。
--flush-logs，-F # 开始转储前刷新MySQL服务器日志文件。该选项要求RELOAD权限。
--force，-f # 在表转储过程中，即使出现SQL错误也继续。
--lock-all-tables，-x # 对所有数据库中的所有表加锁。在整体转储过程中通过全局锁定来实现。该选项自动关闭--single-transaction和--lock-tables。
--lock-tables，-l # 开始转储前锁定所有表。用READ LOCAL锁定表以允许并行插入MyISAM表。对于事务表（例如InnoDB和BDB），--single-transaction是一个更好的选项，因为它根本不需要锁定表。
--no-create-db，-n # 该选项禁用CREATE DATABASE /*!32312 IF NOT EXIST*/db_name语句，如果给出--database或--all-database选项，就包含到输出中。
--no-create-info，-t # 只导出数据，而不添加CREATE TABLE语句。
--no-data，-d # 不写表的任何行信息，只转储表的结构。
--opt # 该选项是速记，它可以快速进行转储操作并产生一个能很快装入MySQL服务器的转储文件。该选项默认开启，但可以用--skip-opt禁用。
--password[=password]，-p[password] # 当连接服务器时使用的密码。
-port=port_num，-P port_num # 用于连接的TCP/IP端口号。
--protocol={TCP|SOCKET|PIPE|MEMORY} # 使用的连接协议。
--replace，-r –replace和--ignore # 控制替换或复制唯一键值已有记录的输入记录的处理。如果指定--replace，新行替换有相同的唯一键值的已有行；如果指定--ignore，复制已有的唯一键值的输入行被跳过。如果不指定这两个选项，当发现一个复制键值时会出现一个错误，并且忽视文本文件的剩余部分。
--silent，-s # 沉默模式。只有出现错误时才输出。
--socket=path，-S path # 当连接localhost时使用的套接字文件（为默认主机）。
--user=user_name，-u user_name # 当连接服务器时MySQL使用的用户名。
--verbose，-v # 冗长模式，打印出程序操作的详细信息。
--xml，-X # 产生XML输出。

3、mysql命令恢复数据

使用mysqldump命令将数据库中的数据备份成一个文本文件。需要恢复时，可以使用mysql命令来恢复备份的数据。mysql命令可以执行备份文件中的CREATE语句和INSERT语句。通过CREATE语句来创建数据库和表。通过INSERT语句来插入备份的数据。

mysql –u root –p [dbname] < backup.sql

其中，dbname参数表示数据库名称。该参数是可选参数，可以指定数据库名，也可以不指定。指定数据库名时，表示还原该数据库下的表。此时需要确保MySQL服务器中已经创建了该名的数据库。不指定数据库名，表示还原文件中所有的数据库。此时sql文件中包含有CREATE DATABASE语句，不需要MySQL服务器中已存在的这些数据库。

# ===========================单库备份中恢复单库=========================
# 如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称
mysql -uroot -p < atguigu.sql
# 否则需要指定数据库名称
mysql -uroot -p atguigu4< atguigu.sql# =========================全量备份恢复=============================
mysql -uroot -pxxxxxx < all.sql
# 说我们只想恢复某一个库，但是我们有的是整个实例的备份，这个时候我们可以从全量备份中分离出单个库的备份
#举例，分离完成后我们再导入atguigu.sql即可恢复单个库
sed -n '/^-- Current Database: `atguigu`/,/^-- Current Database: `/p' all_database.sql > atguigu.sql# ========================从单库备份中恢复单表========================
# 解释可以参考https://www.jianshu.com/p/5c56580c326a/
cat atguigu.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' > class_structure.sql
cat atguigu.sql | grep --ignore-case 'insert into `class`' > class_data.sql
#用shell语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复use atguigu;
mysql> source class_structure.sql;
mysql> source class_data.sql;

4、物理备份与恢复

4.1 物理备份

直接将MySQL中的数据库文件复制出来。这种方法最简单，速度也最快。MySQL的数据库目录位置不一 定相同：

• 在Windows平台下，MySQL 8.0存放数据库的目录通常默认为 “ C:\ProgramData\MySQL\MySQL Server 8.0\Data ”或者其他用户自定义目录；
• 在Linux平台下，数据库目录位置通常为/var/lib/mysql/；
• 在MAC OSX平台下，数据库目录位置通常为“/usr/local/mysql/data”

但为了保证备份的一致性。需要保证：

• 方式1：备份前，将服务器停止。
• 方式2：备份前，对相关表执行 FLUSH TABLES WITH READ LOCK 操作。这样当复制数据库目录中 的文件时，允许其他客户继续查询表。同时，FLUSH TABLES语句来确保开始备份前将所有激活的索 引页写入硬盘。

这种方式方便、快速，但不是最好的备份方法，因为实际情况可能 不允许停止MySQL服务器 或者 锁住表 ，而且这种方法 对InnoDB存储引擎 的表不适用。对于MyISAM存储引擎的表，这样备份和还原很方便，但是还原时最好是相同版本的MySQL数据库，否则可能会存在文件类型不同的情况。注意，物理备份完毕后，执行 UNLOCK TABLES 来结算其他客户对表的修改行为。

说明： 在MySQL版本号中，第一个数字表示主版本号，主版本号相同的MySQL数据库文件格式相同。

此外，还可以考虑使用相关工具实现备份。比如， MySQLhotcopy 工具。MySQLhotcopy是一个Perl脚本，它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。它是备份数据库或单个表最快的途径，但它只能运行在数据库目录所在的机器上，并且只能备份MyISAM类型的表。多用于mysql5.5之前。

4.2 物理恢复

步骤：

1）演示删除备份的数据库中指定表的数据

2）将备份的数据库数据拷贝到数据目录下，并重启MySQL服务器

3）查询相关表的数据是否恢复。需要使用下面的chown 操作

要求：

• 必须确保备份数据的数据库和待恢复的数据库服务器的主版本号相同。 因为只有MySQL数据库主版本号相同时，才能保证这两个MySQL数据库文件类型是相同的。
• 这种方式对 MyISAM类型的表比较有效 ，对于InnoDB类型的表则不可用。 因为InnoDB表的表空间不能直接复制。
• 在Linux操作系统下，复制到数据库目录后，一定要将数据库的用户和组变成mysql，命令如下：chown -R mysql.mysql /var/lib/mysql/dbname。其中，两个mysql分别表示组和用户；“-R”参数可以改变文件夹下的所有子文件的用户和组；“dbname”参数表示数据库目录

提示 ：Linux操作系统下的权限设置非常严格。通常情况下，MySQL数据库只有root用户和mysql用户 组下的mysql用户才可以访问，因此将数据库目录复制到指定文件夹后，一定要使用chown命令将 文件夹的用户组变为mysql，将用户变为mysql。

5、表的导出与导入

5.1 表的导出

1、使用SELECT…INTO OUTFILE导出文本文件

在MySQL中，可以使用SELECT…INTO OUTFILE语句将表的内容导出成一个文本文件。但是mysql默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定目录进行操作。

-- 查询secure_file_priv值
SHOW GLOBAL VARIABLES LIKE '%secure%';
-- 参数secure_file_priv的可选值和作用分别是:
-- 如果设置为empty，表示不限制文件生成的位置，这是不安全的设置;
-- 如果设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录;
-- 如果设置为NULL，就表示禁止在这个MySQL实例上执行select ... into outfile操作-- secure_file_priv变量的值为/var/lib/mysql-files/，导出目录设置为该目录
SELECT * FROM account INTO OUTFILE "/var/lib/mysql-files/account.txt";
-- 查看 /var/lib/mysql-files/account.txt文件

2、使用mysqldump命令导出文本文件

# 使用mysqldump命令将将atguigu数据库中account表中的记录导出到文本文件
mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account
# 使用mysqldump将atguigu数据库中的account表导出到文本文件，使用FIELDS选项，要求字段之 间使用逗号“，”间隔，所有字符类型字段值用双引号括起来
mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account --fields-terminated-by=',' --fields-optionally-enclosed-by='\"'

3、使用mysql命令导出文本文件

# 使用mysql语句导出atguigu数据中account表中的记录到文本文件
mysql -uroot -p --execute="SELECT * FROM account;" atguigu> "/var/lib/mysql-files/account.txt"# 将atguigu数据库account表中的记录导出到文本文件，使用--veritcal参数将该条件记录分为多行显示
mysql -uroot -p --vertical --execute="SELECT * FROM account;" atguigu > "/var/lib/mysql-files/account_1.txt"# 将atguigu数据库account表中的记录导出到xml文件，使用--xml参数
mysql -uroot -p --xml --execute="SELECT * FROM account;" atguigu>"/var/lib/mysqlfiles/account_3.xml"# 如果要将表数据导出到html文件中，可以使用 `--html` 选项

6.2 表的导入

1、使用LOAD DATA INFILE方式导入文本文件

-- 使用SELECT...INTO OUTFILE将atguigu数据库中account表的记录导出到文本文件
SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account_0.txt';
-- 刪除数据
DELETE FROM atguigu.account;
-- 从文本文件account.txt中恢复数据
LOAD DATA INFILE '/var/lib/mysql-files/account_0.txt' INTO TABLE atguigu.account;-- 选择数据库atguigu，使用SELECT…INTO OUTFILE将atguigu数据库account表中的记录导出到文本文件，使用FIELDS选项和LINES选项，要求字段之间使用逗号"，"间隔，所有字段值用双引号括起来
SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account_1.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '\"';
LOAD DATA INFILE '/var/lib/mysql-files/account_1.txt' INTO TABLE atguigu.account FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

2、使用mysqlimport方式导入文本文件

-- 导出文件account.txt，字段之间使用逗号"，"间隔，字段值用双引号括起来
SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '\"';
-- 使用mysqlimport命令将account.txt文件内容导入到数据库atguigu的account表中
mysqlimport -uroot -p atguigu '/var/lib/mysql-files/account.txt' --fields-terminated-by=',' --fields-optionally-enclosed-by='\"'

6、数据库迁移

6.1 概述

数据迁移（data migration）是指选择、准备、提取和转换数据，并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程。此外，验证迁移数据的完整性 和 退役原来旧的数据存储 ，也被认为是整个数据迁移过程的一部分。

数据库迁移的原因是多样的，包括服务器或存储设备更换、维护或升级，应用程序迁移，网站集成，灾难恢复和数据中心迁移。根据不同的需求可能要采取不同的迁移方案，但总体来讲，MySQL 数据迁移方案大致可以分为物理迁移和 逻辑迁移 两类。通常以尽可能 自动化 的方式执行，从而将人力资源从繁琐的任务中解放出来。

6.2 迁移方案

• 物理迁移

物理迁移适用于大数据量下的整体迁移。使用物理迁移方案的优点是比较快速，但需要停机迁移并且要 求 MySQL 版本及配置必须和原服务器相同，也可能引起未知问题。物理迁移包括拷贝数据文件和使用 XtraBackup 备份工具两种。

不同服务器之间可以采用物理迁移，我们可以在新的服务器上安装好同版本的数据库软件，创建好相同目录，建议配置文件也要和原数据库相同，然后从原数据库方拷贝来数据文件及日志文件，配置好文件组权限，之后在新服务器这边使用 mysqld 命令启动数据库。

• 逻辑迁移

逻辑迁移适用范围更广，无论是 部分迁移 还是 全量迁移 ，都可以使用逻辑迁移。逻辑迁移中使用最多的就是通过 mysqldump 等备份工具

6.3 迁移注意点

1、相同版本的数据库之间迁移注意点

指的是在主版本号相同的MySQL数据库之间进行数据库移动。

方式1： 因为迁移前后MySQL数据库的 主版本号相同 ，所以可以通过复制数据库目录来实现数据库迁移，但是物理迁移方式只适用于MyISAM引擎的表。对于InnoDB表，不能用直接复制文件的方式备份数据库。

方式2： 最常见和最安全的方式是使用 mysqldump命令 导出数据，然后在目标数据库服务器中使用 MySQL命令导入。

#host1的机器中备份所有数据库,并将数据库迁移到名为host2的机器上
mysqldump –h host1 –uroot –p –-all-databases| mysql –h host2 –uroot –p# “|”符号表示管道，其作用是将mysqldump备份的文件给mysql命令；
# “--all-databases”表示要迁移所有的数据库。通过这种方式可以直接实现迁移。

2、不同版本的数据库之间迁移注意点

例如，原来很多服务器使用5.7版本的MySQL数据库，在8.0版本推出来以后，改进了5.7版本的很多缺陷， 因此需要把数据库升级到8.0版本旧版本与新版本的MySQL可能使用不同的默认字符集，例如有的旧版本中使用latin1作为默认字符集，而最新版本的MySQL默认字符集为utf8mb4。如果数据库中有中文数据，那么迁移过程中需要对 默认字符集 进行修改 ，不然可能无法正常显示数据。

高版本的MySQL数据库通常都会 兼容低版本 ，因此可以从低版本的MySQL数据库迁移到高版本的MySQL 数据库。

3、不同数据库之间迁移注意点

不同数据库之间迁移是指从其他类型的数据库迁移到MySQL数据库，或者从MySQL数据库迁移到其他类 型的数据库。这种迁移没有普适的解决方法。

迁移之前，需要了解不同数据库的架构， 比较它们之间的差异 。不同数据库中定义相同类型的数据的 关键字可能会不同 。例如，MySQL中日期字段分为DATE和TIME两种，而ORACLE日期字段只有DATE；SQL Server数据库中有ntext、Image等数据类型，MySQL数据库没有这些数据类型；MySQL支持的ENUM和SET 类型，这些SQL Server数据库不支持。

另外，数据库厂商并没有完全按照SQL标准来设计数据库系统，导致不同的数据库系统的 SQL语句 有差别。例如，微软的SQL Server软件使用的是T-SQL语句，T-SQL中包含了非标准的SQL语句，不能和MySQL的SQL语句兼容。不同类型数据库之间的差异造成了互相 迁移的困难 ，这些差异其实是商业公司故意造成的技术壁垒。但 是不同类型的数据库之间的迁移并 不是完全不可能 。例如，可以使用MyODBC 实现MySQL和SQL Server之 间的迁移。MySQL官方提供的工具 MySQL Migration Toolkit 也可以在不同数据之间进行数据迁移。 MySQL迁移到Oracle时，需要使用mysqldump命令导出sql文件，然后， 手动更改 sql文件中的CREATE语句。

6.4 迁移小结

[外链图片转存中…(img-nGQcZdto-1685002854325)]

7、误删数据集合

传统的高可用架构是不能预防误删数据的，因为主库的一个drop table命令，会通过binlog传给所有从库和级联从库，进而导致整个集群的实例都会执行这个命令。为了找到解决误删数据的更高效的方法，我们需要先对和MySQL相关的误删数据，做下分类:

• 使用delete语句误删数据行;
• 使用drop table或者truncate table语句误删数据表;
• 使用drop database语句误删数据库;
• 使用rm命令误删整个MySQL实例。

7.1 delete：误删行

处理措施1：数据恢复

使用Flashback工具恢复数据。原理:修改binlog内容，拿回原库重放。如果误删数据涉及到了多个事务的话，需要将事务的顺序调过来再执行。使用前提:binlog_format=row和binlog_row_image=FULL。

处理措施2：预防

• 代码上线前，必须SQL审查、审计
• 建议可以打开安全模式，把sql_safe_updates参数设置为on。强制要求加where 条件且where后需要是索引字段，否则必须使用limit。否则就会报错。

7.2 truncate/drop ：误删库/表

背景:

delete全表是很慢的，需要生成回滚日志、写redo、写binlog。所以，从性能角度考虑，优先考虑使用truncate table或者drop table命令。使用delete命令删除的数据，你还可以用Flashback来恢复。而使用truncate /drop table和drop database命令删除的数据，就没办法通过Flashback来恢复了。因为，即使我们配置了binlog_format=row，执行这三个命令时，记录的binlog还是statement格式。binlog里面就只有一个truncate/drop语句，这些信息是恢复不出数据的。

方案:

这种情况下恢复数据，需要使用全量备份与增量日志结合的方式。方案的前提:有定期的全量备份，并且实时备份binlog。举例:有人误删了一个库，时间为下午3点。步骤如下:

• 取最近一次全量备份。假设设置数据库库是一天一备，最近备份数据是当天凌晨2点;
• 用备份恢复出一个临时库;（注意:这里选择临时库，而不是直接操作主库)
• 取出凌晨2点之后的binlog日志;
• 剔除误删除数据的语句外，其它语句全部应用到临时库。(前面讲过binlog的恢复)
• 最后恢复到主库

7.3 预防使用truncate/drop误删库/表

权限分离

• 限制帐户权限，核心的数据库，一般都不能随便分配写权限，想要获取写权限需要审批。比如只给业务开发人员DML权限，不给truncate/drop权限。即使是DBA团队成员，日常也都规定只使用只读账号，必要的时候才使用有更新权限的账号
• 不同的账号，不同的数据之间要进行权限分离，避免一个账号可以删除所有库

制定操作规范

比如在删除数据表之前，必须先对表做改名操作（比如加_to_be_deleted )。然后，观察一段时间，确保对业务无影响以后再删除这张表

设置延迟复制备库

简单的说延迟复制就是设置一个固定的延迟时间，比如1个小时，让从库落后主库一个小时。出现误删除操作1小时内，到这个备库上执行stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。这里通过CHANGE MASTER TO MASTER_DELAY = N命令，可以指定这个备库持续保持跟主库有N秒的延迟。比如把N设置为3600，即代表1个小时
此外，延迟复制还可以用来解决以下问题:

用来做延迟测试，比如做好的数据库读写分离，把从库作为读库，那么想知道当数据产生延迟的时候到底会发生什么，就可以使用这个特性模拟延迟。用于老数据的查询等需求，比如你经常需要查看某天前一个表或者字段的数值，你可能需要把备份恢复后进行查看，如果有延迟从库，比如延迟一周，那么就可以解决这样类似的需求。

7.4 rm：误删MySQL实例

对于一个有高可用机制的MySQL集群来说，不用担心 rm删除数据 了。只是删掉了其中某一个节点的数据的话，HA系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。我们要做的就是在这个节点上把数据恢复回来，再接入整个集群。
但如果是恶意地把整个集群删除，那就需要考虑跨机房备份，跨城市备份。

---

参考
https://www.bilibili.com/video/BV1iq4y1u7vj