MySQL(21-25)

*21. 为什么我只改一行的语句，锁那么多？

加锁规则
上一节了解了间隙锁和next-key lock的概念，这一节我们从加锁规则开始。

前提说明：

• MySQL处于可重复读隔离级别。
• MySQL后面的版本可能会改变加锁策略，所以这个规则只限于截止到现在的最新版本，即5.x系列<=5.7.24，8.0系列<=8.0.13。

加锁规则，总结为两个原则、两个优化和一个bug：

1. 原则1：加锁的基本单位是next-key lock，next-key lock是前开后闭区间，(l, r]。
2. 原则2：查找过程中访问的对象才会加锁。
3. 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
4. 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
5. bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

继续以上一节的表t为例，来进行这一节的lab：

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

案例一：等值查询间隙锁
关于等值操作间隙：

图1. 等值查询的间隙锁

由于表t中没有id=7的记录，所以我们用上面提到的加锁规则判断一下的话：

1. 根据原则1，加锁单位是next-key lock，session A加锁范围就是(5, 10]；
2. 同时根据优化2，这是一个等值查询(id=7)，而id=10不满足查询条件，next-key lock退化成间隙锁，因此最终加锁的范围是(5, 10)。

所以，session B要往这个间隙里面插入id=8的记录会被锁住，但是session B修改id=10这行是可以的。

案例二：非唯一索引等值锁
关于覆盖索引上的锁：

图2. 只加在非唯一索引上的锁

这里session A要给索引c=5的这一行加上读锁，分析如下：

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

对于2，普通索引需要访问到第一条不满足条件的记录才会停，因为普通索引是允许有相同值的记录存在的。而唯一索引只要访问到了满足条件的记录就可以立马返回不继续便利了，因为肯定只有一个记录满足条件。但是唯一索引是范围查询时，因为bug问题，那么还会继续便利到第一条不满足条件的记录那里。

所以，session B的update语句可以执行，但是session C插入的(7,7,7)记录会被session A的间隙锁(5, 10)锁住。
需要注意，在这个例子中，lock in share mode只锁覆盖索引，但是如果是for update就不一样了。执行for update时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。
这个例子说明，锁是加在索引上的；同时，它给我们的指导是，如果你要用lock in share mode来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。比如，将session A的查询语句改成select d from t where c = 5 lock in share mode。

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

图3. 主键索引上范围查询的锁

下面对session A加的锁进行分析：

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

所以，session A这时候锁的范围就是主键索引上，行锁id=10和next-key lock(10, 15]。这样，session B和session C的结果你就能理解了。
这里，你需要注意一点，首次session A定位查找id=10的行的时候，是当做等值查询来判断的，而向右扫描到id=15的时候，用的是范围查询判断。

案例四：非唯一索引范围锁
关于普通索引的范围查询：

图4. 非唯一索引范围锁

这次session A用字段c来判断，加锁规则跟案例三唯一的不同是：在第一次用c=10定位记录的时候，索引c上加了(5, 10]这个next-key lock后，由于索引c是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终session A加的锁是，索引c上的(5, 10]和(10, 15]这两个next-key lock。

所以从结果上来看，session B要插入记录c=8会被堵住，session C更新的行c=15也会被堵住。

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

图5. 唯一索引范围所的bug

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

但是实现上，InnoDB会往前扫描第一个不满足条件的行为止，也就是id=20。而且由于这个是个范围扫描，因此索引id上的(15, 20]这个next-key lock也会被锁上。
所以，session B要更新id=20这一行，是会被锁住的，同样session C插入id=16的行会被堵住。
照理说，这里锁住id=20这一行为，其实是没有必要的，但是并未被verified，因此我认为这是个bug。

案例六：非唯一索引上存在“等值”的例子
这里，给表t插入一条新记录：

insert into t values(30,10,30);

新插入的这一行c=10，也就是说现在表里有两个c=10的行。那么，这时候索引c上的间隙是什么状态呢？你要知道，由于非唯一索引上包含主键的值，所以是不可能存在“相同”的两行的。

图6. 非唯一索引等值的例子

可以看到，虽然有两个c=10，但是它们的主键值id是不同的（分别是10和30），因此这两个c=10的记录之间，也是有间隙的。
为了跟间隙锁的开区间形式进行区别，我用(c=10,id=30)这样的形式，来表示索引上的一行。

这次试用delete语句来验证。注意，delete语句加锁的逻辑，其实跟select … for update是类似的。

图7. delete示例

这时，session A在遍历的时候，先访问第一个c=10的记录。同样地，根据原则1，这里加的是(c=5,id=5)到(c=10,id=10)这个next-key lock。
然后，session A向右查找，直到碰到(c=15,id=15)这一行，循环才结束。根据优化2，这是一个等值查询，向右查找了不满足条件的行，所以会退化成(c=10, id=10)到(c=15,id=15)的间隙锁。
也就是说，这个delete语句在索引c上的加锁范围，就是下图中灰色区域覆盖的部分。

图8. delete加锁效果示例

这个灰色区域左右两边都是虚线，表示开区间，即(c=5,id=5)和(c=15,id=15)这两行上都没有锁。

案例七：limit语句加锁
案例6的对照案例如下：

图9. limit语句加锁

这个例子里，session A的delete语句加了limit 2。表t中c=10的记录其实只有两条，因此加不加limit 2，删除的效果都是一样的，但是加锁的效果却不同。可以看到，session B的insert语句执行通过了，跟案例6的结果不同。
这是因为，案例七里的delete语句明确加了limit 2的限制，因此在遍历到(c=10, id=30)这一行之后，满足条件的语句已经有两条，循环就结束了。
因此，索引c上的加锁范围就变成从(c=5,id=5)到(c=10,id=30)这个前开后闭区间，如下图所示：

