如何正确地显示随机消息？读后总结

深藏阁楼爱情的钟 2021-10-30 06:56 255阅读 0赞

背景：有个单词表，随机显示3个单词  
建表语句与初始化语句  
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();

随机显示3个单词用什么sql  
一般可能会用到的 order by rand()  
select word from words order by rand() limit 3;  
我们用explain查询语句执行。  
关注extra字段using temporary表示使用临时表，using filesort表示需要执行排序。

对于内存临时表的排序来说，（PS：内存临时表的默认引擎是memory）  
innodb为了减少磁盘的访问，优先选择全字段排序  
内存表memory回表无须访问磁盘，优先选择rowid排序。

语句执行流程：

1.  创建临时表，引擎为memory，有R，W两个字段，没有建索引  
    2.从words表中，俺主键顺序取出所有的word值，对于每一个word值，调用rand（）函数生成一个大于0小于1的随机小数。把随机小数与word存入临时表的RW字段  
    3.临时表安装R字段排序。  
    4.初始化sort\_buffer,（由于用rowid排序）只有这个R字段与默认rowid字段。  
    5.从临时表中取出R值与rowid，存入sort\_buffer。（这个过程设计内存表的全表扫描会更加扫描行数。）  
    6.sort\_buffer根据R值排序。  
    7.排序后取前三个结果rowid，到内存表取W字段返回给客户端。

小结：order by rand()使用了内存临时表，内存临时表排序的时候使用了rowid排序。

磁盘临时表  
tmp\_table\_size这个参数限制了内存临时表的大小。默认值是16M，如果临时表的大小超过了tmp\_table\_size这个值。则内存临时表会转为磁盘临时表

磁盘临时表默认引擎使用innodb。由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

查看optimizer\_trace的结果。

number\_of\_tmp\_files为0，不需要临时文件。  
mysql5.6后引入了优先队列排序算法（大小根堆算法，不需要将所有的数据进行排序。）  
select city,name,age from t where city=‘杭州’ order by name limit 1000 ;  
之前我们只有三条记录，需要维护堆的大小只有3行。  
但是现在我们将记录行扩展到了1000行，超过了设置的sort\_buffer\_size，只能选用归并排序。

小结：order by rand()不管用什么临时表，计算过程都比较复杂。需要扫描大量的行，且排序过程消耗大量的资源。

随机排序方法  
随机算法1：  
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;  
1.获取这个表主键id的最大值M与最小值N  
2.用随机函数生成一个M~N之间的数X  
3.取不小于X的第一个ID的行。  
缺点ID如果不连续，则M~N之前的空洞会影响其他行的概率。

随机算法2

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。  
2.用随机函数取得1~C之前的随机值  
3.再用limit Y，1取得一行  
mysql处理limit Y，1的做法是按顺序读入，丢弃前Y个，然后把下个作为结果返回。扫描行数会加上Y。 扫描行数增加了，但是解决了概率平均的问题。