萬盛學電腦網

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

MySQL ORDER BY 深度解析:索引優化、filesort 機制與避坑指南

基本語法

SELECT col1, col2 FROM table_name ORDER BY col1 [ASC|DESC], col2 [ASC|DESC];
  • 預設 ASC (升序),DESC 降序
  • 大小寫不敏感

兩種排序執行方式

1. 索引掃描排序 — 最優

條件:

  • ORDER BY 列與索引列順序、方向完全一致
  • 查詢計畫 type 為 ref, range, index
  • 無 Using filesort 標記

範例:

-- 表 A 有 PRIMARY KEY (c1)
EXPLAIN SELECT * FROM A WHERE c1 > 2 ORDER BY c1;
-- type=range, key=PRIMARY, Extra=Using where (無 filesort)

2. filesort 排序 — 成本較高

當無法利用索引順序時,MySQL 在記憶體 (sort_buffer_size) 中排序,不足則寫臨時檔案。

觸發 filesort 的常見情況:

  • ORDER BY` 列非索引或非索引前綴
  • 混合 ASC/DESC (如 ORDER BY a ASC, b DESC)
  • WHERE 使用索引 A,ORDER BY` 使用索引 B
  • 多表 JOIN 且排序列非驅動表 (第一張非 const 表) 列
  • GROUP BY 與 ORDER BY 列不一致

filesort 兩代演算法

特性原演算法 (≤4.1)改良演算法 (≥4.1)
排序緩衝內容(sort_key, row_id)(sort_key, row_id, select_columns)
讀取資料次數兩次 (排序後再回表)一次 (直接從緩衝返回)
記憶體佔用較小較大 (含選取欄位)
啟用條件永遠MAX_LENGTH_FOR_SORT_DATA > 選取欄位總長度

優化 ORDER BY 實戰策略

1. 善用覆蓋索引

-- 查詢只需 id, name,建立複合索引
CREATE INDEX idx_status_name ON orders (status, name);
SELECT id, name FROM orders WHERE status = 1 ORDER BY name;
-- 可使用索引排序,且為覆蓋索引,無需回表

2. 調整排序緩衝區

-- 會話級臨時調大 (預設 256K)
SET SESSION sort_buffer_size = 4M;
SET SESSION max_length_for_sort_data = 2048;  -- 強制使用改良演算法

3. 避免 SELECT *

只選取必要欄位可減少 sort_buffer 佔用,觸發改良演算法機率更高。

4. LIMIT 配合索引

-- 只要前 10 筆,索引排序極快
SELECT * FROM logs ORDER BY created_at DESC LIMIT 10;

5. 強制索引/忽略索引

SELECT * FROM t FORCE INDEX (idx_a) ORDER BY a;  -- 強制用索引排序
SELECT * FROM t IGNORE INDEX (idx_b) ORDER BY b; -- 忽略不適合索引

EXPLAIN 關鍵欄位解讀

  • Extra: Using filesort → 需排序優化
  • Extra: Using temporary; Using filesort → 需建立臨時表再排序 (多表 JOIN 排序非驅動表列)
  • type: index → 全索引掃描排序 (較慢)

常見誤區

  1. 以為有索引就能排序:索引列順序、方向必須與 ORDER BY 完全匹配
  2. 忽略字符集影響:不同校對規則導致索引順序與預期不符
  3. 過度依賴 filesort:大數據量下應優先優化索引或分頁策略
copyright © 萬盛學電腦網 all rights reserved