MySQL 存儲引擎、索引架構、事務模型與聯接算法核心筆記：MyISAM vs InnoDB、聚簇/二級索引、OLTP/OLAP、Join 算法演進

存儲引擎對比：MyISAM vs InnoDB

InnoDB 索引架構：聚簇 vs 二級

聚簇索引 (Clustered Index / Primary Key)

• **葉子節點 = 完整行數據** (而非指針)。
• 表數據按主鍵順序物理存儲 (邏輯順序，頁分裂維護)。
• **查詢走主鍵 = 直接命中數據，無回表**。
• 主鍵設計原則：**單調遞增 (自增/雪花 ID)**，減少頁分裂、碎片、索引體積。

二級索引 (Secondary Index / Non-clustered)

• 葉子節點存 **(索引列值, 主鍵值)**。
• 查詢流程：**遍歷二級索引 B+ 樹 → 獲取主鍵值 → 回表查聚簇索引 → 返回行數據**。
• **覆蓋索引：** 查詢列全包含在二級索引中 → **無需回表**，性能躍升。
• **代價：** 每個二級索引佔用額外空間，寫入需維護多棵 B+ 樹。

MyISAM 索引：全非聚簇

• 數據文件 `.MYD` 按插入順序存儲 (堆表)。
• 索引文件 `.MYI` 葉子節點存 **數據文件物理偏移量 (行指針)**。
• 主鍵/二級索引結構相同，無「回表」概念，直接按偏移讀數據文件。

業務負載分類：OLTP vs OLAP

聯接算法演進

1. Nested-Loop Join (NLJ) - 基礎

FOR each row in outer_table:
    FOR each row in inner_table:
        IF join_condition: OUTPUT

• 複雜度 O(M×N)，極慢。
• **有索引優化：** 內表走索引查找，近似 O(M × log N) (索引高度)。

2. Block Nested-Loop Join (BNL) - MySQL 5.6+ 默認

• 將外表分批讀入 **Join Buffer** (默認 256KB，`join_buffer_size`)。
• 內表掃描一次，與 Buffer 中所有行比較。
• **掃描內表次數 = ceil(外表行數 / Buffer容量)**，大幅減少內表掃描。
• 無索引時極大提升，有索引時優化器可能仍選 NLJ。

3. Hash Join - MySQL 8.0.18+

• **僅支持等值連接 (`=`)**，不支持範圍/模糊。
• 構建階段：外表數據讀入 Join Buffer → **構建哈希表**。
• 探測階段：掃描內表 → 計算哈希 → 探測匹配。
• 複雜度 O(M+N)，大表連接**數量級快於 BNL**。
• 控制：`optimizer_switch='hash_join=on/off'`。

算法選擇決策樹

1. 有合適索引 → **Index Nested-Loop Join** (最優)。
2. 無索引、等值連接、大表 → **Hash Join** (8.0.18+)。
3. 無索引、非等值/小表 → **Block Nested-Loop Join**。
4. 強制指定：`SELECT /*+ MAX_EXECUTION_TIME(1000) */` / `optimizer_hints` (8.0+)。

實戰測試代碼片段

CREATE TABLE myorder (
    id INT NOT NULL AUTO_INCREMENT,
    userid INT NOT NULL,
    orderdate DATE,
    comein INT DEFAULT 0,
    comeout INT DEFAULT 0,
    PRIMARY KEY (id)
);

INSERT INTO myorder VALUES (NULL, 11123940, '2014-05-10', 0, 50);

-- 分組聚合查詢
SELECT userid, orderdate, comein - comeout AS rest
FROM myorder
GROUP BY userid, orderdate;

-- 建立覆蓋索引優化分組
CREATE INDEX idx_order_cover ON myorder (userid, orderdate, comein, comeout);

-- 執行計劃分析
EXPLAIN SELECT userid, orderdate, comein - comeout AS rest
FROM myorder
GROUP BY userid, orderdate;