EXPLAIN SELECT * FROM order WHERE status = 1 ORDER BY create_time DESC;
-- Extra: Using where; Using filesort  ← 存在 filesort

索引排序的过程：
1. 扫描索引 B+Tree
2. 按顺序从叶子节点取出主键
3. 回表获取完整行数据
4. 直接返回结果（有序）
→ 不需要额外的排序操作，没有额外的 CPU/内存/IO

Filesort 的过程：
1. 扫描表/索引，获取排序字段值 + 主键/行指针
2. 在 sort buffer 中对这些值进行排序
3. 如果 sort buffer 放不下，使用磁盘临时文件做归并排序
4. 按排序后的顺序回表获取完整行
→ 额外的 CPU 消耗（排序）+ 可能的 IO 消耗（磁盘临时文件）

-- 1. ORDER BY 字段不是索引列
SELECT * FROM user ORDER BY register_time;

-- 2. ORDER BY 和 GROUP BY 字段不同
SELECT category, COUNT(*) FROM product GROUP BY category ORDER BY COUNT(*);

-- 3. 跨表排序
SELECT u.* FROM user u JOIN order o ON u.id = o.user_id
ORDER BY o.amount;

-- 4. ORDER BY 字段不满足最左前缀
-- 索引：(a, b, c)
ORDER BY b;       -- 未从最左列开始
ORDER BY a, c;    -- 跳过了 b

-- 5. 混合排序方向
-- 索引：idx(a ASC, b ASC)
ORDER BY a ASC, b DESC;  -- 方向不一致

-- 方式 1：EXPLAIN
EXPLAIN SELECT * FROM user ORDER BY register_time LIMIT 10;
-- Extra: Using filesort

-- 方式 2：状态变量
FLUSH STATUS;
SELECT * FROM user ORDER BY register_time LIMIT 10;
SHOW STATUS LIKE 'Sort%';
-- Sort_merge_passes: 归并排序的轮次（> 0 表示使用了磁盘临时文件）
-- Sort_range: 范围扫描排序次数
-- Sort_rows: 总排序行数
-- Sort_scan: 全表扫描排序次数

-- 原始查询
SELECT * FROM order WHERE status = 1 ORDER BY create_time DESC LIMIT 10;

-- 建立联合索引
CREATE INDEX idx_status_create ON order(status, create_time);

-- 优化后
SELECT * FROM order WHERE status = 1 ORDER BY create_time DESC LIMIT 10;
-- Extra: Using where  （不再有 Using filesort）
-- type: ref（走索引）

-- 需要排序字段包含在索引中
CREATE INDEX idx_status_create_amount ON order(status, create_time, amount);

-- 只查索引包含的字段
SELECT status, create_time, amount 
FROM order 
WHERE status = 1 
ORDER BY create_time DESC;
-- Extra: Using where; Using index（完全使用索引覆盖，效率最高）

-- 先过滤再排序，减少参与排序的行数
SELECT * FROM order 
WHERE status = 1 AND create_time > '2024-01-01'  -- 先过滤
ORDER BY create_time DESC 
LIMIT 10;

-- 不加过滤条件会导致排序大量行

# my.cnf
# 增大排序缓冲，减少磁盘排序
sort_buffer_size = 2M  # 默认 256K，通常 1M-8M

-- 统一排序方向可以走索引
-- 索引：idx_create(create_time ASC)
ORDER BY create_time ASC;   -- 走索引
ORDER BY create_time DESC;  -- 走索引（倒序扫描）

-- 以下情况无法走索引
ORDER BY create_time ASC, id DESC;  -- 混合方向

-- 当行数据很小时
-- sort buffer 中只存排序字段和主键
-- 排序完成后，按主键回表获取完整数据

-- 当行数据很大时
-- sort buffer 中存放排序字段和完整行数据
-- 不需要回表，但占用更多 sort buffer 空间

# 控制两种模式的切换
max_length_for_sort_data = 4096  # 默认 4096 字节
# 超过此值使用 Sort Row 模式
# 适当增大可以减少回表次数

-- 检查系统的 filesort 情况
SHOW GLOBAL STATUS LIKE 'Sort%';

-- 理想的系统状态
Sort_merge_passes = 0  -- 没有磁盘排序
Sort_rows << table_rows  -- 不会对全表排序

索引排序 > 单行 filesort（内存）> 多行 filesort（内存）> 磁盘 filesort