2. 首页 > MySQL教程 > 正文

MySQL教程FG032-MySQL内存配置与优化

教程整理：风哥教程  |  更新时间：2026-01-18  |  教程分类：MySQL教程  |  文档学习：22

本文档风哥主要介绍MySQL 8.4的内存配置与优化，包括内存使用组成、内存参数配置、内存性能调优等内容。风哥教程参考MySQL官方文档Server Administration等。更多视频教程www.fgedu.net.cn

Part01-基础概念与理论知识

1.1 MySQL内存使用组成

MySQL的内存使用主要包括以下几个核心部分：

  • InnoDB缓冲池：缓存数据和索引，是MySQL最主要的内存消耗部分
  • MyISAM键缓存：缓存MyISAM表的索引数据
  • 连接内存：每个客户端连接使用的内存，包括sort_buffer_size、read_buffer_size等
  • 查询内存：用于处理查询的内存，如join_buffer_size、tmp_table_size等
  • 系统内存：MySQL服务器进程本身使用的内存

1.2 内存配置的重要性

合理的内存配置对MySQL性能至关重要：

  • 提高数据读写性能，减少磁盘I/O操作
  • 加速查询响应，提升用户体验
  • 优化系统资源利用率，降低硬件成本
  • 避免内存不足导致的性能下降或崩溃

学习交流加群风哥微信: itpux-com

1.3 内存参数关系

# 查看当前MySQL内存相关参数
mysql -u root -p -e “SHOW VARIABLES LIKE ‘%buffer%’; SHOW VARIABLES LIKE ‘%size%’;” | grep -E ‘buffer|size’
| head -20
Enter password: Fgedu123!
+————————————-+—————-+——+——–+———————-+——————————+
| Variable_name | Value | Type | Access | Context | Comment |
+————————————-+—————-+——+——–+———————-+——————————+
| bulk_insert_buffer_size | 8388608 | bigint | SYS_VAR | GLOBAL | Buffer size for bulk inserts |
| innodb_buffer_pool_chunk_size | 134217728 | bigint | SYS_VAR | GLOBAL | InnoDB buffer pool chunk size |
| innodb_buffer_pool_size | 1073741824 | bigint | SYS_VAR | GLOBAL | InnoDB buffer pool size in bytes |
| innodb_change_buffer_max_size | 25 | int | SYS_VAR | GLOBAL | Maximum size for InnoDB change buffer (in
percent) |
| innodb_log_buffer_size | 16777216 | bigint | SYS_VAR | GLOBAL | InnoDB log buffer size in bytes |
| join_buffer_size | 262144 | bigint | SYS_VAR | SESSION | Buffer size for ordinary index scans |
| key_buffer_size | 8388608 | bigint | SYS_VAR | GLOBAL | Index blocks buffer size |
| myisam_sort_buffer_size | 8388608 | bigint | SYS_VAR | GLOBAL | MyISAM sort buffer size |
| net_buffer_length | 16384 | int | SYS_VAR | SESSION | Buffer size for TCP/IP and socket communication |
| preload_buffer_size | 32768 | int | SYS_VAR | SESSION | Buffer size for preload operations |
| read_buffer_size | 131072 | bigint | SYS_VAR | SESSION | Buffer size for read operations |
| read_rnd_buffer_size | 262144 | bigint | SYS_VAR | SESSION | Buffer size for random read operations |
| sort_buffer_size | 262144 | bigint | SYS_VAR | SESSION | Buffer size for sorts |
| tmp_table_size | 16777216 | bigint | SYS_VAR | SESSION | Maximum size for temporary tables |

Part02-生产环境规划与建议

2.1 内存容量规划

根据服务器内存大小和业务需求，合理规划MySQL内存使用：

# 查看服务器总内存
free -h
total used free shared buff/cache available
Mem: 16G 2.3G 11G 128M 2.7G 13G
Swap: 0B 0B 0B

# 内存分配建议比例
# – InnoDB缓冲池：50-70% of total memory
# – 连接内存：根据max_connections和每个连接的内存需求
# – 其他MySQL内存：10-15% of total memory
# – 操作系统预留：20-30% of total memory

2.2 操作系统内存预留

风哥提示：必须为操作系统预留足够的内存，避免因内存不足导致系统swap或崩溃。

