MySQL 核心配置项的相关说明与建议

用了这么多年的Mysql，一直有很多配置项并不知道用途是什么，也是时候研究研究了。

常用的配置项

所有配置项的作用可归为三类 ——内存分配（提升性能）、锁 / 事务（解决死锁）、并发 / 监控（保证稳定），介绍一些常用的配置项：

1.innodb_buffer_pool_size

InnoDB 的核心内存区域，缓存表数据、索引、锁信息、自适应哈希索引等，减少磁盘 IO，决定 InnoDB 性能的最关键参数，缓存命中率越高，查询 / 更新速度越快。

官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool.html

• 专用服务器：建议设置为物理内存的 50%~70%
• 非专用服务器：建议设置为物理内存的 30%~40%

2.innodb_buffer_pool_instances

拆分缓冲池，减少并发线程锁竞争，也就是把缓存区分成多个 “小缓存区”，多人同时查数据时不抢资源

官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool.html

3.query_cache_type/query_cache_size

缓存查询结果，但数据一变缓存就失效，高并发时反而拖慢速度

官方文档：https://dev.mysql.com/doc/refman/5.7/en/query-cache.html

查询缓存会引发严重锁竞争，性能收益远低于开销，5.7 版本仅保留功能但不建议使用

4.innodb_log_buffer_size

重做日志缓冲区，临时存储待刷盘的事务日志

官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_log_buffer_size

• 太小：大事务（如批量插入）时频繁刷磁盘，IO 高
• 太大：内存收益边际递减（超过 512M 无提升）
• 优化建议：上调至 64M（非专用）/128M（专用）

5.innodb_log_file_size

InnoDB 重做日志大小，影响事务性能和崩溃恢复

官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-redo-log.html

• 太小：日志频繁切换，IO 压力大，事务提交慢
• 太大：崩溃后恢复时间变长（1G 日志恢复约 1~2 分钟）
• 优化建议：专用服务器上调至 1G，非专用上调至 512M（修改后需删除旧 ib_logfile 文件重启）

6.innodb_read_io_threads/innodb_write_io_threads

InnoDB 读写 IO 线程数，提升并发 IO 处理能力

官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_read_io_threads

• 太小：多核服务器 IO 并发能力不足，查询 / 写入排队
• 太大：CPU 核心不足时线程切换开销增加

7.max_connections

MySQL 最大并发连接数

官方文档：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_connections

• 根据剩余内存调整，每个连接约占 1~2M 基础内存

8.max_allowed_packet

MySQL 接收 / 发送的最大数据包大小

官方文档：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet

• 太大：占用过多内存，增加网络传输风险（如数据包丢失）
• 太小：导入大 SQL / 传输大字段（如 BLOB）时报错
• 优化建议：mysqld 下调至 64M（非专用）/256M（专用），mysqldump 保持 500M 即可

9.slow_query_log/long_query_time

慢查询日志开关 / 慢查询阈值（秒）

官方文档：https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

死锁有关的

死锁相关的核心配置项，死锁排查、预防的关键要点

1.innodb_lock_wait_timeout

定义 InnoDB 行锁的等待超时时间（单位：秒），超时后事务会被中断并报错（1205 错误），避免事务无限等待锁；该配置不直接解决死锁，仅控制锁等待的超时时间，死锁会被 InnoDB 自动检测并回滚，无需等待该超时。

官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout

• 太小：正常业务的长事务易被中断，引发业务报错；
• 太大：锁等待时间过长，导致大量事务阻塞，连接占满、CPU / 内存飙升；

2.innodb_deadlock_detect

控制 InnoDB 是否开启死锁自动检测功能（默认开启）

官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_deadlock_detect

• 开启（默认）：InnoDB 会实时检测死锁，一旦发现立即回滚 “代价最小” 的事务（通常是修改行数少的），避免死锁持续；
• 关闭：仅在每秒数千个并发锁请求的极端场景（如秒杀），关闭可减少检测开销，但死锁会导致事务一直等待 innodb_lock_wait_timeout 超时，风险极高；

3.innodb_print_all_deadlocks

控制是否将所有死锁信息打印到 MySQL 错误日志（默认关闭）

官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks

• 关闭（默认）：仅能通过 SHOW ENGINE INNODB STATUS 手动查看最近一次死锁；
• 开启：所有死锁详情（涉及的事务、锁、SQL）都会写入错误日志，是排查死锁的核心配置；

4.transaction_isolation 

定义事务隔离级别：

• REPEATABLE READ（默认）：隔离性高，死锁概率略高；
• READ COMMITTED：隔离性稍低（允许幻读），但锁竞争减少，死锁概率大幅降低。