MySQL -- 排序（order by）

1，全字段排序

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

在 city 字段上创建索引之后，我们用 explain 命令来看看这个语句的执行情况

“Using filesort”表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

语句执行流程

初始化 sort_buffer，确定放入 name、city、age 这三个字段；
从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
对 sort_buffer 中的数据按照字段 name 做快速排序；
按照排序结果取前 1000 行返回给客户端。

sort_buffer_size

MySQL 为排序开的内存（sort_buffer）大小。排序的数据量小于 sort_buffer_size，排序就在内存中完成（内存排序）。排序数据量内存放不下，则利用磁盘临时文件辅助排序（外部排序）；

rowid 排序

max_length_for_sort_data：MySQL控制排序的行数据的长度的一个参数。如果单行的长度超过这个值，要换一个算法。新的算法放入 sort_buffer 的字段，只有排序的列（即 name 字段）和主键 id

SET max_length_for_sort_data = 16;（缩小排序的行数据长度）

语句执行流程

初始化 sort_buffer，确定放入两个字段，即 name 和 id；
从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；
对 sort_buffer 中的数据按照字段 name 进行排序；
遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端（回表操作）。

rowid：创建的表没有主键，或把表主键删掉，InnoDB会生成一个长度为6 字节的 rowid 作主键，引擎用来唯一标识数据行的信息。

3，临时表排序

3.1 内存临时表

tmp_table_size 配置内存临时表大小，默认16M。
随机排序取出10000个单词库的前三个单词：select word from words order by rand() limit 3;
order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。

执行流程

创建一个临时表。使用memory 引擎，第一个字段是 double 类型，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。且，这个表没建索引。
从 words 表，按主键顺序取出所有值。每一个值，调rand() 函数生成0 到1之间随机小数，把随机小数和 word 存入临时表的 R 和 W 字段中，扫描行数10000
现在临时表有 10000 行数据，按照字段 R 在这个没有索引的内存临时表上排序。
初始化 sort_buffer两个字段，一个double 类型，一个整型（存位置信息）
从内存临时表取出 R 值和位置信息存入 sort_buffer的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成20000。
sort_buffer 中根据 R进行排序。这个过程没涉及表操作，不会增加扫描行数。
排序完成，取前三个结果的位置信息，再到内存临时表中取出 word 值，返回，访问表的三行数据，总扫描行数变成20003。

3.2 磁盘临时表

临时表大小超过 tmp_table_size，内存临时表就转成磁盘临时表；
参数 internal_tmp_disk_storage_engine 控制磁盘临时表引擎，默认InnoDB。
使用磁盘临时表的时候，就是对一个没有显式索引的 InnoDB 表排序。

参考链接：
文章节选自 极客时间《MySQL45讲》 如有不当，联系侵删