MySQL 性能優化六大實戰技巧

1. 存儲引擎選型

• 需事務、行鎖、外鍵 → InnoDB（ACID、MVCC、崩潰安全）。
• 純讀、全文索引、空間索引、無事務 → MyISAM（輕量、表鎖）。
• 同一事務中勿混用兩引擎，否則事務回滾不一致。可用插件形式單獨載入 InnoDB。

2. COUNT(*) 陷阱

InnoDB 不鎖表掃描聚合，並發下 COUNT(*) 返回近似值。精確計數需維護計數表或用 SELECT COUNT(1) FROM t WHERE id > last_id 分頁統計。

3. 大數據量反復測試

開發環境數據量小掩蓋全表掃描、鎖爭用、臨時表溢寫磁盤等問題。上線前必須用生產級數據量（百萬/千萬行）跑真實負載測試。

4. 避免全表掃描

• WHERE、JOIN、ORDER BY、GROUP BY 涉及列建立索引。
• 用 EXPLAIN 確認 type=ALL 即全表掃描，優化目標達到 ref/range/eq_ref。

5. 善用 EXPLAIN 診斷

實例：SELECT emp_id FROM awesome_pcq WHERE email_id='x' AND password='y' AND deleted=0

EXPLAIN SELECT ...  -- 關注 rows 列：100 行 → 全表掃描
ALTER TABLE awesome_pcq ADD INDEX idx_login (email_id);
EXPLAIN SELECT ...  -- rows=1 → 索引命中

索引列順序不影響優化器選擇，但聯合索引需遵循最左前綴。

6. 索引策略：精準而非堆砌

• 高區分度列優先（如 email、手機號）。
• 低區分度列（性別、狀態）單獨索引收益低，可放入聯合索引尾部。
• 定期 ANALYZE TABLE 更新統計信息，防止優化器誤判。
• 監控 Handler_read_rnd_next 暴增 → 疑似全表掃描。