《MYSQL 实战45讲》深入浅出ORDER BY底层

慢查询日志这个路径下，但是打不开

内存临时表

现在有一个业务功能是从一个单词表里面随机选出3个单词出来

这个表有10000条数据。

select word from words order by rank() limit 3

使用执行计划查看结果：

select_type是SIMPLE表示改查询不包含子查询或者UNION

type:all,表示性能是最差的，没有使用索引，全表扫描

rows:扫描的行数9980

filtered:

Extra:额外信息，这里的Extra是Using temporary表示使用了临时表；using filesort表示需要执行排序操作

对于内存临时表，排序算法会选择全字段排序还是rowid排序？

如果是innodb的磁盘临时表，排序算法会考虑到使用rowid会多一次回表的过程，所以在内存足够大时会尽量选择全字段排序，但是内存临时表不同，rowid排序需要回表时，不需要去磁盘，而是直接访问内存就能拿到rowid对应的完整整行的值，所以对于内存临时表，会选择rowid排序，因为少占用一点内存。

执行流程：

1.首先创建内存临时表，有2个字段（double型）R和（varchar型）W，无索引

2.将每一行记录的word取出来并使用rank函数生成随机小数（大于0并且小于1）分别放在内存临时表的R和W字段。此时已经扫描了10000行数据存在内存临时表，然后需要对这个临时表对R字段进行排序

3.初始化sort_buffer要放2个字段，一个double类型，一个整型

4.一行一行取出内存临时表的R和“位置信息”放在sort_buffer中，此时又扫描了10000行，总共扫描了20000行

5.在sort_buffer中对R的值进行排序，此处不会增加扫描的行数

6.排序完成后，将排序结果的前3条数据的“位置信息”取出来并在内存临时表中取出对应的word放入结果集，返回客户端 。此时又扫描了3行，总扫描行数是20003

rowid排序的rowid是什么意思？

就是MYSQL用来唯一标识数据行的信息。

对于innodb的表来说：

表如果有主键id,那么rowid就是主键id

表如果没有主键id,那么rowid就是系统生成的。

对于这个内存临时表来说：

整个表就相对于一个数组，数组中每个元素就是一个数据行，所以这个rowid就是数组的下标。

前面提到的位置信息就是这个数组下标

磁盘临时表

1.在排序时有一个参数是sort_buffer_size，控制的是用来排序的内存大小，如果要排序的数据量总大小小于sort_buffer_size，就直接在内存中快排，否则就要使用磁盘临时文件来辅助进行归并排序（先让每个文件中的数据是有序的，再合并所有文件）。

2.而在使用order by rand()时使用的表，也有内存临时表和磁盘临时表之分，通过tmp_table_size参数来控制，这个参数配置的是内存临时表的大小，超过这个大小就只能用磁盘临时表了，而这个磁盘临时表默认是innodb引擎的，就和我们平时打交道的表是一样的，只不过这个磁盘临时表没有索引。

我们通过设置参数使MYSQL选择磁盘临时表：

但是发现临时文件数是0，说明没有使用到临时文件，原因是MySQL 5.6引入了优先队列，sort_buffer大小允许的情况下会使用优先队列来进行“排序”，因为这个语句要的是前3个，如果使用归并或者快排，都会让10000条数据全部有序，显然没有必要并且浪费很多资源，所以使用优先队列去维护一个最大堆，每遍历一个数据行就将rand值与最大堆的最大值比较，如果小于这个最大值就把rand值替换进去，最大堆的堆顶永远是堆中的最大值。