17随机

Posted on
17随机

order by rand() 使用内存临时表，内存临时表排序时使用rowid排序方法

内存临时表引擎 Memory
磁盘临时表引擎 InnoDB
临时表大小限制 tmp_table_size 默认16M，太大会转换成磁盘临时表
内存排序最大单行长度 max_length_for_sort_data 超过后再磁盘归并排序

随机排序方法

如果只随机选择1个值，可以怎么做呢？
暂时称作随机算法1：

  1. 取得这个表的主键 id 的最大值 M 和最小值 N;
  2. 用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;
  3. 取不小于 X 的第一个 ID 的行。
1
2
3
4
5
6
7
8
9
10
11
12
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中间可能有空洞，因此选择不同行的概率不一样，不是真正的随机   

为了得到严格随机的结果，你可以用下面这个流程:
1. 取得整个表的行数，并记为 C。
2. 取得 Y = floor(C * rand())。 floor 函数在这里的作用，就是取整数部分。
3. 再用 limit Y,1 取得一行

随机算法2

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;


MySQL 处理 limit Y,1 的做法就是按顺序一个一个地读出来，丢掉前 Y 个，然后把下一个
记录作为返回结果，因此这一步需要扫描 Y+1 行。再加上，第一步扫描的 C 行，总共需
要扫描 C+Y+1 行，执行代价比随机算法 1 的代价要高。
当然，随机算法 2 跟直接 order by rand() 比起来，执行代价还是小很多的。

# 小结
直接使用 order by rand()，这个语句需要 Using temporary 和 Using filesort，
查询的执行代价往往是比较大的。所以，在设计的时候你要量避开这种写法。