一、資料庫結構設計鐵律
| 原則 | 具體做法 | 效能影響 |
|---|
| 字段長度最小化 | 能用 SMALLINT 別用 INT；能用 VARCHAR(20) 別用 VARCHAR(255) | 減少儲存、索引體積、I/O、緩衝池佔用 |
| 優先固定長度 | CHAR 適合固定長 (MD5、UUID、國別碼)；VARCHAR 適合變長 | 固定長無碎片、定位快；但浪費空間時得不償失 |
| 盡量 NOT NULL | 除非業務明確需要 NULL，否則 NOT NULL DEFAULT '' 或 0 | NULL 需額外位圖、索引統計複雜、查詢優化受限 |
| 避免 ZEROFILL | 數值型別勿用 ZEROFILL | 屬於顯示層邏輯，應用層格式化即可 |
| 枚舉優於字串 | 值集固定且有限：用 ENUM('a','b','c') 或 SET | 儲存 1-2 字節，內部整數比較極快 |
二、索引優化黃金法則
1. 索引欄位特徵
- 長度越短越好：
INT > BIGINT > VARCHAR(20) > VARCHAR(255)
- 區分度越高越好：唯一值佔比越高，索引掃描越少。
COUNT(DISTINCT col)/COUNT(*) > 0.8 為佳
2. 多欄位索引 (組合索引) 建立依據
| SQL 模式 | 索引建立順序 | 原理 |
|---|
GROUP BY a, b, c | INDEX idx_abc (a, b, c) | 分組順序 = 索引順序，避免 filesort |
DISTINCT a, b, c | INDEX idx_abc (a, b, c) | 去重順序 = 索引順序 |
WHERE a=? AND b=? AND c=? | INDEX idx_abc (a, b, c) | AND 條件順序 = 索引順序，最左前綴原則 |
WHERE a=? OR b=? OR c=? | 單欄索引 INDEX idx_a(a)、INDEX idx_b(b)... | OR 條件難用組合索引，挑區分度最高的單欄索引 |
JOIN 關聯 ON t1.a = t2.a | 雙方 a 都建索引 | 關聯欄位必須有索引，否則全表掃描 |
3. 索引禁忌
- ❌ 主鍵上再加
UNIQUE 索引 —— 主鍵本身已是唯一索引
- ❌ 寫入密集表濫用索引 —— 每個索引都是寫入開銷，寫多讀少表索引越少越好
- ❌ 低區分度欄位建索引 (性別、狀態、布林) —— 掃描行數接近全表，優化器會放棄索引
- ❌ 索引欄位允許 NULL 且未處理 —— 統計資訊不準，建議
NOT NULL
三、查詢語句優化實戰技巧
1. 善用 EXPLAIN —— 優化器的 X 光機
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1 ORDER BY created_at DESC LIMIT 20;
-- 重點觀察：
-- type: const/eq_ref/ref/range/index/ALL (越靠前越好)
-- key: 實際使用的索引
-- rows: 預估掃描行數 (越小越好)
-- Extra: Using index / Using where / Using filesort / Using temporary
2. 具體改寫技巧
| 反模式 | 優化寫法 | 理由 |
|---|
SELECT * | SELECT id, name, status | 減少網路、I/O、緩衝池、覆蓋索引機會增大 |
| MySQL 函數處理結果 | 應用層處理 | 資料庫只負責存取，業務邏輯上移 |
LIMIT 100000, 20 深分頁 | 游標分頁 WHERE id > last_id LIMIT 20 | OFFSET 大時需掃描前 N 行，游標分頁 O(1) |
WHERE a=1 AND b=2 索引順序錯 | 條件順序匹配索引順序 | 最左前綴原則，順序錯誤導致索引失效 |
LIKE '%keyword%' | 全文索引 / 搜尋引擎 | 前導通配符不可用索引，全表掃描 |
| 子查詢 (MySQL 4.1+) | JOIN 替代 | 早期版本子查詢優化差，JOIN 更穩定 |
| 複雜函數嵌套 | 簡單表達式 / 計算欄位 | CPU 開銷大、不可用索引 |
WHERE a=1 OR b=2 | UNION ALL 分開查 | OR 通常導致全表掃描，UNION 可分別走索引 |
3. 進階優化模式
- 覆蓋索引：
SELECT a, b FROM t WHERE c=1 + INDEX idx_c_ab (c, a, b) —— 不回表，極快
- 延遲關聯：大表分頁
SELECT * FROM t JOIN (SELECT id FROM t WHERE cond LIMIT 100000, 20) tmp USING(id)
- 冗餘欄位/匯總表：高頻統計查詢預計算，定時刷新
總結：優化優先級金字塔
- 架構層：分庫分表、讀寫分離、快取層 (Redis)
- 結構層：合理範式/反範式、字段類型、分區表
- 索引層：組合索引、覆蓋索引、最左前綴
- SQL 層：EXPLAIN 驅動改寫、避免反模式
- 參數層：緩衝池、排序緩衝、連接池
- 硬體層：SSD、記憶體、CPU、網路
優化是系統工程，**索引與 SQL 改寫是性價比最高的杠桿**，硬體升級是最後手段。
