核心記憶體參數
| 參數 | 建議值 | 作用 | 調優要點 |
|---|
innodb_buffer_pool_size | 實體記憶體 60-80% | 資料+索引+鎖+自適應雜湊的核心快取 | 單機專用 DB 設 70-80%；監控 Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests < 1% |
innodb_buffer_pool_instances | 1-8 (≤ Buffer Pool GB 數) | 減少全域互斥鎖爭用 | 8.0 預設自動調整，手動設 4-8 即可 |
innodb_log_buffer_size | 32-256 MB | 事務日誌寫入前的記憶體緩衝 | 大事務/大 Blob 場景調大，避免頻繁 fsync |
key_buffer_size | MyISAM 專用，10-20% 記憶體 | MyISAM 索引快取 | 純 InnoDB 環境可設極小 (32MB) 僅供系統表 |
InnoDB 事務日誌機制深度解析
WAL (Write-Ahead Logging) 核心流程
- 事務修改記憶體中的頁（Buffer Pool），標記為髒頁
- 同步寫入 Redo Log（
ib_logfile0/1），循環寫入、順序 I/O，極快
- 背景執行緒 非同步刷盤 髒頁到資料檔（
.ibd），隨機 I/O，合併寫入提升效率
- 崩潰恢復：重啟時按 Redo Log 重做 已提交事務，回滾 未提交事務
關鍵參數與效能權衡
[mysqld]
# Redo Log 檔案大小與組數：總大小 = innodb_log_file_size * innodb_log_files_in_group
# 大 Log = 檢查點間隔長 = 刷盤壓力小 = 恢復時間長
# 典型寫入密集場景：256MB * 2 = 512MB；極端場景可達 1-4GB (上限 512GB)
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
# 提交時同步策略：1=最安全(每次提交 fsync)；0=最高效(每秒 fsync)；2=折中(提交寫 OS 快取)
# 生產核心業務建議 1；日誌/統計/可重算業務可用 0/2
innodb_flush_log_at_trx_commit = 1
# 雙重寫緩衝：防止頁寫入一半掛掉（partial write），犧牲少量寫入效能換資料安全
innodb_doublewrite = ON
鎖機制與併發控制
| 鎖類型 | 粒度 | 引擎支援 | 特點 |
|---|
| 表鎖 | 表 | MyISAM、MEMORY | 開銷小、鎖定快、併發極差（讀讀共存、讀寫互斥、寫寫互斥） |
| 行鎖 | 行 | InnoDB | 開銷大、併發高；需索引支援，否則退化為表鎖 |
| 頁鎖 | 頁 | BDB（已淘汰） | 折中方案，已少見 |
InnoDB 行鎖模式
- 記錄鎖：鎖定單一索引記錄
- 間隙鎖：鎖定索引記錄間的間隙，防止幻讀（RR 隔離級別）
- 臨鍵鎖：記錄鎖 + 間隙鎖，左開右閉區間
- 意向鎖：表級，表示「我想在這張表加行鎖」，協調表鎖與行鎖共存
MVCC (多版本併發控制)——讀寫不阻塞的秘密
- 每行隱藏欄位：
DB_TRX_ID（建立事務 ID）、DB_ROLL_PTR（回滾指標）
- Read Committed：每次 SELECT 產生新 Read View，讀最新提交版本
- Repeatable Read（預設）：事務啟動時產生 Read View，全程可重複讀
- Undo Log：回滾段維護歷史版本，
innodb_undo_log_truncate=ON (8.0+) 可自動清理
實戰配置模板（生產級，8.0 基準）
[mysqld]
# 基礎
server_id = 1
port = 3306
datadir = /data/mysql
socket = /tmp/mysql.sock
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
skip_name_resolve = ON
# 連線
max_connections = 2000
thread_cache_size = 100
max_connect_errors = 100000
wait_timeout = 600
interactive_timeout = 600
# InnoDB 核心
innodb_buffer_pool_size = 12G # 實體 16G * 75%
innodb_buffer_pool_instances = 8
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON
innodb_data_file_path = ibdata1:1G:autoextend
innodb_undo_tablespaces = 2
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 2G
innodb_io_capacity = 4000 # NVMe SSD IOPS
innodb_io_capacity_max = 8000
innodb_page_cleaners = 4
innodb_lru_scan_depth = 2000
# 安全與複制
binlog_format = ROW
log_bin = /data/binlog/mysql-bin
expire_logs_days = 7
sync_binlog = 1
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = ON
# 慢查詢
slow_query_log = ON
slow_query_log_file = /data/log/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 10
# 其他
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 4M
read_buffer_size = 2M
join_buffer_size = 4M
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
常見誤區辨析
- ❌ MyISAM 比 InnoDB 快 → 僅在純讀、全表掃描、無事務場景；聚簇索引 + Buffer Pool 命中時，InnoDB 遠超 MyISAM
- ❌ 參數越大越好 →
sort_buffer_size、join_buffer_size 是 per-thread，設過大導致 OOM
- ❌ 不用管 undo log → 長事務導致 undo 膨脹、History List Length 飆升、查詢變慢、無法 purge
- ✅ 監控驅動調優：Grafana + Prometheus + node_exporter + mysqld_exporter，建立基線，單一變更，A/B 驗證
