专栏首页洁癖是一只狗Mysql如何随机获取表中的数呢rand()

Mysql如何随机获取表中的数呢rand()

随机获取数据的业务场景,想必大家都有遇到过,今天我们分析一下如何正确的显示随机消息.

mysql> CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
  declare i int;
set i=0;
while i<10000 do
    insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
set i=i+1;
  end while;
end;;
delimiter ;

call idata();

上面是我们插入了10000行记录,现在我们要随机选择三个单词,又有什么办法实现呢.

内存临时表

首先,我们第一时间会想到order by rand()来实现

select  word from words order by rand() limit 3

我们在看看这条语句是如何执行的使用explain

可以看到extra的值有Using temporary,表示使用临时表,Using filesort,表示需要排序,即需要临时表,在临时表上进行排序,

我们可以想想上一次,我们说过的全字段排序,和rowid排序,当时我们总结的是,对于innodb存储引擎,执行全字段排序减少磁盘访问,因此会被优先选择.

但是对于内存表,回表过程只是简单的根据数据行的位置,直接访问内存得到数据,根本不会导致多访问磁盘,因此优化器如果没有这个顾虑,那么他优先考虑的是排序的行越少越好了,所以,Mysql这个时候就会选择rowid.

我们在来看看上面随机获取字段的sql语句是如何执行的

  1. 创建一个临时表,临时表使用的是memory引擎,表里面有两个字段,一个字段double类型,我们叫R,另一个字段varchar(64),记为W,且没有建立索引
  2. 从words表中,按照主键顺序取出word值,使用rand()让每一个word生成一个大于0小于1的小数,并把这个小数和word放入到临时表的R,W,到此扫描行数是10000.
  3. 现在临时表有10000行数据了,接下来你要在这个没有索引的内存临时表上,按照R字段排序
  4. 初始化sort_buffer中两个字段,一个是double,一个整形
  5. 从内存临时表中一行一行的获取R和位置信息,把字段放入到sort_buffer的两个字段中,此时要全表扫描临时表,扫描的行数为10000行,此时总共扫描的行数变成了2000行
  6. sort_buffer根据R字段进行排序,这里没有涉及到表的扫描
  7. 在根据sort_buffer排序的结果到临时表获取前三个word字段,返回给客户端,此时扫描了3行,一共有2003行

我们在通过慢日志验证我们的结论

# Query_time: 0.900376  Lock_time: 0.000347 Rows_sent: 3 Rows_examined: 20003
SET timestamp=1541402277;
select word from words order by rand() limit 3;

下面就是我们整个流程的示意图

上图我们发现sort_buffer中的位置信息,是个什么概念呢,而Mysql是如何定位一行数据的呢,

首先我们知道mysql中有以下规则

  • 对于有主键的innodb表来说,rowid就是我们的主键
  • 对于没有主键的innodb表说,rowid由系统自动生成
  • 而memory引擎不是索引组织表,我们可以认为次引擎中有一个数组,而rowid就是数组的下标

而上面说的rowid就是我们引擎中唯一标识行的标志,最后,我们总结到order by rand(),是使用临时表,按照rowid进行排序在内存表中进行排序

磁盘临时表

其实并不是所有的临时表都是内存表,tmp_table_size配置限制了内存临时表,默认大小是16M,当临时表的大于这个参数的时候,就会使用磁盘临时表.而磁盘临时表是由internal_tmp_disk_storage_engine控制的,

为了复现这个过程,我把tmp_table_size设置成1024,把sort_buffer_size设置成 32768, 把 max_length_for_sort_data 设置成16

set tmp_table_size=1024;
set sort_buffer_size=32768;
set max_length_for_sort_data=16;
/* 打开 optimizer_trace,只对本线程有效 */
SET optimizer_trace='enabled=on';

/* 执行语句 */
select word from words order by rand() limit 3;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

因为我们设置max_length_for_sort_data=16,由于word字段没有超过,因此使用rowid,符合我们的预期,但是我们发现sort_buffer_size=32768,而R长度是8字节,而rowid是6字节,有10000行数据,已经超过了sort_buffer_size,但是number_of_tmp_files=0,说明没有使用临时文件进行排序,这个不是我们所预期的,

接下来,我们分析一下,为什么没有使用临时文件进行排序,那是因为mysql5.6使用了另外一种算法优先级排序算法,

