mysql实战45讲学习笔记四

本篇接上一篇笔记，主要讲了mysql如何保证高可用，主备架构下备库并行复制，一主多从下的主备切换，join语句选择驱动表的原则和优化，详细见目录。有问题及时在本博客或在个人博客留言.

mysql如何保证高可用

主备延迟

主备切换可能是一个主动运维动作，比如软件升级、主库所在机器按计划下线等，也可能是被动操作，比如主库所在机器掉电。

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

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

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

可以在备库上执行show slave status命令，它的返回结果里面会显示seconds_behind_master，用于表示当前备库延迟了多少秒。

seconds_behind_master的计算方法是这样的：

• 每个事务的binlog里面都有一个时间字段，用于记录主库上写入的时间；
• 备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到seconds_behind_master。
  可以看到，其实seconds_behind_master这个参数计算的就是T3-T1。所以，我们可以用seconds_behind_master来作为主备延迟的值，这个值的时间精度是秒。

如果主备库机器的系统时间设置不一致，会不会导致主备延迟的值不准？
其实不会的。因为，备库连接到主库的时候，会通过执行 SELECT UNIX_TIMESTAMP()函数来获得当前主库的系统时间。如果这时候发现主库的系统时间与自己不一致，备库在执行 seconds_behind_master 计算的时候会自动扣掉这个差值。

在网络正常的时候，日志从主库传给备库所需的时间是很短的，即T2-T1的值是非常小的。也就是说，网络正常情况下，主备延迟的主要来源是备库接收完binlog和执行完这个事务之间的时间差。

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

主备延迟的来源

首先，有些部署条件下，备库所在机器的性能要比主库所在的机器性能差.这种部署现在比较少了。因为主备可能发生切换，备库随时可能变成主库，所以主备库选用相同规格的机器，并且做对称部署，是现在比较常见的情况。

第二种常见的可能，即备库的压力大由于主库直接影响业务，大家使用起来会比较克制，反而忽视了备库的压力控制。结果就是，备库上的查询耗费了大量的CPU资源，影响了同步速度，造成主备延迟。

这种情况，我们一般可以这么处理：

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

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

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

造成主备延迟还有一个大方向的原因，就是备库的并行复制能力

主备切换策略

由于主备延迟的存在，所以在主备切换的时候，就相应的有不同的策略。

可靠性优先策略

如下图的双M结构下，从状态1切换到状态2的流程如下：

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

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

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

在这个不可用状态中，比较耗费时间的是步骤 3，可能需要耗费好几秒的时间。这也是为什么需要在步骤 1 先做判断，确保 seconds_behind_master 的值足够小。

可用性优先策略

如果强行把步骤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 );

接下来，业务人员要继续在表 t 上执行两条插入语句的命令，依次是：

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

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

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

整个切换过程如下：

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

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

下面是设置 binlog_format=row的情况

可以看到一些结论：

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

在满足数据可靠性的前提下，MySQL 高可用系统的可用性，是依赖于主备延迟的。延迟的时间越小，在主库故障的时候，服务恢复需要的时间就越短，可用性就越高。

并行复制

备库并行复制能力

先看下之前讲过的主备流程图：

谈到主备的并行复制能力，我们要关注的是图中黑色的两个箭头。一个箭头代表了客户端写入主库，另一箭头代表的是备库上sql_thread执行中转日志（relay log）。如果用箭头的粗细来代表并行度的话，那么真实情况就如图1所示，第一个箭头要明显粗于第二个箭头。

为了让日志在备库上的执行，就是图中备库上sql_thread更新数据(DATA)的逻辑足够快，降低主备延迟，现在版本的mysql都采用多线程复制的方式，如下图二。线程的个数由参数slave_parallel_workers决定的。根据我的经验，把这个值设置为8~16之间最好（32核物理机的情况），毕竟备库还有可能要提供读查询，不能把CPU都吃光了。

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

事务能不能按照轮询的方式分发给各个 worker，也就是第一个事务分给 worker_1，第二个事务发给 worker_2 呢？

