MySQL随机函数RAND

CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=0;
  while i<10000 do
    insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
    set i=i+1;
  end while;
end;;
delimiter ;

call idata();

如何随机取3个单词

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

-- 查看上面语句的执行情况
explain select word from words order by rand() limit 3;

Extra中Using temporary表示使用临时表，Using filesort表示需要执行排序操作。

上述默认使用的临时表是内存表，对于内存表来说，回表过程只是简单地根据数据行的位置直接访问内存得到数据，并不会导致额外的磁盘访问，因此MySQL会在排序时会优先使用rowid排序。

上述SQL语句的执行过程如下：

1. 创建一个临时表（该表使用的是memory引擎），表里有两个字段，第一个字段是double类型（记为字段R），第二个字段是varchar(64)类型（记为字段W）,临时表没有索引
2. 从word表中，按照主键顺序取出所有的word值，对于每一个word值，调用rand函数生成一个大于0小于1的随机小数，把该随机小数和word值存入临时表的R和W字段中，至此扫描行数是10000
3. 临时表目前有10000行数据，下面需要对这个临时表按照字段R进行排序
4. 初始化sort_buffer，sort_buffer中有两个字段，一个是double类型，另一个是整型
5. 从内存临时表中逐行取出R值和位置信息，分别存入sort_buffer中的两个字段，此时扫描行数又增加了10000行，变成了20000(MySQL8.0.12以后这里还是10000行，应该是对内存表做了优化，有知道的朋友可以留言告诉我)
6. 在sort_buffer中根据R值进行排序
7. 排序完成以后，取出前三个结果的位置信息，到内存临时表中取出word值，返回给客户端。总扫描行数变为20003（MySQL8.0以后这里是10003行）。

MySQL8.0下慢查询日志如下图，扫描行数为100003行：

临时表只能是内存表么？

答案是NO。那什么时候临时表会使用内存，什么时候又会使用磁盘呢？该选择主要依赖于tmp_table_size参数的控制，默认是16M，如果临时表的大小超过了tmp_table_size，那么内存临时表就会转换为磁盘临时表。

磁盘临时表使用的默认引擎是InnoDB，由参数default_tmp_storage_engine控制。

set tmp_table_size=1024;
set sort_buffer_size=32768;
set max_length_for_sort_data=16;
/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 
/* 执行语句 */
select word from words order by rand() limit 3;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

在我们上图的输出中，我们可以看出排序算法使用了优先队列排序算法，然后是全字段排序（也就是说不用回表）。

peak_memory_used代表排序时使用到的内存，按道理应该等于sort_buffer_size指定的值，之所以不等的原因是作者本人的MySQL是8.0.12版本。

MySQL8.0.12之前，MySQL优化器会为排序直接分配sort_buffer_size指定大小的内存，但从MySQL8.0.12开始，为排序分配内存是以增量的方式进行。