对于mysql的随机查询
比如一个场景,在一个网站中,有一个随机显示单词的功能,也就是有一个单词表,每次用户访问首页的时候,随机滚动显示三个单词,随着单词表的增大,选单词的逻辑越来越慢
如何能保证选词所消耗的时间不那么大吗
表基本如下
create Table ‘words'(‘id’ int(11) not null AUTO_INCREMENT,
‘word’ varchar(64) DEAULT NULL,
PRIMARY KEY(‘id’)
)ENGINE=InnoDB;
插入了10000行记录,那么如何随机选择出三个单词
1.利用内存临时表
mysql>select word from words order by rand() limit 3;
随机排序后取前三个,虽然SQL语句写法简单,但是执行流程需要用到临时表了
上一节中,我们说到,在InnoDB中,排序可以选择的方式有两种,分别为rowId和sort_buffer两种方式
而rowid因为需要多进行一次回表操作,所以并不会被InnoDB默认选择,但是对于上面使用rand()方法的语句,使用的是Memory引擎,所以会直接选择rowid字段进行排序,整体的执行流程为
1.创建一个临时表,里面有两个字段,一个为随机数字段R,另一个字段为varchar(64) 记为word字段
2.然后从words表中取出所有的word值,对于每一个word值,并且使用rand函数来生成一个大于0小于1的随机小数,然后放入这个临时表,这一次会扫描word表一次,扫描了10000行
3.然后对这个临时表排序,这时候又扫描了一次临时表,变为了20000行
4.最后取出前三个结果的位置信息,依次到内存临时表中取出word值,返回给客户端,总扫描数为20003行
流程基本如下
上面的R/pos中,是因为临时表没有主键,而生成的一个6字节的row id作为主键
而且,临时表的创建还涉及磁盘临时表
如果临时表大小超过了tmp_table_size,那么内存临时表会转为磁盘临时表,使用innodb引擎
如果使用临时磁盘表,那么排序的时候,就是一个innodb表的排序过程
对于上面语句的innodb查询,在mysql之后,引入了一个新的排序算法,优先队列排序算法
是因为现在的 SQL 语句,只需要取R值最小的3个rowid
而优先队列算法，就可以精确地只得到三个最小值，执行流程如下：
1.对于这 10000 个准备排序的 (R,rowid)，先取前三行，构造成一个堆；
2.取下一个行 (R’,rowid’)，跟当前堆里面最大的 R 比较，如果 R’小于 R，把这个 (R,rowid) 从堆中去掉，换成 (R’,rowid’)；
3.重复到完成排序
但是这种写法,还是需要大量的扫描行数，因此排序过程的资源消耗也会很大。
回到原本的问题
如果只随机选择 1 个 word 值，可以怎么做呢？
1.取得这个表的主键 id 的最大值 M 和最小值 N;
2.用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;
3.取不小于 X 的第一个 ID 的行。
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;
取 max(id) 和 min(id) 都是不需要扫描索引的,select利用了索引快速定位
但是由于ID有空洞,所以并不是想象中如此准确
为此我们优化一下
1.取得整个表的行数，并记为 C。
2.取得 Y = floor(C * rand())。 floor 函数在这里的作用，就是取整数部分。
3.再用 limit Y,1 取得一行。
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; |
这样,虽然limit需要顺序的读出,并丢弃不需要的前Y个,所以需要扫描Y个,加上统计的C个,取一个值我们需要扫描C+Y+1,执行代码比随机算法1代价高
但是由于在主键上进行的搜索,所以性能消耗也没想象中的高
那么随机取3个的方式就是如下
| 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； |