其实是不行的。因为，事务被分发给worker以后，不同的worker就独立执行了。但是，由于CPU的调度策略，很可能第二个事务最终比第一个事务先执行。而如果这时候刚好这两个事务更新的是同一行，也就意味着，同一行上的两个事务，在主库和备库上的执行顺序相反，会导致主备不一致的问题。

同一个事务的多个更新语句，能不能分给不同的worker来执行呢？

也不行。举个例子，一个事务更新了表t1和表t2中的各一行，如果这两条更新语句被分到不同worker的话，虽然最终的结果是主备一致的，但如果表t1执行完成的瞬间，备库上有一个查询，就会看到这个事务“更新了一半的结果”，破坏了事务逻辑的隔离性。

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

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

一主多从架构下的主备切换

下面是一个常规的一主多从结构的数据库系统：

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

下图是主库发生故障后，主备切换后的结果：

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

下面看下一个切换系统会怎么完成一主多从的主备切换过程

基于位点的主备切换

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

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

MASTER_HOST、MASTER_PORT、MASTER_USER和MASTER_PASSWORD四个参数，分别代表了主库A’的IP、端口、用户名和密码。
最后两个参数MASTER_LOG_FILE和MASTER_LOG_POS表示，要从主库的master_log_name文件的master_log_pos这个位置的日志继续同步。而这个位置就是我们所说的同步位点，也就是主库对应的文件名和日志偏移量。

节点B要设置成A’的从库，就要执行change master命令，就不可避免地要设置位点的这两个参数，但是这两个参数到底应该怎么设置呢？原来节点B是A的从库，本地记录的也是A的位点。但是相同的日志，A的位点和A’的位点是不同的。因此，从库B要切换的时候，就需要先经过“找同步位点”这个逻辑。

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

一种取同步位点的方法是这样的：

• 等待新主库A’把中转日志（relaylog）全部同步完成；
• 在A’上执行show master status命令，得到当前A’上最新的File和Position；
• 取原主库A故障的时刻T；
• 用mysqlbinlog工具解析A’的File，得到T时刻的位点。

mysqlbinlog File --stop-datetime=T --start-datetime=T

图中，end_log_pos后面的值“123”，表示的就是A’这个实例，在T时刻写入新的binlog的位置。然后，我们就可以把123这个值作为$master_log_pos，用在节点B的change master命令里.

但是个值并不准确，假设在T这个时刻，主库A已经执行完成了一个insert语句插入了一行数据R，并且已经将binlog传给了A’和B，然后在传完的瞬间主库A的主机就掉电了。
那么，这时候系统的状态是这样的：

• 在从库B上，由于同步了binlog，R这一行已经存在；
• 在新主库A’上，R这一行也已经存在，日志是写在123这个位置之后的；
• 我们在从库B上执行change master命令，指向A’的File文件的123位置，就会把插入R这一行数据的binlog又同步到从库B去执行。
  这时候，从库B的同步线程就会报告Duplicate entry ‘id_of_R’ forkey ‘PRIMARY’ 错误，提示出现了主键冲突，然后停止同步。
  所以，通常情况下，我们在切换任务的时候，要先主动跳过这些错误，有两种常用的方法。

一种做法是，主动跳过一个事务。跳过命令的写法是：

set global sql_slave_skip_counter=1; 
start slave;

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

另外一种方式是，通过设置slave_skip_errors参数，直接设置跳过指定的错误。
在执行主备切换时，有这么两类错误，是经常会遇到的：

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

因此，我们可以把slave_skip_errors设置为“1032,1062”，这样中间碰到这两个错误时就直接跳过。
这里需要注意的是，这种直接跳过指定错误的方法，针对的是主备切换时，由于找不到精确的同步位点，所以只能采用这种方法来创建从库和新主库的主备关系。
这个背景是，我们很清楚在主备切换过程中，直接跳过1032和1062这两类错误是无损的，所以才可以这么设置slave_skip_errors参数。等到主备间的同步关系建立完成，并稳定执行一段时间之后，我们还需要把这个参数设置为空，以免之后真的出现了主从数据不一致，也跳过了。

全局事务 ID（GTID）

通过sql_slave_skip_counter跳过事务和通过slave_skip_errors忽略错误的方法，虽然都最终可以建立从库B和新主库A’的主备关系，但这两种操作都很复杂，而且容易出错。所以，MySQL5.6版本引入了GTID，彻底解决了这个困难

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

