MySQL配置调优技巧

MySQL运维实战：从配置调优到SQL性能优化的完整攻略

作者简介：资深运维工程师，专注数据库性能优化8年，曾优化过千万级用户系统的MySQL集群。今天分享一些压箱底的MySQL调优技巧，帮你轻松应对生产环境的各种挑战。

开篇：一个真实的生产事故

上个月，我们公司的核心业务系统突然出现大面积超时，用户投诉电话不断。经过紧急排查，发现是MySQL服务器CPU飙升到99%，大量慢查询堆积。通过一系列配置调优和SQL优化，最终在30分钟内恢复了服务。

这次事故让我深刻认识到：MySQL调优不是纸上谈兵，而是运维工程师的核心竞争力。

Part 1: MySQL配置调优 - 让你的数据库飞起来

1.1 内存配置：合理分配是关键

# my.cnf 核心内存配置
[mysqld]
# 缓冲池大小：通常设为物理内存的70-80%
innodb_buffer_pool_size=8G

# 缓冲池实例数：提高并发性能
innodb_buffer_pool_instances=8

# 日志缓冲区：减少磁盘I/O
innodb_log_buffer_size=64M

# 查询缓存：对读密集型应用很重要（MySQL 8.0已移除）
query_cache_size=256M
query_cache_type=1

实战技巧：如何确定最佳的innodb_buffer_pool_size？

-- 查看缓冲池使用情况
SELECT
 ROUND(A.num*100.0/B.num,2)ASbuffer_pool_hit_rate
FROM
 (SELECTvariable_valueASnumFROMperformance_schema.global_statusWHEREvariable_name='Innodb_buffer_pool_read_requests') A,
 (SELECTvariable_valueASnumFROMperformance_schema.global_statusWHEREvariable_name='Innodb_buffer_pool_reads') B;

缓冲池命中率应该保持在99%以上。

1.2 连接与线程优化

# 连接相关配置
max_connections=2000
max_connect_errors=10000
connect_timeout=60
wait_timeout=28800
interactive_timeout=28800

# 线程缓存
thread_cache_size=64
thread_concurrency=16

运维经验分享：

?max_connections不是越大越好，要根据服务器配置合理设置

? 监控Threads_connected和Threads_running，避免连接数暴涨

1.3 InnoDB核心参数调优

# InnoDB核心配置
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=2
innodb_log_file_size=1G
innodb_log_files_in_group=2
innodb_max_dirty_pages_pct=75
innodb_io_capacity=2000
innodb_read_io_threads=8
innodb_write_io_threads=8

性能提升案例：
将innodb_flush_log_at_trx_commit从1改为2，TPS提升了40%（需要权衡数据安全性）。

Part 2: SQL性能优化 - 让慢查询无处遁形

2.1 索引优化：运维工程师的必杀技

建立复合索引的黄金法则：

-- 错误示例：每个字段单独建索引
CREATEINDEX idx_user_idONorders(user_id);
CREATEINDEX idx_statusONorders(status);
CREATEINDEX idx_create_timeONorders(create_time);

-- 正确示例：根据查询模式建立复合索引
CREATEINDEX idx_user_status_timeONorders(user_id, status, create_time);

索引使用情况分析：

-- 查找未使用的索引
SELECT
 object_schema,
 object_name,
 index_name,
 count_star,
 count_read,
 count_insert,
 count_update,
 count_delete
FROMperformance_schema.table_io_waits_summary_by_index_usage
WHEREindex_nameISNOT NULL
ANDcount_star=0
ANDobject_schema='your_database'
ORDERBYobject_schema, object_name;

2.2 查询优化实战案例

案例1：千万级数据表的分页优化

-- 传统分页（性能差）
SELECT*FROMuser_logs
WHEREuser_id=12345
ORDERBYcreated_atDESC
LIMIT1000000,20;

-- 优化后的分页（性能提升100倍）
SELECT*FROMuser_logs
WHEREuser_id=12345
ANDid

	

	案例2：子查询转JOIN优化

	
-- 慢查询：使用子查询
SELECT*FROMorders o
WHEREo.user_idIN(
SELECTu.idFROMusers uWHEREu.level='VIP'
);

-- 优化：转换为JOIN
SELECTo.*FROMorders o
INNERJOINusers uONo.user_id=u.id
WHEREu.level='VIP';

	

	2.3 慢查询日志分析

	开启慢查询日志：

	
slow_query_log=ON
slow_query_log_file= /var/log/mysql/slow.log
long_query_time=1
log_queries_not_using_indexes=ON

	

	使用pt-query-digest分析慢查询：

	
# 安装percona-toolkit
sudoapt-get install percona-toolkit

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > slow_query_report.txt

	

	Part 3: 高级调优技巧

	3.1 读写分离配置

	
