关于 order By 函数，你应该知道的更多-原创手记-慕课网

order by 是我们常用的 SQL 函数之一，如果我们需要返回结果集为有序的，则我们需要使用 order by 函数。

假设我们现在有一个需求，查询用户表中来自江西的用户，并且按照名字排序返回前 20 位，面对这种需求我们就需要利用 order by 函数来帮我们实现。

用户表定义如下：

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

我们的 SQL 语句就可以这样写：

select city,name,age from user where city='江西' order by name limit 20;

这样返回来的数据就是有序的，order by 函数背后是如何运行的？帮助我们完成结果集排序。

order by 函数要满足结果集排序要求，在 MySQL 数据库中，有两种方式来实现：利用索引满足和使用文件排序。

使用文件排序

先使用 explain 查看上述语句的执行情况
图片描述

可以看到 Extra 这一行，显示为 Using filesort，说明使用了文件排序。对于文件排序 MySQL 数据库会给每个线程分配一块排序内存（sort_buffer）。sort_buffer 的大小可以通过 sort_buffer_size 来控制，默认值为 262144。

在文件排序中根据 max_length_for_sort_data 情况，MySQL 提供了两种算法，如果我们返回的字段长度总和小于 max_length_for_sort_data 的值，这会将所有的字段都放入到 sort_buffer 中，我们称之为全字段排序。

如果返回的字段长度总和大于 max_length_for_sort_data 设置的值，则只会将 id，排序字段放入到 sort_buffer 中，最后排序后再回主键索引中查询所有数据，这种称之为 rowid 排序。

全字段排序和 rowid排序的区别在于数据在 sort_buffer 排序内存中排序后，全字段排序算法在sort_buffer 的临时表中已经有所有的返回字段，不需要再回主键索引中查询信息，可以直接返回给用户，而 rowid排序算法在sort_buffer 中只有部分字段，需要再回主键索引中查询数据，才能返回给用户。

可能看了还是有点懵逼，可以参考下面这张图，加深理解：
图片来源极客时间

文件排序优先在 sort_buffer 内存块中完成，如果需要排序的数据量大于 sort_buffer_size 的值，那么就需要借助磁盘临时文件来完成排序，那么性能就回有所下降，所以在内存允许的情况下，可以适当的增大 sort_buffer_size 的值，来减少使用磁盘文件排序的机率。

对于全字段排序和rowid排序，优选选择使用全字段排序，因为rowid排序需要回表查询，会造成磁盘读，不会被 MySQL 优选使用。在 MySQL8 中好像已经取消了。

利用索引排序

我们还可以利用联合索引来满足排序要求，因为索引在存储的时候就是有序的，所以在读取的时候自然就有序了。

对于这条 SQL 语句：

select city,name,age from user where city='江西' order by name limit 20;

我们可以创建一个city、name、age的联合索引，添加索引的SQL 语句为：

alter table user add index city_user_age(city, name, age);

再次使用 explain 查看 SQL 语句执行过程：

图片描述

还是看 Extra 这一行，不再是 Using filesort，而是 Using index了，说明使用联合索引。

此时这条语句的执行流程大概如下：

1、从索引 (city,name,age) 找到第一个满足 city='江西’条件的记录，取出其中的 city、name 和 age 这三个字段的值，作为结果集的一部分直接返回；
2、从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；
3、重复执行步骤 2，直到查到第 1000 条记录，或者是不满足 city='江西’条件时循环结束。

图片来源极客时间

使用索引来满足排序性能要好不少，但是维护索引需要不少的代价，任何一种技术没有最好，只有最合适。

小小的一个 order by 函数，背后还是有大学问的，希望今天的文章对您的学习或者工作有所帮助，如果您认为文章有价值，欢迎点个赞，谢谢。

最后

目前互联网上很多大佬都有 MySQL 相关文章，如有雷同，请多多包涵了。原创不易，码字不易，还希望大家多多支持。若文中有所错误之处，还望提出，谢谢。

互联网平头哥（id：pingtouge_java）
作者：平头哥