本文共 1222 字，大约阅读时间需要 4 分钟。

在开发应用时，经常需要根据指定字段对结果进行排序。以市民表为例，假设需要查询城市为“杭州”的所有人姓名，并按姓名排序返回前1000个人的姓名和年龄。

SQL 语句示例

SELECT city, name, age FROM t WHERE city = '杭州' ORDER BY name LIMIT 1000;

全字段排序

在city字段上创建索引后，使用EXPLAIN命令分析执行情况。

排序流程

操作示意图

图中“按name排序”可能在内存中完成，也可能需要使用外部排序，这取决于sort_buffer_size参数设置。sort_buffer_size是MySQL为排序分配的内存大小。如果排序数据量小于该值，排序在内存完成；否则，使用磁盘临时文件辅助排序。

rowid排序

在上述算法中，仅读取原表数据一次，其余操作在sort_buffer和临时文件中执行。然而，如果查询返回的字段多，sort_buffer中字段数过多，会导致内存不足，需要分成多个临时文件排序，影响性能。

参数优化

如果MySQL认为排序单行字段过多，可调整参数max_length_for_sort_data。例如：

SET max_length_for_sort_data = 16;

此时，city、name、age三个字段总长度为36。修改后，sort_buffer中仅存储name和id。

排序流程调整

rowid排序示意图

对比全字段排序，rowid排序多访问了一次原表，增加了磁盘读取次数。MySQL优先选择全字段排序，当内存足够时尽量减少磁盘访问。

全字段排序 vs rowid排序

• 全字段排序：内存足够时优先选择，直接返回结果，不需要回表。
• rowid排序：内存不足时使用，排序数据量更多，但需回表取数据，增加磁盘访问。

适用场景

• 全字段排序：内存充足，减少磁盘读取。
• rowid排序：内存不足，适用于大数据量排序。

总结

MySQL的排序机制根据内存情况选择合适算法，优先使用内存排序减少磁盘访问，提升性能。