MySQL 读后总结 (三)

主库出问题了,从库怎么办?

基本的一主多从结构:

MySQL 读后总结 (三)

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

如图2所示为主库发生故障,主备切换后的结果。

MySQL 读后总结 (三)

相比于一主一备的切换流程,一主多从结构在切换完成后,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  

这条命令有这么6个参数:

  • 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上已经执行过的事务。

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

  1. 等待新主库A’把中转日志(relay log)全部同步完成;
  2. 在A’上执行show master status命令,得到当前A’上最新的File 和 Position;
  3. 取原主库A故障的时刻T;
  4. 用mysqlbinlog工具解析A’的File,得到T时刻的位点。
mysqlbinlog File --stop-datetime=T --start-datetime=T

MySQL 读后总结 (三)

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

当然这个值并不精确。为什么呢?

可以设想有这么一种情况,假设在T这个时刻,主库A已经执行完成了一个insert 语句插入了一行数据R,并且已经将binlog传给了A’和B,然后在传完的瞬间主库A的主机就掉电了。

那么,这时候系统的状态是这样的:

  1. 在从库B上,由于同步了binlog, R这一行已经存在;
  2. 在新主库A’上, R这一行也已经存在,日志是写在123这个位置之后的;
  3. 我们在从库B上执行change master命令,指向A’的File文件的123位置,就会把插入R这一行数据的binlog又同步到从库B去执行。

这时候,从库B的同步线程就会报告 Duplicate entry ‘id_of_R’ for key ‘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参数。等到主备间的同步关系建立完成,并稳定执行一段时间之后,我们还需要把这个参数设置为空,以免之后真的出现了主从数据不一致,也跳过了。

GTID

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

那么,GTID到底是什么意思,又是如何解决找同步位点这个问题呢?

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

GTID=server_uuid:gno

其中:

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

在MySQL的官方文档里,GTID格式是这么定义的:

GTID=source_id:transaction_id

这里的source_id就是server_uuid;而后面的这个transaction_id,容易造成误导,所以改成了gno。为什么说使用transaction_id容易造成误解呢?

因为,在MySQL里面我们说transaction_id就是指事务id,事务id是在事务执行过程中分配的,如果这个事务回滚了,事务id也会递增,而gno是在事务提交的时候才会分配。

从效果上看,GTID往往是连续的,因此我们用gno来表示更容易理解。

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

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

  1. 如果gtid_next=automatic,代表使用默认值。这时,MySQL就会把server_uuid:gno分配给这个事务。
    a. 记录binlog的时候,先记录一行 SET @@SESSION.GTID_NEXT=‘server_uuid:gno’;
    b. 把这个GTID加入本实例的GTID集合。
  2. 如果gtid_next是一个指定的GTID的值,比如通过set gtid_next=’current_gtid’指定为current_gtid,那么就有两种可能:
    a. 如果current_gtid已经存在于实例的GTID集合中,接下来执行的这个事务会直接被系统忽略;
    b. 如果current_gtid没有存在于实例的GTID集合中,就将这个current_gtid分配给接下来要执行的事务,也就是说系统不需要给这个事务生成新的GTID,因此gno也不用加1。

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

这样,每个MySQL实例都维护了一个GTID集合,用来对应“这个实例执行过的所有事务”。

用一个简单的例子你说明GTID的基本用法。

创建一个表t:

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

insert into t values(1,1);

MySQL 读后总结 (三)

可以看到,事务的BEGIN之前有一条SET @@SESSION.GTID_NEXT命令。这时,如果实例X有从库,那么将CREATE TABLE和insert语句的binlog同步过去执行的话,执行事务之前就会先执行这两个SET命令, 这样被加入从库的GTID集合的,就是图中的这两个GTID。

假设,现在这个实例X是另外一个实例Y的从库,并且此时在实例Y上执行了下面这条插入语句:

insert into t values(1,1);

并且,这条语句在实例Y上的GTID是 “aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10”。

那么,实例X作为Y的从库,就要同步这个事务过来执行,显然会出现主键冲突,导致实例X的同步线程停止。这时,应该怎么处理呢?
可以执行下面的这个语句序列:

set gtid_next='aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10';
begin;
commit;
set gtid_next=automatic;
start slave;

其中,前三条语句的作用,是通过提交一个空事务,把这个GTID加到实例X的GTID集合中。如图5所示,就是执行完这个空事务之后的show master status的结果。

MySQL 读后总结 (三)

可以看到实例X的Executed_Gtid_set里面,已经加入了这个GTID。

这样,再执行start slave命令让同步线程执行起来的时候,虽然实例X上还是会继续执行实例Y传过来的事务,但是由于“aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10”已经存在于实例X的GTID集合中了,所以实例X就会直接跳过这个事务,也就不会再出现主键冲突的错误。

在上面的这个语句序列中,start slave命令之前还有一句set gtid_next=automatic。这句话的作用是“恢复GTID的默认分配行为”,也就是说如果之后有新的事务再执行,就还是按照原来的分配方式,继续分配gno=3。

基于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参数已经不需要指定了。

在实例B上执行start slave命令,取binlog的逻辑是这样的:

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

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

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

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

由于不需要找位点了,所以从库B、C、D只需要分别执行change master命令指向实例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和在线DDL

在之前业务高峰期的慢查询性能问题时,如果是由于索引缺失引起的性能问题,可以通过在线加索引来解决。但是,考虑到要避免新增索引对主库性能造成的影响,我们可以先在备库加索引,然后再切换。

在双M结构下,备库执行的DDL语句也会传给主库,为了避免传回后对主库造成影响,要通过set sql_log_bin=off关掉binlog。

这样操作的话,数据库里面是加了索引,但是binlog并没有记录下这一个更新,是不是会导致数据和日志不一致?

假设,这两个互为主备关系的库还是实例X和实例Y,且当前主库是X,并且都打开了GTID模式。这时的主备切换流程可以变成下面这样:

  • 在实例X上执行stop slave。
  • 在实例Y上执行DDL语句。注意,这里并不需要关闭binlog。
  • 执行完成后,查出这个DDL语句对应的GTID,并记为 server_uuid_of_Y:gno。
  • 到实例X上执行以下语句序列:
    set GTID_NEXT="server_uuid_of_Y:gno";
    begin;
    commit;
    set gtid_next=automatic;
    start slave;

这样做的目的在于,既可以让实例Y的更新有binlog记录,同时也可以确保不会在实例X上执行这条更新。

  • 接下来,执行完主备切换,然后照着上述流程再执行一遍即可。

问题1:在实际工作中,主从备份似乎是mysql用的最多的高可用方案。
但是主从备份这个方案的问题太多:

  1. binlog数据传输前,主库宕机,导致提交了的事务数据丢失。
  2. 一主多从,即使采用半同步,也只能保证binlog至少在两台机器上,没有一个机制能够选出拥有最完整binlog的从库作为新的主库。
  3. 主从切换涉及到 人为操作,而不是全自动化的。即使在使用GTID的情况下,也会有binlog被删除,需要重新做从库的情况。
  4. 互为主备,如果互为主备的两个实例全部宕机,mysql直接不可用。

mysql应该有更强大更完备的高可用方案(类似于zab协议或者raft协议这种),而在实际环境下,为什么主从备份用得最多呢?

  • 3 这个应该是可以做到自动化的。
  • 4 概率比较小,其实即使是别的三节点的方案,也架不住挂两个实例,所以这个不是MySQL主备的锅。

MySQL到现在,还是提供了很多方案可选的。很多是业务权衡的结果。

  • 比如说,异步复制,在主库异常掉电的时候可能会丢数据。
  • 这个大家知道以后,有一些就改成semi-sync了,但是还是有一些就留着异步复制的模式,因为semi-sync有性能影响(一开始35%,现在好点15%左右,看具体环境),而可能这些业务认为丢一两行,可以从应用层日志去补。 就保留了异步复制模式。

最后,为什么主从备份用得最多,有一部分历史原因。多年前MySQL刚要开始火的时候,大家发现这个主备模式好方便,就都用了。
而基于其他协议的方案,都是后来出现的,并且还是陆陆续续出点bug。
涉及到线上服务,大家使用新方案的热情总是局限在测试环境的多。

semi-sync也是近几年才开始稳定并被一些公司开始作为默认配置。

问题2:在GTID模式下,如果一个新的从库接上主库,但是需要的binlog已经没了,要怎么做?

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

读写分离有哪些坑

MySQL 读后总结 (三)

读写分离的主要目标就是分摊主库的压力。图1中的结构是客户端(client)主动做负载均衡,这种模式下一般会把数据库的连接信息放在客户端的连接层。也就是说,由客户端来选择后端数据库进行查询。

还有一种架构是,在MySQL和客户端之间有一个中间代理层proxy,客户端只连接proxy, 由proxy根据请求类型和上下文决定请求的分发路由。

MySQL 读后总结 (三)

客户端直连和带proxy的读写分离架构,各有哪些特点。

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

目前看,趋势是往带proxy的架构方向发展的。

这种“在从库上会读到系统的一个过期状态”的现象,暂且称之为“过期读”

不论哪种结构,客户端都希望查询从库的数据结果,跟查主库的数据结果是一样的。

怎么处理过期读问题:

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

强制走主库方案

强制走主库方案其实就是,将查询请求做分类。通常情况下,我们可以将查询请求分为这么两类:

  1. 对于必须要拿到最新结果的请求,强制将其发到主库上。比如,在一个交易平台上,卖家发布商品以后,马上要返回主页面,看商品是否发布成功。那么,这个请求需要拿到最新的结果,就必须走主库。
  2. 对于可以读到旧数据的请求,才将其发到从库上。在这个交易平台上,买家来逛商铺页面,就算晚几秒看到最新发布的商品,也是可以接受的。那么,这类请求就可以走从库。

这个方案是不是有点畏难和取巧的意思,但其实这个方案是用得最多的。

当然,这个方案最大的问题在于,有时候会碰到“所有查询都不能是过期读”的需求,比如一些金融类的业务。这样的话,就要放弃读写分离,所有读写压力都在主库,等同于放弃了扩展性。

Sleep 方案

主库更新后,读从库之前先sleep一下。具体的方案就是,类似于执行一条select sleep(1)命令。

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

这个方案给人的第一感觉,很可能是不靠谱儿,应该不会有人用吧?并且,还可能会说,直接在发起查询时先执行一条sleep语句,用户体验很不友好啊。

但,这个思路确实可以在一定程度上解决问题。

以卖家发布商品为例,商品发布后,用Ajax(Asynchronous JavaScript + XML,异步JavaScript和XML)直接把客户端输入的内容作为“新的商品”显示在页面上,而不是真正地去数据库做查询。

这样,卖家就可以通过这个显示,来确认产品已经发布成功了。等到卖家再刷新页面,去查看商品的时候,其实已经过了一段时间,也就达到了sleep的目的,进而也就解决了过期读的问题。

也就是说,这个sleep方案确实解决了类似场景下的过期读问题。但,从严格意义上来说,这个方案存在的问题就是不精确。这个不精确包含了两层意思:

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

判断主备无延迟方案

要确保备库无延迟,通常有三种做法。
show slave status结果里的seconds_behind_master参数的值,可以用来衡量主备延迟时间的长短。

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

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

如图3所示,是一个show slave status结果的部分截图。

MySQL 读后总结 (三)

第二种方法,对比位点确保主备无延迟:

  • Master_Log_File和Read_Master_Log_Pos,表示的是读到的主库的最新位点;
  • Relay_Master_Log_File和Exec_Master_Log_Pos,表示的是备库执行的最新位点。

