MySQL架构

  • 网络连接层

    客户端连接器（Client Connectors）：提供与MySQL服务器建立的支持，它们通过各自API技术与MySQL建立连接。

  • 服务层（MySQL Server）
    服务层是MySQL Server的核心，主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存六个部分。

    1
    2
    3
    4
    5
    6
    1. 连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接。[官网性能测试报告:引入线程池,性能稳定性与性能会有很大得提升,128并发,读写模式, mysql高出60倍,只读18倍,若不引用线程池,线程创建关闭性能消耗大]
    2. 系统管理和控制工具（Management Services & Utilities）：例如备份恢复、安全管理、集群管理等
    3. SQL接口（SQL Interface）：用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。
    4. 解析器（Parser）：负责将请求的SQL解析生成一个"解析树"。然后根据一些MySQL规则进一步检查解析树是否合法。
    5. 查询优化器（Optimizer）：当“解析树”通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互。
    6. 缓存（Cache&Buffer）： 缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

    SELECT uid, name FROMuser WHERE gender = 1;
    执行优化顺序
    1）select先根据where语句进行选取，并不是查询出全部数据再过滤
    2）select查询根据uid和name进行属性投影，并不是取出所有字段
    3）将前面选取和投影联接起来最终生成查询结果

  • 存储引擎层（Pluggable Storage Engines）
    存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异 。

    现在有很多种存储引擎，各有各的特点，最常见的是 InnoDB 和 MyISAM。

  • 系统文件层（File System）
    该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。

    MySQL的日志有很多种，如二进制日志、错误日志、查询日志、慢查询日志等，此外InnoDB存储引擎还提供了两种事务日志：redo log(重做日志)和undo log(回滚日志)。其中redo log用于保证事务持久性；undo log则是事务原子性和隔离性实现的基础。

日志

redo log

1、初识重做日志

  • 生命周期：

    事务开始之后，就开始产生 redo log 日志了，在事务执行的过程中，redo log 开始逐步落盘，当对应事务的脏页写入到磁盘之后，redo log 的使命就完成了，它所占用的空间也就可以被覆盖了。

  • 存储内容

    redo log 包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的，redo log 存储的是物理格式的日志，记录的是物理数据页面的修改信息，它是顺序写入 redo log file 中的。

  • 落盘方式（将 innodb 日志缓冲区的日志刷新到磁盘）

    • Master Thread 每秒一次执行刷新 Innodb_log_buffer 到重做日志文件
    • 每个事务提交时会将重做日志刷新到重做日志文件
    • 当重做日志缓存可用空间少于一半时，重做日志缓存被刷新到重做日志文件

2、实现原理

redo log和undo log都属于InnoDB的事务日志。下面先聊一下redo log存在的背景。

InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘IO，效率会很低。为此，InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。

Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。

于是，redo log被引入来解决这个问题：当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。

既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？主要有以下两方面的原因：

（1）刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。

（2）刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。

undo log

1、初识回滚日志

  • 生命周期

事务开始之前，将当前事务版本生成 undo log，undo log 也会产生 redo log 来保证 undo log 的可靠性。当事务提交之后，undo log 并不能立马被删除，而是放入待清理的链表，由 purge 线程判断是否有其它事务在使用 undo 段中表的上一个事务之前的版本信息，从而决定是否可以清理 undo log 的日志空间。

  • 存储内容

undo log 存储的是逻辑格式的日志，保存了事务发生之前的上一个版本的数据，可以用于回滚。当一个旧的事务需要读取数据时，为了能读取到老版本的数据，需要顺着 undo 链找到满足其可见性的记录。

  • 存储位置

默认情况下，undo 文件是保存在共享表空间的，也即 ibdatafile 文件中，当数据库中发生一些大的事务性操作的时候，要生成大量的 undo log 信息，这些信息全部保存在共享言七墨表空间中，因此共享表空间可能会变得很大，默认情况下，也就是 undo log 使用共享表空间的时候，被“撑大”的共享表空间是不会、也不能自动收缩的。因此，MySQL5.7 之后的“独立 undo 表空间”的配置就显得很有必要了。

2、实现原理

实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的sql语句。InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

undo log属于逻辑日志，它记录的是sql执行相关的信息。当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作：对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去。

以update操作为例：当事务执行update时，其生成的undo log中会包含被修改行的主键(以便知道修改了哪些行)、修改了哪些列、这些列在修改前后的值等信息，回滚时便可以使用这些信息将数据还原到update之前的状态。

binlog

1、初识二进制日志

