四大硬件瓶頸基礎
| 瓶頸 | 典型指標 | 緩解方向 |
|---|
| 磁盤搜索 | IOPS、平均延遲 <10ms | 多盤分佈、SSD/NVMe、RAID |
| IO 吞吐 | 10-20 MB/s（機械盤） | 並行讀寫、順序 IO、壓縮 |
| CPU 週期 | 內存數據處理 | 小表熱點、計算下推、向量化 |
| 內存帶寬 | 超出 CPU Cache | 列式存儲、數據壓縮、預取 |
引擎選型：MyISAM vs InnoDB 核心差異
| 特性 | MyISAM | InnoDB |
|---|
| 事務 | 不支持 | ACID 完整支持 |
| 鎖粒度 | 表鎖 | 行鎖（MVCC） |
| 崩潰恢復 | 全表掃描修復，時間隨數據量線性增長 | Redo Log 前滾，恢復時間固定 |
| 緩存管理 | 依賴 OS 文件系統緩存 | 自帶 Buffer Pool 精細管理 |
| 壓縮 | 支持 myisampack 唯讀壓縮 | 5.0+ 頁壓縮，8.0+ 透明壓縮 |
| 全文索引 | 早期支持 | 5.6+ 原生支持 |
結論：除唯讀/日志/極簡場景，**默認選 InnoDB**。MySQL 5.5+ 已默認 InnoDB，8.0 移除 MyISAM 系統表依賴。
InnoDB 關鍵參數調優清單
[mysqld]
# 內存核心（物理內存 60-80%）
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 8
# Redo Log（寫入性能與恢復時間平衡）
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1 -- 2: 高寫入犧牲 1s 安全性
# 並發與鎖
innodb_thread_concurrency = 0 -- 0=無限，CPU 核心數*2 起步
innodb_lock_wait_timeout = 50
# IO 優化
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000 -- SSD IOPS
innodb_read_io_threads = 8
innodb_write_io_threads = 8
# 表空間
innodb_file_per_table = ON
innodb_autoextend_increment = 128M
SQL 與索引優化黃金法則
- EXPLAIN 必看：
type=ALL 全掃、key=NULL 無索引、rows 巨大
- 最左前綴：聯合索引
(a,b,c) 支持 a/a,b/a,b,c
- 覆蓋索引：
SELECT a,b FROM t WHERE c=1 索引 (c,a,b) 避免回表
- 避免函數索引列：
WHERE DATE(created_at)='...' → WHERE created_at BETWEEN ...
- LIMIT 深分頁：
LIMIT 100000,10 → 子查詢主鍵回表 / 游標分頁
- COUNT(*) 誤區：InnoDB 無統計元數據，
COUNT(*) 全掃；用近似值 SHOW TABLE STATUS 或計數表
架構層面擴展
- 讀寫分離：主寫從讀，ProxySQL/MaxScale 路由
- 分庫分表：ShardingSphere/Vitess，業務鍵分片
- 緩存層：Redis 熱數據，一致性策略（雙寫/失效/ Canal 訂閱 Binlog）
- 只讀副本：延遲監控、半同步複制
rpl_semi_sync_master=ON
監控與持續優化
# 核心指標
- QPS / TPS
- 慢查詢數 / 慢查詢佔比
- Buffer Pool 命中率 (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)
- 連接數使用率
- 主從延遲
- 磁盤 IOPS / 利用率