图10. 带limit2的加锁效果

可以看到，(c=10, id=30)之后的这个间隙并没有在加锁范围里，因此insert语句插入c=12是可以执行成功的。
这个例子对我们时间的知道意义就是，在删除数据的时候尽量加limit。这样不仅可以控制数据的条数，让操作更安全，还可以减少加锁的范围。

案例八：一个死锁的例子
前面的案例在分析的时候，是按照next-key lock的逻辑来分析的，因为这样分析比较方便。最后一个案例，目的是说明：next-key lock实际上是间隙锁和行锁加起来的结果。

图11. 案例八的操作序列

现在，我们按时间顺序来分析一下为什么是这样的结果。

1. session A启动事务后执行查询语句加lock in share mode，在索引c上加了next-key lock(5,10]和间隙锁(10,15)；
2. session B的update语句也要在索引c上加next-key lock(5,10]，进入锁等待；
3. 然后session A要再插入(8,8,8)这一行，被session B的间隙锁锁住。由于出现了死锁，InnoDB让session B回滚。

这里，session B的“加next-key lock(5,10]”操作，实际上分成了两步，先是加(5,10)的间隙锁，加锁成功；然后加c=10的行锁这时候才被锁住的。
也就是说，我们在分析加锁规则的时候可以用next-key lock来分析。但是具体执行的时候，是要分成间隙锁和行锁两段来执行的。

小结
可重复读隔离级别遵守两阶段锁协议，所有加锁的资源，都是在事务提交或者回滚的时候才释放的。
在最后的案例中国，可以清楚地知道next-key lock实际上是由间隙锁加行锁实现的。
如果切换到读提交级别的话，就好理解，过程中去掉间隙锁的部分，也就是只剩下行锁的部分。

小问题
还是以本节开始的初始化表t为例，里面有6条记录，在图5中的语句序列中，为什么session B的insert操作，会被锁住呢？

解析：
使用加锁规则分析一下session A：

1. 由于是order by c desc，第一个要定位的索引c上“最右边的”c=20行，所以会加上间隙锁(20, 25)和next-key lock(15, 20]。
2. 在索引c上向左遍历，要扫描到c=10才停下来，所以next-key lock会加到(5, 10]，这正是阻塞session B的insert语句的原因。
3. 在扫描过程中，c=20、c=15、c=10这三行都存在值，由于是select *，所以会在主键id上加上三个行锁。

因此session A的select语句加锁范围就是：

1. 索引c上(5, 25)；
2. 主键索引上id=10、15、20三个行锁。

这里，每次加锁需要说明是加在哪个索引上的。因为，锁就是加在索引上，这是InnoDB的一个基础设定。

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

场景引入
业务高峰期，生产环境的MySQL压力太大，没法增长响应，需要短期内、临时性地提升一些性能。
如果是无损方案的话，肯定不需要哦等待这个时候才上场。今天我们就聊聊这些临时方案，并着重说一说它们可能存在的风险。

短连接风暴
正常的短连接模式就是连接到数据库后，执行很少的SQL语句就断开，下次需要的时候再重连。如果使用的短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。
MySQL建立连接的过程，成本是很高的。除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限。
在数据库压力比较小的时候，这些额外的成本并不明显。
但是，短连接模型存在一个风险，就是一旦数据库处理得慢一些，连接数就会暴涨。
max_connections参数，用来控制一个MySQL实例同时存在的连接数的上限，超过这个值，系统就会拒绝下来的连接请求，并报错提示“Too may connections”。对于被拒绝连接的请求来说，从业务角度看就是数据库不可用。
在机器负载比较高的时候，处理现有请求的时间变长，每个连接保持的时间也更长。这时，再有新建连接的话，就可能会超过max_connections的限制。
碰到这种情况时，一个比较自然的想法，就是调高max_connections的值。但这样做是有风险的。因为设计max_connections这个参数的目的就是想保护MySQL，如果我们把它改得太大，让再多的连接都可以进来，那么系统的负载可能会进一步加大，大量的资源耗费在权限验证等逻辑上，结果可能是适得其反，已经连接的线程拿不到CPU资源去执行业务的SQL请求。

第一种方法：先处理那些占着连接但是不工作的线程
max_connections的计算，不是看谁在running，是只要连着就占用一个计数位置。对于那些不需要保持的连接，我们可以通过kill connection主动踢掉。这个行为跟实现设置wait_timeout的效果是一样的。设置wait_timeout参数表示的是，一个线程空闲wait_timeout这么多秒之后，就会被MySQL主动断开连接。
但是需要注意，在show processlist的结果里，踢掉显示未sleep的线程，可能是有损的。我们来看下面的例子。

图1. sleep线程的两种状态

在上面这个例子里，如果断开session A的连接，因为这时候session A还没有提交，所以MySQL只能按照回滚事务来处理；而断开session B的连接，就没什么大影响。所以，如果按照优先级来说，你应该优先判断像session B这样的事务外空闲的连接。
但是，怎么判断哪些是事务外空闲的呢？session C在T时刻执行show processlist，看到的结果是这样的。

图2. sleep线程的两种状态，show processlist结果

图中id=4和id=5的两个会话都是Sleep状态。而要看事务具体状态的话，你可以查information_schema库的innodb_trx表。

图3. 从information_schema.innodb_trx查询事务状态