其实,虽然我们只需要前三个word,但是如果我们使用归并算法,发现我们已经把1000行的数据都已经进行排序了,而这种算法非常浪费计算量.

而优先级算法,可以精准的获取最小的三个word

  1. 从临时表中获取前三行,组成一个最大堆
  2. 然后拿下一行数据,和最大堆的R比较,大于R,则丢弃,小于R,则替换
  3. 重复2的步骤,直到把10000行数据循环完成

如下示意图

上面描述了优先级排序的过程,最终获取到一个最大堆(word,rowid).

回想我们上面有一个根据OPTIMIZER_TRACE,发现其中有个值filesort_priority_queue_optimization里面的chosen=true表示使用的就是优先级排序,所以没有使用临时文件.最终显示number_of_tmp_files=0.

最后我们使用最大堆的(word,rowid)到临时表获取对应的word字段

但是,不管我们使用什么算法,最终我们都会产生大量的计算量,排序过程消耗很大的资源.

随机排序方法

我们简化一下问题,只需要获取一个随机的字段,我们的思路如下

  1. 获取表的主键id的最大值,和最小值
  2. 然后根据最大值和最小值,算出x=(M-N)*rand() + N;
  3. 再获取不小于X的第一行

对应的sql语句如下

mysql> 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可能存在空洞,导致每一行的获取概率并不一样,如id=1,2,4,5,而id=4获取的id概率是其他行的两倍。

因此我们可以使用下面算法,叫做随机算法2

  1. 获取整张表的总行数C
  2. 计算出Y= floor(C * rand())。floor函数在这里的作用,就是取整数部分
  3. 获取 limit Y ,1,得到一行数据

对应的sql如下

mysql> 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;

这个算法解决了上一个随机算法1不均匀的问题,并且他在第一步扫描了C行,而在第三步扫描了Y+1行,一共扫描了C+Y+1行,执行的代价要比随机算法高效很多.

现在如果要获取三个随机数,根据随机算法2的思路

  1. 获取整张表的总行数C
  2. 根据同样的共识获取Y1,Y2,Y3
  3. 再执行limit Y,1.获取三个随机数

对应的sql语句如下

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

本文分享自微信公众号 - 洁癖是一只狗(rookie-dog),作者:洁癖汪

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-10-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Mysql如何使用order by工作

    日常开发中,我们经常要进行字段的排序,但是我们大多不知道排序是如何执行的,今天我们就说说order by 的执行逻辑,

    小土豆Yuki
  • Mysql中sql执行如此慢

    我们经常发现,往往执行一条简单的查询语句,但是很长时间都没有返回,今天我们看看是什么原因导致的

    小土豆Yuki
  • 面试Mybatis之#和$

    使用 #{} 格式的语法会导致 MyBatis 创建 PreparedStatement 参数占位符并

    小土豆Yuki
  • npm nodejs 经典安装问题

    自从转开发后,碰到了很多以前没有遇到过的问题,搜索出来的文章因为思维方式和关键字的转变,对应的搜索结果也和以前大不一样,我也发现自己以前对很多技术的理解被国内的...

    运维部落
  • nodejs 与 npm 配置

    xuyaowen
  • 拼车返乡族的数据可视化

    本次数据样本共13041条,本别采集了北京、上海、广州、深圳、杭州的某一天出行数据,由于手动操作难以保证取样的公平性,所以不能对全部数据结果的准确性做保证,本文...

    量化小白
  • DamnVulnerableCryptoApp:一款功能强大的弱加密实现检测工具

    DamnVulnerableCryptoApp是一款实现了各种弱加密的应用程序,广大研究人员可以使用DamnVulnerableCryptoApp来查看、测试或...

    FB客服
  • 5分钟理解String的&#39;+&#39;的性能及原理

    实验二可以很明显地看出,编译器在编译时产生的字节码已经将 "a" + "b" 优化成了 "ab",同理多个字符串的相加也会被优化处理,需要注意的是字符串常量相加...

    好好学java
  • MSSQL 利用 CLR 技术执行系统命令

    在某项目外围打点的过程中,通过文件上传拿到一个 WebShell。通过 WebShell 能够执行大多数的命令,且直接是 System 权限,但却无法执行 di...

    信安之路
  • Nginx日志配置及日志分析脚本案例

    其中access log 记录了哪些用户,哪些页面以及用户浏览器、ip和其他的访问信息

    菲宇

扫码关注云+社区

领取腾讯云代金券