MySQL实战45讲 17

17 | 如何正确地显示随机消息？

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

1. CREATE TABLE `words` (
2.   `id` int(11) NOT NULL AUTO_INCREMENT,
3.   `word` varchar(64) DEFAULT NULL,
4.   PRIMARY KEY (`id`)
5. ) ENGINE=InnoDB;
7. delimiter ;;
8. create procedure idata()
9. begin
10.   declare i int;
11.   set i=0;
12.   while i<10000 do
13.     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))));
14.     set i=i+1;
15.   end while;
16. end;;
17. delimiter ;
19. 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 取得一行。
  

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 语句得到三行数据。
  

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

复制代码

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

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