这个结果里，trx_mysql_thread_id=4，表示id=4的线程还处在事务中。
因此，如果是连接数过多，你可以优先判断断开事务外空闲太久的连接；如果这样还不够，再考虑断开事务内空闲太久的连接。
从服务断开连接使用的是kill connection+id的命令，一个客户端处于Sleep状态时，它的连接被服务端主动断开后，这个客户端并不会马上知道。直到客户端在发起下一个请求的时候，才会收到这样的报错“ERROR 2013(HY000)：Lost connection to MySQL server during query”。
从数据库端断开连接可能是有损的，尤其是有的应用端收到这个错误后，不重新连接，而是直接用这个已经不能用的句柄重试查询。这会导致从应用端看上去，“MySQL一直没恢复”。
所以，如果你是一个支持业务的DBA，不要假设所有的应用代码都会被正确地处理。即时只是一个断开连接的操作，也要确保通知到业务开发团队。

第二种方法：减少连接过程的消耗
有的业务代码会在段时间内先大量申请数据库连接做备用，如果现在数据库确认是被连接行为打挂了，那么一种可能的做法，是让数据库跳过权限验证阶段。
跳过权限验证的方法是：重启数据库，并使用-skip-grant-tables参数启动。这样，整个MySQL会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内。
但是，这种方法特别符合我们标题里说的“饮鸩止渴”，风险极高，是特别不建议使用的方案。尤其当数据库允许外网访问的话，就更不能这么做了。
在MySQL8.0版本中，如果你启用-skip-grant-tables参数，MySQL会默认把-skip-networking参数打开，表示这时候数据库只能被本地的客户端连接。可见，MySQL官方对skip-grant-tables这个参数的安全问题也很重视。
除了短连接数暴增可能会带来性能问题外，实际上，我们在线上碰到更多的是查询或者更新语句导致的性能问题。其中，查询问题比较典型的有两类，一类是由新出现的慢查询导致的，一类是由QPS（每秒查询数）突增导致的。

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

1. 索引没有设计好；
2. SQL语句没写好；
3. MySQL选错了索引。

导致慢查询的第一种可能是，索引没有设计好。
这种场景一般就是通过紧急创建索引来解决。MySQL5.6版本以后，创建索引都支持OnlineDDL了，对于那种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是执行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语句加上索引。

这是一个古老的DDL方案。平时在做变更的时候，你应该考虑gh-ost这样的方案，更加稳妥。但是，在需要紧急处理时，上面这个方案的效率是最高的。

导致慢查询的第二种可能是，语句没写好。
我们可以通过改写SQL语句来处理。MySQL5.7提供了query_write功能，可以把输入的一种语句改写成另外一种模式。
比如，语句被错误地写成了select * from t where id + 1= 10000，你可以通过下面的方式，增加一个语句改写规则。

insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");
call query_rewrite.flush_rewrite_rules();

这里，call query_rewrite.flush_rewrite_rules()这个存储过程，是让插入的新规则生效，也就是我们说的“查询重写”。

图4. 查询重写效果

导致慢查询的第三种可能是，MySQL选错了索引。
这时候，应急方案就是给这个语句加上force index。
同样地，使用查询重写功能，给原来的语句加上force index，也可以解决这个问题。

上面讨论的由慢查询导致性能问题的三种可能情况，实际上出现最多的是前两种：索引设计和语句编写。而这两种情况，恰恰是完全可以避免的。比如，通过下面这个过程，我们就可以预先发现问题。

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

不要吝啬这段花在上线前的“额外”时间，因为这会帮你省下很多故障复盘的时间。
如果新增的SQL语句不多，手动跑一下就可以。而如果是新项目的话，或者是修改了原有项目的 表结构设计，全量回归测试都是必要的。这时候，你需要工具帮你检查所有的SQL语句的返回结 果。比如，你可以使用开源工具pt-query-digest。

QPS徒增问题
有时候由于业务突然出现高峰，或者应用程序bug，导致某个语句的QPS突然暴涨，也可能导致MySQL压力过大，影响服务。
如果问题是由一个新功能导致的，当然，最理想的情况是让业务把这个功能下掉，服务自然就会恢复。
而下掉一个功能 如果从数据库端处理的话，对应于不同的背景，有不同的方法可用。

1. 方案1：一种是由全新业务的bug导致的。假设你的DB运维是比较规范的，也就是说白名单是一个个加的。这种情况下，如果你能够确定业务方向会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。
2. 方案2：如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样新功能的连接不成功，由它引发的QPS就会变成0。
3. 方案3：如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的SQL语句直接重写成“select 1”返回。

当然，这个操作的风险很高，需要特别细致，它可能存在两个副作用：

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

所以，方案3是用于止血的，跟前面提到的去掉权限验证一样，应该是你所有选项里优先级最低的一个方案。
同时，方案1和2都要依赖于规范的运维体系：虚拟化、白名单机制、业务账号分离。由此可见，更多的准备，往往意味着更稳定的系统。

小结
这一节以业务高峰期的性能问题为背景，学习了一些紧急处理的手段。
这些手段了，既包括了粗暴地拒绝连接和断开连接，也有通过重写语句来绕过一些坑的方法；既有临时的高危方案，也有未雨绸缪的、相对安全的预案。
在实际开发了，我们也有避免一些低效的方法，比如避免大量地使用短连接。同时，如果你做业务开发的话，要知道，连接异常断开是常有的事，你的代码里要有正确地重连并重试的机制。
DBA虽然可以通过语句重写来暂时处理问题，但是这本身是一个风险高的操作，做好SQL审计可以减少这类操作的机会。
这一节中提到的解决方法主要集中在server层。