GTID=server_uuid:gno

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

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

基于 GTID 的主备切换

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

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协议。可以看到，前面让我们头疼不已的MASTER_LOG_FILE和MASTER_LOG_POS参数，已经不需要指定了。

我们把现在这个时刻，实例 A’的 GTID 集合记为 set_a，实例 B 的 GTID 集合记为 set_b。

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

• 实例 B 指定主库 A’，基于主备协议建立连接。

• 实例 B 把 set_b 发给主库 A’。

• 实例 A’算出 set_a 与 set_b 的差集，也就是所有存在于 set_a，但是不存在于 set_b 的 GITD 的集合，判断 A’本地是否包含了这个差集需要的所有 binlog 事务。

  a. 如果不包含，表示 A’已经把实例 B 需要的 binlog 给删掉了，直接返回错误；

  b. 如果确认全部包含，A’从自己的 binlog 文件里面，找出第一个不在 set_b 的事务，发给 B；

• 之后就从这个事务开始，往后读文件，按顺序取 binlog 发给 B 去执行。

这个逻辑里面包含了一个设计思想：在基于GTID的主备关系里，系统认为只要建立主备关系，就必须保证主库发给备库的日志是完整的。因此，如果实例B需要的日志已经不存在，A’就拒绝把日志发给B

这跟基于位点的主备协议不同。基于位点的协议，是由备库决定的，备库指定哪个位点， 主库就发哪个位点，不做日志的完整性判断。

下面看下引入 GTID 后，一主多从的切换场景下，主备切换是如何实现的。

由于不需要找位点了，所以从库B、C、D只需要分别执行changemaster命令指向实例A’即可。
其实，严谨地说，主备切换不是不需要找位点了，而是找位点这个工作，在实例A’内部就已经自动完成了。但由于这个工作是自动的，所以对HA系统的开发人员来说，非常友好。
之后这个系统就由新主库A’写入，主库A’的自己生成的binlog中的GTID集合格式是：server_uuid_of_A’:1-M。
如果之前从库B的GTID集合格式是server_uuid_of_A:1-N，那么切换之后GTID集合的格式就变成了server_uuid_of_A:1-N,server_uuid_of_A’:1-M。
当然，主库A’之前也是A的备库，因此主库A’和从库B的GTID集合是一样的。这就达到了我们预期。

思考题

你在GTID模式下设置主从关系的时候，从库执行startslave命令后，主库发现需要的binlog已经被删除掉了，导致主备创建不成功。这种情况下，你觉得可以怎么处理呢？

• 如果业务允许主从不一致的情况，那么可以在主库上先执行show global variables like ‘gtid_purged’，得到主库已经删除的GTID集合，假设是gtid_purged1；然后先在从库上执行reset master，再执行setglobalgtid_purged=‘gtid_purged1’；后执行start slave，就会从主库现存的binlog开始同步。binlog缺失的那一部分，数据在从库上就可能会有丢失，造成主从不一致。
• 如果需要主从数据一致的话，好还是通过重新搭建从库来做。
• 如果有其他的从库保留有全量的binlog的话，可以把新的从库先接到这个保留了全量binlog的从库，追上日志以后，如果有需要，再接回主库。
• 如果binlog有备份的情况，可以先在从库上应用缺失的binlog，然后再执行startslave。

读写分离怎么保证读到最新值

由于主从可能存在延迟，客户端执行完一个更新事务后马上发起查询，如果查询选择的是从库的话，就有可能读到刚刚的事务更新之前的状态。这种“在从库上会读到系统的一个过期状态”的现象，在这篇文章里，我们暂且称之为“过期读”。

这些方案主要包括下面几个：

强制走主库方案；sleep 方案；判断主备无延迟方案；配合semi-sync方案；等主库位点方案；等GTID方案。

强制走主库方案

这个要根据业务的需求来进行区分，对于需要实时得到结果的强制走主库查询，对于时效性要求不那么高的可以走从库。

Sleep 方案

主库更新后，读从库之前先sleep一下。具体的方案就是，类似于执行一条selectsleep(1)命令。
这个方案的假设是，大多数情况下主备延迟在1秒之内，做一个sleep可以有很大概率拿到新的数据。不过这个方案是有一定问题的，数据可能不精确：

