MySQL "Sort aborted: Out of sort memory" 錯誤根因分析與 sort_buffer_size 調優實戰

排序緩衝區溢位錯誤完整診斷：sort_buffer_size 設定原理、基準測試與最佳實踐

錯誤訊息 Sort aborted: Out of sort memory, consider increasing server sort buffer size 直指排序緩衝區不足。盲目調大參數可能導致記憶體耗盡與效能倒退。本文從原理、實測數據、生產建議三維度深度解析。

錯誤觸發機制

• 查詢包含 ORDER BY、GROUP BY、DISTINCT、UNION 等需排序操作
• 優化器選擇 filesort 演算法（無索引可用）
• 排序所需記憶體超過 sort_buffer_size，強制落盤合併排序
• 臨時文件寫入磁碟，若 tmpdir 空間不足或排序緩衝區極小，拋出此錯誤

sort_buffer_size 核心特性

實測數據：sort_buffer_size 對排序效能的影響

測試環境：1GB RAM Fedora VM、1萬列測試表、ORDER BY pad LIMIT 1 強制 filesort、100 併發、10 迭代

結論：跨越 256KB 閾值觸發 mmap() 後，效能下降 30%~80%。小緩衝區配合 LIMIT 反而更快，因減少記憶體分配開銷。

生產環境配置建議

聯合調優：相關排序參數

# 臨時表記憶體上限（配合 sort_buffer_size）
tmp_table_size = 64M
max_heap_table_size = 64M

# 讀取緩衝（順序掃描）
read_buffer_size = 2M
read_rnd_buffer_size = 8M

# JOIN 緩衝（無索引關聯）
join_buffer_size = 2M

排查檢查清單

1. 確認錯誤查詢 EXPLAIN 是否出現 Using filesort
2. 檢查 tmpdir 磁碟空間：df -h /tmp
3. 監控 Sort_merge_passes 狀態變數：SHOW GLOBAL STATUS LIKE 'Sort_merge_passes'; 數值大表示頻繁落盤合併
4. 優化查詢：增加覆蓋索引避免 filesort、使用 LIMIT 減少排序集合
5. Session 級動態調整：SET SESSION sort_buffer_size=4M; 僅影響當前連線

常見誤區澄清

• ❌「調大 sort_buffer_size 總能解決排序慢」→ 可能觸發 mmap 效能倒退、記憶體耗盡
• ❌「全域設 16MB 很安全」→ 1000 併發即需 16GB 記憶體，極易 OOM
• ✅「針對大排序查詢 Session 級臨時調大」→ 精準施策、風險可控
• ✅「優化索引消除 filesort 才是根治」→ 從源頭解決，零參數調整風險