#MySQL 内存使用优化

MySQL 的一个关键指标是实例的内存使用率和缓冲池命中率。高内存使用增加了 OOM（内存溢出）错误的风险，而低缓冲池命中率则表明许多数据页未命中缓冲池中的缓存数据页，需要从存储中读取数据，这将增加 IO 吞吐量和延迟。

#检查内存使用情况

#检查总内存使用情况

select * from sys.memory_global_total;

注意：由于数据更新频率、缓存和内核使用（这一部分未计入）的限制，这条语句提供的内存使用情况是一个大致的指示。

#按内存使用情况检查前 20 个事件

select event_name,CURRENT_NUMBER_OF_BYTES_USED/1024/1024
from performance_schema.memory_summary_global_by_event_name
order by CURRENT_NUMBER_OF_BYTES_USED desc LIMIT 20;

#按内存使用情况检查前 20 个线程

select thread_id,event_name,CURRENT_NUMBER_OF_BYTES_USED/1024/1024
from performance_schema.memory_summary_by_thread_by_event_name
order by CURRENT_NUMBER_OF_BYTES_USED desc limit 20;

#检查当前分配的内存

select substring_index(event_name,'/',2) as code_area, format_bytes(sum(current_alloc)) as current_alloc
from sys.x$memory_global_by_current_bytes
group by substring_index(event_name,'/',2)
order by sum(current_alloc) DESC;

#内存使用优化建议

#多条语句

MySQL 支持使用分号 ; 来分隔多条 SQL 语句，并将它们一起发送给 MySQL 逐一处理。然而，一些内存需要等所有 SQL 语句执行完毕后才能释放。

如果一次发送大量 SQL 语句，例如达到几百兆字节，则在执行 SQL 语句的过程中，各种对象的分配和累计消耗可能会非常大，可能导致 MySQL 进程的内存耗尽。

此外，使用多条语句发送 SQL 可能导致网络流量的突然增加，这可以通过网络流量监控和 SQL 监控进行评估。因此，在业务实现中，建议尽量避免使用多条语句发送 SQL 的方法。

#缓冲池问题

所有表的数据页都存储在缓冲池中。在查询执行期间，如果所需的数据页直接命中缓冲池，则不会发生物理 I/O，从而提高 SQL 执行效率。缓冲池使用 LRU（最少最近使用）算法管理数据页，所有脏页都放置在 Flush List 链表中。

在内存较大的系统中，InnoDB Buffer Pool 内存通常是实例内存中最大的。通过修改 innodb_buffer_pool_instances 参数可以增加缓冲池实例的数量，从而改善并发性能。

与缓冲池相关的常见问题及优化建议如下表所示。

#临时表

在 MySQL 中使用内存临时表时，其大小受 tmp_table_size 和 max_heap_table_size 参数的限制。超过此参数限制的临时表将被转换为磁盘临时表。如果大量连接在短时间内创建众多内存临时表，可能会导致内存使用急剧增加。

MySQL 8.0 引入了一种新的临时表引擎。该引擎要求所有线程分配的内存临时表总大小必须小于参数 temptable_max_ram，默认值为 1GB。当内存临时表超过此限制时，将转换为磁盘临时表。

#集群通信缓存

使用 MGR（MySQL 集群复制）形成集群时，还需考虑 XCom Cache 通信缓存的消耗。默认情况下，此缓存大小为 1GB。在网络质量良好的环境中，可以使用较低的 group_replication_message_cachesize 参数来创建集群，从而降低内存使用。

#其他

当实例中的表数量特别高或查询频率非常高时，表缓存可能会消耗大量内存。建议避免在实例中创建过多的表，或将表的 open_cache 参数设置为适当的大小。

AHI（自适应哈希索引）默认占用缓冲池内存的 1/64。如果查询或写入大量字段（如 BLOB），将会动态分配内存，进而增加内存使用。禁用 AHI 可以更有效、合理地利用服务器的内存资源，为业务系统释放出更多可用内存，从而提高整体性能。然而，需要注意的是，AHI 也具备加速特定查询的能力，因此在禁用它时，需在查询性能提升与内存优化之间取得平衡。

如果 MySQL 实例的内存使用出现异常增加或内存耗尽，建议参考MySQL 5.7 官方文档或MySQL 8.0 官方文档以调查内存使用增加的原因。