寫在開頭

以下的每個(gè)參數(shù)詳細(xì)拉開來講都可以開一篇文章，篇幅所限，我這里會(huì)給出一些基本的描述以及建議的參數(shù)，詳細(xì)了解的請自行百度

知其然才能知其所以然

如果只是把參數(shù)根據(jù)當(dāng)前服務(wù)器的配置列出來，自然是最快完成任務(wù)的方式，但這種方式我們始終不知道是為什么要這么配置，故以下會(huì)先科普一些底層的知識(shí)，比如InnoDB 緩沖池，redo log 緩沖區(qū)， log buffer，redo log 文件這些東西到底是什么玩意，如果要完全理解可能需要系統(tǒng)的學(xué)習(xí)mysql底層的知識(shí)，因?yàn)楸澈鬆砍兜?InnoDB 存儲(chǔ)引擎的內(nèi)部機(jī)制：數(shù)據(jù)是怎么存儲(chǔ)、怎么讀取、怎么保證事務(wù)一致性的。

本文會(huì)盡可能用一些通俗的比喻來讓你理解

內(nèi)存池相關(guān)

innodb_buffer_pool_size
innodb_buffer_pool_instances
innodb_log_buffer_size

innodb_buffer_pool_size

這是 InnoDB 緩沖池大小，作用就是把數(shù)據(jù)頁、索引頁、插入緩存、鎖信息等都放到內(nèi)存里，減少磁盤隨機(jī) I/O。

**建議：**分配為物理內(nèi)存的 60%~75% 左右。

如果 buffer pool 太小，查詢就頻繁讀磁盤，性能會(huì)雪崩。

1. 就像一座“大冰箱”，里面放著你常用的菜（數(shù)據(jù)頁、索引頁）。
2. 查詢數(shù)據(jù)時(shí)，如果“冰箱”里有（命中緩存），就直接從內(nèi)存里取，快得飛起。
3. 如果沒有，就得下樓去超市（磁盤），再順便搬進(jìn)冰箱，下次就快了。

innodb_buffer_pool_size = 冰箱有多大。

innodb_buffer_pool_instances

這個(gè)參數(shù)是把大 buffer pool 分片，避免多線程并發(fā)訪問時(shí)鎖競爭。

MySQL 8 默認(rèn)就會(huì)根據(jù) innodb_buffer_pool_size 自動(dòng)調(diào)整，一般每個(gè) instance 至少要有 1G~2G。

**建議：**比如innodb_buffer_pool_size=24G，innodb_buffer_pool_instances就要為16左右，這樣 24G/16 = 1.5G 每個(gè)實(shí)例，正好在推薦范圍里。

如果實(shí)例數(shù)太少，大鎖競爭嚴(yán)重；太多則浪費(fèi)管理開銷。

innodb_buffer_pool_instances = 冰箱有幾個(gè)獨(dú)立的格子，防止大家擠著搶同一個(gè)門。

innodb_log_buffer_size

這是 redo log 緩沖區(qū)，事務(wù)在提交前會(huì)先寫到 log buffer，然后再刷到 redo log 文件。

**建議：**默認(rèn)一般是 16M 或 128M。如果太小事務(wù)中間就被迫刷盤。如果太大但是業(yè)務(wù)系統(tǒng)事務(wù)都很?。ê唵蔚膯涡?INSERT/UPDATE），太大沒意義意義，可能浪費(fèi)內(nèi)存。

要講清楚這個(gè)配置，就要科普另一個(gè)點(diǎn)

寫數(shù)據(jù)不能直接懟磁盤

• 事務(wù)更新數(shù)據(jù)時(shí)，MySQL 不能每條更新都立刻寫磁盤，那樣會(huì)被磁盤 I/O 拖死。
• 于是 InnoDB 使用 Write-Ahead Logging（先寫日志，再寫數(shù)據(jù)） 的套路。

Redo Log（重做日志）

• 就像一本“日記本”，你在里面記下：今天把賬本第 8 頁的金額加了 100。
• 這個(gè)日記本在磁盤上是循環(huán)寫的 redo log 文件。
• 如果數(shù)據(jù)庫突然崩了，重啟時(shí)可以根據(jù) redo log 把內(nèi)存里沒來得及寫的數(shù)據(jù)“重做一遍”，保證事務(wù)不丟。

Redo Log Buffer（日志緩沖區(qū)）

• 寫日記之前，先把草稿寫在一個(gè)小白板（內(nèi)存 buffer）。
• 等湊夠一批或者事務(wù)提交了，再一次性抄到真正的日記本（redo log 文件）。這樣減少了頻繁刷盤。

