order by rand() 实现随机获取消息

业务场景

比如,要做一个学习单词的App,实现用户在打开首页的时候,随机显示三个单词:

// 创建单词表
mysql> create table words(
	`id` int NOT NULL AUTO_INCREMENT,
	`word` varchar(64) DEFAULT NULL,
	PRIMARY KEY(`id`)
)ENGINE=InnoDB;

// 插入10000行模拟数据
mysql> insert into words values(1, "abandon") (...) (...);

/********************************************************
*  @ order by rand() 
*  实现随机排序,随机选取三个单词
********************************************************/
mysql> select word from words order by rand() limit 3;

// 使用explain命令查看该语句的执行情况
mysql> explain select word from words order by rand() limit 3;

通过学习前面 Mysql数据库 order by 实现原理,我们知道,对于使用InnoDB引擎的表来说,全字段排序,可以不用回表,所以,会被优先采用。但是,对于内存表来说,数据已经读到内存了,无非是根据数据行的位置,直接访问内存的得到,不会再访问磁盘。所以,对于使用内存临时表进行的排序来说,优化器没有了回表的顾虑,rowid 排序会被优先采用。

内存临时表

explain结果,Extra字段 Using temporary表示使用临时表,Using filesort表示需要排序。
explain结果
SQL语句执行流程

  1. 创建临时表,使用memory引擎。该临时表有两个字段,一个 double 类型字段,我们记为R,一个varchar(64)类型字段,我们记为W,表上没有索引。
  2. 根据主键id的顺序,从 words 表中读取 word 值。对于每个 word 值,调用rand()生成一个0~1之间的随机小数,放到R字段,并将对应的 word 放入W字段。
  3. 此时,临时表中有10000行数据,我们需要根据 R 的值进行排序;
  4. 初始化 sort_buffer,确定放入 doule 和 int 两个字段;(其中:double字段保存临时表的R值,int字段保存“位置信息”,后面我们解释为什么保存的是位置信息)
  5. 从内存临时表中一行一行读取R和 “位置信息”放入sort_buffer;
  6. 根据R字段进行排序,排序完,取前三行结果,依次到内存临时表根据位置信息取出word值,返回。

Mysql表是如何定位一条记录的

答案是:rowid,每个引擎用来唯一标记行信息的标识。

  • 对于创建了主键的 InnoDB 表来说,rowid 就是主键ID;
  • 没有创建主键的 InnoDB 表,系统会默认生成一个6字节的 rowid 作为主键;
  • 对于Memory引擎的表,“位置信息”可以理解为数组的下标。

磁盘临时表

临时表一定是内存临时表吗?不一定。

参数 tmp_table_size 限制了内存临时表的大小,默认16 M,如果超过最大限制,就会使用磁盘临时表,磁盘临时表使用的引擎默认是 InnoDB,当使用磁盘临时表的时候,对应的就是一个没有显示索引的InnoDB表的排序过程。

排序优化

经过上面的排序完成后,所有的数据都有序了,但是我们只需要前三个,有点浪费。所以Mysql在5.6版本之后引入新的排序算法:优先队列排序算法(堆排序)。

mysql> select word from words order by rand() limit 3;

如何得到严格的随机结果

  1. 扫描全表,得到总行数C
  2. 取得 Y=floor(C * rand())。floor取整。
  3. select * from words limit Y,1 取得一行

limit Y, 1; 和 id >= Y limit 1; 一样吗?
不一样,MySQL 处理 limit Y,1 的做法就是按顺序一个一个地读出来,丢掉前 Y 个,然后把下一个记录作为返回结果。

笔记参考于极客时间《MySQL实战45讲》

  • 1
    点赞
  • 1
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:游动-白 设计师:我叫白小胖 返回首页
评论

打赏作者

基层搬砖的Panda

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值