前言
MySQL 作為最流行的關係型數據庫,性能優化直接決定業務響應速度。本文從內存佔用模型出發,梳理全局緩存與線程緩存的計算方法,逐一解析核心參數作用與調優建議,幫助新手建立系統性優化認知。
一、MySQL 進程內存佔用模型
MySQL 總內存 = 全局緩存 + (線程緩存 × 最大連接數 max_connections)
1. 全局緩存 計算公式
全局緩存 = key_buffer_size
+ innodb_buffer_pool_size
+ innodb_additional_mem_pool_size
+ innodb_log_buffer_size
+ max_heap_table_size # 為安全起見計入
+ query_cache_size
- innodb_buffer_pool_size:InnoDB 核心緩存(索引+數據),最關鍵參數,建議設為物理內存 60%~80%(專用 DB 服務器)。
- key_buffer_size:MyISAM 索引緩存,若無 MyISAM 表可設小。
- query_cache_size:查詢結果緩存,高併發寫入場景建議關閉(
query_cache_type=0),否則鎖競爭嚴重。
- innodb_log_buffer_size:事務日志緩存,大事務場景可適當增大(如 64M)。
2. 線程緩存 計算公式
線程緩存 = sort_buffer_size
+ read_rnd_buffer_size
+ join_buffer_size
+ read_buffer_size
+ max_allowed_packet
+ thread_stack
- sort_buffer_size:ORDER BY/GROUP BY 排序緩存,按需配置(如 2M~8M),避免過大導致 OOM。
- read_rnd_buffer_size:排序後隨機讀緩存,提升 ORDER BY 性能。
- join_buffer_size:無索引關聯查詢緩存,首選加索引,此參數無需過大(256K~1M)。
- read_buffer_size:全表掃描順序讀緩存,同理不宜過大。
- max_allowed_packet:網絡包緩存上限,大字段場景需調大(如 32M)。
- thread_stack:線程棧大小,默認 256K 通常足夠。
二、核心參數詳細說明
全局緩存參數
| 參數 | 作用 | 調優建議 |
| innodb_buffer_pool_size | 緩存 InnoDB 索引與數據 | 專用 DB 設物理內存 60%-80%,監控 Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests 命中率 |
| innodb_additional_mem_pool_size | 數據字典、內部結構 | 默認通常足夠,錯誤日志報警再增 |
| innodb_log_buffer_size | 事務日志緩存 | 大事務增大至 32M~64M,減少磁盤同步 |
| key_buffer_size | MyISAM 索引緩存 | 無 MyISAM 表設 16M~32M 即可 |
| query_cache_size | SELECT 結果緩存 | 讀多寫少場景開啟 64M~256M;寫多關閉 |
線程緩存參數
| 參數 | 作用 | 調優建議 |
| sort_buffer_size | 排序緩存 | 2M~8M,按會話分配,慎防 OOM |
| read_rnd_buffer_size | 排序後隨機讀 | 1M~2M,提升 ORDER BY LIMIT 性能 |
| join_buffer_size | 無索引 JOIN 緩存 | 256K~1M,優先優化 SQL 加索引 |
| read_buffer_size | 全表掃描順序讀 | 128K~256K,避免全表掃描 |
| max_allowed_packet | 網絡包最大值 | 應用有大字段(Blob/Text)設 16M~64M |
| thread_stack | 線程棧 | 默認 256K/512K,深度遞歸存儲過程才需增大 |
其他關鍵非內存參數
| 參數 | 作用 | 建議 |
| max_connections | 最大連接數 | 默認 151,視應用峰值調整(如 500~2000),配合連接池 |
| innodb_log_file_size | Redo Log 文件大小 | 總大小建議 1G~4G,過大延長崩潰恢復時間 |
| table_open_cache | 表文件描述符緩存 | ≥ 最大連接數 × 單查詢涉及表數,MyISAM 每表 2 描述符,受 OS file-max 限制 |
| thread_cache_size | 線程緩存池 | 高併發設 50~200,降低創建/銷毀開銷,觀察 Threads_created 指標 |
三、優化檢查清單
- 確認
innodb_buffer_pool_size 佔比合理,無 Swap 發生。
- 關閉或縮小 Query Cache(高寫入場景)。
- 排查
sort_buffer_size、join_buffer_size 是否過大導致連接數受限。
- 監控
Table_open_cache_misses 調整 table_open_cache。
- 啟用
slow_query_log 定位慢 SQL,配合 EXPLAIN 加索引優化,遠比調參數收益大。
優化原則:先優化 SQL 與索引,再調架構(分庫分表、讀寫分離),最後調參數。參數調優是精細手術,而非萬能藥。