Mysql Server中的各种buffer你了解么？

Love The Way You Lie 2023-09-24 21:24 53阅读 0赞

区别于存储引擎，来看看server层有哪些特性在里面。

## binlog ##

Binlog（二进制日志）包含描述数据库更改的“事件”，例如表创建操作或表数据的更改。它还包含可能进行更改的语句的事件，除非使用基于Row的日志记录。二进制日志还包含有关每个语句花费多长时间更新数据的信息。二进制日志有两个重要目的：

**1、复制，复制源服务器上的二进制日志提供了要发送到副本的数据更改记录。源将其二进制日志中包含的事件发送到其副本，副本执行这些事件以进行与源相同的数据更改。**

**2、备份+恢复 常用的解决方案是我们会备份某个时间点的全量数据，然后备份该时间点之后的增量binlog，当需要恢复到某时刻的数据时，可以采用备份+对应时间范围的binlog来恢复。**

**和InnoDB存储引擎的特有日志Redo Log一样，Binlog是MySQL Server层特有的日志，存放着对数据库操作的变更记录**。二者的不同点在于，Redo Log是物理日志，而Binlog是逻辑日志，因为是逻辑日志（记录着除SELECT、SHOW之外的SQL语句）的原因，所以单独的Binlog是不具备Crash-safe能力的，整个MySQL体系结构中，Redo Log和Binlog二者相结合才能保证关系型数据库ACID的特性。

### 写入机制 ###

和Redo Log一样，Binlog自身也有其写入策略。其实，Binlog的写入逻辑比较简单：**事务执行过程中，先把日志写到Binlog Cache，事务提交的时候，再把Binlog Cache写到binlog文件中**。

**一个事务的binlog是不能被拆开的，因此不论这个事务多大，也要确保一次性写入（因为Binlog写入的单位是Events，一个Events必须包含一个完整的事务，所以这也是大事务造成主从延迟的主要原因）。这就涉及到了Binlog Cache的保存问题**。

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

事务提交的时候，执行器把Binlog Cache里的完整事务写入到binlog中，并清空Binlog Cache。

![11bdb8b305fba88b2390d98b0bbf0cd5.png][]

img

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

*  **图中的Write，指的就是指把日志写入到文件系统的FS Page Cache，并没有把数据持久化到磁盘，所以速度比较快。**
 *  **图中的Fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为Fsync才占磁盘的IOPS。**

### 相关参数 ###

binlog\_cache\_size

设置Binlog Cache的大小，默认大小为8MB，单位：B（字节）。

sync\_binlog

介绍：控制binlog文件的刷盘策略，可选的参数为0、1或N。如下图所示：

![f91717ba5b80621378dba3e4edcb92d4.png][]

img

*  **策略一：最佳性能（sync\_binlog=0）**

**处理过程**：表示每次提交事务都只Write，不主动Fsync；

*  **策略二：强一致（sync\_binlog=1）**

**处理过程**：表示每次提交事务都会执行Write和Fsync；

*  **策略三：人为折衷控制（sync\_binlog=N）**

**处理过程**：当sync\_binlog=N（N>1）的时候，表示每次提交事务都Write，但累积N个事务后才Fsync。

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

但是，**将sync\_binlog设置为N，对应的风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志**。

经常听到：MySQL的双“1”设置。其实双“1”就代表：innodb\_flush\_log\_at\_trx\_commit=1 && sync\_binlog=1，因为在这种组合配置下，MySQL的数据是最有保障的。

binlog\_format

binlog 有三种格式：

`Statement`（Statement-Based Replication,SBR）：每一条会修改数据的 SQL 都会记录在 binlog 中。

`Row`（Row-Based Replication,RBR）：不记录 SQL 语句上下文信息，仅保存哪条记录被修改。

`Mixed`（Mixed-Based Replication,MBR）：Statement 和 Row 的混合体。

**statement：**

Statement 模式只记录执行的 SQL，不需要记录每一行数据的变化，因此极大的减少了 binlog 的日志量，避免了大量的 IO 操作，提升了系统的性能。

但是，正是由于 Statement 模式只记录 SQL，而如果一些 SQL 中 包含了函数，那么可能会出现执行结果不一致的情况。比如说 **uuid**() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就得到另外一个结果了。

**所以使用 Statement 格式会出现一些数据一致性问题**。

