MySQL SQL 優化九大鐵律：從基準測試到引擎選型全攻略

前言

SQL 優化非玄學，而是「基準 → 測試 → 診斷 → 重構」的工程化流程。本文彙整九大核心原則，涵蓋基準建立、Schema 設計、索引策略、引擎特性與 MySQL 專屬技巧，助你系統化提升資料庫效能。

1. 建立基準，數據說話

• 使用 sysbench、supersmack、ab 模擬高併發負載。
• 找出「最糟查詢」、「瓶頸資源」（CPU/記憶體/磁碟 I/O/網路）。
• 無基準不優化，避免主觀臆測。

2. 啟用慢查詢日誌與監控工具

• slow_query_log=ON、long_query_time=1（秒）。
• 搭配 mtop、innotop 即時觀察執行緒狀態。
• 善用 EXPLAIN、SHOW STATUS、SHOW PROCESSLIST 定位掃表、臨時表、檔案排序等問題。
• 系統層面：top、vmstat、iostat 監控資源飽和度。

3. 縮減 Schema 體積

• 表大小 ≈ 行數 × 單行大小。能用 SMALLINT 別用 BIGINT；固定長度字串用 CHAR，變長用 VARCHAR。
• 適度正規化（3NF）消除冗餘，換取更小記憶體佔用；效能需要時再反正規化。
• 原則：先正規化，後反正規化。

4. 垂直/水平拆分表

• 垂直拆分：熱欄位（標題、建立時間）置主表，冷欄位（全文、大欄位）置副表，減少熱資料記憶體佔用，提升緩存命中率。
• 水平拆分：按時間/租戶/ID 範圍分表，單表控制在 2000 萬行以內。
• 寫入熱點拆分：高頻更新欄位獨立表，避免緩存失效連鎖反應。

5. 慎用人造主鍵

• 多對多關聯表（如 posts_tags）應用 PRIMARY KEY (post_id, tag_id) 複合主鍵，省去 relation_id 空間與索引維護成本。
• 自然鍵穩定且唯一時優先採用；業務鍵易變才用代理鍵。

6. 深度掌握索引

• 索引本質是 B+Tree 有序結構，加速 WHERE、JOIN、ORDER BY、GROUP BY。
• 每個索引佔用空間 ∝ 行數；寫入需維護所有索引，索引非越多越好。
• 覆蓋索引：SELECT a,b FROM t WHERE c=1 → INDEX(c,a,b) 避免回表。

7. SQL 非程序語言：集合思維替代游標思維

• 錯誤範例：相關子查詢逐行執行 SELECT ..., (SELECT MAX(created) FROM posts WHERE author_id=a.id) FROM authors a。
• 正確範例：SELECT a.id, MAX(p.created) FROM authors a JOIN posts p ON a.id=p.author_id GROUP BY a.id。
• 善用 JOIN、派生表、CTE 將行級邏輯轉為集合運算。

8. 理解儲存引擎差異

• 讀多寫少、無事務需求 → MyISAM（或 MySQL 5.7+ InnoDB 同樣優秀）。
• 寫入頻繁、需事務/外鍵/行鎖 → InnoDB。
• InnoDB 分頁統計：SQL_CALC_FOUND_ROWS + FOUND_ROWS() 替代 COUNT(*)。

9. 善用 MySQL 專屬擴展語法

• INSERT ... ON DUPLICATE KEY UPDATE：Upsert 單語句完成。
• REPLACE INTO：主鍵衝突時刪舊插新。
• INSERT IGNORE：忽略重複鍵錯誤。
• 慎用 INSERT DELAYED：高併發下隊列易爆滿導致資料丟失。
• 索引提示 USE INDEX、FORCE INDEX、IGNORE INDEX 輔助優化器決策。

總結

優化是持續迭代：建基準 → 找慢查 → 看執行計畫 → 改 Schema/索引/SQL → 驗證基準。工欲善其事，必先利其器——熟練掌握 EXPLAIN、慢查詢日誌、效能監控三板斧，才能在 MySQL 世界遊刃有餘。