所以：

• innodb_log_buffer_size = 白板有多大。事務(wù)很大時(shí)，白板小了就得頻繁往日記本抄，拖慢速度。
• 拿一個(gè)事務(wù)的完整流程舉例

UPDATE account SET balance = balance - 100 WHERE id = 1;

1. InnoDB 在 Buffer Pool 找到 account 表里 id=1 的數(shù)據(jù)頁（如果不在，就從磁盤讀進(jìn)來）。
2. 在內(nèi)存里修改數(shù)據(jù)頁，balance 減 100。
3. 把“修改過的數(shù)據(jù)”記錄到 Redo Log Buffer（白板）。
4. 事務(wù)提交時(shí)，把 redo log buffer 刷到磁盤上的 Redo Log 文件。
5. 數(shù)據(jù)頁什么時(shí)候刷回磁盤？不一定立刻，可能延遲（后臺(tái)刷臟頁）。

這樣做的好處：

1. 日志先落盤，保證崩潰后還能恢復(fù)。
2. 數(shù)據(jù)頁延遲落盤，提高性能。

所以：innodb_log_buffer_size：白板大小，決定寫日志時(shí)事務(wù)大不大會(huì)卡頓。

寫日志redo log & 刷盤相關(guān)

innodb_redo_log_capacity
innodb_flush_log_at_trx_commit 
innodb_flush_log_at_timeout
sync_binlog

這幾個(gè)參數(shù)都是 “日志系統(tǒng)的節(jié)奏控制器”，決定了 MySQL 寫日志的安全性 vs 性能 取舍。咱們還是延續(xù)“日記本”的類比來解釋：

innodb_redo_log_capacity

這相當(dāng)于 redo log 文件的 總大小（循環(huán)寫）。Redo log 記錄了事務(wù)的修改操作，保證崩潰恢復(fù)。

它是循環(huán)寫的，就像一本日記本，寫滿后會(huì)從頭覆蓋。

• 容量大 → 能容納更多臟頁（dirty page），后臺(tái)寫盤壓力小，適合寫入量大的場景。
• 容量小 → 臟頁必須頻繁被刷回磁盤，否則 redo log 會(huì)被撐爆。

建議： 幾 GB 到幾十 GB 都可以，看磁盤和 workload。我一般配 4G，算比較中等偏大的設(shè)置了，OLTP（高并發(fā)事務(wù)）場景挺合適。普通web系統(tǒng)配置為1-2G就夠

innodb_flush_log_at_trx_commit

控制了事務(wù)提交時(shí) redo log（重做日志）的刷盤策略，有3個(gè)值，

• 0：每秒才統(tǒng)一刷一次盤。性能最好，掉電風(fēng)險(xiǎn)最大。
• 1（默認(rèn)）：每次事務(wù)提交，都會(huì)把 redo log buffer 寫到 操作系統(tǒng)緩存，再 fsync（真正落到磁盤）。最安全，最慢。
• 2：每次提交只寫到 操作系統(tǒng)緩存，不強(qiáng)制 fsync。由操作系統(tǒng)自己決定何時(shí)刷到磁盤。安全性差一點(diǎn)，但性能好很多。
  建議： 一般業(yè)務(wù)系統(tǒng)設(shè)置為2

這個(gè)一秒可以通過參數(shù)：innodb_flush_log_at_timeout 來配置，默認(rèn)值是1

sync_binlog

這是 binlog（歸檔日志）的同步策略。Binlog 是邏輯日志，用于復(fù)制和恢復(fù)。

• 取值 1：每次事務(wù)提交都 fsync binlog → 最安全，但 I/O 壓力大。
• 取值 0：事務(wù)提交時(shí)只是寫到 OS 緩存，不強(qiáng)制落盤 → 崩潰可能丟 binlog。
• 取值 N (>1)：每 N 次事務(wù)才強(qiáng)制刷一次。

后臺(tái) I/O、清理線程和并發(fā)限制

innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_purge_threads = 4
innodb_thread_concurrency = 0

innodb_write_io_threads

• 這是 后臺(tái)寫 I/O 線程數(shù)。
• 負(fù)責(zé)把內(nèi)存里的 臟頁（dirty page） 刷回磁盤。
• 負(fù)責(zé)寫 doublewrite buffer、合并 insert buffer 等寫操作。
• 線程越多 → 能同時(shí)提交更多寫請求。
• 但如果磁盤（比如 SSD）并發(fā)能力有限，開太多線程也沒用，還會(huì)增加調(diào)度開銷。