操作系统内存预留建议：

  • 4GB服务器：预留1-2GB
  • 8GB服务器：预留2-3GB
  • 16GB服务器：预留4-6GB
  • 32GB服务器：预留8-12GB
  • 64GB+服务器：预留20-30%

2.3 内存配置策略

生产环境内存配置策略：

  • 优先保证InnoDB缓冲池大小，这是影响性能的最关键因素
  • 根据并发连接数合理设置连接相关内存参数
  • 避免单个连接占用过多内存，导致内存耗尽
  • 定期监控内存使用情况，及时调整配置

学习交流加群风哥QQ113257174

Part03-生产环境项目实施方案

3.1 缓冲池配置

# 编辑my.cnf配置文件
vi /etc/my.cnf

[mysqld]
# 缓冲池大小（设置为物理内存的60%）
innodb_buffer_pool_size=10G

# 缓冲池实例数（建议与CPU核心数相同或为2的倍数）
innodb_buffer_pool_instances=8

# 缓冲池管理配置
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_dump_pct=50

# 保存并重启MySQL
systemctl restart mysqld

# 验证缓冲池配置
mysql -u root -p -e “SHOW VARIABLES LIKE ‘innodb_buffer_pool%’;”
Enter password: Fgedu123!
+————————————-+—————-+——+——–+———————-+——————————+
| Variable_name | Value | Type | Access | Context | Comment |
+————————————-+—————-+——+——–+———————-+——————————+
| innodb_buffer_pool_chunk_size | 134217728 | bigint | SYS_VAR | GLOBAL | InnoDB buffer pool chunk size |
| innodb_buffer_pool_dump_at_shutdown | ON | bool | SYS_VAR | GLOBAL | Dump buffer pool on shutdown. |
| innodb_buffer_pool_dump_now | OFF | bool | SYS_VAR | GLOBAL | Dump the buffer pool now. |
| innodb_buffer_pool_dump_pct | 50 | int | SYS_VAR | GLOBAL | Percentage of buffer pool to dump. |
| innodb_buffer_pool_load_abort | OFF | bool | SYS_VAR | GLOBAL | Abort buffer pool load. |
| innodb_buffer_pool_load_at_startup | ON | bool | SYS_VAR | GLOBAL | Load buffer pool at startup. |
| innodb_buffer_pool_load_now | OFF | bool | SYS_VAR | GLOBAL | Load the buffer pool now. |
| innodb_buffer_pool_size | 10737418240 | bigint | SYS_VAR | GLOBAL | InnoDB buffer pool size in bytes |
| innodb_buffer_pool_instances | 8 | int | SYS_VAR | GLOBAL | Number of buffer pool instances. |
+————————————-+—————-+——+——–+———————-+——————————+

3.2 连接相关内存配置

# 编辑my.cnf配置文件
vi /etc/my.cnf

[mysqld]
# 最大连接数
max_connections=200

# 连接内存参数
sort_buffer_size=256K
read_buffer_size=128K
read_rnd_buffer_size=256K
join_buffer_size=256K

# 线程管理
thread_cache_size=32
thread_stack=256K

# 保存并重启MySQL
systemctl restart mysqld

