第十七讲笔记——如何显示随机消息

从单词表中随机选出三个单词。

CREATE TABLE `words` (
`id` int(11) NOTNULLAUTO_INCREMENT,
`word` varchar(64) DEFAULTNULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

随机插入 10000 行数据,之后来分析。

内存临时表

select word fromo words order by rand() limit 3

这个 SQL 就是随机排序取前三个,但执行过程有点复杂。

我们使用 explain 这个命令来看看这个语句的执行情况:

在 Extra 这个字段上的 Using temporary,表示使用了临时表;Using filesort,表示需要执行排序操作。

我们上节课得出的结论:对于 InnoDB 表来说,执行全字段排序会减少磁盘访问,会被优先选择。

这里强调的是 InnoDB 表,但是优化器(server 层)就没有了这个顾虑,那么它会优先考虑,用于排列的行越少越好,所以,MySQL 这时会选择 rowid 排序。那这条语句的执行过程是这样:

  1. 创建一个临时表,这个临时表使用的是 memory 引擎,表里有两个字段,第一个字段是 double 类型,记为字段 R,第二个字段是 varchar(64) 类型,记为字段 W。并且这个表没有主键。
  2. 从 word 表中,按主键顺序取出所有 word 值。对于每一个 word 值,调用 rand() 函数生成一个大于 0 小于 1 的随机小数,并和 word 存入临时表的 R 和 W 字段中,到此,扫描行数是 10000;
  3. 现在在没有索引的内存临时表中,按照字段 R 进行排序;
  4. 初始化 sort_buffer。sort_buffer 中有两个字段,一个是 double 类型,另一个是整型
  5. 从内存临时表中一行一行的取出 R 值和位置信息,分别存入 sort_buffer 中的两个字段里。这个过程要对临时表进行全表扫描,扫描行数变成了 20000;
  6. 在 sort_buffer 中根据 R 的值进行排序。这里没有涉及到表操作,扫描行数不变;
  7. 排序完成后,取出前三个结果的位置信息,依次从内存临时表中取出 word 值,返回给客户端。这个过程中,访问了表的三行数据,总扫描行数变成了 20003。

在这里 pos 就是位置信息。这里就涉及到:MySQL 表是用什么方法来定位“一行数据”的

如果我们把表的主键删掉,那么 InnoDB 会自己生成一个长度为 6 字节的 rowid 来作为主键。

这就是排序模式中 rowid 的来源,rowid 实际上表示:每个引擎用来唯一表示数据行的信息。

  • 对于有主键的 InnoDB 表,这个 rowid 就是主键 ID;
  • 对于没有主键的表,这个 rowid 就是由系统生成;
  • MEMORY 引擎不是索引组织表。这个例子中约等于数组,rowid 就是下标。

总结:order by rand() 使用了内存临时表,内存临时表排序时使用了 rowid 排序方法。

磁盘临时表

tmp_table_size 这个配置限制了内存临时表的大小,默认值是 16M。如果临时表大小大于这个值,那么就会转成磁盘临时表。

基本上就是使用外部排序了。

不过 MySQL 5.6 版本引入了一个叫:优先队列排序算法

上面的 SQL 语句就可以使用这个算法,原理是使用小根堆进行排序,因为只要取 3 个数据。

随机排序方法

先简化一下问题,如果只随机选择 1 个 word 值,怎么做呢?

  1. 取这个表的主键 id 的最大值 M 和最小值 N;
  2. 用随机函数生成一个最大值到最小值之间的数 X = ( M − N ) ∗ r a n d ( ) + N X=(M-N)*rand()+N X=(MN)rand()+N
  3. 取不小于 X 的第一个 ID 的行。

把这个算法,暂时称为算法 1。

select max(id),min(id) into @M,@N from t ;
set @X= floor((@M-@N+1)*rand() + @N);
select * from t where id >= @Xlimit 1;

在 MySQL 中 @.. 是用户变量,@@.. 是系统变量。

这个算法效率很高,但是不严格满足题目随机要求,因为 ID 中间可能有空洞,导致不同行的概率是不一样的。

现在来看算法 2:

  1. 取得整张表的行数,记为 C;
  2. 取得 Y = f l o o r ( C ∗ r a n d ( ) ) Y=floor(C*rand()) Y=floor(Crand())。floor 函数在这里作用是取整数部分。
  3. 再用 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;

由于 limit 后面的参数不能直接跟变量,所以使用了 prepare+execute 方法,相当于 SQL 语句的拼接。

这个算法就是按顺序一个个读,丢到前 Y 个,取 Y + 1 行,这要扫描 Y + 1 行。执行代价比算法 1 高。当然,和 order by rand() 比起来开销还是小很多。

按照算法 2 的思路,随机取三个呢?那就是取整张表行数 C ,得出三个随机数,再执行三个 limit Y,1 语句。

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;
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值