| 優化點 | 配置參數 | 預期收益 | 適用場景 |
|---|
| 編譯器選擇 | pgcc (Pentium GCC) | 整體 +10% | Intel 奔騰架構專用 |
| 精簡字符集 | --with-extra-charsets=none
（僅保留 latin1） | 減小體積、提升速度 | 單語言應用 |
| 靜態編譯 mysqld | --with-mysqld-ldflags=-all-static
--disable-shared | 省去動態鏈接開銷 | 高性能專用服務器 |
| 激進編譯旗標 | CFLAGS="-O6 -mpentiumpro -fomit-frame-pointer"
CXXFLAGS="-O6 -mpentiumpro -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" | 綜合 +10-30% | 現代 GCC/Clang 同理 |
推薦編譯命令範例
CFLAGS="-O3 -march=native -fomit-frame-pointer" \
CXXFLAGS="-O3 -march=native -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" \
./configure \
--prefix=/usr/local/mysql \
--enable-assembler \
--with-mysqld-ldflags=-all-static \
--disable-shared \
--with-extra-charsets=none \
--with-unix-socket-path=/tmp/mysql.sock
注意：現代 MySQL 5.7+/8.0 官方二進制包已高度優化，自行編譯收益遞減，除非極端性能敏感場景，否則建議使用官方預編譯包。
二、運行時配置優化：my.cnf 核心參數調優
配置文件層級與生效範圍
| 層級 | 路徑 | 生效範圍 |
|---|
| 全局 | /etc/my.cnf 或 /etc/mysql/my.cnf | 服務器所有 MySQL 實例 |
| 實例 | $MYSQL_BASE/var/my.cnf | 特定實例 |
| 用戶 | ~/.my.cnf | 特定用戶客戶端工具 |
關鍵參數調優參考（以 8GB 內存專用 DB 服務器為例）
| 參數 | 建議值 | 說明 |
|---|
| innodb_buffer_pool_size | 6G (75% 內存) | InnoDB 核心緩衝池，數據+索引熱點 |
| innodb_log_file_size | 512M | Redo Log 大小，減少 checkpoint 壓力 |
| innodb_flush_log_at_trx_commit | 1 (安全) / 2 (性能) | 1=每提交刷盤，2=每秒刷盤 |
| max_connections | 500-1000 | 配合連接池，避免 Too many connections |
| query_cache_type | 0 (關閉) | 5.7+ 建議關閉，8.0 已移除 |
| tmp_table_size / max_heap_table_size | 64M | 臨時表內存上限，超過轉 MyISAM 磁盤表 |
| sort_buffer_size | 2M | 每連接排序緩衝，避免設過大導致 OOM |
| read_rnd_buffer_size | 1M | 隨機讀緩衝 |
| thread_cache_size | 100 | 線程緩存，減少創建銷毀開銷 |
| table_open_cache | 2000 | 表緩存，配合 open_files_limit |
查看當前運行值
mysqld --verbose --help | grep -A1 '^max_connections'
# 或登入後
SHOW VARIABLES LIKE 'max_connections';
三、存儲引擎選型：5+2 種引擎適用場景
| 引擎 | 類型 | 核心特性 | 適用場景 | 版本狀態 |
|---|
| InnoDB | 事務安全 | ACID、行鎖、MVCC、外鍵、崩潰恢復 | 核心業務、高併發、數據一致性要求高 | 5.5+ 預設，主流標準 |
| MyISAM | 非事務 | 表鎖、全文索引(5.6+)、壓縮表、讀多寫少 | 日志、報表、只讀查詢、全文搜尋(舊版) | 成熟穩定，逐漸被 InnoDB 替代 |
| MEMORY (HEAP) | 非事務 | 數據在內存、Hash 索引、極快、重啟丟失 | 臨時表、查詢緩存、Session 存儲 | 4.1+ 支援自增 |
| MERGE (MRG_MyISAM) | 非事務 | 多個同結構 MyISAM 表邏輯合併 | 大表分片、日志分表、歸檔查詢 | 3.23.25+ |
| Archive | 非事務 | 僅支援 INSERT/SELECT、極高壓縮、無索引 | 審計日志、歷史數據、寫一次讀少 | 5.0+ |
| Federated | 非事務 | 遠程表映射為本地表、跨實例查詢 | 分布式查詢、異構數據整合 | 5.0+ |
| BDB (BerkeleyDB) | 事務安全 | 頁鎖、事務、Sleepycat 開發 | 歷史遺留，已廢棄 | 5.1 移除 |
指定引擎建表語法
CREATE TABLE t (id INT PRIMARY KEY, data VARCHAR(100)) ENGINE=InnoDB;
-- 或修改現有表
ALTER TABLE t ENGINE=InnoDB;
四、內建診斷優化工具
| 工具/命令 | 用途 | 關鍵輸出 |
|---|
| SHOW STATUS | 服務器狀態計數器 | Connections, Queries, Slow_queries, Threads_connected, Innodb_buffer_pool_reads... |
| SHOW PROCESSLIST | 當前執行線程 | 長查詢、鎖等待、Sleep 連接 |
| EXPLAIN SELECT ... | 查詢執行計劃 | type, key, rows, Extra (Using index, Using where, Using temporary, Using filesort) |
| OPTIMIZE TABLE | 碎片整理、統計更新 | 僅 MyISAM/InnoDB，重建表、回收空間 |
| ANALYZE TABLE | 更新索引統計信息 | 優化器決策依據 |
| CHECK TABLE | 檢查表錯誤 | 損壞偵測 |
| mysqlslap | 負載測試工具 | 模擬併發壓測 |
| mysqltuner.pl | 第三方調優腳本 | 配置建議報告 |
優化執行路線圖
- 基線測量： 記錄當前 QPS、延遲、慢查詢數
- 慢查詢分析： 开启 slow_query_log，用 pt-query-digest 分析 Top N
- 執行計劃優化： EXPLAIN 定位全表掃描、臨時表、文件排序 → 加索引/改 SQL
- 配置調優： 依據內存/負載調整核心參數，單項變更、觀測效果
- 架構優化： 讀寫分離、分庫分表、緩存層、異步處理
- 持續監控： Prometheus + Grafana、PMM、Datadog 等建立可觀測體系
- 上一篇文章： MySQL AUTO_INCREMENT 深度解析：各存儲引擎行為差異與最佳實踐
- 下一篇文章： MySQL 3.23.15+ 內建複製功能設定指南：主從、雙主熱備
Copyright ©
程式碼之家 All Rights Reserved
