核心認知：資料庫是 IO 密集型應用
記憶體讀取微秒級，磁碟 IO 毫秒級，差距三個數量級。優化首要任務：將磁碟 IO 轉化為記憶體 IO。本文系統梳理 MySQL 關鍵緩存參數、調優邏輯與實戰建議值。
關鍵緩存參數詳細解析
1. Query Cache：query_cache_size / query_cache_type (Global)
- 緩存 SELECT 結果集，命中則跳過解析、優化、引擎層查詢。
- 致命缺陷：表任意變更導致相關 Query Cache 全部失效，寫入頻繁場景得不償失。
query_cache_type：0=OFF, 1=ON(預設用), 2=DEMAND(SQL_CACHE 顯式啟用)。- 建議：純 InnoDB 環境設 0；MyISAM 唯讀/靜態資料設 2 並顯式控制。
2. Binlog Cache：binlog_cache_size (Global)
- 事務提交前暫存 Binlog，減少磁碟同步頻率。
- 一般 2-4MB 足夠；大事務高寫入環境適度調大，不超 32MB。
- 監控
Binlog_cache_disk_use 判斷是否不足落盤。
3. MyISAM 專屬：key_buffer_size (Global)
- 緩存 MyISAM 索引塊。不緩存資料塊，故 SQL 必須讓過濾條件走索引。
- 不用 MyISAM 設 16MB 緩存系統表；主力引擎則「越大越好」容納所有索引。
4. 批量插入緩存：bulk_insert_buffer_size (Thread)
- 服務
INSERT ... SELECT、INSERT ... VALUES(),(),()、LOAD DATA INFILE (非空表)。
- 頻繁批量寫入可調 16-32MB，不建議更大。
5. InnoDB 核心：innodb_buffer_pool_size (Global)
- InnoDB 效能生死線，緩存索引+資料頁，類似 Oracle
db_cache_size。
- 記憶體允許下「越大越好」，目標容納所有熱點資料與索引。
- 命中率公式：
(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%，目標 > 99%。
6. InnoDB 內部結構：innodb_additional_mem_pool_size (Global)
- 儲存資料字典、內部結構。表對象極多時需調大。
- 不足時 Error Log 會印 Warning，據此調整。建議 8-32MB。
7. Redo Log 緩存：innodb_log_buffer_size (Global)
- 事務日志先寫緩存，滿足
innodb_flush_log_at_trx_commit 條件再刷盤。
- 預設 1MB，高寫入調 4-8MB，不超 32MB。配合
innodb_flush_log_at_trx_commit：
| 值 | 行為 |
|---|
| 0 | 每秒刷日志+同步，commit 不觸發（效能最好，可能丟 1 秒資料） |
| 1 | 每次 commit 刷日志+同步（ACID 最強，效能最差） |
| 2 | commit 刷日志不同步，每秒同步一次（折中，Galera 建議 2） |
8. 髒頁控制：innodb_max_dirty_pages_pct (Global)
- 控制 Buffer Pool 中髒頁比例上限，減少背景刷盤 IO。
- 值過大 → 崩潰恢復時間過長；過小 → 刷盤頻繁。建議：
1GB / innodb_buffer_pool_size(GB) * 100，不超 90。
實戰建議值速查表
| 參數 | 建議值 | 備註 |
|---|
| query_cache_type | InnoDB:0 / MyISAM:2 | 靜態資料可開大 |
| query_cache_size | 256MB 起調 | 視命中率調整 |
| binlog_cache_size | 2-4MB | 大事務可至 32MB |
| key_buffer_size | 無MyISAM:16MB / 有:越大越好 | 含納所有索引 |
| bulk_insert_buffer_size | 8-32MB | 視批量頻率 |
| innodb_buffer_pool_size | 記憶體 60-80% | 核心參數，越大越好 |
| innodb_additional_mem_pool_size | 8-32MB | 視表數量 |
| innodb_log_buffer_size | 4-8MB | 不超 32MB |
| innodb_max_dirty_pages_pct | 75-90 | 平衡恢復時間與寫入效能 |
註：以上數值源於實戰經驗，非絕對真理。實際調優需結合 SHOW GLOBAL STATUS、SHOW ENGINE INNODB STATUS 監控指標動態調整。
