Ubuntu MySQL调整内存使用的完整指南

一、调整前的准备工作

在修改MySQL内存配置前，需先确认系统内存状况和MySQL当前内存使用，避免盲目调整导致系统崩溃：

查看系统内存：使用free -h命令查看系统总内存、已用内存及可用内存（重点关注available字段，代表应用程序可用的内存）；
查看MySQL内存使用：通过top或htop命令过滤mysqld进程，观察其内存占用情况；或登录MySQL执行SHOW STATUS LIKE 'Threads_connected';（查看当前连接数）、SHOW ENGINE INNODB STATUS;（查看InnoDB缓冲池使用情况）。

MySQL的内存使用主要由缓冲池、连接缓存、查询缓存等参数控制，需根据应用场景（如InnoDB/MyISAM使用比例、并发连接数）调整：

作用：InnoDB存储引擎的核心缓存，用于缓存表数据、索引及undo日志，是MySQL内存占用最大的部分（约占可用内存的70%-80%）。
设置建议：若系统主要使用InnoDB（MySQL 5.5+默认引擎），将其设置为系统可用内存的70%-80%（如8GB内存可设为6G）；若同时使用MyISAM，需预留部分内存给MyISAM索引。
配置示例：在/etc/mysql/mysql.conf.d/mysqld.cnf（Ubuntu默认路径）的[mysqld]部分添加：
```
innodb_buffer_pool_size = 6G
```
注意：MySQL 8.0+版本无需单独设置innodb_buffer_pool_instances（默认自动优化）。

作用：MyISAM存储引擎的索引缓存，仅适用于MyISAM表（读密集型场景）。
设置建议：若主要使用InnoDB，可将其设置为系统可用内存的5%-10%（如8GB内存设为512M）；若主要使用MyISAM，可适当增大（但不超过可用内存的30%）。
配置示例：
```
key_buffer_size = 512M
```

作用：限制MySQL同时接受的客户端连接数，每个连接都会占用一定内存（约几MB至几十MB）。
设置建议：根据应用需求调整（如Web应用通常设为100-300），避免过多连接导致内存耗尽。可通过SHOW STATUS LIKE 'Threads_connected';查看当前连接数。
配置示例：
```
max_connections = 200
```

作用：缓存查询结果（MySQL 5.7.20+已废弃，InnoDB有自己的缓冲池）。
设置建议：
- MySQL 5.7及以上版本：直接禁用（设为0），避免查询缓存的维护开销；
- MySQL 5.6及以下版本：若查询重复率高（如报表系统），可设置为32M-64M（不宜过大，否则会导致缓存失效频繁）。

配置示例：

query_cache_type = 0  # 禁用查询缓存
query_cache_size = 0

配置示例：

tmp_table_size = 64M
max_heap_table_size = 64M

保存配置文件：编辑完成后，按Ctrl+O保存，Ctrl+X退出；
重启MySQL服务：执行sudo systemctl restart mysql（或sudo service mysql restart）使配置生效；

验证参数设置：登录MySQL执行以下命令，确认参数已修改：

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'query_cache_size';

调整后需定期监控内存使用，根据实际情况进一步优化：

监控工具：
- 系统层面：top（按M排序内存占用）、htop（可视化监控）；
- MySQL层面：performance_schema（内置性能监控）、SHOW STATUS LIKE 'Innodb_buffer_pool_read%'（查看缓冲池命中率）；
- 第三方工具：pt-query-digest（分析慢查询日志）、MySQL Workbench（图形化管理）。
关键指标：
- 缓冲池命中率：Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests（应接近100%，否则需增大缓冲池）；
- 临时表使用率：Created_tmp_tables（内存临时表数）与Created_tmp_disk_tables（磁盘临时表数）的比例（应尽量让内存临时表占比高，避免磁盘IO）；
- 连接数波动：Threads_connected（当前连接数）与Max_used_connections（历史最大连接数）（避免连接数超过max_connections）。

优化SQL与索引：使用EXPLAIN分析慢查询，避免SELECT *，为常用查询字段创建索引（如WHERE、ORDER BY子句中的字段）；
定期维护：执行ANALYZE TABLE（更新表统计信息，优化查询计划）、OPTIMIZE TABLE（整理表碎片，回收空间）；
升级硬件：若内存调整后仍无法满足需求，可考虑增加服务器内存（最有效的解决方案）。

通过以上步骤，可有效调整Ubuntu上MySQL的内存使用，提升数据库性能并避免内存不足问题。调整时需根据实际业务场景（如读写比例、并发量）灵活配置，避免生搬硬套。