本篇接上一篇笔记，主要讲了排序，幻读问题，间隙锁，mysql保证数据不丢，主备一致等内容，详细见目录。有问题及时在本博客或在CSDN留言.

order by是怎么工作的

全字段排序

以如下案例来看下排序的过程.假设有下表：

1
2
3
4
5
6
7
8
9
CREATE TABLE `t` (
	`id` INT ( 11 ) NOT NULL,
	`city` VARCHAR ( 16 ) NOT NULL,
	`name` VARCHAR ( 16 ) NOT NULL,
	`age` INT ( 11 ) NOT NULL,
	`addr` VARCHAR ( 128 ) DEFAULT NULL,
	PRIMARY KEY ( `id` ),
KEY `city` ( `city` ) 
) ENGINE = INNODB;

假设现在的需求是查询城市是”杭州”的所有人名字，并且按照姓名排序返回前1000个人的姓名、年龄,sql语句如下

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

当时用explain查看该语句执行计划后，可以看到extra字段中有Using filesort表示需要排序，mysql会给每个线程分配一块内存用于排序，称为 sort_buffer按name排序这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。

sort_buffer_size，就是MySQL为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序，内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。 可以这么简单理解，MySQL 将需要排序的数据分成 N份，每一份单独排序后存在这些临时文件中。然后把这 N 个有序文件再合并成一个有序的大文件。

在这种排序中，要查询的字段city,name,age都会放进sort_buffer中，因此也称为全字段排序。整个执行过程如下：

  • 初始化sort_buffer，确定放入name、city、age这三个字段；
  • 从索引city找到第一个满足city=’杭州’条件的主键id；
  • 到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；
  • 从索引city取下一个记录的主键id；
  • 重复步骤3、4直到city的值不满足查询条件为止；
  • 对sort_buffer中的数据按照字段name做快速排序；
  • 按照排序结果取前1000行返回给客户端。

整个过程流程图如下：

rowid 排序

在上面这个算法过程里面，只对原表的数据读了一遍，剩下的操作都是在sort_buffer和临时文件中执行的。但这个算法有一个问题，就是如果查询要返回的字段很多的话，那么sort_buffer里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。所以如果单行很大，这个方法效率不够好。
那么，如果 MySQL 认为排序的单行长度太大会怎么做呢？我们通过修改一个参数，让 MySQL 采用另外一种算法。

SET max_length_for_sort_data = 16;

max_length_for_sort_data，是MySQL中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL就认为单行太大，要换一个算法。

新的算法放入sort_buffer的字段，只有要排序的列（即name字段）和主键id。

但这时，排序的结果就因为少了city和age字段的值，不能直接返回了，整个执行流程就变成如下所示的样子：

  • 初始化sort_buffer，确定放入两个字段，即name和id；
  • 从索引city找到第一个满足city=’杭州’条件的主键id；
  • 到主键id索引取出整行，取name、id这两个字段，存入sort_buffer中；
  • 从索引city取下一个记录的主键id；
  • 重复步骤3、4直到不满足city=’杭州’条件为止；
  • 对sort_buffer中的数据按照字段name进行排序；
  • 遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name和age三个字段返回给客户端。

这个过程称为rowid排序，流程如下：

对比全字段排序流程图发现，rowid 排序多访问了一次表 t 的主键索引，就是步骤 7。

需要说明的是，最后的“结果集”是一个逻辑概念，实际上MySQL服务端从排序后的sort_buffer中依次取出id，然后到原表查到city、name和age这三个字段的结果，不需要在服务端再耗费内存存储结果，是直接返回给客户端的。

全字段排序 VS rowid 排序

如果MySQL实在是担心排序内存太小，会影响排序效率，才会采用rowid排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。
如果MySQL认为内存足够大，会优先选择全字段排序，把需要的字段都放到sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。
这也就体现了MySQL的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。
对于InnoDB表来说，rowid排序会要求回表多造成磁盘读，因此不会被优先选择。

并不是所有的orderby语句，都需要排序操作的。从上面分析的执行过程，我们可以看到，MySQL之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。

如果能够保证从 city 这个索引上取出来的行，天然就是按照 name 递增排序的话，就可以不用再排序了,所以，我们可以在这个市民表上创建一个 city 和 name 的联合索引，对应的 SQL 语句是：

1
alter table t add index city_user(city, name);

在这个索引里面，我们依然可以用树搜索的方式定位到第一个满足 city=’杭州’的记录， 并且额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要 city 的值是杭州， name 的值就一定是有序的。这样就不需要临时表，也不需要排序了。

