您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 資料庫優化實戰：14 個開發者必知的效能調校技巧

編輯：MySQL 資料庫相關知識

MySQL 效能優化是後端開發的核心技能之一。本文整理 14 個實戰驗證的優化技巧，涵蓋 WHERE 條件簡化、索引設計、查詢快取、EXPLAIN 分析、JOIN 優化、亂序查詢避坑等核心場景，附完整程式碼對照，幫助開發者快速定位與解決效能瓶頸。

1. WHERE 子句優化：簡化邏輯、消除冗餘

減少括號巢狀

-- 優化前
WHERE ((a AND b) AND c OR (((a AND b) AND (c AND d))))
-- 優化後
WHERE (a AND b AND c) OR (a AND b AND c AND d)

常數摺疊與移除

-- 優化前
(a < b AND b = c) AND a = 5 AND (5 = 5)
-- 優化後
b > 5 AND b = c AND a = 5

MySQL 會自動將常數表（空表、單行表、唯一索引等值查詢）優先讀取，善用此特性可減少掃描行數。

2. 避免 SELECT *：只查需要的欄位

-- 慢：讀取所有欄位
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";

-- 快：只查 username
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";

減少網路傳輸、記憶體佔用與磁碟 I/O，養成明確指定欄位的習慣。

3. DISTINCT 優化：等同於 GROUP BY

-- 這兩句等效
SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 > const;
SELECT c1, c2, c3 FROM t1 WHERE c1 > const GROUP BY c1, c2, c3;

適用於 GROUP BY 的優化技巧（如索引覆蓋）同樣適用於 DISTINCT。

4. 查詢快取優化：避免非確定性函數

-- 無法快取：CURDATE() 每次結果不同
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");

-- 可快取：PHP 端產生固定日期字串
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

查詢快取要求 SQL 文本完全一致，含非確定性函數（NOW、RAND、UUID 等）的查詢不會被快取。

5. 善用 EXPLAIN 分析執行計畫

在複雜 SELECT（特別是 JOIN）前加上 EXPLAIN，重點關注：

type：目標為 ref、eq_ref、range，避免 ALL（全表掃描）
key：實際使用的索引
rows：預估掃描行數
Extra：Using index（覆蓋索引）、Using where、Using filesort（需優化）

6. ORDER BY 優化：利用索引避免檔案排序

MySQL 可利用索引滿足 ORDER BY，避免額外排序。以下情況無法使用索引排序：

ORDER BY 欄位混合 ASC/DESC：ORDER BY key1 DESC, key2 ASC
WHERE 條件欄位與 ORDER BY 欄位不一致：WHERE key2=const ORDER BY key1
JOIN 多表且 ORDER BY 欄位非來自第一張非 const 表
使用 HASH 索引（HEAP 表）

使用 EXPLAIN SELECT ... ORDER BY 驗證是否使用索引排序。

7. LIMIT 1 取得唯一行：提前終止掃描

-- 掃描全表
$r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");
-- 找到 1 筆即停止
$r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");

明確只需 1 筆資料時，加上 LIMIT 1 讓引擎提前返回，效能提升顯著。

8. LIMIT 分頁優化：大偏移量效能陷阱

LIMIT row_count 無 HAVING 時，MySQL 可能改用索引掃描
LIMIT + ORDER BY：找到前 N 筆即停止排序
LIMIT + DISTINCT：找到 N 筆唯一值即停止
大偏移量（LIMIT 100000, 20）會掃描大量行，建議改用游標式分頁（WHERE id > last_id LIMIT 20）

9. JOIN 欄位類型必須一致

-- 錯誤：DECIMAL 與 INT 無法使用索引
LEFT JOIN companies ON (users.state = companies.state)
-- 正確：雙方同為 INT 或同字元集 VARCHAR

類型不匹配會導致隱含轉換，索引失效，退化為全表掃描。

10. 避免 ORDER BY RAND()：隨機查詢替代方案

-- 慢：全表掃描 + 排序
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");

-- 快：先取總數，再隨機 OFFSET
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0, $d[0] - 1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

11. IP 地址儲存為 UNSIGNED INT

VARCHAR(15) 改為 INT UNSIGNED（4 bytes），使用 INET_ATON() / INET_NTOA() 轉換，節省空間並加快比較與索引。

12. LEFT JOIN / RIGHT JOIN 優化：強制表讀取順序

-- 若 WHERE 條件使 NULL 行為假，LEFT JOIN 可轉為 INNER JOIN
SELECT * FROM t1 LEFT JOIN t2 ON (t1.id = t2.id) WHERE t2.col = 5;
-- 等效於
SELECT * FROM t1, t2 WHERE t2.col = 5 AND t1.id = t2.id;

使用 STRAIGHT_JOIN 強制連接順序，減少優化器嘗試的排列組合。