Mysql45讲-实践（二）

April 14, 2022 20468

MySQL有哪些“饮鸩止渴”提高性能的方法？

业务高峰期，生产环境的 MySQL 压力太大，没法正常响应，需要短期内、临时性地提升一些性能。

短连接风暴

正常的短连接模式就是连接到数据库后，执行很少的 SQL 语句就断开，下次需要的时候再重连。

如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。

MySQL 建立连接的过程，成本是很高的。除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限。

短连接模型存在一个风险，就是一旦数据库处理得慢一些，连接数就会暴涨。

max_connections 参数，用来控制一个 MySQL 实例同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。对于被拒绝连接的请求来说，从业务角度看就是数据库不可用。

调高 max_connections 的值:

因为设计 max_connections 这个参数的目的是想保护 MySQL，如果我们把它改得太大，让更多的连接都可以进来，那么系统的负载可能会进一步加大，大量的资源耗费在权限验证等逻辑上，结果可能是适得其反，已经连接的线程拿不到 CPU 资源去执行业务的 SQL 请求。

  1. 第一种方法：先处理掉那些占着连接但是不工作的线程。

    对于那些不需要保持的连接，我们可以通过 kill connection 主动踢掉。这个行为跟事先设置 wait_timeout 的效果是一样的。设置 wait_timeout 参数表示的是，一个线程空闲 wait_timeout 这么多秒之后，就会被 MySQL 直接断开连接。

    但是需要注意，在 show processlist 的结果里，踢掉显示为 sleep 的线程，可能是有损的。

    应该优先断开事务外空闲的连接。

    看事务具体状态的话，你可以查 information_schema 库的 innodb_trx 表。

    trx_mysql_thread_id=n，表示 id=n 的线程还处在事务中。

    如果是连接数过多，你可以优先断开事务外空闲太久的连接；如果这样还不够，再考虑断开事务内空闲太久的连接。

    从服务端断开连接使用的是 kill connection + id 的命令， 一个客户端处于 sleep 状态时，它的连接被服务端主动断开后，这个客户端并不会马上知道。直到客户端在发起下一个请求的时候，才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”。

    从数据库端主动断开连接可能是有损的，尤其是有的应用端收到这个错误后，不重新连接，而是直接用这个已经不能用的句柄重试查询。这会导致从应用端看上去，“MySQL 一直没恢复”。

  2. 第二种方法：减少连接过程的消耗。

    有的业务代码会在短时间内先大量申请数据库连接做备用，如果现在数据库确认是被连接行为打挂了，那么一种可能的做法，是让数据库跳过权限验证阶段。

    跳过权限验证的方法是：重启数据库，并使用–skip-grant-tables 参数启动。这样，整个 MySQL 会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内。

    风险极高，特别不建议使用

慢查询性能问题

在 MySQL 中，会引发性能问题的慢查询，大体有以下三种可能：

  1. 索引没有设计好；

    这种场景一般就是通过紧急创建索引来解决。

    MySQL 5.6 版本以后，创建索引都支持 Online DDL 了，对于那种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行 alter table 语句。

    比较理想的是能够在备库先执行。假设你现在的服务是一主一备，主库 A、备库 B，这个方案的大致流程是这样的：

    1. 在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引；
    2. 执行主备切换；
    3. 这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，然后执行 alter table 语句加上索引。

  2. SQL 语句没写好；

    我们可以通过改写 SQL 语句来处理。MySQL 5.7 提供了 query_rewrite 功能，可以把输入的一种语句改写成另外一种模式。

  3. MySQL 选错了索引。

    使用查询重写功能，给原来的语句加上 force index

预先发现问题

  1. 上线前，在测试环境，把慢查询日志（slow log）打开，并且把 long_query_time 设置成 0，确保每个语句都会被记录入慢查询日志；
  2. 在测试表里插入模拟线上的数据，做一遍回归测试；
  3. 观察慢查询日志里每类语句的输出，特别留意 Rows_examined 字段是否与预期一致。

QPS 突增问题

有时候由于业务突然出现高峰，或者应用程序 bug，导致某个语句的 QPS 突然暴涨，也可能导致 MySQL 压力过大，影响服务。

由一个新功能的 bug 导致的，最理想的情况是让业务把这个功能下掉，服务自然就会恢复。

  1. 一种是由全新业务的 bug 导致的。假设你的 DB 运维是比较规范的，也就是说白名单是一个个加的。这种情况下，如果你能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。

  2. 如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的 QPS 就会变成 0。

  3. 如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的 SQL 语句直接重写成 select 1 返回。

    副作用:

    1. 如果别的功能里面也用到了这个 SQL 语句模板，会有误伤；
    2. 很多业务并不是靠这一个语句就能完成逻辑的，所以如果单独把这一个语句以 select 1 的结果返回的话，可能会导致后面的业务逻辑一起失败。

    风险极高，特别不建议使用

