MySQL 資料庫 SQL 優化核心原則與實戰技巧總結

前言

SQL 優化是資料庫效能調校的關鍵環節。本文針對 MySQL 資料庫，整理出 10 大核心優化原則，並附上具體的 SQL 改寫範例，幫助開發人員建立良好的 SQL 撰寫習慣，提升查詢效能。需注意的是，這些原則主要適用於 MySQL，其他資料庫雖有共通之處，但細節上仍存差異，特殊情況仍需具體分析。

十大 SQL 優化核心原則

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

雖然對查詢速度影響不大，但能顯著減少應用伺服器的記憶體佔用與網路傳輸量。

-- 原寫法
SELECT * FROM admin;

-- 優化後
SELECT admin_id, admin_name, admin_password FROM admin;

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

對欄位使用函數會導致索引失效，應將運算移至常數側。

-- 原寫法：year() 函數導致索引失效
SELECT * FROM admin WHERE YEAR(admin_time) > 2014;

-- 優化後：改用範圍查詢走索引
SELECT * FROM admin WHERE admin_time > '2014-01-01';

原則 3：JOIN 查詢以小表驅動大表，並盡量拆分多表關聯

LEFT JOIN 時左表結果集應盡量小，條件下推至驅動表；多表關聯易造成鎖表與阻塞，建議拆分為多個 Query 執行。

-- 原寫法
SELECT * FROM admin 
LEFT JOIN log ON admin.admin_id = log.admin_id 
WHERE log.admin_id > 10;

-- 優化後：先過濾駕駛表再關聯
SELECT * FROM (
    SELECT * FROM admin WHERE admin_id > 10
) T1 
LEFT JOIN log ON T1.admin_id = log.admin_id;

原則 4：謹慎使用 LIKE 模糊查詢，避免雙百分號導致全表掃描

前導 % 會使索引失效，若業務允許，優先使用後綴 % 或改用範圍查詢替代。

-- 原寫法：雙 % 不走索引
SELECT * FROM admin WHERE admin_name LIKE '%de%';

-- 優化思路：利用範圍查詢模擬前綴匹配（非完全等效，視業務而定）
SELECT * FROM admin WHERE admin_name >= 'de' AND admin_name < 'df';

原則 5：採用批量插入減少客戶端與伺服器交互次數

合併多條 INSERT 為單條語句，大幅降低網路開銷；複雜邏輯可考慮存儲過程。

-- 原寫法：三次交互
INSERT INTO admin(admin_name, admin_password) VALUES ('test1','pass1');
INSERT INTO admin(admin_name, admin_password) VALUES ('test2','pass2');
INSERT INTO admin(admin_name, admin_password) VALUES ('test3','pass3');

-- 優化後：單次交互
INSERT INTO admin(admin_name, admin_password) 
VALUES ('test1','pass1'), ('test2','pass2'), ('test3','pass3');

原則 6：大偏移量 LIMIT 查詢改用 BETWEEN 範圍查詢

LIMIT 100000,10 需掃描前 10 萬行，效能極差；若主鍵連續，可改用主鍵範圍查詢。

-- 原寫法
SELECT * FROM admin ORDER BY admin_id LIMIT 100000, 10;

-- 優化後（假設 admin_id 連續）
SELECT * FROM admin 
WHERE admin_id BETWEEN 100000 AND 100010 
ORDER BY admin_id;

原則 7：避免使用 ORDER BY RAND() 獲取隨機記錄

RAND() 會產生臨時表並排序，極其消耗資源。可改用主鍵隨機範圍 + JOIN 方式。

-- 原寫法
SELECT * FROM admin ORDER BY RAND() LIMIT 20;

-- 優化思路：利用主鍵最大最小值計算隨機點
SELECT * FROM admin AS t1 
JOIN (
    SELECT ROUND(RAND() * (
        (SELECT MAX(admin_id) FROM admin) - (SELECT MIN(admin_id) FROM admin)
    ) + (SELECT MIN(admin_id) FROM admin)) AS id
) AS t2 ON t1.admin_id >= t2.id 
ORDER BY t1.admin_id LIMIT 20;

原則 8：盡量避免使用 NULL 值

NULL 會增加索引儲存空間、複雜化統計資訊、影響 `COUNT()`、`ORDER BY` 等行為，建議字段預設 NOT NULL 並給予預設值。

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

MyISAM 引擎會直接讀取表元數據，InnoDB 掃描主鍵索引；`COUNT(*)` 是標準語法且經優化器優化，效能不差於指定欄位。

原則 10：避免無謂的排序操作，善用索引完成排序

若 `ORDER BY` 欄位無索引，會觸發 Filesort；建立合適的複合索引可讓資料庫直接按索引順序返回結果，省去排序開銷。

輔助工具：使用 PROFILING 分析 SQL 效能

MySQL 內建的 Profiling 功能可幫助定位慢查詢瓶頸：

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

總結

資料庫優化包含 SQL 程式優化與資料庫配置優化兩大面向。養成良好的 SQL 撰寫習慣，配合合理的索引設計與參數調校，才能充分發揮 MySQL 效能。實務中應結合 `EXPLAIN`、慢查詢日誌、Profiling 等工具持續觀測與迭代優化。