为什么MySQL内存占用这么大？ for InnoDB

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

1. global级共享内存
2. session级私有内存

下面我们分别探究一下这两种内存：

1. global级共享内存

包括的内容如下图所示

执行如下命令，即可查询示例的共享内存分配情况：

show variables where variable_name in (
'innodb_buffer_pool_size','innodb_log_buffer_size','innodb_additional_mem_pool_size','query_cache_size'
);

以下是一个mysql的输出结果

+---------------------------------+-----------+
| 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级私有内存，主要是数据库连接私有内存使用，查询命令如下：

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'
);

查询结果如下：

+----------------------+-----------+
| 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
)

在线计算工具 http://www.mysqlcalculator.com/