[db] random

[Alice52]

rand(): 随机下的性能

• sort_buffer_size

-- 1w rows
explain select word from t_words order by rand() limit 3; {Using temporary; Using filesort}

1. order by rand() 执行过程: 扫描行数很多有性能有问题的

   • 创建一个临时表{内存/磁盘}: memory 引擎的{tmp_table_size 值大于会被放入的值}, R(), W{字段长度}
   • 从 words 表中, 按主键顺序取出所有的 word 值, 插入临时表, 扫描 1w 行
   • 现在临时表有 10000 行数据, 按照字段 R 排序
   • 初始化 sort_buffer{double 类型, 整型}: sort_buffer_size 会影响排序算法{归并排序算法[临时文件], 优先队列排序算法}
   • 临时表中一行一行地取出 R 值和位置信息存入 sort_buffer[对内存临时表做全表扫描], 扫描 +1w 行
   • 在 sort_buffer 中根据 R 的值进行排序
   • 排序完成后, 取出前三个结果的位置信息, 依次到内存临时表中取出 word 值, 返回给客户端, 扫描 +3 行
   • 这个过程中, 总扫描行数变成了 20003

   

2. order by rand()使用了内存临时表, 内存临时表排序的时候使用了 rowid 排序方法{优先队列排序算法}

   • 内存临时表: 这个适合参数 tmp_table_size 有关
   • rowid: 这个是因为内存临时表, 不会回表问题
   • 优先队列排序算法: sort_buffer_size 大于 limit{真实需要的字段大小{这里就是 rowid+R} * limit}

3. 随机排序方法

   • M=max(id), N=min(id), X = (M-N)*rand() + N + 取不小于 X 的第一个 ID 的行

     1. 这个会使用索引, 不会大量扫描数据
     2. 但是并不是真正的随机
     3. code

     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;

   • C=count(*) + Y, floor(C * rand()) , limit Y,1

     1. 一共会扫描 C+Y+1 行: count(*) 扫描 C 行 + limit Y,1 会扫描 Y+1 行
     2. 但是由于是 id primary, 代价比 order by random() 小很多
     3. 解决了上一个方案中的不是真正随机问题: 与 Id 无关, 以个数为基准的随机

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

   • 再优化: C + Y2 + 1

     -- select * from t limit count(*)*rand() 1;
     mysql>
     select count(*) into @C from t;
     set @Y1 = floor(@C * rand());
     set @Y2 = floor(@C * rand());
     -- 比大小设置 @Y1 小于 @Y2
     id1 = select * from t limit @Y1, 1;
     select * from t where id > id1 limit @Y2 - @Y1, 1;