MySQL的参数优化是一个非常重要且复杂的内容。在实际使用中，参数优化没有一套“放之四海而皆准”的，因为它严重依赖于具体硬件配置、数据库版本、工作负载类型（OLTP 还是 OLAP）以及数据规模。同时也需要对MySQL数据库的体系架构，应用场景有一定的了解。
MySQL参数可以按照：内存，用户链连接，线程，IO刷新策略，网络，死锁，日志，表等进行分类。下面介绍如何进行优化。
1.内存优化
内存优化是性能调优的重中之重，目标是尽可能减少磁盘I/O，提升速度。
| NO | 参数 | 说明 | 备注 |
|---|---|---|---|
| 1 | innodb_buffer_pool_size | 建议配置成，实际物理内存 50%~60% | content3 |
| 2 | read_buffer_size | 读缓存：1M ~4M | 初期2M，可以最大设置8M |
| 3 | sort_buffer_size | 读缓存：1M ~4M | 初期2M，可以最大设置8M |
| 4 | join_buffer_size | 连接缓存：1M ~4M read_buffer_size sort_buffer_size join_buffer_size 3个值对齐 | 初期2M，可以最大设置8M |
| 5 | sort_buffer_size | 2M~8M read_buffer_size的两倍 | 初期4M，可以最大设置16M |
| 6 | thread_stack | 每个连接线程被创建时，MySQL给它分配的内存大小。当MySQL创建一个新的连接线程时，需要给它分配一定大小的内存堆栈空间，以便存放客户端的请求的Query及自身的各种状态和处理信息。 | 保持默认值，或改成512kb |
| 7 | thread_cache_size | 连接临时缓存，一直保留在内存里 | 建议设置24~64 |
| 8 | innodb_log_buffer_size | redo log 刷新 | 建议缓存 4M-16M |
| 9 | binlog_cache_size | binlog刷新缓存 1M~32M |
2.临时表缓存优化
MySQL中临时表缓存，其核心目的是避免重复的昂贵操作（如磁盘 I/O、复杂计算），从而加速查询和数据处理。
临时表缓存内部使用场景如下：
相关调整的参数：
| NO | 参数 | 说明 | 备注 |
|---|---|---|---|
| 1 | tmp_table_size | 临时表缓存 大概32M~128M 既可以 | |
| 2 | max_heap_table_size | 创建的MEMORY表增长的最大大小。变量的值用于计算MEMORY表MAX_ROWS值 建议大于tmp_table_size | |
| 3 | internal_tmp_mem_storage_engine | 临时表缓存引擎（MEMORY InnoDB引擎） | 采用MEMORY引擎 |
| 4 | temptable_max_mmap | 内存映射的临时文件中分配的最大内存量 | 默认1G |
| 5 | temptable_max_ram | 数据存储到磁盘上之前可以占用的最大内存量 | 默认1G |
3.用户链接相关
控制客户端与数据库的连接资源。
| NO | 参数 | 说明 | 备注 |
|---|---|---|---|
| 1 | max_connections | 同时允许的最大客户端连接数 | 建议最多设置3000。 |
| 2 | max_user_connections | 任何给定MySQL用户帐户所允许的最大并发连接数 | 可以设置成0 或则 大概设置小于 max_connections 100个。 |
| 3 | max_connect_errors | max_connect_errors连续的连接请求在没有成功连接的情况下被中断后，服务器将阻止该主机进一步连接 | 建议设置1000000。 |
| 4 | skip_ssl | ssl通讯 | 需要关闭，默认开启。 |
| 5 | admin_address，admin_port | 建议设置 管理员账号链接操作， | 不受max_connection限制。 |
| 6 | mysqlx_port xmysql | 建议关闭 X Protocol协议提供一个非常类似MongoDB的shell服务，CRUD等各种类型。 | |
| 7 | default_authentication_plugin | 目前8.0默认是caching_sha2_password，建议采用原密码方式mysql_native_password | 跟应用配置和安全有关。需要实际情况去设置。 |
4.线程相关
管理处理后台线程相关的参数。
| NO | 参数 | 说明 | 备注 |
|---|---|---|---|
| 1 | innodb_purge_threads | purge线程有关 | cpu核数有关 建议小于等于 |
| 2 | innodb_read_io_threads | read线程有关 基本异步 | cpu核数有关 建议小于等于 |
| 3 | innodb_write_io_threads | write线程有关 基本异步 | cpu核数有关 建议小于等于 |
| 4 | innodb_page_cleaners | page线程有关 基本异步 | 小于8核，建议默认，cpu核数大于等于16 可以使16/2 |
| 5 | innodb_buffer_pool_instances | 每个内存保证最少1G分配InnoDB缓冲池划分的区域数，设置innodb_buffer_pool_instances，使每个实例至少使用1gb。 | cpu核数有关 建议小于等于，建议小于16核 |
5.IO刷新策略
IO刷新策略是性能和数据安全之间最关键的权衡参数。
| NO | 参数 | 说明 | 备注 |
|---|---|---|---|
| 1 | innodb_lru_scan_depth | 数据数据的算法深度 默认1024 | 建议改成512.但IO要求高的情况下默认值下 出现mysql系统卡主的现象，导致所有服务等待中 flush net 等执行info日志的时候 需要更改 512，256 |
| 2 | innodb_io_capacity | 刷新脏页的数量，跟系统可以支持多大的 IOPS有关 | 建议SAS 200~1000 ，SSD 2000~5000 ，PCI-E 10000-50000 https://dev.mysql.com/doc/refman/8.0/en/innodb-configuring-io-capacity.html |
| 3 | innodb_io_capacity_max | 限制一次的刷脏数量不能超过最高IOPS值 | 参考innodb_io_capacity值 |
| 4 | innodb_adaptive_hash_index | 自适应哈希索引，系统自动控制 出现 flush 耗时比较长的日志的时候 | 建议关闭 |
| 5 | innodb_flush_log_at_trx_commit | 刷新到磁盘的重做日志文件Redo，如果不设置1，突然服务关闭有可能会丢失数据 | 建议改成1 |
| 6 | sync_binlog | binlog落盘参数，建议保持1，但从库延迟非常大的时候可以临时调整0。会把对应的日志 buffer 刷新到对应的实际日志文件里 | 建议设置1 |
| 7 | innodb_redo_log_capacity | Redo日志文件占用的磁盘空间 | 默认值1G（8.0.28高版本才配置的参数） |
6.网络相关
MySQL服务和服务器，客户端之间传输的MySQL内部网络配置参数。
| NO | 参数 | 说明 | 备注 |
|---|---|---|---|
| 1 | max_allowed_packet | 网络传输包限制大小，128M,512M,1G 设置 | 千兆建议512M，万兆建议1G |
| 2 | slave_max_allowed_packet | slave sql线程可以处理的最大数据包大小由参数slave_max_allowed_packet控制。这是限制binlog event大小 | 千兆建议512M，万兆建议1G。跟max_allowed_packet对齐 |
| 3 | slave_pending_jobs_size_max | 当前需要执行事件所需的内存大小，需要大于主库max_allowed_packet的大小 | |
| 4 | net_read_timeout | 用于中断读数据之前等待连接的其它数据的秒数 | 默认不更改 |
| 5 | slave_net_timeout | 复制心跳网络超时参数 | 默认不更改 |
7.死锁相关
MySQL 死锁是一个非常重要且常见的并发控制问题，死锁参数有利于合理控制释放资源。
| NO | 参数 | 说明 | 备注 |
|---|---|---|---|
| 1 | innodb_deadlock_detect | 发起死锁检测，主动回滚一条事务，让其他事务继续执行,但会影响部分性能。 | 建议开启 |
| 2 | innodb_print_all_deadlocks | 打印死锁信息到error日志。因为show engine innodb status是最新数据 | 建议开启 |
| 3 | innodb_lock_wait_timeout | 等待锁的事件超时时间 考虑业务设置。 | 建议3s~10S |
8. 日志相关
MySQL 的日志体系是其核心架构中至关重要的一部分，它确保了数据库的持久性（Durability）、一致性（Consistency），并支撑了数据恢复、主从复制和故障排查等关键功能。
| NO | 参数 | 说明 | 备注 |
|---|---|---|---|
| 1 | log_error_verbosity | error日志记录。必须设置为3. | 必须设置为3. |
| 2 | slow_query_log | 慢日志开启 | 必须开启 |
| 3 | long_query_time | 慢日志时间间隔 | 建议设置1s~3S |
| 4 | log_bin | mysql binlog开启 | 必须开启 |
| 5 | binlog_format | binlog记入模式 | binlog格式必须为ROW模式 |
| 6 | expire_logs_days | binlog硬盘保留时间，单位填 | 建议7天 |
| 7 | binlog_expire_logs_seconds | binlog硬盘保留时间，单位秒 | 604800 （7天） |
9.表参数
MySQL 表参数是对于优化表的行为、性能和数据存储方式至关重要。这些参数主要针对使用InnoDB存储引擎的表。
| NO | 参数 | 说明 | 备注 |
|---|---|---|---|
| 1 | table_open_cache | 所有线程打开的表的数量。增加这个值会增加mysqld需要的文件描述符的数量。通过检查Opened_tables状态变量来检查是否需要增加表缓存。 | 建议初始值3000值 |
| 2 | table_definition_cache | 就是控制总frm文件的数量，还是个hash表，内部维护。 | 建议初始值3000 |
| 3 | open_files_limit | mysqld从操作系统可使用的文件描述符的数量， | 建议设置为65535 /etc/ |
| 4 | innodb_open_files | innodb 操作文件句柄的数量 | 建议设置为65535 /etc/security/limits.conf 建议设置对齐 |
10.其他参数
除此之外，控制和影响整体性能的参数。
| NO | 参数 | 说明 | 备注 |
|---|---|---|---|
| 1 | innodb_thread_concurrency | 并发数限制参数，cpu无压力的时候不建议设置 | 建议0 64~128值 左右开始调节，cpu越高可以往上提升。或交给操作系统控制，值为0 |
| 2 | max_execution_time | 表示执行select语句的超时时间，看压力太大的时候设置，其他采取默认值 | 建议默认 |
| 3 | lower_case_table_names | 大小写敏感。为0时表示区分大小写，为1时，表示将名字转化为小写后存储，不区分大小写。 | 无特殊需求，初始化必须设置1 |
总结
按照个人经验，MySQL参数的优化理念：
- 理解而非复制：深刻理解参数背后的原理，而不是复制粘贴配置。
- 同时循序渐进，对于不确定参数，可以适当的调整。在进行观察。
- 很多参数是在性能和数据安全性/一致性之间做权衡。
当然前提是，必须对MySQL的体系架构有一定的理解。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