23. MySQL是如何保证数据不丢失的？

前面的文章中，我们学习了WAL机制，得到的结论是：只要redo log和binlog保证持久化到磁盘，就能确保MySQL重启后，数据可以恢复。
这一节，深入学习MySQL写入binlog和redo log的流程。

binlog的写入机制
其实，binlog的写入逻辑比较简单：事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog中。
一个事务的binlog是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到binlog cache的保存问题。

系统给binlog cache分配了一片内存，每个线程一个，参数binlog_cache_size用于控制单个线程内binlog_cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。
事务提交的时候，执行器把binlog_cache里的完整事务写入到binlog中，并清空binlog cache。状态如图5所示。

图5. binlog写盘状态

可以看到，每个线程都有自己binlog cache，但是共用同一份binlog文件。

• 图中的write，指的就是只把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快；
• 图中的fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为fsync才占磁盘的IOPS。

write和fsync的时机，是由参数sync_binlog控制的：

因此，在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成0，比较常见的是将其设置成100~1000中的某个值。
但是，将sync_binlog设置成N，对应的风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志。

redo log的写入机制
在第15节中，我们了解了redo log buffer。事务在执行过程中，生成的redo log是要先到redo log buffer的。
那么，redo log buffer里面的内容，是不是每次生成后都要直接持久化到磁盘呢？
答案是，不需要。
如果事务执行期间，MySQL发生了异常重启，那么这部分日志就丢了。由于事务并没有提交，所以这时日志丢了也不会有丢失。
那么，事务还没提交的时候，redo log buffer中的部分日志有没有可能被持久化到磁盘呢？
答案是，确实会有。

这个问题，要从redo log可能存在的三种状态说起。这三种状态，对应的就是图6中的三个颜色块。

图6. MySQL redo log存储状态

各个颜色对应的状态如下表：

日志写到redo log buffer是很快的，write到page cache也差不多，但是持久化到磁盘的速度就慢多了。
为了控制redo log的写入策略，是由innodb_flush_log_at_trx_commit参数控制的：

InnoDB有一个后台线程，每隔1秒，就会把redo log buffer中的日志，调用writw写到文件系统的page cache，然后调用fsync持久化到磁盘。
注意，事务执行中间过程的redo log也是直接写在redo log buffer中的，这些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里的日志一起持久化到磁盘。

这里需要说明，两阶段提交，时序上redo log先prepare，再写binlog，最后再把redo log commit。
如果把innodb_flush_log_at_trx_commit设置成1，那么redo log在prepare阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于prepare的redo log，再加上binlog来恢复的。
每秒一次后台轮询刷盘，再加上崩溃恢复这个逻辑，InnoDB就认为redo log在comit的时候就不需要fsync了，只会write到文件系统的page cache中就够了。
通常我们说MySQL的双“1”配置，指的就是sync_binlog和innodb_flush_at_trx_commit都设置成1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是redo log（prepare阶段），一次是binlog。

这时候，你可以有一个疑问，这意味着我从MySQL看到的TPS（Trsansaction Per Second，每秒处理的事务数目）是每秒两万的话，每秒就会写四万次磁盘。但是，我用工具测试出来，磁盘能力也就两万左右，怎么能实现两万的TPS。
解释这个问题，就要用到组提交（group commit）机制了。
这里，我们先了解日志逻辑序列号（log sequence number，LSN）的概念。LSN是单调递增的，用来对应redo log的一个个写入点。每次写入长度为length的redo og，LSN的值就会加上length。
LSN也会写到InnoDB的数据页中，来确保数据页不会被多次执行重复的redo log。

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

图7. redo log提交

从图中可以看到：

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

所以，一次组提交里面，组员越多，节约磁盘IOPS的效果越好。但如果只有单线程压测，那就只能老老实实地一个事务对应一次持久化操作了。
在并发更新场景下，第一个事务写完redo log buffer以后，接下来这个fsync越晚调用，组员可能越多，节约IOPS的效果就越好。

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

图8. 两阶段提交

图中，我把“写binlog”当成一个动作。但实际上，写binlog是分成两步的：

1. 先把binlog从binlog cache中写道磁盘上的binlog文件；
2. 调用fsync持久化。

MySQL为了让组提交的效果更好，把redo log做fsync的时间拖到了步骤1之后。也就是说，上面的图变成了这样：

图9. 两阶段提交细化

这么一来，binlog也可以组提交了。在执行图9中第4步把binlog fsync到磁盘时，如果有多个事务的binlog已经写完了，也是一起持久化，这样也可以减少IOPS的消耗。
不过通常情况下第3步执行得会很快，所以binlog的write和fsync间的间隔时间短，导致能集合到一起持久化的bilog比较少，因此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。
所以，当binlog_group_commit_sync_delay设置为0的时候，binlog_group_commit_sync_no_delay_count也无效了。
因此，WAL机制主要得益于两个方面：

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

到此，我们再来回答这个问题：如果你的MySQL出现了性能瓶颈，而且瓶颈在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，因为这样redo log值保存在内存中，这样的话MySQL本身异常重启也会丢数据，风险太大。而redo log写到文件系统的page cache的速度也是很快的，所以将这个参数设置成2跟设置成0趋势性能差不多，但这样做MySQ异常重启时就不会丢数据了，相比之下风险会更小。

小结
第二节、第十五节和这一节的内容串起来，我们对crash-safe这个概念有了更清晰的理解。

Q1：执行一个update语句以后，我再去执行hexdump命令直接查看ibd文件内容，为什么没 有看到数据有改变呢？
A1：执行一个update语句以后，我再去执行hexdump命令直接查看ibd文件内容，为什么没 有看到数据有改变呢？

