HikariTish

1
有索引排序（理想）：
2
  ORDER BY age
3
  → idx_age 的 B+ 树叶子节点天然按 age 有序
4
  → 顺着链表读就行，零排序成本 ✅
5
  → EXPLAIN Extra: Using index
6

7
无索引排序（filesort）：
8
  ORDER BY age
9
  → age 上没有索引，全表扫描出所有行
10
  → 把数据扔进 sort_buffer，执行快速排序
11
  → EXPLAIN Extra: Using filesort ❌

1
数据量 ≤ sort_buffer_size 时：
2

3
  ① 全表扫描 → 把需要的字段读入 sort_buffer
4
  ② 在内存中执行快速排序（Quicksort）
5
  ③ 返回排好序的结果
6

7
  全程无磁盘 I/O，纯 CPU 操作，相对较快

1
数据量 > sort_buffer_size 时：
2

3
  ① 全表扫描 → 数据分批读入 sort_buffer
4
  ② 每批在内存中排序后写入磁盘临时文件
5
  ③ 对所有临时文件执行归并排序（Merge Sort）
6
  ④ 返回排好序的结果
7

8
  此时"file sort"名副其实——产生了大量磁盘 I/O，性能跳水

1
-- 开启 optimizer trace
2
SET optimizer_trace = 'enabled=on';
3

4
-- 执行你的 SQL
5
SELECT * FROM orders ORDER BY create_time;
6

7
-- 查看排序详情
8
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
9

10
-- 关闭
11
SET optimizer_trace = 'enabled=off';

1
"filesort_summary": {
2
  "rows": 150000,
3
  "examined_rows": 150000,
4
  "number_of_tmp_files": 0,      ← 0 → 全内存排序 ✅
5
  "sort_buffer_size": 262144,
6
  "sort_mode": "<sort_key, packed_additional_fields>"
7
}
8

9
"filesort_summary": {
10
  "rows": 150000,
11
  "examined_rows": 150000,
12
  "number_of_tmp_files": 28,     ← 28 个临时文件 → 磁盘排序 ❌
13
  "sort_buffer_size": 262144,
14
  "sort_mode": "<sort_key, packed_additional_fields>"
15
}

1
SHOW GLOBAL STATUS LIKE '%sort%';

1
-- 假设 city 上没有索引
2

3
-- 这条 SQL 会触发 filesort（通过排序实现分组）
4
SELECT city, COUNT(*) FROM users GROUP BY city;
5
-- Extra: Using filesort
6

7
-- 这条也一样（DISTINCT 靠排序去重）
8
SELECT DISTINCT city FROM users;
9
-- Extra: Using filesort

1
ALTER TABLE users ADD INDEX idx_city(city);
2

3
SELECT city, COUNT(*) FROM users GROUP BY city;
4
-- Extra: Using index  ← 完美

1
INDEX idx_a_b ON t(a ASC, b ASC)  -- 索引中 a 升序，b 升序
2

3
-- ✅ 排序方向与索引一致 → 不走 filesort
4
SELECT * FROM t ORDER BY a ASC, b ASC;
5

6
-- ❌ b 降序与索引方向冲突 → filesort！
7
SELECT * FROM t ORDER BY a ASC, b DESC;

1
INDEX idx_a_b_c ON t(a, b, c)
2

3
-- ✅ ORDER BY 满足最左前缀 → 不走 filesort
4
SELECT * FROM t WHERE a = 1 ORDER BY b, c;
5

6
-- ❌ 跳过了 a → filesort
7
SELECT * FROM t ORDER BY b, c;
8

9
-- ⚠️ a 是范围查询，b 的排序信息失效 → filesort
10
SELECT * FROM t WHERE a > 1 ORDER BY b;

1
没有索引时：
2
  SELECT id, name, age FROM users ORDER BY age;
3
  → 全表扫描 → 把 age、name、id 全部读入 sort_buffer → 排序 → 返回
4
  → Using filesort ❌
5

6
加了索引后：
7
  ALTER TABLE users ADD INDEX idx_age(age);
8
  SELECT id, name, age FROM users ORDER BY age;
9
  → 顺着 idx_age 的 B+ 树叶子节点从左往右读
10
  → 拿到的数据天然按 age 升序排好 → 零排序成本
11
  → Using filesort 消失 ✅

1
-- ❌ 低效：单列索引只能解决一部分
2
SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time;
3
-- INDEX idx_user ON orders(user_id);     ← 只解决 WHERE
4
-- INDEX idx_time ON orders(create_time);  ← 只解决 ORDER BY
5
-- MySQL 一次查询只用一棵索引，filesort 逃不掉
6

7
-- ✅ 高效：联合索引一步到位
8
ALTER TABLE orders ADD INDEX idx_user_time(user_id, create_time);
9
-- 先按 user_id 定位 → 组内 create_time 天然有序 → 零排序
10
-- EXPLAIN Extra: Using index condition（无 filesort）

1
sort_buffer_size = 256KB：
2
  100 个并发连接同时排序 → 100 × 256KB = 25MB  ← 可控
3

4
sort_buffer_size = 32MB：
5
  100 个并发连接同时排序 → 100 × 32MB = 3.2GB  ← 危险！

1
排查优先级：
2
  ① EXPLAIN 看有没有 filesort → 加索引消灭它（根治）
3
  ② 确定 filesort 不可避免 → 缩小 SELECT 列 + 加 LIMIT（缓解）
4
  ③ 仍有磁盘排序 → 监控 Sort_merge_passes → 适当调大 sort_buffer_size（兜底）

1
┌──────────────────────────────────────────────────────────────┐
2
│                   Using filesort 全景                         │
3
├──────────────────────────────────────────────────────────────┤
4
│                                                              │
5
│   Using filesort ≠ 一定用磁盘                                 │
6
│              ↓                                               │
7
│   ┌──────────────────────────────────────┐                   │
8
│   │   排序数据量 ≤ sort_buffer_size?       │                   │
9
│   └─────┬────────────────────┬───────────┘                   │
10
│      YES ↓              NO ↓                                 │
11
│   全内存排序             磁盘归并排序                           │
12
│   (Quicksort)          (Merge Sort)                          │
13
│   较快，CPU开销        大量磁盘I/O，性能跳水                     │
14
│                                                              │
15
│   排查工具：                                                   │
16
│     EXPLAIN → Extra: Using filesort                          │
17
│     optimizer_trace → number_of_tmp_files                    │
18
│     SHOW STATUS → Sort_merge_passes                          │
19
│                                                              │
20
│   消灭策略（优先级从高到低）：                                   │
21
│     ① 给 ORDER BY / GROUP BY 列建索引（尤其是联合索引）         │
22
│     ② 杜绝 SELECT *，只查必要字段                              │
23
│     ③ 加 LIMIT，限制排序行数                                   │
24
│     ④ 适当调大 sort_buffer_size（注意并发 × 内存的乘法陷阱）    │
25
│                                                              │
26
└──────────────────────────────────────────────────────────────┘