binlog 用于主从复制中，从库利用主库上的 binlog 进行重播，实现主从同步。用于数据库的基于时间点、位点等的还原操作。binlog 的模式分三种：Statement、Row、Mixed。

binlog 的三种模式

  • 生命周期

事务提交的时候，一次性将事务中的 sql 语句（一个事务可能对应多个 sql 语句）按照一定的格式记录到 binlog 中，这里与 redo log 很明显的差异就是 redo log 并不一定是在事务提交的时候才刷新到磁盘，而是在事务开始之后就开始逐步写入磁盘。binlog 的默认保存时间是由参数 expire_logs_days 配置的，对于非活动的日志文件，在生成时间超过 expire_logs_days 配置的天数之后，会被自动删除

redo log与binlog

我们知道，在MySQL中还存在binlog(二进制日志)也可以记录写操作并用于数据的恢复，但二者是有着根本的不同的：

（1）作用不同：redo log是用于crash recovery的，保证MySQL宕机也不会影响持久性；binlog是用于point-in-time recovery的，保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制。

（2）层次不同：redo log是InnoDB存储引擎实现的，而binlog是MySQL的服务器层实现的，同时支持InnoDB和其他存储引擎。

（3）内容不同：redo log是物理日志，内容基于磁盘的Page；binlog的内容是二进制的，根据binlog_format参数的不同，可能基于sql语句、基于数据本身或者二者的混合。

（4）写入时机不同：binlog在事务提交时写入；redo log的写入时机相对多元：

  • 前面曾提到：当事务提交时会调用fsync对redo log进行刷盘；这是默认情况下的策略，修改innodb_flush_log_at_trx_commit参数可以改变该策略，但事务的持久性将无法保证。
  • 除了事务提交时，还有其他刷盘时机：如master thread每秒刷盘一次redo log等，这样的好处是不一定要等到commit时刷盘，commit速度大大加快。

ACID特性及其实现原理

原子性(Atomicity)：语句要么全执行，要么全不执行，是事务最核心的特性，事务本身就是以原子性来定义的；实现主要基于undo log

持久性(Consistency)：保证事务提交后不会因为宕机等原因导致数据丢失；实现主要基于redo log

隔离性(Isolation)：保证事务执行尽可能不受其他事务影响；InnoDB默认的隔离级别是RR，RR的实现主要基于锁机制（包含next-key lock）、MVCC（包括数据的隐藏列、基于undo log的版本链、ReadView）

一致性(Durability)：事务追求的最终目标，一致性的实现既需要数据库层面的保障，也需要应用层面的保障

原子性

1、定义

原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都成功，要么都失败；如果事务中一个sql语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态。

2、实现原理：undo log

持久性

1、定义

持久性是指事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

2、实现原理：redo log

隔离性

与原子性、持久性侧重于研究事务本身不同，隔离性研究的是不同事务之间的相互影响。隔离性是指，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。严格的隔离性，对应了事务隔离级别中的Serializable (可串行化)，但实际应用中出于性能方面的考虑很少会使用可串行化。

