MySQL 读后总结 (二)

正确显示随机消息

一个英语学习App首页有一个随机显示单词的功能,也就是根据每个用户的级别有一个单词表,然后这个用户每次访问首页的时候,都会随机滚动显示三个单词。他们发现随着单词表变大,选单词这个逻辑变得越来越慢,甚至影响到了首页的打开速度。

简化:去掉每个级别的用户都有一个对应的单词表这个逻辑,直接就是从一个单词表中随机选出三个单词。这个表的建表语句和初始数据的命令如下:

mysql> CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=0;
  while i<10000 do
    insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
    set i=i+1;
  end while;
end;;
delimiter ;

call idata();

在这个表里面插入了10000行记录,随机选择3个单词。

内存临时表

使用 order by rand() 随机排序取前3个

mysql> select word from words order by rand() limit 3;

用explain命令查看执行情况。

MySQL  读后总结(二)

Extra字段显示Using temporary,表示的是需要使用临时表;Using filesort,表示的是需要执行排序操作。

Extra:需要临时表,并且需要在临时表上排序。

MySQL  读后总结(二)

MySQL  读后总结(二)

对于InnoDB表来说,执行全字段排序会减少磁盘访问,因此会被优先选择。

对于内存表,回表过程只是简单地根据数据行的位置,直接访问内存得到数据,根本不会导致多访问磁盘。优化器没有了这一层顾虑,那么它会优先考虑的,就是用于排序的行越少越好了,所以,MySQL这时就会选择rowid排序。

这条语句的执行流程是这样的:

  1. 创建一个临时表。这个临时表使用的是memory引擎,表里有两个字段,第一个字段是double类型,为了后面描述方便,记为字段R,第二个字段是varchar(64)类型,记为字段W。并且,这个表没有建索引。
  2. 从words表中,按主键顺序取出所有的word值。对于每一个word值,调用rand()函数生成一个大于0小于1的随机小数,并把这个随机小数和word分别存入临时表的R和W字段中,到此,扫描行数是10000。
  3. 现在临时表有10000行数据了,接下来你要在这个没有索引的内存临时表上,按照字段R排序。
  4. 初始化 sort_buffer。sort_buffer中有两个字段,一个是double类型,另一个是整型。
  5. 从内存临时表中一行一行地取出R值和位置信息(我后面会和你解释这里为什么是“位置信息”),分别存入sort_buffer中的两个字段里。这个过程要对内存临时表做全表扫描,此时扫描行数增加10000,变成了20000。
  6. 在sort_buffer中根据R的值进行排序。注意,这个过程没有涉及到表操作,所以不会增加扫描行数。
  7. 排序完成后,取出前三个结果的位置信息,依次到内存临时表中取出word值,返回给客户端。这个过程中,访问了表的三行数据,总扫描行数变成了20003。

通过慢查询日志(slow log)来验证一下我们分析得到的扫描行数是否正确

# Query_time: 0.900376  Lock_time: 0.000347 Rows_sent: 3 Rows_examined: 20003
SET timestamp=1541402277;
select word from words order by rand() limit 3;

Rows_examined:20003就表示这个语句执行过程中扫描了20003行,也就验证了我们分析得出的结论。

完整的排序执行流程图:

MySQL  读后总结(二)

图中的pos就是位置信息。

MySQL的表是用什么方法来定位“一行数据”的。

如果把一个InnoDB表的主键删掉,是不是就没有主键,就没办法回表了?

  • 如果你创建的表没有主键,或者把一个表的主键删掉了,那么InnoDB会自己生成一个长度为6字节的rowid来作为主键。

这也就是排序模式里面,rowid名字的来历。实际上它表示的是:每个引擎用来唯一标识数据行的信息。

  • 对于有主键的InnoDB表来说,这个rowid就是主键ID;
  • 对于没有主键的InnoDB表来说,这个rowid就是由系统生成的;
  • MEMORY引擎不是索引组织表。在这个例子里面,你可以认为它就是一个数组。因此,这个rowid其实就是数组的下标。

order by rand()使用了内存临时表,内存临时表排序的时候使用了rowid排序方法。

磁盘临时表

不是所有的临时表都是内存表,tmp_table_size这个配置限制了内存临时表的大小,默认值是16M。如果临时表大小超过了tmp_table_size,那么内存临时表就会转成磁盘临时表。

磁盘临时表使用的引擎默认是InnoDB,是由参数internal_tmp_disk_storage_engine控制的。

当使用磁盘临时表的时候,对应的就是一个没有显式索引的InnoDB表的排序过程。

复现这个过程,把tmp_table_size设置成1024,把sort_buffer_size设置成 32768, 把 max_length_for_sort_data 设置成16。

set tmp_table_size=1024;
set sort_buffer_size=32768;
set max_length_for_sort_data=16;
/* 打开 optimizer_trace,只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* 执行语句 */
select word from words order by rand() limit 3;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

MySQL  读后总结(二)

OPTIMIZER_TRACE的结果:

将max_length_for_sort_data设置成16,小于word字段的长度定义,所以 sort_mode 里面显示的是rowid排序,参与排序的是随机值R字段和rowid字段组成的行。

R字段存放的随机值就8个字节,rowid是6个字节,数据总行数是10000,这样算出来就有140000字节,超过了sort_buffer_size 定义的 32768字节了。但是,number_of_tmp_files的值居然是0,难道不需要用临时文件吗?

这个SQL语句的排序确实没有用到临时文件,采用是MySQL 5.6版本引入的一个新的排序算法,即:优先队列排序算法。接下来,我们就看看为什么没有使用临时文件的算法,也就是归并排序算法,而是采用了优先队列排序算法

其实,现在的SQL语句,只需要取R值最小的3个rowid。但是,如果使用归并排序算法的话,虽然最终也能得到前3个值,但是这个算法结束后,已经将10000行数据都排好序了。

也就是说,后面的9997行也是有序的了。但,我们的查询并不需要这些数据是有序的。所以,想一下就明白了,这浪费了非常多的计算量。

而优先队列算法,就可以精确地只得到三个最小值,执行流程如下:

  • 对于这10000个准备排序的(R,rowid),先取前三行,构造成一个堆;
    • (对数据结构印象模糊的同学,可以先设想成这是一个由三个元素组成的数组)
  • 取下一个行(R’,rowid’),跟当前堆里面最大的R比较,如果R’小于R,把这个(R,rowid)从堆中去掉,换成(R’,rowid’);
  • 重复第2步,直到第10000个(R’,rowid’)完成比较。

优先队列排序过程的示意图:

MySQL  读后总结(二)

图6是模拟6个(R,rowid)行,通过优先队列排序找到最小的三个R值的行的过程。整个排序过程中,为了最快地拿到当前堆的最大值,总是保持最大值在堆顶,因此这是一个最大堆。

图5的OPTIMIZER_TRACE结果中,filesort_priority_queue_optimization这个部分的chosen=true,就表示使用了优先队列排序算法,这个过程不需要临时文件,因此对应的number_of_tmp_files是0。

这个流程结束后,在构造的堆里面,就是这个10000行里面R值最小的三行。然后,依次把它们的rowid取出来,去临时表里面拿到word字段。

select city,name,age from t where city='杭州' order by name limit 1000  ;

这条SQL语句是limit 1000,如果使用优先队列算法的话,需要维护的堆的大小就是1000行的(name,rowid),超过了我设置的sort_buffer_size大小,所以只能使用归并排序算法。

不论是使用哪种类型的临时表,order by rand()这种写法都会让计算过程非常复杂,需要大量的扫描行数,因此排序过程的资源消耗也会很大。

随机排序方法

如果只随机选择1个word值,可以怎么做呢?思路:

  1. 取得这个表的主键id的最大值M和最小值N;
  2. 用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;
  3. 取不小于X的第一个ID的行。

把这个算法,暂时称作随机算法1,执行语句的序列:

mysql> select max(id),min(id) into @M,@N from t ;
set @X= floor((@M-@N+1)*rand() + @N);
select * from t where id >= @X limit 1;

这个方法效率很高,因为取max(id)和min(id)都是不需要扫描索引的,而第三步的select也可以用索引快速定位,可以认为就只扫描了3行。但实际上,这个算法本身并不严格满足题目的随机要求,因为ID中间可能有空洞,因此选择不同行的概率不一样,不是真正的随机。

比如有4个id,分别是1、2、4、5,如果按照上面的方法,那么取到 id=4的这一行的概率是取得其他行概率的两倍。

如果这四行的id分别是1、2、40000、40001呢?这个算法基本就能当bug来看待了。

为了得到严格随机的结果,你可以用下面这个流程:

  1. 取得整个表的行数,并记为C。
  2. 取得 Y = floor(C * rand())。 floor函数在这里的作用,就是取整数部分。
  3. 再用limit Y,1 取得一行。

把这个算法,称为随机算法2。下面这段代码,就是上面流程的执行语句的序列。

mysql> select count(*) into @C from t;
set @Y = floor(@C * rand());
set @sql = concat("select * from t limit ", @Y, ",1");
prepare stmt from @sql;
execute stmt;
DEALLOCATE prepare stmt;

由于limit 后面的参数不能直接跟变量,所以在上面的代码中使用了prepare+execute的方法。也可以把拼接SQL语句的方法写在应用程序中,会更简单些。

这个随机算法2,解决了算法1里面明显的概率不均匀问题。

