針對 Web 開發高頻場景，精選 10 條立竿見影的 MySQL 優化技巧，附反面教材與正確寫法對照。

1. 善用查詢快取 — 避免非確定性函數

-- ❌ 快取失效：NOW()/CURDATE()/RAND() 每次結果不同
SELECT * FROM user WHERE signup_date >= CURDATE();

-- ✅ 快取生效：應用層產生固定值
$today = date('Y-m-d');
SELECT * FROM user WHERE signup_date >= '$today';

2. EXPLAIN 看穿執行計畫

關注 type (目標 ref/eq_ref/const)、key (實際用到的索引)、rows (預估掃描行)、Extra (Using index/Using temporary/Using filesort)。

案例：忘記在 group_id 建索引導致 type=ALL，加上索引後變 ref，rows 從幾萬降至個位數。

3. 只需一筆 → LIMIT 1

-- ❌ 全表掃描再計數
SELECT * FROM user WHERE state='Alabama';

-- ✅ 找到第一筆即停止
SELECT 1 FROM user WHERE state='Alabama' LIMIT 1;

4. 為搜尋欄位建索引

非 PK/UK 欄位若常用於 WHERE、JOIN、ORDER BY，必須建索引。示例：username 無索引 → 全掃；加索引後 type=ref。

5. 連接鍵型別、字元集、排序規則必須一致

-- ❌ users.state (VARCHAR) JOIN companies.state (CHAR) → 隱式轉換、索引失效
-- ✅ 同型別、同字元集、同排序規則

6. 禁用 ORDER BY RAND()

-- ❌ 全表掃描 + 排序 + 臨時表
SELECT username FROM user ORDER BY RAND() LIMIT 1;

-- ✅ 兩步驟：先取總數 → 隨機 OFFSET
$cnt = (int)mysqli_fetch_row(mysqli_query($link, "SELECT COUNT(*) FROM user"))[0];
$rand = mt_rand(0, $cnt-1);
SELECT username FROM user LIMIT $rand, 1;

7. 避免 SELECT *

• 減少網路傳輸與記憶體佔用。
• 利用覆蓋索引 (Covering Index) 避免回表。
• Schema 變更不影響程式。

8. PROCEDURE ANALYSE() 獲取結構建議

SELECT * FROM user PROCEDURE ANALYSE(16, 256);
-- 建議可轉 ENUM 的低基數欄位、過長 VARCHAR 等

9. 使用 Prepared Statement

• 效能：預編譯減少解析優化開銷。
• 安全：參數綁定防 SQL Injection。

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

10. IP 存為 INT UNSIGNED

-- 寫入
UPDATE users SET ip = INET_ATON('192.168.1.1') WHERE id=1;
-- 讀出
SELECT INET_NTOA(ip) FROM users WHERE id=1;

4 bytes vs 15 bytes，整數比較更快，支援範圍查詢 (BETWEEN)。

掌握這 10 招，MySQL 效能即刻升級。