系統層面瓶頸與對策
| 瓶頸 | 特徵 | 優化方向 |
|---|
| 磁碟尋道 | 7200 RPM 理論 7200 IOPS | RAID 0/10、多磁碟分散 I/O、SSD/NVMe |
| 磁碟傳輸 | 連續讀寫較快 | 並行讀寫、大塊 I/O、檔案系統調優 |
| CPU | 小表熱點運算 | 查詢優化、適當索引、避免複雜運算下推 |
| 記憶體 | 熱資料超出快取 | 增加 RAM、調整 buffer pool、熱資料預熱 |
關鍵服務器參數調優 (my.cnf)
[mysqld]
# 連線層
max_connections = 500
max_connect_errors = 10000
connect_timeout = 10
wait_timeout = 28800
interactive_timeout = 28800
# 緩衝區 (依實體 RAM 調整，建議 60-80% 給 InnoDB)
innodb_buffer_pool_size = 4G # 核心：資料+索引熱點
innodb_log_file_size = 512M # 大檔減少 checkpoint
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2 # 1=最安全, 2=效能平衡, 0=最高效能風險
innodb_flush_method = O_DIRECT # 避免雙重快取
key_buffer_size = 256M # MyISAM 索引快取
sort_buffer_size = 4M # 每連線排序緩衝
read_buffer_size = 2M # 全表掃描緩衝
read_rnd_buffer_size = 8M # 排序後隨機讀緩衝
join_buffer_size = 4M # JOIN 無索引時緩衝
tmp_table_size = 256M # 記憶體臨時表上限
max_heap_table_size = 256M
# 快取
query_cache_type = 0 # 5.7+ 建議關閉，改用 ProxySQL/Redis
query_cache_size = 0
# 表快取
table_open_cache = 4000
table_definition_cache = 2000
# 並發
thread_cache_size = 100
innodb_thread_concurrency = 0 # 0=自動
索引設計黃金法則
- 最左前綴原則：複合索引
(a,b,c) 支援 a/a,b/a,b,c 查詢
- 高選擇性優先：唯一值/基數高的欄位放前面
- 覆蓋索引：
SELECT a,b FROM t WHERE c=1 → 索引 (c,a,b) 避免回表
- 避免冗餘索引：
(a) + (a,b) → 保留後者
- 前綴索引：長字串
varchar(200) → INDEX (col(10))
- 外鍵需索引：子表外鍵欄位必須有索引
SQL 改寫實戰技巧
| 問題 | 改寫前 | 改寫後 |
|---|
| OR 條件無法用索引 | WHERE a=1 OR b=2 | WHERE a=1 UNION ALL WHERE b=2 |
| LIKE 導模糊 | WHERE name LIKE '%abc%' | 全文索引 / 反向索引 / 搜尋引擎 |
| 函數欄位 | WHERE DATE(created)=CURDATE() | WHERE created >= CURDATE() AND created < CURDATE()+1 |
| 隱含轉型 | WHERE varchar_col = 123 | WHERE varchar_col = '123' |
| 分頁深翻 | LIMIT 100000, 20 | 延遲關聯 / 游標分頁 / ID 游標 |
| COUNT(*) 慢 | SELECT COUNT(*) FROM big_table | 資訊表估算 / 觸發器維護計數表 / 約略值 |
儲存引擎選擇決策樹
需要交易/外鍵/崩潰安全？
├─ 是 → InnoDB (預設首選)
└─ 否 → 唯讀/分析/日誌？
├─ 是 → MyISAM / Archive / ColumnStore
└─ 否 → 記憶體級極速？
├─ 是 → Memory (臨時) / Redis (外部)
└─ 否 → 一般 OLTP → InnoDB
InnoDB vs MyISAM 關鍵差異
| 特性 | InnoDB | MyISAM |
|---|
| 交易 | 支援 (ACID) | 不支援 |
| 鎖粒度 | 行鎖 | 表鎖 |
| 外鍵 | 支援 | 不支援 |
| 崩潰恢復 | Redo/Undo Log 自動 | myisamchk 手動 |
| 全文索引 | 5.6+ 支援 | 傳統支援 |
| 壓縮表 | 5.7+ 透明壓縮 | myisampack 唯讀壓縮 |
| 適用場景 | 核心業務、高併發 | 唯讀分析、日誌、快取表 |
監控與持續優化
SHOW GLOBAL STATUS / SHOW ENGINE INNODB STATUSpt-query-digest 分析慢查詢日誌sys.schema_*_statistics 找出未用索引- 定期
ANALYZE TABLE 更新統計資訊
- 線上 DDL (5.6+) 減少鎖表時間
