10.2 儲存演算法的效能調校實戰#

概念說明#

把前九章的工具串成一條調校流程。核心心法只有一句：先量測，再假設，後修改。儲存效能問題九成出在「I/O 次數太多」或「並行度不足」，而非 CPU。

實戰守則（依診斷順序）：

量測分頁層級的統計：緩衝池命中率、每秒讀/寫頁數、隨機與順序比例。命中率 < 95% 通常代表記憶體不足或掃描失控。
找熱點查詢：用 EXPLAIN 看執行計畫——樹高幾、回表幾次、是否全表掃描。一次「索引掃描代替全掃」可省 1000 倍 I/O。
寫入路徑：交易日誌是否在慢裝置上（WAL fsync 常是最大瓶頸）；group commit 是否開啟；SSD 寫入放大是否過高。
結構選擇：讀多寫少 → B+tree；寫多讀少 → LSM；等值查詢為主 → 雜湊；範圍掃描為主 → B+tree；資料靜態 → ISAM。
參數槓桿：分頁大小、緩衝池大小、compaction 速度、預讀視窗——每次只動一個，量測前後差異。

「頁 I/O 預算」是實用的思考框架：給定延遲目標（如 10ms），除以裝置延遲（NVMe 10μs）得單查詢 I/O 預算（1000 次）；B+tree 高 3，預算充裕；但若是 3 個 B+tree 的交集查詢，就要考慮覆蓋索引或 zone map 砍掉 I/O。

Python 範例：查詢成本診斷器#

import math

def btree_io(n_rows, row_bytes, page_kb=16, key_bytes=8, ptr_bytes=8):
    """估計主鍵 B+tree 樹高與查詢 I/O"""
    branch = page_kb * 1024 // (key_bytes + ptr_bytes)
    rows_per_leaf = page_kb * 1024 // row_bytes
    leaves = math.ceil(n_rows / rows_per_leaf)
    height = 1
    nodes = leaves
    while nodes > 1:
        nodes = math.ceil(nodes / branch)
        height += 1
    return height, branch, rows_per_leaf

def diagnose(n_rows, row_bytes, device_latency_us=10, target_ms=10):
    h, branch, rpl = btree_io(n_rows, row_bytes)
    io_budget = target_ms * 1000 / device_latency_us
    print(f"資料 {n_rows:,} 列 → 樹高 {h}（{branch:,} 路分支，葉頁 {rpl} 列）")
    print(f"點查詢 I/O ≈ {h} 次 ≈ {h * device_latency_us} μs（預算 {io_budget:.0f} 次）")
    if h * device_latency_us > target_ms * 1000:
        print("→ 超預算：考慮覆蓋索引或提高緩衝池")
    return h

diagnose(10_000_000, 200)
diagnose(10_000_000, 4000)

# 緩衝池命中率對平均延遲的影響
def avg_latency(hit_ratio, cold_io_us=10):
    return hit_ratio * 0.5 + (1 - hit_ratio) * cold_io_us   # 命中≈記憶體 0.5μs

for hr in [0.99, 0.95, 0.90, 0.80]:
    print(f"命中率 {hr:.0%} → 平均延遲 {avg_latency(hr):>5.2f} μs")

複雜度與 I/O 成本分析#

命中率從 99% 降到 80%，平均延遲從 0.6μs 升到 2.1μs；若冷 I/O 是 HDD 的 10ms，則從 0.1ms 升到 2ms——20 倍。這就是「加記憶體」永遠是第一優先的原因。
索引調校：多個單欄索引 vs 一個複合索引——複合索引一次掃描同時過濾多條件，I/O 相乘變相加。
寫入調校：把多個小交易合併成批次，攤提日誌 fsync；這是 group commit 與 LSM memtable 的共同哲學。

練習#

為你的資料（自行假設列數與列寬）算出 B+tree 樹高，評估是否需要分區（partition）。
範例診斷器加入「二級索引回表」模式，比較覆蓋索引前後的 I/O。
選擇一個真實系統（MySQL/RocksDB），找出兩個可調參數並說明它們對應本書哪一章的哪個公式。