MySQL架构浅谈

MySQL基本架构

MySQL可以分为Server层和存储引擎层两部分。

Server层包括连接器、查询缓存、分析器、优化器、执行器等。所有的内置函数（如日期、时间、数学等函数）、所有的跨存储引擎功能（存储过程、触发器、视图等）都在这里。不同的存储引擎共用一个Server层。

存储层，负责数据的存储和提取，架构模式是插件式。支持 InnoDB、MyISAM、Memory 等多个存储引擎。最常用的存储引擎是 InnoDB，从 MySQL 5.5.5 版本开始已是默认存储引擎。

连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。

关于权限：

在验证用户名和密码后，连接器会到权限表里面查出登陆账号拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。所以，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

关于长短连接：

客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。建立连接过程复杂，尽量要减少建立连接动作，使用长连接。但是MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。可以通过两个方案来解决这个问题：一是定期断开长连接、二是执行一个较大操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存

MySQL 拿到一个查询请求后，会先到查询缓存看看。之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。

如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

问题：查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。缓存更适合静态表。MySQL 8.0 版本直接将查询缓存的整块功能删掉。

分析器

词法分析：针对输入的SQL语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。

语法分析：根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果SQL语句不对，就会收到“You have an error in your SQL syntax”的错误提醒。

优化器

优化器在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

执行器

首先判断权限，先判断一下账户对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限。

然后打开表执行，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

比如，对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之 后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的

数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

MySQL更新

对于一次查询语句，要经过连接器、分析器、优化器、执行器等功能模块，最后到达存储引擎。

对于一次更新语句，也要经过上述链路过程，只不过更新过程还涉及了两个重要的日志模块，redo log 重做日志、binlog归档日志。

redo log 重做日志

MySQL 里，如果每一次的更新操作都需要写进磁盘，从磁盘找到对应的那条记录再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。也即是WAL技术（Write-Ahead Logging），先写日志，等不忙的时候再写磁盘。

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候（往往是系统比较空闲时），将这个操作记录更新到磁盘里面。但是redo log大小是固定的，从头开始写，写到末尾就又回到开头循环写，这时为了保证不会被覆盖，在写满的时候，需要把记录更新到磁盘，才能继续从头开始写。也就是说擦除记录前要把记录更新到数据文件。

redo log功能是InnoDB特有的。因为有redo log功能，如果数据库发生异常重启，之前提交的记录都不会丢失，所以 InnoDB有 crash-safe 的能力。

innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不丢失。

redo buffer

redo log buffer 就是一块内存，用来先存 redo 日志的。

begin;
insert into t1 ...
insert into t2 ...
commit;

如上事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没 commit 的时候就直接写到 redo log 文件里。

在执行第一个 insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。
在执行commit语句时，真正把日志写到 redo log 文件（文件名是 ib_logfile+ 数字）。

事务执行过程中不会“主动去刷盘”，以减少不必要的 IO 消耗。但是可能会出现“被动写入磁盘”，比如内存不够、其他事务提交等情况。

binlog 归档日志

它与redolog的区别：

1、redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
2、redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
3、redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

最开始MySql自带的引擎是 MyISAM，没有crash-safe 能力，binlog 日志只能用于归档。后来出现的InnoDB， 用redo log 来实现 crash-safe 能力。

sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

两阶段提交

update T set c=c+1 where ID=2;

InnoDB 引擎在执行上述简单的 update 语句时的内部流程如下：

1、执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

2、执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

3、引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

4、执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

5、执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

这里我给出这个 update 语句的执行流程图，图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的。

这里的redo log是两阶段提交，是为了保证数据状态和用它的日志恢复出来的保持一致性。

如果先写binlog后写redo log。在写完binlog之后crash，由于redolog没有写，这个事务无效，数据重启恢复后这一行值依旧是更新前的，但是用binlog恢复的话会是更新后的。

如果先写redolog后写binlog。在写完redolog之后crash，由于redolog已写，这个事务有效，数据重启恢复后这一行值是更新后的，但是binlog没有写用binlog恢复的话会是更新之前的。

MySQL事务

事务定义

事务就是要保证一组数据库操作，要么全部成功，要么全部失败。MySQL 是一个支持多引擎的系统，但是只有InnoDB支持事务。

事务的特性：ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）。

事务隔离特性

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。

隔离的越高，效率就会越低。很多时候需要寻找一个平衡。
SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。这4种隔离级别，并行性能依次降低，安全性依次提高。

1、读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。（别人改数据的事务尚未提交，我在我的事务中也能读到。）

2、RC读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。（别人改数据的事务已经提交，我在我的事务中才能读到。）

3、RR可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。（别人改数据的事务已经提交，我在我的事务中也不去读。）银行对账场景用到。

4、串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。（我的事务尚未提交，别人就别想改数据。）

PS：数据库里有两个视图的概念，一个是view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样，此外view视图是有物理结构的。另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图（没有物理结构，通过高低水位，数据版本号，undo日记来进行判断数据可见不可见），即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。访问时以视图逻辑为准。

