| 場景 | 推薦類型 | 避坑指南 |
|---|
| 整數 ID | BIGINT UNSIGNED / INT UNSIGNED | 不用 BIGINT 存小範圍值 |
| 狀態/枚舉 | TINYINT / ENUM | ENUM 修改需 DDL，謹慎用於高頻變更字段 |
| 金額 | DECIMAL(19,4) | 嚴禁 FLOAT/DOUBLE |
| 長文本 | TEXT / LONGTEXT | 大字段拆表，主表只存指針 |
| 定長字符 | CHAR | 如 MD5、UUID、國家代碼 |
三、NULL 與 NOT NULL
- 默認 NOT NULL：節省 1 bit/行，優化器統計更準，索引可用性更好
- 確需 NULL：顯式聲明，業務語義上「未知」≠「空串/0」
- 禁止所有列默認 NULL：導致統計信息失真、
COUNT(*) 語義混淆
四、定長 vs 變長
- 定長（CHAR）：查詢快、無碎片、易計算偏移量
- 變長（VARCHAR/TEXT/BLOB）：節省空間、但需額外 1-2 字節長度前綴、易產生行溢出頁
- 策略：高頻訪問、長度固定 → CHAR；其餘 → VARCHAR
五、索引設計黃金法則
1. 建索引場景
WHERE、ORDER BY、GROUP BY、JOIN ON 出現的定長列- 高選擇性列（唯一值/總行數 > 0.1）優先
2. 聯合索引 = 最左前綴原則
INDEX idx(a,b,c)
-- 命中：WHERE a=1; WHERE a=1 AND b=2; WHERE a=1 AND b=2 AND c=3
-- 不命中：WHERE b=2; WHERE c=3; WHERE a=1 AND c=3
3. 字符串前綴索引
INDEX idx_name(name(10)) -- 區分度足夠時大幅縮小索引體積
4. 索引失效常見模式
LIKE '%abc%'（前導通配符）- 隱式類型轉換：
WHERE phone=13800000000（phone 是 VARCHAR）
- 函數運算：
WHERE DATE(create_time)='2024-01-01' → 改 WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'
OR 條件兩側索引不一致
5. 強制/忽略索引
SELECT * FROM t USE INDEX(idx_a) WHERE a=1;
SELECT * FROM t IGNORE INDEX(idx_b) WHERE b=2;
六、SQL 編寫最佳實踐
- 禁用
SELECT *：明確列名，減少網絡傳輸、避免覆蓋索引失效
- 條件下推：過濾性最強的條件寫在前面（優化器通常會自動調序，但複雜查詢建議顯式優化）
- 應用層計算：
WHERE create_time > ? 而非 WHERE create_time > NOW()
- InnoDB 禁用
COUNT(*) 全表掃描：
技巧：SELECT COUNT(*) FROM t WHERE secondary_key IS NOT NULL 走輔助索引，速度提升數量級
- 批量導入用
LOAD DATA INFILE：比 INSERT 快 10-20 倍
- 善用
EXPLAIN：關注 type、key、rows、Extra
七、表維護
OPTIMIZE TABLE：重建表空間，消除碎片（InnoDB 相當於 ALTER TABLE ... ENGINE=InnoDB）ANALYZE TABLE：更新統計信息，修正優化器誤判- 定期
CHECK TABLE 發現損壞
- 上一篇文章： MySQL 主從複製架構搭建與運維全攻略
- 下一篇文章： MySQL Query Cache 機制深度解析與最佳配置策略
Copyright ©
程式碼之家 All Rights Reserved
