Mysql中的常用调优参数用法及解读
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">写在开头</a></li><li><a href="#_label1">内存池相关</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_0">innodb_buffer_pool_size</a></li><li><a href="#_lab2_1_1">innodb_buffer_pool_instances</a></li><li><a href="#_lab2_1_2">innodb_log_buffer_size</a></li><ul class="third_class_ul"><li><a href="#_label3_1_2_0">写数据不能直接怼磁盘</a></li></ul></ul><li><a href="#_label2">写日志redo log & 刷盘相关</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_3">innodb_redo_log_capacity</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_2_4">innodb_flush_log_at_trx_commit</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_2_5">sync_binlog</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label3">后台 I/O、清理线程和并发限制</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_6">innodb_write_io_threads</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_3_7">innodb_read_io_threads</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_3_8">innodb_purge_threads</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label4">临时表全内存</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_9">temptable_max_ram</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_10">tmp_table_size / max_heap_table_size</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label5">最佳配置</a></li><ul class="second_class_ul"><li><a href="#_lab2_5_11">普通的web类后台管理系统</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label6">总结</a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>写在开头</h2><p>以下的每个参数详细拉开来讲都可以开一篇文章，篇幅所限，我这里会给出一些基本的描述以及建议的参数，详细了解的请自行百度</p>
<p><strong>知其然才能知其所以然</strong></p>
<p>如果只是把参数根据当前服务器的配置列出来，自然是最快完成任务的方式，但这种方式我们始终不知道是为什么要这么配置，故以下会先科普一些底层的知识，比如InnoDB 缓冲池，redo log 缓冲区， log buffer，redo log 文件这些东西到底是什么玩意，如果要完全理解可能需要系统的学习mysql底层的知识，因为背后牵扯到 InnoDB 存储引擎的内部机制：数据是怎么存储、怎么读取、怎么保证事务一致性的。</p>
<p>本文会尽可能用一些通俗的比喻来让你理解</p>
<p class="maodian"><a name="_label1"></a></p><h2>内存池相关</h2>
<div class="jb51code"><pre class="brush:sql;">innodb_buffer_pool_size
innodb_buffer_pool_instances
innodb_log_buffer_size
</pre></div>
<p class="maodian"><a name="_lab2_1_0"></a></p><h3>innodb_buffer_pool_size</h3>
<p>这是 InnoDB 缓冲池大小，作用就是把数据页、索引页、插入缓存、锁信息等都放到内存里，减少磁盘随机 I/O。</p>
<p>**建议：**分配为物理内存的 60%~75% 左右。</p>
<p>如果 buffer pool 太小，查询就频繁读磁盘，性能会雪崩。</p>
<ol><li>就像一座“大冰箱”，里面放着你常用的菜（数据页、索引页）。</li><li>查询数据时，如果“冰箱”里有（命中缓存），就直接从内存里取，快得飞起。</li><li>如果没有，就得下楼去超市（磁盘），再顺便搬进冰箱，下次就快了。</li></ol>
<p>innodb_buffer_pool_size = 冰箱有多大。</p>
<p class="maodian"><a name="_lab2_1_1"></a></p><h3>innodb_buffer_pool_instances</h3>
<p>这个参数是把大 buffer pool 分片，避免多线程并发访问时锁竞争。</p>
<p>MySQL 8 默认就会根据 innodb_buffer_pool_size 自动调整，一般每个 instance 至少要有 1G~2G。</p>
<p>**建议：**比如innodb_buffer_pool_size=24G，innodb_buffer_pool_instances就要为16左右，这样 24G/16 = 1.5G 每个实例，正好在推荐范围里。</p>
<p>如果实例数太少，大锁竞争严重；太多则浪费管理开销。</p>
<p>innodb_buffer_pool_instances = 冰箱有几个独立的格子，防止大家挤着抢同一个门。</p>
<p class="maodian"><a name="_lab2_1_2"></a></p><h3>innodb_log_buffer_size</h3>
<p>这是 redo log 缓冲区，事务在提交前会先写到 log buffer，然后再刷到 redo log 文件。</p>
<p>**建议：**默认一般是 16M 或 128M。如果太小事务中间就被迫刷盘。如果太大但是业务系统事务都很小（简单的单行 INSERT/UPDATE），太大没意义意义，可能浪费内存。</p>
<p>要讲清楚这个配置，就要科普另一个点</p>
<p class="maodian"><a name="_label3_1_2_0"></a></p><h4>写数据不能直接怼磁盘</h4>
<ul><li>事务更新数据时，MySQL 不能每条更新都立刻写磁盘，那样会被磁盘 I/O 拖死。</li><li>于是 InnoDB 使用 Write-Ahead Logging（先写日志，再写数据） 的套路。</li></ul>
<p><strong>Redo Log（重做日志）</strong></p>
<ul><li>就像一本“日记本”，你在里面记下：今天把账本第 8 页的金额加了 100。</li><li>这个日记本在磁盘上是循环写的 redo log 文件。</li><li>如果数据库突然崩了，重启时可以根据 redo log 把内存里没来得及写的数据“重做一遍”，保证事务不丢。</li></ul>
<p><strong>Redo Log Buffer（日志缓冲区）</strong></p>
<ul><li>写日记之前，先把草稿写在一个小白板（内存 buffer）。</li><li>等凑够一批或者事务提交了，再一次性抄到真正的日记本（redo log 文件）。这样减少了频繁刷盘。</li></ul>
<p>所以：</p>
<ul><li>innodb_log_buffer_size = 白板有多大。事务很大时，白板小了就得频繁往日记本抄，拖慢速度。</li><li>拿一个事务的完整流程举例</li></ul>
<div class="jb51code"><pre class="brush:sql;">UPDATE account SET balance = balance - 100 WHERE id = 1;
</pre></div>
<ol><li>InnoDB 在 Buffer Pool 找到 account 表里 id=1 的数据页（如果不在，就从磁盘读进来）。</li><li>在内存里修改数据页，balance 减 100。</li><li>把“修改过的数据”记录到 Redo Log Buffer（白板）。</li><li>事务提交时，把 redo log buffer 刷到磁盘上的 Redo Log 文件。</li><li>数据页什么时候刷回磁盘？不一定立刻，可能延迟（后台刷脏页）。</li></ol>
<p>这样做的好处：</p>
<ol><li>日志先落盘，保证崩溃后还能恢复。</li><li>数据页延迟落盘，提高性能。</li></ol>
<p>所以：innodb_log_buffer_size：白板大小，决定写日志时事务大不大会卡顿。</p>
<p class="maodian"><a name="_label2"></a></p><h2>写日志redo log & 刷盘相关</h2>
<div class="jb51code"><pre class="brush:sql;">innodb_redo_log_capacity
innodb_flush_log_at_trx_commit
innodb_flush_log_at_timeout
sync_binlog
</pre></div>
<p>这几个参数都是 “日志系统的节奏控制器”，决定了 MySQL 写日志的安全性 vs 性能 取舍。咱们还是延续“日记本”的类比来解释：</p>
<p class="maodian"><a name="_lab2_2_3"></a></p><h3>innodb_redo_log_capacity</h3>
<p>这相当于 redo log 文件的 总大小（循环写）。Redo log 记录了事务的修改操作，保证崩溃恢复。</p>
<p>它是循环写的，就像一本日记本，写满后会从头覆盖。</p>
<ul><li>容量大 → 能容纳更多脏页（dirty page），后台写盘压力小，适合写入量大的场景。</li><li>容量小 → 脏页必须频繁被刷回磁盘，否则 redo log 会被撑爆。</li></ul>
<p><strong>建议：</strong> 几 GB 到几十 GB 都可以，看磁盘和 workload。我一般配 4G，算比较中等偏大的设置了，OLTP（高并发事务）场景挺合适。普通web系统配置为1-2G就够</p>
<p class="maodian"><a name="_lab2_2_4"></a></p><h3>innodb_flush_log_at_trx_commit</h3>
<p>控制了事务提交时 redo log（重做日志）的刷盘策略，有3个值，</p>
<ul><li>0：每秒才统一刷一次盘。性能最好，掉电风险最大。</li><li>1（默认）：每次事务提交，都会把 redo log buffer 写到 操作系统缓存，再 fsync（真正落到磁盘）。最安全，最慢。</li><li>2：每次提交只写到 操作系统缓存，不强制 fsync。由操作系统自己决定何时刷到磁盘。安全性差一点，但性能好很多。<br /><strong>建议：</strong> 一般业务系统设置为2</li></ul>
<table><thead><tr><th>业务类型</th><th>推荐值</th><th>理由</th></tr></thead><tbody><tr><td>金融、支付、订单</td><td><strong>1</strong></td><td>数据不丢是底线</td></tr><tr><td>普通 Web 应用</td><td><strong>2</strong></td><td>性能与安全的折中</td></tr><tr><td>日志、监控、分析</td><td><strong>0</strong></td><td>可接受数据丢失</td></tr></tbody></table>
<p>这个一秒可以通过参数：<code>innodb_flush_log_at_timeout</code> 来配置，默认值是1</p>
<p class="maodian"><a name="_lab2_2_5"></a></p><h3>sync_binlog</h3>
<p>这是 binlog（归档日志）的同步策略。Binlog 是逻辑日志，用于复制和恢复。</p>
<ul><li>取值 1：每次事务提交都 fsync binlog → 最安全，但 I/O 压力大。</li><li>取值 0：事务提交时只是写到 OS 缓存，不强制落盘 → 崩溃可能丢 binlog。</li><li>取值 N (>1)：每 N 次事务才强制刷一次。</li></ul>
<p class="maodian"><a name="_label3"></a></p><h2>后台 I/O、清理线程和并发限制</h2>
<div class="jb51code"><pre class="brush:sql;">innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_purge_threads = 4
innodb_thread_concurrency = 0
</pre></div>
<p class="maodian"><a name="_lab2_3_6"></a></p><h3>innodb_write_io_threads</h3>
<ul><li>这是 后台写 I/O 线程数。</li><li>负责把内存里的 脏页（dirty page） 刷回磁盘。</li><li>负责写 doublewrite buffer、合并 insert buffer 等写操作。</li><li>线程越多 → 能同时提交更多写请求。</li><li>但如果磁盘（比如 SSD）并发能力有限，开太多线程也没用，还会增加调度开销。</li></ul>
<p><strong>建议：</strong> 4-16。在现代服务器 + SSD 场景，比如16 个写线程是比较激进的配置，适合高写入压力的系统，如果是HHD硬盘，设置为4足够，设置多了会增加线程调度开销。</p>
<p class="maodian"><a name="_lab2_3_7"></a></p><h3>innodb_read_io_threads</h3>
<ul><li>这是 后台读 I/O 线程数。</li><li>负责预读数据页（read-ahead）和处理用户线程发起的磁盘读请求。</li><li>线程数越多，能同时发起更多读请求。</li><li>同样受磁盘并发能力限制。</li></ul>
<p><strong>建议：</strong> 一般 4-8 足够。</p>
<p class="maodian"><a name="_lab2_3_8"></a></p><h3>innodb_purge_threads</h3>
<ul><li>这是 purge（清理）线程数。</li><li>purge 负责清理 undo log（事务提交后遗留的旧版本）。</li><li>如果 purge 太慢，undo log 会堆积，导致表膨胀、性能下降。</li><li>多线程 purge 可以更快回收空间、减少 undo 膨胀。</li></ul>
<p><strong>建议：</strong> 一般设置为2就够了，高并发场景可以考虑设置为4</p>
<p class="maodian"><a name="_label4"></a></p><h2>临时表全内存</h2>
<div class="jb51code"><pre class="brush:sql;">temptable_max_ram
tmp_table_size
max_heap_table_size
internal_tmp_mem_storage_engine
</pre></div>
<p>这几个参数都跟 临时表 有关，而临时表就是 MySQL 在执行 SQL 时的“临时工地”，用来放中间结果。</p>
<p>比如：GROUP BY、ORDER BY、DISTINCT、UNION、JOIN 等操作，如果不能直接用索引，就可能生成临时表。</p>
<p class="maodian"><a name="_lab2_4_9"></a></p><h3>temptable_max_ram</h3>
<ul><li>这是 MySQL 8 新引入的参数，专门限制 **TempTable 引擎（内存临时表）**的最大内存占用。</li><li>TempTable 是 MySQL 8 默认的内部临时表引擎（之前默认是 Memory 引擎）。</li><li>当单个临时表大小超过这个值时，会溢出到磁盘（存成 on-disk temporary table）。</li></ul>
<p class="maodian"><a name="_lab2_4_10"></a></p><h3>tmp_table_size / max_heap_table_size</h3>
<p>内存临时表的大小上限。复杂查询（GROUP BY、JOIN、ORDER BY）结果可能要放临时表。超过大小会落到磁盘 → 性能急剧下降。</p>
<p>这两个值建议设置为一样的，对齐限制，避免临时表因为两个参数不一致导致意外落盘或报错。</p>
<p class="maodian"><a name="_label5"></a></p><h2>最佳配置</h2>
<p class="maodian"><a name="_lab2_5_11"></a></p><h3>普通的web类后台管理系统</h3>
<p>这类系统的普遍情况是：很少有高并发，但存在很多复杂，多张表关联查询</p>
<ul><li>16G内存最佳配置</li></ul>
<div class="jb51code"><pre class="brush:sql;"># -------------------------
# InnoDB 缓冲池（核心参数）
# -------------------------
innodb_buffer_pool_size = 8G # 给 InnoDB 分配 50% 内存，留给系统和其他 MySQL 组件
innodb_log_buffer_size = 128M # 日志缓冲大小，适合复杂查询场景
innodb_max_dirty_pages_pct=50 # 控制 脏页（dirty page）在缓冲池中允许的最大比例。推荐 50~60，平衡刷盘压力和性能。
# -------------------------
# Redo Log & 刷盘策略（安全优先）
# -------------------------
innodb_redo_log_capacity = 1G # 合理大小，防止频繁刷新
innodb_flush_log_at_trx_commit = 2 # web系统配置为2足够
innodb_flush_log_at_timeout = 1 # 最多延迟 1 秒刷盘
# 默认200，如果SSD，调整为5000~20000
innodb_io_capacity=200
# -------------------------
# 临时表优化（多表关联查询关键）
# -------------------------
temptable_max_ram = 512M
tmp_table_size = 512M
max_heap_table_size = 512M
internal_tmp_mem_storage_engine = TempTable
# -------------------------
# 连接和缓存（低并发适度）
# -------------------------
max_connections = 200
thread_cache_size = 50
table_open_cache = 2000
table_definition_cache = 1000
open_files_limit = 65535
</pre></div>
<p class="maodian"><a name="_label6"></a></p><h2>总结</h2>
<p>以上为个人经验，希望能给大家一个参考，也希望大家多多支持琼殿技术社区。</p>
頁:
[1]