MySQL是怎么保证数据不丢的？

只要 redo log 和 binlog 保证持久化到磁盘，就能确保 MySQL 异常重启后，数据可以恢复。

binlog 的写入机制

事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入

binlog cache 的保存：

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。

binlog 写盘状态

每个线程有自己 binlog cache，但是共用同一份 binlog 文件

  1. 图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。
  2. 图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。
  1. sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
  2. sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
  3. sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。(对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。)

redo log 的写入机制

事务在执行过程中，生成的 redo log 是要先写到 redo log buffer 的。

  1. redo log buffer 里面的内容，是不是每次生成后都要直接持久化到磁盘呢？

    不需要

    如果事务执行期间 MySQL 发生异常重启，那这部分日志就丢了。由于事务并没有提交，所以这时日志丢了也不会有损失。

  2. 事务还没提交的时候，redo log buffer 中的部分日志有没有可能被持久化到磁盘呢？

    有可能

    MySQL redo log 存储状态

    1. 存在 redo log buffer 中，物理上是在 MySQL 进程内存中，就是图中的红色部分；
    2. 写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面，也就是图中的黄色部分；
    3. 持久化到磁盘，对应的是 hard disk，也就是图中的绿色部分。

    为了控制 redo log 的写入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它有三种可能取值：

    1. 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
    2. 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
    3. 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

    InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。

    注意，事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些 redo log 也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的 redo log，也是可能已经持久化到磁盘的。

实际上，除了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交的事务的 redo log 写入到磁盘中。

1. 一种是，redo log buffer 占用的空间即将达到 `innodb_log_buffer_size `**一半**的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。
2. 另一种是，并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 `innodb_flush_log_at_trx_commit` 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘

> 如果把 `innodb_flush_log_at_trx_commit` 设置成 1，那么 redo log 在 prepare 阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于 prepare 的 redo log，再加上 binlog 来恢复的。
>
> 每秒一次后台轮询刷盘，再加上崩溃恢复这个逻辑，InnoDB 就认为 redo log 在 commit 的时候就不需要 fsync 了，只会 write 到文件系统的 page cache 中就够了。

MySQL 的“双 1”配置

sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1

一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。

组提交（group commit）机制

日志逻辑序列号（log sequence number，LSN）:LSN 是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。

LSN 也会写到 InnoDB 的数据页中，来确保数据页不会被多次执行重复的 redo log。

redo log 组提交

三个并发事务 (trx1, trx2, trx3) 在 prepare 阶段，都写完 redo log buffer，持久化到磁盘的过程，对应的 LSN 分别是 50、120 和 160。

  1. trx1 是第一个到达的，会被选为这组的 leader；
  2. 等 trx1 要开始写盘的时候，这个组里面已经有了三个事务，这时候 LSN 也变成了 160；
  3. trx1 去写盘的时候，带的就是 LSN=160，因此等 trx1 返回时，所有 LSN 小于等于 160 的 redo log，都已经被持久化到磁盘；
  4. 这时候 trx2 和 trx3 就可以直接返回了。

一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好。

但如果只有单线程压测，那就只能一个事务对应一次持久化操作。

为了让一次 fsync 带的组员更多，MySQL 有一个很有趣的优化：拖时间

把 redo log 做 fsync 的时间拖到了步骤 1 之后

在执行第 4 步把 binlog fsync 到磁盘时，如果有多个事务的 binlog 已经写完了，也是一起持久化的，这样也可以减少 IOPS 的消耗。

不过通常情况下第 3 步执行得会很快，所以 binlog 的 write 和 fsync 间的间隔时间短，导致能集合到一起持久化的 binlog 比较少，因此 binlog 的组提交的效果通常不如 redo log 的效果那么好。

提升 binlog 组提交的效果:

可以通过设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 来实现。

  1. binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;
  2. binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。

只要有一个满足条件就会调用 fsync

WAL 机制是减少磁盘写，可是每次提交事务都要写 redo log 和 binlog，磁盘读写次数为什么没变少？

WAL 机制主要得益于两个方面：

  1. redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；
  2. 组提交机制，可以大幅度降低磁盘的 IOPS 消耗。