Q2：为什么binlog cache是每个线程自己维护的，而redo log buffer是全局共用的？
A2：MySQL这么设计的主要原因是，binlog是不能“被打断的”。一个事务的binlog必须连续 写，因此要整个事务完成后，再一起写到文件里。

Q3：事务执行期间，还没到提交阶段，如果发生crash的话，redo log肯定丢了，这会不会导致主备不一致呢？
A3：不会。因为这时候binlog 也还在binlog cache里，没发给备库。crash以后redo log和binlog都没有了，从业务角度看这个事务也没有提交，所以数据是一致的。

Q4：如果binlog写完盘以后发生crash，这时候还没给客户端答复就重启了。等客户端再重连进来，发现事务已经提交成功了，这是不是bug？
A4：不是。
你可以设想一下更极端的情况，整个事务都提交成功了，redo log commit完成了，备库也收到binlog并执行了。但是主库和客户端网络断开了，导致事务成功的包返回不回去，这时候客户端也会收到“网络断开”的异常。这种也只能算是事务成功的，不能认为是bug。
实际上数据库的crash-safe保证的是：

1. 如果客户端收到事务成功的消息，事务就一定持久化了；
2. 如果客户端收到事务失败（比如主键冲突、回滚等）的消息，事务就一定失败了；
3. 如果客户端收到“执行异常”的消息，应用需要重连后通过查询当前状态来继续后续的逻辑。
   此时数据库只需要保证内部（数据和日志之间，主库和备库之间）一致就可以了。

小问题
什么时候会把线上生产库设置成“非双1”？
解析：
目前已知场景如下：

1. 业务高峰期。一般如果有预知的高峰期，DBA会有预案，把主库设置成“非双1”。
2. 备库延迟，为了让备库尽快赶上主库。
3. 用诶分恢复主库的副本，应用binlog的过程。
4. 批量导入数据的时候。

24. MySQL是如何保证主备一致的？

MySQL主备的基本原理
如图1所示就是基本的主备切换流程。

图10. 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这条线的内部流程是什么样的。
如图11所示就是一个update语句在节点A执行，然后同步到节点B的完整流程图。

图11. 主备流程图

图11中，包含了binlog和redo log的写入机制相关的内容，可以看到：主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写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读取中转日志，解析出日志里的命令，并执行。

这里需要说明，由于多线程复制方案的引入，sql_thread演化为了多个线程，跟我们今天要介绍的原理没有直接关系，暂且不展开。

binlog的三种格式对比
binlog有三种格式：

• statement
• row
• mixed

为了便于描述binlog的这三种格式间的区别，我创建了一个表，初始化几行数据。

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是怎么记录的。
注意，下面这个语句包含注释，如果用MySQL客户端来做这个实验的话，要记得加-c参数，否则客户端会自动去掉注释。

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

通过下面的语句把binlog_format=设置为statement：

set binlog_format='statement';

这时候binlog里面记录的就是SQL语句的原文，可以用下面这条命令

show binlog events in 'PARAK-bin.000113';

看binlog的内容。

图12. statement格式binlog示例

现在，我们来看一下图12的输出结果。

• 第一行SET @@SESSION.GTID=’ANONYMOUS’可以先忽略；
• 第二行是一个BEGIN，跟第四行commit对应，表示中间是一个事务；
• 第三行就是真实执行的语句了。可以看到，在真实执行的delete命令之前还有一个”use web”命令。这条命令部署我们主动执行的，而是MySQL根据当前要操作的表所在的数据库，自行添加的。这样做保证日志传到备库去执行的时候，不论当前的工作线程在哪个库里，都能够正确地更新到web库的表t。”use web”命令之后的delete语句，就是我们输入的SQL原文了。可以看到，binlog忠实地记录了SQL命令，甚至连注释也一并记录了。
• 最后一行是一个COMMIT。

为了说明statement和row格式的区别，我们来看一下delete命令的执行效果图：

图13. delete执行warnings

可以看到，运行这条delete命令产生了一个warnig，原因是当前binlog设置的是statement格式，并且语句中有limit，所以这个命令可能是unsafe的。
这是因为delete带limit，很可能会出现主备数据不一致的情况。比如上面这个例子：

1. 如果delete语句使用的时索引a，那么会根据索引a找到第一个满足条件的行，也就是说删除的是a=4这一行；
2. 但如果使用的是索引t_modified，那么删除的就是就是t_modified=’2018-11-09’也就是a=5这一行。

由于statement格式下，记录到binlog里的是语句原文，因此可能会出现这样一种情况：在主库执行这条SQL语句的时候，用的是索引t_modified。因此，MySQL认为这样写是有风险的。

那么，如果我把binlog_format改为row：

set binlog_format='row';

是不是这样就没有问题了呢？我们先来看看这时候binlog中的内容吧。

图14. row格式binlog示例

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

1. Table_map_event，用来说明接下来要操作的表是web库的表t；
2. Delete_rows_event，用于定义删除的行为。

其实，我们通过图14是看不到详细信息，还需要借助mysqlbinlog工具，用下面这个命令解析和查看binlog的内容。因为图14中的信息显示，这个事务的binlog是从3036344这个位置开始的，所以可以用start-position参数来指定这个位置的日志开始解析。

mysqlbinlog PARAK-bin.000113 --start-postion=3036344;

图15. row格式binlog示例的详细信息

从这个图中，我们可以看到以下几个信息：

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

