MySQL 5.0 官方手冊深度解讀：查詢優化器核心邏輯 (第三篇)

查詢效能估算：磁碟搜尋次數模型

MySQL 使用 B-tree 索引，單次查詢磁碟搜尋次數估算公式：

log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1

• index_block_length：通常 1024 bytes
• data_pointer_length：通常 4 bytes
• 範例：50 萬行、MEDIUMINT 索引 (3 bytes) → 約 4 次搜尋
• 索引體積：500,000 × 7 × 3/2 ≈ 5.2 MB (假設 2/3 快取)，多數在記憶體中，實際 1-2 次 I/O

寫入成本：約 4 次搜尋定位新索引位置，更新約 2 次搜尋。

快取效應與 key_buffer_size

只要索引能放入快取 (OS 或 MySQL key_buffer_size)，效能僅隨資料量對數增長。資料超出快取才急劇下降。對策：隨資料增長同步調大 key_buffer_size (MyISAM)。

加速 SELECT 查詢的實戰清單

1. 檢查索引：EXPLAIN SELECT ... 確認 type、key、rows
2. 更新統計資訊：ANALYZE TABLE tbl 或 myisamchk --analyze，更新 Cardinality (不重複值估算)，優化器據此選擇連接順序
3. 預排序資料：myisamchk --sort-index --sort-records=1 (按索引 1 排序)，適合按主鍵順序大量讀取，首次執行耗時極長

WHERE 子句優化：優化器自動重寫規則

MySQL 優化器在執行前自動進行以下轉換 (持續改進中，文檔未盡列舉)：

常數表定義

• 空表或單行表
• 對 PRIMARY KEY 或 UNIQUE NOT NULL 索引所有部分做等值比較

連接優化策略

• 嘗試所有連接順序排列，選擇成本最低者
• 若 ORDER BY/GROUP BY 欄位來自同一表，該表優先連接
• 若 ORDER BY 與 GROUP BY 不一致或涉及非第一表欄位 → 建立臨時表
• SQL_SMALL_RESULT 強制使用記憶體臨時表

索引使用決策：全表掃描 vs 索引掃描

• 舊版啟發式：最佳索引範圍 > 30% 表大小 → 全表掃描
• 現代優化器：綜合評估表大小、行數、I/O 區塊大小、統計資訊，**不再使用固定百分比**
• 覆蓋索引：查詢欄位全在索引中 (且為數值型) → 僅讀索引樹，不讀資料檔

OR 條件優化：Index Merge (5.0 新功能)

傳統限制：每表最多用一個索引。Index Merge 允許合併多個 ref/ref_or_null/range 掃描結果：

SELECT * FROM t WHERE key1 = 10 OR key2 = 20;
-- 可同時使用 key1 和 key2 索引，結果合併

EXPLAIN 顯示 type=index_merge, key=key1,key2

適用條件：OR 條件分別能用不同索引，且為 ref/range 類型。

IS NULL 優化

• col IS NULL 等同 col = constant 優化，可用索引/範圍查詢
• <=> (NULL-safe 等值) 同樣優化
• WHERE key=1 OR key=2 OR key IS NULL → 索引合併
• NOT NULL 欄位的 IS NULL → 優化器直接判定為 FALSE (除非來自 LEFT JOIN 右表)
• 4.1.1+：col = expr AND col IS NULL 額外優化 (常見於子查詢)，EXPLAIN 顯示 ref_or_null
• 限制：單查詢僅優化一層 IS NULL

DISTINCT 優化

• 常與 ORDER BY 觸發臨時表
• LIMIT n 配合 DISTINCT：找到 n 筆不同值即停止
• 未使用的表：找到第一匹配行即停止讀取該表 (連接順序由 EXPLAIN 決定)

LEFT/RIGHT JOIN 優化邏輯

A LEFT JOIN B ON cond 執行規則：

1. B 依賴 A 及 A 的所有依賴表
2. A 依賴 LEFT JOIN 條件中除 B 外的所有表
3. ON 條件決定如何從 B 讀取 (WHERE 不影響)
4. 標準連接優化均執行，例外：依賴表必在被依賴表之後讀取
5. 循環依賴 → 錯誤
6. 若 A 中行匹配 WHERE，但 B 無匹配 ON → 產生全 NULL 的 B 行
7. 反向查詢技巧：LEFT JOIN ... WHERE b.col IS NULL 且 b.col NOT NULL → 找到第一匹配即停止

常見效能陷阱與修正

-- 慢：b 在 a 之前讀取，但 LEFT JOIN 強制 a 先於 d 讀取，導致 b 全表掃描
SELECT * FROM a, b LEFT JOIN c ON c.key=a.key LEFT JOIN d ON d.key=a.key
WHERE b.key = d.key;

-- 快：調整表順序，b 移至最前
SELECT * FROM b, a LEFT JOIN c ON c.key=a.key LEFT JOIN d ON d.key=a.key
WHERE b.key = d.key;
-- 或使用 STRAIGHT_JOIN 強制順序

4.0.14+ LEFT JOIN 消除優化

若 WHERE 條件對產生的 NULL 行恆為 FALSE (如 t2.col=5 且 col NOT NULL)，LEFT JOIN 自動轉為內連接，優化器可自由調整順序。

總結：優化器如何思考

1. 邏輯重寫：常數摺疊、條件簡化、子查詢展開
2. 統計驅動：Cardinality、直方圖 (8.0+)、索引選擇性
3. 成本模型：I/O 成本 + CPU 成本，評估所有連接順序排列
4. 規則修正：LEFT JOIN 順序強制、Index Merge、覆蓋索引
5. 執行引擎：Iterator 模式逐行產出，LIMIT 早期終止

理解優化器邏輯，善用 EXPLAIN、ANALYZE TABLE、OPTIMIZE TABLE、合理索引設計，是查詢調校的核心技能。