深入浅出 MySQL 索引与排序机制详解与优化实践

两种排序方式概述

filesort 排序方式

当无法使用索引排序时采用这种方式：

1. 数据获取阶段
2. 排序处理阶段
3. 结果返回阶段

index 排序方式

利用索引本身的有序特性：

• 直接利用索引
• 无额外开销
• 性能优势明显

条件

• ORDER BY 子句必须与索引列顺序完全匹配
• 排序方向必须与索引定义一致

filesort 算法详解

双路排序（Two-pass sorting）

1. 第一次磁盘扫描：只读取排序字段和行指针
2. 排序阶段：在 sort buffer 中排序
3. 第二次磁盘扫描：回表读取完整数据

单路排序（Single-pass sorting）

1. 单次磁盘扫描：一次性读取所有列
2. 内存排序
3. 结果返回

优化建议

-- 避免使用 SELECT *
SELECT id, name, create_time FROM large_table ORDER BY create_time DESC;

-- 增加排序缓冲区大小
SET sort_buffer_size = 4 * 1024 * 1024;

EXPLAIN 分析

Using filesort

表示使用了文件排序操作。

Using index

表示查询使用了覆盖索引，最理想的情况。

示例

-- 可以使用索引
EXPLAIN select id from wzk_user order by id;

-- 使用filesort
EXPLAIN select id from user_info order by username;

索引与排序的关系

无法使用索引排序的情况

1. 排序方向不一致
2. 字段顺序不匹配
3. 混合使用 ASC 和 DESC
4. 包含非索引字段

ASC DESC

• ASC：升序
• DESC：降序