美團技術團隊實戰分享：MySQL 索引原理與慢查詢優化方法論

前言：慢查詢優化是開發必修課

MySQL 以高性能、低成本成為互聯網首選 RDBMS。但「好馬配好鞍」，讀寫比約 10:1 的典型業務中，**複雜查詢是效能殺手**。美團核心業務系統部積累上百個慢查詢案例，本文從索引底層原理出發，構建系統化優化方法論。

一、 索引底層原理：B+ 樹為什麼是最佳選擇？

1. 從字典查找到磁盤 I/O 模型

索引本質是通過不斷縮小範圍將隨機查找轉為順序查找。但資料庫面臨磁盤 I/O 瓶頸：

• 一次磁盤 I/O ≈ 9 ms（尋道 5 ms + 旋轉延遲 4.17 ms）
• CPU 同期可執行 40 萬條指令
• 百萬級數據若無索引，百萬次 I/O = 災難

OS 預讀機制將相鄰數據一次性讀入內存頁（Page，通常 4KB/8KB），**索引結構設計目標：將查找過程中的磁盤 I/O 次數控制在常數級（樹高 h）。**

2. B+ 樹結構與查找過程

• 非葉子節點：僅存儲索引鍵值 + 指針，不存實際數據，扇出度大，樹高低。
• 葉子節點：存儲完整數據行，通過雙向鏈表鏈接，支援範圍查詢。
• 查找 29 示例：根節點 → 第 2 層 → 葉子節點，**3 次 I/O 完成百萬級數據定位**。

3. B+ 樹兩大核心性質

1. 索引列越小，樹越矮：m = 頁大小 / 索引項大小，h = log(m+1)N。INT(4B) 優於 BIGINT(8B)，字符串慎用長索引。
2. 最左前綴匹配：聯合索引 (a,b,c) 按 a → b → c 順序建樹。查詢 WHERE a=1 AND b=2 可用索引；WHERE b=2 無法利用索引（缺少引導列 a）。

二、 建索引五大鐵律

1. 最左前綴匹配：遇到範圍查詢（>、<、LIKE、BETWEEN）停止匹配。a=1 AND b=2 AND c>3 AND d=4 建 (a,b,d,c) 可用到 d。
2. = 和 IN 可亂序：優化器會自動重排。
3. 高區分度列優先：count(DISTINCT col)/count(*) 越大越好。JOIN 字段建議 > 0.1（平均 1 行掃 10 行）。
4. 索引列不參與計算：from_unixtime(create_time) = '2014-05-29' ❌ → create_time = unix_timestamp('2014-05-29') ✅
5. 擴展索引優於新建：已有 (a)，需 (a,b) 直接 ALTER TABLE ... ADD INDEX (a,b) 覆蓋舊索引。

三、 慢查詢優化標準流程（6 步法）

1. 跑一下：加 SQL_NO_CACHE 確認真慢。
2. 單表 WHERE 條件分別跑：鎖定返回行數最少的表/字段，找最高區分度列。
3. EXPLAIN 對比預期：核心指標 rows，越小越快。
4. ORDER BY LIMIT 讓排序表優先查。
5. 了解業務場景：數據分佈、調用頻率、容忍延遲。
6. 按原則加索引，觀察結果，不符合預期回滾重來。

四、 實戰案例深度剖析

案例 1：複雜 SQL 改寫 —— 200 倍提升

原 SQL：cm_log 關聯派生表（employee + emp_certificate），EXPLAIN 顯示派生表掃描 6.3 萬行，再與 379 行 cm_log 關聯，Using temporary; Using join buffer。

優化思路：將 OR 條件拆分為兩個 UNION 子查詢，提前關聯 cm_log 過濾，利用 UNION 天然去重替代 DISTINCT。

結果：1.87s → 10ms，**掃描行數從 6.3 萬降至 379+379**。

案例 2：業務場景決策 —— 低區分度列也能建索引

SQL：WHERE accurate_result=1 AND sync_status IN (0,2,4)

理論困境：兩列區分度極低（accurate_result 只有 -1/0/1，sync_status 99% 是 3），教科書說不該建索引。

業務實情：定時任務每 5 分鐘掃描，處理完將 sync_status 改為 1，**符合條件的行始終只有 ~1000 行，極度不平衡**。

實測：建立 idx_acc_status(accurate_result, sync_status)，6.22s → 200ms，**30 倍提升**。

啟示：不了解業務場景，不敢動索引。

案例 3：ORDER BY LIMIT 陷阱 —— 13000 倍提升後的隱患

原 SQL：4 表 JOIN 後 ORDER BY c.created_time DESC LIMIT 10，13s。
EXPLAIN：驅動表 org_emp_info 掃 8849 行，最終排序前鎖定 77 萬行！

優化 1：改用 EXISTS 子查詢，先按 contact.created_time 索引排序取 Top 10 再 JOIN 過濾。13s → 1ms。

極端反例：修改參數導致內層無匹配行，MySQL 持續取 10 行再 JOIN 失敗，**近乎全表掃描，2 分 18 秒！**

結論：並非所有 SQL 都能優化；優化必須覆蓋極端參數，否則引入新災難。

五、 寫在最後：查詢容易，優化不易

任何資料庫層面優化都抵不上應用架構優化。同樣的 MySQL，能支撐 Google/Facebook，也可能撐不住個人站點。熟悉優化器、索引內核、執行計劃，是寫出高性能 SQL 的前提。