# Python示例：使用PyMySQL实现读写分离
importpymysql
importrandom

classMySQLPool:
 def__init__(self):
   # 主库配置（写操作）
   self.master = {
     'host':'192.168.1.10',
     'user':'root',
     'password':'password',
     'database':'mydb'
    }
   
   # 从库配置（读操作）
   self.slaves = [
      {'host':'192.168.1.11','user':'root','password':'password','database':'mydb'},
      {'host':'192.168.1.12','user':'root','password':'password','database':'mydb'}
    ]
 
 defget_read_connection(self):
    slave_config = random.choice(self.slaves)
   returnpymysql.connect(**slave_config)
 
 defget_write_connection(self):
   returnpymysql.connect(**self.master)

	

	3.2 MySQL监控脚本

	
#!/bin/bash
# MySQL性能监控脚本

# 获取MySQL状态
mysql_status() {
  mysql -e"SHOW GLOBAL STATUS;"| grep -E"(Connections|Questions|Threads_running|Slow_queries)"
}

# 检查InnoDB状态
innodb_status() {
  mysql -e"SHOW ENGINE INNODB STATUSG"| grep -A 10"BUFFER POOL AND MEMORY"
}

# 获取当前运行的查询
current_queries() {
  mysql -e"SELECT * FROM information_schema.processlist WHERE COMMAND != 'Sleep' ORDER BY TIME DESC;"
}

echo"=== MySQL Performance Monitor ===="
echo"1. Connection Status:"
mysql_status
echo""
echo"2. InnoDB Buffer Pool:"
innodb_status
echo""
echo"3. Current Running Queries:"
current_queries

	

	3.3 分库分表策略

	水平分表示例：

	
-- 按时间分表
CREATE TABLEuser_logs_202501LIKEuser_logs;
CREATE TABLEuser_logs_202502LIKEuser_logs;

-- 按hash分表
CREATE TABLEuser_data_0LIKEuser_data;
CREATE TABLEuser_data_1LIKEuser_data;

-- 分表路由逻辑（Python）
def get_table_name(user_id, table_count=10):
 returnf"user_data_{user_id % table_count}"

	

	Part 4: 生产环境实战经验

	4.1 MySQL故障排查流程

	
# 1. 检查MySQL服务状态
systemctl status mysql

# 2. 查看错误日志
tail-f /var/log/mysql/error.log

# 3. 检查磁盘空间
df-h

# 4. 查看当前连接数
mysql -e"SHOW PROCESSLIST;"

# 5. 分析慢查询
mysql -e"SELECT * FROM information_schema.processlist WHERE TIME > 10;"

	

	4.2 备份恢复最佳实践

	
# 热备份脚本
#!/bin/bash
DATE=$(date+%Y%m%d_%H%M%S)
BACKUP_DIR="/backup/mysql"
DB_NAME="production_db"

# 使用mysqldump进行逻辑备份
mysqldump --single-transaction --routines --triggers 
 --master-data=2 --databases$DB_NAME
 | gzip >$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# 使用xtrabackup进行物理备份（推荐）
xtrabackup --backup --target-dir=$BACKUP_DIR/full_${DATE}

	

	4.3 高可用架构部署

	MySQL主从复制配置：

	主库配置：

	
[mysqld]
server-id=1
log-bin= mysql-bin
binlog-format= ROW
gtid-mode=ON
enforce-gtid-consistency=ON

	

	从库配置：

	
[mysqld]
server-id=2
relay-log= relay-bin
read-only=1

	

	Part 5: 性能调优工具箱

	5.1 必备监控工具

	1. Prometheus + Grafana监控

	
# prometheus.yml配置
global:
scrape_interval:15s

scrape_configs:
-job_name:'mysql'
 static_configs:
  -targets:['localhost:9104']

	

	2. pt-stalk故障诊断

	
# 当MySQL出现性能问题时自动收集诊断信息
pt-stalk --function=processlist --variable=Threads_running 
 --threshold=25 --match-command=Query --collect-oprofile 
 --collect-strace --collect-tcpdump

	

	5.2 压测工具使用

	sysbench压测示例：

	
# 准备测试数据
sysbench oltp_read_write --table-size=1000000 
 --mysql-host=localhost --mysql-user=root 
 --mysql-password=password --mysql-db=testdb prepare

# 执行压测
sysbench oltp_read_write --table-size=1000000 
 --mysql-host=localhost --mysql-user=root 
 --mysql-password=password --mysql-db=testdb 
 --threads=16 --time=300 run

	

	总结：MySQL调优的核心思路

	1.监控先行：建立完善的监控体系，及时发现问题

	2.配置为基：合理的参数配置是性能的基础

	3.索引为王：良好的索引设计解决80%的性能问题

	4.架构为本：读写分离、分库分表解决高并发问题

	5.持续优化：性能调优是一个持续的过程