创建表

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;

查询：city为杭州的所有人的名字，按照姓名排序返回前1000个人的姓名和年龄

select city,name,age from t where city = '杭州' order by name limit 1000

这个SQL的执行流程是什么样的呢？

全字段排序

首先为了避免全表扫描，我们在city上加了普通索引。
执行explain 命令，Extra字段显示”Using filesort”，表示需要排序，MySQL为每个线程分配一块内存用于排序，称为“sort buffer”

1. 初始化sort buffer，确定放入name,age,city三个字段
2. 从索引取出主键id，回表查询主键索引取出整行，取name,age,city三个字段，放入sort buffer中
3. 从索引city取下一个记录的主键id
4. 重复上边的步骤，回表查询，放入sortbuffer，直到查询到city的值不是“杭州”为止
5. 对sort buffer中的数据根据name快速排序
6. 按照排序结果取前1000行返回给客户端

这个排序过程可能是在sort buffer中完成，也可能需要使用外部排序，这取决于sort buffer size和排序所需内存的大小。如果sort buffer size大于排序所需内存，就在内存中完成；如果小于，就不得不利用磁盘临时文件辅助排序。MySQL会将需要排序的数据分成多份，每一份单独排序之后存在这些临时文件中，最终把这些临时文件再合并成一个有序的大文件。

rowid排序

上边的算法都是在sort buffer或者临时文件中执行的，但是这种算法的问题是，如果要返回字段太多的话，可能一行占用的空间很大，这样sort buffer中的行数就会很少，就需要分成多个临时文件进行排序，排序的性能会很差。
可以通过修改这个参数来改变排序的算法，set max_length_for_sort_data = 16 ，这个参数是专门用于控制排序的行长度的，如果单行的长度超过这个值，MySQL就会换一种算法。
新的算法放入sort buffer中的字段只有主键id 和 name索引字段(需要排序的列)
执行流程如下

1. 初始化sort buffer，确定放入两个字段name和id
2. 从索引city中找到第一个等于“杭州”的记录，根据主键id回表查主键索引，取出整行记录，取id、name字段放入sort buffer中
3. 从索引city取下一条记录
4. 重复上边的步骤，知道city的值不符合条件为止
5. 对sort buffer中的数据根据name进行排序
6. 排序结果取前1000行，并按照id的值回到原表中取出city、name、age字段返回给客户端

称为rowid排序
与上边的算法对比，多访问了一次主键索引。由于排序完成后又根据id查询了一次，所以多扫描了1000行，也就是5000行。
**

全字段排序 VS rowid排序

如果MySQL认为内存够用，就会使用全字段排序，比rowid少一次查询，减少了磁盘的访问。

所有的order by都会排序吗？

答案：不是，如果我们要排序的字段是已经根据索引排好序的，也就是说如果name字段是已经根据city字段排好序的，就不需要重新排序了，效率会更高。此时我们可以创建(city,name)联合索引。
此时的Extra中就没有Using filesort了。

如果我们使用覆盖索引的话，也就是把要查询的字段建立联合索引，那么就减少了在city索引上查询出主键id然后回表的操作，此时的Extra字段为Using index。但是也不能为了业务一味的建索引，要权衡利弊，索引维护的代价是很高的。