IO性能瓶颈，提升性能

  1. 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
  2. 将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。
  3. 将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。(不建议把 innodb_flush_log_at_trx_commit 设置成 0,因为把这个参数设置成 0，表示 redo log 只保存在内存中，这样的话 MySQL 本身异常重启也会丢数据，风险太大。而 redo log 写到文件系统的 page cache 的速度也是很快的，所以将这个参数设置成 2 跟设置成 0 其实性能差不多，但这样做 MySQL 异常重启时就不会丢数据了，相比之下风险会更小。)

问题：为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的？

MySQL 这么设计的主要原因是，binlog 是不能“被打断的”。一个事务的 binlog 必须连续写，因此要整个事务完成后，再一起写到文件里。

而 redo log 并没有这个要求，中间有生成的日志可以写到 redo log buffer 中。redo log buffer 中的内容还能“搭便车”，其他事务提交的时候可以被一起写到磁盘中。

MySQL是怎么保证主备一致的？

MySQL 主备的基本原理

基本的主备切换流程

MySQL 主备切换流程

在状态 1 中，客户端的读写都直接访问节点 A，而节点 B 是 A 的备库，只是将 A 的更新都同步过来，到本地执行。这样可以保持节点 B 和 A 的数据是相同的。

当需要切换的时候，就切成状态 2。这时候客户端读写访问的都是节点 B，而节点 A 是 B 的备库。

在状态 1 中，虽然节点 B 没有被直接访问，但是依然建议把节点 B（也就是备库）设置成只读（readonly）模式。这样做，有以下几个考虑：

  1. 有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；
  2. 防止切换逻辑有 bug，比如切换过程中出现双写，造成主备不一致；
  3. 可以用 readonly 状态，来判断节点的角色。

readonly 设置对超级 (super) 权限用户是无效的，而用于同步更新的线程，就拥有超级权限。

节点 A 到 B 这条线的内部流程是什么样的?

一个 update 语句在节点 A 执行，然后同步到节点 B 的完整流程图

主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写 binlog

备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。一个事务日志同步的完整过程是这样的：

  1. 在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
  2. 在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。
  3. 主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。
  4. 备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。
  5. sql_thread 读取中转日志，解析出日志里的命令，并执行。

binlog 的三种格式对比

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `t_modified`(`t_modified`)
) ENGINE=InnoDB;

insert into t values(1,1,'2018-11-13');
insert into t values(2,2,'2018-11-12');
insert into t values(3,3,'2018-11-11');
insert into t values(4,4,'2018-11-10');
insert into t values(5,5,'2018-11-09');

在表中删除一行数据的话， delete 语句的 binlog 是怎么记录的?

1
mysql> delete from t /*comment*/  where a>=4 and t_modified<='2018-11-10' limit 1;

  1. 当 binlog_format=statement 时，binlog 里面记录的就是 SQL 语句的原文

    执行

    1
    mysql> show binlog events in 'master.000001';

    查看 binlog 中的内容

    statement 格式 binlog 示例

    1. 第一行 SET @@SESSION.GTID_NEXT=’ANONYMOUS’你可以先忽略，后面文章我们会在介绍主备切换的时候再提到；
    2. 第二行是一个 BEGIN，跟第四行的 commit 对应，表示中间是一个事务；
    3. 第三行就是真实执行的语句了。可以看到，在真实执行的 delete 命令之前，还有一个“use ‘test’”命令。这条命令不是我们主动执行的，而是 MySQL 根据当前要操作的表所在的数据库，自行添加的。这样做可以保证日志传到备库去执行的时候，不论当前的工作线程在哪个库里，都能够正确地更新到 test 库的表 t。
    4. 最后一行是一个 COMMIT。（XID是用来联系bin log和redo log的。比如redo log里面有一个事务是prepare状态，但是不知道是不是commit状态，那就可以用XID去bin log里面查询该事务到底有没有提交。有提交则是commit状态，若没有提交则回滚该事务。）

  2. 当 binlog_format=row 时，binlog 里面记录的就是 事件

    row 格式 binlog 示例

    与 statement 格式的 binlog 相比，前后的 BEGIN 和 COMMIT 是一样的。但是，row 格式的 binlog 里没有了 SQL 语句的原文，而是替换成了两个 event：Table_map 和 Delete_rows。

    1. Table_map event，用于说明接下来要操作的表是 test 库的表 t;
    2. Delete_rows event，用于定义删除的行为。

    看不到详细信息需要借助 mysqlbinlog 工具，用命令

    1
    mysqlbinlog  -vv data/master.000001 --start-position=8900;

    解析和查看 binlog 中的内容

    row 格式 binlog 示例的详细信息

    1. server id 1，表示这个事务是在 server_id=1 的这个库上执行的
    2. 每个 event 都有 CRC32 的值，这是因为我把参数 binlog_checksum 设置成了 CRC32
    3. Table_map event 显示了接下来要打开的表，map 到数字 226。现在我们这条 SQL 语句只操作了一张表，如果要操作多张表呢？每个表都有一个对应的 Table_map event、都会 map 到一个单独的数字，用于区分对不同表的操作。
    4. 我们在 mysqlbinlog 的命令中，使用了 -vv 参数是为了把内容都解析出来，所以从结果里面可以看到各个字段的值（比如，@1=4、 @2=4 这些值）。binlog_row_image 的默认配置是 FULL，因此 Delete_event 里面，包含了删掉的行的所有字段的值。如果把 binlog_row_image 设置为 MINIMAL，则只会记录必要的信息，在这个例子里，就是只会记录 id=4 这个信息。
    5. 最后的 Xid event，用于表示事务被正确地提交了。

  3. 为什么会有 mixed 格式的 binlog？

    1. 因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。
    2. 但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。
    3. 所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。