**row：**

从 MySQL5.1.5 版本开始，binlog 引入了 Row 格式，Row 格式不记录 SQL 语句上下文相关信息，仅仅只需要记录某一条记录被修改成什么样子了。

Row 格式的日志内容会非常清楚地记录下**每一行数据修改的细节**，这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。

不过 Row 格式也有一个很大的问题，那就是**日志量太大了**，特别是批量 update、整表 delete、alter 表等操作，由于要记录每一行数据的变化，此时会产生大量的日志，大量的日志也会带来 IO 性能问题。

一般为了主从的正确性，生产都使用row格式

### 主从复制 ###

目前业界mysql的主从复制主要是基于binlog的逻辑sql实现，他的原理如下图：

![8beb0b987495acb4dbba05b0b440d035.jpeg][]

img

通过上图我们知道复制主要涉及到3个线程：**Binlog Dump线程跑在主库上，I/0线程和 SQL线程跑在从库上**。当在从库上启动复制时，首先创建I/0程连接主库，主库随后创建 Binlog Dump线程读取数据库事件并发送给 I/0线程， I/0线程获取到事件数据后更新到从库的中继日志 Relay Log中去，之后从库上的 SQL线程读取中继日志RelayLog中更新的数据库事件并应用。

目前mysql主要有异步和半同步两种复制方式。

*  `异步复制`

通过主从复制的实现原理，主从可以知道slave是主动发起是读maser的binlog，也就是slave节点apply master节点的binlog是必然延迟的，而且连接master节点的等待从节点read或者apply binlog之后才会告知应用，所以这种mysql的主从是异步方式，这也就是mysql目前默认的实现方式。所以这种异步方式天然从库会有落后主库。

*  `半同步复制`

Mysql在5.5版本后推出了半同步复制的实现方式。所谓半同步复制是在一主一从，一主多从情况下，Master节点**只要确认至少有一个slave接受到了事务**，即可向发起请求的客户端返回执行成功的操作，master节点是不需要等待slave节点成功执行完这个事务。slave节点接受到这个事务，并成功写入到本地relay日志中，就算是成功了。

相比异步复制，半同步复制提高了数据完整性，因为很明确知道，在一个事务提交成功之后，这个事务就至少会存在于两个地方。即在master的dumper线程通知slave后，增加了一个ack（消息确认），即是否成功收到的标志码，也就是dumper线程除了发送到slave，还承担了接收slave的ack工作。如果出现异常，没有收到ack，那么将自动降级为普通的复制，直到异常修复后又会自动变为半同步复制。

## Query Cache ##

就是server层的查询缓存，不过一般我们都不用。任意一个修改都有可能导致缓存时效，效率提升并不高

## Sort Buffer ##

### Sort Buffer是什么 ###

MySQL会给每个线程分配一块内存用于排序，即SQL语句中的ORDER BY，这块内存区域就称之为Sort Buffer。

### Sort Buffer相关参数 ###

*  参数：`sort_buffer_size`

介绍：定义Sort Buffer缓冲区大小的参数。

### 常见的排序算法 ###

全字段排序

SELECT列表中的全部字段都参与排序的排序方式叫做全字段排序。

归并排序

如果要排序的数据量小于sort\_buffer\_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

OPTIMIZER\_TRACE的结果可以从number\_of\_tmp\_files中看到是否使用了临时文件。（内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。数字表示，MySQL将需要排序的数据分成12份，每一份单独排序后存在这些临时文件中。然后把这12个有序文件再合并成一个有序的大文件。0表示排序可以直接在内存中完成，sort\_buffer\_size 越小，需要分成的份数越多，number\_of\_tmp\_files的值就越大）

rowid排序

MySQL认为排序的单行长度太大，采用另外一种算法，max\_length\_for\_sort\_data，是MySQL中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL就认为单行太大，要换一个算法。（排序的列只包含order by后面的字段和id）

## Join Buffer ##

### Join Buffer是什么 ###

MySQL提出了一个Join Buffer的概念，Join Buffer就是执行连接连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个Join Buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和Join Buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以**显著减少被驱动表的I/O代价**。

### Join Buffer相关参数 ###

*  参数：`join_buffer_size`