如果这个查询请求本来0.5秒就可以在从库上拿到正确结果，也会等1秒；如果延迟超过1秒，还是会出现过期读。

判断主备无延迟方案

要确保备库无延迟，通常有三种做法：

第一种确保主备无延迟的方法是，每次从库执行查询请求前，先判断 seconds_behind_master 是否已经等于 0。如果还不等于 0 ，那就必须等到这个参数变为0才能执行查询请求。

seconds_behind_master的单位是秒，如果你觉得精度不够的话，还可以采用对比位点和GTID的方法来确保主备无延迟，也就是我们接下来要说的第二和第三种方法。

下面是一个 show slave status 结果的部分截图

通过这个结果我们看看具体如何通过对比位点和 GTID 来确保主备无延迟。
第二种方法，对比位点确保主备无延迟：
如果 Master_Log_File 和 Relay_Master_Log_File、Read_Master_Log_Pos和 Exec_Master_Log_Pos 这两组值完全相同，就表示接收到的日志已经同步完成。

第三种方法，对比 GTID 集合确保主备无延迟：

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

如果这两个集合相同，也表示备库接收到的日志都已经同步完成。
可见，对比位点和对比 GTID 这两种方法，都要比判断 seconds_behind_master 是否为 0 更准确。

在执行查询请求之前，先判断从库是否同步完成的方法，相比于sleep方案，准确度确实提升了不少，但还是没有达到“精确”的程度。为什么这么说呢？
我们现在一起来回顾下，一个事务的binlog在主备库之间的状态：

• 主库执行完成，写入binlog，并反馈给客户端；
• binlog被从主库发送给备库，备库收到；
• 在备库执行binlog完成。
  我们上面判断主备无延迟的逻辑，是“备库收到的日志都执行完成了”。但是，从binlog在主备之间状态的分析中，不难看出还有一部分日志，处于客户端已经收到提交确认，而备库还没收到日志的状态。如何解决这个问题呢？

配合 semi-sync

要解决上面的问题，就要引入半同步复制，也就是 semi-sync replication。

semi-sync 做了这样的设计：

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

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

这样，semi-sync配合前面关于位点的判断，就能够确定在从库上执行的查询请求，可以避免过期读。
但是，semi-sync+位点判断的方案，只对一主一备的场景是成立的。在一主多从场景中，主库只要等到一个从库的ack，就开始给客户端返回确认。这时，在从库上执行查询请求，就有两种情况：

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

其实，判断同步位点的方案还有另外一个潜在的问题，即：如果在业务更新的高峰期，主库的位点或者GTID集合更新很快，那么上面的两个位点等值判断就会一直不成立，很可能出现从库上迟迟无法响应查询请求的情况

等主库位点方案

要理解等主库位点方案，我需要先和你介绍一条命令：

select master_pos_wait(file, pos[, timeout]);

这条命令的逻辑如下：

• 它是在从库执行的；
• 参数file和pos指的是主库上的文件名和位置；
• timeout可选，设置为正整数N表示这个函数多等待N秒。

这个命令正常返回的结果是一个正整数M，表示从命令开始执行，到应用完file和pos表示的binlog位置，执行了多少事务。
当然，除了正常返回一个正整数M外，这条命令还会返回一些其他结果，包括：

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

所以对于在主库上执行一个事务trx1，之后查询的逻辑，可以如下来进行：

• trx1 事务更新完成后，马上执行 show master status 得到当前主库执行到的 File 和 Position；
• 选定一个从库执行查询语句；
• 在从库上执行 select master_pos_wait(File, Position, 1)；
• 如果返回值是 >=0 的正整数，则在这个从库执行查询语句；
• 否则，到主库执行查询语句。

步骤 5 到主库执行查询语句，是这类方案常用的退化机制。因为从库的延迟时间不可控， 不能无限等待，所以如果等待超时，就应该放弃，然后到主库去查。

GTID 方案

如果你的数据库开启了 GTID 模式，对应的也有等待 GTID 的方案。
MySQL 中同样提供了一个类似的命令：

