MySQL 生产环境性能优化与故障排查

本文基于生产级 InnoDB 配置规范，全流程附带配置代码、排查命令、校验 SQL，覆盖参数优化、故障定位、问题修复，可直接落地生产环境。

一、生产环境核心配置优化

所有配置均写入 MySQL 配置文件/etc/my.cnf或/etc/mysql/my.cnf，修改后重启服务生效。

1.1 核心基础配置

# InnoDB缓冲池（核心）
innodb_buffer_pool_size = 40G
# Redo日志单文件大小
innodb_log_file_size = 2G
# 事务日志刷新策略
innodb_flush_log_at_trx_commit = 2
# 最大并发连接数
max_connections = 1000
# 线程缓存大小
thread_cache_size = 100

配置校验代码

-- 查看缓冲池配置
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- 查看连接数配置
SHOW VARIABLES LIKE 'max_connections';
-- 查看日志刷新策略
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

---

1.2 查询性能优化

# 内存临时表大小
tmp_table_size = 128M
# 内存堆表大小
max_heap_table_size = 128M
# 排序缓冲区
sort_buffer_size = 4M
# 表连接缓冲区
join_buffer_size = 8M

配置校验代码

SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW VARIABLES LIKE 'join_buffer_size';

---

1.3 日志与监控配置

# 开启慢查询日志
slow_query_log = ON
# 慢查询阈值（秒）
long_query_time = 1
# 错误日志路径
log_error = /var/log/mysql/error.log
# 二进制日志格式
binlog_format = ROW
# 日志自动清理天数
expire_logs_days = 7

日志查看 / 校验代码

-- 查看慢查询状态
SHOW VARIABLES LIKE 'slow_query_log';
-- 查看binlog格式
SHOW VARIABLES LIKE 'binlog_format';

# 查看错误日志
tail -f /var/log/mysql/error.log
# 分析慢查询日志
mysqldumpslow -s t /var/log/mysql/slow.log

---

1.4 InnoDB 高级优化

# InnoDB IO吞吐量
innodb_io_capacity = 2000
# 日志刷新方式
innodb_flush_method = O_DIRECT
# 并发线程控制
innodb_thread_concurrency = 0
# 自增锁模式
innodb_autoinc_lock_mode = 2

高级配置校验代码

SHOW VARIABLES LIKE 'innodb_io_capacity';
SHOW VARIABLES LIKE 'innodb_flush_method';
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

---

二、生产高频故障排查

2.1 连接数溢出故障

现象：应用报错Too many connections

排查代码

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
-- 查看最大连接数配置
SHOW VARIABLES LIKE 'max_connections';
-- 查看所有连接详情
SHOW PROCESSLIST;

解决方案

1. 调大max_connections（不超过 1000）
2. 优化应用连接池，关闭闲置连接

---

2.2 慢查询导致性能卡顿

现象：CPU / IO 高、接口响应超时

排查代码

-- 查看慢查询开启状态
SHOW VARIABLES LIKE 'slow_query_log';
-- 分析SQL执行计划
EXPLAIN SELECT * FROM order_table WHERE user_id = 1001;
-- 查看当前慢查询列表
SHOW FULL PROCESSLIST WHERE Time > 1;

解决方案

-- 缺失索引修复
CREATE INDEX idx_user_id ON order_table(user_id);

---

2.3 InnoDB 缓冲池不足

现象：磁盘 IO 飙升、缓存命中率低

排查代码

-- 查看InnoDB状态（重点看Buffer pool hit rate）
SHOW ENGINE INNODB STATUS;
-- 查看缓冲池命中率
SELECT
ROUND((PAGES_READ / (PAGES_READ + PAGES_WRITTEN)) * 100, 2) AS hit_rate
FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;

解决方案

调整innodb_buffer_pool_size为物理内存50%-75%。

---

2.4 Redo 日志频繁切换

现象：事务提交延迟、IO 异常高

排查代码

-- 查看redo日志大小
SHOW VARIABLES LIKE 'innodb_log_file_size';
-- 查看日志切换频率
SHOW STATUS LIKE 'Innodb_log_waits';

解决方案

调大innodb_log_file_size = 2G。

---

2.5 IO 性能瓶颈

现象：InnoDB 读写缓慢、磁盘满负载

排查代码

SHOW VARIABLES LIKE 'innodb_io_capacity';
SHOW VARIABLES LIKE 'innodb_flush_method';

解决方案

1. innodb_io_capacity设为磁盘 IOPS 的 70%-80%
2. 固定innodb_flush_method = O_DIRECT

---

2.6 排序 / JOIN / 临时表性能差

现象：GROUP BY/ORDER BY/JOIN 极慢

排查代码

-- 查看临时表使用情况
SHOW STATUS LIKE 'Created_tmp_disk_tables';
SHOW STATUS LIKE 'Created_tmp_tables';
-- 查看排序使用情况
SHOW STATUS LIKE 'Sort_merge_passes';

解决方案

1. 调大sort_buffer_size/join_buffer_size
2. 优化 SQL 并添加索引

-- 排序索引优化
CREATE INDEX idx_create_time ON order_table(create_time);

---

2.7 日志磁盘占满

现象：MySQL 写入失败、服务宕机

排查代码

# 查看日志磁盘占用
du -sh /var/log/mysql/
# 查看binlog文件
ls -lh /var/lib/mysql/mysql-bin.*

解决方案

配置expire_logs_days = 7自动清理 binlog。

---

2.8 主从复制异常

现象：主从延迟、复制中断

排查代码

-- 查看主从状态
SHOW SLAVE STATUS\G;
-- 查看binlog格式
SHOW VARIABLES LIKE 'binlog_format';

解决方案

固定binlog_format = ROW，修复数据一致性。

---

三、生产巡检一键脚本

#!/bin/bash
# MySQL生产巡检脚本
echo "===== MySQL配置巡检 ====="
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -e "SHOW VARIABLES LIKE 'max_connections';"
mysql -e "SHOW VARIABLES LIKE 'slow_query_log';"

echo "===== 连接数巡检 ====="
mysql -e "SHOW STATUS LIKE 'Threads_connected';"

echo "===== 缓冲池命中率 ====="
mysql -e "SELECT ROUND((PAGES_READ/(PAGES_READ+PAGES_WRITTEN))*100,2) AS hit_rate FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;"