可以看到，当binlog_format使用row格式的时候，binlog里面记录了真实删除行的主键id，这样binlog传到备库去的时候，就肯定会删除id=4的行，不会有主备删除不同行的问题。

为什么有mixed格式的binlog
基于上面的信息，我们就来讨论一个问题：为什么会有mixed这种binlog格式的存在场景？
推论过程是这样的：

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

也就是说，mixed格式可以利用statement的优点，同时又避免数据不一致的风险。
因此，如果你的线上MySQL设置的binlog格式是statement的话，那基本上就可以认为这是一个不合理的设置。你至少应该把binlog的格式设置为mixed。

比如我们这个例子，设置为mixed后，就会记录row格式；而如果执行的语句去掉limit 1，就会记录statement格式。

当然，现在越来越多的场景要求把MySQL的binlog格式设置从成row。这么做的理由有很多，比如：恢复数据。
接下来，我们就分别从delete、insert和update三种SQL语句的角度，来看看数据恢复的问题。
通过图15可以看出来，即时执行的是delete语句，row格式的binlog也会把被删掉的行的整行信息保存起来。所以，如果你在执行完一条delete语句以后，发现删错数据了，可以直接把binlog中记录的delete语句转成insert，把被错删的数据插入回去就可以恢复了。
如果执行错了insert语句，那就更直接了。row格式下，insert语句的binlog里会记录所有的字段信息，这些信息可以用来精确定位刚刚被插入的那一行。这时，你直接把insert语句转成delete语句，删除掉被误插入的一行数据就可以了。
如果执行错了update语句，binlog里面会记录前整行的数据和修改后的整行数据。所以，如果你误执行了update语句的话，只需要把这个event前后的两行信息对调一下，再去数据库里面执行，就能恢复这个更新操作了。
其实，由delete、insert或者update语句导致的数据操作错误，需要恢复到操作之前状态的情况，也时有发生。MariaDB的Flashback工具就是基于上面介绍的原理来回滚数据的。

虽然mixed格式的binlog现在已经用得不多了，但这里我还是要要借用一下mixed格式来说明一个问题，来看一下这条SQL语句：

insert into t values(10, 10, now());

如果我们把binlog设置为mixed：

set binlog_format = 'mixed';

那么MySQL会把它记录为row格式还是statemet格式呢？
这条语句执行的效果如下：

图16. mixed格式和now()

可以看到，MySQL用的居然是statement格式。
接下来，我们再用mysqlbinlog工具看看：

图17. TIMESTAMP命令

从图中的结果可以看到，原来binlog在记录event的是，多记录了一条命令：SET TIMESTAMP=1546103491。它用SET TIMESTAMP命令约定了接下的now()函数的返回时间。
因此，不论这个binlog是1分钟之后被备库执行，还是3天后用来恢复这个库的备份，这个insert语句插入的行，值都是固定的。也就是说，通过这条SET TIMESTAMP命令，MySQL就确保了主备数据的一致性。
有人在重放binlog数据的时候，是这么做的：用mysqlbinlog工具解析出来，然后把解析结果整个发给MySQL执行。类似下面的命令：

mysqlbinlog PARAK-bin.000113  --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

这个命令的意思是，将PARAK-bin.000113文件里面从2738字节到2973字节中间这段内容解析出来，放到MySQL去执行。

循环复制问题
通过上面对MySQL中binlog基本内容的理解，你现在可以知道，binlog的特性确保了在备库执行相同的binlog，可以得到与主库相同的状态。
因此，我们可认为正常情况下主备的数据是一致的。也就是说，图10中A、B两个节点的内容是一致的。其实，图1 展示的是M-S结果，但实际生产上使用比较多的是双M结构，也就是图18所示的主备切换流程。

图18. MySQL主备切换流程——双M结构

对比图18和图10，你可以发现，双M结构和M-S结构，其实区别只是多了一条线，即：节点A和B之间总是互为主备关系。这样在切换的时候就不用再修改主备关系。

但是，双M结构还有一个问题需要解决。
业务逻辑在节点A上更细了一条语句，然后再把生成的binlog发给节点B，节点B执行完这条更新语句后也会生成binlog。（建议把参数log_slave_updates设置为on，表示备库执行relay后生成binlog）。
那么，如果节点A同时是节点B的备库，相当于又把节点B新生成的binlog拿过来执行了一次，然后节点A和节点B间，会不断地循环执行这个更新语句，也就是虚幻复制了。
从上面的图15中可以看到，MySQL在binlog中记录了这个命令第一次执行时所在实例的server id。因此，我们可以用下面的逻辑，来解决两个节点间的循环复制的问题：

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

按照这个逻辑，如果我们设置了双M结构，日志的执行流就会变成这样：

1. 从节点A更新的事务，binlog里面记的都是A的server id；
2. 传到节点B执行一次以后，节点B生成的binlog的server id也是A的server id；
3. 再传回到节点A，A判断到这个server id与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了。

小结
这一节学习了MySQL binlog的格式和一些基本机制，binlog在MySQL的各种高可用方案上扮演了重要角色。
这一节也介绍了MySQL高可用方案的基础，在这之上演化出了注入多节点、版同步、MySQL group replication等相对复杂的方案。

小问题
MySQL通过判断server id的方式，断掉死循环。但是，这个机制其实并不完备，在某些场景下，还是有肯能出现死循环。
你能构造出一个这样的场景吗？又应该怎么解决呢？
解析：
一种场景是，在一个主库更新事务后，用命令set global server_id=x修改了server_id。等日志传回来的时候，发现server_id跟自己的server_id不同，就只能执行了。
另一种场景是，有三个节点的时候，如图19所示，trx1是在节点B执行的，因此binlog上的server_id就是B，binlog传给节点A，然后A和A’搭建了双M结构，就会出现循环复制。

