ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL实战45讲 17 [打印本页]

作者: 雁过留声 时间: 2022-9-1 16:08
标题: MySQL实战45讲 17
17 | 如何正确地显示随机消息？

场景：从一个单词表中随机选出三个单词。
表的建表语句和初始数据的命令如下，在这个表里面插入了 10000 行记录：

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();

复制代码

这个方法效率很高，因为取 max(id) 和 min(id) 都是不需要扫描索引的，而第三步的 select 也可以用索引快速定位，可以认为就只扫描了 3 行。但实际上，这个算法本身并不严格满足题目的随机要求，因为 ID 中间可能有空洞，因此选择不同行的概率不一样，不是真正的随机。
比如你有 4 个 id，分别是 1、2、4、5，如果按照上面的方法，那么取到 id=4 的这一行的概率是取得其他行概率的两倍。如果这四行的 id 分别是 1、2、40000、40001 ，这个算法 bug 了。
方法二：
为了得到严格随机的结果，可以用下面这个流程:

取得整个表的行数，并记为 C。
取得 Y = floor(C * rand())。 floor 函数在这里的作用，就是取整数部分。
再用 limit Y,1 取得一行。

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

复制代码

MySQL 处理 limit Y,1 的做法就是按顺序一个一个地读出来，丢掉前 Y 个，然后把下一个记录作为返回结果，因此这一步需要扫描 Y+1 行。再加上，第一步扫描的 C 行，总共需要扫描 C+Y+1 行，执行代价比随机算法 1 的代价要高。
按照随机算法 2 的思路，要随机取 3 个 word 值

取得整个表的行数，记为 C；
根据相同的随机方法得到 Y1、Y2、Y3；
再执行三个 limit Y, 1 语句得到三行数据。

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

复制代码

总扫描行数是 C+(Y1+1)+(Y2+1)+(Y3+1)
Q：怎么做来减少扫描行数呢？说说你的方案，并说明你的方案需要的扫描行数。
A：
让表里面保存一个无空洞的自增值，这样就可以用算法 1 来实现

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)