本文描述基于MySQL 8.0.30
[hhu] 23:49:36> show variables like '%cache_size%';
+----------------------------+-----------+
| Variable_name | Value |
+----------------------------+-----------+
| binlog_cache_size | 4096 |
| binlog_stmt_cache_size | 4096 |
| host_cache_size | 279 |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_total_cache_size | 640000000 |
| max_binlog_cache_size | 4096 |
| max_binlog_stmt_cache_size | 4096 |
| thread_cache_size | 9 |
+----------------------------+-----------+
8 rows in set (0.00 sec)
本文主要关注如下几个参数：
binlog_cache_size
binlog_stmt_cache_size
max_binlog_cache_size
max_binlog_stmt_cache_size
其中，binlog_cache_size和max_binlog_cache_size是一组，binlog_stmt_cache_size和max_binlog_stmt_cache_size是一组。
先看看官方文档直接搜索参数的解释
binlog_cache_size：The size of the memory buffer to hold changes to the binary log during a transaction.binlog_cache_size sets the size for the transaction cache only.
max_binlog_cache_size：If a transaction requires more than this many bytes of memory, the server generates a Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage error.
binlog_stmt_cache_size：The size of the memory buffer for the binary log to hold nontransactional statements issued during a transaction. the size of the statement cache is governed by the binlog_stmt_cache_size system variable.
max_binlog_stmt_cache_size：If nontransactional statements within a transaction require more than this many bytes of memory, the server generates an error.max_binlog_stmt_cache_size sets the size for the statement cache only; the upper limit for the transaction cache is governed exclusively by the max_binlog_cache_size system variable.
这个解释看着让人非常迷惑，从官方文档另一处，有相对清晰的描述
5.4.4 The Binary Log 里有如下描述：
When a thread that handles the transaction starts, it allocates a buffer of binlog_cache_size to buffer statements. If a statement is bigger than this, the thread opens a temporary file to store the transaction. The temporary file is deleted when the thread ends. From MySQL 8.0.17, if binary log encryption is active on the server, the temporary file is encrypted.
The Binlog_cache_usestatus variable shows the number of transactions that used this buffer (and possibly a temporary file) for storing statements. The Binlog_cache_disk_use status variable shows how many of those transactions actually had to use a temporary file. These two variables can be used for tuning binlog_cache_size to a large enough value that avoids the use of temporary files.
The max_binlog_cache_sizesystem variable (default 4GB, which is also the maximum) can be used to restrict the total size used to cache a multiple-statement transaction. If a transaction is larger than this many bytes, it fails and rolls back. The minimum value is 4096.
当一个事务开始时，MySQL会分配一块叫binlog_cache_size的内存用来缓存语句对应二进制日志（binlog），binlog_cache_size定义了事务缓存对应binlog能用的最大内存，超过这个值，就会使用磁盘的临时文件。值得注意的是，这块缓存是每个session都会分配一块。
max_binlog_cache_size定义了一个事务能使用的缓存上限，超过这个值，这个事务会直接失败。
来做个实验验证下，先做个环境准备
[hr] 21:09:14> select @@binlog_cache_size,@@max_binlog_cache_size,@@binlog_format;
+---------------------+-------------------------+-----------------+
| @@binlog_cache_size | @@max_binlog_cache_size | @@binlog_format |
+---------------------+-------------------------+-----------------+
| 32768 | 65536 | ROW |
+---------------------+-------------------------+-----------------+
1 row in set (0.00 sec)
[hr] 21:09:22> show create table t1;
+-------+---------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`a` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[hr] 21:09:36> select version();
+-----------+
| version() |
+-----------+
| 8.0.30 |
+-----------+
1 row in set (0.00 sec)
1、当事务大小小于@@binlog_cache_size
实验结果：1、事务能执行成功 2、使用到了内存，没有使用磁盘 3、Binlog_cache_use无法反映同一个表上的不同事务，只对不同表的事务进行计数（不一定对，可能哪里需要进一步操作）
[hr] 21:11:50> flush status\G
Query OK, 0 rows affected (0.00 sec)
[hr] 21:11:56> begin;
Query OK, 0 rows affected (0.00 sec)
[hr] 21:12:09> insert into t1 values (repeat('a',20000));
Query OK, 1 row affected (0.00 sec)
[hr] 21:14:46> insert into t1 values (repeat('a',10000));
Query OK, 1 row affected (0.00 sec)
[hr] 21:14:58> commit;
Query OK, 0 rows affected (0.00 sec)
[hr] 21:15:06> show status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 1 |
+-----------------------+-------+
2 rows in set (0.01 sec)
**Binlog_cache_disk_use和Binlog_cache_use的数值代表使用内存/磁盘来缓存binlog cache的事务个数。
Binlog_cache_disk_use为0，说明当前事务没有用到磁盘临时文件，Binlog_cache_use为1，说明当前事务用到了内存**
**新开一个session，然后启动一个新事务，在同样表t1上操作，这个时候发现Binlog_cache_disk_use和Binlog_cache_use的数值均没有变化**
[(none)] 21:16:23> use hr
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
[hr] 21:16:25> begin;
Query OK, 0 rows affected (0.00 sec)
[hr] 21:18:34> insert into t1 values (repeat('b',20000));
Query OK, 1 row affected (0.00 sec)
[hr] 21:19:09> show status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 1 |
+-----------------------+-------+
2 rows in set (0.00 sec)
**新建一张表t，在表t上开启一个事务，Binlog_cache_use会+1，变为2**
[hr] 21:30:40> create table t as select * from t1 where 1=2;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
[hr] 21:30:42> begin;
Query OK, 0 rows affected (0.00 sec)
[hr] 21:30:49> show create table t;
+-------+--------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`a` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
[hr] 21:30:57> insert into t values (repeat('c',20000));
Query OK, 1 row affected (0.00 sec)
[hr] 21:31:17> show status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 2 |
+-----------------------+-------+
2 rows in set (0.00 sec)
**同样在表t上开启另一个事务，Binlog_cache_use仍为2**
[hr] 21:31:45> begin;
Query OK, 0 rows affected (0.00 sec)
[hr] 21:31:50> insert into t values(repeat('d',20000));
Query OK, 1 row affected (0.00 sec)
[hr] 21:32:13> show status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 2 |
+-----------------------+-------+
2 rows in set (0.00 sec)
**再新建表t2，再次实验，开启两个session并分别开启一个事务，发现Binlog_cache_use只会增加1**
session 1
[hr] 21:33:07> show create table t2;
+-------+---------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`a` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[hr] 21:33:15> begin;
Query OK, 0 rows affected (0.00 sec)
[hr] 21:33:46> insert into t2 values(repeat('f',20000));
Query OK, 1 row affected (0.00 sec)
[hr] 21:33:53> show status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 3 |
+-----------------------+-------+
2 rows in set (0.00 sec)
---------------------------------------------------------------
session 2
[hr] 21:35:23> show status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 3 |
+-----------------------+-------+
2 rows in set (0.00 sec)
[hr] 21:35:55> begin;
Query OK, 0 rows affected (0.00 sec)
[hr] 21:36:27> insert into t2 values(repeat('g',20000));
Query OK, 1 row affected (0.00 sec)
[hr] 21:36:34> show status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 3 |
+-----------------------+-------+
2 rows in set (0.00 sec)
2、当事务大小大于@@binlog_cache_size，且小于@@max_binlog_cache_size 实验结果：1、事务会同时用到内存和磁盘
[hr] 22:22:34> flush status\G
Query OK, 0 rows affected (0.00 sec)
[hr] 22:25:18> show status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec)
[hr] 22:25:20> begin;
Query OK, 0 rows affected (0.00 sec)
[hr] 22:25:52> insert into t1 values (repeat('a',20000));
Query OK, 1 row affected (0.00 sec)
[hr] 22:26:16> insert into t1 values (repeat('a',20000));
Query OK, 1 row affected (0.00 sec)
[hr] 22:27:04> commit;
Query OK, 0 rows affected (0.00 sec)
[hr] 22:27:14> show status like 'Binlog_cache%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 1 |
| Binlog_cache_use | 1 |
+-----------------------+-------+
2 rows in set (0.00 sec)
**Binlog_cache_disk_use和Binlog_cache_use都从0变为1**
3、当事务大小超过@@max_binlog_cache_size 实验结果：1、事务会报错（但不会主动提交或者回滚）
[hr] 22:27:22> flush status;
Query OK, 0 rows affected (0.01 sec)
[hr] 22:29:29> begin;
Query OK, 0 rows affected (0.00 sec)
[hr] 22:29:38> insert into t1 values (repeat('a',20000));
Query OK, 1 row affected (0.00 sec)
[hr] 22:29:53> insert into t1 values (repeat('a',20000));
Query OK, 1 row affected (0.01 sec)
[hr] 22:29:54> insert into t1 values (repeat('a',20000));
Query OK, 1 row affected (0.00 sec)
[hr] 22:29:56> insert into t1 values (repeat('a',20000));
ERROR 1197 (HY000): Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again
**这里报错了，但是这个事务并没有结束，感兴趣的同学可以验证下**
总结：
如果数据库里的事务很大，但连接并不多，可以考虑增加@@binlog_cache_size来避免额外的磁盘写；
如果数据库有大量并发连接，应该注意大量连接同时申请大量内存的问题；
如果想控制事务，不让事务过大，可以限制@@max_binlog_cache_size大小；
@@binlog_stmt_cache_size 和 @@max_binlog_stmt_cache_size有着类似的机制，只不过这两者是适用于非事务引擎（如MyISAM）。
个人水平有限，如果疏漏错误，欢迎批评指出，谢谢
