MYSQL核心参数说明(偏OLTP)

一、中型 MySQL OLTP 系统典型指标参考值

基于：16～32 GB 内存 / 8～16 Core / SSD / InnoDB / 主库

指标	常见范围	说明
QPS	2k ～ 8k	含 SELECT/INSERT/UPDATE/DELETE
TPS	500 ～ 2k	通常 ≈ QPS ÷ (2~4条SQL/事务)
活跃并发线程	8 ～ 30	SHOW GLOBAL STATUS LIKE 'Threads_running'
前端连接数	200 ～ 800	来自连接池，≠ 并发工作线程
峰值 QPS	偶尔 10k～12k	通常已接近扩容线

二、基础必须关注的参数

1、内存相关

innodb_buffer_pool_size = 物理内存的 50%~70%

例：16G 服务器

innodb_buffer_pool_size = 10G

innodb_buffer_pool_instances = 4~16 （通常 = buffer_pool_size/1G，但 ≤ 64）

✅ 作用：减少锁竞争，提升 InnoDB 性能

2、连接控制

max_connections = 500~2000

max_connect_errors = 100000

wait_timeout = 300

interactive_timeout = 300

✅ 避免 idle 连接打满 MySQL

✅ 应用必须使用连接池

3️、InnoDB IO & 事务

innodb_flush_log_at_trx_commit = 1

sync_binlog = 1

innodb_log_file_size = 512M~2G

innodb_log_files_in_group = 2

✅ 减少 redo log 切换频率

4、临时表 / 排序

tmp_table_size = 64M

max_heap_table_size = 64M

sort_buffer_size = 2M~4M

join_buffer_size = 2M~4M

三、Binlog/复制（主从）

server-id = 唯一ID

log-bin = mysql-bin

binlog_format = ROW

expire_logs_days = 7

# MySQL 8.0 推荐：

binlog_expire_logs_seconds = 604800

✅ ROW 格式 + 从库并行复制更安全

四、MySQL 8.0 建议补充

innodb_dedicated_server = ON

👉 让 InnoDB 自动根据内存设置 buffer_pool / log_file

sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

lower_case_table_names = 1 # Linux下如需忽略大小写（创建实例前定！）

五、安全 & 运维建议

slow_query_log = ON

long_query_time = 1

log_queries_not_using_indexes = OFF

max_allowed_packet = 64M

注：

-- 临时开启

SET GLOBAL log_queries_not_using_indexes = ON;

-- 跑一段时间 / 重现业务

-- 然后马上关

SET GLOBAL log_queries_not_using_indexes = OFF;