循环复制问题

实际生产上使用比较多的是双 M 结构

MySQL 主备切换流程 -- 双 M 结构

节点 A 和 B 之间总是互为主备关系。这样在切换的时候就不用再修改主备关系。

循环复制问题:

业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。（参数 log_slave_updates 设置为 on，表示备库执行 relay log 后生成 binlog）。

那么，如果节点 A 同时是节点 B 的备库，相当于又把节点 B 新生成的 binlog 拿过来执行了一次，然后节点 A 和 B 间，会不断地循环执行这个更新语句，也就是循环复制了。

解决两个节点间的循环复制的问题：

MySQL 在 binlog 中记录了这个命令第一次执行时所在实例的 server id。

  1. 规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；
  2. 一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；
  3. 每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

MySQL是怎么保证高可用的？

MySQL 主备切换流程 -- 双 M 结构

主备延迟

与数据同步有关的时间点主要包括以下三个：

  1. 主库 A 执行完成一个事务，写入 binlog，我们把这个时刻记为 T1;
  2. 之后传给备库 B，我们把备库 B 接收完这个 binlog 的时刻记为 T2;
  3. 备库 B 执行完成这个事务，我们把这个时刻记为 T3。

主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是 T3-T1。

1
>show slave status

返回结果里面会显示 seconds_behind_master，用于表示当前备库延迟了多少秒

  1. 每个事务的 binlog 里面都有一个时间字段，用于记录主库上写入的时间；
  2. 备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到 seconds_behind_master。

主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产 binlog 的速度要慢。

主备延迟的来源

  1. 有些部署条件下，备库所在机器的性能要比主库所在的机器性能差。

  2. 备库的压力大

    因为主备可能发生切换，备库随时可能变成主库，所以主备库选用相同规格的机器，并且做对称部署，是现在比较常见的情况。

    备库上的查询耗费了大量的 CPU 资源，影响了同步速度，造成主备延迟。

    处理:

    1. 一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力。
    2. 通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力。

  3. 大事务

    因为主库上必须等事务执行完成才会写入 binlog，再传给备库。所以，如果一个主库上的语句执行 10 分钟，那这个事务很可能就会导致从库延迟 10 分钟。

  4. 备库的并行复制能力

可靠性优先策略

在双 M 结构下，从状态 1 到状态 2 切换的详细过程是这样的：

  1. 判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；
  2. 把主库 A 改成只读状态，即把 readonly 设置为 true；
  3. 判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；
  4. 把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
  5. 把业务请求切到备库 B。

MySQL 可靠性优先主备切换流程

这个切换流程中是有不可用时间的。因为在步骤 2 之后，主库 A 和备库 B 都处于 readonly 状态，也就是说这时系统处于不可写状态，直到步骤 5 完成后才能恢复。

可用性优先策略

把步骤 4、5 调整到最开始执行，也就是说不等主备数据同步，直接把连接切到备库 B，并且让备库 B 可以读写，那么系统几乎就没有不可用时间了。

把这个切换流程，暂时称作可用性优先流程。这个切换流程的代价，就是可能出现数据不一致的情况。

  1. 使用 row 格式的 binlog 时，数据不一致的问题更容易被发现。而使用 mixed 或者 statement 格式的 binlog 时，数据很可能悄悄地就不一致了。如果过了很久才发现数据不一致的问题，很可能这时的数据不一致已经不可查，或者连带造成了更多的数据逻辑不一致。
  2. 主备切换的可用性优先策略会导致数据不一致。因此，大多数情况下，建议使用可靠性优先策略。毕竟对数据服务来说的话，数据的可靠性一般还是要优于可用性的。