# 验证连接内存配置
mysql -u root -p -e “SHOW VARIABLES LIKE ‘max_connections’; SHOW VARIABLES LIKE ‘%buffer_size%’; SHOW
VARIABLES LIKE ‘thread%’;”
Enter password: Fgedu123!
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 200 |
+—————–+——-+
+————————-+—————-+——+——–+———————-+——————————+
| Variable_name | Value | Type | Access | Context | Comment |
+————————-+—————-+——+——–+———————-+——————————+
| bulk_insert_buffer_size | 8388608 | bigint | SYS_VAR | GLOBAL | Buffer size for bulk inserts |
| innodb_buffer_pool_size | 10737418240 | bigint | SYS_VAR | GLOBAL | InnoDB buffer pool size in bytes |
| innodb_log_buffer_size | 16777216 | bigint | SYS_VAR | GLOBAL | InnoDB log buffer size in bytes |
| join_buffer_size | 262144 | bigint | SYS_VAR | SESSION | Buffer size for ordinary index scans |
| key_buffer_size | 8388608 | bigint | SYS_VAR | GLOBAL | Index blocks buffer size |
| myisam_sort_buffer_size | 8388608 | bigint | SYS_VAR | GLOBAL | MyISAM sort buffer size |
| net_buffer_length | 16384 | int | SYS_VAR | SESSION | Buffer size for TCP/IP and socket communication |
| preload_buffer_size | 32768 | int | SYS_VAR | SESSION | Buffer size for preload operations |
| read_buffer_size | 131072 | bigint | SYS_VAR | SESSION | Buffer size for read operations |
| read_rnd_buffer_size | 262144 | bigint | SYS_VAR | SESSION | Buffer size for random read operations |
| sort_buffer_size | 262144 | bigint | SYS_VAR | SESSION | Buffer size for sorts |
+————————-+—————-+——+——–+———————-+——————————+
+—————————+—————-+——+——–+———————-+——————————+
| Variable_name | Value | Type | Access | Context | Comment |
+—————————+—————-+——+——–+———————-+——————————+
| thread_cache_size | 32 | int | SYS_VAR | GLOBAL | How many threads we should keep in a cache for reuse. |
| thread_handling | one-thread-per-connection | string | SYS_VAR | GLOBAL | Thread handling mode. |
| thread_stack | 262144 | int | SYS_VAR | GLOBAL | Size of stack for each thread. |
+—————————+—————-+——+——–+———————-+——————————+

3.3 其他内存参数配置

# 编辑my.cnf配置文件
vi /etc/my.cnf

[mysqld]
# 临时表配置
tmp_table_size=128M
max_heap_table_size=128M

# 表缓存配置
table_open_cache=4000
table_definition_cache=800
table_open_cache_instances=8

# 保存并重启MySQL
systemctl restart mysqld

# 验证配置
mysql -u root -p -e “SHOW VARIABLES LIKE ‘tmp_table_size’; SHOW VARIABLES LIKE ‘max_heap_table_size’; SHOW
VARIABLES LIKE ‘table%cache%’;”
Enter password: Fgedu123!
+—————-+————+
| Variable_name | Value |
+—————-+————+
| tmp_table_size | 134217728 |
+—————-+————+
+———————+————+
| Variable_name | Value |
+———————+————+
| max_heap_table_size | 134217728 |
+———————+————+
+—————————-+——-+——+——–+———————-+——————————+
| Variable_name | Value | Type | Access | Context | Comment |
+—————————-+——-+——+——–+———————-+——————————+
| table_definition_cache | 800 | int | SYS_VAR | GLOBAL | Number of table definitions that can be cached. |
| table_open_cache | 4000 | int | SYS_VAR | GLOBAL | How many open tables MySQL can keep in cache. |
| table_open_cache_instances | 8 | int | SYS_VAR | GLOBAL | Number of open table cache instances. |
+—————————-+——-+——+——–+———————-+——————————+

风哥提示：临时表大小设置应根据业务需求调整，避免过大导致内存不足。更多学习教程公众号风哥教程itpux_com

Part04-生产案例与实战讲解

4.1 内存配置案例

# 16GB服务器内存配置示例
[mysqld]
# 核心内存配置
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances=8

# 连接配置
max_connections=200
thread_cache_size=32

# 查询内存配置
sort_buffer_size=256K
read_buffer_size=128K
read_rnd_buffer_size=256K
join_buffer_size=256K

# 临时表配置
tmp_table_size=128M
max_heap_table_size=128M

# 表缓存配置
table_open_cache=4000
table_definition_cache=800
table_open_cache_instances=8

# 其他配置
key_buffer_size=128M
innodb_log_buffer_size=32M

4.2 内存使用监控

# 监控系统内存使用
iostat -x 5
top -p $(pgrep -f mysqld)

# 监控MySQL内存相关状态
mysql -u root -p -e “SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool%’; SHOW GLOBAL STATUS LIKE ‘Threads%’; SHOW
GLOBAL STATUS LIKE ‘Created_tmp%’;”
Enter password: Fgedu123!

# 计算缓冲池命中率
mysql -u root -p -e “SELECT \
(1 – (VARIABLE_VALUE / ( \
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME =
‘Innodb_buffer_pool_read_requests’) + \
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME =
‘Innodb_buffer_pool_reads’) \
))) * 100 AS hit_rate \
FROM performance_schema.global_status \
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_reads’;”
Enter password: Fgedu123!
+———-+——+——–+———————-+——————+
| hit_rate | Type | Access | Context | Comment |
+———-+——+——–+———————-+——————+
| 99.85 | real | SYS_VAR | GLOBAL | Hit rate in percent |
+———-+——+——–+———————-+——————+

