MySQL SQL 優化核心原則：從入門到實戰的 10 大黃金法則

前言

SQL 語句的優劣直接決定了資料庫的效能表現。本文整理了針對 MySQL 的 10 大優化原則，涵欄位選取、索引運用、連接查詢、分頁優化等核心場景，助你撰寫高效能的 SQL。

核心優化原則

原則 1：明確指定查詢欄位，避免 SELECT *

錯誤示範： SELECT * FROM admin;
優化寫法： SELECT admin_id, admin_name, admin_password FROM admin;
解析： 雖然對查詢速度影響有限，但能大幅減少應用伺服器的記憶體佔用與網路傳輸量，且避免資料表結構變更時程式報錯。

原則 2：避免在索引欄位上進行運算或函數操作

錯誤示範： SELECT * FROM admin WHERE YEAR(admin_time) > 2014;
優化寫法： SELECT * FROM admin WHERE admin_time > '2014-01-01';
解析： 對欄位使用函數會導致索引失效，改用範圍查詢可利用索引加速。

原則 3：小表驅動大表，優化 JOIN 查詢

核心概念： 使用 LEFT JOIN 時，左表結果集應盡量小；有 WHERE 條件優先放左表過濾。必要時將多表連接拆分為多個單表查詢，再由應用層組裝，避免大事務鎖表阻塞。
優化示例： 將 SELECT * FROM admin LEFT JOIN log ON admin.admin_id = log.admin_id WHERE log.admin_id > 10 改為先查小結果集再關聯。

原則 4：謹慎使用 LIKE 模糊查詢，避免雙百分號開頭

錯誤示範： SELECT * FROM admin WHERE admin_name LIKE '%de%';
優化思路： 盡量使用 admin_name LIKE 'de%'（前綴匹配可用索引），或改用全文索引、ElasticSearch 等搜尋引擎處理複雜模糊查詢。

原則 5：採用批次 INSERT 減少網路交互

優化寫法：

INSERT INTO admin(admin_name, admin_password) 
VALUES ('test1','pass1'), ('test2','pass2'), ('test3','pass3');

單條插入會產生多次網路往返，批次插入僅需一次交互，效能提升顯著。大量資料匯入時建議配合 LOAD DATA INFILE 或存儲過程。

原則 6：大偏移量 LIMIT 優化，改用 BETWEEN 或延遲關聯

問題： LIMIT 100000, 10 需掃描前 10 萬行，效能極差。
優化： 利用主鍵連續性改為 WHERE id BETWEEN 100000 AND 100010，或使用子查詢先取 ID 再關聯（延遲關聯）。

原則 7：禁用 ORDER BY RAND() 隨機查詢

問題： 會產生臨時表並進行檔案排序，極度消耗資源。
替代方案： 先取得最大/最小 ID，應用層生成隨機 ID，再用 WHERE id >= random_id ORDER BY id LIMIT N 查詢。

原則 8：避免使用 NULL 值

NULL 需額外儲存空間標識，且 B-Tree 索引不儲存 NULL，導致索引效率下降。建議用 0、空字串或特殊預設值替代。

原則 9：統計行數請用 COUNT(*) 而非 COUNT(col)

COUNT(*) 是 SQL 標準統計行數語法，MySQL (InnoDB) 已優化不需掃描全表；COUNT(col) 需判斷該欄位是否為 NULL，額外增加開銷。

原則 10：善用索引完成排序，避免無謂 ORDER BY

若 ORDER BY 欄位有索引，MySQL 可直接按索引順序讀取，避免 Filesort。EXPLAIN 中出現 Using filesort 即代表需優化。

效能分析工具：Profiling

MySQL 內建剖析工具可幫助定位慢查詢瓶頸：

SET profiling = 1;
SELECT * FROM typecho_comments ORDER BY mail LIMIT 10, 30;
SHOW PROFILES;
SHOW PROFILE FOR QUERY ;

總結

SQL 優化是系統效能調優的關鍵環節。除上述原則外，還需結合 EXPLAIN 分析執行計畫、監控慢查詢日誌、合理設計索引與資料表結構，才能從根本上提升資料庫效能。