美團技術團隊分享：MySQL 索引原理與慢查詢優化實戰案例全解析

美團技術團隊分享：MySQL 索引原理與慢查詢優化實戰案例全解析

引言：一個慢查詢引發的思考

SELECT COUNT(*) FROM task
WHERE status=2 AND operator_id=20839
  AND operate_time>1371169729 AND operate_time<1371174603
  AND type=2;

開發建議「給每個字段加索引」，實則應建聯合索引並遵循最左前綴原則，將範圍列 operate_time 放最後，且需綜合評估所有相關查詢確定最佳列順序。

索引底層原理：B+Tree 為何是最佳選擇

1. 磁盤 IO 特性：一次 IO ≈ 9ms (尋道 5ms + 旋轉延遲 4.17ms)，CPU 同期可執行 40 萬指令，IO 是瓶頸。
2. 預讀與頁：OS 每次讀取一頁 (4KB/8KB)，局部性原理使預讀極高效。
3. B+Tree 特性：
   • 高度可控：3 層 B+Tree 可存億級數據，查找僅需 3 次 IO。
   • 非葉節點只存鍵值 + 指針，葉節點存完整數據 (聚簇索引) 或主鍵值 (二級索引)。
   • 鍵值越小 (如 INT 4 字節 vs BIGINT 8 字節) → 每頁存更多鍵 → 樹越矮 → IO 越少。
4. 最左前綴匹配：組合索引 (a,b,c) 按順序建樹，查詢必須從最左列開始匹配，遇到範圍查詢 (>,<,>=,<=,LIKE) 停止匹配後續列。

建索引五大核心原則

1. 最左前綴匹配：a=1 AND b=2 AND c>3 AND d=4 → 索引 (a,b,c,d) 中 d 無法用到；調整為 (a,b,d,c) 則全部生效。
2. = 和 IN 可亂序：優化器自動調整順序匹配索引。
3. 高區分度優先：count(DISTINCT col)/count(*) > 0.1 (JOIN 字段建議)。低區分度列 (性別/狀態) 不宜建索引。
4. 索引列不參與計算：from_unixtime(create_time) = '...' 失效，改為 create_time = unix_timestamp('...')。
5. 擴展索引不新建：已有 (a) 需 (a,b) 直接修改原索引。

慢查詢優化標準流程 (6 步法)

1. 運行驗證 (加 SQL_NO_CACHE) 確認確實慢。
2. EXPLAIN 定位驅動表 (返回行數最少的表優先)。
3. EXPLAIN 驗證執行計劃是否符合預期 (從小表驅動)。
4. ORDER BY ... LIMIT 讓排序表優先查。
5. 理解業務場景 (關鍵：區分度低的列在特定業務下可能高效)。
6. 按原則加索引，觀察結果，不符合預期回第 1 步。

實戰案例精講

案例 1：複雜 OR 條件 JOIN 改寫為 UNION (提速 200 倍)

原 SQL：cm_log JOIN 子查詢 (employee LEFT JOIN emp_certificate) 含 OR 條件，產生臨時表 + join buffer，掃描 6 萬行，耗時 1.87s。

優化：拆分為兩個 UNION (去重) / UNION ALL (不去重) 子查詢，各自簡單 JOIN，利用現有索引，耗時 10ms。

案例 2：低區分度列在業務保證下建索引 (提速 30 倍)

SQL：WHERE accurate_result=1 AND sync_status IN (0,2,4)，兩列區分度極低 (各 3 個值)，理論不建索引。

業務場景：每 5 分鐘掃描處理，處理後將 sync_status 改為 1，符合條件行極少 (~1000)。

優化：建立 INDEX (accurate_result, sync_status)，業務保證的數據傾斜使索引極高效，6.22s → 200ms。

案例 3：ORDER BY LIMIT 陷阱導致無法優化 (反面教材)

SQL：4 表 JOIN 後 ORDER BY c.created_time DESC LIMIT 10，執行計劃 rows 很小但實際排序 77 萬行，13s。

嘗試優化：改為 EXISTS 子查詢 + 主表排序，參數正常時 0.00s，但極端參數 (內層無匹配) 導致 MySQL 反覆取 10 行嘗試 JOIN，耗時 2 分 18 秒 (比原 SQL 更差)。

結論：並非所有 SQL 可優化，極端場景下優化可能適得其反，需業務層面解決 (如分頁改為游標、增加過濾條件)。

總結

「查詢容易，優化不易，且寫且珍惜！」
數據庫層面優化永遠抵不上應用架構優化。同樣的 MySQL，能支撐 Google/Taobao，也可能撐不起個人站點。