萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL ORDER BY 性能優化三板斧:原理、參數與實戰

ORDER BY 兩種執行路徑

  1. Using Index(索引有序掃描):
    查詢條件與索引鍵完全匹配,且存取類型為 ref/range/index 時,MySQL 直接按索引順序返回,零排序開銷,效能最優。
  2. Filesort(檔案排序):
    無可用有序索引時,MySQL 在 sort_buffer_size 記憶體區排序,超出則落磁碟臨時表。每個線程獨享 Sort Buffer,高併發時記憶體壓力大。

Filesort 兩代演算法

演算法邏輯特點
雙路排序 (雙通道)1. 讀取排序欄位 + Row ID
2. Sort Buffer 排序
3. 再按 Row ID 回表讀取其餘欄位
MySQL 4.1 前唯一算法,兩次 I/O,佔用 Sort Buffer 小
單路排序 (單通道)一次性讀取所有需回傳欄位,在 Sort Buffer 直接排序4.1+ 引入,以空間換時間,減少隨機 I/O,但佔用更多 Sort Buffer

MySQL 依 max_length_for_sort_data 與 SELECT 列總長度比較決定用哪種:
列總長 < 參數值 → 單路;否則雙路。

三大優化手段

1. 加大 max_length_for_sort_data 強制單路排序

SET GLOBAL max_length_for_sort_data = 4096;  -- 預設 1024,視記憶體調大

前提:機器記憶體充裕,且 SELECT 列長度可控。過大導致 Sort Buffer 壓力劇增、甚至分段排序反而變慢。

2. 精簡 SELECT 列表,縮減排序行寬度

  • 嚴禁 SELECT *,僅取業務必要欄位。
  • 大字段(TEXT/BLOB/長 VARCHAR)若非排序後必須顯示,改用子查詢/延遲關聯回表獲取。
  • 使行長度自然落入 max_length_for_sort_data 門檻,誘導優化器選單路排序。

3. 調大 sort_buffer_size 減少分段排序

SET GLOBAL sort_buffer_size = 4M;  -- 預設 256K,連線級參數

關鍵權衡:
• 連線級參數 × 最大併發連線 = 總記憶體佔用上限,切勿盲目設大(如 256M × 1000 連線 = 250GB OOM)。
• 超過 2MB 可能觸發 mmap() 替代 malloc(),反而降低分配效率。
• 建議範圍:256K ~ 4MB,視併發與記憶體容量酌情。

實戰檢核清單

  1. EXPLAIN 確認是否 Using filesort。
  2. 能否加覆蓋索引消除 Filesort?
  3. 若不可避免 Filesort:
     a. 縮減 SELECT 列 → 觸發單路排序。
     b. 適度調大 sort_buffer_size → 減少臨時表分段。
     c. 觀察 Sort_merge_passes 狀態變數,非零表示發生分段,需優化。

進階技巧:延遲關聯(見專文)

對於 ORDER BY id LIMIT 100000, 20 類深分頁,先用覆蓋索引取主鍵,再回表,可將掃描行數從 10 萬降至 20,配合上述參數調優效果更佳。

copyright © 萬盛學電腦網 all rights reserved