1 MYSQL 基础架构

宋涛收录于存储

2020-03-01 约 9416 字预计阅读 19 分钟

一条 SQL 查询语句是如何执行的，一条更新语句又是如何执行的

今天是本系列的第一篇文章，借由MySQL查询和更新语句的执行，我们来说一说 MySQL 的一些重要的概念。内容包括:

查询语句的执行，我们说聊聊 MySQL 的基础架构
更新语句的执行，我们会介绍MySQL 非常重要的两个日志: redo log 和 bin log以及两阶段提交
与更新效率有关的 change buffer
此数据持久化相关的，刷脏页

1.查询语句的执行流程

1.1 MySQL 基础架构

上面是 MySQL 基础架构示意图。MySQL 可以分为 Server 层和存储引擎层两部分:

Server 包括:
- 连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能
- 包含了所有的内置函数（如日期、时间、数学和加密函数等）
- 所有跨存储引擎的功能，比如存储过程、触发器、视图等都在这一层实现
存储引擎层: 负责数据的存储和提取

1.2 连接器

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

权限获取:
- 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。
连接管理:
- 连接分为短连接，长连接，长时间空闲的链接连接称为空闲连接
- 空闲连接的保持时长由 wait_timeout 参数配置，默认为 8 小时，超时后连接就会自动断开
- 全部使用长连接后，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了，解决办法有如下两个:
  - 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
  - MySQL>=5.7，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

1.3 查询缓存

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。大多数情况下，建议按需启用查询缓存。

QUERY_CACHE_TYPE 配置参数用于控制是否启用查询缓存,可选值包括:

OFF: 不启用，显示指定 SQL_CACHE 也不会缓存
ON: 启用，可以使用 SQL_NO_CACHE 显示指定不缓存查询结果
DEMAND: 按需启用，即可以使用 SQL_CACHE 显示指定缓存查询结果
MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。

1.4 分析器

分析器，包括词法分析和语法分析，语法错误，会在此阶段爆出。

1.5 优化器

优化器主要是优化SQL语句的执行:

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

1.6 执行器

执行器负责执行语句

开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限 (在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限)
如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。
慢查询日志中记录有一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

2.更新语句的执行流程

首先，可以确定的说，查询语句的那一套流程，更新语句也是同样会走一遍。前面我们说过，在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表 T 上所有缓存结果都清空。这也就是我们一般不建议使用查询缓存的原因。清除缓存这一步就是在分析器中执行的。与查询流程不一样的是，更新流程还涉及两个重要的日志模块：redo log（重做日志）和 binlog（归档日志）。

2.1 redo log

为了提高磁盘的IO效率，避免大规模的随机IO，MySQL 采用一种叫做 WAL 技术(Write-Ahead Logging)它的关键点就是先写日志，再写磁盘:

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做
InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头
checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
write pos 和 checkpoint 之间是可用部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，这时候不能再执行新的更新，得停下来将数据更新到数据文件，把 checkpoint 推进一下

有了 redo log，InnoDB 既保证了即使数据库发生异常重启，之前提交的记录都不会丢失(数据已记录到文件，这个能力称为 crash-safe)，也避免了大规模的随机IO带来的效率低下。

2.2 bin log

redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。binlog 的主要作用有两个:

备份和数据恢复
主从同步的数据复制

bin log 与 redo log

redo log 是物理日志，记录的是“在某个数据页上做了什么修改”
binlog 是逻辑日志，记录的是这个语句的原始逻辑，用于数据归档。
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

2.3 Innodb 更新语句的执行流程

1

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

我们以上面更新语句为例，来看执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程。

执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

浅色框表示是在 InnoDB 内部执行的
深色框表示是在执行器中执行的。

两阶段提交

redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。要明白为什么 redo log 需要使用两阶段提交，我们需要明白下面几点:

redo log 与 bin log 存在的目的不同:
- redo log: 为了兼顾crash-safe 和IO效率
- bin log: 是为了备份和数据
mysql 要保证已经提交 commit 的事务数据不能丢失，这就包括事务提交后，mysql 服务奔溃数据不丢失，误操作进行数据恢复时或主从同步时数据不丢失，前者是 redo log 保证的，后者是 binlog 保证，因此需要保证redo log 与 bin log 之间数据一致
两阶段提交的目的是为了让两份日志之间的逻辑一致。

redo log/bin log 如何保证奔溃恢复

