随机获取数据的业务场景,想必大家都有遇到过,今天我们分析一下如何正确的显示随机消息.
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();
上面是我们插入了10000行记录,现在我们要随机选择三个单词,又有什么办法实现呢.
内存临时表
首先,我们第一时间会想到order by rand()来实现
select word from words order by rand() limit 3
我们在看看这条语句是如何执行的使用explain
可以看到extra的值有Using temporary,表示使用临时表,Using filesort,表示需要排序,即需要临时表,在临时表上进行排序,
我们可以想想上一次,我们说过的全字段排序,和rowid排序,当时我们总结的是,对于innodb存储引擎,执行全字段排序减少磁盘访问,因此会被优先选择.
但是对于内存表,回表过程只是简单的根据数据行的位置,直接访问内存得到数据,根本不会导致多访问磁盘,因此优化器如果没有这个顾虑,那么他优先考虑的是排序的行越少越好了,所以,Mysql这个时候就会选择rowid.
我们在来看看上面随机获取字段的sql语句是如何执行的
我们在通过慢日志验证我们的结论
# 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;
下面就是我们整个流程的示意图
上图我们发现sort_buffer中的位置信息,是个什么概念呢,而Mysql是如何定位一行数据的呢,
首先我们知道mysql中有以下规则
而上面说的rowid就是我们引擎中唯一标识行的标志,最后,我们总结到order by rand(),是使用临时表,按照rowid进行排序在内存表中进行排序
磁盘临时表
其实并不是所有的临时表都是内存表,tmp_table_size配置限制了内存临时表,默认大小是16M,当临时表的大于这个参数的时候,就会使用磁盘临时表.而磁盘临时表是由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
因为我们设置max_length_for_sort_data=16,由于word字段没有超过,因此使用rowid,符合我们的预期,但是我们发现sort_buffer_size=32768,而R长度是8字节,而rowid是6字节,有10000行数据,已经超过了sort_buffer_size,但是number_of_tmp_files=0,说明没有使用临时文件进行排序,这个不是我们所预期的,
接下来,我们分析一下,为什么没有使用临时文件进行排序,那是因为mysql5.6使用了另外一种算法优先级排序算法,
其实,虽然我们只需要前三个word,但是如果我们使用归并算法,发现我们已经把1000行的数据都已经进行排序了,而这种算法非常浪费计算量.
而优先级算法,可以精准的获取最小的三个word
如下示意图
上面描述了优先级排序的过程,最终获取到一个最大堆(word,rowid).
回想我们上面有一个根据OPTIMIZER_TRACE,发现其中有个值filesort_priority_queue_optimization里面的chosen=true表示使用的就是优先级排序,所以没有使用临时文件.最终显示number_of_tmp_files=0.
最后我们使用最大堆的(word,rowid)到临时表获取对应的word字段
但是,不管我们使用什么算法,最终我们都会产生大量的计算量,排序过程消耗很大的资源.
随机排序方法
我们简化一下问题,只需要获取一个随机的字段,我们的思路如下
对应的sql语句如下
mysql> 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;
虽然上面可以获取一个数,但是他并不是一个随机数,因为如何表中的id可能存在空洞,导致每一行的获取概率并不一样,如id=1,2,4,5,而id=4获取的id概率是其他行的两倍。
因此我们可以使用下面算法,叫做随机算法2
对应的sql如下
mysql> 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行,而在第三步扫描了Y+1行,一共扫描了C+Y+1行,执行的代价要比随机算法高效很多.
现在如果要获取三个随机数,根据随机算法2的思路
对应的sql语句如下
mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1,1;//在应用代码里面取Y1、Y2、Y3值,拼出SQL后执行
select * from t limit @Y2,1;
select * from t limit @Y3,1;