核心概念
内存是 MySQL 性能的关键因素，合理的内存配置可以大幅提升性能。
MySQL 内存结构
MySQL 内存结构：
├── InnoDB Buffer Pool（最重要）
├── Query Cache（8.0 已移除）
├── 连接缓冲区
│ ├── Sort Buffer
│ ├── Read Buffer
│ ├── Join Buffer
│ └── ...
├── InnoDB Log Buffer
└── 其他缓冲区内存分配原则
1. Buffer Pool 优先：分配 50-80% 物理内存
2. 避免过度分配：预留 20% 给操作系统
3. 连接缓冲区按需：不要设置过大
4. 监控调整：根据实际使用情况调整InnoDB Buffer Pool
Buffer Pool 作用
Buffer Pool 是 InnoDB 最重要的内存区域，用于缓存数据和索引页。
Buffer Pool 结构：
├── Free List：空闲页
├── Flush List：脏页（需要刷盘）
├── LRU List：最近使用页
│ ├── Old Sublist（5/8）
│ └── Young Sublist（3/8）
└── Hash Table：页查找Buffer Pool 配置
[mysqld]
# Buffer Pool 大小（物理内存的 50-80%）
innodb_buffer_pool_size = 8G
# Buffer Pool 实例数（减少锁竞争）
innodb_buffer_pool_instances = 8
# 预读页数
innodb_read_ahead_threshold = 56
# 随机预读
innodb_random_read_ahead = OFFBuffer Pool 监控
-- 查看 Buffer Pool 使用情况
SHOW ENGINE INNODB STATUS\G
-- 关键指标：
-- BUFFER POOL AND MEMORY
-- Total memory allocated: 8589934592
-- Buffer pool size: 524287
-- Free buffers: 1000
-- Database pages: 520000
-- Old database pages: 192000
-- Modified db pages: 100
-- Pending reads: 0
-- Pending writes: LRU 0, flush list 0, single page 0
-- 使用 performance_schema
SELECT
pool_id,
instance_id,
pages_used,
pages_free,
dirty_pages,
hit_rate
FROM information_schema.INNODB_BUFFER_POOL_STATS;Buffer Pool 命中率
-- 计算命中率
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- 命中率公式：
-- 命中率 = (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads)
-- / Innodb_buffer_pool_read_requests * 100
-- 示例查询：
SELECT
(1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100 AS hit_rate
FROM information_schema.GLOBAL_STATUS
WHERE Variable_name IN ('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests');
-- 理想命中率：> 99%Buffer Pool 预热
-- 重启后预热 Buffer Pool
-- MySQL 8.0+ 支持 Buffer Pool 持久化
-- 开启持久化
SET GLOBAL innodb_buffer_pool_dump_at_shutdown = 1;
SET GLOBAL innodb_buffer_pool_load_at_startup = 1;
-- 手动导出热点页
SET GLOBAL innodb_buffer_pool_dump_now = 1;
-- 手动加载
SET GLOBAL innodb_buffer_pool_load_now = 1;
-- 导出特定比例的页
SET GLOBAL innodb_buffer_pool_dump_pct = 40;Query Cache（8.0 已移除）
Query Cache 原理
MySQL 5.7 及之前版本支持，8.0 已移除。
Query Cache 工作流程：
1. 查询 SQL 哈希
2. 查找缓存
3. 命中：直接返回
4. 未命中：执行查询并缓存Query Cache 配置（5.7）
[mysqld]
# 开启 Query Cache
query_cache_type = 1 # 0:OFF, 1:ON, 2:DEMAND
# Query Cache 大小
query_cache_size = 64M
# 最小缓存单元
query_cache_min_res_unit = 2K
# 缓存限制
query_cache_limit = 1MQuery Cache 问题
1. 锁竞争：全局锁，并发差
2. 失效频繁：表变更导致缓存失效
3. 碎片化：内存碎片
MySQL 8.0 移除原因：
- 并发场景性能差
- 维护成本高
- 实际收益低连接缓冲区
Sort Buffer
Sort Buffer 用于 ORDER BY 和 GROUP BY 排序。[mysqld]
# 排序缓冲区大小（每连接）
sort_buffer_size = 2M
# 排序临时表限制
max_heap_table_size = 256M
tmp_table_size = 256M监控：
-- 查看文件排序次数
SHOW STATUS LIKE 'Sort%';
-- Sort_merge_passes: 归并次数（越少越好）
-- Sort_rows: 排序行数
-- Sort_range: 范围排序次数
-- Sort_scan: 全表排序次数
-- 优化目标：减少 Sort_merge_passesRead Buffer
Read Buffer 用于顺序读取数据。[mysqld]
# 顺序读缓冲区（每连接）
read_buffer_size = 2M
# 随机读缓冲区（每连接）
read_rnd_buffer_size = 1MJoin Buffer
Join Buffer 用于无索引的 JOIN 操作。[mysqld]
# JOIN 缓冲区（每连接）
join_buffer_size = 4M优化建议：
1. 优先添加索引，而不是增大 Join Buffer
2. Join Buffer 过大会浪费内存
3. 监控临时表使用Thread Cache
Thread Cache 用于缓存空闲线程，减少创建开销。[mysqld]
# 线程缓存数
thread_cache_size = 100
-- 监控线程创建
SHOW STATUS LIKE 'Threads%';
-- Threads_created: 创建次数（越少越好）
-- Threads_cached: 缓存数
-- 命中率 = Threads_created / Connections * 100InnoDB Log Buffer
Log Buffer 作用
Log Buffer 用于缓存 redo log，定期刷盘。[mysqld]
# Log Buffer 大小
innodb_log_buffer_size = 64M
# 刷新策略
innodb_flush_log_at_trx_commit = 1
# 0: 每秒刷盘，事务提交不刷
# 1: 每次提交刷盘（最安全）
# 2: 每次提交写 OS 缓存，每秒刷盘
# 日志文件大小
innodb_log_file_size = 512M
innodb_log_files_in_group = 2Log Buffer 监控
-- 查看 Log Buffer 使用情况
SHOW ENGINE INNODB STATUS\G
-- Log 部分：
-- Log sequence number: 1234567890
-- Log buffered up to: 1234567890
-- Last checkpoint at: 1234500000
-- Pending checkpoint writes: 0内存调优实践
调优步骤
1. 查看物理内存
free -h
2. 计算可用内存
可用内存 = 物理内存 * 0.8
3. 分配 Buffer Pool
innodb_buffer_pool_size = 可用内存 * 0.7
4. 分配其他缓冲区
连接缓冲区 = 可用内存 * 0.1
5. 预留操作系统
操作系统 = 物理内存 * 0.2配置示例
# 16GB 内存服务器
[mysqld]
# InnoDB Buffer Pool（8GB）
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
# Log Buffer（64MB）
innodb_log_buffer_size = 64M
# 连接缓冲区（每连接）
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 1M
join_buffer_size = 4M
# 线程缓存
thread_cache_size = 100
# 临时表
tmp_table_size = 256M
max_heap_table_size = 256M
# 连接数限制
max_connections = 200内存计算公式
总内存需求 =
innodb_buffer_pool_size +
(sort_buffer_size + read_buffer_size + join_buffer_size) * max_connections +
innodb_log_buffer_size +
其他内存
示例：
8G (Buffer Pool) +
(2M + 2M + 4M) * 200 (连接) = 1.6G +
64M (Log Buffer) +
其他 ≈ 10G
16GB 服务器配置 200 连接是安全的内存监控
监控指标
-- 1. Buffer Pool 命中率
SELECT
(1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100 AS hit_rate
FROM information_schema.GLOBAL_STATUS;
-- 2. 内存使用情况
SELECT
VARIABLE_NAME,
VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME LIKE '%memory%'
OR VARIABLE_NAME LIKE '%buffer%';
-- 3. 临时表使用情况
SHOW STATUS LIKE 'Created_tmp%';
-- Created_tmp_tables: 创建总数
-- Created_tmp_disk_tables: 磁盘临时表数
-- 磁盘临时表占比 = Created_tmp_disk_tables / Created_tmp_tables
-- 理想值：< 10%Prometheus 监控
# MySQL 内存监控
groups:
- name: mysql_memory
rules:
- alert: LowBufferPoolHitRate
expr: (1 - mysql_global_status_innodb_buffer_pool_reads / mysql_global_status_innodb_buffer_pool_read_requests) * 100 < 95
for: 5m
labels:
severity: warning
annotations:
summary: "Buffer Pool 命中率低于 95%"
- alert: HighTemporaryTables
expr: mysql_global_status_created_tmp_disk_tables / mysql_global_status_created_tmp_tables > 0.1
for: 10m
labels:
severity: warning
annotations:
summary: "磁盘临时表占比过高"常见问题
问题 1：内存不足
症状：
- OOM Killer 杀死 MySQL
- 系统 swap 频繁
解决：
1. 减小 innodb_buffer_pool_size
2. 减小 max_connections
3. 减小连接缓冲区
4. 增加物理内存问题 2：Buffer Pool 命中率低
症状：
- 命中率 < 95%
- 磁盘 IO 高
解决：
1. 增大 innodb_buffer_pool_size
2. 优化 SQL，减少全表扫描
3. 添加合适的索引
4. 预热 Buffer Pool问题 3：临时表过多
症状：
- Created_tmp_disk_tables 占比高
- 查询慢
解决：
1. 增大 tmp_table_size 和 max_heap_table_size
2. 优化复杂查询
3. 添加索引避免文件排序最佳实践
配置检查清单
[ ] innodb_buffer_pool_size = 物理内存的 50-80%
[ ] innodb_buffer_pool_instances = buffer_pool_size / 1G
[ ] 连接缓冲区不要设置过大
[ ] tmp_table_size = max_heap_table_size
[ ] 预留 20% 内存给操作系统
[ ] 开启 Buffer Pool 持久化
[ ] 监控 Buffer Pool 命中率参考资料
- MySQL 官方文档 - InnoDB Buffer Pool
- 《高性能 MySQL》第 8 章：MySQL 服务器性能剖析
- MySQL 官方博客：InnoDB Buffer Pool 优化