# 使用Performance Schema监控内存分配
mysql -u root -p -e “SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE
current_alloc > 0 ORDER BY current_alloc DESC LIMIT 10;”
Enter password: Fgedu123!

4.3 内存优化实战

# 案例1：缓冲池命中率低的优化
# 1. 检查当前命中率
mysql -u root -p -e “SELECT \
(1 – (VARIABLE_VALUE / ( \
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME =
‘Innodb_buffer_pool_read_requests’) + \
(SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME =
‘Innodb_buffer_pool_reads’) \
))) * 100 AS hit_rate \
FROM performance_schema.global_status \
WHERE VARIABLE_NAME = ‘Innodb_buffer_pool_reads’;”
Enter password: Fgedu123!

# 2. 增加缓冲池大小
vi /etc/my.cnf
innodb_buffer_pool_size=16G # 从10G增加到16G

# 3. 重启MySQL并验证
systemctl restart mysqld
mysql -u root -p -e “SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;”
Enter password: Fgedu123!

# 案例2：临时表使用过多的优化
# 1. 检查临时表使用情况
mysql -u root -p -e “SHOW GLOBAL STATUS LIKE ‘Created_tmp%’;”
Enter password: Fgedu123!

# 2. 优化查询或增加临时表大小
vi /etc/my.cnf
tmp_table_size=256M
max_heap_table_size=256M

# 3. 重启MySQL并验证
systemctl restart mysqld

Part05-风哥经验总结与分享

5.1 内存配置最佳实践

风哥提示：MySQL内存配置是一个动态过程，需要根据业务发展和服务器资源变化不断调整。
  • 缓冲池配置：设置为物理内存的50-70%，实例数与CPU核心数匹配
  • 连接内存：根据并发连接数合理设置，避免单个连接占用过多内存
  • 临时表：设置合理大小，避免频繁创建磁盘临时表
  • 表缓存：根据数据库大小和表数量调整，避免设置过大
  • 操作系统预留：必须预留足够内存给操作系统和其他进程

5.2 常见问题与解决方案

# 问题1：MySQL内存使用过高导致系统swap
# 解决方案：
1. 减少innodb_buffer_pool_size大小
2. 限制max_connections数量
3. 减少每个连接的内存分配（如sort_buffer_size等）
4. 增加服务器物理内存

# 问题2：缓冲池命中率低
# 解决方案：
1. 增加innodb_buffer_pool_size
2. 优化查询，减少全表扫描
3. 增加索引，提高缓存利用率
4. 考虑数据分片或分区

# 问题3：连接数达到上限
# 解决方案：
1. 增加max_connections
2. 优化应用程序连接池配置
3. 检查是否有连接泄漏
4. 考虑使用连接池中间件

# 问题4：临时表使用过多
# 解决方案：
1. 优化查询，减少临时表使用
2. 增加tmp_table_size和max_heap_table_size
3. 使用索引覆盖查询
4. 考虑分区表

5.3 内存性能调优

内存性能调优步骤：

# 1. 监控内存使用现状
mysql -u root -p -e “SHOW GLOBAL VARIABLES LIKE ‘%buffer%’; SHOW GLOBAL VARIABLES LIKE ‘%cache%’; SHOW
GLOBAL STATUS LIKE ‘%Innodb_buffer_pool%’;”
Enter password: Fgedu123!

# 2. 分析内存使用瓶颈
# – 缓冲池命中率是否低于95%
# – 连接数是否经常达到max_connections
# – 是否频繁创建磁盘临时表
# – 表缓存命中率是否合理

# 3. 调整内存配置参数
# 根据分析结果调整相应参数

# 4. 测试验证性能
# 使用sysbench等工具测试性能变化
sysbench –test=oltp_read_write –db-driver=mysql –mysql-host=127.0.0.1 –mysql-user=root
–mysql-password=Fgedu123! –mysql-db=fgedudb –tables=10 –table-size=1000000 –threads=16 –time=60 run

# 5. 持续监控优化
# 建立长期监控机制，定期分析内存使用情况

from MySQL:www.itpux.com

GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息