更进一步，假如我们在要查询的city，name，age字段上建立联合索引，那么通过这个覆盖索引就能直接拿到所有数据了，不需要排序也不需要临时表，并且因为走了索引查询会更快，当然索引维护也是成本，需要综合考量。

如果你创建的表没有主键，或者把一个表的主键删掉了，那么 InnoDB 会自 己生成一个长度为 6 字节的 rowid 来作为主键。
这也就是排序模式里面，rowid 名字的来历。实际上它表示的是：每个引擎用来唯一标识数据行的信息。

临时表

上面的排序过程中会用到临时表，临时表有时是完全在内存中建立的，有时需要用到磁盘。

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

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

只查一行数据为什么也慢

查询长时间不返回

一般碰到这种情况的话，大概率是表t被锁住了。接下来分析原因的时候，一般都是首先执行一下showprocesslist命令，看看当前语句处于什么状态。
然后我们再针对每种状态，去分析它们产生的原因、如何复现，以及如何处理。

等MDL锁

如下图所示，就是使用 show processlist 命令查看 Waiting for table metadata lock 的示意图。

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

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

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

等flush

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

1
2
3
flush table t with read lock;
 
flush tables with read lock;

这两个flush语句，如果指定表t的话，代表的是只关闭表t；如果没有指定具体的表名，则表示关闭MySQL里所有打开的表。
但是正常这两个语句执行起来都很快，除非它们也被别的线程堵住了。
所以，出现Waiting for table flush状态的可能情况是：有一个flush tables命令被别的语句堵住了，然后它又堵住了我们的select语句。

等行锁

假设有如下查询语句被阻塞了

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

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

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

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

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

幻读问题

幻读指的是一个事务在 前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行

  • 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因 此，幻读在“当前读”下才会出现。
  • 幻读仅专指“新插入的行”,修改不算。

如何解决幻读

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

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

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

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

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

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

间隙锁加锁规则

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

因为间隙锁在可重复读隔离级别下才有效，所以本篇文章接下来的描述，若没有特殊说明，默认是可重复读隔离级别。

加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。

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

下面通过多个案例来看下间隙锁加锁情况

表内容还是以上面的为例：

1
2
3
4
5
6
7
8
9
10
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 );

案例一：等值查询间隙锁

sessionA sessionB sessionC
begin;update t set d=d+1 where id=7
insert into t values(8,8,8);(blocked)
update t set d=d+1 where id=10;(Query OK)

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

  • 根据原则1，加锁单位是next-keylock，sessionA加锁范围就是(5,10]；
  • 同时根据优化2，这是一个等值查询(id=7)，而id=10不满足查询条件，next-keylock退化成间隙锁，因此最终加锁的范围是(5,10)。
  • 所以，sessionB要往这个间隙里面插入id=8的记录会被锁住，但是sessionC修改id=10这行是可以的。

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

这个案例是关于覆盖索引上的锁

sessionA sessionB sessionC
begin;select id from t where c=5 lock in share mode;
update t set d=d+1 where id=5;(Query OK)
insert into t values(7,7,7);(blocked)

看到这个例子，你是不是有一种“该锁的不锁，不该锁的乱锁”的感觉？我们来分析一下吧。

这里sessionA要给索引c上c=5的这一行加上读锁。

  • 根据原则1，加锁单位是next-key lock，因此会给(0,5]加上next-key lock。
  • 要注意c是普通索引，因此仅访问c=5这一条记录是不能马上停下来的，需要向右遍历，查到c=10才放弃。根据原则2，访问到的都要加锁，因此要给(5,10]加next-key lock。
  • 但是同时这个符合优化2：等值判断，向右遍历，最后一个值不满足c=5这个等值条件，因此退化成间隙锁(5,10)。
  • 根据原则2，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么sessionB的update语句可以执行完成。但sessionC要插入一个(7,7,7)的记录，就会被sessionA的间隙锁(5,10)锁住。
  • 需要注意，在这个例子中，lock in share mode只锁覆盖索引，但是如果是for update就不一样了。执行for update时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。
    这个例子说明，锁是加在索引上的；同时，它给我们的指导是，如果你要用lock in share mode来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。比如，将sessionA的查询语句改成select d from t where c=5 lock in share mode。你可以自己验证一下效果。

案例三：主键索引范围锁

第三个例子是关于范围查询的。

你可以先思考一下这个问题：对于我们这个表t，下面这两条查询语句，加锁范围相同吗？

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

在逻辑上，这两条查语句肯定是等价的，但是它们的加锁规则不太一样

sessionA sessionB sessionC
begin;select * from t where id>=10 and id<11 for update;
insert into t values(8,8,8);(Query OK)
insert into t values(13,13,13);(blocked)
update t set d=d+1 where id=15;(blocked)

