《高性能 MySQL》核心筆記：架構、鎖、索引與 Schema 設計精華
這本經典之作從應用層到硬體平台全鏈路覆蓋，系統梳理如下核心知識點。
第 1 章：架構與歷史
事務隔離級別
- READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ（InnoDB 預設）、SERIALIZABLE
- InnoDB 採用 Next-Key Locking 解決幻讀，實現 RR 級別
隱式與顯式鎖
-- 共享鎖（讀鎖）
SELECT ... LOCK IN SHARE MODE;
-- 排他鎖（寫鎖）
SELECT ... FOR UPDATE;
兩階段鎖協議 (2PL)
- 加鎖階段：可申請鎖，不可釋放鎖
- 解鎖階段：可釋放鎖，不可申請鎖
- 優點：提高併發度（解鎖不必等事務結束）
- 缺點：無法解決死鎖（加鎖無順序要求）
MVCC 多版本併發控制
InnoDB 借用 MVCC 名稱，實現讀不阻塞寫、寫不阻塞讀。核心依賴 undo log 版本鏈。
第 4 章：Schema 與資料類型優化
選擇最優資料類型三原則
| 原則 | 核心 | 實例 |
|---|
| 越小越好 | 佔用少 → 磁碟/記憶體/CPU 快取命中率高 | 用 SMALLINT 代替 INT、用 DATE 代替 DATETIME |
| 簡單越好 | 字符串比較涉及字符集、排序規則，複雜且慢 | IP 存 INT UNSIGNED、時間存 TIMESTAMP/DATETIME |
| 盡量避免 NULL | NULL 導致索引、統計、比較邏輯複雜 | 定義 NOT NULL DEFAULT '' 或 0 |
ENUM 陷阱
- 看似節省空間，實則
ALTER TABLE 修改枚舉值會重建表
- 排序按內部整數值而非字面值，易導致業務邏輯錯誤
- 建議：查找表 代替 ENUM
正規化 vs 反正規化
-- 熱點計數器：分槽減少鎖競爭
CREATE TABLE hit_counter (
slot TINYINT UNSIGNED NOT NULL PRIMARY KEY,
cnt INT UNSIGNED NOT NULL
) ENGINE=InnoDB;
-- 更新隨機槽
UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;
-- 讀取總和
SELECT SUM(cnt) FROM hit_counter;
-- 每日重置版本
CREATE TABLE daily_hit_counter (
day DATE NOT NULL,
slot TINYINT UNSIGNED NOT NULL,
cnt INT UNSIGNED NOT NULL,
PRIMARY KEY (day, slot)
) ENGINE=InnoDB;
-- 原子 upsert
INSERT INTO daily_hit_counter (day, slot, cnt)
VALUES (CURRENT_DATE, RAND()*100, 1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;
第 5 章：索引深度解析
B-Tree 家族演進
- B-Tree：內部節點存鍵+數據，葉子節點存數據
- B+Tree：內部節點僅存鍵，葉子節點存數據並雙向鏈表連接（InnoDB 採用）
- 優勢：範圍查詢只需掃描葉子鏈表、非葉子節點更多鍵 → 樹高更低 → 磁碟 IO 更少
索引查找成本模型
查找一行：3 次磁碟 IO + 3 次記憶體二分查找（假設 3 層 B+Tree）。平衡二叉樹需 4~5 次 IO，資料量越大優勢越明顯。
聚簇索引 vs 二級索引
- 聚簇索引：葉子節點 = 完整行數據（主鍵）
- 二級索引：葉子節點 = 主鍵值 → 回表查聚簇索引
- 覆蓋索引：查詢列全在二級索引中，避免回表
實戰金句
「索引是通往高效查詢的橋樑，但每座橋都有維護成本。只在讀遠大於寫、區分度高、頻繁出現在 WHERE/ORDER BY/GROUP BY 的列上建索引。」
