假设你要查询城市是“杭州”的所有人名字， 并且按照姓名排序返回前1000个人的姓名、 年龄。
假设这个表的部分定义是这样的：
-- 创建表t
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;全字段排序
为避免全表扫描， 我们需要在city字段加上索引。在city字段上创建索引之后， 我们用explain命令来看看这个语句的执行情况。
Extra这个字段中的“Using filesort”表示的就是需要排序， MySQL会给每个线程分配一块内存用于排序， 称为sort_buffer。
为了说明这个SQL查询语句的执行过程， 我们先来看一下city这个索引的示意图。
通常情况下， 这个语句执行流程如下所示（即全字段排序流程）：
1）初始化sort_buffer， 确定放入name、 city、 age这三个字段；
2）从索引city找到第一个满足city='杭州’条件的主键id， 也就是图中的ID_X；
3）到主键id索引取出整行， 取name、 city、 age三个字段的值， 存入sort_buffer中；
4）从索引city取下一个记录的主键id；
5）重复步骤3、 4直到city的值不满足查询条件为止， 对应的主键id也就是图中的ID_Y；
6）对sort_buffer中的数据按照字段name做快速排序；
7）按照排序结果取前1000行返回给客户端。
全字段排序示意图：
注：图中“按name排序”这个动作， 可能在内存中完成， 也可能需要使用外部排序， 这取决于排序所需的内存和参数sort_buffer_size。
sort_buffer_size， 就是MySQL为排序开辟的内存（sort_buffer） 的大小。 如果要排序的数据量小于sort_buffer_size， 排序就在内存中完成。 但如果排序数据量太大， 内存放不下， 则不得不利用磁盘临时文件辅助排序。
问1：什么是sort_buffer？
答：sort buffer是MySQL Server层的一种优化。
1）MySQL会给每个线程分配一块内存用于排序，称为sort_buffer。
2）sort_buffer_size：决定sort_buffer的大小，默认：256KB。
- 如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。
- 如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。
3）max_sort_length：决定了放入sort_buffer的一行数据的最大长度，默认：1KB。
4）判断策略：MySQL根据 sort_buffer_size / max_sort_length 估算出sort_buffer可容纳的行数；然后与实际待排序的行数比较，如果待排序行数小于该行数，则在内存排序。
5）可通过HINT显式指定一个语句的sort_buffer大小，比如：
select /*+ SET_VAR(sort_buffer_size = 10M)*/ host, user from mysql.user orderby user desc;问2：sort_buffer和innodb_sort_buffer的区别是什么？
- innodb_sort_buffer：是在执行DML语句时，执行数据更新时，对数据进行排序，然后写入磁盘，以此使得数据能够尽可能”按顺序”插入B+树，以提升性能。
- innodb_sort_buffer_size：决定了innodb_sort_buffer的大小，默认：1MB。
问3：如何确定一个排序语句是否使用了临时文件？
答：
/* 打开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中看到是否使用了临时文件。
number_of_tmp_files表示的是， 排序过程中使用的临时文件数。 你一定奇怪， 为什么需要12个文件？ 内存放不下时， 就需要使用外部排序， 外部排序一般使用归并排序算法。 可以这么简单理解， MySQL将需要排序的数据分成12份， 每一份单独排序后存在这些临时文件中。 然后把这12个有序文件再合并成一个有序的大文件。
如果sort_buffer_size超过了需要排序的数据量的大小， number_of_tmp_files就是0， 表示排序可以直接在内存中完成。否则就需要放在临时文件中排序。 sort_buffer_size越小， 需要分成的份数越多， number_of_tmp_files的值就越大。
注1：当sort_buffer_size小于需要排序的数据量的大小时，排序流程为：
- 把待排序的数据读入sort_buffer，直到读满为止。
- 对sort_buffer中的数据进行排序，并把排好序的数据存在一个临时文件中（每一份单独排序后的数据存放到一个临时文件中）。
- 清空sort_buffer，继续往sort_buffer中读入剩余待排序数据。
- 跳转至步骤二（直至处理完所有数据）。
- 最后，对所有临时文件使用归并排序方法进行排序。
注2：对于InnoDB表来说， 执行全字段排序会减少磁盘访问， 因此会被优先选择。
rowid排序
全字段排序算法只对原表的数据读了一遍， 剩下的操作都是在sort_buffer和临时文件中执行的。 但这个算法有一个问题， 就是如果查询要返回的字段很多的话， 那么sort_buffer里面要放的字段数太多， 这样内存里能够同时放下的行数很少， 要分成很多个临时文件， 排序的性能会很差。
问1：如果MySQL认为排序的单行长度太大会怎么做呢？
答：如果MySQL认为单行太大， 它会换一个算法。什么时候换算法由参数max_length_for_sort_data决定。
max_length_for_sort_data， 是MySQL中专门控制用于排序的行数据的长度的一个参数。 它的意思是， 如果单行的长度超过这个值， MySQL就认为单行太大， 要换一个算法。
假设city、 name、 age这三个字段的定义总长度是36， 我把max_length_for_sort_data设置为16（如下所示）， 我们再来看看计算过程有什么改变。
// 把用于排序的行数据长度设为16
SET max_length_for_sort_data = 16;新算法放入sort_buffer的字段， 只有要排序的列（ 即name字段） 和主键id。
但这时， 排序的结果就因为少了city和age字段的值， 不能直接返回了， 整个执行流程就变成如下所示的样子（rowid排序）：
1）初始化sort_buffer， 确定放入两个字段， 即name和id；
2）从索引city找到第一个满足city='杭州’条件的主键id， 也就是图中的ID_X；
3）到主键id索引取出整行， 取name、 id这两个字段， 存入sort_buffer中；
4）从索引city取下一个记录的主键id；
5）重复步骤3、 4直到不满足city='杭州’条件为止， 也就是图中的ID_Y；
6）对sort_buffer中的数据按照字段name进行排序；
7）遍历排序结果， 取前1000行， 并按照id的值回到原表中取出city、 name和age三个字段返回给客户端。
rowid排序示意图：
问2：根据这个说明过程和图示， 你可以想一下， 这个时候执行select @b-@a， 结果会是多少呢？
rowid排序的OPTIMIZER_TRACE部分输出：
图中的examined_rows的值还是4000， 表示用于排序的数据是4000行。 但是select @b- @a这个语句的值变成5000了。因为排序后需要输出的1000行数据都需要回表操作，所以多了1000行。
注：
- sort_mode变成了， 表示参与排序的只有name和id这两个字段。
- number_of_tmp_files变成10了， 是因为这时候参与排序的行数虽然仍然是4000行， 但是每一行都变小了， 因此需要排序的总数据量就变小了， 需要的临时文件也相应地变少了。
全字段排序 VS rowid排序
因为rowid排序涉及回表，因此在内存够用的情况下优先选择全字段排序。
这也是MySQL的一个设计思想：如果内存够用，就要多利用内存，尽量减少磁盘访问。
对于InnoDB表来说， rowid排序会要求回表多造成磁盘读， 因此不会被优先选择。
由此得出MySQL做排序是一个成本比较高的操作。
问：是不是所有的order by都需要排序操作呢？
答：并不是所有的order by语句， 都需要排序操作的。
从上面分析的执行过程， 我们可以看到， MySQL之所以需要生成临时表， 并且在临时表上做排序操作， 其原因是原来的数据都是无序的。
如果能够保证从city这个索引上取出来的行， 天然就是按照name递增排序的话，那么就不用再排序了。
利用索引减少排序
就前面的表t而言，可以在该表上创建一个city和name的联合索引， 对应的SQL语句是：
alter table t add index city_user(city, name);作为与city索引的对比， 我们来看看这个索引的示意图。
在联合索引下，只要city的值是杭州， name的值就一定是有序的。
整个查询过程如下：
- 从索引(city, name)找到第一个满足city='杭州’条件的主键id。
- 到主键id索引取出整行， 取name、 city、 age三个字段的值， 作为结果集的一部分直接返回。
- 从索引(city, name)取下一个记录主键id。
- 重复步骤2、 3， 直到查到第1000条记录， 或者是不满足city='杭州’条件时循环结束。
联合索引查询示意图：
可以看到， 这个查询过程不需要临时表， 也不需要排序。 接下来， 我们用explain的结果来印证一下。
从图中可以看到， Extra字段中没有Using filesort了， 也就是不需要排序了。 而且由于(city,name)这个联合索引本身有序， 所以这个查询也不用把4000行全都读一遍， 只要找到满足条件的前1000条记录就可以退出了。 也就是说， 在我们这个例子里， 只需要扫描1000次。
覆盖索引减少回表
虽然上述联合索引可以避免排序，但仍存在回表操作，仍会有磁盘IO消耗。
针对这个查询， 我们可以创建一个city、 name和age的联合索引， 对应的SQL语句就是：
alter table t add index city_user_age(city, name, age);这时， 对于city字段的值相同的行来说， 还是按照name字段的值递增排序的， 此时的查询语句也就不再需要排序了。
这样整个查询语句的执行流程就变成了：
- 从索引(city,name,age)找到第一个满足city='杭州’条件的记录， 取出其中的city、 name和age这三个字段的值， 作为结果集的一部分直接返回；
- 从索引(city,name,age)取下一个记录， 同样取出这三个字段的值， 作为结果集的一部分直接返回；
- 重复执行步骤2， 直到查到第1000条记录， 或者是不满足city='杭州’条件时循环结束。
覆盖索引查询执行示意图：
再来看看explain的结果
可以看到， Extra字段里面多了“Using index”， 表示的就是使用了覆盖索引， 性能上会快很多。
注：并不是说要为了每个查询能用上覆盖索引， 就要把语句中涉及的字段都建上联合索引， 毕竟索引还是有维护代价的。 这是一个需要权衡的决定。
小结：思考题
假设你的表里面已经有了city_name(city, name)这个联合索引， 然后你要查杭州和苏州两个城市中所有的市民的姓名， 并且按名字排序， 显示前100条记录。 如果SQL查询语句是这么写的 ：
select * from t where city in ('杭州',"苏州") order by name limit 100;思考1：这个语句执行的时候会有排序过程吗？
答：有排序。虽然有(city,name)联合索引， 对于单个city内部， name是递增的。 但是由于这条SQL语句不是要单独地查一个city的值， 而是同时查了"杭州"和" 苏州 "两个城市， 因此所有满足条件的name就不是递增的了。 也就是说， 这条SQL语句需要排序。
思考2：如何避免排序？
答：用到(city,name)联合索引的特性， 把这一条语句拆成两条语句（将结果集在业务端排序，取前100条）， 执行流程如下：
1）执行select * from t where city=“杭州” order byname limit 100; 这个语句是不需要排序的， 客户端用一个长度为100的内存数组A保存结果。
2）执行select * from t where city=“苏州” order byname limit 100; 用相同的方法， 假设结果被存进了内存数组B。
3）现在A和B是两个有序数组， 然后你可以用归并排序的思想， 得到name最小的前100值， 就是我们需要的结果了。
思考3：如果有分页需求，要显示第101页，即语句最后要改成 “limit 10000,100”，如何实现？
分别执行如下两条语句，将结果集在业务端排序，取前100条；
select * from t where city in ('杭州') order by name limit 10100;
select * from t where city in ("苏州") order by name limit 10100;这时候数据量较大， 可以同时起两个连接一行行读结果， 用归并排序算法拿到这两个结果集里，按顺序取第10001~10100的name值， 就是需要的结果了。
当然这个方案有一个明显的损失， 就是从数据库返回给客户端的数据量变大了。所以， 如果数据的单行比较大的话， 可以考虑把这两条SQL语句改成下面这种写法：
select id, name from t where city="杭州" order by name limit 10100;
select id, name from t where city="苏州" order by name limit 10100;然后， 再用归并排序的方法取得按name顺序第10001~10100的name、 id的值， 然后拿着这100个id到数据库中去查出所有记录。
上面这些方法， 需要你根据性能需求和开发的复杂度做出权衡。
思考4：无条件的order by语句是否走索引？
场景1）只有order by create_time，即便create_time上有索引，也不走。
因为优化器认为走二级索引再去回表成本比全表扫描排序更高。所以选择走全表扫描,然后根据前述两种排序方式选择一种来排序。
场景2）order by create_time limit m，如果m值较小，可以走索引。
因为优化器认为根据索引有序性去回表查数据，然后得到m条数据，就可以终止循环，那么成本比全表扫描小，则选择走二级索引。
即便没有二级索引，mysql针对order by limit也做了优化，采用堆排序。
注：说白了都是基于CBO的考量。
思考5：使用group by语句是否走索引？
场景1）如果是group by a，a上不能使用索引，则走rowid排序。
场景2）如果是group by limit，不能使用索引，则走堆排序。
场景3）如果是只有group by a，a上有索引，则根据选取值不同，索引的扫描方式不同：
- select * from t group by a -- 走索引全扫描。
- select a from t group by a --走的是索引松散扫描，也就说只需要扫描每组的第一行数据即可，不用扫描每一行的值。
思考6：bigint/int类型，在其后加数字是否影响其占用空间大小？
答：不影响，bigint(1)和bigint(19)都能存储2^64-1范围内的值，int是2^32-1。只是有些前端会根据括号里来截取显示而已。