MySQL处理limit Y,1 的做法就是按顺序一个一个地读出来,丢掉前Y个,然后把下一个记录作为返回结果,因此这一步需要扫描Y+1行。再加上,第一步扫描的C行,总共需要扫描C+Y+1行,执行代价比随机算法1的代价要高。

当然,随机算法2跟直接order by rand()比起来,执行代价还是小很多的。

如果按照随机算法2的思路,要随机取3个word值呢?

  1. 取得整个表的行数,记为C;
  2. 根据相同的随机方法得到Y1、Y2、Y3;
  3. 再执行三个limit Y, 1语句得到三行数据。

把这个算法,称作随机算法3。下面这段代码,就是上面流程的执行语句的序列。

mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y11//在应用代码里面取Y1、Y2、Y3值,拼出SQL后执行
select * from t limit @Y21select * from t limit @Y31

通过三次limit Y,1 来得到需要的数据,进一步的优化方法。

取Y1、Y2和Y3里面最大的一个数,记为M,最小的一个数记为N,然后执行下面这条SQL语句:

mysql> select * from t limit N, M-N+1;

再加上取整个表总行数的C行,这个方案的扫描行数总共只需要C+M+1行。
当然也可以先取回id值,在应用中确定了三个id值以后,再执行三次where id=X的语句也是可以的。

语句逻辑相同的SQL,性能却差异巨大

条件字段函数操作

现在维护了一个交易系统,其中交易记录表tradelog包含交易流水号(tradeid)、交易员id(operator)、交易时间(t_modified)等字段。忽略其他字段。这个表的建表语句如下:

mysql> CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

假设,现在已经记录了从2016年初到2018年底的所有数据,运营部门有一个需求是,要统计发生在所有年份中7月份的交易记录总数。

mysql> select count(*) from tradelog where month(t_modified)=7;

由于t_modified字段上有索引,于是在生产库中执行了这条语句,但却发现执行了特别久,才返回了结果。

下面是这个t_modified索引的示意图。方框上面的数字就是month()函数对应的值。

MySQL  读后总结 (二)

如果你的SQL语句条件用的是where t_modified=’2018-7-1’的话,引擎就会按照上面绿色箭头的路线,快速定位到 t_modified=’2018-7-1’需要的结果。

实际上,B+树提供的这个快速定位能力,来源于同一层兄弟节点的有序性。

但是,如果计算month()函数的话,你会看到传入7的时候,在树的第一层就不知道该怎么办了。

需要注意的是,优化器并不是要放弃使用这个索引。

在这个例子里,放弃了树搜索功能,优化器可以选择遍历主键索引,也可以选择遍历索引t_modified,优化器对比索引大小后发现,索引t_modified更小,遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引t_modified。

使用explain命令,查看这条SQL语句的执行结果:

MySQL  读后总结 (二)

key=”t_modified”表示的是,使用了t_modified这个索引;我在测试表数据中插入了10万行数据,rows=100335,说明这条语句扫描了整个索引的所有值;Extra字段的Using index,表示的是使用了覆盖索引。

由于在t_modified字段加了month()函数操作,导致了全索引扫描。为了能够用上索引的快速定位能力,我们就要把SQL语句改成基于字段本身的范围查询。按照下面这个写法,优化器就能按照我们预期的,用上t_modified索引的快速定位能力了。

mysql> select count(*) from tradelog where
    -> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or 
    -> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

当然,如果系统上线时间更早,或者后面又插入了之后年份的数据的话,就需要再把其他年份补齐。

由于加了month()函数操作,MySQL无法再使用索引快速定位功能,而只能使用全索引扫描。

不过优化器在个问题上确实有“偷懒”行为,即使是对于不改变有序性的函数,也不会考虑使用索引。比如,对于select * from tradelog where id + 1 = 10000这个SQL语句,这个加1操作并不会改变有序性,但是MySQL优化器还是不能用id索引快速定位到9999这一行。所以,需要在写SQL语句的时候,手动改写成 where id = 10000 -1才可以。

隐式类型转换

mysql> select * from tradelog where tradeid=110717;

交易编号tradeid这个字段上,本来就有索引,但是explain的结果却显示,这条语句需要走全表扫描。你可能也发现了,tradeid的字段类型是varchar(32),而输入的参数却是整型,所以需要做类型转换。

这里就有两个问题:

  1. 数据类型转换的规则是什么?
  2. 为什么有数据类型转换,就需要走全索引扫描?

数据库里面类型这么多,这种数据类型转换规则更多,应该怎么办呢?

简单的方法:看 select “10” > 9的结果:

  1. 如果规则是“将字符串转成数字”,那么就是做数字比较,结果应该是1;
  2. 如果规则是“将数字转成字符串”,那么就是做字符串比较,结果应该是0。

验证结果:
MySQL  读后总结 (二)

select “10” > 9返回的是1,所以你就能确认MySQL里的转换规则了:在MySQL中,字符串和数字做比较的话,是将字符串转换成数字。

全表扫描的语句:

mysql> select * from tradelog where tradeid=110717;

对于优化器来说,这个语句相当于:

mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;

也就是说,这条语句触发:对索引字段做函数操作,优化器会放弃走树搜索功能。

隐式字符编码转换

假设系统里还有另外一个表trade_detail,用于记录交易的操作细节。为了便于量化分析和复现,可以往交易日志表tradelog和交易详情表trade_detail这两个表里插入一些数据。

mysql> CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `trade_step` int(11) DEFAULT NULL, /*操作步骤*/
  `step_info` varchar(32) DEFAULT NULL, /*步骤信息*/
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into tradelog values(1, 'aaaaaaaa', 1000, now());
insert into tradelog values(2, 'aaaaaaab', 1000, now());
insert into tradelog values(3, 'aaaaaaac', 1000, now());

insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');
insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');

要查询id=2的交易的所有操作步骤信息:

mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /*语句Q1*/

MySQL  读后总结 (二)

结果:

  1. 第一行显示优化器会先在交易记录表tradelog上查到id=2的行,这个步骤用上了主键索引,rows=1表示只扫描一行;
  2. 第二行key=NULL,表示没有用上交易详情表trade_detail上的tradeid索引,进行了全表扫描。

在这个执行计划里,是从tradelog表中取tradeid字段,再去trade_detail表里查询匹配字段。因此,把tradelog称为驱动表,把trade_detail称为被驱动表,把tradeid称为关联字段。

explain结果表示的执行流程:

MySQL  读后总结 (二)

图中:

  • 第1步,是根据id在tradelog表里找到L2这一行;
  • 第2步,是从L2中取出tradeid字段的值;
  • 第3步,是根据tradeid值到trade_detail表中查找条件匹配的行。explain的结果里面第二行的key=NULL表示的就是,这个过程是通过遍历主键索引的方式,一个一个地判断tradeid的值是否匹配。

进行到这里,发现第3步不符合预期。因为表trade_detail里tradeid字段上是有索引的,本来是希望通过使用tradeid索引能够快速定位到等值的行。但,这里并没有,因为这两个表的字符集不同,一个是utf8,一个是utf8mb4,所以做表连接查询的时候用不上关联字段的索引。

为什么字符集不同就用不上索引呢?

问题出在执行步骤的第3步,如果单独把这一步改成SQL语句的话,那就是:

mysql> select * from trade_detail where tradeid=$L2.tradeid.value; 

其中,$L2.tradeid.value的字符集是utf8mb4。

字符集utf8mb4是utf8的超集,所以当这两个类型的字符串在做比较的时候,MySQL内部的操作是,先把utf8字符串转成utf8mb4字符集,再做比较。

utf8mb4是utf8的超集。类似地,在程序设计语言里面,做自动类型转换的时候,为了避免数据在转换过程中由于截断导致数据错误,也都是“按数据长度增加的方向”进行转换的。

因此, 在执行上面这个语句的时候,需要将被驱动数据表里的字段一个个地转换成utf8mb4,再跟L2做比较。

实际上这个语句等同于下面这个写法:

select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value; 

CONVERT()函数,在这里的意思是把输入的字符串转成utf8mb4字符集。

再次触发了所说的原则:对索引字段做函数操作,优化器会放弃走树搜索功能。

字符集不同只是条件之一,连接过程中要求在被驱动表的索引字段上加函数操作,是直接导致对被驱动表做全表扫描的原因。

对比验证:“查找trade_detail表里id=4的操作,对应的操作者是谁”,再来看下这个语句和它的执行计划。

mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

MySQL  读后总结 (二)

这个语句里trade_detail 表成了驱动表,但是explain结果的第二行显示,这次的查询操作用上了被驱动表tradelog里的索引(tradeid),扫描行数是1。

这也是两个tradeid字段的join操作,为什么这次能用上被驱动表的tradeid索引呢?

假设驱动表trade_detail里id=4的行记为R4,那么在连接的时候(图5的第3步),被驱动表tradelog上执行的就是类似这样的SQL 语句:

select operator from tradelog  where traideid =$R4.tradeid.value; 

这时候$R4.tradeid.value的字符集是utf8, 按照字符集转换规则,要转成utf8mb4,所以这个过程就被改写成:

select operator from tradelog  where traideid =CONVERT($R4.tradeid.value USING utf8mb4); 

这里的CONVERT函数是加在输入参数上的,这样就可以用上被驱动表的traideid索引。

优化语句:

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