select wait_for_executed_gtid_set(gtid_set, 1);

这条命令的逻辑是：
1.等待，直到这个库执行的事务中包含传入的gtid_set，返回0；2.超时返回1

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

• trx1事务更新完成后，从返回包直接获取这个事务的GTID，记为gtid1；
• 选定一个从库执行查询语句；
• 在从库上执行selectwait_for_executed_gtid_set(gtid1,1)；
• 如果返回值是0，则在这个从库执行查询语句；
• 否则，到主库执行查询语句。

跟等主库位点的方案一样，等待超时后是否直接到主库查询，需要业务开发同学来做限流考虑

在上面的第一步中，trx1 事务更新完成后，从返回包直接获取这个事务的 GTID。问题是，怎么能够让 MySQL 在执行事务后，返回包中带上 GTID 呢？
你只需要将参数 session_track_gtids 设置为 OWN_GTID，然后通过 API 接口 mysql_session_track_get_first 从返回包解析出 GTID 的值即可。

MySQL 并没有提供这类接口的 SQL 用法，是提供给程序的 API。比如，为了让客户端在事务提交后，返回的 GITD 能够在客户端显示出来，对 MySQL 客户端代码做点修改，如下所示：

这样，就可以看到语句执行完成，显示出 GITD 的值。

思考题

如果一个事务被kill之后，持续处于回滚状态，从恢复速度的角度看，你是应该重启等它执行结束，还是应该强行重启整个MySQL进程。

因为重启之后该做的回滚动作还是不能少的，所以从恢复速度的角度来说，应该让它自己结束。
当然，如果这个语句可能会占用别的锁，或者由于占用IO资源过多，从而影响到了别的语句执行的话，就需要先做主备切换，切到新主库提供服务。
切换之后别的线程都断开了连接，自动停止执行。接下来还是等它自己执行完成。这个操作属于我们在文章中说到的，减少系统压力，加速终止逻辑。

全表扫描对innodb的影响

InnoDB内存的一个作用，是保存更新的结果，再配合redolog，就避免了随机写盘。

内存的数据页是在BufferPool(BP)中管理的，在WAL里BufferPool起到了加速更新的作用。而实际上，BufferPool还有一个更重要的作用，就是加速查询。

由于有WAL机制，当事务提交的时候，磁盘上的数据页是旧的，那如果这时候马上有一个查询要来读这个数据页，是不是要马上把redolog应用到数据页呢？答案是不需要。因为这时候内存数据页的结果是最新的，直接读内存页就可以了。这时候查询根本不需要读磁盘，直接从内存拿结果，速度是很快的。所以说，BufferPool还有加速查询的作用。
而BufferPool对查询的加速效果，依赖于一个重要的指标，即：内存命中率。

执行show engine innodb status，可以看到“Buffer pool hit rate”字样，显示的就是当前的命中率

InnoDB Buffer Pool 的大小是由参数 innodb_buffer_pool_size 确定的，一般建议设置 成可用物理内存的 60%~80%。

如果一个 Buffer Pool 满了，而又要从磁盘读入一个数据页，那肯定是要淘汰一个旧数据页的。InnoDB 内存管理用的是最近最少使用 (Least Recently Used, LRU) 算法，这个算法的核心就是淘汰最久未使用的数据。

基本的LRU算法模型

下图显示的是一个基本的LRU算法模型图：

InnoDB管理BufferPool的LRU算法，是用链表来实现的。

• 在上图的状态1里，链表头部是P1，表示P1是最近刚刚被访问过的数据页；假设内存里只能放下这么多数据页；
• 这时候有一个读请求访问P3，因此变成状态2，P3被移到最前面；
• 状态3表示，这次访问的数据页是不存在于链表中的，所以需要在BufferPool中新申请一个数据页Px，加到链表头部。但是由于内存已经满了，不能申请新的内存。于是，会清空链表末尾Pm这个数据页的内存，存入Px的内容，然后放到链表头部。
• 从效果上看，就是最久没有被访问的数据页Pm，被淘汰了。