图19. 三节点循环复制

这种三节点复制的场景，做数据库迁移的时候会出现。
如果出现了循环复制，可以在A或者A’上，执行如下命令：

stop slave;
CHANGE MASTER TO IGNORE_SERVER_IDS=(server_id_of_B);
start slave;

这样这个节点收到日志后就不会再执行。过一段时间后，再执行下面的命令把这个值改回来。

stop slave；
CHANGE MASTER TO IGNORE_SERVER_IDS=();
start slave;

25. MySQL是如何保证高可用的？

正常情况下，只要主库执行更新生成的所有binlog，都可以传到备库并被正确地执行，备库就能达到和主库一样的状态，这就是最终一致性。

这里再放一次双M结构的主备切换流程图。

图20. MySQL主备切换流程——双M结构

主备延迟
主备切换可能是一个主动运维动作，比如软件升级、主库所在机器按计划下线等，也可能是被动操作，比如主库所在机器停电。
接下来，我们先一起看看主动切换的场景。

在介绍主动切换流程的详细步骤之前，我们先学习一个概念，即“同步延迟”。与数据同步有关的时间点主要包括以下三个：

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

所谓主备延迟，就是同一个事物，在备库执行完成的时间和主库执行执行完成的时间之间的差值，也就是T3-T1。
可以在备库上执行show slave status命令，它的返回结果里面会显示second_behind_master，用于表示当前备库延迟了多少秒。

second_behind_master的计算方法是这样的： 1。 每个事务的binlog里面都有一个时间字段，用于记录主库上写入的时间； 备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的插值，得到second_behind_master。

可以看到，其实seconds_behind_master这个参数计算的就是T3-T1。所以，我们可以用seconds_behind_master来作为主备延迟的值，这个值的时间精度是秒。

你可能会问，如果朱备库机器的系统时间设置不一致，会不会导致主备延迟的值不准？
其实不会的。因为，备库连接到主库的时候，会通过执行SELECT UNIX_TIMESTAMP()函数来获得当前主库的系统时间。如果这时候发现主库的系统时间与自己不一致，备库在执行seconds_behind_master计算的时候会自动扣掉这个差值。
需要说明的是，在网络正常的时候，日志从主库传给备库所需的时间是很短的，即T2-T1的值是非常小的。也就是说，网络正常情况下，主备延迟的主要来源是备库接收完binlg和执行完这个事务之间的时间差。
所以说，主备延迟最直接的表现是，备库消费中转日志（redo log）的速度，比主库生产binlog的速度要慢。

主备延迟的来源
首先，有些部署条件下，备库所在机器的性能要比主库所在的机器性能差。
备库没有请求，用差一点的机器也无所谓。这种想法是不对的。
更新请求对IOPS的压力，在主库和备库是无差别的。所以，做这种部署时，一般都会将备库设置为“非双1”的模式。
但实际上，更新过程中也会触发大量的读操作。所以，当备库主机上的多个备库都在争抢资源的时候，就可能会导致主备延迟了。
当然，这种部署现在比较少了。因为主备可能发生切换，备库随时可能变成主库，所以主备库选用相同规格的机器，并且做对称部署，是现在比较常见的情况。

Q1：但是，做了对称部署以后，还可能会有延迟。这是为什么呢？
这就是第二种常见的可能了，即备库的压力大。一般的想法是，主库既然提供了写能力，那么备库可以提供一些读能力。或者一些运营后台需要的分析语句，不能影响正常业务，所以只能在备库上跑。
由于主库直接影响业务，使用起来比较客户自，反而忽视了备库的压力控制。结果是，备库上的查询耗费了大量的CPU资源，影响了同步速度，造成主备延迟。
这种情况，我们一般可以这么处理：

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

其中，一主多从的方式大都会被采用。因为作v 不VC 为数据库系统，还必须保证有定期全量备份的能力。而从库，就很适合用来做备份。

将HA过程中被选为新主库的，称为备库，其他的称为从库。

Q2：采用了一主多从，保证备库的压力不会超过主库，还有什么情况可能导致主备延迟吗？
这就是第三种可能了，即大事务。
大事务这种情况很好理解，因为主库上必须等事务执行完成才会写入binlog，再传给备库。所以，如果一个主库上的语句执行10分钟，那这个事务很可能就会导致从库延迟10分钟。
不要一次性地用delete语句删除太多数据。其实，这就是一个典型的大事务场景。
另一种典型的大失误场景，就是大表DDL。这个场景，处理方案就是，计划内的DDL，建议使用gh-ost方案。

Q3：如果主库上也不做大事务了，还有什么原因会导致主备延迟吗？
造成主备延迟还有一个大方向的原因，就是备库的并行复制能力。

可靠性优先策略
在图20的双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。

这个切换流程，一般是由专门的HA系统来完成的，暂时称之为可靠性优先流程。

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

备注：图中的SBM，是seconds_behind_master参数的简写。
可以看到，这个切换流程是有不可用时间的。因为在步骤2之后，主库A和备库B都处于readonly状态，也就是说这时系统处于不可写状态，直到步骤5完成后才恢复。
在这个不可用状态中，比较耗费时间的是步骤3，可能需要消费好几秒的时间。这也是为什么需要在步骤1先做判断，确保seconds_behind_master的值足够小。
试想如果一开始主备延迟就长达30分钟，而不先做判断直接切换的话，系统的不可用时间就会长达30分钟，这种情况一般业务都是不可接受的。
当然，系统的不可用时间，是由这个数据优先的策略决定的。你也可以选择可用性优先的策略，来把这个不可用时间几乎降为0。

