MySQL 21 條開發者必知最佳實踐：從查詢緩存到存儲引擎選型

1. 善用查詢緩存，但避開非確定性函數

// ❌ 緩存失效：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'");

原理：NOW()、RAND()、CURDATE() 等非確定性函數導致查詢緩存繞過。

2. EXPLAIN 讀懂執行計劃

關注 type（至少 range）、key（用上預期索引）、rows（預估掃描行數）、Extra（避開 Using temporary/filesort）。

3. 單行查詢加 LIMIT 1

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

4. 搜索字段必建索引

LIKE 'abc%' 可用索引，LIKE '%abc%' 需全文索引或 ES。

5. 連接字段同類型、同字符集、同加索引

-- ❌ 類型不匹配導致隱式轉換，索引失效
JOIN ON users.state(CHAR) = companies.state(INT)
-- ✅ 同類型、同編碼、雙側索引

6. 禁用 ORDER BY RAND()

// ❌ 全表賦隨機數排序，災難性性能
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// ✅ 兩步法：先 COUNT 再隨機 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");

7. 避免 SELECT *

只取需要的列，減少 IO、網絡、內存、覆蓋索引機會增加。

8. 幾乎總要有自增整數主鍵

INT UNSIGNED AUTO_INCREMENT PRIMARY KEY。業務鍵（用戶名/身份證）加唯一索引，不做主鍵。關聯表除外。

9. 定值少用 ENUM 代替 VARCHAR

內部存儲為 TINYINT，極致緊湊。PROCEDURE ANALYSE() 會建議轉 ENUM。

10. 用 PROCEDURE ANALYSE() 獲取結構建議

基於實際數據分佈推薦類型，僅作參考，結合業務擴展性判斷。

11. 能 NOT NULL 就 NOT NULL

NULL 額外佔用位圖空間，增加比較複雜度（三值邏輯）。

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

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

單次解析多次執行，二進制協議傳輸，原生防注入。

13. 無緩衝查詢處理大結果集

$r = mysql_unbuffered_query("SELECT * FROM huge_table");
while ($row = mysql_fetch_assoc($r)) { /* 流式處理 */ }

不佔用客戶端內存緩存全量結果，首行即可處理。限制：需讀完或 free_result 才能執行下一查詢。

14. IP 地址用 UNSIGNED INT 存儲

$r = "UPDATE users SET ip=INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE id=$uid";

4 字節定長，比 VARCHAR(15) 省空間、比較快。PHP 對應 ip2long()/long2ip()。

15. 固定長度表更快（CHAR vs VARCHAR）

全定長列表（無 VARCHAR/TEXT/BLOB）便於定位行偏移，易緩存、易恢復。權衡：空間換時間。垂直分表將變長列拆出。

16. 垂直分表：冷熱數據分離

• 不常用大字段（地址、備註、Blob）拆到擴展表
• 高頻更新字段（last_login）拆出，減少主表更新導致緩存失效
• 避免頻繁 JOIN 回拼

17. 大批量 DELETE/INSERT 分批提交

while (true) {
    mysql_query("DELETE FROM logs WHERE log_date <= '2019-01-01' LIMIT 10000");
    if (mysql_affected_rows() == 0) break;
    usleep(50000); // 讓出 CPU，緩解鎖衝突
}

18. 越小的列越快

TINYINT < SMALLINT < MEDIUMINT < INT < BIGINT。日期無時間用 DATE 不用 DATETIME。預留增長空間但勿過度。

19. 選對存儲引擎

20. 善用 ORM 但警惕 N+1 問題

延遲加載導致大量微查詢。批量操作用事務合併。推薦 Doctrine、Eloquent。

21. 謹慎使用持久連接

mysql_pconnect() 理論復用連接，實際易導致連接數耗盡、內存洩漏、Apache 多進程模型不匹配。生產環境建議短連接+連接池。

總結

這 21 條實踐覆蓋查詢、索引、結構、引擎、連接、安全全鏈路。核心心法：理解底層機制（B+Tree、鎖、緩存、協議），在應用層做正確選擇。定期 EXPLAIN 慢查詢，持續優化，而非一次性調優。