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

背景：有个单词表，随机显示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。 扫描行数增加了，但是解决了概率平均的问题。