author：陈镇坤27
创建时间：2021年11月21日18:53:26
转载请注明出处
文章目录- MySQL对临时表排序——随机展示数据示例
- 问：临时表分哪几种？
- 问：为什么会使用临时表？
- 内存临时表
- 问：举一个例子，并说明排序过程中临时表的作用。
- 问：当使用到临时表的rowid排序时，为什么sort_buffer中初始化的字段之一id被替换为位置信息了？
- 问：为什么隐式自增索引的rowid是6字节？
- 问：sort_buffer的排序涉及到什么排序算法？
- 磁盘临时表
- 问：复现一个使用临时表的排序语句场景。
- 问：有一个场景，明明需要进行排序的数据总量超过sort_buffer_size，但是查看优化日志发现，该排序并没有借助外部磁盘文件，可能的原因是什么？解释一下。
- 问：什么是堆排序？
- 问：全字段排序和rowid排序对应的最佳场景是什么？
- 随机排序
- 问：要实现随机查询的功能，使用order by rand()可以吗？不可以的话使用什么方式好一些？
- 问：随机查询的话，可以用下面的例子吗，不可以的话解释下原因。
- mysql> select max(id),min(id) into @M,@N from t ;
—————————————————————————————— 问：临时表分哪几种？
答：内存临时表和磁盘临时表，磁盘临时表又可以根据引擎区分为Innodb类型或MyINSAM类型。
问：为什么会使用临时表？答：（个人理解）当执行语句需要对获取得到的数据进行额外的整理，并且这种处理是在表层面上进行时。例如需要对每一行获取的值，进行随机赋值，得到的值需要进行存储。
需要注意的是，使用派生表的情景没有必要在extra中特意说明使用了临时表（EXPLAIN does not necessarily say Using temporary for derived or materialized temporary tables.）
具体的触发关键字或场景在官方文档中地址位置：https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html
PS：临时表会在本线程执行完结束后释放。
内存临时表 问：举一个例子，并说明排序过程中临时表的作用。答：假设表数据有10000行，一个id主键字段和word字符字段。
select word from t order by rand() limit 3
执行结果中，extra为：Using temporary、Using filesort
执行流程：
1、创建引擎为memory（无索引）的临时表，表字段有R（随机数）和W（word）；
2、扫描t表，每一行都单独进行rand()函数运算，将求值与字段word逐行存入临时表，直到符合筛选条件数据全部存完。
3、初始化sort_buffer，有字段R（随机值为double类型）和P（P为临时表的行数据对应位置信息（整型），对应下面的rowid排序中的rowid）；
4、将临时表中的行数据逐行加载进sort_buffer；
5、sort_buffer对R进行排序；（此时采取的是堆排序算法）
6、根据sort_buffer前三的数据的位置信息，逐一回临时表获取对应数据，并直接返回给客户端。
PS：查询慢日志结果，可以结合上面步骤分析为什么扫描次数是20003行。
# Query_time: 0.003494 Lock_time: 0.000101 Rows_sent: 3 Rows_examined: 20003 SET timestamp=1637290944; select word from words order by rand() limit 3;问：当使用到临时表的rowid排序时，为什么sort_buffer中初始化的字段之一id被替换为位置信息了？
答：所谓rowid，是每个引擎用来唯一标识数据行的信息。当临时表是内存表时，相当于数组的下标便是其唯一的标识。
问：为什么隐式自增索引的rowid是6字节？答：64位Linux操作系统下内存虚拟地址寻址空间并不是 2^64，而是 2^48 ，一个字节8bit，所以是6字节。
问：sort_buffer的排序涉及到什么排序算法？答：
如果只获取排序数据中的一小部分，使用堆排序，此时即使数据总大小超出sort_buffer，也不需要使用到外部文件（前提是sort_buffer能容纳所谓的“数据中的一小部分”（除非你恶搞，不然当选择堆排序时，都能满足））；
如果排序的数据总大小超出sort_buffer，外部文件会采用归并排序；
如果排序的数据总大小没超出sort_buffer，sort_buffer内会采取快速排序。
磁盘临时表 问：复现一个使用临时表的排序语句场景。答：
show VARIABLES like '%tmp_table_size%'; show VARIABLES like '%sort_buffer_size%'; show VARIABLES like '%max_length_for_sort_data%'; set tmp_table_size=1024; set sort_buffer_size=32768; set max_length_for_sort_data=16; SET optimizer_trace='enabled=on'; select word from words order by rand() limit 3; SELECt * FROM `information_schema`.`OPTIMIZER_TRACE`G问：有一个场景，明明需要进行排序的数据总量超过sort_buffer_size，但是查看优化日志发现，该排序并没有借助外部磁盘文件，可能的原因是什么？解释一下。
答：该排序语句仅需要返回少部分排序完毕的数据，此时在sort_buffer会使用堆排序算法。（查前三，则每次在sort_buffer中只会存在4行数据，其中一条会被替换）
答：（知道怎么做，但数学原理不了解）
首先构建完全二叉树（不需要排序），根据选择构建大堆顶或小堆顶，前者是儿子比父亲小，后者反之。
从下到上，每次都只对一个最小堆进行最大（最小）的置顶，然后对同层的另一个最小堆做同样的操作，总体上逐一向顶推进，最后得到整个树最大（最小）的堆。
首先将序列按照完全二叉树方式进行排列，从底部的最小堆往上，以堆为单位，先找非叶子节点，然后以此和自己的孩子做比较，与最大值做交替，如果交替后的小值成为下面的孩子的新的父亲，则要判断是否有打破大顶堆规则，有的话，继续调整。
问：全字段排序和rowid排序对应的最佳场景是什么？答：首先，根据是否有临时表做判断。
如果是内存临时表，证明回表查询过程不需要进行磁盘IO，此时进行rowid排序速度最快。
如果是Innodb表（包括用户创建的表和磁盘临时表），则根据配置的max_length_for_sort_data长度来进行选择，大于，则进行rowid排序，否则进行全字段排序。
随机排序 问：要实现随机查询的功能，使用order by rand()可以吗？不可以的话使用什么方式好一些？答：不可以。因为过程性能开销大。
建议通过行的总数进行随机查。
方法如下：
取总行数–总行数取随机值–limit查询该值
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;
该查询仅需要遍历主键索引树的叶节点链表即可。
扫描行数为C+Y+1。
即使随机rand得到的值要扫描的行数=表的行数，其性能也远比order rand()要好——主键索引树天然排序。
如果要查三个随机数，则执行三次即可
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；
而这又可继续优化：将得到的y1到y3中最大和最小的数取出，记为M和N
select * from t limit N, M-N+1;问：随机查询的话，可以用下面的例子吗，不可以的话解释下原因。 mysql> select max(id),min(id) into @M,@N from t ;
set @X= floor((@M-@N+1)*rand() + @N);
select * from t where id >= @X limit 1;
答：不可以。id与id之间可能存在空洞，则区间段随机命中概率不等。
mysql> select max(id),min(id) into @M,@N from t ;
set @X= floor((@M-@N+1)*rand() + @N);
select * from t where id >= @X limit 1;
答：不可以。id与id之间可能存在空洞，则区间段随机命中概率不等。