建議： 4-16。在現(xiàn)代服務(wù)器 + SSD 場景，比如16 個(gè)寫線程是比較激進(jìn)的配置，適合高寫入壓力的系統(tǒng)，如果是HHD硬盤，設(shè)置為4足夠，設(shè)置多了會(huì)增加線程調(diào)度開銷。

innodb_read_io_threads

• 這是 后臺(tái)讀 I/O 線程數(shù)。
• 負(fù)責(zé)預(yù)讀數(shù)據(jù)頁（read-ahead）和處理用戶線程發(fā)起的磁盤讀請求。
• 線程數(shù)越多，能同時(shí)發(fā)起更多讀請求。
• 同樣受磁盤并發(fā)能力限制。

建議： 一般 4-8 足夠。

innodb_purge_threads

• 這是 purge（清理）線程數(shù)。
• purge 負(fù)責(zé)清理 undo log（事務(wù)提交后遺留的舊版本）。
• 如果 purge 太慢，undo log 會(huì)堆積，導(dǎo)致表膨脹、性能下降。
• 多線程 purge 可以更快回收空間、減少 undo 膨脹。

建議： 一般設(shè)置為2就夠了，高并發(fā)場景可以考慮設(shè)置為4

臨時(shí)表全內(nèi)存

temptable_max_ram
tmp_table_size
max_heap_table_size
internal_tmp_mem_storage_engine

這幾個(gè)參數(shù)都跟 臨時(shí)表 有關(guān)，而臨時(shí)表就是 MySQL 在執(zhí)行 SQL 時(shí)的“臨時(shí)工地”，用來放中間結(jié)果。

比如：GROUP BY、ORDER BY、DISTINCT、UNION、JOIN 等操作，如果不能直接用索引，就可能生成臨時(shí)表。

temptable_max_ram

• 這是 MySQL 8 新引入的參數(shù)，專門限制 **TempTable 引擎（內(nèi)存臨時(shí)表）**的最大內(nèi)存占用。
• TempTable 是 MySQL 8 默認(rèn)的內(nèi)部臨時(shí)表引擎（之前默認(rèn)是 Memory 引擎）。
• 當(dāng)單個(gè)臨時(shí)表大小超過這個(gè)值時(shí)，會(huì)溢出到磁盤（存成 on-disk temporary table）。

tmp_table_size / max_heap_table_size

內(nèi)存臨時(shí)表的大小上限。復(fù)雜查詢（GROUP BY、JOIN、ORDER BY）結(jié)果可能要放臨時(shí)表。超過大小會(huì)落到磁盤 → 性能急劇下降。

這兩個(gè)值建議設(shè)置為一樣的，對(duì)齊限制，避免臨時(shí)表因?yàn)閮蓚€(gè)參數(shù)不一致導(dǎo)致意外落盤或報(bào)錯(cuò)。

最佳配置

普通的web類后臺(tái)管理系統(tǒng)

這類系統(tǒng)的普遍情況是：很少有高并發(fā)，但存在很多復(fù)雜，多張表關(guān)聯(lián)查詢

• 16G內(nèi)存最佳配置

# -------------------------
# InnoDB 緩沖池（核心參數(shù)）
# -------------------------
innodb_buffer_pool_size = 8G           # 給 InnoDB 分配 50% 內(nèi)存，留給系統(tǒng)和其他 MySQL 組件
innodb_log_buffer_size = 128M          # 日志緩沖大小，適合復(fù)雜查詢場景
innodb_max_dirty_pages_pct=50	# 控制 臟頁（dirty page）在緩沖池中允許的最大比例。推薦 50~60，平衡刷盤壓力和性能。

# -------------------------
# Redo Log & 刷盤策略（安全優(yōu)先）
# -------------------------
innodb_redo_log_capacity = 1G          # 合理大小，防止頻繁刷新
innodb_flush_log_at_trx_commit = 2     # web系統(tǒng)配置為2足夠
innodb_flush_log_at_timeout = 1        # 最多延遲 1 秒刷盤
# 默認(rèn)200，如果SSD，調(diào)整為5000~20000
innodb_io_capacity=200

# -------------------------
# 臨時(shí)表優(yōu)化（多表關(guān)聯(lián)查詢關(guān)鍵）
# -------------------------
temptable_max_ram = 512M
tmp_table_size = 512M
max_heap_table_size = 512M
internal_tmp_mem_storage_engine = TempTable

# -------------------------
# 連接和緩存（低并發(fā)適度）
# -------------------------
max_connections = 200
thread_cache_size = 50
table_open_cache = 2000
table_definition_cache = 1000
open_files_limit = 65535

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論