这个语句的执行过程,有两种做法:

  • 比较常见的优化方法是,把trade_detail表上的tradeid字段的字符集也改成utf8mb4,这样就没有字符集转换的问题了。
    alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;
  • 如果能够修改字段的字符集的话,是最好不过了。但如果数据量比较大, 或者业务上暂时不能做这个DDL的话,那就只能采用修改SQL语句的方法了。
    mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 

MySQL  读后总结 (二)

主动把 l.tradeid转成utf8,就避免了被驱动表上的字符编码转换。

一个例子:
表结构如下:

mysql> CREATE TABLE `table_a` (
  `id` int(11) NOT NULL,
  `b` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

假设现在表里面,有100万行数据,其中有10万行数据的b的值是’1234567890’, 假设现在执行语句是这么写的:

mysql> select * from table_a where b='1234567890abcd';

这时候,MySQL会怎么执行呢?
最理想的情况是,MySQL看到字段b定义的是varchar(10),那肯定返回空呀。但是,MySQL并没有这么做。

这条SQL语句的执行很慢,流程是这样的:

  1. 在传给引擎执行的时候,做了字符截断。因为引擎里面这个行只定义了长度是10,所以只截了前10个字节,就是’1234567890’进去做匹配;
  2. 这样满足条件的数据有10万行;
  3. 因为是select *, 所以要做10万次回表;
  4. 但是每次回表以后查出整行,到server层一判断,b的值都不是’1234567890abcd’;
  5. 返回结果是空。

虽然执行过程中可能经过函数操作,但是最终在拿到结果后,server层还是要做一轮判断的。

只查一行的语句,执行慢

构造一个表,这个表有两个字段id和c,并且插入10万行记录。

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

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i,i);
    set i=i+1;
  end while;
end;;
delimiter ;

call idata();

查询长时间不返回

mysql> select * from t where id=1;

查询结果长时间不返回。

MySQL  读后总结 (二)

一般碰到这种情况的话,大概率是表t被锁住了。

等MDL锁

使用show processlist命令查看Waiting for table metadata lock示意图。

MySQL  读后总结 (二)

这个状态表示的是,现在有一个线程正在表t上请求或者持有MDL写锁,把select语句堵住了。

MySQL 5.7版本修改了MDL的加锁策略,MySQL 5.7版本下复现这个场景的复现步骤。

MySQL  读后总结 (二)

session A 通过lock table命令持有表t的MDL写锁,而session B的查询需要获取MDL读锁。所以,session B进入等待状态。

这类问题的处理方式,就是找到谁持有MDL写锁,然后把它kill掉。

由于在show processlist的结果里面,session A的Command列是“Sleep”,导致查找起来很不方便。不过有了performance_schema和sys系统库以后,就方便多了。(MySQL启动时需要设置performance_schema=on,相比于设置为off会有10%左右的性能损失)

通过查询sys.schema_table_lock_waits这张表,我们就可以直接找出造成阻塞的process id,把这个连接用kill 命令断开即可。

MySQL  读后总结 (二)

等flush

在表t上,执行下面的SQL语句:

mysql> select * from information_schema.processlist where id=1;

查出来这个线程的状态是Waiting for table flush

MySQL  读后总结 (二)

这个状态表示的是,现在有一个线程正要对表t做flush操作。MySQL里面对表做flush操作的用法,一般有以下两个:

flush tables t with read lock;

flush tables with read lock;

这两个flush语句,如果指定表t的话,代表的是只关闭表t;如果没有指定具体的表名,则表示关闭MySQL里所有打开的表。

但是正常这两个语句执行起来都很快,除非它们也被别的线程堵住了。

所以,出现Waiting for table flush状态的可能情况是:有一个flush tables命令被别的语句堵住了,然后它又堵住了我们的select语句。

复现步骤

MySQL  读后总结 (二)

在session A中,我故意每行都调用一次sleep(1),这样这个语句默认要执行10万秒,在这期间表t一直是被session A“打开”着。然后,session B的flush tables t命令再要去关闭表t,就需要等session A的查询结束。这样,session C要再次查询的话,就会被flush 命令堵住了。

图7是这个复现步骤的show processlist结果。

MySQL  读后总结 (二)

等行锁

经过了表级锁的研究,我们的select 语句终于来到引擎里了。

mysql> select * from t where id=1 lock in share mode; 

由于访问id=1这个记录时要加读锁,如果这时候已经有一个事务在这行记录上持有一个写锁,我们的select语句就会被堵住。

复现步骤和现场:

MySQL  读后总结 (二)

MySQL  读后总结 (二)

session A启动了事务,占有写锁,还不提交,是导致session B被堵住的原因。

怎么查出是谁占着这个写锁。如果你用的是MySQL 5.7版本,可以通过sys.innodb_lock_waits 表查到。

查询方法:

mysql> select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`\G

MySQL  读后总结 (二)

可以看到,这个信息很全,4号线程是造成堵塞的罪魁祸首。而干掉这个罪魁祸首的方式,就是KILL QUERY 4或KILL 4。

不过,这里不应该显示“KILL QUERY 4”。这个命令表示停止4号线程当前正在执行的语句,而这个方法其实是没有用的。因为占有行锁的是update语句,这个语句已经是之前执行完成了的,现在执行KILL QUERY,无法让这个事务去掉id=1上的行锁。

实际上,KILL 4才有效,也就是说直接断开这个连接。这里隐含的一个逻辑就是,连接被断开的时候,会自动回滚这个连接里面正在执行的线程,也就释放了id=1上的行锁。

查询慢

查询慢的例子:

mysql> select * from t where c=50000 limit 1;

由于字段c上没有索引,这个语句只能走id主键顺序扫描,因此需要扫描5万行。

作为确认,你可以看一下慢查询日志。注意,这里为了把所有语句记录到slow log里,我在连接后先执行了 set long_query_time=0,将慢查询日志的时间阈值设置为0。

MySQL  读后总结 (二)

Rows_examined显示扫描了50000行。11.5毫秒返回,在线上一般都配置超过1秒才算慢查询。但是:坏查询不一定是慢查询。我们这个例子里面只有10万行记录,数据量大起来的话,执行时间就线性涨上去了,扫描行数多,所以执行慢。

只扫描一行,但是执行很慢的语句。
如图12所示,是这个例子的slow log。可以看到,执行的语句是

mysql> select * from t where id=1

虽然扫描行数是1,但执行时间却长达800毫秒。

MySQL  读后总结 (二)

如果我把这个slow log的截图再往下拉一点,你可以看到下一个语句,select * from t where id=1 lock in share mode,执行时扫描行数也是1行,执行时间是0.2毫秒。

MySQL  读后总结 (二)

MySQL  读后总结 (二)

第一个语句的查询结果里c=1,带lock in share mode的语句返回的是c=1000001。

复现步骤:

MySQL  读后总结 (二)

session A先用start transaction with consistent snapshot命令启动了一个事务,之后session B才开始执行update 语句。

session B执行完100万次update语句后,id=1这一行处于什么状态呢?

MySQL  读后总结 (二)

session B更新完100万次,生成了100万个回滚日志(undo log)。

带lock in share mode的SQL语句,是当前读,因此会直接读到1000001这个结果,所以速度很快;而select * from t where id=1这个语句,是一致性读,因此需要从1000001开始,依次执行undo log,执行了100万次以后,才将1这个结果返回。

注意,undo log里记录的其实是“把2改成1”,“把3改成2”这样的操作逻辑,画成减1的目的是方便看图。

幻读和幻读的问题

建表和初始化语句:

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

这个表除了主键id外,还有一个索引c,初始化语句在表中插入了6行数据。

问题:下面的语句序列,是怎么加锁的,加的锁又是什么时候释放的呢?

begin;
select * from t where d=5 for update;
commit;

这个语句命中d=5的这一行,对应的主键id=5,因此在select 语句执行完成后,id=5这一行会加一个写锁,而且由于两阶段锁协议,这个写锁会在执行commit语句的时候释放。

由于字段d上没有索引,因此这条查询语句会做全表扫描。那么,其他被扫描到的,但是不满足条件的5行记录上,会不会被加锁呢?

InnoDB的默认事务隔离级别是可重复读,所以本文接下来没有特殊说明的部分,都是设定在可重复读隔离级别下。

幻读是什么?

只在id=5这一行加锁,而其他行的不加锁的话,会怎么样。

假设一个场景:

MySQL  读后总结 (二)

session A里执行了三次查询,分别是Q1、Q2和Q3。它们的SQL语句相同,都是select * from t where d=5 for update。这个语句的意思你应该很清楚了,查所有d=5的行,而且使用的是当前读,并且加上写锁。

这三条SQL语句,分别会返回什么结果。

  1. Q1只返回id=5这一行;
  2. 在T2时刻,session B把id=0这一行的d值改成了5,因此T3时刻Q2查出来的是id=0和id=5这两行;
  3. 在T4时刻,session C又插入一行(1,1,5),因此T5时刻Q3查出来的是id=0、id=1和id=5的这三行。

其中,Q3读到id=1这一行的现象,被称为“幻读”。也就是说,幻读指的是一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行。

“幻读”说明:

  1. 在可重复读隔离级别下,普通的查询是快照读,是不会看到别的事务插入的数据的。因此,幻读在“当前读”下才会出现。
  2. 上面session B的修改结果,被session A之后的select语句用“当前读”看到,不能称为幻读。幻读仅专指“新插入的行”。

如果用事务可见性规则来分析的话,这三条SQL语句的返回结果都没有问题。

因为这三个查询都是加了for update,都是当前读。而当前读的规则,就是要能读到所有已经提交的记录的最新值。并且,session B和sessionC的两条语句,执行后就会提交,所以Q2和Q3就是应该看到这两个事务的操作效果,而且也看到了,这跟事务的可见性规则并不矛盾,但是这里还有问题。

幻读问题

语义上

session A在T1时刻就声明了,“我要把所有d=5的行锁住,不准别的事务进行读写操作”。而实际上,这个语义被破坏了。

再往session B和session C里面分别加一条SQL语句,然后看看出现了什么现象。

MySQL  读后总结 (二)

session B的第二条语句update t set c=5 where id=0,语义是“我把id=0、d=5这一行的c值,改成了5”。

由于在T1时刻,session A 还只是给id=5这一行加了行锁, 并没有给id=0这行加上锁。因此,session B在T2时刻,是可以执行这两条update语句的。这样,就破坏了 session A 里Q1语句要锁住所有d=5的行的加锁声明。

session C也是一样的道理,对id=1这一行的修改,也是破坏了Q1的加锁声明。

数据一致性的问题
锁的设计是为了保证数据的一致性。而这个一致性,不止是数据库内部数据状态在此刻的一致性,还包含了数据和日志在逻辑上的一致性。

为了说明这个问题,给session A在T1时刻再加一个更新语句,即:

update t set d=100 where d=5

MySQL  读后总结 (二)

update的加锁语义和select …for update 是一致的,所以这时候加上这条update语句也很合理。session A声明说“要给d=5的语句加上锁”,就是为了要更新数据,新加的这条up这样看,这些数据也没啥问题,但是我们再来看看这时候binlog里面的内容。
date语句就是把它认为加上了锁的这一行的d值修改成了100。

图3执行完成后,数据库里的结果。

  1. 经过T1时刻,id=5这一行变成 (5,5,100),当然这个结果最终是在T6时刻正式提交的;
  2. 经过T2时刻,id=0这一行变成(0,5,5);
  3. 经过T4时刻,表里面多了一行(1,5,5);
  4. 其他行跟这个执行序列无关,保持不变。

这样看,这些数据也没啥问题,但是我们再来看看这时候binlog里面的内容。

  1. T2时刻,session B事务提交,写入了两条语句;
  2. T4时刻,session C事务提交,写入了两条语句;
  3. T6时刻,session A事务提交,写入了update t set d=100 where d=5 这条语句。

统一放到一起的话,就是:

update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/

update t set d=100 where d=5;/*所有d=5的行,d改成100*/

这个语句序列,不论是拿到备库去执行,还是以后用binlog来克隆一个库,这三行的结果,都变成了 (0,5,100)、(1,5,100)和(5,5,100)。

也就是说,id=0和id=1这两行,发生了数据不一致。这个问题很严重。

这个数据不一致到底是怎么引入的?

假设“select * from t where d=5 for update这条语句只给d=5这一行,也就是id=5的这一行加锁”导致的。

所以会认为,上面的设定不合理,要改。

我们把扫描过程中碰到的行,也都加上写锁,再来看看执行效果。

MySQL  读后总结 (二)

由于session A把所有的行都加了写锁,所以session B在执行第一个update语句的时候就被锁住了。需要等到T6时刻session A提交以后,session B才能继续执行。

这样对于id=0这一行,在数据库里的最终结果还是 (0,5,5)。在binlog里面,执行序列是这样的:

insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/

update t set d=100 where d=5;/*所有d=5的行,d改成100*/

update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

按照日志顺序执行,id=0这一行的最终结果也是(0,5,5)。所以,id=0这一行的问题解决了。

但同时也可以看到,id=1这一行,在数据库里面的结果是(1,5,5),而根据binlog的执行结果是(1,5,100),也就是说幻读的问题还是没有解决。为什么已经把所有的记录都上了锁,还是阻止不了id=1这一行的插入和更新呢?

原因很简单。在T3时刻,我们给所有行加锁的时候,id=1这一行还不存在,不存在也就加不上锁。

也就是说,即使把所有的记录都加上锁,还是阻止不了新插入的记录

如何解决幻读?

产生幻读的原因是,行锁只能锁住行,但是新插入记录这个动作,要更新的是记录之间的“间隙”。因此,为了解决幻读问题,InnoDB只好引入新的锁,也就是间隙锁(Gap Lock)。

间隙锁,锁的就是两个值之间的空隙。比如文章开头的表t,初始化插入了6个记录,这就产生了7个间隙。

MySQL  读后总结 (二)

当你执行 select * from t where d=5 for update的时候,就不止是给数据库中已有的6个记录加上了行锁,还同时加了7个间隙锁。这样就确保了无法再插入新的记录。

也就是说这时候,在一行行扫描的过程中,不仅将给行加上了行锁,还给行两边的空隙,也加上了间隙锁。

数据行是可以加上锁的实体,数据行之间的间隙,也是可以加上锁的实体。但是间隙锁跟我们之前碰到过的锁都不太一样。

比如行锁,分成读锁和写锁。下图就是这两种类型行锁的冲突关系。

MySQL  读后总结 (二)

也就是说,跟行锁有冲突关系的是“另外一个行锁”。

但是间隙锁不一样,跟间隙锁存在冲突关系的,是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

举个例子:

MySQL  读后总结 (二)

这里session B并不会被堵住。因为表t里并没有c=7这个记录,因此session A加的是间隙锁(5,10)。而session B也是在这个间隙加的间隙锁。它们有共同的目标,即:保护这个间隙,不允许插入值。但它们之间是不冲突的。

间隙锁和行锁合称next-key lock,每个next-key lock是前开后闭区间。也就是说,我们的表t初始化以后,如果用select * from t for update要把整个表所有记录锁起来,就形成了7个next-key lock,分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

备注:如果没有特别说明,我们把间隙锁记为开区间,把next-key lock记为前开后闭区间。

这个supremum从哪儿来的呢?

  • 这是因为+∞是开区间。实现上,InnoDB给每个索引加了一个不存在的最大值supremum,这样才符合我们前面说的“都是前开后闭区间”。

间隙锁和next-key lock的引入,帮我们解决了幻读的问题,但同时也带来了一些“困扰”。

例子:任意锁住一行,如果这一行不存在的话就插入,如果存在这一行就更新它的数据,代码如下:

begin;
select * from t where id=N for update;

/*如果行不存在*/
insert into t values(N,N,N);
/*如果行存在*/
update t set d=N set id=N;

commit;

使用insert … on duplicate key update 解决,在有多个唯一键的时候,这个方法是不能满足需求的。

这个逻辑一旦有并发,就会碰到死锁。为什么这个逻辑每次操作前用for update锁起来,已经是最严格的模式了,怎么还会有死锁呢?

用两个session来模拟并发,并假设N=9。

MySQL  读后总结 (二)

其实都不需要用到后面的update语句,就已经形成死锁了。我们按语句执行顺序来分析一下:

  1. session A 执行select … for update语句,由于id=9这一行并不存在,因此会加上间隙锁(5,10);
  2. session B 执行select … for update语句,同样会加上间隙锁(5,10),间隙锁之间不会冲突,因此这个语句可以执行成功;
  3. session B 试图插入一行(9,9,9),被session A的间隙锁挡住了,只好进入等待;
  4. session A试图插入一行(9,9,9),被session B的间隙锁挡住了。

两个session进入互相等待状态,形成死锁。当然,InnoDB的死锁检测马上就发现了这对死锁关系,让session A的insert语句报错返回了。

间隙锁的引入,可能会导致同样的语句锁住更大的范围,这其实是影响了并发度的

如果把隔离级别设置为读提交的话,就没有间隙锁了。但同时,你要解决可能出现的数据和日志不一致问题,需要把binlog格式设置为row。这,也是现在不少公司使用的配置组合。

使用读提交隔离级别加binlog_format=row的组合,如果读提交隔离级别够用,也就是说,业务不需要可重复读的保证,这样考虑到读提交下操作数据的锁范围更小(没有间隙锁),这个选择是合理的。

只改一行的语句,锁这么多

前提说明:

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

因为间隙锁在可重复读隔离级别下才有效,所以若没有特殊说明,默认是可重复读隔离级别。

两个“原则”、两个“优化”和一个“bug”

  1. 原则1:加锁的基本单位是next-key lock。希望你还记得,next-key lock是前开后闭区间。
  2. 原则2:查找过程中访问到的对象才会加锁。
  3. 优化1:索引上的等值查询,给唯一索引加锁的时候,next-key lock退化为行锁。
  4. 优化2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock退化为间隙锁。
  5. 一个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);

案例一:等值查询间隙锁

等值条件操作间隙:

MySQL  读后总结 (二)

由于表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 C修改id=10这行是可以的。

案例二:非唯一索引等值锁

覆盖索引上的锁:

MySQL  读后总结 (二)

这里session A要给索引c上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 ,只有访问到的对象才会加锁,这个查询使用覆盖索引,并不需要访问主键索引,所以主键索引上没有加任何锁,这就是为什么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。

案例三:主键索引范围锁

对于表t,下面的两条查询语句,加锁范围相同吗?

mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;

在逻辑上,这两条查语句肯定是等价的,但是它们的加锁规则不太一样。现在,我们就让session A执行第二个查询语句,来看看加锁效果。

MySQL  读后总结 (二)

用前面提到的加锁规则,来分析一下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 A定位查找id=10的行的时候,是当做等值查询来判断的,而向右扫描到id=15的时候,用的是范围查询判断。

案例四:非唯一索引范围锁

对照案例三,看两个范围查询加锁的例子。

需要注意的是,与案例三不同的是,案例四中查询语句的where部分用的是字段c。

MySQL  读后总结 (二)

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

所以从结果上来看,sesson B要插入(8,8,8)的这个insert语句时就被堵住了。

这里需要扫描到c=15才停止扫描,是合理的,因为InnoDB要扫到c=15,才知道不需要继续往后找了。

案例五:唯一索引范围锁bug

前面的四个案例,用到了加锁规则中的两个原则和两个优化,接下来再看一个关于加锁规则中bug的案例。

MySQL  读后总结 (二)

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这一行的行为,其实是没有必要的。因为扫描到id=15,就可以确定不用往后再找了。但实现上还是这么做了,所以说这是个bug。

官方bug系统上也有提到,但是并未被verifie。

案例六:非唯一索引上存在”等值”的例子

给表t插入一条新记录。

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

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

MySQL  读后总结 (二)

可以看到,虽然有两个c=10,但是它们的主键值id是不同的(分别是10和30),因此这两个c=10的记录之间,也是有间隙的。

图中画出了索引c上的主键id。为了跟间隙锁的开区间形式进行区别,我用(c=10,id=30)这样的形式,来表示索引上的一行。

案例六:

  • 这次用delete语句来验证。注意,delete语句加锁的逻辑,其实跟select … for update 是类似的,也就是我在文章开始总结的两个“原则”、两个“优化”和一个“bug”。

MySQL  读后总结 (二)

这时,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上的加锁范围,就是下图中蓝色区域覆盖的部分。

MySQL  读后总结 (二)

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

案例七:limit 语句加锁

对照案例6场景:

MySQL  读后总结 (二)

这个例子里,session A的delete语句加了 limit 2。你知道表t里c=10的记录其实只有两条,因此加不加limit 2,删除的效果都是一样的,但是加锁的效果却不同。可以看到,session B的insert语句执行通过了,跟案例六的结果不同。

因为:案例七里的delete语句明确加了limit 2的限制,因此在遍历到(c=10, id=30)这一行之后,满足条件的语句已经有两条,循环就结束了。

因此,索引c上的加锁范围就变成了从(c=5,id=5)到(c=10,id=30)这个前开后闭区间,如下图所示:

MySQL  读后总结 (二)

可以看到,(c=10,id=30)之后的这个间隙并没有在加锁范围里,因此insert语句插入c=12是可以执行成功的。

在删除数据的时候尽量加limit。这样不仅可以控制删除数据的条数,让操作更安全,还可以减小加锁的范围。

案例八:一个死锁的例子

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

MySQL  读后总结 (二)

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

  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不是还没申请成功吗?

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

问题1
下面这个图的执行序列中,为什么session B的insert语句会被堵住。

MySQL  读后总结 (二)

用加锁规则来分析一下,看看session A的select语句加了哪些锁:

  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=15、20两个行锁。

问题2
<=到底是间隙锁还是行锁?
跟“执行过程”配合起来分析。在InnoDB要去找“第一个值”的时候,是按照等值去找的,用的是等值判断的规则;找到第一个值以后,要在索引内找“下一个值”,对应于我们规则中说的范围查找。

MySQL“饮鸩止渴”提高性能的方法

短连接风暴

正常的短连接模式就是连接到数据库后,执行很少的SQL语句就断开,下次需要的时候再重连。如果使用的是短连接,在业务高峰期的时候,就可能出现连接数突然暴涨的情况。

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

在数据库压力比较小的时候,这些额外的成本并不明显。

但是,短连接模型存在一个风险,就是一旦数据库处理得慢一些,连接数就会暴涨。max_connections参数,用来控制一个MySQL实例同时存在的连接数的上限,超过这个值,系统就会拒绝接下来的连接请求,并报错提示“Too many 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的线程,可能是有损的。

MySQL  读后总结 (二)

在上面这个例子里,如果断开session A的连接,因为这时候session A还没有提交,所以MySQL只能按照回滚事务来处理;而断开session B的连接,就没什么大影响。所以,如果按照优先级来说,你应该优先断开像session B这样的事务外空闲的连接。

但是,怎么判断哪些是事务外空闲的呢?session C在T时刻之后的30秒执行show processlist,看到的结果是这样的。

MySQL  读后总结 (二)

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

MySQL  读后总结 (二)

这个结果里,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会跳过所有的权限验证阶段,包括连接过程和语句执行过程在内。

但是,这种方法特别符合我们标题里说的“饮鸩止渴”,风险极高,特别不建议使用的方案。尤其你的库外网可访问的话,就更不能这么做了。

在MySQL 8.0版本里,如果你启用–skip-grant-tables参数,MySQL会默认把 –skip-networking参数打开,表示这时候数据库只能被本地的客户端连接。可见,MySQL官方对skip-grant-tables这个参数的安全问题也很重视。

除了短连接数暴增可能会带来性能问题外,实际上,在线上碰到更多的是查询或者更新语句导致的性能问题。其中,查询问题比较典型的有两类,一类是由新出现的慢查询导致的,一类是由QPS(每秒查询数)突增导致的。

慢查询性能问题

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

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

索引没有设计好

这种场景一般就是通过紧急创建索引来解决。MySQL 5.6版本以后,创建索引都支持Online DDL了,对于那种高峰期数据库已经被这个语句打挂了的情况,最高效的做法就是直接执行alter table 语句。

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

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

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

语句没写好

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

比如,语句被错误地写成了 select * from t where id + 1 = 10000,你可以通过下面的方式,增加一个语句改写规则。

mysql> 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  读后总结 (二)

MySQL选错了索引。

这时候,应急方案就是给这个语句加上force index。

同样地,使用查询重写功能,给原来的语句加上force index,也可以解决这个问题。

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

  1. 上线前,在测试环境,把慢查询日志(slow log)打开,并且把long_query_time设置成0,确保每个语句都会被记录入慢查询日志;
  2. 在测试表里插入模拟线上的数据,做一遍回归测试;
  3. 观察慢查询日志里每类语句的输出,特别留意Rows_examined字段是否与预期一致。(我们在前面文章中已经多次用到过Rows_examined方法了,相信你已经动手尝试过了。如果还有不明白的,欢迎给我留言,我们一起讨论)。

如果新增的SQL语句不多,手动跑一下就可以。而如果是新项目的话,或者是修改了原有项目的 表结构设计,全量回归测试都是必要的。这时候,需要工具帮你检查所有的SQL语句的返回结果。比如,可以使用开源工具pt-query-digest(www.percona.com/doc/percona-toolki...)。

QPS突增问题

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

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

而下掉一个功能,如果从数据库端处理的话,对应于不同的背景,有不同的方法可用。

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

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

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

所以,方案3是用于止血的,跟前面提到的去掉权限验证一样,应该是所有选项里优先级最低的一个方案。

同时会发现,其实方案1和2都要依赖于规范的运维体系:虚拟化、白名单机制、业务账号分离。由此可见,更多的准备,往往意味着更稳定的系统。

MySQL是怎么保证数据不丢

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

binlog的写入机制

其实,binlog的写入逻辑比较简单:事务执行过程中,先把日志写到binlog cache,事务提交的时候,再把binlog cache写到binlog文件中。

一个事务的binlog是不能被拆开的,因此不论这个事务多大,也要确保一次性写入。这就涉及到了binlog cache的保存问题。

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

事务提交的时候,执行器把binlog cache里的完整事务写入到binlog中,并清空binlog cache。状态如图1所示。

MySQL  读后总结 (二)

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

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

write 和fsync的时机,是由参数sync_binlog控制的:

  1. sync_binlog=0的时候,表示每次提交事务都只write,不fsync;
  2. sync_binlog=1的时候,表示每次提交事务都会执行fsync;
  3. sync_binlog=N(N>1)的时候,表示每次提交事务都write,但累积N个事务后才fsync。

因此,在出现IO瓶颈的场景里,将sync_binlog设置成一个比较大的值,可以提升性能。在实际的业务场景中,考虑到丢失日志量的可控性,一般不建议将这个参数设成0,比较常见的是将其设置为100~1000中的某个数值。

但是,将sync_binlog设置为N,对应的风险是:如果主机发生异常重启,会丢失最近N个事务的binlog日志。

redo log的写入机制

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

redo log buffer里面的内容,每次生成后不需要直接持久化到磁盘。

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

事务还没提交的时候,redo log buffer中的部分日志就有可能被持久化到磁盘

从redo log可能存在的三种状态说起。这三种状态,对应的就是图2 中的三个颜色块。

MySQL  读后总结 (二)

这三种状态分别是:

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

MySQL  读后总结 (二)

这三种状态分别是:

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

日志写到redo log buffer是很快的,wirte到page cache也差不多,但是持久化到磁盘的速度就慢多了。

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

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

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

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

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

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

时序上redo log先prepare, 再写binlog,最后再把redo log commit。

如果把innodb_flush_log_at_trx_commit设置成1,那么redo log在prepare阶段就要持久化一次,因为有一个崩溃恢复逻辑是要依赖于prepare 的redo log,再加上binlog来恢复的。

每秒一次后台轮询刷盘,再加上崩溃恢复这个逻辑,InnoDB就认为redo log在commit的时候就不需要fsync了,只会write到文件系统的page cache中就够了。

通常说MySQL的“双1”配置,指的就是sync_binlog和innodb_flush_log_at_trx_commit都设置成 1。也就是说,一个事务完整提交前,需要等待两次刷盘,一次是redo log(prepare 阶段),一次是binlog。

这时候,有一个疑问,这意味着我从MySQL看到的TPS是每秒两万的话,每秒就会写四万次磁盘。但是,我用工具测试出来,磁盘能力也就两万左右,怎么能实现两万的TPS?

这时候就要用到组提交(group commit)机制了。

首先需要先介绍一下日志逻辑序列号(log sequence number,LSN)的概念。LSN是单调递增的,用来对应redo log的一个个写入点。每次写入长度为length的redo log, LSN的值就会加上length。

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

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

MySQL  读后总结 (二)

从图中可以看到,

  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有一个很有趣的优化:拖时间。

MySQL  读后总结 (二)

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

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

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

MySQL  读后总结 (二)

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

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

如果想提升binlog组提交的效果,可以通过设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count来实现。

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

这两个条件是或的关系,也就是说只要有一个满足条件就会调用fsync。

所以,当binlog_group_commit_sync_delay设置为0的时候,binlog_group_commit_sync_no_delay_count也无效了。

WAL机制是减少磁盘写,可是每次提交事务都要写redo log和binlog,这磁盘读写次数也没变少呀?

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。因为把这个参数设置成0,表示redo log只保存在内存中,这样的话MySQL本身异常重启也会丢数据,风险太大。而redo log写到文件系统的page cache的速度也是很快的,所以将这个参数设置成2跟设置成0其实性能差不多,但这样做MySQL异常重启时就不会丢数据了,相比之下风险会更小。

小结

问题1:执行一个update语句以后,我再去执行hexdump命令直接查看ibd文件内容,为什么没有看到数据有改变呢?

回答:这可能是因为WAL机制的原因。update语句执行完成后,InnoDB只保证写完了redo log、内存,可能还没来得及将数据写到磁盘。

问题2:为什么binlog cache是每个线程自己维护的,而redo log buffer是全局共用的?

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

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

问题3:事务执行期间,还没到提交阶段,如果发生crash的话,redo log肯定丢了,这会不会导致主备不一致呢?

回答:不会。因为这时候binlog 也还在binlog cache里,没发给备库。crash以后redo log和binlog都没有了,从业务角度看这个事务也没有提交,所以数据是一致的。

问题4:如果binlog写完盘以后发生crash,这时候还没给客户端答复就重启了。等客户端再重连进来,发现事务已经提交成功了,这是不是bug?

回答:不是。

设想一下更极端的情况,整个事务都提交成功了,redo log commit完成了,备库也收到binlog并执行了。但是主库和客户端网络断开了,导致事务成功的包返回不回去,这时候客户端也会收到“网络断开”的异常。这种也只能算是事务成功的,不能认为是bug。

实际上数据库的crash-safe保证的是:

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

问题5:在什么时候会把线上生产库设置成“非双1”?

  1. 业务高峰期。一般如果有预知的高峰期,DBA会有预案,把主库设置成“非双1”。
  2. 备库延迟,为了让备库尽快赶上主库。@永恒记忆和@Second Sight提到了这个场景。
  3. 用备份恢复主库的副本,应用binlog的过程,这个跟上一种场景类似。
  4. 批量导入数据的时候。

一般情况下,把生产库改成“非双1”配置,是设置innodb_flush_logs_at_trx_commit=2、sync_binlog=1000。

备注:

  • 由于从库设置了 binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count导致一直延迟的情况。我们在主库设置这两个参数,是为了减少binlog的写盘压力。备库这么设置,尤其在“快要追上”的时候,就反而会受这两个参数的拖累。一般追主备就用“非双1”(追上记得改回来)。
  • 在sync_binlog=0的情况下,设置sync_delay和sync_no_delay_count的现象,点赞这种发现边界的意识和手动验证的好习惯。是这样的:sync_delay和sync_no_delay_count的逻辑先走,因此该等还是会等。等到满足了这两个条件之一,就进入sync_binlog阶段。这时候如果判断sync_binlog=0,就直接跳过,还是不调fsync。
  • 设置sync_binlog=0的时候,还是可以看到binlog文件马上做了修改。这个是对的,我们说“写到了page cache”,就是文件系统的page cache。而你用ls命令看到的就是文件系统返回的结果。

MySQL如何保证主备一致

MySQL主备的基本原理

主备切换流程:

MySQL  读后总结 (二)

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

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

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

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

把备库设置成只读了,还怎么跟主库保持同步更新呢?

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

再来看看节点A到B这条线的内部流程是什么样的。图2中画出的就是一个update语句在节点A执行,然后同步到节点B的完整流程图。

MySQL  读后总结 (二)

图2中可以看到:主库接收到客户端的更新请求后,执行内部事务的更新逻辑,同时写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的三种格式对比

一种是statement,一种是row,第三种格式,叫作mixed,它是前两种格式的混合。

创建一个表,并初始化几行数据:

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

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

如果要在表中删除一行数据的话,我们来看看这个delete语句的binlog是怎么记录的。

注意,下面这个语句包含注释,如果你用MySQL客户端来做这个实验的话,要记得加-c参数,否则客户端会自动去掉注释。

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

当binlog_format=statement时,binlog里面记录的就是SQL语句的原文。你可以用

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

命令看binlog中的内容

MySQL  读后总结 (二)

看一下图3的输出结果。

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

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

MySQL  读后总结 (二)

可以看到,运行这条delete命令产生了一个warning,原因是当前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语句的时候,用的是索引a;而在备库执行这条SQL语句的时候,却使用了索引t_modified。因此,MySQL认为这样写是有风险的。

那么,如果把binlog的格式改为binlog_format=‘row’, 是不是就没有这个问题了呢?先来看看这时候binog中的内容吧。

MySQL  读后总结 (二)

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

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

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

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

MySQL  读后总结 (二)

从这个图中,我们可以看到以下几个信息:

  • server id 1,表示这个事务是在server_id=1的这个库上执行的。
  • 每个event都有CRC32的值,这是因为我把参数binlog_checksum设置成了CRC32。
  • Table_map event跟在图5中看到的相同,显示了接下来要打开的表,map到数字226。现在我们这条SQL语句只操作了一张表,如果要操作多张表呢?每个表都有一个对应的Table_map event、都会map到一个单独的数字,用于区分对不同表的操作。
  • 我们在mysqlbinlog的命令中,使用了-vv参数是为了把内容都解析出来,所以从结果里面可以看到各个字段的值(比如,@1=4、 @2=4这些值)。
  • 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格式可以利用statment格式的优点,同时又避免了数据不一致的风险。

因此,如果线上MySQL设置的binlog格式是statement的话,那基本上就可以认为这是一个不合理的设置。你至少应该把binlog的格式设置为mixed。

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

现在越来越多的场景要求把MySQL的binlog格式设置成row。这么做的理由有很多,举一个可以直接看出来的好处:恢复数据

接下来,分别从delete、insert和update这三种SQL语句的角度,来看看数据恢复的问题。

通过图6可以看出来,即使执行的是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语句:

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

如果把binlog格式设置为mixed,MySQL会把它记录为row格式还是statement格式呢?
语句执行的效果:

MySQL  读后总结 (二)

可以看到,MySQL用的是statement格式。如果这个binlog过了1分钟才传给备库的话,那主备的数据不就不一致了吗?

接下来,再用mysqlbinlog工具来看看:

MySQL  读后总结 (二)

从图中的结果可以看到,原来binlog在记录event的时候,多记了一条命令:SET TIMESTAMP=1546103491。它用 SET TIMESTAMP命令约定了接下来的now()函数的返回时间。

因此,不论这个binlog是1分钟之后被备库执行,还是3天后用来恢复这个库的备份,这个insert语句插入的行,值都是固定的。也就是说,通过这条SET TIMESTAMP命令,MySQL就确保了主备数据的一致性。

例子:在重放binlog数据时,用mysqlbinlog解析出日志,然后把里面的statement语句直接拷贝出来执行。

这个方法是有风险的。因为有些语句的执行结果是依赖于上下文命令的,直接执行的结果很可能是错误的。

所以,用binlog来恢复数据的标准做法是,用 mysqlbinlog工具解析出来,然后把解析结果整个发给MySQL执行。类似下面的命令:

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

这个命令的意思是,将 master.000001 文件里面从第2738字节到第2973字节中间这段内容解析出来,放到MySQL去执行。

循环复制问题

binlog的特性确保了在备库执行相同的binlog,可以得到与主库相同的状态。

因此,可以认为正常情况下主备的数据是一致的。也就是说,图1中A、B两个节点的内容是一致的。其实,图1中我画的是M-S结构,但实际生产上使用比较多的是双M结构,也就是图9所示的主备切换流程。

MySQL  读后总结 (二)

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

但是,双M结构还有一个问题需要解决。

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

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

从上面的图6中可以看到,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与自己的相同,就不会再处理这个日志。所以,死循环在这里就断掉了。

什么情况下双M结构会出现循环复制?
一种场景是,在一个主库更新事务后,用命令set global server_id=x修改了server_id。等日志再传回来的时候,发现server_id跟自己的server_id不同,就只能执行了。

另一种场景是,有三个节点的时候,如图7所示,trx1是在节点 B执行的,因此binlog上的server_id就是B,binlog传给节点 A,然后A和A’搭建了双M结构,就会出现循环复制。

MySQL  读后总结 (二)

这种三节点复制的场景,做数据库迁移的时候会出现。

如果出现了循环复制,可以在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;

MySQL如何保证高可用

在一个主备关系中,每个备库接收主库的binlog并执行。

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

但是,MySQL要提供高可用能力,只有最终一致性是不够的。

双M结构的主备切换流程图:

MySQL  读后总结 (二)

主备延迟

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

先一起看看主动切换的场景。

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

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

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

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

seconds_behind_master的计算方法是这样的:

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

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

如果主备库机器的系统时间设置不一致,会不会导致主备延迟的值不准?

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

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

所以说,主备延迟最直接的表现是,备库消费中转日志(relay log)的速度,比主库生产binlog的速度要慢。

主备延迟的来源

首先,有些部署条件下,备库所在机器的性能要比主库所在的机器性能差。

一般情况下,有人部署时的想法是,反正备库没有请求,所以可以用差一点儿的机器。或者,他们会把20个主库放在4台机器上,而把备库集中在一台机器上。

其实都知道,更新请求对IOPS的压力,在主库和备库上是无差别的。所以,做这种部署时,一般都会将备库设置为“非双1”的模式。

但实际上,更新过程中也会触发大量的读操作。所以,当备库主机上的多个备库都在争抢资源的时候,就可能会导致主备延迟了。

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

追问1:但是,做了对称部署以后,还可能会有延迟。这是为什么呢?

  • 这就是第二种常见的可能了,即备库的压力大。一般的想法是,主库既然提供了写能力,那么备库可以提供一些读能力。或者一些运营后台需要的分析语句,不能影响正常业务,所以只能在备库上跑。

场景:于主库直接影响业务,大家使用起来会比较克制,反而忽视了备库的压力控制。结果就是,备库上的查询耗费了大量的CPU资源,影响了同步速度,造成主备延迟。

这种情况,我们一般可以这么处理:

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

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

备注从库和备库在概念上其实差不多。在我们这个专栏里,为了方便描述,我把会在HA过程中被选成新主库的,称为备库,其他的称为从库。

追问2:采用了一主多从,保证备库的压力不会超过主库,还有什么情况可能导致主备延迟吗?

这就是第三种可能了,即大事务。

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

一个典型的大事务场景:不要一次性地用delete语句删除太多数据

比如,一些归档类的数据,平时没有注意删除历史数据,等到空间快满了,业务开发人员要一次性地删掉大量历史数据。同时,又因为要避免在高峰期操作会影响业务(至少有这个意识还是很不错的),所以会在晚上执行这些大量数据的删除操作。

结果,负责的DBA半夜就会收到延迟报警。然后,DBA团队就要求你后续再删除数据的时候,要控制每个事务删除的数据量,分成多次删除。

另一种典型的大事务场景,就是大表DDL。

追问3:如果主库上也不做大事务了,还有什么原因会导致主备延迟吗?

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

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

可靠性优先策略

在图1的双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系统来完成的,暂时称之为可靠性优先流程。

MySQL  读后总结 (二)
备注:图中的SBM,是seconds_behind_master参数的简写。

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

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

试想如果一开始主备延迟就长达30分钟,而不先做判断直接切换的话,系统的不可用时间就会长达30分钟,这种情况一般业务都是不可接受的。

当然,系统的不可用时间,是由这个数据可靠性优先的策略决定的。也可以选择可用性优先的策略,来把这个不可用时间几乎降为0。

可用性优先策略

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

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

一个可用性优先流程产生数据不一致的案例:

mysql> 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的语句后,发起了主备切换。

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

MySQL  读后总结 (二)

现在,一起分析下这个切换流程:

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

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

那么,如果我还是用可用性优先策略,但设置binlog_format=row,情况又会怎样呢?

因为row格式在记录binlog的时候,会记录新插入的行的所有字段值,所以最后只会有一行不一致。而且,两边的主备同步的应用线程会报错duplicate key error并停止。也就是说,这种情况下,备库B的(5,4)和主库A的(5,5)这两行数据,都不会被对方执行。

详细过程:

MySQL  读后总结 (二)

从上面的分析中,可以看出一些结论:

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

有没有哪种情况数据的可用性优先级更高呢?
一个场景:

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

这时候,你可能就需要选择先强行切换,事后再补数据的策略。

当然,事后复盘的时候,想到的一个改进措施就是,让业务逻辑不要依赖于这类日志的写入。也就是说,日志写入这个逻辑模块应该可以降级,比如写到本地文件,或者写到另外一个临时库里面。

这样的话,这种场景就又可以使用可靠性优先策略了。

接下来再来看看,按照可靠性优先的思路,异常切换会是什么效果?

假设,主库A和备库B间的主备延迟是30分钟,这时候主库A掉电了,HA系统要切换B作为主库。在主动切换的时候,可以等到主备延迟小于5秒的时候再启动切换,但这时候已经别无选择了。

MySQL  读后总结 (二)

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

那能不能直接切换到备库B,但是保持B只读呢?

  • 这样也不行。

因为,这段时间内,中转日志还没有应用完成,如果直接发起主备切换,客户端查询看不到之前执行完成的事务,会认为有“数据丢失”。

虽然随着中转日志的继续应用,这些数据会恢复回来,但是对于一些业务来说,查询到“暂时丢失数据的状态”也是不能被接受的。

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

问题1:几种造成主从延迟的情况

  1. 主库DML语句并发大,从库qps高
  2. 从库服务器配置差或者一台服务器上几台从库(资源竞争激烈,特别是io)
  3. 主库和从库的参数配置不一样
  4. 大事务(DDL,我觉得DDL也相当于一个大事务)
  5. 从库上在进行备份操作
  6. 表上无主键的情况(主库利用索引更改数据,备库回放只能用全表扫描,这种情况可以调整slave_rows_search_algorithms参数适当优化下)
  7. 设置的是延迟备库
  8. 备库空间不足的情况下

问题2:T3的解释是:备库执行完这个事物。则:Seconds_Behind_Master=T3-T1。如T1=30min,主执行完成,备没有执行。猜测1:那么Seconds_Behind_Master=30min吗?猜测2:备执需要先把这个30min的事务执行完后,Seconds_Behind_Master=30min?

  • 备库没收到,还是收到没执行,前者0,后者30.

问题3:什么情况下,备库的主备延迟会表现为一个45度的线段?
产生这种现象典型的场景主要包括两种:

  • 一种是大事务(包括大表DDL、一个事务操作很多行);
  • 还有一种情况比较隐蔽,就是备库起了一个长事务,比如
    begin; 
    select * from t limit 1

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

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

主备流程图:

MySQL  读后总结 (二)

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

在主库上,影响并发度的原因就是各种锁了。由于InnoDB引擎支持行锁,除了所有并发事务都在更新同一行(热点行)这种极端场景外,它对业务并发度的支持还是很友好的。所以,你在性能测试的时候会发现,并发压测线程32就比单线程时,总体吞吐量高。

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

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

从单线程复制到最新版本的多线程复制,中间的演化经历了好几个版本。

其实说到底,所有的多线程复制机制,都是要把图1中只有一个线程的sql_thread,拆成多个线程,也就是都符合下面的这个模型:

MySQL  读后总结 (二)

图2中,coordinator就是原来的sql_thread, 不过现在它不再直接更新数据了,只负责读取中转日志和分发事务。真正更新日志的,变成了worker线程。而work线程的个数,就是由参数slave_parallel_workers决定的。把这个值设置为8~16之间最好(32核物理机的情况),毕竟备库还有可能要提供读查询,不能把CPU都吃光了。

接下来,需要先思考一个问题:事务能不能按照轮询的方式分发给各个worker,也就是第一个事务分给worker_1,第二个事务发给worker_2呢?

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

接下来,再设想一下另外一个问题:同一个事务的多个更新语句,能不能分给不同的worker来执行呢?

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

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

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

各个版本的多线程复制,都遵循了这两条基本原则。

MySQL 5.5版本的并行复制策略

官方MySQL 5.5版本是不支持并行复制的。
这里有两个版本的并行策略,即按表分发策略和按行分发策略,帮助理解MySQL官方版本并行复制策略的迭代。

按表分发策略

按表分发事务的基本思路是,如果两个事务更新不同的表,它们就可以并行。因为数据是存储在表里的,所以按表分发,可以保证两个worker不会更新同一行。

当然,如果有跨表的事务,还是要把两张表放在一起考虑的。如图3所示,就是按表分发的规则。

MySQL  读后总结 (二)

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

在有事务分配给worker时,事务里面涉及的表会被加到对应的hash表中。worker执行完成后,这个表会被从hash表中去掉。

图3中,hash_table_1表示,现在worker_1的“待执行事务队列”里,有4个事务涉及到db1.t1表,有1个事务涉及到db2.t2表;hash_table_2表示,现在worker_2中有一个事务会更新到表t3的数据。

假设在图中的情况下,coordinator从中转日志中读入一个新事务T,这个事务修改的行涉及到表t1和t3。

现在用事务T的分配流程,来看一下分配规则。

  1. 由于事务T中涉及修改表t1,而worker_1队列中有事务在修改表t1,事务T和队列中的某个事务要修改同一个表的数据,这种情况我们说事务T和worker_1是冲突的。
  2. 按照这个逻辑,顺序判断事务T和每个worker队列的冲突关系,会发现事务T跟worker_2也冲突。
  3. 事务T跟多于一个worker冲突,coordinator线程就进入等待。
  4. 每个worker继续执行,同时修改hash_table。假设hash_table_2里面涉及到修改表t3的事务先执行完成,就会从hash_table_2中把db1.t3这一项去掉。
  5. 这样coordinator会发现跟事务T冲突的worker只有worker_1了,因此就把它分配给worker_1。
  6. coordinator继续读下一个中转日志,继续分配事务。

也就是说,每个事务在分发的时候,跟所有worker的冲突关系包括以下三种情况:

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

这个按表分发的方案,在多个表负载均匀的场景里应用效果很好。但是,如果碰到热点表,比如所有的更新事务都会涉及到某一个表的时候,所有事务都会被分配到同一个worker中,就变成单线程复制了。

按行分发策略

要解决热点表的并行复制问题,就需要一个按行并行复制的方案。按行复制的核心思路是:如果两个事务没有更新相同的行,它们在备库上可以并行执行。显然,这个模式要求binlog格式必须是row。

这时候,我们判断一个事务T和worker是否冲突,用的就规则就不是“修改同一个表”,而是“修改同一行”。

按行复制和按表复制的数据结构差不多,也是为每个worker,分配一个hash表。只是要实现按行分发,这时候的key,就必须是“库名+表名+唯一键的值”。

但是,这个“唯一键”只有主键id还是不够的,我们还需要考虑下面这种场景,表t1中除了主键,还有唯一索引a:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB;

insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5);

可以看到,这两个事务要更新的行的主键值不同,但是如果它们被分到不同的worker,就有可能session B的语句先执行。这时候id=1的行的a的值还是1,就会报唯一键冲突。

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

比如,在上面这个例子中,要在表t1上执行update t1 set a=1 where id=2语句,在binlog里面记录了整行的数据修改前各个字段的值,和修改后各个字段的值。

因此,coordinator在解析这个语句的binlog的时候,这个事务的hash表就有三个项:

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

相比于按表并行分发策略,按行并行策略在决定线程分发的时候,需要消耗更多的计算资源。这两个方案其实都有一些约束条件:

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

但是好在这三条约束规则,本来就是DBA之前要求业务开发人员必须遵守的线上使用规范,所以这两个并行复制策略在应用上也没有碰到什么麻烦。

对比按表分发和按行分发这两个方案的话,按行分发策略的并行度更高。不过,如果是要操作很多行的大事务的话,按行分发的策略有两个问题:

  1. 耗费内存。比如一个语句要删除100万行数据,这时候hash表就要记录100万个项。
  2. 耗费CPU。解析binlog,然后计算hash值,对于大事务,这个成本还是很高的。

所以,在实现这个策略的时候会设置一个阈值,单个事务如果超过设置的行数阈值(比如,如果单个事务更新的行数超过10万行),就暂时退化为单线程模式,退化过程的逻辑大概是这样的:

  1. coordinator暂时先hold住这个事务;
  2. 等待所有worker都执行完成,变成空队列;
  3. coordinator直接执行这个事务;
  4. 恢复并行模式。

MySQL 5.6版本的并行复制策略

官方MySQL5.6版本,支持了并行复制,只是支持的粒度是按库并行。用于决定分发策略的hash表里,key就是数据库名。

这个策略的并行效果,取决于压力模型。如果在主库上有多个DB,并且各个DB的压力均衡,使用这个策略的效果会很好。

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

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

但是,如果你的主库上的表都放在同一个DB里面,这个策略就没有效果了;或者如果不同DB的热点不同,比如一个是业务逻辑库,一个是系统配置库,那也起不到并行的效果。

理论上可以创建不同的DB,把相同热度的表均匀分到这些不同的DB中,强行使用这个策略。不过,由于需要特地移动数据,这个策略用得并不多。

MariaDB的并行复制策略

MariaDB的并行复制策略的特性:

  1. 能够在同一组里提交的事务,一定不会修改同一行;
  2. 主库上可以并行执行的事务,备库上也一定是可以并行执行的。

在实现上,MariaDB是这么做的:

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

当时,这个策略出来的时候是相当惊艳的。因为,之前业界的思路都是在“分析binlog,并拆分到worker”上。而MariaDB的这个策略,目标是“模拟主库的并行模式”。

但是,这个策略有一个问题,它并没有实现“真正的模拟主库并发度”这个目标。在主库上,一组事务在commit的时候,下一组事务是同时处于“执行中”状态的。

如图5所示,假设了三组事务在主库的执行情况,你可以看到在trx1、trx2和trx3提交的时候,trx4、trx5和trx6是在执行的。这样,在第一组事务提交完成的时候,下一组事务很快就会进入commit状态。

MySQL  读后总结 (二)

而按照MariaDB的并行复制策略,备库上的执行效果如图6所示。

MySQL  读后总结 (二)

可以看到,在备库上执行的时候,要等第一组事务完全执行完成后,第二组事务才能开始执行,这样系统的吞吐量就不够。

另外,这个方案很容易被大事务拖后腿。假设trx2是一个超大事务,那么在备库应用的时候,trx1和trx3执行完成后,就只能等trx2完全执行完成,下一组才能开始执行。这段时间,只有一个worker线程在工作,是对资源的浪费。

不过即使如此,这个策略仍然是一个很漂亮的创新。因为,它对原系统的改造非常少,实现也很优雅。

MySQL 5.7的并行复制策略

在MariaDB并行复制实现之后,官方的MySQL5.7版本也提供了类似的功能,由参数slave-parallel-type来控制并行复制策略:

  1. 配置为DATABASE,表示使用MySQL 5.6版本的按库并行策略;
  2. 配置为 LOGICAL_CLOCK,表示的就是类似MariaDB的策略。不过,MySQL 5.7这个策略,针对并行度做了优化。

同时处于“执行状态”的所有事务,是不能并行的。

因为,这里面可能有由于锁冲突而处于锁等待状态的事务。如果这些事务在备库上被分配到不同的worker,就会出现备库跟主库不一致的情况。

而上面提到的MariaDB这个策略的核心,是“所有处于commit”状态的事务可以并行。事务处于commit状态,表示已经通过了锁冲突的检验了。

两阶段提交过程图:

MySQL  读后总结 (二)

其实,不用等到commit阶段,只要能够到达redo log prepare阶段,就表示事务已经通过锁冲突的检验了。

因此,MySQL 5.7并行复制策略的思想是:

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

在binlog的组提交的时候,有两个参数:

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

这两个参数是用于故意拉长binlog从write到fsync的时间,以此减少binlog的写盘次数。在MySQL 5.7的并行复制策略里,它们可以用来制造更多的“同时处于prepare阶段的事务”。这样就增加了备库复制的并行度。

也就是说,这两个参数,既可以“故意”让主库提交得慢些,又可以让备库执行得快些。在MySQL 5.7处理备库延迟的时候,可以考虑调整这两个参数值,来达到提升备库复制并发度的目的。

MySQL 5.7.22的并行复制策略

在2018年4月份发布的MySQL 5.7.22版本里,MySQL增加了一个新的并行复制策略,基于WRITESET的并行复制。

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

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

当然为了唯一标识,这个hash值是通过“库名+表名+索引名+值”计算出来的。如果一个表上除了有主键索引外,还有其他唯一索引,那么对于每个唯一索引,insert语句对应的writeset就要多增加一个hash值。

这跟前面介绍的基于MySQL 5.5版本的按行分发的策略是差不多的。不过,MySQL官方的这个实现还是有很大的优势:

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

因此,MySQL 5.7.22的并行复制策略在通用性上还是有保证的。

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

问题1:mariadb的并行策略,当同一组中有3个事务,它们都对同一行同一字段值进行更改,而它们的commit_id相同,可以在从库并行执行,那么3者的先后顺序是怎么保证不影响该行该字段的最终结果与主库一致?

  1. redo log prepare write
  2. binlog write
  3. redo log prepare fsync
  4. binlog fsync
  5. redo log commit write
  • 如果更新通一条记录是有锁的,只能一个事务执行,其他事务等待锁。
    第4步的时候会因为下面两个参数,等其他没有锁冲突的事务,一起刷盘,此时一起执行的事务拥有相同的commit_id
    binlog_group_commit_sync_delay
    binlog_group_commit_sync_no_delay_count
  • 执行步骤5后,释放锁,等待锁的事务开始执行。

所以对同一行更新的事务,不可能拥有相同的commit_id

问题2:如果主库都是单线程压力模式,在从库追主库的过程中,binlog-transaction-dependency-tracking 应该选用什么参数?

  • 应该将这个参数设置为WRITESET。

由于主库是单线程压力模式,所以每个事务的commit_id都不同,那么设置为COMMIT_ORDER模式的话,从库也只能单线程执行。

同样地,由于WRITESET_SESSION模式要求在备库应用日志的时候,同一个线程的日志必须与主库上执行的先后顺序相同,也会导致主库单线程压力模式下退化成单线程复制。

所以,应该将binlog-transaction-dependency-tracking 设置为WRITESET。

转自:极客时间《 Mysql实战45讲》 –林晓斌

本作品采用《CC 协议》,转载必须注明作者和本文链接
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!