MySQL 如何使用内存

MySQL 在服务器启动时分配全局缓冲区，这些缓冲区在所有连接之间共享。MySQL 的大部分内存被全局缓冲区（例如 innodb_buffer_pool_size、innodb_log_buffer_size、key_buffer_size 等）消耗了。

InnoDB 缓冲区池通常是 MySQL 实例中最大的内存使用方。它使用 innodb_buffer_pool_size 参数进行配置。它用于缓存表数据和索引、更改缓冲区、自适应哈希索引以及其他内部结构。Cloud SQL for MySQL 是 Google Cloud 的托管式 MySQL 产品，可配置最多 72% 的实例内存的 innodb_buffer_pool_size，具体取决于实例大小。

InnoDB 为缓冲区和关联的数据结构预留额外的内存，分配的内存总大小大约比指定缓冲区池大小多 10%。您可以在 show engine innodb status\G 输出中查看 InnoDB 缓冲区池内存用量。

mysql> show engine innodb status\G

…

----------------------

缓冲区池和内容

----------------------

分配的大内存总量 11511349248

…

InnoDB 日志缓冲区用于保存要写入磁盘上 InnoDB 重做日志文件的更改。它使用 innodb_log_buffer_size 进行配置。MySQL 社区的默认值为 16 MB，Cloud SQL for MySQL 也使用该值。

MySQL 使用键缓冲区在内存中缓存 MyISAM 索引。它使用 key_buffer_size 进行配置。MySQL 社区的默认值为 8 MB，Cloud SQL for MySQL 也使用该值。Cloud SQL for MySQL 不支持 MyISAM 表，因此可以将其保留为默认值。

query_cache_size 配置变量用于定义为缓存查询结果分配的内存量。默认情况下，MySQL Community 5.7 和 Cloud SQL for MySQL 5.7 处于停用状态。

查询缓存以严重的可伸缩性问题而闻名，因此它在 MySQL 5.7.20 中已弃用，并在 MySQL 8.0 中被移除。如果您仍在 MySQL 5.7 实例中使用查询缓存，请检查它是否对您的工作负载确实有用。请参阅这篇此博客了解详情。

MySQL 会分配在所有连接之间共享的全局缓存，这些缓存是动态分配的，并且配置变量定义了它们的最大限制。

MySQL 使用表缓存来加快表的打开速度。MySQL 表缓存分为两部分，即打开表的缓存和表定义的缓存，分别使用 table_open_cache 和 table_definition_cache 进行配置。

table_open_cache 是内存缓存，用于存储所有已连接线程上已打开表的文件描述符。 提高此值会增加 mysqld 程序（也称为 MySQL 服务器）所需的文件描述符数量。请确保您的操作系统可以处理 table_open_cache 设置隐含的打开文件描述符的数量。

多个客户端会话可以同时访问给定表，并且该表会由每个并发客户端会话单独打开。因此，您可能会发现打开表数大于服务器中的表数。当表缓存已满时，服务器会从当前最不常使用的表开始释放当前未使用的表。

table_definition_cache 是用于存储表定义的内存缓存。它是全局性的，在所有连接之间共享。

CloudSQL for MySQL 5.7 使用 2000 和 1400 作为 table_open_cache 和 table_definition_cache 的默认值。

CloudSQL for MySQL 8.0 使用 4000 和 2000 作为 table_open_cache 和 table_definition_cache 的默认值。

对于每个客户端连接，MySQL 会分配一个专用线程来执行所有查询并将结果返回给客户端，直到客户端断开连接。MySQL 会缓存线程，这样就不必为每个连接创建和销毁线程。线程缓存中的线程数使用 thread_cache_size 变量配置。

CloudSQL for MySQL 使用 48 作为 thread_cache_size 的默认值。

InnoDB 有自己的缓存来存储表定义，这与表打开缓存和表定义缓存不同。您可以在 show engine innodb status\G 输出中检查为 InnoDB 数据字典分配的内存。

----------------------

缓冲区池和内容

----------------------

…

分配的字典内存 65816817

table_definition_cache 对 InnoDB 数据字典缓存中的表实例数量设置了软限制，如果 InnoDB 数据字典缓存中的表实例数量超过了 table_definition_cache 限制，则 LRU 机制会开始将表实例标记为逐出并最终将其从此缓存中移除

