MySQL-16：order by如何运作

order by：根据提供的字段进行排序；用法：order by column[ASC(默认) | DESC]。

limit： 从结果集上再进行刷选；用法：limit offset ,rows。

如下语句建表：给city建索引

CREATE table a(
    id int(11) not null,
		name varchar(16) not null,
		age VARCHAR(16) not null,
		city VARCHAR(16) not null,
		PRIMARY key(id),
		key city (city)
)ENGINE = INNODB

当我们查询city = “南京”时的sql语句。

SELECT name,age,city from a WHERE city="南京" ORDER BY age limit 10

我们用explain命令看下语句，发现它使用的是city索引，而且Extra是Using filesort，表示是需要排序的。

MySQL会给每个线程分配一块内存用于排序，叫做sort_buffer。

16.1 全字段排序

sql语句的执行流程：

1. 初始化sort_buffer，确定name，age，city三个字段
2. 去city索引中找到第一个满足city="南京"的数据，得到id
3. 带着上面的id去id主键索引树中寻找对应的数据行，取出name，age，city字段值存入sort_buffer
4. 从city索引去下一个满足条件的id
5. 重复3,4步，直到从city索引中取不到满足条件的id
6. 对sort_buffer中的数据根据age进行排序

流程示意图如下：

sort_buffer_size：

上面流程的第五步，根据age将数据进行排序，可能是在内存中进行，也可能借助了临时文件。这取决于参数sort_buffer_size的大小与所需要的内存大小。如果排序数据量过大，无法在内存中完成排序，就必需要借助于磁盘临时文件辅助排序。排序磁盘临时文件的算法是归并算法，归并算法的思想是分而治之。

16.2 row id 排序

MySQL中有个参数max_length_for_sort_data表示用于排序的当行长度，如果我们排序的字段有a,b,c…h这么多，内存中存下数据有限，必定会借用更多的磁盘临时表，性能不佳。

当出现单行的数据长度超过参数设定值，排序的列只会是 主键 id和 需要排序列。

sql语句的执行流程：

1. 初始化sort_buffer，确定主键id，age列
2. 从city索引中查找到第一个满足city = “南京”数据并取到id
3. 带着主键id去id索引树找到对应的数据行，取出age，id两列放入sort_buffer中
4. 去city索引树继续找到下一个满足条件的数据，取出id
5. 重复3,4步，直到city索引树找不到满足条件的数据。
6. 对sort_buffer中的数据根据age进行排序
7. 遍历排序结果，根据id去id索引树中取到name，age，city返回给客户端

流程示意图：

16.3讨论及优化

1. 使用临时表排序会造成较大的性能消耗，能用内存尽量用内存，减少磁盘访问。
2. 利用索引覆盖规则，我们创建name,age,city可以减少回表的步骤，直接得到结果。
3. 利用磁盘文件的目的是为了排序，那我们通过建立联合索引，在当初数据放置的时候就已经排序好，我们取出来可以直接用。

像这样做：

alter TABLE a add index city_age(city,age)

练习问题：

1. order by 与 limit 的用法
2. sort_buffer是啥？什么参数控制？
3. 全字段排序执行流程？
4. row id排序执行流程？
5. 优化方案？
6. MySQL的OPTIMIZER_TRACE 特性？