MySQL之Server层的“Buffer”和“Cache”

前面的『MySQL各种“Buffer”』系列文章我们介绍了InnoDB存储引擎的一些内存、内存+磁盘的结构以及工作原理，今天我们就来看一下关于MySQL Server层的一些内存结构。

参考资料：

• 丁奇-极客时间APP-课程《MySQL实战45讲》

Binlog Cache

和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。

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

• 图中的Write，指的就是指把日志写入到文件系统的FS Page Cache，并没有把数据持久化到磁盘，所以速度比较快。

• 图中的Fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为Fsync才占磁盘的IOPS。

SHOW VARIABLES LIKE '%binlog%';

参数有很多，我们还是拿重要的参数进行说明：

• 参数：binlog_cache_size

• 参数：sync_binlog

• 策略一：最佳性能（sync_binlog=0）

• 策略二：强一致（sync_binlog=1）

• 策略三：人为折衷控制（sync_binlog=N）

因此，在出现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的数据是最有保障的。

Query Cache

还记得丁奇大佬的这张图吗？本小节要介绍的Query Cache就在这张图中，已经用红框标注出来了，中文名：查询缓存。

MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到key，那么这个value就会被直接返回给客户端。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

好在MySQL也提供了这种“按需使用”的方式。你可以将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用SQL_CACHE显式指定，像下面这个语句一样：

SELECT SQL_CACHE * FROM table WHERE id=10;

需要注意的是，MySQL 8.0版本直接将查询缓存的整块功能删掉了，也就是说8.0开始彻底没有这个功能了。这是为何？因为这个功能有点鸡肋。为什么鸡肋？因为任何一个DML操作，查询缓存都会失效，并且：

SELECT * FROM table WHERE id=10;select * from table where id=10;

这两个语句都不会使用同一个查询缓存，只有字符串相等查询SQL才使用相同缓存。还是顺便提一下相关参数，已经被MySQL官方放弃的功能就不一一介绍了，感兴趣的童鞋可以查阅相关资料。

SHOW VARIABLES LIKE '%query_cache%';

Key Buffer

Key Buffer是缓冲MyISAM表的索引块的，注意：只缓存MyISAM存储引擎的表索引块，即该参数只对MyISAM表生效。同时，这个内存区域是所有线程共享的。

SHOW VARIABLES LIKE '%key_buffer%';

• 参数：key_buffer_size

Read Buffer & Read Rnd Buffer

每个对MyISAM表执行顺序扫描的线程为其扫描的每个表分配的缓冲区。也用于所有存储引擎的上下文：

• 用于在临时文件（不是临时表）中缓存索引，当为ORDER BY；

• 用于批量插入分区；

• 用于缓存嵌套查询的结果；

• 也以另一种特定于存储引擎的方式使用：确定MEMORY
  表的内存块大小。

SHOW VARIABLES LIKE '%read_buffer%';

• 参数：read_buffer_size

用于从MyISAM表中读取、任何存储引擎用于Multi-Range Read（MRR）优化的缓冲区。在MyISAM键排序操作后按排序顺序从表中读取行时，通过此缓冲区读取行以避免磁盘寻道。这是为每个客户端分配的缓冲区，不是全局变量。

SHOW VARIABLES LIKE '%read_rnd_buffer%';

• 参数：read_rnd_buffer_size

Multi-Range Read（MRR）是关于排序优化中的相关技术术语，不在我们本文讨论范围之内，后期的优化文章会详细讲解这些优化技术，拭目以待。

Sort Buffer

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

SHOW VARIABLES LIKE 'sort_buffer_size';

• 参数：sort_buffer_size

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

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

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

Join Buffer

之前表联结的章节提到了笛卡尔积（两张表做联结查询的时候，第一个表有M行数据，第二个表有N行数据，第二张表的每行数据要与第一张表的每行数据一一匹配，得到的结果集行数为M×N）的概念。MySQL针对笛卡尔积的联结查询，做了优化，这里我们称M表为驱动表，N表为被驱动表（驱动表和被驱动表不在本章讨论范围，后续的文章会单独讲解，大家先知道这个概念就好），MySQL提出了一个Join Buffer的概念，Join Buffer就是执行连接连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个Join Buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和Join Buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价。

SHOW VARIABLES LIKE '%join_buffer%';

• 参数：join_buffer_size

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

注意，这里的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足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。

小结

end