可用性优先策略
如果强行把步骤4、5调整到最开始执行，也就是说不等主备数据同步，直接把连接切到备库B，并且让备库B可以读写，那么系统几乎就没有不可用时间了。
我们把这个切换流程，暂时称为可用性优先流程。这个切换流程的代价，就是可能出现数据不一致的情况。

这里分享一个可用性优先流程产生数据不一致的例子。假设有一个表t：

CREATE TABLE `t` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `c` int(11) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(c) values(1),(2),(3);

这个表定义了一个自增主键id，初始化数据后，主库和备库上都是3行数据。接下来，业务人员要继续在表t上执行两天插入语句的命令，依次是：

insert into t(c) values(4);
insert into t(c) values(5);

假设，闲杂主库上其他的数据表有大量的更新，导致主备延迟达到5秒。在插入一条c=4的语句，发起了主备切换。

图22是可用性优先策略，且binlog_format=mixed时的切换流程和数据结果。

图22. 可用性优先策略，且binlog_format=mixed

现在，我们一起分析下这个切换流程：

1. 步骤2中，主库A执行完insert语句，插入了一行数据（4, 4），之后开始进行主备切换；
2. 步骤3中，由于主备之间有5秒的延迟，所以比酷还没来得及应用“插入c=4”这个中转日志，就开始接收客户端“插入c=5”的命令；
3. 步骤4中，备库B插入了一行数据（4, 5），并且把这个binlog发给主库A；
4. 步骤3中，备库执行了“插入c=4”这个中转日志，插入了一行数据（5, 4）。而直接在备库B执行的“插入c=5”这个语句，传到主库A，就插入了一行新数据（5, 5）。

最后的结果就是，主库A和备库B上出现了两行不一致的数据。这个数据不一致，是可用性优先流程导致的。

那么，如果我还是用可用性优先策略，但设置binlog_format=row，情况会怎么样呢？
因为row格式在记录的时候，会记录新插入的行的所有字段值，所以最后会有一行不一致。而且，两边的主备同步的应用线程会报错duplicate key error并停止。也就是说，这种情况下，备库B的（5, 4）和主库A的（5, 5）这两行数据，都不会被对方执行。详细流程如图23。

图23. 可用性优先策略，且binlog_format=row

从上面的分析中，可以看到一些结论：

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

但事无绝对，有没有哪种情况数据的可用性优先级更高呢？
答案是，有的，比如下面这个场景：

• 有一个库的作用是记录操作日志。这时候，如果数据不一致可以通过binlog来修补，而这个短暂的不一致也不会引发业务问题。
• 同时，业务系统依赖于这个日志写入逻辑，如果这个库不可写，会导致线上的业务操作无法执行。

这时候，你可能就需要选择先强行切换，事后再补数据的策略。
当然，事后复盘的时候，我们想到了一个改进措施就是，让业务逻辑不要依赖于这类日志的写入。也就是说，日志写入这个逻辑模块应该可以降级，比如写到本地文件，或者写到另外一个临时库里面。

这样的话，这种场景就又可以使用可靠性优先策略了。
接下来我们再看看，按照可靠性优先的思路，异常切换会是什么效果？
假设，主库A和备库B的主备延迟是30分钟，这时候主库A掉电了，HA系统要切换B作为主库。我们在主动切换的时候，要等待主备延迟小于5秒的时候再启动切换，但这时候已经别无选择了。

图24. 可靠性优先策略，主库不可用

采用可靠性优先策略的话，你就必须得等到备库B的seconds_behind_master=0之后，才能切换。但现在的情况比刚刚更严重，并不是系统只读、不可写的问题了，而是系统处于完全不可用的状态。因为，主库A掉电后，我们的连接还没有切到备库B。

那么，能不能直接切换到备库B，但是保持B只读呢？
这样也不行。因为，这段时间内，中转日志还没有应用完成，如果直接发起主备切换，客户端查询看不到之前执行完成的事务，会认为有“数据丢失”。
虽然随着中转日志的继续应用，这些数据会恢复回来，但是对于一些业务来说，查询到“暂时丢失数据的状态”也是不能被接受的。
到这里，在满足数据可靠性的前提下，MySQL高可用系统的可用性，是依赖于主备延迟的。延迟的时间越小，在主库故障的时候，服务恢复需要的时间就越短，可用性就越高。

小结
这一节学习了MySQL高可用系统的基础，就是主备切换逻辑。紧接着，我们学习了几种会导致主备延迟的情况，以及相应的改进方向。
然后，由于主备延迟的存在，切换策略就有不同的选择。所以，我们又分析了可靠性优先和可用性优先的区别。
在实际的应用中，我更建议使用可靠性优先的策略。毕竟保证数据准确，应该数数据库服务的基础。在这个基础上，通过减少主备延迟，提升系统的可用性。

小问题
假如你看你维护的一个备库，它的延迟监控的图像类似图25，是一个45°斜向上的线段，你觉得可能是什么原因导致呢？你又会怎么去确认这个原因呢？

图25. 备库延迟

解析：
重点：备库的同步在这段时间完全被堵住了。
产生这种现象典型的场景主要包括两种：
（1）大事务（大表DDL、一个事务操作多行）；
（2）备库起了一个长事务，比如：

begin;
select * from t limit 1;

然后就不懂了。
这时候，主库对表t做了一个加字段操作，即时这个表很小，这个DLL在备库应用的时候也会被堵住，也不能看到这个现象。