MySQL 數據庫優化十大核心技巧：從查詢緩存到架構演進

MySQL 優化不止調參，更是系統工程。本文提煉十大高收益優化技巧，覆蓋查詢緩存、執行計劃、索引設計、架構模式，每條附帶反模式對比與驗證方法。

1. 善用查詢緩存：避開非確定性函數陷阱

-- ❌ 緩存失效：每次調用結果不同
SELECT username FROM user WHERE signup_date >= CURDATE();

-- ✅ 緩存命中：應用層構造確定值
$today = date('Y-m-d');
SELECT username FROM user WHERE signup_date >= '$today';

原則：查詢文本完全一致（含字面量）才命中緩存。NOW()、RAND()、UUID()、CURDATE() 等非確定性函數導致緩存繞過。

現代建議：MySQL 8.0 移除 Query Cache，改用 Redis 等外部緩存層。

2. EXPLAIN 讀懂執行計劃

• 關注 type（訪問類型）、key（實際索引）、rows（預估行數）、Extra（filesort/temporary/using index）
• phpMyAdmin / Workbench 可視化展示，降低閱讀門檻
• 案例：忘記聯表字段建索引，EXPLAIN 顯示 type=ALL，補索引後變 ref

3. LIMIT 1 取存在性檢查

-- ❌ 全表掃描計數
SELECT * FROM user WHERE state='Alabama';
IF (mysql_num_rows > 0) ...

-- ✅ 命中即停
SELECT 1 FROM user WHERE state='Alabama' LIMIT 1;
IF (mysql_num_rows > 0) ...

適用場景：唯一鍵查找、存在性檢查、取樣預覽。

4. 為搜索列建索引

• WHERE/JOIN/ORDER BY/GROUP BY 涉及的列
• 區分度高優先（唯一值/總行數 > 0.1）
• 聯合索引遵循最左前綴

5. 聯表字段類型字符集完全一致

-- ❌ 類型不匹配導致隱式轉換，索引失效
users.state VARCHAR(20) utf8mb4
companies.state CHAR(2) latin1

-- ✅ 完全一致
state VARCHAR(20) utf8mb4_unicode_ci  -- 兩表相同

6. 拒絕 ORDER BY RAND()

-- ❌ 全表賦隨機數排序，O(N log N)
SELECT username FROM user ORDER BY RAND() LIMIT 1;

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

7. 拒絕 SELECT *

收益：減少網絡傳輸、緩衝池污染、解析開銷、覆蓋索引機會增加。

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

SELECT * FROM user PROCEDURE ANALYSE();

輸出建議：最優類型、長度、是否 NULL、是否枚舉等。需基於真實數據分布運行。

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 地址存無符號整型

-- 表結構
ip INT UNSIGNED NOT NULL DEFAULT 0

-- 寫入
UPDATE users SET ip = INET_ATON('192.168.1.1') WHERE id=1;

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

優勢：4 字節固定長度 vs 15 字節字符串，索引更小、比較更快、範圍查詢原生支持。

架構級演進路線圖

1. 單機優化（索引、SQL、參數）
2. 讀寫分離（主從 + ProxySQL）
3. 緩存層（Redis 熱點數據）
4. 分庫分表（ShardingSphere）
5. NewSQL（TiDB/OceanBase）

十大技巧解決 80% 性能問題，剩餘 20% 需架構重構。建立優化閉環，持續交付高性能數據服務。