MySQL 效能優化十大實戰技巧

技巧 1：善用查詢快取

相同查詢重複執行時，快取命中極快。但含非確定性函數會失效：

-- 失效
SELECT * FROM user WHERE signup_date >= CURDATE();

-- 生效
$today = date("Y-m-d");
SELECT * FROM user WHERE signup_date >= '$today';

技巧 2：EXPLAIN 分析執行計劃

檢視索引使用、掃描行數、連接類型。案例：缺少 group_id 索引導致全表掃描，加上後 rows 從幾萬降為 1。

技巧 3：只需一行用 LIMIT 1

-- 低效
SELECT * FROM user WHERE state = 'Alabama';

-- 高效
SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1;

引擎找到第一筆即停止掃描。

技巧 4：搜尋欄位必建索引

任何出現在 WHERE/JOIN 的欄位皆應評估建立索引。

技巧 5：JOIN 欄位類型與編碼必須一致

-- 錯誤：DECIMAL JOIN INT，字符集不一
LEFT JOIN companies ON (users.state = companies.state)

類型不符或編碼不同會導致隱式轉換，索引失效、全表掃描。

技巧 6：禁用 ORDER BY RAND()

-- 極差：全表計算隨機值排序
SELECT username FROM user ORDER BY RAND() LIMIT 1;

-- 優化：應用層取隨機 offset
$count = SELECT COUNT(*) FROM user;
$rand = mt_rand(0, $count-1);
SELECT username FROM user LIMIT $rand, 1;

技巧 7：避免 SELECT *

-- 差
SELECT * FROM user WHERE user_id = 1;

-- 好
SELECT username FROM user WHERE user_id = 1;

減少網路傳輸、磁碟 I/O、記憶體佔用，大結果集差異顯著。

技巧 8：PROCEDURE ANALYSE() 獲取結構建議

根據現有資料分析建議最佳欄位類型、長度、是否 NOT NULL 等。

技巧 9：使用 Prepared Statements

$stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?");
$stmt->bind_param("s", $state);
$stmt->execute();
$stmt->bind_result($username);
$stmt->fetch();

優點：防 SQL 注入、重複執行解析優化、二進制協議傳輸效率高。

技巧 10：IP 地址存為 UNSIGNED INT

-- 存入
UPDATE users SET ip = INET_ATON('192.168.1.1') WHERE user_id = 1;

-- 讀出
SELECT INET_NTOA(ip) FROM users;

僅佔 4 字節，比 VARCHAR(15) 省空間、比較更快、支援範圍查詢。

總結檢查清單

• ☐ 關鍵查詢是否命中快取
• ☐ 慢查詢是否經過 EXPLAIN 分析
• ☐ 存在性檢查是否用 LIMIT 1
• ☐ 搜尋/JOIN 欄位是否有索引
• ☐ JOIN 關聯欄位類型編碼一致
• ☐ 移除所有 ORDER BY RAND()
• ☐ 所有 SELECT 明確列出欄位
• ☐ 定期跑 PROCEDURE ANALYSE()
• ☐ 核心業務改用 Prepared Statements
• ☐ IP/整數型資料用最小足夠類型