MySQL深度解析---临时表排序规则

最新推荐文章于 2024-06-18 20:58:40 发布

纯洁2016

最新推荐文章于 2024-06-18 20:58:40 发布

阅读量1.2k

点赞数

分类专栏： # MySQL

本文链接：https://blog.csdn.net/qq_42230770/article/details/111311181

版权

MySQL 专栏收录该内容

13 篇文章 2 订阅

订阅专栏

一、内存临时表排序

内存临时表触发条件：

rand() 随机函数

mysql> explain select word from words order by rand() limit 3;

在这里插入图片描述
Extra字段显示Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。
因此这个Extra的意思就是，需要临时表，并且需要在临时表上排序。

1.内存临时表排序方式选择

对于内存临时表来说，表中的数据都在内存中存储，相当于回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘。
优化器没有了这一层顾虑，那么它会优先考虑的，就是用于排序的行越少越好了，所以，MySQL这时就会选择rowid排序。

2.内存临时表排序执行流程

创建一个临时表。这个临时表使用的是memory引擎，表里有两个字段，第一个字段是double类型，为了后面描述方便，记为字段R，第二个字段是varchar(64)类型，记为字段W。并且，这个表没有建索引。
从words表中，按主键顺序取出所有的word值。对于每一个word值，调用rand()函数生成一个大于0小于1的随机小数，并把这个随机小数和word分别存入临时表的R和W字段中，到此，扫描行数是10000。
现在临时表有10000行数据了，接下来要在这个没有索引的内存临时表上，按照字段R排序。
初始化 sort_buffer。sort_buffer中有两个字段，一个是double类型，另一个是整型。
从内存临时表中一行一行地取出R值和位置信息（我后面会和你解释这里为什么是“位置信息”），分别存入sort_buffer中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加10000，变成了20000。
在sort_buffer中根据R的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。
排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出word值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了20003。

在这里插入图片描述
order by rand()使用了内存临时表，内存临时表排序的时候使用了rowid排序方法

二、磁盘临时表排序

磁盘临时表触发条件：
参数：tmp_table_size
tmp_table_size这个配置限制了内存临时表的大小，默认值是16M。如果临时表大小超过了tmp_table_size，那么内存临时表就会转成磁盘临时表。
磁盘临时表存储引擎：
参数：internal_tmp_disk_storage_engine
默认是InnoDB

当使用磁盘临时表的时候，对应的就是一个没有显式索引的InnoDB表的排序过程。

1.磁盘临时表排序方式选择

磁盘临时表和实际的数据表都是在磁盘存储，所以排序方式也相同：
按照max_length_for_sort_data定义的长度 和 要排序字段的总长度 来决定是使用全字段排序还是Rowid排序。

2.优先队列算法

如果要取的有序元素个数比较少（比如：limit 3 取三个有序元素），MySQL会采取一种优先队列的算法来排序

优先队列算法的触发条件：
所需的有序行数 * 每一行的大小 < sort_buffer_size 的大小
执行计划的OPTIMIZER_TRACE结果中，filesort_priority_queue_optimization这个部分的chosen=true，就表示使用了优先队列排序算法，这个过程不需要临时文件，因此对应的number_of_tmp_files是0。

优先队列算法，就可以精确地只得到三个最小值，执行流程如下：

对于这10000个准备排序的(R,rowid)，先取前三行，构造成一个堆
取下一个行(R’,rowid’)，跟当前堆里面最大的R比较，如果R’小于R，把这个(R,rowid)从堆中去掉，换成(R’,rowid’)；
重复第2步，直到第10000个(R’,rowid’)完成比较。

优先队列排序过程的示意图
优先队列排序算法示例
模拟6个(R,rowid)行，通过优先队列排序找到最小的三个R值的行的过程。整个排序过程中，为了最快地拿到当前堆的最大值，总是保持最大值在堆顶，因此这是一个最大堆。
这个流程结束后，我们构造的堆里面，就是这个10000行里面R值最小的三行。然后，依次把它们的rowid取出来，去临时表里面拿到word字段，这个过程就跟传统的rowid排序的过程一样了。

三、随机排序方法

鉴于rand() 函数会导致临时表排序，在海量数据下SQL的执行效率极低，所以采用函数的方法来生成随机的结果

mysql> select count(*) into @C from t;
set @Y = floor(@C * rand());
set @sql = concat("select * from t limit ", @Y, ",1");
prepare stmt from @sql;
execute stmt;
DEALLOCATE prepare stmt;

随机取3个word值：

取得整个表的行数，记为C；
根据相同的随机方法得到Y1、Y2、Y3；
再执行三个limit Y, 1语句得到三行数据。

mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1，1； //在应用代码里面取Y1、Y2、Y3值，拼出SQL后执行
select * from t limit @Y2，1；
select * from t limit @Y3，1；