InnoDB下的内存分析和优化计算

MySQL的内存消耗，一般来说包含两种内存。

- global级共享内存
- session级私有内存

# 1. global级共享内存
包括的内容如下图所示

![](/media/202202/2022-02-21_154252_715316.png)

执行如下命令，即可查询示例的共享内存分配情况：
```sql
show variables where variable_name in (
'innodb_buffer_pool_size','innodb_log_buffer_size', \
'innodb_additional_mem_pool_size','query_cache_size');
```
以下是一个mysql的输出结果 
```sql
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| innodb_additional_mem_pool_size | 8388608   |
| innodb_buffer_pool_size         | 524288000 |
| innodb_log_buffer_size          | 67108864  |
| query_cache_size                | 0         |
+---------------------------------+-----------+
```

`innodb_buffer_pool_size`: 这是 Innodb 引擎最重要的缓存，也是提升查询性能的重要手段。一般是global共享内存中占用最大的部分。

在进行 SQL 读和写的操作时，首先并不是对物理数据文件操作，而是先对 buffer_pool 进行操作，然后再通过 checkpoint 等机制写回数据文件。

占用的内存启动后就不会自动释放，默认通过LRU的算法镜像缓存淘汰，每次的新数据页，都会插入buffer pool的中间，防止前面的热数据被冲掉，长时间没动静的冷数据，会被淘汰出buffer pool，但是是被其它新数据占用了，所以一般这里不会释放的，除非重启（5.7 开始支持动态调整，默认以128M的chunk单位分配内存块）。

`innodb_buffer_pool`主要包含数据页、索引页、undo 页、insert buffer、自适应哈希索引、锁信息以及数据字典等信息。

`innodb_log_buffer_size` : 对应图中redo log_buffer
缓存 redo log 的信息，大小可以配置。redo log 会先写在这里，然后依照一定频率刷新回redo log文件中。

`innodb_additional_mem_pool_size` : 存放 InnoDB 内的一些数据结构，一般在 buffer_pool 中申请内存的时候，还需要在此空间申请存储该对象的结构信息。该大小主要与表数量有关，表数量越大需要更大的空间。

`query_cache_size` : 该部分是对查询结果做缓存以减少解析 SQL 和执行 SQL 的花销，主要适合于读多写少的应用场景，因为它是按照 SQL 语句的 hash 值进行缓存的，当表数据发生变化后即失效。

# 2. session级私有内存
session级私有内存，主要是数据库连接私有内存使用，查询命令如下：
```sql
show variables where variable_name in (
'tmp_table_size','sort_buffer_size','read_buffer_size', \
'read_rnd_buffer_size','join_buffer_size','thread_stack', \
'binlog_cache_size');
```
查询结果如下：
```sql
+----------------------+-----------+
| Variable_name        | Value     |
+----------------------+-----------+
| binlog_cache_size    | 32768     |
| join_buffer_size     | 262144    |
| read_buffer_size     | 262144    |
| read_rnd_buffer_size | 524288    |
| sort_buffer_size     | 524288    |
| thread_stack         | 524288    |
| tmp_table_size       | 209715200 |
+----------------------+-----------+
```
`tmp_table_size` : 是MySQL的heap（堆积）表缓冲大小，表示内存中临时表的最大值。如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下

`sort_buffer_size` : 执行ORDER BY和GROUP BY排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小。若存储量大于 sort_buffer_size，则会在磁盘生成临时表以完成操作。在 Linux 系统中，当分配空间大于 2 M 时会使用 mmap() 而不是 malloc() 来进行内存分配，导致效率降低。

`read_buffer_size` ： 顺序读缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。

`read_rnd_buffer_size` ：随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

`join_buffer_size`： 每次join操作都会调用my_malloc、my_free函数申请/释放join_buffer_size的大小的内存。

`thread_stack` ： 每个session连接线程被创建时，MySQL给它分配的内存大小。当MySQL创建一个新的连接线程时，需要给它分配一定大小的内存堆栈空间，以便存放客户端的请求的Query及自身的各种状态和处理信息。
查看连接线程相关的系统变量的设置值： show variables like 'thread%';

`binlog_cache_size` ： 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存。表示的是binlog 能够使用的最大cache 内存大小。在一个事务还没有 commit 之前会先将其日志存储于 binlog_cache 中，等到事务 commit 后会将其 binlog 刷回磁盘上的 binlog 文件以持久化。当我们执行多语句事务的时候 所有session的使用的内存超过max_binlog_cache_size的值时就会报错：“Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage”

# 3. 理论内存消耗计算方式
这里先不考虑 `innodb_buffer_pool_size` 未被完全使用的情况。另外，`max_connections` 计算的是最高session消耗。

>`key_buffer_size` + `query_cache_size` + `tmp_table_size` + `innodb_buffer_pool_size` + `innodb_additional_mem_pool_size` + `innodb_log_buffer_size` + `max_connections` *  (
`sort_buffer_size` + `read_buffer_size` + `read_rnd_buffer_size` + `join_buffer_size` + `thread_stack` + `binlog_cache_size`)

[ MySQL Memory在线计算工具] http://www.mysqlcalculator.com/