假设按照这个算法，我们要扫描一个200G的表，而这个表是一个历史数据表，平时没有业务访问它。
那么，按照这个算法扫描的话，就会把当前的Buffer Pool里的数据全部淘汰掉，存入扫描过程中访问到的数据页的内容。也就是说Buffer Pool里面主要放的是这个历史数据表的数据。
对于一个正在做业务服务的库，这可不妙。你会看到，BufferPool的内存命中率急剧下降，磁盘压力增加，SQL语句响应变慢。
所以，InnoDB不能直接使用这个LRU算法。实际上，InnoDB对LRU算法做了改进,如下图：

在InnoDB实现上，按照5:3的比例把整个LRU链表分成了young区域和old区域。图中LRU_old指向的就是old区域的第一个位置，是整个链表的5/8处。也就是说，靠近链表头部的5/8是young区域，靠近链表尾部的3/8是old区域。

改进后的LRU算法执行流程变成了下面这样。

• 上图中状态1，要访问数据页P3，由于P3在young区域，因此和优化前的LRU算法一样，将其移到链表头部，变成状态2。
• 之后要访问一个新的不存在于当前链表的数据页，这时候依然是淘汰掉数据页Pm，但是新插入的数据页Px，是放在LRU_old处。
• 处于old区域的数据页，每次被访问的时候都要做下面这个判断：
  这个策略，就是为了处理类似全表扫描的操作量身定制的。还是以刚刚的扫描200G的历史数据表为例，我们看看改进后的LRU算法的操作逻辑：
  • 扫描过程中，需要新插入的数据页，都被放到old区域;
  • 一个数据页里面有多条记录，这个数据页会被多次访问到，但由于是顺序扫描，这个数据页第一次被访问和最后一次被访问的时间间隔不会超过1秒，因此还是会被保留在old区域；
  • 再继续扫描后续的数据，之前的这个数据页之后也不会再被访问到，于是始终没有机会移到链表头部（也就是young区域），很快就会被淘汰出去。

可以看到，这个策略最大的收益，就是在扫描这个大表的过程中，虽然也用到了BufferPool，但是对young区域完全没有影响，从而保证了Buffer Pool响应正常业务的查询命中率。

思考题

如果客户端由于压力过大，迟迟不能接收数据，会对服务端造成什么严重的影响？
这个问题的核心是，造成了“长事务”。至于长事务的影响，就要结合我们前面文章中提到的锁、MVCC的知识点了。

如果前面的语句有更新，意味着它们在占用着行锁，会导致别的语句更新被锁住；
当然读的事务也有问题，就是会导致 undo log 不能被回收，导致回滚段空间膨胀。

join语句

下面我们看下join语句到底是怎么执行的，首先执行如下语句创建两个表,并向其中插入数据：

CREATE TABLE `t2` (
	`id` INT ( 11 ) NOT NULL,
	`a` INT ( 11 ) DEFAULT NULL,
	`b` INT ( 11 ) DEFAULT NULL,
	PRIMARY KEY ( `id` ),
KEY `a` ( `a` ) 
) ENGINE = INNODB;
#通过存储过程插入数据
DROP PROCEDURE IF EXISTS idata;

delimiter;;
CREATE PROCEDURE idata () BEGIN
	DECLARE
		i INT;
	
	SET i = 1;
	WHILE
			( i <= 1000 ) DO
			INSERT INTO t2
		VALUES
			( i, i, i );
		
		SET i = i + 1;
		
	END WHILE;
	
END;;

delimiter;
CALL idata ();
#创建另外一张表
create table t1 like t2; 
insert into t1 (select * from t2 where id<=100)

可以看到，这两个表都有一个主键索引id和一个索引a，字段b上无索引。存储过程idata()往表t2里插入了1000行数据，在表t1里插入的是100行数据。

Index Nested-Loop Join

我们查看下下面语句：

select * from t1 straight_join t2 on (t1.a=t2.a);

如果直接使用join语句，MySQL优化器可能会选择表t1或t2作为驱动表，这样会影响我们分析SQL语句的执行过程。所以，为了便于分析执行过程中的性能问题，我改用straight_join让MySQL使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去join。在这个语句里，t1是驱动表，t2是被驱动表。

下面是这条语句的explain结果：

可以看到，在这条语句里，被驱动表t2的字段a上有索引，join过程用上了这个索引，因此这个语句的执行流程是这样的：

