MySQL 十大優化技巧：從查詢緩存到架構設計

匯總 MySQL 性能優化十大核心技巧，涵蓋查詢寫法、索引設計、架構決策，附代碼對比與工具驗證方法。

1. 善用查詢緩存

-- 緩存失效：非確定性函數
SELECT * FROM user WHERE signup_date >= CURDATE();

-- 緩存生效：確定性值
$today = date('Y-m-d');
SELECT * FROM user WHERE signup_date >= '$today';

CURDATE()、NOW()、RAND() 等導致緩存失效，應用層預計算傳入確定值。

2. EXPLAIN 讀懂執行計劃

關注 type（const/ref/range/index/ALL）、key（實際用索引）、rows（掃描行數）、Extra（Using filesort/temporary/index）。

3. LIMIT 1 止於一行

-- 差：掃描全表
SELECT * FROM user WHERE state = 'Alabama';

-- 優：命中一行即停
SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1;

4. 為搜索字段建索引

不僅主鍵/唯一鍵，WHERE、JOIN、ORDER BY 涉及的列都應評估建索引。

5. JOIN 字段類型編碼必須一致

-- 錯誤：DECIMAL JOIN INT，字符集不同
SELECT * FROM users 
JOIN companies ON users.state = companies.state;

類型不匹配導致隱式轉換、索引失效、全表掃描。

6. 禁用 ORDER BY RAND()

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

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

7. 避免 SELECT *

• 增加網絡傳輸、磁盤 IO、緩存壓力
• 明確列出需要的列，利用覆蓋索引

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

基於現有數據分析列類型優化建議（如 ENUM 替代 VARCHAR、縮小 INT 位寬）。

9. 預處理語句：性能與安全雙贏

$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 id = 1;
-- 讀取
SELECT INET_NTOA(ip) FROM users;

4 字節定長 vs 15 字節變長，支持範圍查詢（BETWEEN）、索引效率高。