MySql 你知道如何正确的取随机数据吗 ?

志在巅峰的攀登者,不会陶醉在沿途的某个脚印之中,在码农的世界里,优美的应用体验,来源于程序员对细节的处理以及自我要求的境界,年轻人也是忙忙碌碌的码农中一员,每天、每周,都会留下一些脚印,就是这些创作的内容,有一种执着,就是不知为什么,如果你迷茫,不妨来瞅瞅码农的轨迹。


1 前言

如下我这里有一张抽题记录表
在这里插入图片描述
我需要随机从中选4条数据,于是我会这样写(随机排序,取前4个)

在这里插入图片描述
执行时间 6.73秒,这个时间是绝对无法容忍的,你知道这个过程发生了什么吗???

2 MySql 的 rand 查询过程 内存临时表

select * from question_extracting order by rand() limit 4

上述这一句话先随机排序,再取前4条,它的执行过程如下:

  • 第一步 创建一个临时表,有两个字段,一个是double 类型使用A表示,另一个是 varchar(64) 类型 使用B表示,记为 字段 W,需要注意的是这个表没有建索引。
  • 第二步就是 从 上述 question_extracting 表中,按主键顺序取出所有的行(因为这里需要的是每行的所有数据),对于每一行数据,调用 rand() 函数生成一个大于 0 小于 1 的随机小数,并把这个随机小数和这一行数据 分别存入临时表的 A 和 B 字段中,需要扫描全表,如这里的 4974098 行。
  1. 第三步就是在临时表中按照字段 A 排序,初始化 sort_buffer,sort_buffer 中会放两个字段,一个是 double 类型,用来放临时表中的 A 字段,另一个是整型,用来放临时表中对应的数据的行号。
  2. 第四步就是在 sort_buffer 中根据 A 的值进行排序,排序完成后,取出前 4 个结果的位置信息,然后回到依次到内存临时表中取出 对就的行信息 值,返回给 客户端。

在这里插入图片描述

在上述过程中,在第二步扫描了 question_extracting 全表 4974098 行 ,在第三步中也扫描了临时表 4974098 行 ,然后在最后一步取数据又扫描了4行数据,所以这一次查询总共扫描了 4974098 +4974098 + 4 次。

在上述这个过程中,order by rand() 使用了内存临时表,内存临时表排序的时候 使用了 rowid 排序方法,这个临时表,没有主键ID,由临时生成的长度为 6 字节的 rowid 来作为主键。

3 MySql 的 rand 查询过程 磁盘临时表

在 MySql 中,参数 tmp_table_size 限制了内存临时表的大小,默认值是 16M,如果临时表大小超过了 tmp_table_size,那么内存临时表就会转成磁盘临时表。

使用磁盘临时表的时候,就是对一个没有显式索引的 InnoDB 表进行排序。

在这个过程中使用的是 MySQL 5.6 版本引入的优先队列排序算法,如我们上述的随机取值,按照 优先队列排序算法 只需要取出 临时表中最小的 4 个 A 值对应的数据就可以,这个过程可简单描述如下:

  • 第一步 对于临时表中这 4974098 个准备排序的 (A,rowid),先取前四行,构造成一个堆(可以理解为一个组)
  • 第二步 取下一个行 (R’,rowid’),跟当前堆里面最大的 R 比较,如果 R’小于 R,把这个 (R,rowid) 从堆中去掉,换成 (R’,rowid’)
  • 重复第 2 步,直到第 4974098 个 (R’,rowid’) 完成比较
  • 最后一步就是 拿到这最小的4个值后,回到临时表中取出对应的数据。

3 MySql 随机排序的正确姿势

随机取一条数据,按照最开始的写法如下:
在这里插入图片描述

随机算法的正确姿势

在这里插入图片描述
你并没有看错,这就是正确的写法


select max(id),min(id) into @A,@B from question_extracting ;

set @C= floor((@A-@B+1)*rand() + @B);

select * from question_extracting where id >= @C limit 1;

可描述如下:

  • 第一步 取得这个表的主键 id 的最大值 M 和最小值 N,这个过程不需要扫描表
  • 第二步 用随机函数生成一个最大值到最小值之间的数 C = (A-B)*rand() + B;
  • 第三步 取不小于 C 的第一个 ID 的行

完毕

不局限于思维,不局限语言限制,才是编程的最高境界。

以小编的性格,肯定是要录制一套视频的,随后会上传

有兴趣 你可以关注一下 西瓜视频 — 早起的年轻人

在这里插入图片描述

早起的年轻人 CSDN认证博客专家 移动开发 项目管理 Java
只要用心去做,每一件事情还是有可能成功的,当然成功是没有界限的,只不过是达到自己心里的那个目标,公众号:我的大前端生涯,一个爱喝茶的程序员,通常会搞搞SpringBoot 、Herbinate、Mybatiys、Android、iOS、Flutter、Vue、小程序等.
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 代码科技 设计师:Amelia_0503 返回首页