六大核心差異
| 維度 | MyISAM | InnoDB |
|---|
| 全文索引 | 原生支持 (5.6 前唯一選擇) | 5.6+ 支持，建議用 ES |
| 行數統計 | 元數據直接讀取，極快 | 全表掃描計算，慢；帶 WHERE 同速 |
| 自增主鍵 | 可與其他列組合索引 | **必須**單列索引或組合索引首列 |
| DELETE 全表 | 重建表，極快 | 逐行刪除，慢；建議 TRUNCATE |
| LOAD TABLE FROM MASTER | 支持 | 不支持，需轉 MyISAM 導入再轉回 |
| 鎖粒度 | 表鎖 | 行鎖（但無法確定範圍時升級為表鎖） |
| 事務支持 | 無 | 完整 ACID |
選型決策
- 查詢多、寫入少、無事務：MyISAM（如日志分析、數據倉庫只讀表）
- 事務必要、高並發寫入、數據一致性關鍵：InnoDB（OLTP 核心業務首選）
- MySQL 5.5+ **默認 InnoDB**，生產環境無特殊理由**不建議切回 MyISAM**
InnoDB 關鍵優化參數
| 參數 | 推薦值 | 作用 | 調優要點 |
|---|
| innodb_buffer_pool_size | 物理內存 60-70% | 數據+索引緩存核心 | 專用 DB 可至 80%，監控 Pages_free=0 擴容 |
| innodb_buffer_pool_instances | 4-8 | 減少緩存池鎖競爭 | 每實例 ≥1GB，總大小不變 |
| innodb_flush_log_at_trx_commit | 2 (非金融) / 1 (金融) | 刷盤策略 | 2: 秒級刷盤，性能提升 10x；掉電丟 1 秒 |
| innodb_file_per_table | ON | 獨立表空間 | 支持單表空間回收、遷移、壓縮 |
| innodb_log_file_size | 256M-1G | Redo Log 大小 | 大 = 寫入快/恢復慢，設為 Buffer Pool 25% |
| innodb_log_buffer_size | 8-16M | Log 寫入緩存 | 大事務場景適當增大 |
| innodb_flush_method | O_DIRECT | 避免雙重緩衝 | 直接 I/O，繞過 OS Cache |
MyISAM 關鍵參數（如必須使用）
| 參數 | 推薦值 | 作用 |
|---|
| key_buffer_size | 物理內存 30-40% | 索引塊緩存 |
| read_buffer_size | 16-32M | 全表掃描順序讀緩存 |
通用優化參數
| 參數 | 推薦值 | 說明 |
|---|
| max_connections | 預估峰值 * 1.2 | 防止 "Too many connections" |
| sort_buffer_size | 16M | ORDER BY/GROUP BY 專用 |
| query_cache_type/size | OFF / 0 | 高並發下鎖競爭大，建議關閉 |
| open_files_limit | 65535 | 配合 ulimit -n |
| table_open_cache | 2000+ | 減少 .frm 打開/關閉開銷 |
| thread_cache_size | 16-64 | 減少線程創建銷毀開銷 |
系統層優化
# /etc/sysctl.conf
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_tw_buckets = 4096
net.ipv4.tcp_max_syn_backlog = 4096
# /etc/security/limits.conf
* soft nofile 65535
* hard nofile 65535
硬件選型
- 內存：越大越好，Buffer Pool + OS Cache 雙受益
- 存儲：NVMe SSD > SATA SSD > SAS 15K > SATA HDD
- RAID：RAID 10 (性能+冗余) > RAID 0 (純性能) > RAID 5/6 (寫入懲罰)
SQL 層優化清單
- 字段類型最小化：
ENUM 代替 VARCHAR(枚舉值)、INT UNSIGNED 存 IP
JOIN 替代子查詢（MySQL 5.6+ 優化器改進但 JOIN 仍更穩）UNION ALL 替代臨時表- 事務包裹多語句保證一致性
- 覆蓋索引避免回表
EXPLAIN 分析執行計劃- 大表分表：垂直拆冷熱字段、水平按分片鍵分表
總結
InnoDB 參數調優核心三板斧：Buffer Pool 蓋住熱數據、Redo Log 適度大且 O_DIRECT、flush_log_at_trx_commit=2 換性能。MyISAM 僅作為特定只讀場景補充。硬件上內存與 NVMe SSD 是性能杠桿。SQL 優化以「用最小類型、走覆蓋索引、避免全表掃描」為準則。
- 上一篇文章： MySQL 索引類型全解析：主鍵、唯一、普通、全文、組合索引創建與選型指南
- 下一篇文章： MySQL GROUP_CONCAT 實現按字段分類聚合輸出：字符串拼接與應用層解析
Copyright ©
程式碼之家 All Rights Reserved