如果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在主备库之间的状态:

  1. 主库执行完成,写入binlog,并反馈给客户端;
  2. binlog被从主库发送给备库,备库收到;
  3. 在备库执行binlog完成。

上面判断主备无延迟的逻辑,是“备库收到的日志都执行完成了”。但是,从binlog在主备之间状态的分析中,不难看出还有一部分日志,处于客户端已经收到提交确认,而备库还没收到日志的状态。

如图4所示就是这样的一个状态。

MySQL 读后总结 (三)

这时,主库上执行完成了三个事务trx1、trx2和trx3,其中:

  1. trx1和trx2已经传到从库,并且已经执行完成了;
  2. trx3在主库执行完成,并且已经回复给客户端,但是还没有传到从库中。

如果这时候在从库B上执行查询请求,按照上面的逻辑,从库认为已经没有同步延迟,但还是查不到trx3的。严格地说,就是出现了过期读。

解决办法:

配合semi-sync

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

semi-sync做了这样的设计:

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

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

如果主库掉电的时候,有些binlog还来不及发给从库,会不会导致系统数据丢失?

  • 答案是,如果使用的是普通的异步复制模式,就可能会丢失,但semi-sync就可以解决这个问题。

这样,semi-sync配合前面关于位点的判断,就能够确定在从库上执行的查询请求,可以避免过期读。

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

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

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

实际上,回到最初的业务逻辑里,当发起一个查询请求以后,我们要得到准确的结果,其实并不需要等到“主备完全同步”。

看一下这个时序图:
MySQL 读后总结 (三)

图5所示,就是等待位点方案的一个bad case。图中备库B下的虚线框,分别表示relaylog和binlog中的事务。可以看到,图5中从状态1 到状态4,一直处于延迟一个事务的状态。

备库B一直到状态4都和主库A存在延迟,如果用上面必须等到无延迟才能查询的方案,select语句直到状态4都不能被执行。

但是,其实客户端是在发完trx1更新后发起的select语句,只需要确保trx1已经执行完成就可以执行select语句了。也就是说,如果在状态3执行查询请求,得到的就是预期结果了。

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

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

接下来的等主库位点方案,就可以解决这两个问题。

等主库位点方案

先看一条命令:

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。

对于图5中先执行trx1,再执行一个查询请求的逻辑,要保证能够查到正确的数据,可以使用这个逻辑:

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

流程如下:

MySQL 读后总结 (三)

这里假设,这条select查询最多在从库上等待1秒。那么,如果1秒内master_pos_wait返回一个大于等于0的整数,就确保了从库上执行的这个查询结果一定包含了trx1的数据。

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

如果所有的从库都延迟超过1秒了,那查询压力不就都跑到主库上了吗?确实是这样。

但是,按照我们设定不允许过期读的要求,就只有两种选择,一种是超时放弃,一种是转到主库查询。具体怎么选择,就需要业务开发做好限流策略。

GTID方案

如果你的数据库开启了GTID模式,对应的也有等待GTID的方案。

MySQL中同样提供了一个类似的命令:

 select wait_for_executed_gtid_set(gtid_set, 1);

这条命令的逻辑是:

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

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

这时,等GTID的执行流程就变成了:

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

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

流程如下:

MySQL 读后总结 (三)

在上面的第一步中,trx1事务更新完成后,从返回包直接获取这个事务的GTID。问题是,怎么能够让MySQL在执行事务后,返回包中带上GTID呢?

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

问题1:
关于 mysql_reset_connection ,这类接口应该怎么使用?

比如,为了让客户端在事务提交后,返回的GITD能够在客户端显示出来,对MySQL客户端代码做了点修改,如下所示:

MySQL 读后总结 (三)

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

MySQL 读后总结 (三)

这只是一个例子。要使用这个方案的时候,还是应该在你的客户端代码中调用mysql_session_track_get_first这个函数。

问题2:
如果使用GTID等位点的方案做读写分离,在对大表做DDL的时候会怎么样?

假设,这条语句在主库上要执行10分钟,提交后传到备库就要10分钟(典型的大事务)。那么,在主库DDL之后再提交的事务的GTID,去备库查的时候,就会等10分钟才出现。

这样,这个读写分离机制在这10分钟之内都会超时,然后走主库。

这种预期内的操作,应该在业务低峰期的时候,确保主库能够支持所有业务查询,然后把读请求都切到主库,再在主库上做DDL。等备库延迟追上以后,再把读请求切回备库。

主要关注的是,大事务对等位点方案的影响。

当然了,使用gh-ost方案来解决这个问题也是不错的选择。

问题3:
如果只有一台服务器来做判断,是否数据库出问题了,就是采用的update的方式。如果是主从架构就一条语句,如果是双主的话就是两条update语句。但是这种方式有很大的弊端,只有一个进程来判断数据库出问题的话,会出现单点判断的问题。所以后续准备多个单数进程来做判断,如果超过了半数以上的监控进程都认为数据库出问题,才做切换。
1.innodb_thread_concurrency的设置是不是应该跟计算机核数成正比,一般是1.5倍-2倍左右?

  • 虽然理论上是核数的2倍左右最好,但是现在很多人把MySQL创建在虚拟机上,就分1~2个核。

2.怎么之前遇到空间满了,数据库都登不上了,所有的连接都连不上,更不用执行select语句了,这个是什么原因啊?

  • 空间满本身是不会导致连不上的。但是因为空间满,事务无法提交,可能会导致接下来外部事务重试,新重试的业务还是堵在提交阶段,持续累积可能会把连接数用满。

问题4:
外部检测的时候,主备使用同一条更新语句,造成行冲突,导致主备同步停止.

外部检测是只是看更新语句的返回时间,health_check表在主库备库都有,为啥会造成行冲突?为啥会导致主备同步停止?

  • 比如两个表刚开始都是空表,
    然后第一个语句执行
    insert into mysql.health_check(id, t_modified) values (1, now()) on duplicate key update t_modified=now();
    就会两边各写入一个insert语句的binlog日志,传到对面就导致同步停止了。

判断一个数据库是否出问题

主备切换有两种场景,一种是主动切换,一种是被动切换。而其中被动切换,往往是因为主库出问题了,由HA系统发起的。

select 1判断

select 1成功返回,只能说明这个库的进程还在,并不能说明主库没问题。现在,我们来看一下这个场景。

set global innodb_thread_concurrency=3;

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

 insert into t values(1,1)

MySQL 读后总结 (三)

设置innodb_thread_concurrency参数的目的是,控制InnoDB的并发线程上限。也就是说,一旦并发线程数达到这个值,InnoDB在接收到新请求的时候,就会进入等待状态,直到有线程退出。

这里,把innodb_thread_concurrency设置成3,表示InnoDB只允许3个线程并行执行。而在我们的例子中,前三个session 中的sleep(100),使得这三个语句都处于“执行”状态,以此来模拟大查询。

session D里面,select 1是能执行成功的,但是查询表t的语句会被堵住。也就是说,如果这时候用select 1来检测实例是否正常的话,是检测不出问题的。

在InnoDB中,innodb_thread_concurrency这个参数的默认值是0,表示不限制并发线程数量。但是,不限制并发线程数肯定是不行的。因为,一个机器的CPU核数有限,线程全冲进来,上下文切换的成本就会太高。

所以,通常情况下,建议把innodb_thread_concurrency设置为64~128之间的值。这时会有一个疑问,并发线程上限数设置为128够干啥,线上的并发连接数动不动就上千了。

并发连接和并发查询,并不是同一个概念。你在show processlist的结果里,看到的几千个连接,指的就是并发连接。而“当前正在执行”的语句,才是所说的并发查询。

并发连接数达到几千个影响并不大,就是多占一些内存而已。应该关注的是并发查询,因为并发查询太高才是CPU杀手。这也是为什么需要设置innodb_thread_concurrency参数的原因。

如果把innodb_thread_concurrency设置为128的话,那么出现同一行热点更新的问题时,是不是很快就把128消耗完了,这样整个系统是不是就挂了呢?

  • 实际上,在线程进入锁等待以后,并发线程的计数会减一,也就是说等行锁(也包括间隙锁)的线程是不算在128里面的。

MySQL这样设计是非常有意义的。因为,进入锁等待的线程已经不吃CPU了;更重要的是,必须这么设计,才能避免整个系统锁死。

为什么呢?假设处于锁等待的线程也占并发线程的计数,可以设想一下这个场景:

  1. 线程1执行begin; update t set c=c+1 where id=1, 启动了事务trx1, 然后保持这个状态。这时候,线程处于空闲状态,不算在并发线程里面。
  2. 线程2到线程129都执行 update t set c=c+1 where id=1; 由于等行锁,进入等待状态。这样就有128个线程处于等待状态;
  3. 如果处于锁等待状态的线程计数不减一,InnoDB就会认为线程数用满了,会阻止其他语句进入引擎执行,这样线程1不能提交事务。而另外的128个线程又处于锁等待状态,整个系统就堵住了。

下图2显示的就是这个状态。

MySQL 读后总结 (三)
这时候InnoDB不能响应任何请求,整个系统被锁死。而且,由于所有线程都处于等待状态,此时占用的CPU却是0,而这明显不合理。所以,我们说InnoDB在设计时,遇到进程进入锁等待的情况时,将并发线程的计数减1的设计,是合理而且是必要的。

虽然说等锁的线程不算在并发线程计数里,但如果它在真正地执行查询,就比如上面例子中前三个事务中的select sleep(100) from t,还是要算进并发线程的计数的。

在这个例子中,同时在执行的语句超过了设置的innodb_thread_concurrency的值,这时候系统其实已经不行了,但是通过select 1来检测系统,会认为系统还是正常的。

因此,使用select 1的判断逻辑要修改一下。

查表判断

为了能够检测InnoDB并发线程数过多导致的系统不可用情况,需要找一个访问InnoDB的场景。一般的做法是,在系统库(mysql库)里创建一个表,比如命名为health_check,里面只放一行数据,然后定期执行:

mysql> select * from mysql.health_check; 

使用这个方法,可以检测出由于并发线程过多导致的数据库不可用的情况。

但是,马上还会碰到下一个问题,即:空间满了以后,这种方法又会变得不好使。

更新事务要写binlog,而一旦binlog所在磁盘的空间占用率达到100%,那么所有的更新语句和事务提交的commit语句就都会被堵住。但是,系统这时候还是可以正常读数据的。

因此还是把这条监控语句再改进一下。接下来,就再来看看把查询语句改成更新语句后的效果。

更新判断

既然要更新,就要放个有意义的字段,常见做法是放一个timestamp字段,用来表示最后一次执行检测的时间。这条更新语句类似于:

mysql> update mysql.health_check set t_modified=now();

节点可用性的检测都应该包含主库和备库。如果用更新来检测主库的话,那么备库也要进行更新检测。

但,备库的检测也是要写binlog的。由于一般会把数据库A和B的主备关系设计为双M结构,所以在备库B上执行的检测命令,也要发回给主库A。

但是,如果主库A和备库B都用相同的更新命令,就可能出现行冲突,也就是可能会导致主备同步停止。所以,现在看来mysql.health_check 这个表就不能只有一行数据了。

为了让主备之间的更新不产生冲突,可以在mysql.health_check表上存入多行数据,并用A、B的server_id做主键。