在上面更新语句的执行流程图中，标明了两阶段提交的两个不同时刻，我们来看看在两阶段提交的不同时刻，MySQL 异常重启会出现什么现象。

时刻A: 也就是写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃（crash），由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库。

时刻 B: 也就是 binlog 写完，redo log 还没 commit 前发生 crash。我们先来看一下崩溃恢复时的判断规则:

如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：
- 如果是，则提交事务；
- 否则，回滚事务。时刻 B 发生 crash 对应的就是 2(a) 的情况，崩溃恢复过程中事务会被提交。为什么这么设计，这时候 binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用。所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性。

2.4 一些常见问题

bin log 是如何保证完整性的

一个事务的 binlog 是有完整格式的：

statement 格式的 binlog，最后会有 COMMIT；
row 格式的 binlog，最后会有一个 XID event。

在 MySQL 5.6.2 版本以后，还引入了 binlog-checksum 参数，用来验证 binlog 内容的正确性。对于 binlog 日志由于磁盘原因，可能会在日志中间出错的情况，MySQL 可以通过校验 checksum 的结果来发现。

binlog redolog 是如何关联的

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

binlog 为什么不能做奔溃恢复

其中一个点是binlog 没有能力恢复“数据页”。假如事务提交了，也写入 binlog 了，但是数据在数据页级的丢失。此时，binlog 里面并没有记录数据页的更新细节，是补不回来的。redolog 是可以的，他记录了数据页的更新细节。

在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。

数据是从哪落盘

正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？

redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由 redo log 更新过去”的情况。

如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与 redo log 毫无关系。

redo log 设置多大

几个 TB 的磁盘的话，直接将 redo log 设置为 4 个文件、每个文件 1GB 吧。

redo log buffer

redo log buffer 是什么？是先修改内存，还是先写 redo log 文件？

在一个事务的更新过程中，日志是要写多次的，比如多次 insert。插入数据的过程中，生成的日志都得先保存起来，但又不能在还没 commit 的时候就直接写到 redo log 文件里。

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

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

更新相同的值

当 MySQL 去更新一行，但是要修改的值跟原来的值是相同的，这时候 MySQL 会真的去执行一次修改吗？还是看到值相同就直接返回呢？

MySQL 是否会更新，取决于MySQL是否读了数据，“读了数据，就会判断”。

update t set a=1 where id=1:

在这个语句里面，MySQL 认为读出来的值，只有一个确定的 (id=1), 而要写的是 (a=3)，只从这两个信息是看不出来“不需要修改”
因此InnoDB 认真执行了“把这个值修改成 (1,2)“这个操作，该加锁的加锁，该更新的更新。

update t set a=1 where id=1 and a=1:

在这个语句里面，MySQL 读出了 a 的值也做了判断
因此，Innodb 不会执行修改而是直接返回

面我们的验证结果都是在 binlog_format=statement 格式下进行的。如果是 binlog_format=row 并且 binlog_row_image=FULL 的时候，由于 MySQL 需要在 binlog 里面记录所有的字段，所以在读数据的时候就会把所有数据都读出来了。因此 update t set a=1 where id=1 中也会判断出 a=1，而不修改直接返回。同理，如果是 binlog_row_image=NOBLOB, 会读出除 blob 外的所有字段，在我们这个例子里，结果还是“返回 (1,2)”。

如果表中有 timestamp 字段而且设置了自动更新的话，那么更新“别的字段”的时候，MySQL 会读入所有涉及的字段，这样通过判断，就会发现不需要修改。

3. change buffer

除了 redo log，bin log 为了提高IO效率，mysql 还有一个重要的组件 change buffer。要想理解 change buffer 的作用我们要回到在前面 Innodb 的更新流程中来。

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，需要先从磁盘读入内存，在执行更新操作。但是将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。

在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

3.1 有 change buffer 的更新过程

假设我们要执行下面的插入语句:

1

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

我们假设当前 k 索引树的状态，查找到位置后，k1 所在的数据页在内存 (InnoDB buffer pool) 中，k2 所在的数据页不在内存中。如图 2 所示是带 change buffer 的更新状态图。

更新语句涉及四个部分，内存、redo log（ib_log_fileX）、数据表空间（t.ibd）、Innodb 全局表空间（ibdata1）。

