《高效能 MySQL》查詢效能優化核心筆記
系統梳理查詢生命週期、資料訪問優化、查詢重構策略、MySQL 客戶端/服務端協議、查詢快取、優化器原理、關聯執行機制、排序優化、執行引擎、結果返回機制六大核心模組。
一、查詢生命週期與慢的根因
客戶端 → 服務器 → 解析 → 優化器生成執行計劃 → 執行(存儲引擎交互) → 返回結果
核心洞察：查詢慢 90%+ 因**訪問資料過多**（掃描行數 ≫ 返回行數）。優化本質：**減少資料訪問量**。
二、優化資料訪問：兩大檢視點
- 應用層：是否請求了不需要的資料？
— SELECT *、多表關聯全欄位、重複查詢同資料、未加 LIMIT 的大結果集。
— 對策：精確列表、覆蓋索引、應用層快取、分頁限制。
- 服務器層：MySQL 是否掃描了過多行？
— 三大指標：**響應時間**、**掃描行數**、**返回行數**（慢日誌均記錄）。
— 理想：掃描行數 ≈ 返回行數；典型比率 1:1 ~ 10:1。
— 訪問類型（EXPLAIN type）：system > const > eq_ref > ref > range > index > ALL。
三、WHERE 條件應用三層次（由優到劣）
- 存儲引擎層索引過濾：索引直接定位匹配行，最省 I/O。
- 服務器層覆蓋索引掃描：
Using index，索引滿足所有需求列，無需回表。
- 服務器層回表後過濾：
Using where，先回表讀全行再過濾，最費 I/O。
四、查詢重構四大策略
| 策略 | 核心思想 | 適用場景 |
|---|
| 拆分大查詢 | 大 DELETE/UPDATE 分批次，減少鎖持有、日誌膨脹、復制延遲 | 定期清理歷史資料、大批量狀態變更 |
| 分解關聯查詢 | JOIN 拆為多單表查詢，應用層組裝 | 緩存命中率高、鎖競爭大、需按 ID 順序讀取 |
| 切分複雜邏輯 | 將超大 SQL 拆解為多簡單 SQL | 優化器統計不足導致計劃劣化時 |
| 冗余/匯總表 | 預計算聚合結果，查詢直接讀匯總表 | 儀表板、報表、高頻統計 |
五、MySQL 客戶端/服務端半雙工協議
- 任一時刻單向傳輸：要麼客戶端發請求，要麼服務端推結果。
max_allowed_packet 限制單包大小，大查詢/大結果需調大。- 結果集**流式推送**：服務端生成第一行即開始推送，客戶端不可中途截斷（只能粗暴斷連）。
mysql_unbuffered_query() (PHP) / 游標模式：避免客戶端驅動全量緩存結果集，節省記憶體。
六、查詢快取機制與局限
- 哈希匹配（大小寫敏感），命中則跳過解析/優化/執行，直接返回結果（含權限檢查）。
- 任意表寫入即失效相關快取，**寫多讀少場景快取命中率極低、反成負擔**。
- MySQL 8.0 移除查詢快取，建議應用層快取替代。
七、優化器與關聯執行
- 基於成本優化器：成本單位 = 隨機讀 4K 頁代價，統計資訊（頁數、基數、長度、分佈）驅動決策。
- 關聯算法：**嵌套循環關聯**，驅動表逐行 → 被驅動表索引查找匹配。
- 執行計劃：左側深度優先樹，
EXPLAIN EXTENDED + SHOW WARNINGS 可見重寫後 SQL。
- 搜索空間：n 表關聯 n! 種順序，超過
optimizer_search_depth 改貪婪搜索。
八、排序優化：兩代算法與臨時表陷阱
| 算法 | 特點 | 代價 |
|---|
| 兩次傳輸 (舊) | 讀排序鍵+行指針 → 排序 → 再按指針回表讀數據 | 大量隨機 I/O，慢 |
| 單次傳輸 (新) | 一次讀取所有需求列 → 內存排序 → 直接返回 | 順序 I/O，快；但佔用更多 sort_buffer |
臨時表陷阱：關聯查詢 + ORDER BY 非驅動表欄位 → Using temporary; Using filesort，即使 LIMIT 10 也會先建全量臨時表再排序，極慢。
九、執行引擎與 Handler API
- 優化階段為每表建立
handler 實例，獲取欄位/索引統計資訊。
- 底層僅幾十個介面（如
index_first、index_next、rnd_next），像積木組合完成複雜操作。
十、結果返回增量流式
- 最後關聯完成、第一行就緒即開始推送，服務端無需緩存全量結果。
- 雙重優勢：服務端記憶體穩定、客戶端最快拿到首行資料。
- 上一篇文章： MySQL LIMIT 分頁語法詳解：OFFSET 與 ROW COUNT 正確理解
- 下一篇文章： SQL Server Management Studio Express (SSMSE) 安裝圖文教程
Copyright ©
程式碼之家 All Rights Reserved