mysql> CREATE TABLE `health_check` (
  `id` int(11) NOT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

/* 检测命令 */
insert into mysql.health_check(id, t_modified) values (@@server_id, now()) on duplicate key update t_modified=now();

由于MySQL规定了主库和备库的server_id必须不同(否则创建主备关系的时候就会报错),这样就可以保证主、备库各自的检测命令不会发生冲突。

更新判断是一个相对比较常用的方案了,不过依然存在一些问题。其中,“判定慢”一直是让DBA头疼的问题。

更新语句,如果失败或者超时,就可以发起主备切换了,为什么还会有判定慢的问题呢?

这里涉及到的是服务器IO资源分配的问题。

首先,所有的检测逻辑都需要一个超时时间N。执行一条update语句,超过N秒后还不返回,就认为系统不可用。

可以设想一个日志盘的IO利用率已经是100%的场景。这时候,整个系统响应非常慢,已经需要做主备切换了。

IO利用率100%表示系统的IO是在工作的,每个请求都有机会获得IO资源,执行自己的任务。而在检测使用的update命令,需要的资源很少,所以可能在拿到IO资源的时候就可以提交成功,并且在超时时间N秒未到达之前就返回给了检测系统。

检测系统一看,update命令没有超时,于是就得到了“系统正常”的结论。

也就是说,这时候在业务系统上正常的SQL语句已经执行得很慢了,但是DBA上去一看,HA系统还在正常工作,并且认为主库现在处于可用状态。

之所以会出现这个现象,根本原因是上面说的所有方法,都是基于外部检测的。外部检测天然有一个问题,就是随机性。

因为,外部检测都需要定时轮询,所以系统可能已经出问题了,但是却需要等到下一个检测发起执行语句的时候,才有可能发现问题。而且,如果运气不够好的话,可能第一次轮询还不能发现,这就会导致切换慢的问题。

内部统计

针对磁盘利用率这个问题,如果MySQL可以告诉我们,内部每一次IO请求的时间,那我们判断数据库是否出问题的方法就可靠得多了。

其实,MySQL 5.6版本以后提供的performance_schema库,就在file_summary_by_event_name表里统计了每次IO请求的时间。

file_summary_by_event_name表里有很多行数据,先来看看event_name=’wait/io/file/innodb/innodb_log_file’这一行。

MySQL 读后总结 (三)

图中这一行表示统计的是redo log的写入时间,第一列EVENT_NAME 表示统计的类型。

接下来的三组数据,显示的是redo log操作的时间统计。

第一组五列,是所有IO类型的统计。其中,COUNT_STAR是所有IO的总次数,接下来四列是具体的统计项, 单位是皮秒;前缀SUM、MIN、AVG、MAX,顾名思义指的就是总和、最小值、平均值和最大值。

第二组六列,是读操作的统计。最后一列SUM_NUMBER_OF_BYTES_READ统计的是,总共从redo log里读了多少个字节。

第三组六列,统计的是写操作。

最后的第四组数据,是对其他类型数据的统计。在redo log里,可以认为它们就是对fsync的统计。

在performance_schema库的file_summary_by_event_name表里,binlog对应的是event_name = “wait/io/file/sql/binlog”这一行。各个字段的统计逻辑,与redo log的各个字段完全相同。这里,我就不再赘述了。

因为在每一次操作数据库时,performance_schema都需要额外地统计这些信息,所以打开这个统计功能是有性能损耗的。

测试结果是,如果打开所有的performance_schema项,性能大概会下降10%左右。所以建议只打开自己需要的项进行统计。可以通过下面的方法打开或者关闭某个具体项的统计。

如果要打开redo log的时间监控,可以执行这个语句:

mysql> update setup_instruments set ENABLED='YES', Timed='YES' where name like '%wait/io/file/innodb/innodb_log_file%';

假设,现在已经开启了redo log和binlog这两个统计信息,那要怎么把这个信息用在实例状态诊断上呢?

很简单,可以通过MAX_TIMER的值来判断数据库是否出问题了。比如,可以设定阈值,单次IO请求时间超过200毫秒属于异常,然后使用类似下面这条语句作为检测逻辑。

mysql> select event_name,MAX_TIMER_WAIT  FROM performance_schema.file_summary_by_event_name where event_name in ('wait/io/file/innodb/innodb_log_file','wait/io/file/sql/binlog') and MAX_TIMER_WAIT>200*1000000000;

发现异常后,取到需要的信息,再通过下面这条语句:

mysql> truncate table performance_schema.file_summary_by_event_name;

把之前的统计信息清空。这样如果后面的监控中,再次出现这个异常,就可以加入监控累积值了。

用动态的观点看加锁

加锁规则:这个规则中,包含了两个“原则”、两个“优化”和一个“bug”:

  • 原则1:加锁的基本单位是next-key lock。希望你还记得,next-key lock是前开后闭区间。
  • 原则2:查找过程中访问到的对象才会加锁。
  • 优化1:索引上的等值查询,给唯一索引加锁的时候,next-key lock退化为行锁。
  • 优化2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock退化为间隙锁。
  • 一个bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。

基于表t:

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);

不等号条件里的等值查询

等值查询和“遍历”有什么区别?

看一个例子,分析一下这条查询语句的加锁范围:

begin;
select * from t where id>9 and id<12 order by id desc for update;

利用上面的加锁规则,知道这个语句的加锁范围是主键索引上的 (0,5]、(5,10]和(10, 15)。也就是说,id=15这一行,并没有被加上行锁。为什么呢?

加锁单位是next-key lock,都是前开后闭区间,但是这里用到了优化2,即索引上的等值查询,向右遍历的时候id=15不满足条件,所以next-key lock退化为了间隙锁 (10, 15)。

但是,查询语句中where条件是大于号和小于号,这里的“等值查询”又是从哪里来的呢?

要知道,加锁动作是发生在语句执行过程中的,所以在分析加锁行为的时候,要从索引上的数据结构开始。

表的索引id的示意图:

MySQL 读后总结 (三)

  1. 首先这个查询语句的语义是order by id desc,要拿到满足条件的所有行,优化器必须先找到“第一个id<12的值”。
  2. 这个过程是通过索引树的搜索过程得到的,在引擎内部,其实是要找到id=12的这个值,只是最终没找到,但找到了(10,15)这个间隙。
  3. 然后向左遍历,在遍历过程中,就不是等值查询了,会扫描到id=5这一行,所以会加一个next-key lock (0,5]。

也就是说,在执行过程中,通过树搜索的方式定位记录的时候,用的是“等值查询”的方法。

等值查询的过程

这个语句的加锁范围是什么?

begin;
select id from t where c in(5,20,10) lock in share mode;

这条查询语句里用的是in,先来看这条语句的explain结果。

MySQL 读后总结 (三)

可以看到,这条in语句使用了索引c并且rows=3,说明这三个值都是通过B+树搜索定位的。

在查找c=5的时候,先锁住了(0,5]。但是因为c不是唯一索引,为了确认还有没有别的记录c=5,就要向右遍历,找到c=10才确认没有了,这个过程满足优化2,所以加了间隙锁(5,10)。

同样的,执行c=10这个逻辑的时候,加锁的范围是(5,10] 和 (10,15);执行c=20这个逻辑的时候,加锁的范围是(15,20] 和 (20,25)。

这条语句在索引c上加的三个记录锁的顺序是:先加c=5的记录锁,再加c=10的记录锁,最后加c=20的记录锁。

这个加锁范围,不就是从(5,25)中去掉c=15的行锁吗?为什么这么麻烦地分段说呢?

  • 因为这些锁是“在执行过程中一个一个加的”,而不是一次性加上去的。

分析下面例子中的死锁问题:

select id from t where c in(5,20,10) order by c desc for update;

此时的加锁范围,又是什么呢?

间隙锁是不互锁的,但是这两条语句都会在索引c上的c=5、10、20这三行记录上加记录锁。

由于语句里面是order by c desc, 这三个记录锁的加锁顺序,是先锁c=20,然后c=10,最后是c=5。

也就是说,这两条语句要加锁相同的资源,但是加锁顺序相反。当这两条语句并发执行的时候,就可能出现死锁。

关于死锁的信息,MySQL只保留了最后一个死锁的现场,但这个现场还是不完备的。

怎么看死锁?

图3是在出现死锁后,执行show engine innodb status命令得到的部分输出。这个命令会输出很多信息,有一节LATESTDETECTED DEADLOCK,就是记录的最后一次死锁信息。

MySQL 读后总结 (三)

我们来看看这图中的几个关键信息。

  1. 这个结果分成三部分:
    • (1) TRANSACTION,是第一个事务的信息;
    • (2) TRANSACTION,是第二个事务的信息;
    • WE ROLL BACK TRANSACTION (1),是最终的处理结果,表示回滚了第一个事务。
  2. 第一个事务的信息中:
    • WAITING FOR THIS LOCK TO BE GRANTED,表示的是这个事务在等待的锁信息;
    • index c of table test.t,说明在等的是表t的索引c上面的锁;
    • lock mode S waiting 表示这个语句要自己加一个读锁,当前的状态是等待中;
    • Record lock说明这是一个记录锁;
    • n_fields 2表示这个记录是两列,也就是字段c和主键字段id;
    • 0: len 4; hex 0000000a; asc ;;是第一个字段,也就是c。值是十六进制a,也就是10;
    • 1: len 4; hex 0000000a; asc ;;是第二个字段,也就是主键id,值也是10;
    • 这两行里面的asc表示的是,接下来要打印出值里面的“可打印字符”,但10不是可打印字符,因此就显示空格。
    • 第一个事务信息就只显示出了等锁的状态,在等待(c=10,id=10)这一行的锁。
    • 当然你是知道的,既然出现死锁了,就表示这个事务也占有别的锁,但是没有显示出来。别着急,我们从第二个事务的信息中推导出来。
  3. 第二个事务显示的信息要多一些:
    • “ HOLDS THE LOCK(S)”用来显示这个事务持有哪些锁;
    • index c of table test.t 表示锁是在表t的索引c上;
    • hex 0000000a和hex 00000014表示这个事务持有c=10和c=20这两个记录锁;
    • WAITING FOR THIS LOCK TO BE GRANTED,表示在等(c=5,id=5)这个记录锁。

从上面这些信息中,我们就知道:

  1. “lock in share mode”的这条语句,持有c=5的记录锁,在等c=10的锁;
  2. “for update”这个语句,持有c=20和c=10的记录锁,在等c=5的记录锁。

因此导致了死锁,得到的两个结论:

  1. 由于锁是一个个加的,要避免死锁,对同一组资源,要按照尽量相同的顺序访问;
  2. 在发生死锁的时刻,for update 这条语句占有的资源更多,回滚成本更大,所以InnoDB选择了回滚成本更小的lock in share mode语句,来回滚。

怎么看锁等待?

MySQL 读后总结 (三)

可以看到,由于session A并没有锁住c=10这个记录,所以session B删除id=10这一行是可以的。但是之后,session B再想insert id=10这一行回去就不行了。

一起看一下此时show engine innodb status的结果,看看能不能给出一些提示。锁信息是在这个命令输出结果的TRANSACTIONS这一节。

MySQL 读后总结 (三)

来看几个关键信息。

  • index PRIMARY of table test.t ,表示这个语句被锁住是因为表t主键上的某个锁。
  • lock_mode X locks gap before rec insert intention waiting 这里有几个信息:
    • insert intention表示当前线程准备插入一个记录,这是一个插入意向锁。为了便于理解,你可以认为它就是这个插入动作本身。
    • gap before rec 表示这是一个间隙锁,而不是记录锁。
  • 那么这个gap是在哪个记录之前的呢?接下来的0~4这5行的内容就是这个记录的信息。
  • n_fields 5也表示了,这一个记录有5列:
    • 0: len 4; hex 0000000f; asc ;;第一列是主键id字段,十六进制f就是id=15。所以,这时我们就知道了,这个间隙就是id=15之前的,因为id=10已经不存在了,它表示的就是(5,15)。
    • 1: len 6; hex 000000000513; asc ;;第二列是长度为6字节的事务id,表示最后修改这一行的是trx id为1299的事务。
    • 2: len 7; hex b0000001250134; asc % 4;; 第三列长度为7字节的回滚段信息。可以看到,这里的acs后面有显示内容(%和4),这是因为刚好这个字节是可打印字符。
    • 后面两列是c和d的值,都是15。