t.ibd: 数据表空间存放着原始数据页
ibdata1: change buffer 在内存中有拷贝，也会被写入Innodb 的全局表空间中

如上图所示更新语句做了如下操作:

Page 1 在内存中，直接更新内存；
Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息
将上述两个动作记入 redo log 中（图中 3 和 4），注意两个动作在 redo log 记录的不同，一个是 To Page，一个是 new change buffer item

做完上面这些，事务就可以完成了。所以，你会看到，执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。图中的两个虚线箭头，是后台操作，不影响更新的响应时间。

3.2 更新后的读请求

我们现在要执行 select * from t where k in (k1, k2) 如果读语句发生在更新语句后不久，内存中的数据都还在，那么此时的这两个读操作就与系统表空间（ibdata1）和 redo log（ib_log_fileX）无关了。

读 Page 1 的时候，直接从内存返回
要读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的版本并返回结果。
写 redo log 包含了数据的变更和 change buffer 的变更。

可以看到，直到需要读 Page 2 的时候，这个数据页才会被读入内存。所以，如果要简单地对比这两个机制在提升更新性能上的收益的话，redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。数据读入内存是需要占用 buffer pool 的，所以 changer buffer还能够避免占用内存，提高内存利用率。

3.3 merge 的执行流程

上面我们介绍了单条记录 merge 的过程，但是 change buffer merge 的基本单位是磁盘页，merge 的执行流程是这样的：

从磁盘读入数据页到内存（老版本的数据页）
从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；
写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。

到这里 merge 过程就结束了。这时候，数据页和内存中 change buffer 对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据，就是另外一个过程了。

3.4 changer buffer 的丢失问题

如果某次写入使用了 change buffer 机制，之后主机异常重启，是否会丢失 change buffer 和数据。这个问题的答案是不会丢失，虽然是只更新内存，但是在事务提交的时候，我们把 change buffer 的操作也记录到 redo log 里了，所以崩溃恢复的时候，change buffer 也能找回来。

3.5 changer buffer 适用场景

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

因此对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。对于此类不适用 change buffer 的场景，应该关闭 changer buffer。

特别地，在使用机械硬盘时，change buffer 这个机制的收效是非常显著的。所以，当你有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

4. 刷脏页

通过上面的介绍，我们知道 InnoDB 在处理更新语句的时候，在更新内存写完 redo log 后，就返回给客户端，本次更新成功，数据并没有真正写入磁盘数据页。

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

mysql 终究是要把数据写入磁盘数据页，对应的就是把内存里的数据写入磁盘的过程，术语就是 flush。不难想象，平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。

4.1 什么时候会触发 flush

有四种情况会触发 flush 刷脏页:

InnoDB 的 redo log 写满了，这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。checkpoint 往前推进，就需要将对应的所有脏页都 flush 到磁盘上
系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
MySQL 认为系统“空闲”的时候
MySQL 正常关闭的情况，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快

我们一一来看这四种情况是如何触发 flush 刷脏页的

为什么淘汰内存时要 flush

淘汰内存时必须刷脏页了，是因为如果刷脏页一定会写盘，就保证了每个数据页有两种状态：

一种是内存里存在，内存里就肯定是正确的结果，直接返回；
另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。这样效率最高

4.2 flush 的性能影响

redo log 写满

这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为 0。

因此如果 redo log 文件设置的过小，这时候系统不得不停止所有更新，去推进 checkpoint。就会出现磁盘压力很小，但是数据库出现间歇性的性能下跌。，在这种情况下，连 change buffer 的优化也失效了。因为 checkpoint 一直要往前推，这个操作就会触发 merge 操作，然后又进一步地触发刷脏页操作；

内存不够用

这种情况其实是常态。InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：第一种是，还没有使用的；第二种是，使用了并且是干净页；第三种是，使用了并且是脏页。InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。

而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。

刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：

一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。

所以，InnoDB 需要有控制脏页比例的机制，来尽量避免上面的这两种情况。

4.3 InnoDB 刷脏页的控制策略

首先，你要正确地告诉 InnoDB 所在主机的 IO 能力，这样 InnoDB 才能知道需要全力刷脏页的时候，可以刷多快。

这就要用到 innodb_io_capacity 这个参数了，它会告诉 InnoDB 你的磁盘能力。这个值我建议你设置成磁盘的 IOPS。磁盘的 IOPS 可以通过 fio 这个工具来测试，下面的语句是我用来测试磁盘随机读写的命令：