现在我们就用前面提到的加锁规则，来分析一下sessionA会加什么锁呢？

  • 开始执行的时候，要找到第一个id=10的行，因此本该是next-keylock(5,10]。根据优化1，主键id上的等值条件，退化成行锁，只加了id=10这一行的行锁。
  • 范围查找就往后继续找，找到id=15这一行停下来，因此需要加next-key lock(10,15]。
  • 所以，sessionA这时候锁的范围就是主键索引上，行锁id=10和next-key lock(10,15]。这样，sessionB和sessionC的结果你就能理解了。
  • 这里你需要注意一点，首次sessionA定位查找id=10的行的时候，是当做等值查询来判断的，而向右扫描到id=15的时候，用的是范围查询判断。

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

与案例三不同的是，案例四中查询语句的 where 部分用的是字段 c。

sessionA sessionB sessionC
begin;select * from t where c>=10 and c<11 for update;
insert into t values(8,8,8);(blocked)
update t set d=d+1 where c=15;(blocked)

这次sessionA用字段c来判断，加锁规则跟案例三唯一的不同是：在第一次用c=10定位记录的时候，索引c上加了(5,10]这个next-key lock后，由于索引c是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终sesionA加的锁是，索引c上的(5,10]和(10,15]这两个next-key lock。
所以从结果上来看，sessonB要插入（8,8,8)的这个insert语句时就被堵住了。
这里需要扫描到c=15才停止扫描，是合理的，因为InnoDB要扫到c=15，才知道不需要继续往后找了。

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

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

sessionA sessionB sessionC
begin;select * from t where id>10 and id<=15 for update;
update t set d=d+1 where id=20;(blocked)
insert into t values(16,16,16);(blocked)

sessionA是一个范围查询，按照原则1的话，应该是索引id上只加(10,15]这个next-key lock，并且因为id是唯一键，所以循环判断到id=15这一行就应该停止了。
但是实现上，InnoDB会往前扫描到第一个不满足条件的行为止，也就是id=20。而且由于这是个范围扫描，因此索引id上的(15,20]这个next-key lock也会被锁上。
所以你看到了，sessionB要更新id=20这一行，是会被锁住的。同样地，sessionC要插入id=16的一行，也会被锁住。
照理说，这里锁住id=20这一行的行为，其实是没有必要的。因为扫描到id=15，就可以确定不用往后再找了。但实现上还是这么做了，因此我认为这是个bug。

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

接下来的例子，是为了更好地说明“间隙”这个概念。这里，我给表 t 插入一条新记录。

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

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

可以看到，虽然有两个c=10，但是它们的主键值id是不同的（分别是10和30），因此这两个c=10的记录之间，也是有间隙的。
图中画出了索引c上的主键id。为了跟间隙锁的开区间形式进行区别，用(c=10,id=30)这样的形式，来表示索引上的一行。
现在，我们来看一下案例六。这次我们用delete语句来验证。注意，delete语句加锁的逻辑，其实跟select…forupdate是类似的，也就是上面总结的两个“原则”、两个“优化”和一个“bug”。

sessionA sessionB sessionC
begin;delete from t where c=10;
insert into t values(12,12,12);(blocked)
update t set d=d+1 where c=15;(Query OK)

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

案例七：limit 语句加锁

案例七对比案例6

sessionA sessionB
begin；delete from t where c=10 limit 2
insert into t values(12,12,12);(Query OK)

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

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

案例八：一个死锁的例子

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

sessionA sessionB
begin;select id from t where c=10 lock in share mode;
update t set d=d+1 where c=10;(blocked)
insert into t values(8,8,8);
ERROR 1213(40001)Deadlock found when trying to get lock;try restarting transaction

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

  • sessionA启动事务后执行查询语句加lock in share mode，在索引c上加了next-key lock(5,10]和间隙锁(10,15)；
  • sessionB的update语句也要在索引c上加next-key lock(5,10]，进入锁等待；
  • 然后sessionA要再插入(8,8,8)这一行，被sessionB的间隙锁锁住。由于出现了死锁，InnoDB让sessionB回滚。
  • 你可能会问，sessionB的next-key lock不是还没申请成功吗？
    其实是这样的，sessionB的“加next-key lock(5,10]”操作，实际上分成了两步，先是加(5,10)的间隙锁，加锁成功；然后加c=10的行锁，这时候才被锁住的。也就是说，我们在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。

思考题

为什么 session B 的 insert 语句会被阻塞。

