MySQL 高品質 SQL 撰寫七大黃金法則：從避免列運算到 EXPLAIN 與 Profiling 實戰

MySQL 高品質 SQL 七大黃金法則：效能、可讀與可維護並重

SQL 寫得快不代表寫得好。本文提煉七大實戰法則，涵蓋避免列運算、整數索引、小表驅動大表、精選欄位、批次寫入、EXPLAIN 執行計畫、Profiling 微觀分析，附反模式對比與工具鏈。

法則一：避免在索引列上運算 (導致索引失效)

法則二：優先使用整數型別設計索引

• 整數比較快、佔用空間小 (4B vs 字串 20B+)、索引樹更淺
• IP 存 INT UNSIGNED (INET_ATON/INET_NTOA)
• 日期存 INT (YYYYMMDD) 或 DATE (3B)
• 狀態/型別存 TINYINT + 應用層 Enum
• 主鍵/唯一索引**首選整數自增**

法則三：JOIN 以小結果集驅動大結果集

-- 反模式：大表在左，全量掃描再 JOIN
SELECT * FROM big_table a LEFT JOIN small_table b ON a.id = b.id WHERE a.type=1;

-- 優化：子查詢先過濾小結果集
SELECT * FROM (SELECT * FROM big_table WHERE type=1) a
LEFT JOIN (SELECT * FROM small_table WHERE status=1) b ON a.id = b.id;

• MySQL JOIN 為 Nested Loop Join，外層表 (Driving Table) 應盡量小
• 複雜多表 JOIN 拆分為多步驟、落地臨時表

法則四：SELECT 只列出需要的欄位

• SELECT * 傳輸多餘資料、佔用網路/記憶體、阻礙覆蓋索引
• 明確列出欄位：SELECT id, name, email FROM users
• 覆蓋索引機會大增：INDEX (status, id, name, email) 可直接滿足查詢

法則五：批次 INSERT 減少網路往返

-- 反模式：N 條語句 = N 次網路往返
INSERT INTO t VALUES (1); INSERT INTO t VALUES (2); ...

-- 優化：單條語句多值
INSERT INTO t (id) VALUES (1), (2), (3), ...; -- 批次 500-1000 行最佳

-- 大量載入：LOAD DATA INFILE (快 20 倍)
LOAD DATA INFILE '/data.txt' INTO TABLE t FIELDS TERMINATED BY ',';

法則六：善用 EXPLAIN 分析執行計畫

法則七：用 Profiling 定位慢 SQL 耗時細節

-- 開啟
SET profiling = 1;

-- 執行目標 SQL
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10;

-- 查看剖析
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1; -- 顯示每階段耗時：Sending data, Locked, Sorting, etc.

Profiling 已在 MySQL 8.0 棄用，建議改用 Performance Schema (events_statements_history_long) 或 sys schema 視圖。

工具鏈推薦

• EXPLAIN ANALYZE (MySQL 8.0.18+)：實際執行統計
• pt-query-digest (Percona Toolkit)：慢查詢日誌分析神器
• MySQL Workbench：視覺化 EXPLAIN、索引建議
• Explain.depesz.com：線上 EXPLAIN 視覺化

總結： 好 SQL = 正確語義 + 索引友善 + 最小資料傳輸 + 可預測效能。養成「先 EXPLAIN、再上線」習慣，效能事故減 90%。

1. 上一篇文章： MySQL 更新中文欄位報錯 1366 Incorrect string value 徹底解決：四層編碼統一
2. 下一篇文章： Linux 安裝 CMake 與 MySQL 從原始碼編譯完整踩坑記錄：wget、rz/sz、i-node 刪除亂碼檔

MySQL 綜合教學

MySQL UDF 提權原理與 mysqldoor 創建失敗排查實戰

UDF 提權攻擊鏈 獲取 MySQL 高權限賬號 (注

MySQL Cluster 集群技術入門：NDB 存儲引擎架構、節點角色與部署規劃全解析

MySQL Cluster 集群技術入門：NDB 存儲引擎架

MySQL 5.5 存儲過程支援 LIMIT 變數，5.1 需動態 SQL 繞過

版本差異 MySQL 5.1：存儲過程中 LIMIT 不支

MySQL 多表 JOIN 索引優化實戰：EXPLAIN 分析與索引建立策略

場景模型 三表 tblA、tblB、tblC，各含 col1