本文主要是介绍如何正确地显示随机消息?读后总结,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
背景:有个单词表,随机显示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排序。
语句执行流程:
- 创建临时表,引擎为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。 扫描行数增加了,但是解决了概率平均的问题。
这篇关于如何正确地显示随机消息?读后总结的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!