sessionA sessionB
begin;select * from t where c>=15 and c<=20 order by c desc lock in share mode;
insert into t values(6,6,6);（blocked）
  • 由于是order by c desc，第一个要定位的是索引c上“最右边的”c=20的行，所以会加上间隙锁(20,25)和next-key lock(15,20]。
  • 在索引c上向左遍历，要扫描到c=10才停下来，所以next-keylock会加到(5,10]，这正是阻塞sessionB的insert语句的原因。
  • 在扫描过程中，c=20、c=15、c=10这三行都存在值，由于是select*，所以会在主键id上加三个行锁。
  • 因此，sessionA的select语句锁的范围就是：
    索引c上(5,25)；2.主键索引上id=15、20两个行锁。
    记住，锁就是加在索引上的，这是InnoDB的一个基础设定，需要你在分析问题的时候要一直记得。

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

mysql怎么保证数据不丢

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

binlog写入机制

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

一个事务的binlog是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了binlog cache的保存问题。
系统给binlog cache分配了一片内存，每个线程一个，参数binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。
事务提交的时候，执行器把binlogcache里的完整事务写入到binlog中，并清空binlogcache。状态如下图所示。

可以看到，每个线程有自己binlog cache，但是共用同一份binlog文件。
图中的write，指的就是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快,图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁 盘的 IOPS

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

  • sync_binlog=0的时候，表示每次提交事务都只write，不fsync；
  • 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 可能存在的三种状态。这三种状态，对应的就是下图中的三个颜色块。

这三种状态分别是：

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

日志写到 redo log buffer 是很快的，wirte 到 page cache 也差不多，但是持久化到磁盘的速度就慢多了。
为了控制 redo log 的写入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit参 数，它有三种可能取值：

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

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。
注意，事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些 redo log 也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的 redo log，也 是可能已经持久化到磁盘的。

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

  • 一种是，redo log buffer占用的空间即将达到innodb_log_buffer_size一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是write，而没有调用fsync，也就是只留在了文件系统的page cache。
  • 另一种是，并行的事务提交的时候，顺带将这个事务的redo log buffer持久化到磁盘。假设一个事务A执行到一半，已经写了一些redo log到buffer中，这时候有另外一个线程的事务B提交，如果innodb_flush_log_at_trx_commit设置的是1，那么按照这个参数的逻辑，事务B要把redo log buffer里的日志全部持久化到磁盘。这时候，就会带上事务A在redologbuffer里的日志一起持久化到磁盘。
    这里需要说明的是，我们介绍两阶段提交的时候说过，时序上redolog先prepare，再写binlog，最后再把redolog commit
    如果把innodb_flush_log_at_trx_commit设置成1，那么redolog在prepare阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于prepare的redolog，再加上binlog来恢复的

每秒一次后台轮询刷盘，再加上崩溃恢复这个逻辑，InnoDB就认为redolog在commit的时候就不需要fsync了，只会write到文件系统的page cache中就够了。
通常我们说MySQL的“双1”配置，指的就是sync_binlog和innodb_flush_log_at_trx_commit都设置成1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是redolog（prepare阶段），一次是binlog。

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

解释这个问题，就要用到组提交group commit机制了。
这里，需要先介绍日志逻辑序列号log sequence number，LSN）的概念。LSN是单调递增的，用来对应redolog的一个个写入点。每次写入长度为length的redolog，LSN的值就会加上length。
LSN也会写到InnoDB的数据页中，来确保数据页不会被多次执行重复的redolog。
如下图所示，是三个并发事务(trx1,trx2,trx3)在prepare阶段，都写完redolog buffer，持久化到磁盘的过程，对应的LSN分别是50、120和160。

从图中可以看到，

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

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

为了让一次 fsync 带的组员更多，MySQL 有一个很有趣的优化：拖时间。之前介绍的两阶段提交，其实真实执行顺序如下图：

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

不过通常情况下第3步执行得会很快，所以binlog的write和fsync间的间隔时间短，导致能集合到一起持久化的binlog比较少，因此binlog的组提交的效果通常不如redolog的效果那么好。
如果你想提升binlog组提交的效果，可以通过设置binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count来实现。

  • binlog_group_commit_sync_delay参数，表示延迟多少微秒后才调用fsync;
  • 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 上，可以通过哪些方法来提升性能呢

可以考虑以下三种方法：

  • 设置binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count参数，减少binlog的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。

  • 将sync_binlog设置为大于1的值（比较常见是100~1000）。这样做的风险是，主机掉电时会丢binlog日志。

  • 将innodb_flush_log_at_trx_commit设置为2。这样做的风险是，主机掉电的时候会丢数据。

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