由于delete操作把id=10这一行删掉了,原来的两个间隙(5,10)、(10,15)变成了一个(5,15)。

说到这里,可以联合起来再思考一下这两个现象之间的关联:

  1. session A执行完select语句后,什么都没做,但它加锁的范围突然“变大”了;
  2. 当我们执行select * from t where c>=15 and c<=20 order by c desc lock in share mode; 向左扫描到c=10的时候,要把(5, 10]锁起来。

也就是说,所谓“间隙”,其实根本就是由“这个间隙右边的那个记录”定义的。

update的例子

再来看一个update语句的案例:

MySQL 读后总结 (三)

可以自己分析一下,session A的加锁范围是索引c上的 (5,10]、(10,15]、(15,20]、(20,25]和(25,supremum]。

注意:根据c>5查到的第一个记录是c=10,因此不会加(0,5]这个next-key lock。

之后session B的第一个update语句,要把c=5改成c=1,可以理解为两步:

  1. 插入(c=1, id=5)这个记录;
  2. 删除(c=5, id=5)这个记录。

索引c上(5,10)间隙是由这个间隙右边的记录,也就是c=10定义的。所以通过这个操作,session A的加锁范围变成了图7所示的样子:

MySQL 读后总结 (三)

接下来session B要执行 update t set c = 5 where c = 1这个语句了,一样地可以拆成两步:

  1. 插入(c=5, id=5)这个记录;
  2. 删除(c=1, id=5)这个记录。

第一步试图在已经加了间隙锁的(1,10)中插入数据,所以就被堵住了。

问题1:
死锁日志里,lock_mode X waiting是间隙锁+行锁,lock_mode X locks rec but not gap这种加but not gap才是行锁?

  • lock_mode X waiting表示next-key lock;
    lock_mode X locks rec but not gap是只有行锁;
    还有一种 “locks gap before rec”,就是只有间隙锁;

问题2:
一个空表就只有一个间隙。比如,在空表上执行:

begin;
select * from t where id>1 for update;

这个查询语句加锁的范围就是next-key lock (-∞, supremum]。

验证方法可以使用下面的操作序列。可以在图4中看到显示的结果。

MySQL 读后总结 (三)

MySQL 读后总结 (三)

误删数据后除了跑路,还能怎么办?

MySQL相关的误删数据,有几个分类:

  1. 使用delete语句误删数据行;
  2. 使用drop table或者truncate table语句误删数据表;
  3. 使用drop database语句误删数据库;
  4. 使用rm命令误删整个MySQL实例。

误删行

如果是使用delete语句误删了数据行,可以用Flashback工具通过闪回把数据恢复回来。

Flashback恢复数据的原理,是修改binlog的内容,拿回原库重放。而能够使用这个方案的前提是,需要确保binlog_format=row 和 binlog_row_image=FULL。

具体恢复数据时,对单个事务做如下处理:

  1. 对于insert语句,对应的binlog event类型是Write_rows event,把它改成Delete_rows event即可;
  2. 同理,对于delete语句,也是将Delete_rows event改为Write_rows event;
  3. 而如果是Update_rows的话,binlog里面记录了数据行修改前和修改后的值,对调这两行的位置即可。

如果误操作不是一个,而是多个,会怎么样呢?比如下面三个事务:

(A)delete ...
(B)insert ...
(C)update ...

现在要把数据库恢复回这三个事务操作之前的状态,用Flashback工具解析binlog后,写回主库的命令是:

(reverse C)update ...
(reverse B)delete ...
(reverse A)insert ...

也就是说,如果误删数据涉及到了多个事务的话,需要将事务的顺序调过来再执行。

不建议直接在主库上执行这些操作。

恢复数据比较安全的做法,是恢复出一个备份,或者找一个从库作为临时库,在这个临时库上执行这些操作,然后再将确认过的临时库的数据,恢复回主库。

因为,一个在执行线上逻辑的主库,数据状态的变更往往是有关联的。可能由于发现数据问题的时间晚了一点儿,就导致已经在之前误操作的基础上,业务代码逻辑又继续修改了其他数据。所以,如果这时候单独恢复这几行数据,而又未经确认的话,就可能会出现对数据的二次破坏。

更重要是要做到事前预防,有两个建议:

  1. 把sql_safe_updates参数设置为on。这样一来,如果我们忘记在delete或者update语句中写where条件,或者where条件里面没有包含索引字段的话,这条语句的执行就会报错。
  2. 代码上线前,必须经过SQL审计。

设置了sql_safe_updates=on,如果真的要把一个小表的数据全部删掉,应该怎么办呢?

如果确定这个删除操作没问题的话,可以在delete语句中加上where条件,比如where id>=0。

但是,delete全表是很慢的,需要生成回滚日志、写redo、写binlog。所以,从性能角度考虑,你应该优先考虑使用truncate table或者drop table命令。

使用delete命令删除的数据,还可以用Flashback来恢复。而使用truncate /drop table和drop database命令删除的数据,就没办法通过Flashback来恢复了。为什么呢?

这是因为,即使配置了binlog_format=row,执行这三个命令时,记录的binlog还是statement格式。binlog里面就只有一个truncate/drop 语句,这些信息是恢复不出数据的。

误删库/表

这种情况下,要想恢复数据,就需要使用全量备份,加增量日志的方式了。这个方案要求线上有定期的全量备份,并且实时备份binlog。

在这两个条件都具备的情况下,假如有人中午12点误删了一个库,恢复数据的流程如下:

  1. 取最近一次全量备份,假设这个库是一天一备,上次备份是当天0点;
  2. 用备份恢复出一个临时库;
  3. 从日志备份里面,取出凌晨0点之后的日志;
  4. 把这些日志,除了误删除数据的语句外,全部应用到临时库。

流程的示意图如下所示:

MySQL 读后总结 (三)

关于这个过程,有以下几点说明:

  1. 为了加速数据恢复,如果这个临时库上有多个数据库,你可以在使用mysqlbinlog命令时,加上一个–database参数,用来指定误删表所在的库。这样,就避免了在恢复数据时还要应用其他库日志的情况。
  2. 在应用日志的时候,需要跳过12点误操作的那个语句的binlog:
    • 如果原实例没有使用GTID模式,只能在应用到包含12点的binlog文件的时候,先用–stop-position参数执行到误操作之前的日志,然后再用–start-position从误操作之后的日志继续执行;
    • 如果实例使用了GTID模式,就方便多了。假设误操作命令的GTID是gtid1,那么只需要执行set gtid_next=gtid1;begin;commit; 先把这个GTID加到临时实例的GTID集合,之后按顺序执行binlog的时候,就会自动跳过误操作的语句。

不过,即使这样,使用mysqlbinlog方法恢复数据还是不够快,主要原因有两个:

  1. 如果是误删表,最好就是只恢复出这张表,也就是只重放这张表的操作,但是mysqlbinlog工具并不能指定只解析一个表的日志;
  2. 用mysqlbinlog解析出日志应用,应用日志的过程就只能是单线程。

一种加速的方法是,在用备份恢复出临时实例之后,将这个临时实例设置成线上备库的从库,这样:

  1. 在start slave之前,先通过执行
    change replication filter replicate_do_table = (tbl_name) 命令,就可以让临时库只同步误操作的表;
  2. 这样做也可以用上并行复制技术,来加速整个数据恢复过程。

这个过程的示意图如下:

MySQL 读后总结 (三)

图中binlog备份系统到线上备库有一条虚线,是指如果由于时间太久,备库上已经删除了临时实例需要的binlog的话,我们可以从binlog备份系统中找到需要的binlog,再放回备库中。

假设,我们发现当前临时实例需要的binlog是从master.000005开始的,但是在备库上执行show binlogs 显示的最小的binlog文件是master.000007,意味着少了两个binlog文件。这时,就需要去binlog备份系统中找到这两个文件。

把之前删掉的binlog放回备库的操作步骤,是这样的:

  1. 从备份系统下载master.000005和master.000006这两个文件,放到备库的日志目录下;
  2. 打开日志目录下的master.index文件,在文件开头加入两行,内容分别是 “./master.000005”和“./master.000006”;
  3. 重启备库,目的是要让备库重新识别这两个日志文件;
  4. 现在这个备库上就有了临时库需要的所有binlog了,建立主备关系,就可以正常同步了。

不论是把mysqlbinlog工具解析出的binlog文件应用到临时库,还是把临时库接到备库上,这两个方案的共同点是:误删库或者表后,恢复数据的思路主要就是通过备份,再加上应用binlog的方式。

也就是说,这两个方案都要求备份系统定期备份全量日志,而且需要确保binlog在被从本地删除之前已经做了备份。

但是,一个系统不可能备份无限的日志,还需要根据成本和磁盘空间资源,设定一个日志保留的天数。如果你的DBA团队告诉你,可以保证把某个实例恢复到半个月内的任意时间点,这就表示备份系统保留的日志时间就至少是半个月。

另外,建议不论使用上述哪种方式,都要把这个数据恢复功能做成自动化工具,并且经常拿出来演练。为什么这么说呢?

这里的原因,主要包括两个方面:

  1. 虽然“发生这种事,大家都不想的”,但是万一出现了误删事件,能够快速恢复数据,将损失降到最小,也应该不用跑路了。
  2. 而如果临时再手忙脚乱地手动操作,最后又误操作了,对业务造成了二次伤害,那就说不过去了。

延迟复制备库

通过利用并行复制来加速恢复数据的过程,但是这个方案仍然存在“恢复时间不可控”的问题。

如果一个库的备份特别大,或者误操作的时间距离上一个全量备份的时间较长,比如一周一备的实例,在备份之后的第6天发生误操作,那就需要恢复6天的日志,这个恢复时间可能是要按天来计算的。

那么,我们有什么方法可以缩短恢复数据需要的时间呢?

如果有非常核心的业务,不允许太长的恢复时间,可以考虑搭建延迟复制的备库。这个功能是MySQL 5.6版本引入的。

一般的主备复制结构存在的问题是,如果主库上有个表被误删了,这个命令很快也会被发给所有从库,进而导致所有从库的数据表也都一起被误删了。

延迟复制的备库是一种特殊的备库,通过 CHANGE MASTER TO MASTER_DELAY = N命令,可以指定这个备库持续保持跟主库有N秒的延迟。

比如你把N设置为3600,这就代表了如果主库上有数据被误删了,并且在1小时内发现了这个误操作命令,这个命令就还没有在这个延迟复制的备库执行。这时候到这个备库上执行stop slave,再通过之前介绍的方法,跳过误操作命令,就可以恢复出需要的数据。

这样的话,就随时可以得到一个,只需要最多再追1小时,就可以恢复出数据的临时实例,也就缩短了整个数据恢复需要的时间。

预防误删库/表的方法

第一条建议是,账号分离。这样做的目的是,避免写错命令。比如:

  • 只给业务开发同学DML权限,而不给truncate/drop权限。而如果业务开发人员有DDL需求的话,也可以通过开发管理系统得到支持。
  • 即使是DBA团队成员,日常也都规定只使用只读账号,必要的时候才使用有更新权限的账号。