备库为什么会延迟好几个小时？

一个箭头代表了客户端写入主库，另一箭头代表的是备库上 sql_thread 执行中转日志（relay log）。

如果用箭头的粗细来代表并行度的话，第一个箭头要明显粗于第二个箭头。

日志在备库上的执行，就是图中备库上 sql_thread 更新数据 (DATA) 的逻辑。如果是用单线程的话，就会导致备库应用日志不够快，造成主备延迟。

在官方的 5.6 版本之前，MySQL 只支持单线程复制，由此在主库并发高、TPS 高时就会出现严重的主备延迟问题。

MySQL 多线程复制

把只有一个线程的 sql_thread，拆成多个线程。

coordinator 就是原来的 sql_thread, 不过现在它不再直接更新数据了，只负责读取中转日志和分发事务。真正更新日志的，变成了 worker 线程。而 work 线程的个数，就是由参数 slave_parallel_workers 决定的。

coordinator 在分发的时候，需要满足以下这两个基本要求：

  1. 不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个 worker 中。
  2. 同一个事务不能被拆开，必须放到同一个 worker 中。

按表分发策略

每个 worker 线程对应一个 hash 表，用于保存当前正在这个 worker 的“执行队列”里的事务所涉及的表。hash 表的 key 是“库名. 表名”，value 是一个数字，表示队列中有多少个事务修改这个表。

每个事务在分发的时候，跟所有 worker 的冲突关系包括以下三种情况：

  1. 如果跟所有 worker 都不冲突，coordinator 线程就会把这个事务分配给最空闲的 woker;
  2. 如果跟多于一个 worker 冲突，coordinator 线程就进入等待状态，直到和这个事务存在冲突关系的 worker 只剩下 1 个；
  3. 如果只跟一个 worker 冲突，coordinator 线程就会把这个事务分配给这个存在冲突关系的 worker。

问题：如果碰到热点表，比如所有的更新事务都会涉及到某一个表的时候，所有事务都会被分配到同一个 worker 中，就变成单线程复制了。

按行分发策略

要解决热点表的并行复制问题，就需要一个按行并行复制的方案。按行复制的核心思路是：如果两个事务没有更新相同的行，它们在备库上可以并行执行。

这个模式要求 binlog 格式必须是 row。

基于行的策略，事务 hash 表中还需要考虑唯一键，即 key 应该是“库名 + 表名 + 索引名 + 该索引的值”。

1
2
3
4
5
6
7
8
9
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB;

insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5);
  1. key=hash_func(db1+t1+“PRIMARY”+2), value=2; 这里 value=2 是因为修改前后的行 id 值不变，出现了两次。
  2. key=hash_func(db1+t1+“a”+2), value=1，表示会影响到这个表 a=2 的行。
  3. key=hash_func(db1+t1+“a”+1), value=1，表示会影响到这个表 a=1 的行。

相比于按表并行分发策略，按行并行策略在决定线程分发的时候，需要消耗更多的计算资源。

这两个方案其实都有一些约束条件：

  1. 要能够从 binlog 里面解析出表名、主键值和唯一索引的值。也就是说，主库的 binlog 格式必须是 row；
  2. 表必须有主键；不能有外键。
  3. 表上如果有外键，级联更新的行不会记录在 binlog 中，这样冲突检测就不准确。

MySQL 5.6 版本的并行复制策略

用于决定分发策略的 hash 表里，key 就是数据库名。

相比于按表和按行分发，这个策略有两个优势：

  1. 构造 hash 值的时候很快，只需要库名；而且一个实例上 DB 数也不会很多，不会出现需要构造 100 万个项这种情况。
  2. 不要求 binlog 的格式。因为 statement 格式的 binlog 也可以很容易拿到库名。

MariaDB 的并行复制策略

  1. 在一组里面一起提交的事务，有一个相同的 commit_id，下一组就是 commit_id+1；
  2. commit_id 直接写到 binlog 里面；
  3. 传到备库应用的时候，相同 commit_id 的事务分发到多个 worker 执行；
  4. 这一组全部执行完成后，coordinator 再去取下一批。

这个方案很容易被大事务拖后腿

MySQL 5.7 的并行复制策略