隔离性追求的是并发情形下事务之间互不干扰。简单起见，我们主要考虑最简单的读操作和写操作(加锁读等特殊读操作会特殊说明)，那么隔离性的探讨，主要可以分为两个方面：

  • (一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性
  • (一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性

一致性

数据库通过原子性（A）、隔离性（I）、持久性（D）来保证一致性（C）。其中一致性是目的，原子性、隔离性、持久性是手段。因此数据库必须实现AID三大特性才有可能实现一致性。

什么是MVCC机制

简介

MVCC是指多版本并发控制。MVCC是在并发访问数据库时，通过对数据进行多版本控制，避免因写锁而导致读操作的堵塞，从而很好的优化并发堵塞问题。解决并发问题的通用方案有：

（1）对并发访问的数据添加一把排它锁，添加锁之后，其他的读和写操作都需等待锁释放后才能访问。

（2）添加一把共享锁，读读操作不需要等待锁的释放，读写和写写操作需要等待锁的释放。

（3）通过对并发数据进行快照备份，从而达到无锁数据的并发访问。

通俗的讲就是MVCC通过对数据进行多版本保存，根据比较版本号来控制数据是否展示，从而达到读取数据时无需加锁就可以实现事务的隔离性。

示例

在事务 A 提交前后，事务 B 读取到的 x 的值是什么呢？答案是：事务 B 在不同的隔离级别下，读取到的值不一样。

  • 如果事务 B 的隔离级别是读未提交（RU），那么两次读取均读取到 x 的最新值，即 20。
  • 如果事务 B 的隔离级别是读已提交（RC），那么第一次读取到旧值 10，第二次因为事务 A 已经提交，则读取到新值 20。
  • 如果事务 B 的隔离级别是可重复读或者串行（RR/RS），则两次均读到旧值 10，不论事务 A 是否已经提交。

作用

InnoDB 相比 MyISAM 有两大特点，一是支持事务而是支持行级锁，事务的引入带来了一些新的挑战。相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况：

  • 更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题 —— 最后的更新覆盖了其他事务所做的更新。如何避免这个问题呢，最好在一个事务对数据进行更改但还未提交时，其他事务不能访问修改同一个数据。
  • 脏读（Dirty Reads）
  • 不可重复读（Non-Repeatable Reads）
  • 幻读（Phantom Reads）

实现隔离机制的方法主要有两种：

  • 加读写锁;
  • 一致性快照读，即MVCC;

实现原理

总体上来讲MVCC的实现是基于ReadView版本链以及Undo日志实现的。

ReadView(可读视图)

RR 下的 ReadView 生成

在 RR 隔离级别下，每个事务 touch first read 时（本质上就是执行第一个 SELECT 语句时，后续所有的 SELECT 都是复用这个 ReadView，其它 update, delete, insert 语句和一致性读 snapshot 的建立没有关系），会将当前系统中的所有的活跃事务拷贝到一个列表生成ReadView。

RC 下的 ReadView 生成

在 RC 隔离级别下，每个 SELECT 语句开始时，都会重新将当前系统中的所有的活跃事务拷贝到一个列表生成 ReadView。二者的区别就在于生成 ReadView 的时间点不同，RR是事务之后第一个 SELECT 语句开始、RC是事务中每条 SELECT 语句开始。

快照读和当前读

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的 trx_id 前的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

快照读：简单的select操作，属于快照读，不加锁。当然除了 排他锁(写锁)for update。

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

1、开启排他锁

1
2
3
START TRANSACTION;

SELECT * FROM `student` WHERE  sid = '01' FOR UPDATE;

2、其他事务修改数据

1
UPDATE student SET SNAME = '赵雷11' WHERE sid = '01';

3、commit - 释放锁，然后再次执行update

什么是脏读？幻读？不可重复读？

  • 脏读(Drity Read)：一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些尚未提交的脏数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做 “脏读”。
  • 不可重复读(Non-repeatable read): 一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了。
  • 幻读(Phantom Read): 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，

事务的隔离级别

  1. 读未提交(RU): 一个事务还没提交时, 它做的变更就能被别的事务看到.
  2. 读已提交(RC): 一个事务提交之后, 它做的变更才会被其他事务看到.
  3. 可重复读(RR): 一个事务执行过程中看到的数据, 总是跟这个事务在启动时看到的数据是一致的. 当然在可重复读隔离级别下, 未提交变更对其他事务也是不可见的.
  4. 串行化(S): 对于同一行记录, 读写都会加锁. 当出现读写锁冲突的时候, 后访问的事务必须等前一个 事务执行完成才能继续执行.

MySQL8查看当前事务隔离级别

1
2
3
select @@transaction_isolation;

-- select @@tx_isolation;8以下版本命令
1
2
3
4
5
6
7
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |   可重复读
+-------------------------+
1 row in set (0.04 sec)

修改事务隔离级别

1
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

注意：MySQL的InnoDB默认事务隔离级别是可重复读，不满足隔离性；Oracle默认的事务隔离级别为READ COMMITTED，不满足隔离性。

MySQL事务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 默认自动提交事务
mysql> show global variables like 'autocommit';  
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select @@global.autocommit;
+---------------------+
| @@global.autocommit |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

# 查看当前正在执行的事务，要 start transaction; 后执行select语句。
select * from information_schema.innodb_trx;

提交和回滚

典型的MySQL事务是如下操作的：

1
2
3
start transaction;
…… #一条或多条sql语句
commit;

其中start transaction标识事务开始，commit提交事务，将执行结果写入到数据库。如果sql语句执行出现问题，会调用rollback，回滚所有已经执行成功的sql语句。当然，也可以在事务中直接使用rollback语句进行回滚。

自动提交

MySQL中默认采用的是自动提交（autocommit）模式

在自动提交模式下，如果没有start transaction显式地开始一个事务，那么每个sql语句都会被当做一个事务执行提交操作。

通过如下方式，可以关闭autocommit；需要注意的是，autocommit参数是针对连接的，在一个连接中修改了参数，不会对其他连接产生影响。

如果关闭了autocommit，则所有的sql语句都在一个事务中，直到执行了commit或rollback，该事务结束，同时开始了另外一个事务。

特殊操作

在MySQL中，存在一些特殊的命令，如果在事务中执行了这些命令，会马上强制执行commit提交事务；如DDL语句(create table/drop table/alter/table)、lock tables语句等等。

不过，常用的select、insert、update和delete命令，都不会强制提交事务。

默认隔离级别(RR)下的两个事务

1、开启第一个事务

1
2
3
4
5
6
7
8
START TRANSACTION;

-- 第一select后才真正开启事务，有Readview快照读，当然我进行update/delete/insert这种也是能开启事务
SELECT * FROM `student`

UPDATE student SET SNAME = 'Lauy' WHERE sid = '01';

ROLLBACK;

2、在第一个事务开启后且update前开启第二个事务

1
2
3
4
5
6
7
8
9
10
11
START TRANSACTION;

SELECT * FROM `student`

-- 报错：> 1205 - Lock wait timeout exceeded; try restarting transaction 当前表被锁住了,等待持有锁的第一个事务释放才行。
UPDATE student SET SNAME = '赵雷' WHERE sid = '01' AND SNAME = '赵雷';

-- 修改其他表无影响
update sc c set c.CId = '02' WHERE c.SId = '01' LIMIT 1;

COMMIT;

可重复读基本下，第一个事务修该了数据，对第二个事务是不可见的，所以会出现明明我们看着数据是对的且条件成立，可就是无法修改。

读未提交(RU)

1、第一个事务开启并修改sid为01的sname为赵雷

1
2
3
4
5
6
-- 修改事务隔离级别为：READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

START TRANSACTION;

SELECT * FROM `student` WHERE  sid = '01';

1
2
3
4
-- 修改sid=1的值
UPDATE student SET SNAME = '赵雷' WHERE sid = '01';

-- COMMIT; 这里先不提交，我们看第二个事务可以看到步

2、第二个事务开启

1
SELECT * FROM `student` WHERE sid = '01'; -- 查询

可见在读未提交级别下，即便我们没有被commit的数据操作，其他事务下也可以看见。

读已提交(RC)

1、第一个事务开启，依旧修改sid=01的值

1
2
3
4
5
-- READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

SELECT * FROM `student` WHERE sid = '01';

2、修改sid为01的sname

1
UPDATE student SET SNAME = '赵雷' WHERE sid = '01';

3、第二个事务开启

1
2
3
4
5
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;

SELECT * FROM `student` WHERE  sid = '01';

4、第一个事务提交，后第二个事务再查询，就发现改成sname=赵磊了

1
SELECT * FROM `student` WHERE  sid = '01';

串行化

1
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

串行化是4种事务隔离级别中隔离效果最好的，解决了脏读、可重复读、幻读的问题，但是效果最差，它将事务的执行变为顺序执行，与其他三个隔离级别相比，它就相当于单线程，后一个事务的执行必须等待前一个事务结束。

Spring事务

aop，默认捕获运行时异常，如果在同一个类中，一个a事物方法调用当前类的另外的b事物方法 ，最后只会a方法创建事物，b方法不会。因为生成的代理类会调用a方法生成事物，但是使用 this.b 方法不会经过代理，可以采用 service.b 方法，这样就会经过 service的代理类新建事物。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
@PutMapping("/setRoles")
@ResponseBody
public Result updateRolesById(@RequestBody UserInfo userInfo) {
    userInfoService.updateUserById(userInfo);
    userInfoService.deleteById("1");
    // 捕获异常，事务失效，因为无法try catch
    // try {
    //     int i = 1 / 0;
    // } catch (Exception e) {
    //     e.printStackTrace();
    // }
    this.methodA(userInfo);
	return Result.success().setCode(200).setMsg("修改成功");
}

@Transactional
public void methodA(UserInfo userInfo) {
    System.out.println("A");
	userInfoService.findAll(userInfo);
}

@Transactional
public void methodB() {
	System.out.println("B");
}

mysql 网站执行的 SQL 语句日志

1
2
3
SHOW VARIABLES LIKE "general_log%";
-- 如果日志是OFF，说明没有打开日志记录，可以使用以下语句开启
SET GLOBAL general_log = 'ON'

如果日志是OFF，说明没有打开日志记录，可以使用以下语句开启

然后使用notepad ++打开以上日志地址，即可跟踪网站所执行的SQ命令

参考链接：

MySQL 中 redo log、undo log、binlog 的总结

MySQL架构二 MySQL体系架构

MySQL-MVCC机制

深入学习MySQL事务：ACID特性的实现原理