數據庫屬於典型的 **IO 密集型**應用。內存讀取延遲 ~微秒級,機械硬盤 IO 延遲 ~毫秒級,**相差 3 個數量級**。優化首要目標:**將磁盤 IO 轉化為內存 IO**。MySQL 提供多層緩存機制,精準配置這些參數,是性能調優的最高 ROI 動作。
query_cache_size:緩存 ResultSet 內存大小(Global)query_cache_type:0=OFF, 1=ON(默認用), 2=DEMAND(需 SQL_CACHE)| 場景 | query_cache_type | query_cache_size | 理由 |
|---|---|---|---|
| 純 InnoDB、高寫入 | 0 (OFF) | 0 | 寫入頻繁導致緩存命中率極低,鎖競爭反拖慢性能 |
| MyISAM 為主、靜態數據多 | 2 (DEMAND) | 256MB 起步,視命中率調整 | 显式 SQL_CACHE 控制,避免污染 |
| 只讀報表庫 | 1 (ON) | 512MB-1GB | 數據幾乎不變,命中率極高 |
監控指標:Qcache_hits / (Qcache_hits + Qcache_inserts) * 100%。命中率 < 20% 即應關閉。
binlog_cache_size (Global)Binlog_cache_disk_use 激增)Binlog_cache_use vs Binlog_cache_disk_use,磁盤比例 > 1% 即需調大key_buffer_size (Global)bulk_insert_buffer_size (Thread)INSERT ... SELECT、INSERT ... VALUES (...),(...)、LOAD DATA INFILE(非空表)innodb_buffer_pool_size (Global)db_cache_size + shared_pool 部分功能內存允許情況下,**越大越好**,目標:熱數據 100% 駐留內存。
(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%
目標 > **99.9%**。低於 99% 需擴容內存或拆分熱冷數據。
innodb_additional_mem_pool_size (Global)Warning: InnoDB: Additional memory pool size is too smallinnodb_log_buffer_size (Global)innodb_flush_log_at_trx_commit 條件才刷盤innodb_flush_log_at_trx_commit 策略:| 值 | 行為 | 性能 | 持久性 |
|---|---|---|---|
| 0 | 每秒刷日志文件 + 同步磁盤;Commit 不觸發刷盤 | 最高 | 崩潰丟失 1 秒事務 |
| 1 (默認) | 每次 Commit 刷日志文件 + 同步磁盤 | 最低 | 完全 ACID |
| 2 | Commit 刷日志文件(OS 緩存),每秒同步磁盤 | 折中 | OS 崩潰可能丟 1 秒 |
innodb_max_dirty_pages_pct (Global)min(90, 1GB / innodb_buffer_pool_size(GB) * 100)
即:若 Buffer Pool 100GB,建議 ≤ 1%;若 10GB,建議 ≤ 10%。**不可盲目設 90%。**
| 參數 | 建議值 | 調整依據 |
|---|---|---|
query_cache_type | 0 (InnoDB) / 2 (MyISAM) | 寫入頻率、命中率 |
query_cache_size | 0 / 256MB | 命中率計算 |
binlog_cache_size | 4MB | Binlog_cache_disk_use 監控 |
key_buffer_size | 32MB (無MyISAM) / 索引總大小 (有MyISAM) | 存儲引擎分布 |
bulk_insert_buffer_size | 16MB | 批量導入頻次 |
innodb_buffer_pool_size | 40-48GB (60-75% 物理內存) | 命中率 > 99.9% |
innodb_additional_mem_pool_size | 16MB | Error Log Warning |
innodb_log_buffer_size | 8MB | 大事務頻率 |
innodb_max_dirty_pages_pct | 50-75 | 恢復時間容忍度 |
innodb_flush_log_at_trx_commit | 1 (核心業務) / 2 (日志/報表) | 持久性要求 |
flush_log_at_trx_commit=1,日志/報表庫可放寬innodb_dedicated_server 自動調優緩存參數調優是 MySQL 性能優化的**杠桿點**。沒有銀彈,只有基於業務負載特徵、硬件資源約束、版本特性的**精準配置**。建立監控體系、建立基準線、小步快跑驗證,才是通往高性能 MySQL 的正確路徑。