MySQL性能调优my.cnf详解

MySQL 性能调优 my.cnf 配置详解

MySQL 的性能很大程度上取决于其配置文件 my.cnf（或 Windows 下的 my.ini）中的参数设置。以下是一份详细的配置说明，适用于通用场景，实际使用时请根据服务器硬件配置和具体业务负载进行调整。

客户端与服务器基础配置

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1

• server-id：在复制环境中标识服务器，单机或主库通常设为 1。

连接与网络相关

skip-name-resolve
# skip-networking
back_log = 600
max_connections = 1000
max_connect_errors = 6000
open_files_limit = 65535

• skip-name-resolve：禁用 DNS 解析，可提升连接速度。启用后，授权必须使用 IP 地址而非主机名。
• back_log：连接请求队列长度，在高并发场景下可适当增加。
• max_connections：最大并发连接数。设置过高可能导致内存耗尽，需结合系统资源。
• max_connect_errors：同一主机连续错误连接数上限，超过则禁止连接。
• open_files_limit：MySQL 可打开的文件描述符数量，需大于 table_open_cache 设置。

缓存与缓冲区配置

table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
query_cache_size = 8M
query_cache_limit = 2M
key_buffer_size = 4M

• table_open_cache：表文件描述符缓存大小，影响表打开速度。
• tmp_table_size 与 max_heap_table_size：内存临时表大小限制，超过则转为磁盘表。
• thread_cache_size：线程缓存大小，减少频繁创建和销毁线程的开销。
• query_cache_size：查询缓存大小。注意：在 MySQL 5.7.20 后已弃用，8.0 中已移除。若使用旧版本，需评估查询重复率决定是否启用。
• key_buffer_size：MyISAM 引擎的索引缓冲区大小，若仅使用 InnoDB，可设置较小值。

InnoDB 存储引擎配置

default-storage-engine = InnoDB
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

• innodb_buffer_pool_size：最重要的参数之一，建议设置为可用物理内存的 70%-80%。
• innodb_flush_log_at_trx_commit：
  • 0：每秒刷写日志，性能最高，宕机可能丢失约 1 秒数据。
  • 1：每次提交都刷写日志，最安全，性能最低。
  • 2：每次提交写日志，但每秒刷盘，在操作系统崩溃时可能丢失数据。平衡安全与性能的常用选择。
• innodb_log_file_size：重做日志文件大小，设置过大会增加恢复时间。

日志与事务配置

transaction_isolation = REPEATABLE-READ
log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30
log_error = /data/mysql/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql-slow.log
performance_schema = 0
explicit_defaults_for_timestamp
# lower_case_table_names = 1
skip-external-locking

• transaction_isolation：事务隔离级别，默认为 REPEATABLE-READ。
• binlog_format：二进制日志格式，mixed 是 STATEMENT 和 ROW 的混合模式。
• slow_query_log：开启慢查询日志，便于性能分析。
• lower_case_table_names：表名大小写敏感设置，在跨平台迁移时需特别注意。

其他配置

ft_min_word_len = 4
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
interactive_timeout = 28800
wait_timeout = 28800

• bulk_insert_buffer_size：MyISAM 引擎批量插入缓存。
• interactive_timeout 与 wait_timeout：连接空闲超时时间，单位秒。防止大量空闲连接占用资源。

工具专用配置段

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

这些配置段仅为 mysqldump 或 myisamchk 工具运行时使用，不影响 MySQL 服务器本身。

配置建议与注意事项

1. 循序渐进：每次只修改少量参数，观察效果后再做进一步调整。
2. 监控先行：调整前、后使用 SHOW GLOBAL STATUS、SHOW ENGINE INNODB STATUS 等命令监控数据库状态。
3. 硬件相关：innodb_buffer_pool_size、max_connections 等关键参数必须与服务器内存容量匹配。
4. 版本差异：不同 MySQL 版本（如 5.7 与 8.0）的默认值和有效参数可能不同，请以官方文档为准。
5. 生产环境：任何配置修改在应用到生产环境前，都应在测试环境充分验证。