查詢緩存優化

1. 避免不可緩存函數
NOW()、RAND()、CURDATE() 等非確定性函數會導致查詢緩存失效。
✅ 改用應用層變量：WHERE created_at > '2024-01-15'

2. 善用 EXPLAIN
關注 type（目標：ref/range/eq_ref）、rows（掃描行數）、Extra（Using index/Using where/Using filesort）。

3. 只需一行用 LIMIT 1
SELECT 1 FROM users WHERE country='CN' LIMIT 1 比 SELECT * 快，引擎找到第一筆即停止。

索引設計

4. 為搜尋欄位建索引
LIKE 'prefix%' 可用索引，LIKE '%suffix' 不可 → 全文索引或關鍵詞表。

5. JOIN 欄位同類型、同字符集、同長度
DECIMAL 與 INT JOIN 不走索引；utf8 與 latin1 同理。

6. 杜絕 ORDER BY RAND()
全表排序殺手，改用「隨機起點 + 索引範圍掃描」法（見文章 73137）。

SQL 寫法習慣

7. 禁用 SELECT *
只取需要欄位：減少網傳、利用覆蓋索引、降低緩存池壓力。

8. 每表必有整型自增主鍵
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
InnoDB 聚簇索引順序寫入，避免頁分裂；VARCHAR 主鍵導致隨機 I/O。

9. 枚舉值用 ENUM 而非 VARCHAR
存儲 1 byte (TINYINT)，比較按內部整數，極快。適用：性別、狀態、類型等固定集合。

10. 用 PROCEDURE ANALYSE() 獲取結構建議
SELECT * FROM tbl PROCEDURE ANALYSE()\G
僅作參考，決策需結合業務增長預期。

欄位設計

11. 能 NOT NULL 就 NOT NULL
NULL 需額外位圖存儲，比較邏輯複雜（IS NULL vs =），索引統計準確度低。

進階特性

12. Prepared Statements
防注入、解析一次多次執行、二進制協議傳輸高效。5.7+ 支持查詢緩存。

13. 無緩衝查詢
mysqlnd / mysqli 支持 MYSQLI_USE_RESULT，大結果集流式處理，記憶體常數級。

架構層面

14. 拆大表：分區、分表、歸檔
按時間範圍分區（RANGE）、按業務鍵分表（Hash）、冷數據歸檔到 ClickHouse/ODPS。

15. 讀寫分離 + 連接池
ProxySQL / MaxScale 做讀寫分離，應用層連接池複用連線。

16. 監控慢查詢 + 定期審核
long_query_time=1，pt-query-digest 週報驅動優化迭代。

避坑補充

誤區	真相
索引越多越好	寫入每多一個索引就多一次 B+ 樹維護
count(*) 慢必加索引	InnoDB 無快速 count，改用近似值或計數表
字符集隨意	統一 utf8mb4，避免隱式轉換導致索引失效
大事務無妨	長事務阻塞 Purge、膨脹 Undo Log、鎖競爭

工具箱

• pt-query-digest：慢查詢分析
• MySQL Diagnostics：自動化巡檢
• MySQLTuner：配置建議

**優化無終點，只有持續的「測量 → 分析 → 調整 → 驗證」閉環。**