第二条建议是,制定操作规范。这样做的目的,是避免写错要删除的表名。比如:

  • 在删除数据表之前,必须先对表做改名操作。然后,观察一段时间,确保对业务无影响以后再删除这张表。
  • 改表名的时候,要求给表名加固定的后缀(比如加_to_be_deleted),然后删除表的动作必须通过管理系统执行。并且,管理系删除表的时候,只能删除固定后缀的表。

rm删除数据

其实,对于一个有高可用机制的MySQL集群来说,最不怕的就是rm删除数据了。只要不是恶意地把整个集群删除,而只是删掉了其中某一个节点的数据的话,HA系统就会开始工作,选出一个新的主库,从而保证整个集群的正常工作。

这时,要做的就是在这个节点上把数据恢复回来,再接入整个集群。

当然了,现在不止是DBA有自动化系统,SA(系统管理员)也有自动化系统,所以也许一个批量下线机器的操作,会让你整个MySQL集群的所有节点都全军覆没。

应对这种情况,建议只能是说尽量把你的备份跨机房,或者最好是跨城市保存。

为什么还有kill不掉的语句?

在MySQL中有两个kill命令:一个是kill query +线程id,表示终止这个线程中正在执行的语句;一个是kill connection +线程id,这里connection可缺省,表示断开这个线程的连接,当然如果这个线程有语句正在执行,也是要先停止正在执行的语句的。

有这么一个现象:使用了kill命令,却没能断开这个连接。再执行show processlist命令,看到这条语句的Command列显示的是Killed。

显示为Killed是什么意思,不是应该直接在show processlist的结果里看不到这个线程了吗?

  • 其实大多数情况下,kill query/connection命令是有效的。比如,执行一个查询的过程中,发现执行时间太久,要放弃继续查询,这时我们就可以用kill query命令,终止这条查询语句。
  • 还有一种情况是,语句处于锁等待的时候,直接使用kill命令也是有效的。我们一起来看下这个例子:

MySQL 读后总结 (三)

可以看到,session C 执行kill query以后,session B几乎同时就提示了语句被中断,这就是预期的结果。

收到kill以后,线程做什么?

session B是直接终止掉线程,什么都不管就直接退出吗?显然,这是不行的。

当对一个表做增删改查操作时,会在表上加MDL读锁。所以,session B虽然处于blocked状态,但还是拿着一个MDL读锁的。如果线程被kill的时候,就直接终止,那之后这个MDL读锁就没机会被释放了。

这样看来,kill并不是马上停止的意思,而是告诉执行线程说,这条语句已经不需要继续执行了,可以开始“执行停止的逻辑了”。

其实,这跟Linux的kill命令类似,kill -N pid并不是让进程直接停止,而是给进程发一个信号,然后进程处理这个信号,进入终止逻辑。只是对于MySQL的kill命令来说,不需要传信号量参数,就只有“停止”这个命令。

实现上,当用户执行kill query thread_id_B时,MySQL里处理kill命令的线程做了两件事:

  1. 把session B的运行状态改成THD::KILL_QUERY(将变量killed赋值为THD::KILL_QUERY);
  2. 给session B的执行线程发一个信号。

为什么要发信号呢?

  • 因为像图1的例子里面,session B处于锁等待状态,如果只是把session B的线程状态设置THD::KILL_QUERY,线程B并不知道这个状态变化,还是会继续等待。发一个信号的目的,就是让session B退出等待,来处理这个THD::KILL_QUERY状态。

上面的分析中,隐含了这么三层意思:

  1. 一个语句执行过程中有多处“埋点”,在这些“埋点”的地方判断线程状态,如果发现线程状态是THD::KILL_QUERY,才开始进入语句终止逻辑;
  2. 如果处于等待状态,必须是一个可以被唤醒的等待,否则根本不会执行到“埋点”处;
  3. 语句从开始进入终止逻辑,到终止逻辑完全完成,是有一个过程的。

再看一个kill不掉的例子(innodb_thread_concurrency 不够用):

首先,执行set global innodb_thread_concurrency=2,将InnoDB的并发线程上限数设置为2;然后,执行下面的序列:

MySQL 读后总结 (三)

可以看到:

  1. sesssion C执行的时候被堵住了;
  2. 但是session D执行的kill query C命令却没什么效果,
  3. 直到session E执行了kill connection命令,才断开了session C的连接,提示“Lost connection to MySQL server during query”,
  4. 但是这时候,如果在session E中执行show processlist,你可以看到下面这个图。

MySQL 读后总结 (三)

这时候,id=12这个线程的Commnad列显示的是Killed。也就是说,客户端虽然断开了连接,但实际上服务端上这条语句还在执行过程中。

为什么在执行kill query命令时,这条语句不像第一个例子的update语句一样退出呢?

在实现上,等行锁时,使用的是pthread_cond_timedwait函数,这个等待状态可以被唤醒。但是,在这个例子里,12号线程的等待逻辑是这样的:每10毫秒判断一下是否可以进入InnoDB执行,如果不行,就调用nanosleep函数进入sleep状态。

也就是说,虽然12号线程的状态已经被设置成了KILL_QUERY,但是在这个等待进入InnoDB的循环过程中,并没有去判断线程的状态,因此根本不会进入终止逻辑阶段。

而当session E执行kill connection 命令时,是这么做的,

  1. 把12号线程状态设置为KILL_CONNECTION;
  2. 关掉12号线程的网络连接。因为有这个操作,所以你会看到,这时候session C收到了断开连接的提示。

为什么执行show processlist的时候,会看到Command列显示为killed呢?其实,这就是因为在执行show processlist的时候,有一个特别的逻辑:

如果一个线程的状态是KILL_CONNECTION,就把Command列显示成Killed。

以其实,即使是客户端退出了,这个线程的状态仍然是在等待中。那这个线程什么时候会退出呢?

  • 只有等到满足进入InnoDB的条件后,session C的查询语句继续执行,然后才有可能判断到线程状态已经变成了KILL_QUERY或者KILL_CONNECTION,再进入终止逻辑阶段。

这个例子是kill无效的第一类情况,即:线程没有执行到判断线程状态的逻辑。跟这种情况相同的,还有由于IO压力过大,读写IO的函数一直无法返回,导致不能及时判断线程的状态。

另一类情况是,终止逻辑耗时较长。这时候,从show processlist结果上看也是Command=Killed,需要等到终止逻辑完成,语句才算真正完成。这类情况,比较常见的场景有以下几种:

  1. 超大事务执行期间被kill。这时候,回滚操作需要对事务执行期间生成的所有新数据版本做回收操作,耗时很长。
  2. 大查询回滚。如果查询过程中生成了比较大的临时文件,加上此时文件系统压力大,删除临时文件可能需要等待IO资源,导致耗时较长。
  3. DDL命令执行到最后阶段,如果被kill,需要删除中间过程的临时文件,也可能受IO资源影响耗时较久。

在客户端通过Ctrl+C命令,是不可以直接终止线程的。

这里有一个误解,其实在客户端的操作只能操作到客户端的线程,客户端和服务端只能通过网络交互,是不可能直接操作服务端线程的。

而由于MySQL是停等协议,所以这个线程执行的语句还没有返回的时候,再往这个连接里面继续发命令也是没有用的。实际上,执行Ctrl+C的时候,是MySQL客户端另外启动一个连接,然后发送一个kill query 命令。

所以,要kill掉一个线程,还涉及到后端的很多操作。

另外两个关于客户端的误解

第一个误解是:如果库里面的表特别多,连接就会很慢。

有些线上的库,会包含很多表这时候,你就会发现,每次用客户端连接都会卡在下面这个界面上。

MySQL 读后总结 (三)

而如果db1这个库里表很少的话,连接起来就会很快,可以很快进入输入命令的状态。因此,有同学会认为是表的数目影响了连接性能。

每个客户端在和服务端建立连接的时候,需要做的事情就是TCP握手、用户校验、获取权限。但这几个操作,显然跟库里面表的个数无关。

但实际上,正如图中的文字提示所说的,当使用默认参数连接的时候,MySQL客户端会提供一个本地库名和表名补全的功能。为了实现这个功能,客户端在连接成功后,需要多做一些操作:

  1. 执行show databases;
  2. 切到db1库,执行show tables;
  3. 把这两个命令的结果用于构建一个本地的哈希表。

在这些操作中,最花时间的就是第三步在本地构建哈希表的操作。所以,当一个库中的表个数非常多的时候,这一步就会花比较长的时间。
也就是说,感知到的连接过程慢,其实并不是连接慢,也不是服务端慢,而是客户端慢。

图中的提示也说了,如果在连接命令中加上-A,就可以关掉这个自动补全的功能,然后客户端就可以快速返回了。

这里自动补全的效果就是,在输入库名或者表名的时候,输入前缀,可以使用Tab键自动补全表名或者显示提示。

实际使用中,如果自动补全功能用得并不多,建议每次使用的时候都默认加-A。

其实提示里面没有说,除了加-A以外,加–quick(或者简写为-q)参数,也可以跳过这个阶段。但是,这个–quick是一个更容易引起误会的参数,也是关于客户端常见的一个误解。

设置了这个参数可能会降低服务端的性能,并不是一个让服务端加速的参数。为什么这么说呢?

MySQL客户端发送请求后,接收服务端返回结果的方式有两种:

  1. 一种是本地缓存,也就是在本地开一片内存,先把结果存起来。如果你用API开发,对应的就是mysql_store_result 方法。
  2. 另一种是不缓存,读一个处理一个。如果你用API开发,对应的就是mysql_use_result方法。

MySQL客户端默认采用第一种方式,而如果加上–quick参数,就会使用第二种不缓存的方式。

采用不缓存的方式时,如果本地处理得慢,就会导致服务端发送结果被阻塞,因此会让服务端变慢。

,既然这样,为什么要给这个参数取名叫作quick呢?这是因为使用这个参数可以达到以下三点效果:

  • 第一点,跳过表名自动补全功能。
  • 第二点,mysql_store_result需要申请本地内存来缓存查询结果,如果查询结果太大,会耗费较多的本地内存,可能会影响客户端本地机器的性能;
  • 第三点,是不会把执行命令记录到本地的命令历史文件。

所以–quick参数的意思,是让客户端变得更快。

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

因为重启之后该做的回滚动作还是不能少的,所以从恢复速度的角度来说,应该让它自己结束。

当然,如果这个语句可能会占用别的锁,或者由于占用IO资源过多,从而影响到了别的语句执行的话,就需要先做主备切换,切到新主库提供服务。

切换之后别的线程都断开了连接,自动停止执行。接下来还是等它自己执行完成。

大数据查询,会不会把数据库内存打爆?

主机内存只有100G,现在要对一个200G的大表做全表扫描,会不会把数据库主机的内存用光了?

这个问题确实值得担心,被系统OOM(out of memory)可不是闹着玩的。但是,反过来想想,逻辑备份的时候,可不就是做整库扫描吗?如果这样就会把内存吃光,逻辑备份不是早就挂了?

所以说,对大表做全表扫描,看来应该是没问题的。但是,这个流程到底是怎么样的呢?

全表扫描对server层的影响

假设,现在要对一个200G的InnoDB表db1. t,执行一个全表扫描。当然,你要把扫描结果保存在客户端,会使用类似这样的命令:

mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file

InnoDB的数据是保存在主键索引上的,所以全表扫描实际上是直接扫描表t的主键索引。这条查询语句由于没有其他的判断条件,所以查到的每一行都可以直接放到结果集里面,然后返回给客户端。

那么,这个“结果集”存在哪里呢?