由参数 slave-parallel-type 来控制并行复制策略：

  1. 配置为 DATABASE，表示使用 MySQL 5.6 版本的按库并行策略；
  2. 配置为 LOGICAL_CLOCK，表示的就是类似 MariaDB 的策略

MySQL 5.7 并行复制策略的思想是：

  1. 同时处于 prepare 状态的事务，在备库执行时是可以并行的；
  2. 处于 prepare 状态的事务，与处于 commit 状态的事务之间，在备库执行时也是可以并行的。

MySQL 5.7.22 的并行复制策略

基于 WRITESET 的并行复制

新增了一个参数 binlog-transaction-dependency-tracking，用来控制是否启用这个新策略。参数的可选值有以下三种：

  1. COMMIT_ORDER，表示的就是前面介绍的，根据同时进入 prepare 和 commit 来判断是否可以并行的策略。
  2. WRITESET，表示的是对于事务涉及更新的每一行，计算出这一行的 hash 值，组成集合 writeset。如果两个事务没有操作相同的行，也就是说它们的 writeset 没有交集，就可以并行。(hash 值是通过“库名 + 表名 + 索引名 + 值”计算)
  3. WRITESET_SESSION，是在 WRITESET 的基础上多了一个约束，即在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序。

优势:

  1. writeset 是在主库生成后直接写入到 binlog 里面的，这样在备库执行的时候，不需要解析 binlog 内容（event 里的行数据），节省了很多计算量；
  2. 不需要把整个事务的 binlog 都扫一遍才能决定分发到哪个 worker，更省内存；
  3. 由于备库的分发策略不依赖于 binlog 内容，所以 binlog 是 statement 格式也是可以的。

约束：

对于“表上没主键”和“外键约束”的场景，WRITESET 策略是没法并行的，也会暂时退化为单线程模型。

主库出问题了，从库怎么办？

虚线箭头表示的是主备关系，也就是 A 和 A’互为主备， 从库 B、C、D 指向的是主库 A。一主多从的设置，一般用于读写分离，主库负责所有的写入和一部分读，其他的读请求则由从库分担。

主库发生故障，主备切换后的结果

一主多从基本结构 -- 主备切换

一主多从结构在切换完成后，A’会成为新的主库，从库 B、C、D 也要改接到 A’。正是由于多了从库 B、C、D 重新指向的这个过程，所以主备切换的复杂性也相应增加了。

基于位点的主备切换

当把节点 B 设置成节点 A’的从库的时候，需要执行一条 change master 命令：

1
2
3
4
5
6
7
CHANGE MASTER TO 
MASTER_HOST=$host_name 
MASTER_PORT=$port 
MASTER_USER=$user_name 
MASTER_PASSWORD=$password 
MASTER_LOG_FILE=$master_log_name 
MASTER_LOG_POS=$master_log_pos
  1. MASTER_HOST、MASTER_PORT、MASTER_USER 和 MASTER_PASSWORD 四个参数，分别代表了主库 A’的 IP、端口、用户名和密码。
  2. 参数 MASTER_LOG_FILE 和 MASTER_LOG_POS 表示，要从主库的 master_log_name 文件的 master_log_pos 这个位置的日志继续同步。而这个位置就是我们所说的同步位点，也就是主库对应的文件名和日志偏移量。

同步位点

节点 B 是 A 的从库，本地记录的也是 A 的位点。但是相同的日志，A 的位点和 A’的位点是不同的。因此，从库 B 要切换的时候，就需要先经过“找同步位点”这个逻辑。

考虑到切换过程中不能丢数据，所以总是要找一个“稍微往前”的，然后再通过判断跳过那些在从库 B 上已经执行过的事务。

主动跳过错误

在进行主从切换时，由于同步位点找的并不是很精确，就会出现如 主键冲突，停止同步 的错误

通常情况下，我们在切换任务的时候，要先主动跳过这些错误，有两种常用的方法

  1. 主动跳过一个事务

    1
    2
    set global sql_slave_skip_counter=1;
    start slave;

    因为切换过程中，可能会不止重复执行一个事务，所以我们需要在从库 B 刚开始接到新主库 A’时，持续观察，每次碰到这些错误就停下来，执行一次跳过命令，直到不再出现停下来的情况，以此来跳过可能涉及的所有事务。

  2. 通过设置 slave_skip_errors 参数，直接设置跳过指定的错误

    在执行主备切换时，有这么两类错误，是经常会遇到的：

    1. 1062 错误是插入数据时唯一键冲突；
    2. 1032 错误是删除数据时找不到行。

    可以把 slave_skip_errors 设置为 “1032,1062”，这样中间碰到这两个错误时就直接跳过

    这种直接跳过指定错误的方法，针对的是主备切换时，由于找不到精确的同步位点，所以只能采用这种方法来创建从库和新主库的主备关系。

