1 order by rand()执行过程
先生成一张表,有10000条记录。
select word from words order by rand() limit 3;
用 explain 命令来看看这个语句的执行情况。
Extra 字段显示 Using temporary,表示的是需要使用临时表;Using filesort,表示的是需要执行排序操作。
因此这个 Extra 的意思就是,该命令,需要使用临时表,并且需要在临时表上排序。
这条语句的详细执行流程:
- 创建一个临时表。这个临时表使用的是 memory 引擎,表里有两个字段,第一个字段是 double 类型,为了后面描述方便,记为字段 R,表示每个字段的分数值; 第二个字段是 varchar(64) 类型,记为字段 W,表示字段word。并且,这个表没有建索引。
- 从 words 表中,按主键顺序取出所有的 word 值。对于每一个 word 值,调用 rand() 函数生成一个大于 0 小于 1 的随机小数,并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中,到此,扫描行数是 10000。
- 现在临时表有 10000 行数据了,接下来你要在这个没有索引的内存临时表上,按照字段 R 排序。
- 初始化 sort_buffer。sort_buffer 中有两个字段,一个是 double 类型,另一个是整型。
- 从内存临时表中一行一行地取出 R 值和位置信息,分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描,此时扫描行数增加 10000,变成了 20000。
- 在 sort_buffer 中根据 R 的值进行排序。注意,这个过程没有涉及到表操作,所以不会增加扫描行数。
- 排序完成后,取出前三个结果的位置信息,依次到内存临时表中取出 word 值,返回给客户端。这个过程中,访问了表的三行数据,总扫描行数变成了 20003。
2 其他
innodb用rowid来唯一标识数据行: 对于有主键的innodb,rowid就是主键。 对于没有主键的innodb,rowid由系统生成。 对于Memory引擎,rowid就是数组下标。-----关于记录在表中的位置
order by rand(),需要 Using temporary 和 Using filesort,查询的执行代价往往是比较大的。所以,在设计的时候你要尽量避开 order by rand这种写法。 -----关于 order by rand 写法的评价
当临时表需要占据的空间较大时,就不会在内存里存放了,而是转到磁盘里。
MySQL 5.6 版本引入堆排序,而不是归并排序,好处:不需要临时文件了
即:优先队列排序算法。(使用最小堆),不使用之前的归并排序了。
执行步骤:
而优先队列算法,就可以精确地只得到三个最小值,执行流程如下:对于这 10000 个准备排序的 (R,rowid),先取前三行,构造成一个堆;(对数据结构印象模糊的同学,可以先设想成这是一个由三个元素组成的数组)取下一个行 (R’,rowid’),跟当前堆里面最大的 R 比较,如果 R’小于 R,把这个 (R,rowid) 从堆中去掉,换成 (R’,rowid’);重复第 2 步,直到第 10000 个 (R’,rowid’) 完成比较。
3 如何高效的实现“随机排序”
实现的目标:随机选择一条记录
3.1 法1
取得这个表的主键 id 的最大值 M 和最小值 N;
用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;
取不小于 X 的第一个 ID 的行。
执行语句的序列
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;
方法评价:
这效率很高,因为取 max(id) 和 min(id) 都是不需要扫描索引的,而第三步的 select 也可以用索引快速定位,可以认为就只扫描了 3 行。
不足:
这个算法本身并不严格满足题目的随机要求,因为 ID 中间可能有空洞,因此选择不同行的概率不一样,不是真正的随机。比如你有 4 个 id,分别是 1、2、4、5,如果按照上面的方法,那么取到 id=4 的这一行的概率是取得其他行概率的两倍。如果这四行的 id 分别是 1、2、40000、40001 呢?这个算法基本就能当 bug 来看待了。
解决方案:
目标:避免空洞。 新加一个字段,记录每一行的rowid。
3.2 法2
取得整个表的行数,并记为 C。
取得 Y = floor(C * rand())。 rand函数返回一个0,1之间的随机数。floor 函数在这里的作用,就是取整数部分。
再用 limit Y,1 取得一行。
代码表示:
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;
这个随机算法 2,解决了算法 1 里面明显的概率不均匀问题。
延伸:随机取3个值呢?
就把上面的步骤,重复执行3次即可。 在业务层面拼装从mysql查询到的结果。
3.3 法3
为了减少法2中扫描的行数,可以进一步优化
id1 = select * from t limit @Y1,1;
id2= select * from t where id > id1 limit @Y2-@Y1,1;
select * from t where id > id2 limit @Y3 - @Y2,1;
总扫描行数: C + Y3