MySQL优化指南

1. 系统层面优化

1.1 内核参数调整

编辑 /etc/sysctl.conf：

# 增加TCP连接数
net.ipv4.tcp_max_syn_backlog = 65535
net.core.somaxconn = 65535

# 内存相关
vm.swappiness = 10
vm.dirty_ratio = 60
vm.dirty_background_ratio = 5

# 文件描述符
fs.file-max = 65535

应用修改：sysctl -p

1.2 文件系统优化

• 使用XFS或ext4文件系统
• 挂载选项添加noatime,nodiratime,data=writeback

2. MySQL配置优化

2.1 基础配置 (/etc/my.cnf 或 /etc/mysql/my.cnf)

[mysqld]
# 基础设置
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0

# 连接设置
max_connections = 1000
back_log = 300
wait_timeout = 300
interactive_timeout = 300

# 缓冲区设置
key_buffer_size = 256M
query_cache_size = 0  # MySQL 8.0已移除
query_cache_type = 0

# InnoDB设置
innodb_buffer_pool_size = 4G  # 建议为物理内存的50-70%
innodb_buffer_pool_instances = 8  # 每个实例至少1GB
innodb_log_file_size = 512M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 1  # 重要数据安全
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON
innodb_thread_concurrency = 0
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# 其他优化
tmp_table_size = 64M
max_heap_table_size = 64M
table_open_cache = 4000
thread_cache_size = 100

3. 查询优化

3.1 索引优化

• 为WHERE、JOIN、ORDER BY字段添加索引
• 使用复合索引时遵循最左前缀原则
• 避免在索引列上使用函数或计算

3.2 EXPLAIN分析

EXPLAIN SELECT * FROM users WHERE username = 'test';

3.3 慢查询日志

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

4. 维护与监控

4.1 定期优化表

OPTIMIZE TABLE table_name;
ANALYZE TABLE table_name;

4.2 监控工具

• SHOW STATUS / SHOW VARIABLES
• SHOW PROCESSLIST
• MySQL Enterprise Monitor
• Percona Monitoring and Management (PMM)