可以通过如下语句查看隔离级别：

mysql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+

关于读未提交：
该隔离级别下直接返回记录上的最新值，没有视图概念；

关于读提交：
该隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。Oracle库默认为读提交隔离级别。

关于可重复读：
该隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。MySQL库默认为可重复读隔离级别。

关于串行化：
该隔离级别下直接用加锁的方式来避免并行访问。

事务隔离的实现

数据库的多版本并发控制（MVCC），指的是 “维持一个数据的多个版本，使得读写操作没有冲突”。不同时刻启动的事务会有不同的 read-view。如下图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4。

在MySQL中，每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。也就是undo log 回滚日志。

undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。它采用段(segment)的方式来记录的，每个undo操作在记录的时候占用一个undo log segment。rollback segment称为回滚段，每个回滚段中有1024个undo log segment。

undo日志何时删除？当系统里没有比这个回滚日志更早的 read-view 的时候，就会删除。

也就是说，在事务结束前，因为事务随时可能访问数据库中的任何数据，所以数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。长事务意味着系统里面会存在很老的事务视图。所以建议尽量不使用长事务。此外长事务还占用锁资源，可能会拖垮库。

在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。

每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。

如下图所示，就是一个记录被多个事务连续更新后的状态。

图 中的三个虚线箭头，就是 undo log；而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的。比如，需要 V2 的时候，就是通过 V4 依次执行 U3、U2 算出来。

规则一：一致性读，事务A在启动后，不论在什么时候查询，看到同一行的数据的结果都是一致的（即使期间有其他事务将这行数据进行修改）

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：
过程中其他版本未提交，不可见；
过程中其他版本已提交，但是是在视图创建后提交的，不可见；
过程中其他版本已提交，而且是在视图创建前提交的，可见。

规则二：更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。当前读总是读取已经提交完成的最新版本。

以下图中的示例，假设在三个事务之前k=1，则：
事务A的查询语句，查询到的k值为1，用到上述规则一，一致性读。
事务 B 在更新之前查询一次数据，这个查询返回的 k 的值确实是 1，在更新后结果是3，这里用到上述规则二，更新数据的先读后写中的读是当前读。

注意：select 语句如果加锁，也是当前读。

如果把事务 A 的查询语句 select * from t where id=1 修改一下，加上 lock in share mode 或 for update，也都可以读到版本号是 101 的数据，返回的 k 的值是 3。

mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;

以下图中的示例，假设在三个事务之前k=1，则：
事务 C’更新后并没有马上提交，但是 (1,2) 这个版本也已经生成了，并且是当前的最新版本。在它提交前，事务 B 的更新语句先发起了。虽然事务 C’还没提交，那么，事务 B 的更新语句会怎么处理呢？这时候，“两阶段锁协议”就要上场了。
此时，由于事务 C’没提交，也就是说 (1,2) 这个版本上的写锁还没释放。而事务 B 是当前读，必须要读最新版本，而且必须加锁，因此就被锁住了，必须等到事务 C’释放这个锁，才能继续它的当前读。

总结一下：可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

上图示例中，在读提交隔离级别下，事务Aselect得到的k=2，事务Bselect得到的k=3。

问题：如下图，事务隔离级别是可重复读。现在，我要把所有“字段 c 和 id 值相等的行”的 c 值清零，但是却发现了一个“诡异”的、改不掉的情况。请你构造出这种情况，并说明其原理。

答案：在如下两种情况下会出现上述诡异情况。

事务的启动方式

1、显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。

2、set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。

有些客户端连接框架会默认连接成功后先执行一个 set autocommit=0 的命令。这就导致接下来的查询都在事务中，如果是长连接，就导致了意外的长事务。因此，建议总是使用 set autocommit=1, 通过显式语句的方式来启动事务。

commit work and chain 语法是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

3、事务的启动时间
（1）begin/start transaction 命令，并不是一个事务的起点，一致性视图是在执行第一个快照读语句时创建的，此时事务才是真正启动。

（2）start transaction with consistent snapshot 命令，一致性视图是在执行 start transaction with consistent snapshot 时创建的，如果想马上启动一个事务，可以使用这个。

如何查询各个表中的长事务?

可以在 information_schema 库的 innodb_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务。
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

如何避免长事务对业务的影响

从应用开发端来看：
1、确认是否使用了 set autocommit=0。这个确认工作可以在测试环境中开展，把 MySQL 的 general_log 开起来，然后随便跑一个业务逻辑，通过 general_log 的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成 1。
2、确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。我见过有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中。这种只读事务可以去掉。
3、控制语句执行最长时间。业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

从数据库端来看：
1、监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；
2、Percona 的 pt-kill 这个工具不错，推荐使用；
3、在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；
4、如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

参考：
https://time.geekbang.org/column/article/68319
https://time.geekbang.org/column/article/68963
https://time.geekbang.org/column/article/70848