GTID

MySQL 5.6 版本引入了 GTID

GTID 的全称是 Global Transaction Identifier，也就是全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。它由两部分组成，格式是：

1
GTID=server_uuid:gno
  • server_uuid 是一个实例第一次启动时自动生成的，是一个全局唯一的值；
  • gno 是一个整数，初始值是 1，每次提交事务的时候分配给这个事务，并加 1。

GTID 模式的启动也很简单，我们只需要在启动一个 MySQL 实例的时候，加上参数 gtid_mode=on 和 enforce_gtid_consistency=on

在 GTID 模式下，每个事务都会跟一个 GTID 一一对应。这个 GTID 有两种生成方式，而使用哪种方式取决于 session 变量 gtid_next 的值。

  1. 如果 gtid_next=automatic，代表使用默认值。这时，MySQL 就会把 server_uuid:gno 分配给这个事务。

    1. 记录 binlog 的时候，先记录一行 SET 

      1
      @@SESSION.GTID_NEXT=‘server_uuid:gno’;

    2. 把这个 GTID 加入本实例的 GTID 集合。

  2. 如果 gtid_next 是一个指定的 GTID 的值，比如通过 set gtid_next='current_gtid’ 指定为 current_gtid，那么就有两种可能：

    1. 如果 current_gtid 已经存在于实例的 GTID 集合中，接下来执行的这个事务会直接被系统忽略；
    2. 如果 current_gtid 没有存在于实例的 GTID 集合中，就将这个 current_gtid 分配给接下来要执行的事务，也就是说系统不需要给这个事务生成新的 GTID，因此 gno 也不用加 1。

一个 current_gtid 只能给一个事务使用。这个事务提交后，如果要执行下一个事务，就要执行 set 命令，把 gtid_next 设置成另外一个 gtid 或者 automatic。

基于 GTID 的主备切换

在 GTID 模式下，备库 B 要设置为新主库 A’的从库的语法如下：

1
2
3
4
5
6
CHANGE MASTER TO 
MASTER_HOST=$host_name 
MASTER_PORT=$port 
MASTER_USER=$user_name 
MASTER_PASSWORD=$password 
master_auto_position=1

master_auto_position=1 就表示这个主备关系使用的是 GTID 协议。

实例 A’的 GTID 集合记为 set_a，实例 B 的 GTID 集合记为 set_b。

我们在实例 B 上执行 start slave 命令，取 binlog 的逻辑是这样的：

  1. 实例 B 指定主库 A’，基于主备协议建立连接。
  2. 实例 B 把 set_b 发给主库 A’。
  3. 实例 A’算出 set_a 与 set_b 的差集，也就是所有存在于 set_a，但是不存在于 set_b 的 GTID 的集合，判断 A’本地是否包含了这个差集需要的所有 binlog 事务。
    1. 如果不包含，表示 A’已经把实例 B 需要的 binlog 给删掉了，直接返回错误；
    2. 如果确认全部包含，A’从自己的 binlog 文件里面，找出第一个不在 set_b 的事务，发给 B；
  4. 之后就从这个事务开始，往后读文件，按顺序取 binlog 发给 B 去执行。

在基于 GTID 的主备关系里，系统认为只要建立主备关系，就必须保证主库发给备库的日志是完整的。

读写分离有哪些坑？

读写分离基本结构

带 proxy 的读写分离架构

带 proxy 的读写分离架构

  1. 客户端直连方案，因为少了一层 proxy 转发，所以查询性能稍微好一点儿，并且整体架构简单，排查问题更方便。但是这种方案，由于要了解后端部署细节，所以在出现主备切换、库迁移等操作的时候，客户端都会感知到，并且需要调整数据库连接信息。你可能会觉得这样客户端也太麻烦了，信息大量冗余，架构很丑。其实也未必，一般采用这样的架构，一定会伴随一个负责管理后端的组件，比如 Zookeeper，尽量让业务端只专注于业务逻辑开发。
  2. 带 proxy 的架构，对客户端比较友好。客户端不需要关注后端细节，连接维护、后端信息维护等工作，都是由 proxy 完成的。但这样的话，对后端维护团队的要求会更高。而且，proxy 也需要有高可用架构。因此，带 proxy 架构的整体就相对比较复杂。

