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

最新推荐文章于 2024-03-26 20:04:55 发布

肥肥技术宅

最新推荐文章于 2024-03-26 20:04:55 发布

阅读量439

点赞数

分类专栏： java 文章标签：数据库 mysql java

本文链接：https://blog.csdn.net/m0_71777195/article/details/129296003

版权

java 专栏收录该内容

3486 篇文章 107 订阅

订阅专栏

区别于存储引擎，来看看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。

img

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

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

主从复制

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

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+λNM；

2、内存判断N*M次。

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

综上所述，应该让小表当驱动表。在N+λNM这个式子里，λ才是影响扫描行数的关键因素，这个值越小越好。

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，磁道的寻址是个很大的开销。

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 的查询过程会变成这样：