介绍：用于普通索引扫描、范围索引扫描和不使用索引并因此执行全表扫描的连接的缓冲区的最小大小（单位：B字节），默认大小为262144字节（也就是256KB），最小可以设置为128字节。

### 小提示 ###

如果Join Buffer放不下所有数据的话，策略很简单，就是`**分段**`放。假设，驱动表的数据行数是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 Buffer足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。

## MRR ##

MRR，全称「Multi-Range Read Optimization」。

简单说：**MRR 通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能。**

select * from stu where age between 10 and 20;

执行这么一条语句的时候，我们会在二级索引age上查询一条符合条件的，然后根据id回表，再去找下一条符合条件的。如果age对应的每个id都是非常分散的，那么每次回表，都需要一次随机的IO，磁道的寻址是个很大的开销。

![a8b04ae40d4964ee06bf8a80d4a99116.png][]

img

要如何将随机IO转为顺序IO呢？

mysql > set optimizer_switch='mrr=on';
    Query OK, 0 rows affected (0.06 sec)
    
    mysql > explain select * from stu where age between 10 and 20;
    +----+-------------+-------+-------+------+---------+------+------+----------------+
    | id | select_type | table | type  | key  | key_len | ref  | rows | Extra          |
    +----+-------------+-------+-------+------+---------+------+------+----------------+
    |  1 | SIMPLE      | tbl   | range | age  |    5    | NULL |  960 | ...; Using MRR |
    +----+-------------+-------+-------+------+---------+------+------+----------------+

我们开启了 MRR，重新执行 sql 语句，发现 Extra 里多了一个「Using MRR」。

这下 MySQL 的查询过程会变成这样：

![a07843c2cecb049800f09000c791b40a.png][]

img

**对于 Myisam，在去磁盘获取完整数据之前，会先按照 rowid 排好序，再去顺序的读取磁盘。**

**对于 Innodb，则会按照聚簇索引键值排好序，再顺序的读取聚簇索引。**

顺序读带来了几个好处：

**1、磁盘和磁头不再需要来回做机械运动；**

**2、可以充分利用磁盘预读**

比如在客户端请求一页的数据时，可以把后面几页的数据也一起返回，放到数据缓冲池中，这样如果下次刚好需要下一页的数据，就不再需要到磁盘读取。这样做的理论依据是计算机科学中著名的局部性原理：

*当一个数据被用到时，其附近的数据也通常会马上被使用。*

**3、在一次查询中，每一页的数据只会从磁盘读取一次**

MySQL 从磁盘读取页的数据后，会把数据放到数据缓冲池，下次如果还用到这个页，就不需要去磁盘读取，直接从内存读。

但是如果不排序，可能你在读取了第 1 页的数据后，会去读取第2、3、4页数据，接着你又要去读取第 1 页的数据，这时你发现第 1 页的数据，**已经从缓存中被剔除了**，于是又得再去磁盘读取第 1 页的数据。

而转化为顺序读后，你会连续的使用第 1 页的数据，这时候按照 MySQL 的缓存剔除机制，这一页的缓存是不会失效的，直到你利用完这一页的数据，由于是顺序读，在这次查询的余下过程中，你确信不会再用到这一页的数据，可以和这一页数据说告辞了。

**顺序读就是通过这三个方面，最大的优化了索引的读取。**

**别忘了，索引本身就是为了减少磁盘 IO，加快查询，而 MRR，则是把索引减少磁盘 IO 的作用，进一步放大。**

[11bdb8b305fba88b2390d98b0bbf0cd5.png]: https://img-blog.csdnimg.cn/img_convert/11bdb8b305fba88b2390d98b0bbf0cd5.png
[f91717ba5b80621378dba3e4edcb92d4.png]: https://img-blog.csdnimg.cn/img_convert/f91717ba5b80621378dba3e4edcb92d4.png
[8beb0b987495acb4dbba05b0b440d035.jpeg]: https://img-blog.csdnimg.cn/img_convert/8beb0b987495acb4dbba05b0b440d035.jpeg
[a8b04ae40d4964ee06bf8a80d4a99116.png]: https://img-blog.csdnimg.cn/img_convert/a8b04ae40d4964ee06bf8a80d4a99116.png
[a07843c2cecb049800f09000c791b40a.png]: https://img-blog.csdnimg.cn/img_convert/a07843c2cecb049800f09000c791b40a.png