实际上,服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的:

  1. 获取一行,写到net_buffer中。这块内存的大小是由参数net_buffer_length定义的,默认是16k。
  2. 重复获取行,直到net_buffer写满,调用网络接口发出去。
  3. 如果发送成功,就清空net_buffer,然后继续取下一行,并写入net_buffer。
  4. 如果发送函数返回EAGAIN或WSAEWOULDBLOCK,就表示本地网络栈(socket send buffer)写满了,进入等待。直到网络栈重新可写,再继续发送。

对应的流程图如下:

MySQL 读后总结 (三)

从这个流程中,你可以看到:

  1. 一个查询在发送过程中,占用的MySQL内部的内存最大就是net_buffer_length这么大,并不会达到200G;
  2. socket send buffer 也不可能达到200G(默认定义/proc/sys/net/core/wmem_default),如果socket send buffer被写满,就会暂停读数据的流程。

也就是说,MySQL是“边读边发的”,这个概念很重要。这就意味着,如果客户端接收得慢,会导致MySQL服务端由于结果发不出去,这个事务的执行时间变长。

比如下面这个状态,故意让客户端不去读socket receive buffer中的内容,然后在服务端show processlist看到的结果。

MySQL 读后总结 (三)

如果看到State的值一直处于“Sending to client”,就表示服务器端的网络栈写满了。

如果客户端使用–quick参数,会使用mysql_use_result方法。这个方法是读一行处理一行。可以想象一下,假设有一个业务的逻辑比较复杂,每读一行数据以后要处理的逻辑如果很慢,就会导致客户端要过很久才会去取下一行数据,可能就会出现如图2所示的这种情况。

因此,对于正常的线上业务来说,如果一个查询的返回结果不会很多的话,都建议你使用mysql_store_result这个接口,直接把查询结果保存到本地内存。

当然前提是查询返回结果不多(例子:如果执行了一个大查询导致客户端占用内存近20G,这种情况下就需要改用mysql_use_result接口了)。

另一方面,如果在自己负责维护的MySQL里看到很多个线程都处于“Sending to client”这个状态,就意味着你要让业务开发同学优化查询结果,并评估这么多的返回结果是否合理。

而如果要快速减少处于这个状态的线程的话,将net_buffer_length参数设置为一个更大的值是一个可选方案。

与“Sending to client”长相很类似的一个状态是“Sending data”,这是一个经常被误会的问题。案例:在自己维护的实例上看到很多查询语句的状态是“Sending data”,但查看网络也没什么问题啊,为什么Sending data要这么久?

实际上,一个查询语句的状态变化是这样的(注意:这里略去了其他无关的状态):

  • MySQL查询语句进入执行阶段后,首先把状态设置成“Sending data”;
  • 然后,发送执行结果的列相关的信息(meta data) 给客户端;
  • 再继续执行语句的流程;
  • 执行完成后,把状态设置成空字符串。

也就是说,“Sending data”并不一定是指“正在发送数据”,而可能是处于执行器过程中的任意阶段。比如,可以构造一个锁等待的场景,就能看到Sending data状态。

MySQL 读后总结 (三)

MySQL 读后总结 (三)

可以看到,session B明显是在等锁,状态显示为Sending data。

也就是说,仅当一个线程处于“等待客户端接收结果”的状态,才会显示”Sending to client”;而如果显示成“Sending data”,它的意思只是“正在执行”。

现在可以看出,查询的结果是分段发给客户端的,因此扫描全表,查询返回大量的数据,并不会把内存打爆。

全表扫描对InnoDB的影响

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

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

由于有WAL机制,当事务提交的时候,磁盘上的数据页是旧的,那如果这时候马上有一个查询要来读这个数据页,是不是要马上把redo log应用到数据页呢?

  • 答案是不需要。因为这时候内存数据页的结果是最新的,直接读内存页就可以了。你看,这时候查询根本不需要读磁盘,直接从内存拿结果,速度是很快的。所以说,Buffer Pool还有加速查询的作用。

而Buffer Pool对查询的加速效果,依赖于一个重要的指标,即:内存命中率

可以在show engine innodb status结果中,查看一个系统当前的BP命中率。一般情况下,一个稳定服务的线上系统,要保证响应时间符合要求的话,内存命中率要在99%以上。

执行show engine innodb status ,可以看到“Buffer pool hit rate”字样,显示的就是当前的命中率。比如图5这个命中率,就是99.0%。

MySQL 读后总结 (三)

如果所有查询需要的数据页都能够直接从内存得到,那是最好的,对应的命中率就是100%。但,这在实际生产上是很难做到的。

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

在大约十年前,单机的数据量是上百个G,而物理内存是几个G;现在虽然很多服务器都能有128G甚至更高的内存,但是单机的数据量却达到了T级别。

所以,innodb_buffer_pool_size小于磁盘的数据量是很常见的。如果一个 Buffer Pool满了,而又要从磁盘读入一个数据页,那肯定是要淘汰一个旧数据页的。

InnoDB内存管理用的是最近最少使用 (Least Recently Used, LRU)算法,这个算法的核心就是淘汰最久未使用的数据。

下图是一个LRU算法的基本模型。

MySQL 读后总结 (三)
InnoDB管理Buffer Pool的LRU算法,是用链表来实现的。

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

这个算法看上去没什么问题,但是如果考虑到要做一个全表扫描,会不会有问题呢?

假设按照这个算法,我们要扫描一个200G的表,而这个表是一个历史数据表,平时没有业务访问它。

那么,按照这个算法扫描的话,就会把当前的Buffer Pool里的数据全部淘汰掉,存入扫描过程中访问到的数据页的内容。也就是说Buffer Pool里面主要放的是这个历史数据表的数据。

对于一个正在做业务服务的库,这可不妙。你会看到,Buffer Pool的内存命中率急剧下降,磁盘压力增加,SQL语句响应变慢。

所以,InnoDB不能直接使用这个LRU算法。实际上,InnoDB对LRU算法做了改进。

MySQL 读后总结 (三)

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

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

  1. 图7中状态1,要访问数据页P3,由于P3在young区域,因此和优化前的LRU算法一样,将其移到链表头部,变成状态2。
  2. 之后要访问一个新的不存在于当前链表的数据页,这时候依然是淘汰掉数据页Pm,但是新插入的数据页Px,是放在LRU_old处。
  3. 处于old区域的数据页,每次被访问的时候都要做下面这个判断:
    • 若这个数据页在LRU链表中存在的时间超过了1秒,就把它移动到链表头部;
    • 如果这个数据页在LRU链表中存在的时间短于1秒,位置保持不变。1秒这个时间,是由参数innodb_old_blocks_time控制的。其默认值是1000,单位毫秒。

这个策略,就是为了处理类似全表扫描的操作量身定制的。还是以刚刚的扫描200G的历史数据表为例,改进后的LRU算法的操作逻辑:

  1. 扫描过程中,需要新插入的数据页,都被放到old区域;
  2. 一个数据页里面有多条记录,这个数据页会被多次访问到,但由于是顺序扫描,这个数据页第一次被访问和最后一次被访问的时间间隔不会超过1秒,因此还是会被保留在old区域;
  3. 再继续扫描后续的数据,之前的这个数据页之后也不会再被访问到,于是始终没有机会移到链表头部(也就是young区域),很快就会被淘汰出去。

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

问题1:
如果你看到 State 的值一直处于“Sending to client”,就表示服务器端的网络栈写满了。如何处理?

  1. 使用 mysql_store_result 这个接口,直接把查询结果保存到本地内存。
  2. 优化查询结果,并评估这么多的返回结果是否合理。
  3. 而如果要快速减少处于这个状态的线程的话,将 net_buffer_length 参数设置为一个更大的值是一个可选方案。
    “Sending to client” 表示服务器端的网路栈写满了,那不是应该加大 socket send buffer 吗?跟加大 net_buffer_length 有什么关系?net_buffer_length 加再大,但 socket send buffer 很小的话,网络栈不还是处于写满状态?
  • net_buffer_length 的最大值是 1G,这个值比 socket send buffer大(一般是几M)
  • 比如假设一个业务,平均查询结果都是10M (当然这个业务有有问题,最终是要通过业务解决)
  • 但是如果把net_buffer_length 改成10M,就不会有“Sending to client” 的情况。虽然网络栈还是慢慢发的,但是那些没发完的都缓存在net_buffer中,对于执行器来说,都是“已经写出去了”。

问题2:
如果客户端由于压力过大,迟迟不能接收数据,会对服务端造成什么严重的影响?

这个问题的核心是,造成了“长事务”。

至于长事务的影响,结合锁、MVCC的知识点。

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

可不可以使用join?

在实际生产中,关于join语句使用的问题,一般会集中在以下两类:

  1. 我们DBA不让使用join,使用join有什么问题呢?
  2. 如果有两个大小不同的表做join,应该用哪个表做驱动表呢?

创建两个表t1和t2:

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 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结果:

MySQL 读后总结 (三)

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

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

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

对应的流程图如下所示:

MySQL 读后总结 (三)

在这个流程里:

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

能不能使用join?

假设不使用join,那我们就只能用单表查询。我们看看上面这条语句的需求,用单表查询怎么实现。

  1. 执行select * from t1,查出表t1的所有数据,这里有100行;
  2. 循环遍历这100行数据:
    • 从每一行R取出字段a的值$R.a;
    • 执行select * from t2 where a=$R.a
    • 把返回的结果和R构成结果集的一行。

可以看到,在这个查询过程,也是扫描了200行,但是总共执行了101条语句,比直接join多了100次交互。除此之外,客户端还要自己拼接SQL语句和结果。

显然,这么做还不如直接join好。

怎么选择驱动表?

在这个join语句执行过程中,驱动表是走全表扫描,而被驱动表是走树搜索。

假设被驱动表的行数是M。每次在被驱动表查一行数据,要先搜索索引a,再搜索主键索引。每次搜索一棵树近似复杂度是以2为底的M的对数,记为log2M,所以在被驱动表上查一行的时间复杂度是 2*log2M。

假设驱动表的行数是N,执行过程就要扫描驱动表N行,然后对于每一行,到被驱动表上匹配一次。

因此整个执行过程,近似复杂度是 N + N2log2M。

显然,N对扫描行数的影响更大,因此应该让小表来做驱动表。

如果你没觉得这个影响有那么“显然”, 可以这么理解:N扩大1000倍的话,扫描行数就会扩大1000倍;而M扩大1000倍,扫描行数扩大不到10倍。

到这里小结一下,通过上面的分析得到了两个结论:

  1. 使用join语句,性能比强行拆成多个单表执行SQL语句的性能要好;
  2. 如果使用join语句的话,需要让小表做驱动表。

但是需要注意的是,这个结论的前提是“可以使用被驱动表的索引”。

Simple Nested-Loop Join

把SQL语句为:

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

由于表t2的字段b上没有索引,因此再用图2的执行流程时,每次到t2去匹配的时候,就要做一次全表扫描。

可以先设想一下这个问题,继续使用图2的算法,是不是可以得到正确的结果呢?如果只看结果的话,这个算法是正确的,而且这个算法也有一个名字,叫做“Simple Nested-Loop Join”。

但是,这样算来,这个SQL请求就要扫描表t2多达100次,总共扫描100*1000=10万行。

这还只是两个小表,如果t1和t2都是10万行的表(当然了,这也还是属于小表的范围),就要扫描100亿行,这个算法看上去太“笨重”了。

当然,MySQL也没有使用这个Simple Nested-Loop Join算法,而是使用了另一个叫作“Block Nested-Loop Join”的算法,简称BNL。

Block Nested-Loop Join

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

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

这个过程的流程图如下:

MySQL 读后总结 (三)

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

MySQL 读后总结 (三)

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

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