• 从表t1中读入一行数据R；
• 从数据行R中，取出a字段到表t2里去查找；
• 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
• 重复执行步骤1到3，直到表t1的末尾循环结束。

这个过程是先遍历表t1，然后根据从表t1中取出的每行数据中的a值，去表t2中查找满足条件的记录。在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为Index Nested-Loop Join，简称NLJ。

在这个流程里：

• 对驱动表t1做了全表扫描，这个过程需要扫描100行；
• 而对于每一行R，根据a字段去表t2查找，走的是树搜索过程。由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行，也是总共扫描100行；
• 所以，整个执行流程，总扫描行数是200。

怎么选择驱动表

在这个join语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。
假设被驱动表的行数是M。每次在被驱动表查一行数据，要先搜索索引a，再搜索主键索引。每次搜索一棵树近似复杂度是以2为底的M的对数，记为logM，所以在被驱动表上查一行的时间复杂度是2*logM。
假设驱动表的行数是N，执行过程就要扫描驱动表N行，然后对于每一行，到被驱动表上匹配一次。

因此整个执行过程，近似复杂度是N+N2logM。
显然，N对扫描行数的影响更大，因此应该让小表来做驱动表。

到这里小结一下，通过上面的分析我们得到了两个结论：
1.使用join语句，性能比强行拆成多个单表执行SQL语句的性能要好；2.如果使用join语句的话，需要让小表做驱动表。
但是，你需要注意，这个结论的前提是“可以使用被驱动表的索引”。假如用不上被驱动表索引，怎么选择？

Simple Nested-Loop Join

假如我们的查询语句变成如下：

 select * from t1 straight_join t2 on (t1.a=t2.b);

由于表t2的字段b上没有索引，因此再用图2的执行流程时，每次到t2去匹配的时候，就要做一次全表扫描。这种情况叫做“Simple Nested Loop Join”。
但是，这样算来，这个SQL请求就要扫描表t2多达100次，总共扫描100*1000=10万行。

这还只是两个小表，如果t1和t2都是10万行的表（当然了，这也还是属于小表的范围），就要扫描100亿行，这个算法看上去太“笨重”了。
当然，MySQL也没有使用这个SimpleNested-LoopJoin算法，而是使用了另一个叫作Block Nested-Loop Join的算法，简称BNL。

Block Nested-Loop Join

这时候，被驱动表上没有可用的索引，算法的流程是这样的：

• 把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select*，因此是把整个表t1放入了内存；
• 扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。
  这个过程的流程图如下：
  

这条 SQL 语句的 explain 结果如下所示：

可以看到，在这个过程中，对表t1和t2都做了一次全表扫描，因此总的扫描行数是1100。由于join_buffer是以无序数组的方式组织的，因此对表t2中的每一行，都要做100次判断，总共需要在内存中做的判断次数是：100*1000=10万次。

如果使用Simple Nested-Loop Join算法进行查询，扫描行数也是10万行。因此，从时间复杂度上来说，这两个算法是一样的。但是，Block Nested-Loop Join算法的这10万次判断是内存操作，速度上会快很多，性能也更好。

怎么选择驱动表

假设小表的行数是N，大表的行数是M，那么在这个算法里：

• 两个表都做一次全表扫描，所以总的扫描行数是M+N；
• 内存中的判断次数是M*N。

可以看到，调换这两个算式中的M和N没差别，因此这时候选择大表还是小表做驱动表，执行耗时是一样的。

这个例子里表t1才100行，要是表t1是一个大表，join_buffer放不下怎么办呢？
join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。如果放不下表t1的所有数据话，策略很简单，就是分段放，然后每段执行流程和上面一样，一块执行完执行另一块（block）这也是名字的由来。

那么在分段放的情况下怎么选择驱动表呢？

假设，驱动表的数据行数是N，需要分K段才能完成算法流程，被驱动表的数据行数是M。
注意，这里的K不是常数，N越大K就会越大，因此把K表示为λ*N，显然λ的取值范围是(0,1)。

所以，在这个算法的执行过程中：

1. 扫描行数是 N+λ*N*M； 2. 内存判断 N*M 次。