思考题

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

  • 业务高峰期。一般如果有预知的高峰期，DBA 会有预案，把主库设置成“非双 1”。
  • 备库延迟，为了让备库尽快赶上主库
  • 用备份恢复主库的副本，应用 binlog 的过程，这个跟上一种场景类似。
  • 批量导入数据的时候。

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

mysql怎么保证主备一致的

先看一个基本的主从架构示意图：

主从架构示意图
主从架构示意图

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

下图展示一个update语句执行后，在主库A和从库B之间数据同步的一个完整流程图：

可以看到：主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写binlog。
备库B跟主库A之间维持了一个长连接。主库A内部有一个线程，专门用于服务备库B的这个长连接。一个事务日志同步的完整过程是这样的：

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

这里需要说明，后来由于多线程复制方案的引入，sql_thread演化成为了多个线程

binlog 里面到底是什么内容

binlog有两种格式，一种是statement，一种是row。第三种格式，叫作mixed，其实它就是前两种格式的混合。

假设有表如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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' );

对该表执行如下sql：

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

binlog_format=statement下的日志

当 binlog_format=statement 时，binlog 里面记录的就是 SQL 语句的原文。可以用如下命令查看

1
show binlog events in 'master.000001';

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

但是在当前binlog格式下，该条语句可能出现主备不一致的问题：

当前binlog设置的是statement格式，并且语句中有limit，所以这个命令可能是unsafe的,很可能会出现主备数据不一致的情况。比如上面这个例子：

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

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

binlog_format=row下的日志

如果把 binlog 的格式改为 binlog_format=‘row’,binlog内容如下：

图1
图1

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

Table_map event，用于说明接下来要操作的表是 test 库的表 t;

Delete_rows event，用于定义删除的行为

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

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

得到结果如下：

图2
图2

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

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

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。这么做的理由有很多，一个可以直接看出来的好处是方便恢复数据。

通过图2你可以看出来，即使执行的是delete语句，row格式的binlog也会把被删掉的行的整行信息保存起来。所以，如果你在执行完一条delete语句以后，发现删错数据了，可以直接把binlog中记录的delete语句转成insert，把被错删的数据插入回去就可以恢复了。

如果你是执行错了insert语句呢？那就更直接了。row格式下，insert语句的binlog里会记录所有的字段信息，这些信息可以用来精确定位刚刚被插入的那一行。这时，你直接把insert语句转成delete语句，删除掉这被误插入的一行数据就可以了。

如果执行的是update语句的话，binlog里面会记录修改前整行的数据和修改后的整行数据。所以，如果你误执行了update语句的话，只需要把这个event前后的两行信息对调一下，再去数据库里面执行，就能恢复这个更新操作了。

循环复制问题

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

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

互为主备架构
互为主备架构

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

双M结构还有一个问题需要解决。
业务逻辑在节点A上更新了一条语句，然后再把生成的binlog发给节点B，节点B执行完这条更新语句后也会生成binlog。（我建议你把参数log_slave_updates设置为on，表示备库执行relaylog后生成binlog）。
那么，如果节点A同时是节点B的备库，相当于又把节点B新生成的binlog拿过来执行了一次，然后节点A和B间，会不断地循环执行这个更新语句，也就是循环复制了。这个要怎么解决呢？

从上面的图2中可以看到，MySQL在binlog中记录了这个命令第一次执行时所在实例的server id。因此，我们可以用下面的逻辑，来解决两个节点间的循环复制的问题：

  • 规定两个库的server id必须不同，如果相同，则它们之间不能设定为主备关系；
  • 一个备库接到binlog并在重放的过程中，生成与原binlog的server id相同的新的binlog；
  • 每个库在收到从自己的主库发过来的日志后，先判断server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。
    按照这个逻辑，如果我们设置了双M结构，日志的执行流就会变成这样：
    • 从节点A更新的事务，binlog里面记的都是A的server id；
    • 传到节点B执行一次以后，节点B生成的binlog的server id也是A的serverid；
    • 再传回给节点A，A判断到这个server id与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了。

但是，这个机制其实并不完备，在某些场景下，还是有可能出现死循环。

一种场景是，在一个主库更新事务后，用命令set global server_id=x修改了server_id。等日志再传回来的时候，发现server_id跟自己的server_id不同，就只能执行了。
另一种场景是，有三个节点的时候，如下图所示，trx1是在节点B执行的，因此binlog上的server_id就是B，binlog传给节点A，然后A和A’搭建了双M结构，就会出现循环复制。

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

如果出现了循环复制，可以在 A 或者 A’上，执行如下命令：

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

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

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