在對以一個 SQL 的執行計劃進行分析時，我們會關注 Extra 這個屬性，當這個屬性的值是 Using filesort 時，就需要看下 SQL 是否需要優化一下了。

1.Using filesort

比如下面的這個 SQL，排序字段上沒有索引, 執行計劃 Extra 就顯示 Using filesort ：

EXPLAIN SELECT * FROM test4 ORDER BY time_stamp;

圖片

也可以通過 FORMAT=JSON 獲取更詳細的信息：

EXPLAIN FORMAT=JSON SELECT * FROM employees ORDER BY first_name;

結果如下：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1034355.60"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "857663.00"
      },
      "table": {
        "table_name": "test4",
        "access_type": "ALL",
        "rows_examined_per_scan": 857663,
        "rows_produced_per_join": 857663,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "5160.00",
          "eval_cost": "171532.60",
          "prefix_cost": "176692.60",
          "data_read_per_join": "65M"
        },
        "used_columns": [
          "id",
          "a",
          "b",
          "date",
          "date_time",
          "time_stamp"
        ]
      }
    }
  }
}

Using filesort 是指執行的 SQL 無法使用索引進行排序，需要在內存或磁盤上進行排序。如下圖：

圖片

所以 Using filesort 不是一定會使用磁盤排序。

那 Using filesort 對性能有哪些影響呢？

• 排序操作需要大量 CPU，CPU 消耗很大；
• 如果在內存中排序，排序操作會消耗很大內存；
• 當數據無法在內存中完成排序時，就會用到磁盤臨時文件，消耗磁盤 I/O。

由于 CPU、內存或磁盤的消耗，會直接導致 SQL 性能下降，執行時間長。

2.原因分析

2.1 ORDER BY 未走索引

這是最常見的原因，主要包括以下幾種情況：

• ORDER BY 字段未加索引；
• ORDER BY 語句中字段排序順序跟索引順序不一致；
• 有復合索引，但 ORDER BY 語句中的列不符合最左前綴原則；
• ORDER BY 語句中使用了表達式或函數。

上面這些情況都可能走 filesort。

2.2 JOIN 語句

SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1 order by t2.c2;

MySQL 會選擇結果集較小的表作為驅動表，比如上面的 SQL，兩張表使用 c1 這個字段進行 JOIN，如果選擇了 t1 做為驅動表，但是使用 t2 的字段 c2 進行排序，那排序操作就會走不上索引。

2.3 UNION 語句

EXPLAIN SELECT id,a,b FROM test4 UNION SELECT * FROM test5 ORDER BY a;

在 union 語句中，如果對結果集進行排序，也有可能會走 filesort。

2.4 GROUP BY

SELECT age,COUNT(age) ca FROM db_staff GROUP BY age;

上面是一個分組的 SQL，在 MySQL 8.0 以前，GROUP BY 默認是會對分組字段做排序的，即使 sql 中沒寫 ORDER BY，也會排序。如果分組字段未加索引，很容易走 filesort

如果想要 sql 語句不排序，可以在 sql 尾部加 order by null。

2.5 DISTINCT 語句

EXPLAIN SELECT DISTINCT b FROM test5 ORDER BY b;

如果在 DISTINCT 語句中使用 ORDER BY，可能會走 filesort。

2.6 臨時表

當查詢需要創建臨時表，比如上面的 UNION、DISTINCT 和 GROUP BY，如果臨時表結果集需要排序，則可能使用 filesort。

3.優化思路

3.1 索引優化

如果 ORDER BY 字段沒能走索引，可以考慮進行優化：

• ORDER BY 只有一個字段，則為該字段增加索引；
• ORDER BY 有多個字段，可以考慮復合索引。比如下面的示例：

CREATE INDEX idx_a_b ON orders(a, b);

EXPLAIN SELECT * FROM test4 WHERE a = 'testa' ORDER BY b;

• 排除可能使索引失效的情況，比如函數或表達式、ORDER BY 和索引順序不一致；
• 沒有必要排序的場景，可以不排序，比如使用 ORDER BY NULL 語句。

JOIN 優化

確保 ORDER BY 子句的字段是驅動表上的索引字段，并且符合最左前綴原則，必要時可以強制使用索引 FORCE INDEX。

配置優化

增大 sort_buffer_size 配置，避免走磁盤排序。不過一定要注意，sort buffer 是會話私有的，高并發場景下，可能導致數據庫內存升高。

4.總結

當執行計劃的 Extra 屬性出現 Using filesort 時，大概率我們的 SQL 需要優化了。可能查看 SQL 屬于那種情況，針對性地進行優化。