我们在开发的过程中，肯定会经常碰到需要排序的需求，今天我们就聊一聊order by的工作原理。
首先我们创建一个表：

CREATE TABLE `t` (
 `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;

执行查询语句

select city,name,age from t where city='nj' order by name limit 2;

这条语句看起来还是很简单的，今天我们就聊一聊这条语句的执行流程。

全字段排序

在该表中我们在city上添加索引，如果数据过大，可以避免全表扫描。接下来我们通过explain命令查看语句的执行情况。

order by和 搜索列不匹配导致索引失效 order by索引原理_MySQL


我们可以看到在Extra字段中有Using filesort，也就是说需要排序，此时MySQL就会每个线程分配一块内存用于排序，称为sort_buffer。

该语句的执行流程：

  1. 首先初始化sort_buffer，确定name、city、age是哪个字段；
  2. 从索引city中找到满足 city=‘nj’ 的主键id；
  3. 从主键id中取出name、city、age三个字段的值，存入sort_buffer中；
  4. 在索引city中取下一个主键id，重复3,4直到不满足查询条件为止；
  5. 对sort_buffer中的数据按照name快排；
  6. 按照排序结果取前2行返回客户端。

按照name字段排序是在内存中完成的，但是也有可能是在外部进行的，这就要取决于排序所要的内存和参数sort_buffer_size的大小。
sort_buffer_size就是MySQL为排序开辟的内存大小，如果内存太小或者需要排序的数据太大，内存放不下就需要使用磁盘临时文件辅助排序，外部排序一般使用归并排序，会将数据分成多分，每一份单独排序，最终合并成一个有序的大文件。

rowid排序

在上述的全字段排序中，如果要返回的字段内容太多那么sort_buffer内存不够使用外部排序的性能会有很大的影响。在MySQL中，MySQL如果认为单行数据太大一般会采用另一种算法。

SHOW VARIABLES LIKE "%max_length_for_sort_data%"
SET max_length_for_sort_data = 8

我们将max_length_for_sort_data的值调小，使得MySQL使用id排序的算法，此时排序的结果少了city和age的值，就不能直接返回，还要有一个回表取值的步骤：

  1. 首先初始化sort_buffer，确定name和id字段；
  2. 从索引city找到满足city=“nj” 的主键id；
  3. 到主键id索引中取出整行，取name、id字段，存入sort_buffer；
  4. 从索引city去取一个主键id，重复3、4步骤，直到结束；
  5. 对sort_buffer中数据按照name排序；
  6. 遍历排序结果，并取出排序的前2行，并按照id值回到原表取出city、name、age字段返回客户端。

全字段排序和rowid排序比较

两者对比，我们发现在MySQL中如果担心排序内存太小，影响排序效率，可以使用rowid排序，但是rowid排序需要回到原表取数据，会造成磁盘读，一般不会被优先选择。
我们也可以看到MySQL的设计思想：内存足够时，尽量多利用内存，减少磁盘访问。

在MySQL中排序其实是一个成本比较高的操作，这是就会有人要问，所有的order by都是需要排序的吗，如果不排序应该会对系统的消耗小很多，执行的效率也会高很多。
如果我们能保证从city索引上取出的数据都是按照name进行排序的，那也就不需要进行排序了。我们可以使用联合索引：

alter table t add index city_user(city, name);

此时我们在通过explain命令查看执行计划。

order by和 搜索列不匹配导致索引失效 order by索引原理_mysql_02


在Extra字段找那个没有Using filesort，也就不需要排序了由于(name、age)本身有序，也就不需要把所有的数据全部读一遍，只需找到满足的前2条就可以。