过期读问题：由于主从可能存在延迟，客户端执行完一个更新事务后马上发起查询，如果查询选择的是从库的话，就有可能读到刚刚的事务更新之前的状态。

处理过期读的方案：

  1. 强制走主库方案；
  2. sleep 方案；
  3. 判断主备无延迟方案；
  4. 配合 semi-sync 方案；
  5. 等主库位点方案；
  6. 等 GTID 方案。

强制走主库方案

强制走主库方案其实就是，将查询请求做分类

  1. 对于必须要拿到最新结果的请求，强制将其发到主库上。
  2. 对于可以读到旧数据的请求，才将其发到从库上。

Sleep 方案

主库更新后，读从库之前先 sleep 一下。

假设是，大多数情况下主备延迟在 1 秒之内，做一个 sleep 可以有很大概率拿到最新的数据。

判断主备无延迟方案

show slave status 结果里的 seconds_behind_master 参数的值，可以用来衡量主备延迟时间的长短。

  1. 每次从库执行查询请求前，先判断 seconds_behind_master 是否已经等于 0。如果还不等于 0 ，那就必须等到这个参数变为 0 才能执行查询请求。

  2. 对比位点确保主备无延迟

    1. Master_Log_File 和 Read_Master_Log_Pos，表示的是读到的主库的最新位点；
    2. Relay_Master_Log_File 和 Exec_Master_Log_Pos，表示的是备库执行的最新位点。

    如果 Master_Log_File 和 Relay_Master_Log_File、Read_Master_Log_Pos 和 Exec_Master_Log_Pos 这两组值完全相同，就表示接收到的日志已经同步完成。

  3. 对比 GTID 集合确保主备无延迟

    1. Auto_Position=1 ，表示这对主备关系使用了 GTID 协议。
    2. Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合；
    3. Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合。

    如果这两个集合相同，也表示备库接收到的日志都已经同步完成。

    问题：有时，binlog 在主备之间状态的分析中，不难看出还有一部分日志，处于客户端已经收到提交确认，而备库还没收到日志的状态。

配合 semi-sync

semi-sync 引入半同步复制，也就是 semi-sync replication

  1. 事务提交的时候，主库把 binlog 发给从库；
  2. 从库收到 binlog 以后，发回给主库一个 ack，表示收到了；
  3. 主库收到这个 ack 以后，才能给客户端返回“事务完成”的确认。

如果启用了 semi-sync，就表示所有给客户端发送过确认的事务，都确保了备库已经收到了这个日志。

semi-sync+ 位点判断的方案，只对一主一备的场景是成立的，在一主多从场景中，主库只要等到一个从库的 ack，就开始给客户端返回确认。这时，在从库上执行查询请求，就有两种情况：

  1. 如果查询是落在这个响应了 ack 的从库上，是能够确保读到最新数据；
  2. 但如果是查询落到其他从库上，它们可能还没有收到最新的日志，就会产生过期读的问题。

semi-sync 配合判断主备无延迟的方案，存在两个问题：

  1. 一主多从的时候，在某些从库执行查询请求会存在过期读的现象；
  2. 在持续延迟的情况下，可能出现过度等待的问题。

等主库位点方案

1
select master_pos_wait(file, pos[, timeout]);
  1. 它是在从库执行的；
  2. 参数 file 和 pos 指的是主库上的文件名和位置；
  3. timeout 可选，设置为正整数 N 表示这个函数最多等待 N 秒。

这个命令正常返回的结果是一个正整数 M，表示从命令开始执行，到应用完 file 和 pos 表示的 binlog 位置，执行了多少事务。

除了正常返回一个正整数 M 外，这条命令还会返回一些其他结果，包括：

  1. 如果执行期间，备库同步线程发生异常，则返回 NULL；
  2. 如果等待超过 N 秒，就返回 -1；
  3. 如果刚开始执行的时候，就发现已经执行过这个位置了，则返回 0。

GTID 方案

1
select wait_for_executed_gtid_set(gtid_set, 1);
  1. 等待，直到这个库执行的事务中包含传入的 gtid_set，返回 0；
  2. 超时返回 1。

在前面等位点的方案中，我们执行完事务后，还要主动去主库执行 show master status。而 MySQL 5.7.6 版本开始，允许在执行完更新类事务后，把这个事务的 GTID 返回给客户端，这样等 GTID 的方案就可以减少一次查询。

让 MySQL 在执行事务后，返回包中带上 GTID

需要将参数 session_track_gtids 设置为 OWN_GTID，然后通过 API 接口 mysql_session_track_get_first 从返回包解析出 GTID 的值即可。

查看评论