在这种情况下,应该选择哪个表做驱动表。

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

  1. 两个表都做一次全表扫描,所以总的扫描行数是M+N;
  2. 内存中的判断次数是M*N。

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

这个例子里表t1才100行,要是表t1是一个大表,join_buffer放不下怎么办呢?

join_buffer的大小是由参数join_buffer_size设定的,默认值是256k。如果放不下表t1的所有数据话,策略很简单,就是分段放。我把join_buffer_size改成1200,再执行:

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

执行过程就变成了:

  1. 扫描表t1,顺序读取数据行放入join_buffer中,放完第88行join_buffer满了,继续第2步;
  2. 扫描表t2,把t2中的每一行取出来,跟join_buffer中的数据做对比,满足join条件的,作为结果集的一部分返回;
  3. 清空join_buffer;
  4. 继续扫描表t1,顺序读取最后的12行数据放入join_buffer中,继续执行第2步。

执行流程图如下:

MySQL 读后总结 (三)

图中的步骤4和5,表示清空join_buffer再复用。

这个流程才体现出了这个算法名字中“Block”的由来,表示“分块去join”。

可以看到,这时候由于表t1被分成了两次放入join_buffer中,导致表t2会被扫描两次。虽然分成两次放入join_buffer,但是判断等值条件的次数还是不变的,依然是(88+12)*1000=10万次。

再来看下,在这种情况下驱动表的选择问题。

假设,驱动表的数据行数是N,需要分K段才能完成算法流程,被驱动表的数据行数是M。

注意,这里的K不是常数,N越大K就会越大,因此把K表示为λ*N,显然λ的取值范围是(0,1)。

所以,在这个算法的执行过程中:

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

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

所以结论是,应该让小表当驱动表。

这时候会发现,在N+λ*N*M这个式子里,λ才是影响扫描行数的关键因素,这个值越小越好。

刚刚说了N越大,分段数K越大。那么,N固定的时候,什么参数会影响K的大小呢?(也就是λ的大小)答案是join_buffer_size。join_buffer_size越大,一次可以放入的行越多,分成的段数也就越少,对被驱动表的全表扫描次数就越少。

这就是为什么,可能会看到一些建议说:如果你的join语句很慢,就把join_buffer_size改大。

第一个问题:能不能使用join语句?

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

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

第二个问题是:如果要使用join,应该选择大表做驱动表还是选择小表做驱动表?

  1. 如果是Index Nested-Loop Join算法,应该选择小表做驱动表;
  2. 如果是Block Nested-Loop Join算法:
    • 在join_buffer_size足够大的时候,是一样的;
    • 在join_buffer_size不够大的时候(这种情况更常见),应该选择小表做驱动表。

所以,这个问题的结论就是,总是应该使用小表做驱动表。

前面的例子是没有加条件的。如果在语句的where条件加上 t2.id<=50这个限定条件,再来看下这两条语句:

select * from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=50;
select * from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=50;

注意,为了让两条语句的被驱动表都用不上索引,所以join字段都使用了没有索引的字段b。

但如果是用第二个语句的话,join_buffer只需要放入t2的前50行,显然是更好的。所以这里,“t2的前50行”是那个相对小的表,也就是“小表”。

我们再来看另外一组例子:

select t1.b,t2.* from  t1  straight_join t2 on (t1.b=t2.b) where t2.id<=100;
select t1.b,t2.* from  t2  straight_join t1 on (t1.b=t2.b) where t2.id<=100;

这个例子里,表t1 和 t2都是只有100行参加join。但是,这两条语句每次查询放入join_buffer中的数据是不一样的:

  • 表t1只查字段b,因此如果把t1放到join_buffer中,则join_buffer中只需要放入b的值;
  • 表t2需要查所有的字段,因此如果把表t2放到join_buffer中的话,就需要放入三个字段id、a和b。

这里,应该选择表t1作为驱动表。也就是说在这个例子里,“只需要一列参与join的表t1”是那个相对小的表。

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

怎么优化join语句

创建两个表t1和t2