这是否意味着 InnoDB 数据字典缓存中的表实例数将始终低于 table_definition_cache 限制？事实并非如此，具有外键关系的表实例不会放在 LRU 列表中。它们会保持缓存状态，并导致表实例超出 table_definition_cache 限制，进而增加内存用量。具有外键关系的表所消耗的内存仅在 MySQL 关停/重启事件发生时释放。MySQL 5.7 和 8.0 中都存在此问题，并且有一个经过验证的已知 bug。

如果您的 MySQL 实例中有大量具有外键关系的表，InnoDB 数据字典缓存可能会占用数 GB 的内存。此情况在配置 MySQL 缓冲区/缓存时经常被忽略，并且可能是导致内存用量异常高或内存不足 (OOM) 问题的原因之一。

示例输出显示了一个使用 4.16GB 的 InnoDB 数据字典缓存的小型实例。

$ mysql -e "show engine innodb status\G" | grep -i memory

缓冲区池和内容

分配的大内存总量 7696023552

分配的字典内存 4465193358

MySQL 的另一个消耗内存的功能是会话缓冲区。这些缓冲区是按会话进行分配的，在某些情况下，可以为单个查询（特别是 join_buffer_size）分配多个缓冲区实例。

• sort_buffer_size 
• join_buffer_size
• read_buffer_size
• read_rnd_buffer_size

这些缓冲区仅在查询需要时（用于排序、联接、索引/全表扫描等）进行分配；但当需要这些缓冲区时，即使需要非常小的部分，系统也会将其分配到完整大小。将这些缓冲区设置为较高的值可能会导致内存浪费。

MySQL 社区和 Cloud SQL for MySQL 中的默认值相同。

MySQL 使用二进制日志缓存来保存事务运行时对二进制日志所做的更改。它使用 binlog_cache_size 进行配置。如果启用了二进制日志记录 (log_bin=ON)，则系统会为每个客户端分配二进制日志缓存。

binlog_cache_size 的默认值在 MySQL 社区和 Cloud SQL for MySQL 中相同。

在处理某些类型的查询（如 GROUP BY、ORDER BY、DISTINCT 和 UNION）时，MySQL 会创建内部临时表来存储中间结果。这些内部临时表首先在内存中创建，并在达到大小上限时转换为磁盘上的表。内部临时表的大小上限取决于 tmp_table_size 和 max_heap_table_size 变量的最小值。

tmp_table_size 和 max_heap_table_size 的默认值在 MySQL 社区和 Cloud SQL for MySQL 中相同。

注意：由于每个会话的缓冲区和内存中的临时表会为每个连接分别分配内存，因此如果连接数量较大，则整体内存用量可能会非常高。建议不要将这些值设置得过高，请进行实验以找到最适合您工作负载的值。

每个线程都需要少量内存来管理客户端连接。以下变量控制其大小。

• thread_stack：每个线程的堆栈大小，默认值为 256KB。
• net_buffer_length：每个客户端均与 net_buffer_length 的连接缓冲区和结果缓冲区关联。它可以进一步增加到 max_allowed_packet 大小。

如果启用了 performance_schema，则有助于在较低级别监控 MySQL 服务器执行情况。Performance_schema 用于动态分配内存，并且仅在 MySQL 关闭/重启时释放。

Cloud SQL for MySQL 允许在 RAM 大小为 15 GB 或更高的实例上启用 performance_schema（从 MySQL 8.0.26 版开始，该功能默认处于启用状态）。在 MySQL 5.6、5.7 和 8.0.18 版本中，Performance_schema 默认处于停用状态，您可以使用数据库标志启用该架构。

Cloud SQL for MySQL 会自动配置与内存相关的参数，以实现良好的开箱即用性能。如果工作负载需要更大的缓存来支持大量表和/或连接，您的实例可能仍会遇到 OOM 问题。同时分配会话缓冲区的许多线程也可能会导致内存相关问题。为全局缓冲区/缓存分配的内存越多，可用于连接和会话缓冲区的内存就越少，反之亦然，找到良好的平衡是关键。

如果您的工作负载需要更大的缓存/会话缓冲区，您可以使用 innodb_buffer_pool_size 减小全局缓冲区的大小。您可以使用数据库标志更改 Cloud SQL for MySQL 实例的配置参数。如果您仍然遇到 OOM 或性能问题，可以升级实例大小来增加内存。