MySQL 性能優化是系統工程，涵蓋編譯安裝、服務器參數調優、存儲引擎選型、內置工具診斷四大維度。本文結合官方文檔與工程實踐，給出可落地的優化清單。
| 優化點 | 操作 | 預期收益 |
|---|---|---|
| 編譯器選擇 | Intel CPU 推薦 icc / gcc -march=native；舊版可用 pgcc（僅 Pentium） | 10-30% 整體提升 |
| 精簡字符集 | ./configure --with-extra-charsets=none 僅保留 utf8mb4 / latin1 | 減小體積、加快啟動、降低內存 |
| 靜態鏈接 mysqld | ./configure --with-mysqld-ldflags=-all-static --disable-shared | 省去動態鏈接開銷，響應更穩定 |
| 優化編譯旗標 | CFLAGS="-O3 -march=native -fomit-frame-pointer" CXXFLAGS="-O3 -march=native -felide-constructors -fno-exceptions -fno-rtti" | 指令級並行、內聯優化、異常開銷消除 |
⚠️ 現代發佈版（RHEL/CentOS、Ubuntu、Percona、MariaDB）官方包已做深度優化，除非極致場景，建議直接使用官方二進制包。
[mysqld]
# 內存相關（按實際內存 60-80% 分配）
innodb_buffer_pool_size = 4G # 核心：數據+索引緩存
innodb_log_file_size = 512M # 減少 checkpoint 壓力
innodb_log_buffer_size = 64M # 大事務提交不阻塞
key_buffer_size = 256M # MyISAM 索引緩存（若仍有 MyISAM 表）
# 連接與線程
max_connections = 2000
thread_cache_size = 100
thread_stack = 256K
# 查詢緩存（MySQL 5.7+ 已棄用，8.0 移除）
# query_cache_type = 0
# query_cache_size = 0
# 臨時表與排序
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 4M
read_rnd_buffer_size = 2M
# InnoDB 並發與刷盤
innodb_flush_log_at_trx_commit = 1 # 1=最安全，2=高性能風險秒級丟失
innodb_flush_method = O_DIRECT # 避免雙重緩存
innodb_io_capacity = 2000 # SSD 可調大
innodb_write_io_threads = 8
innodb_read_io_threads = 8
# 安全與複制
sync_binlog = 1
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON| 引擎 | 事務 | 鎖粒度 | 適用場景 | 備註 |
|---|---|---|---|---|
| InnoDB | ✅ ACID | 行鎖 | 核心業務、高並發讀寫、外鍵約束 | MySQL 5.5+ 默認，首選 |
| MyISAM | ❌ | 表鎖 | 只讀/多讀少寫、全文檢索（5.6 前）、日誌/統計表 | 崩潰易損壞，需定期 CHECK TABLE |
| MEMORY (HEAP) | ❌ | 表鎖 | 臨時緩存、查找表、會話級數據 | 重啟數據丟失，Hash 索引不支持範圍查詢 |
| ARCHIVE | ❌ | 行鎖 | 海量日誌歸檔、審計追蹤 | 僅支持 INSERT/SELECT，壓縮率高 |
| CSV | ❌ | 表鎖 | 外部系統數據交換 | 不支持索引 |
原則： 除非有極強理由（如全文檢索、只讀歸檔），全表預設 InnoDB。
| 工具/命令 | 用途 | 關鍵指標 |
|---|---|---|
SHOW GLOBAL STATUS | 實時運行指標 | Threads_connected、Innodb_buffer_pool_reads、Slow_queries、Table_locks_waited |
SHOW ENGINE INNODB STATUS | InnoDB 內部狀態 | 死鎖歷史、信號量等待、刷盤進度、事務列表 |
EXPLAIN SELECT ... | 執行計劃分析 | type (ALL/index/range/ref/eq_ref/const)、rows、Extra (Using filesort/temporary/index) |
OPTIMIZE TABLE | 碎片整理、統計更新 | 適用 MyISAM/InnoDB（5.7+ 在線），大表建議 pt-online-schema-change |
ANALYZE TABLE | 更新索引統計信息 | 優化器依賴統計生成計劃，大表變更 >10% 後執行 |
CHECK TABLE | 校驗表完整性 | MyISAM 必做，InnoDB 依賴校驗和自動修復 |
Slow Query Log + EXPLAIN + SHOW PROFILE 定位 Top SQL。性能優化無終點，只有在特定業務負載下的「足夠好」。建立度量體系、小步快跑、持續迭代，才是長久之道。