create table t1(id int primary key, a int, b int, index(a));
create table t2 like t1;
drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t1 values(i, 1001-i, i);
    set i=i+1;
  end while;

  set i=1;
  while(i<=1000000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;

end;;
delimiter ;
call idata();

在表t1里,插入1000行数据,每一行的a=1001-id的值。也就是说,表t1中字段a是逆序的。同时,我在表t2中插入了100万行数据。

Multi-Range Read优化

Multi-Range Read优化(MRR)。这个优化的主要目的是尽量使用顺序读盘。

先熟悉一下回表:InnoDB在普通索引a上查到主键id的值后,再根据一个个主键id的值到主键索引上去查整行数据的过程。

回表过程是一行行地查数据,还是批量地查数据?

假设执行这个语句:

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

主键索引是一棵B+树,在这棵树上,每次只能根据一个主键id查到一行数据。因此,回表肯定是一行行搜索主键索引的,基本流程如图1所示。

MySQL 读后总结 (三)

如果随着a的值递增顺序查询的话,id的值就变成随机的,那么就会出现随机访问,性能相对较差。虽然“按行查”这个机制不能改,但是调整查询的顺序,还是能够加速的。

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

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

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

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

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

下面两幅图就是使用了MRR优化后的执行流程和explain结果。

MySQL 读后总结 (三)

MySQL 读后总结 (三)

从图3的explain结果中,可以看到Extra字段多了Using MRR,表示的是用上了MRR优化。而且,由于在read_rnd_buffer中按照id做了排序,所以最后得到的结果集也是按照主键id递增顺序的,也就是与图1结果集中行的顺序相反。

小结

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

Batched Key Access

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

NLJ算法的流程图:

MySQL 读后总结 (三)

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

那怎么才能一次性地多传些值给表t2呢?方法就是,从表t1里一次性地多拿些行出来,一起传给表t2。

既然如此,把表t1的数据取出来一部分,先放到一个临时内存。这个临时内存不是别人,就是join_buffer。

join_buffer 在BNL算法里的作用,是暂存驱动表的数据。但是在NLJ算法里并没有用。那么,刚好就可以复用join_buffer到BKA算法中。

如图5所示,是上面的NLJ算法优化后的BKA算法的流程。

MySQL 读后总结 (三)

图中,在join_buffer中放入的数据是P1P100,表示的是只会取查询需要的字段。当然,如果join buffer放不下P1P100的所有数据,就会把这100行数据分成多段执行上图的流程。

那么,这个BKA算法到底要怎么启用呢?

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

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

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

BNL算法的性能问题

问题:使用Block Nested-Loop Join(BNL)算法时,可能会对被驱动表做多次扫描。如果这个被驱动表是一个大的冷数据表,除了会导致IO压力大以外,还会对系统有什么影响呢?

由于InnoDB对Bufffer Pool的LRU算法做了优化,即:第一次从磁盘读入内存的数据页,会先放在old区域。如果1秒之后这个数据页不再被访问了,就不会被移动到LRU链表头部,这样对Buffer Pool的命中率影响就不大。

但是,如果一个使用BNL算法的join语句,多次扫描一个冷表,而且这个语句执行时间超过1秒,就会在再次扫描冷表的时候,把冷表的数据页移到LRU链表头部。

这种情况对应的,是冷表的数据量小于整个Buffer Pool的3/8,能够完全放入old区域的情况。

如果这个冷表很大,就会出现另外一种情况:业务正常访问的数据页,没有机会进入young区域。

由于优化机制的存在,一个正常访问的数据页,要进入young区域,需要隔1秒后再次被访问到。但是,由于我们的join语句在循环读磁盘和淘汰内存页,进入old区域的数据页,很可能在1秒之内就被淘汰了。这样,就会导致这个MySQL实例的Buffer Pool在这段时间内,young区域的数据页没有被合理地淘汰。

也就是说,这两种情况都会影响Buffer Pool的正常运作。

大表join操作虽然对IO有影响,但是在语句执行结束后,对IO的影响也就结束了。但是,对Buffer Pool的影响就是持续性的,需要依靠后续的查询请求慢慢恢复内存命中率。

为了减少这种影响,可以考虑增大join_buffer_size的值,减少对被驱动表的扫描次数。

也就是说,BNL算法对系统的影响主要包括三个方面:

  1. 可能会多次扫描被驱动表,占用磁盘IO资源;
  2. 判断join条件需要执行M*N次对比(M、N分别是两张表的行数),如果是大表就会占用非常多的CPU资源;
  3. 可能会导致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中插入了100万行数据,但是经过where条件过滤后,需要参与join的只有2000行数据。如果这条语句同时是一个低频的SQL语句,那么再为这个语句在表t2的字段b上创建一个索引就很浪费了。

但是,如果使用BNL算法来join的话,这个语句的执行流程是这样的:

  1. 把表t1的所有字段取出来,存入join_buffer中。这个表只有1000行,join_buffer_size默认值是256k,可以完全存入。
  2. 扫描表t2,取出每一行数据跟join_buffer中的数据进行对比,
    • 如果不满足t1.b=t2.b,则跳过;
    • 如果满足t1.b=t2.b, 再判断其他条件,也就是是否满足t2.b处于[1,2000]的条件,如果是,就作为结果集的一部分返回,否则跳过。

对于表t2的每一行,判断join是否满足的时候,都需要遍历join_buffer中的所有行。因此判断等值条件的次数是1000*100万=10亿次,这个判断的工作量很大。

MySQL 读后总结 (三)

MySQL 读后总结 (三)

可以看到,explain结果里Extra字段显示使用了BNL算法。在测试环境里,这条语句需要执行1分11秒。

在表t2的字段b上创建索引会浪费资源,但是不创建索引的话这个语句的等值条件要判断10亿次,想想也是浪费。那么,有没有两全其美的办法呢?

这时候,可以考虑使用临时表。使用临时表的大致思路是:

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

此时,对应的SQL语句的写法如下:

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

图8就是这个语句序列的执行效果。

MySQL 读后总结 (三)

可以看到,整个过程3个语句执行时间的总和还不到1秒,相比于前面的1分11秒,性能得到了大幅提升。接下来,我们一起看一下这个过程的消耗:

  1. 执行insert语句构造temp_t表并插入数据的过程中,对表t2做了全表扫描,这里扫描行数是100万。
  2. 之后的join语句,扫描表t1,这里的扫描行数是1000;join比较过程中,做了1000次带索引的查询。相比于优化前的join语句需要做10亿次条件判断来说,这个优化效果还是很明显的。

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

扩展-hash join

其实上面计算10亿次那个操作,看上去有点儿傻。如果join_buffer里面维护的不是一个无序数组,而是一个哈希表的话,那么就不是10亿次判断,而是100万次hash查找。这样的话,整条语句的执行速度就快多了吧?

这也正是MySQL的优化器和执行器一直被诟病的一个原因:不支持哈希join。并且,MySQL官方的roadmap,也是迟迟没有把这个优化排上议程。

实际上,这个优化思路,我们可以自己实现在业务端。实现流程大致如下:

  1. select * from t1;取得表t1的全部1000行数据,在业务端存入一个hash结构,比如C++里的set、PHP的dict这样的数据结构。
  2. select * from t2 where b>=1 and b<=2000; 获取表t2中满足条件的2000行数据。
  3. 把这2000行数据,一行一行地取到业务端,到hash结构的数据表中寻找匹配的数据。满足匹配的条件的这行数据,就作为结果集的一行。

问题:
下面这个三个表的join语句:

select * from t1 join t2 on(t1.a=t2.a) join t3 on (t2.b=t3.b) where t1.c>=X and t2.c>=Y and t3.c>=Z;

如果改写成straight_join,要怎么指定连接顺序,以及怎么给三个表创建索引。

第一原则是要尽量使用BKA算法。需要注意的是,使用BKA算法的时候,并不是“先计算两个表join的结果,再跟第三个表join”,而是直接嵌套查询的。

具体实现是:在t1.c>=X、t2.c>=Y、t3.c>=Z这三个条件里,选择一个经过过滤以后,数据最少的那个表,作为第一个驱动表。此时,可能会出现如下两种情况。

第一种情况,如果选出来是表t1或者t3,那剩下的部分就固定了。

  1. 如果驱动表是t1,则连接顺序是t1->t2->t3,要在被驱动表字段创建上索引,也就是t2.a 和 t3.b上创建索引;
  2. 如果驱动表是t3,则连接顺序是t3->t2->t1,需要在t2.b 和 t1.a上创建索引。

同时,还需要在第一个驱动表的字段c上创建索引。

第二种情况是,如果选出来的第一个驱动表是表t2的话,则需要评估另外两个条件的过滤效果。

总之,整体的思路就是,尽量让每一次参与join的驱动表的数据集,越小越好,因为这样我们的驱动表就会越小。

为什么临时表可以重名

在优化join查询的时候使用到了临时表。

create temporary table temp_t like t1;
alter table temp_t add index(b);
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

临时表和内存表的区别:

  • 内存表,指的是使用Memory引擎的表,建表语法是create table … engine=memory。这种表的数据都保存在内存里,系统重启的时候会被清空,但是表结构还在。除了这两个特性看上去比较“奇怪”外,从其他的特征上看,它就是一个正常的表。
  • 而临时表,可以使用各种引擎类型 。如果是使用InnoDB引擎或者MyISAM引擎的临时表,写数据的时候是写到磁盘上的。当然,临时表也可以使用Memory引擎。

临时表的特性

看下下面这个操作序列:

MySQL 读后总结 (三)

可以看到,临时表在使用上有以下几个特点:

  1. 建表语法是create temporary table …。
  2. 一个临时表只能被创建它的session访问,对其他线程不可见。所以,图中session A创建的临时表t,对于session B就是不可见的。
  3. 临时表可以与普通表同名。
  4. session A内有同名的临时表和普通表的时候,show create语句,以及增删改查语句访问的是临时表。
  5. show tables命令不显示临时表。

由于临时表只能被创建它的session访问,所以在这个session结束的时候,会自动删除临时表。也正是由于这个特性,临时表就特别适合我们文章开头的join优化这种场景。为什么呢?

原因主要包括以下两个方面:

  1. 不同session的临时表是可以重名的,如果有多个session同时执行join优化,不需要担心表名重复导致建表失败的问题。
  2. 不需要担心数据删除问题。如果使用普通表,在流程执行过程中客户端发生了异常断开,或者数据库发生异常重启,还需要专门来清理中间过程中生成的数据表。而临时表由于会自动回收,所以不需要这个额外的操作。

临时表的应用

由于不用担心线程之间的重名冲突,临时表经常会被用在复杂查询的优化过程中。其中,分库分表系统的跨库查询就是一个典型的使用场景。

一般分库分表的场景,就是要把一个逻辑上的大表分散到不同的数据库实例上。比如。将一个大表ht,按照字段f,拆分成1024个分表,然后分布到32个数据库实例上。如下图所示:

MySQL 读后总结 (三)

一般情况下,这种分库分表系统都有一个中间层proxy。不过,也有一些方案会让客户端直接连接数据库,也就是没有proxy这一层。

在这个架构中,分区key的选择是以“减少跨库和跨表查询”为依据的。如果大部分的语句都会包含f的等值条件,那么就要用f做分区键。这样,在proxy这一层解析完SQL语句以后,就能确定将这条语句路由到哪个分表做查询。

比如下面这条语句:

select v from ht where f=N;

这时,就可以通过分表规则(比如,N%1024)来确认需要的数据被放在了哪个分表上。这种语句只需要访问一个分表,是分库分表方案最欢迎的语句形式了。

但是,如果这个表上还有另外一个索引k,并且查询语句是这样的:

select v from ht where k >= M order by t_modified desc limit 100;

这时候,由于查询条件里面没有用到分区字段f,只能到所有的分区中去查找满足条件的所有行,然后统一做order by 的操作。这种情况下,有两种比较常用的思路。

第一种思路是,在proxy层的进程代码中实现排序。

这种方式的优势是处理速度快,拿到分库的数据以后,直接在内存中参与计算。不过,这个方案的缺点也比较明显:

  1. 需要的开发工作量比较大。我们举例的这条语句还算是比较简单的,如果涉及到复杂的操作,比如group by,甚至join这样的操作,对中间层的开发能力要求比较高;
  2. 对proxy端的压力比较大,尤其是很容易出现内存不够用和CPU瓶颈的问题。

另一种思路就是,把各个分库拿到的数据,汇总到一个MySQL实例的一个表中,然后在这个汇总实例上做逻辑操作。

比如上面这条语句,执行流程可以类似这样:

  • 在汇总库上创建一个临时表temp_ht,表里包含三个字段v、k、t_modified;
  • 在各个分库上执行
    select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;
  • 把分库执行的结果插入到temp_ht表中;
  • 执行
    select v from temp_ht order by t_modified desc limit 100;

这个过程对应的流程图如下所示:

MySQL 读后总结 (三)

在实践中,往往会发现每个分库的计算量都不饱和,所以会直接把临时表temp_ht放到32个分库中的某一个上。这时的查询逻辑与图3类似

为什么临时表可以重名?

不同线程可以创建同名的临时表,这是怎么做到的呢?

执行:

create temporary table temp_t(id int primary key)engine=innodb;

这个语句的时候,MySQL要给这个InnoDB表创建一个frm文件保存表结构定义,还要有地方保存表数据。

这个frm文件放在临时文件目录下,文件名的后缀是.frm,前缀是“#sql{进程id}{线程id}\序列号”。你可以使用select @@tmpdir命令,来显示实例的临时文件目录。

而关于表中数据的存放方式,在不同的MySQL版本中有着不同的处理方式:

  • 在5.6以及之前的版本里,MySQL会在临时文件目录下创建一个相同前缀、以.ibd为后缀的文件,用来存放数据文件;
  • 而从 5.7版本开始,MySQL引入了一个临时文件表空间,专门用来存放临时文件的数据。因此,我们就不需要再创建ibd文件了。

从文件名的前缀规则,可以看到,其实创建一个叫作t1的InnoDB临时表,MySQL在存储上认为我们创建的表名跟普通表t1是不同的,因此同一个库下面已经有普通表t1的情况下,还是可以再创建一个临时表t1的。

举个例子:

MySQL 读后总结 (三)

这个进程的进程号是1234,session A的线程id是4,session B的线程id是5。所以你看到了,session A和session B创建的临时表,在磁盘上的文件不会重名。

MySQL维护数据表,除了物理上要有文件外,内存里面也有一套机制区别不同的表,每个表都对应一个table_def_key。

  • 一个普通表的table_def_key的值是由“库名+表名”得到的,所以如果你要在同一个库下创建两个同名的普通表,创建第二个表的过程中就会发现table_def_key已经存在了。
  • 而对于临时表,table_def_key在“库名+表名”基础上,又加入了“server_id+thread_id”。

也就是说,session A和sessionB创建的两个临时表t1,它们的table_def_key不同,磁盘文件名也不同,因此可以并存。

在实现上,每个线程都维护了自己的临时表链表。这样每次session内操作表的时候,先遍历链表,检查是否有这个名字的临时表,如果有就优先操作临时表,如果没有再操作普通表;在session结束的时候,对链表里的每个临时表,执行 “DROP TEMPORARY TABLE +表名”操作。

这时候你会发现,binlog中也记录了DROP TEMPORARY TABLE这条命令。你一定会觉得奇怪,临时表只在线程内自己可以访问,为什么需要写到binlog里面?

这就需要说到主备复制了。

临时表和主备复制

既然写binlog,就意味着备库需要。

可以设想一下,在主库上执行下面这个语句序列:

create table t_normal(id int primary key, c int)engine=innodb;/*Q1*/
create temporary table temp_t like t_normal;/*Q2*/
insert into temp_t values(1,1);/*Q3*/
insert into t_normal select * from temp_t;/*Q4*/

如果关于临时表的操作都不记录,那么在备库就只有create table t_normal表和insert into t_normal select * from temp_t这两个语句的binlog日志,备库在执行到insert into t_normal的时候,就会报错“表temp_t不存在”。

如果把binlog设置为row格式就好了吧?因为binlog是row格式时,在记录insert into t_normal的binlog时,记录的是这个操作的数据,即:write_row event里面记录的逻辑是“插入一行数据(1,1)”。

确实是这样。如果当前的binlog_format=row,那么跟临时表有关的语句,就不会记录到binlog里。也就是说,只在binlog_format=statment/mixed 的时候,binlog中才会记录临时表的操作。

这种情况下,创建临时表的语句会传到备库执行,因此备库的同步线程就会创建这个临时表。主库在线程退出的时候,会自动删除临时表,但是备库同步线程是持续在运行的。所以,这时候就需要在主库上再写一个DROP TEMPORARY TABLE传给备库执行。

问题:MySQL在记录binlog的时候,不论是create table还是alter table语句,都是原样记录,甚至于连空格都不变。但是如果执行drop table t_normal,系统记录binlog就会写成:

DROP TABLE `t_normal` /* generated by server */

也就是改成了标准的格式。为什么要这么做呢 ?

  • drop table命令是可以一次删除多个表的。比如,在上面的例子中,设置binlog_format=row,如果主库上执行 “drop table t_normal, temp_t”这个命令,那么binlog中就只能记录:
    DROP TABLE `t_normal` /* generated by server */

因为备库上并没有表temp_t,将这个命令重写后再传到备库执行,才不会导致备库同步线程停止。

所以,drop table命令记录binlog的时候,就必须对语句做改写。“/* generated by server */”说明了这是一个被服务端改写过的命令。

说到主备复制,还有另外一个问题需要解决:主库上不同的线程创建同名的临时表是没关系的,但是传到备库执行是怎么处理的呢?

举个例子,下面的序列中实例S是M的备库。

MySQL 读后总结 (三)

主库M上的两个session创建了同名的临时表t1,这两个create temporary table t1 语句都会被传到备库S上。

但是,备库的应用日志线程是共用的,也就是说要在应用线程里面先后执行这个create 语句两次。(即使开了多线程复制,也可能被分配到从库的同一个worker中执行)。那么,这会不会导致同步线程报错 ?

显然是不会的,否则临时表就是一个bug了。也就是说,备库线程在执行的时候,要把这两个t1表当做两个不同的临时表来处理。这,又是怎么实现的呢?

MySQL在记录binlog的时候,会把主库执行这个语句的线程id写到binlog中。这样,在备库的应用线程就能够知道执行每个语句的主库线程id,并利用这个线程id来构造临时表的table_def_key:

  1. session A的临时表t1,在备库的table_def_key就是:库名+t1+“M的serverid”+“session A的thread_id”;
  2. session B的临时表t1,在备库的table_def_key就是 :库名+t1+“M的serverid”+“session B的thread_id”。

由于table_def_key不同,所以这两个表在备库的应用线程里面是不会冲突的。

问题:
为什么不能用rename修改临时表的改名?

  • 在实现上,执行rename table语句的时候,要求按照“库名/表名.frm”的规则去磁盘找文件,但是临时表在磁盘上的frm文件是放在tmpdir目录下的,并且文件名的规则是“#sql{进程id}_{线程id}_序列号.frm”,因此会报“找不到文件名”的错误。
本作品采用《CC 协议》,转载必须注明作者和本文链接
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!