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

╰半夏微凉° 2023-10-07 15:53 16阅读 0赞

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

*  [优美的音乐节奏带你浏览这个效果的编码过程][Link 1]
 *  [坚持每一天，是每个有理想青年的追求][Link 2]
 *  [追寻年轻人的脚步，也许你的答案就在这里][Link 3]
 *  [如果你迷茫 不妨来瞅瞅这里][Link 4]

--------------------

#### 1 前言 ####

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 1]  
执行时间 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 个结果的位置信息，然后回到依次到内存临时表中取出 对就的行信息 值，返回给 客户端。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 2]

在上述过程中，在第二步扫描了 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 随机排序的正确姿势 ####

随机取一条数据，按照最开始的写法如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 3]

随机算法的正确姿势

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 4]  
你并没有看错，这就是正确的写法

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 的行

--------------------

完毕

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

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

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

![在这里插入图片描述][20201031094959816.gif_pic_center]

[Link 1]: https://www.bilibili.com/video/BV1QX4y1G7Sh/
[Link 2]: https://www.zhihu.com/zvideo/1348960604003180544
[Link 3]: https://www.ixigua.com/6933784858019758595
[Link 4]: https://biglead.blog.csdn.net/article/details/93532582
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/202103161808252.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210316180955994.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210316184742630.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/2021031619054799.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20210316190815594.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[Link 5]: https://www.toutiao.com/c/user/token/MS4wLjABAAAAYMrKikomuQJ4d-cPaeBqtAK2cQY697Pv9xIyyDhtwIM/
[20201031094959816.gif_pic_center]: https://img-blog.csdnimg.cn/20201031094959816.gif#pic_center