显然，内存判断次数是不受选择哪个表作为驱动表影响的。而考虑到扫描行数，在 M 和 N 大小确定的情况下，N 小一些，整个算式的结果会更小。
所以结论是，应该让小表当驱动表。

能不能使用join语句

• 如果可以使用Index Nested-Loop Join算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
• 如果使用Block Nested-Loop Join算法，扫描行数就会过多。尤其是在大表上的join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种join尽量不要用。

所以你在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样,出现了就尽量不要使用。

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤， 过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小 表”，应该作为驱动表。

join语句怎么优化

假如有如下语句：

select * from t1 where a>=1 and a<=100;

其中a是普通索引，我们知道查询的时候需要先根据a查询到主键，然后回表查询其他数据。

在介绍 join 语句的优化方案之前，先介绍一个知识点，即：Multi-Range Read 优化 (MRR)。这个优化的主要目的是尽量使用顺序读盘。

因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能

这，就是 MRR 优化的设计思路。此时，语句的执行流程变成了这样：

• 根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中 ;
• 将 read_rnd_buffer 中的 id 进行递增排序；
• 排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回。

这里，read_rnd_buffer 的大小是由 read_rnd_buffer_size参数控制的。如果步骤 1 中， read_rnd_buffer 放满了，就会先执行完步骤 2 和 3，然后清空 read_rnd_buffer。之后 继续找索引 a 的下个记录，并继续循环。

需要说明的是，如果你想要稳定地使用MRR优化的话，需要设置setoptimizer_switch="mrr_cost_based=off"。（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用MRR，把mrr_cost_based设置为off，就是固定使用MRR了。）

MRR能够提升性能的核心在于，这条查询语句在索引a上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

Batched Key Access

理解了MRR性能提升的原理，我们就能理解MySQL在5.6版本后开始引入的Batched Key Acess(BKA)算法了。这个BKA算法，其实就是对NLJ算法的优化

上一节中讲过的NLJ算法执行的逻辑是：从驱动表t1，一行行地取出a的值，再到被驱动表t2去做join。也就是说，对于表t2来说，每次都是匹配一个值。这时，MRR的优势就用不上了

那怎么才能一次性地多传些值给表t2呢？方法就是，从表t1里一次性地多拿些行出来，一起传给表t2。
既然如此，我们就把表t1的数据取出来一部分，先放到一个临时内存。这个临时内存不是别人，就是join_buffer。
通过上一篇文章，我们知道join_buffer在BNL算法里的作用，是暂存驱动表的数据。但是在NLJ算法里并没有用。那么，我们刚好就可以复用join_buffer到BKA算法中

如果要使用 BKA 优化算法的话，你需要在执行 SQL 语句之前，先设置

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

其中，前两个参数的作用是要启用 MRR。这么做的原因是，BKA 算法的优化要依赖于 MRR。

BNL算法的性能问题

上面是NLJ算法的优化，我们再来看 BNL 算法的优化。

BNL 算法对系统的影响主要包括三个方面：

• 可能会多次扫描被驱动表，占用磁盘IO资源；
• 判断join条件需要执行M*N次对比（M、N分别是两张表的行数），如果是大表就会占用非常多的CPU资源；
• 可能会导致Buffer Pool的热数据被淘汰，影响内存命中率

我们执行语句之前，需要通过理论分析和查看explain结果的方式，确认是否要使用BNL算法。如果确认优化器会使用BNL算法，就需要做优化。优化的常见做法是，给被驱动表的join字段加上索引，把BNL算法转成BKA算法

BNL转BKA

一些情况下，我们可以直接在被驱动表上建索引，这时就可以直接转成 BKA 算法了。
但是，有时候你的查询语句要获取的数据量很小，且是一个低频查询语句，为此专门建一个索引就不值得了，比如下面语句：

 select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

这时候，我们可以考虑使用临时表。使用临时表的大致思路是：

• 把表t2中满足条件的数据放在临时表tmp_t中；
• 为了让join使用BKA算法，给临时表tmp_t的字段b加上索引；
• 让表t1和tmp_t做join操作。

不论是在原表上加索引，还是用有索引的临时表，我们的思路都是让 join 语句 能够用上被驱动表上的索引，来触发 BKA 算法，提升查询性能。