1

 fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

因为没能正确地设置 innodb_io_capacity 参数，很可能会出现MySQL 的写入速度很慢，TPS 很低，但是数据库主机的 IO 压力并不大。如果这个是值设置的很低，InnoDB 认为这个系统的能力就这么差，所以刷脏页刷得特别慢，甚至比脏页生成的速度还慢，这样就造成了脏页累积，影响了查询和更新性能。

InnoDB 的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是 redo log 写盘速度。InnoDB 会根据这两个因素先单独算出两个数字。

脏页比例

innodb_max_dirty_pages_pct : 脏页比例上限，默认值是 75%。InnoDB 会根据当前的脏页比例（假设为 M），算出一个范围在 0 到 100 之间的数字，计算的伪代码如下:

1
2
3
4
5
6
7



F1(M)
{
  if M>=innodb_max_dirty_pages_pct then
      return 100;
  return 100*M/innodb_max_dirty_pages_pct;
}

redo log

InnoDB 每次写入的日志都有一个序号，当前写入的序号跟 checkpoint 对应的序号之间的差值，我们假设为 N。InnoDB 会根据这个 N 算出一个范围在 0 到 100 之间的数字，这个计算公式可以记为 F2(N) N 越大，算出来的值越大就好了。

刷盘速率

根据上述算得的 F1(M) 和 F2(N) 两个值，取其中较大的值记为 R，之后引擎就可以按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度。

4.3 脏页比例监控

InnoDB 会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到 MySQL“抖”了一下的原因。要尽量避免这种情况，你就要合理地设置 innodb_io_capacity 的值，并且平时要多关注脏页比例，不要让它经常接近 75%。

脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的，具体的命令参考下面的代码：

1
2
3
4
5
6



mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';

mysql> select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';

mysql> select @a/@b;

4.4 连带刷页

在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。

innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。

找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机 IO。机械硬盘的随机 IOPS 一般只有几百，相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。

SSD 这类 IOPS 比较高的设备的话，我就建议你把 innodb_flush_neighbors 的值设置成 0。在 MySQL 8.0 中，innodb_flush_neighbors 参数的默认值已经是 0 了

4.5 内存淘汰脏页，对应的 redo log 的操作

日志相关参数

redo log

innodb_flush_log_at_trx_commit:
- 作用: 事务提交之后多久更新 redo log
- 建议: 设置为 1 表示每次事务的 redo log 都直接持久化到磁盘。建议设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失

bin log

sync_binlog:
- 作用: 表示事务提交之后多久更新 bin log
- 建议: 设置为 1 表示每次事务的 binlog 都持久化到磁盘。建议设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失

change buffer

innodb_change_buffer_max_size:
- 作用: change buffer 用的是 buffer pool 里的内存，此参数用于控制 changer buffer 能够占用 buffer pool 最大百分比
- 示例: =50 表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

刷脏页

innodb_io_capacity: 设置磁盘的 IO 能力
innodb_max_dirty_pages_pct: 脏页比例的上线
innodb_flush_neighbors: 刷脏页时是否刷新邻居脏页

目录

1 MYSQL 基础架构

1.查询语句的执行流程

1.1 MySQL 基础架构

1.2 连接器

1.3 查询缓存

1.4 分析器

1.5 优化器

1.6 执行器

2.更新语句的执行流程

2.1 redo log

2.2 bin log

2.3 Innodb 更新语句的执行流程

两阶段提交

redo log/bin log 如何保证奔溃恢复

2.4 一些常见问题

bin log 是如何保证完整性的

binlog redolog 是如何关联的

binlog 为什么不能做奔溃恢复

数据是从哪落盘

redo log 设置多大

redo log buffer

更新相同的值

3. change buffer

3.1 有 change buffer 的更新过程

3.2 更新后的读请求

3.3 merge 的执行流程

3.4 changer buffer 的丢失问题

3.5 changer buffer 适用场景

4. 刷脏页

4.1 什么时候会触发 flush

为什么淘汰内存时要 flush

4.2 flush 的性能影响

redo log 写满

内存不够用

4.3 InnoDB 刷脏页的控制策略

脏页比例

redo log

刷盘速率

4.3 脏页比例监控

4.4 连带刷页

4.5 内存淘汰脏页，对应的 redo log 的操作

日志相关参数

redo log

bin log

change buffer

刷脏页