建表语句
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;
执行sql
select city,name,age from t where city='杭州' order by name limit 1000 ;
一、全字段排序的原理
用explain sql语句会得到下图
图中的Extra中的 Using filesort表示需要排序，MySQL会给每个线程分配一块内存用作排序叫做sort_buffer
1.上述排序sql在用全字段排序的流程
（1）初始化sort_buffer，确定可以放入city、name、age三个字段；
（2）根据索引city，找到第一个city=‘杭州’的主键id；
（3）根据主键id找到该行数据，返回city、name、age到sort_buffer中；
（4）依次寻找下一个主键id；
（5）循环执行步骤3、4，直到city != '杭州'为止；
（6）把sort_buffer的数据根据name做快速排序；
（7）按照结果返回1000行给客户端；
2.在MySQL中根据字段排序是在哪里进行的？
排序既可能只在内存中进行，也可能内存和磁盘同时进行；
在哪里进行是由什么决定的？
sort_buffer_size：排序时MySQL开辟的内存大小；
如果sort_buffer_size大于需要排序参数的内存，就只在内存排序；反之，就既在内存排序，也会用磁盘空间辅助进行；
怎么看MySQL是否使用了磁盘空间辅助排序呢？
/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on';
/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000;
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 计算Innodb_rows_read差值 */
select @b-@a;
在查看OPTIMIZER_TRACE这步可以看出下图：
number_of_tmp_files表示什么意思？
理解为什么是12，因为外部排序一般会使用归并排序的算法，MySQL把需要排序的数据分成12份，把每份单独排序后存入磁盘的12个文件，最后合并成一个文件返回给客户端。
如果number_of_tmp_files的值为0，则表示排序只在内存进行。
examined_rows=4000表示什么意思？
examined_rows=4000，表示参与排序的行数是 4000 行
sort_mode 里面的 packed_additional_fields 的意思是？
说明排序的过程中对字符串做了紧凑处理；按字符串的实际长度来分配空间。
select @b-@a 的返回结果是 4000表示什么？
表示整个执行过程扫描的行数
怎么把internal_tmp_disk_storage_engine 设置成 MyISAM？
internal_tmp_disk_storage_engine 这个参数是用来空值MySQL内部在创建临时表的时候，使用的默认存储引擎是什么，这个参数可以配置为MyISAM或者InnoDB两个。 5.7之后，默认的参数为InnoDB。
为什么InnoDB引擎select @b-@a 的返回结果是 4001？
因为查询 OPTIMIZER_TRACE 这个表时，需要用到临时表，而 internal_tmp_disk_storage_engine 的默认值是 InnoDB。如果使用的是 InnoDB 引擎的话，把数据从临时表取出来的时候，会让 Innodb_rows_read 的值加 1。
二、rowid排序
rowid排序会限制排序时单条数数据字段的总长度：例如
SET max_length_for_sort_data = 16；
意思就是如果返回的单条字段长度大于16，那么只会返回需要排序的字段和主键；以上例子中只会返回name、id；
1.rowid排序的执行流程和全字段排序流程有什么区别？
rowid排序的流程比全字段排序，在数据库取字段的时候只取name、id；在排完序后，还会再到表里根据主键再查一次，最终把结果返回给客户端。
2.为什么select @b-@a 这个语句的值变成 5000 了？
因为rowid排序比全字段排序多了回表里查询的过程。由于limit是1000，所以多了1000。
3.sort_mode 变成了 <sort_key, rowid>？
因为只用了这两个字段。
4.number_of_tmp_files 变成 10 了？
因为需要进行排序的字段小了，所需要分配的空间也变小了。
三、全字段排序和rowid排序的比较？
MySQL的原则是能尽量使用内存排序就不要用磁盘。如果实在是觉得内存不够用了才会用rowid排序，因为rowid排序会有增加的读盘操作，所以不推荐使用。
四、优化方案
1.优化方案1，使用city、name的联合索引
使用该方案的时候，只需要查找到满足city='杭州'的记录即可，不需要进行排序，最后再用主键进行查找<=1000条的记录即可。
Extra 字段中没有 Using filesort 了，也就是不需要排序了
2.进一步优化方案，使用city、name、age的联合索引
这个时候用到了覆盖索引的优化方案，覆盖索引的概念是索引就已经满足查询，不需要在回到主键索引去取数据。
Extra 字段里面多了“Using index”，表示的就是使用了覆盖索引
五、思考题
假设你的表里面已经有了 city_name(city, name) 这个联合索引，然后你要查杭州和苏州两个城市中所有的市民的姓名，并且按名字排序，显示前 100 条记录。如果 SQL 查询语句是这么写的 ：
mysql> select * from t where city in ('杭州',"苏州") order by name limit 100;
那么，这个语句执行的时候会有排序过程吗，为什么？如果业务端代码由你来开发，需要实现一个在数据库端不需要排序的方案，你会怎么实现呢？进一步地，如果有分页需求，要显示第 101 页，也就是说语句最后要改成 “limit 10000,100”， 你的实现方法又会是什么呢？
