《MySQL学习》 表中随机取记录的方式

一.初始化测试表

创建表 words

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

插入测试数据

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;;
call idata();

二.rand()函数随机取出三行数据

常见的写法为，SQL虽然简单，但执行过程很复杂 rand() 取 0~1之间的值

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

分析结果

explain select * from words w order by rand() limit 3

1. Using temporary; 使用临时表
2. Using filesort 使用内存排序

用到临时表的原因是 rand() 函数取得的随机值需要一个 表去存储，而 filesort 是用来排序的，不能直接放到排序内存中。 tmp_table_size 值规定了内存临时表的大小，超过该值将使用磁盘临时表。磁盘临时表使用的引擎默认是 InnoDB引擎，内存临时表使用的是memory 引擎 。 是由参数 internal_tmp_disk_storage_engine 控制的。

我们已知InnoDB排序有单路排序和双路排序，当排序的字段过长时，将使用双路排序导致要多回一次表，增加IO成本。但此时由于使用的是内存临时表，InnoDB将择优选择使用 双路排序，减少排序的字段。

这条语句的执行流程是这样的：

1. 创建一个临时表。这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，为了后面描述方便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。

2. 从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中，到此，扫描行数是 10000。

3. 现在临时表有 10000 行数据了，接下来你要在这个没有索引的内存临时表上，按照字段 R 排序。

4. 初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。

5. 从内存临时表中一行一行地取出 R 值和位置信息，分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000。

6. 在 sort_buffer 中根据 R 的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。

7. 排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。

​ 首先从磁盘扫描10000条数据读入内存临时表中，然后从内存临时表中将10000条数据一条条读入排序内存中，期间使用优先队列排序获取最大的三个值，最后再回到临时表中根据位置信息读取三条记录的值返回。此处没有使用堆排序是由于只需要获取前三的值，没有必要将所有的数据都排序序。对于这 10000 个准备排序的 rowid（或主键索引），先取前三行，构造成一个堆；取下一个行 (R’,rowid’)，跟当前堆里面最大的 R 比较，如果 R’小于 R，把这个 (R,rowid) 从堆中去掉，换成 (R’,rowid’)；重复第 2 步，直到第 10000 个 (R’,rowid’) 完成比较。

​ 如果此处是limit 1000 ,那么维护一个排好序且大小是1000的堆还不如使用归并排序。

​ 但是使用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；

用此种方式取随机数，扫描行数为 c（表总记录数） + Y1 +1 + Y2 +1 +Y3 +1 ，虽然扫描行数很多，但能利用索引，索引天然有序，效率非常高