mysql取最大值_Mysql如何随机获取表中的数呢rand()

随机获取数据的业务场景,想必大家都有遇到过,今天我们分析一下如何正确的显示随机消息.

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语句是如何执行的

1. 创建一个临时表，临时表使用的是memory引擎，表里面有两个字段,一个字段double类型，我们叫R，另一个字段varchar(64),记为W，且没有建立索引

2. 从words表中，按照主键顺序取出word值，使用rand()让每一个word生成一个大于0小于1的小数，并把这个小数和word放入到临时表的R,W,到此扫描行数是10000.

3. 现在临时表有10000行数据了，接下来你要在这个没有索引的内存临时表上,按照R字段排序

4. 初始化sort_buffer中两个字段，一个是double,一个整形

5. 从内存临时表中一行一行的获取R和位置信息，把字段放入到sort_buffer的两个字段中，此时要全表扫描临时表，扫描的行数为10000行，此时总共扫描的行数变成了2000行

6. sort_buffer根据R字段进行排序，这里没有涉及到表的扫描

7. 在根据sort_buffer排序的结果到临时表获取前三个word字段，返回给客户端,此时扫描了3行，一共有2003行

我们在通过慢日志验证我们的结论

# Query_time: 0.900376  Lock_time: 0.000347 Rows_sent: 3 Rows_examined: 20003SET timestamp=1541402277;select word from words order by rand() limit 3;

下面就是我们整个流程的示意图

上图我们发现sort_buffer中的位置信息，是个什么概念呢，而Mysql是如何定位一行数据的呢, 首先我们知道mysql中有以下规则

• 对于有主键的innodb表来说，rowid就是我们的主键

• 对于没有主键的innodb表说，rowid由系统自动生成

• 而memory引擎不是索引组织表,我们可以认为次引擎中有一个数组，而rowid就是数组的下标

而上面说的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

1. 从临时表中获取前三行，组成一个最大堆

2. 然后拿下一行数据，和最大堆的R比较，大于R，则丢弃，小于R，则替换

3. 重复2的步骤，直到把10000行数据循环完成

如下示意图

上面描述了优先级排序的过程，最终获取到一个最大堆(word,rowid). 回想我们上面有一个根据OPTIMIZER_TRACE，发现其中有个值filesort_priority_queue_optimization里面的chosen=true表示使用的就是优先级排序,所以没有使用临时文件.最终显示number_of_tmp_files=0. 最后我们使用最大堆的(word,rowid)到临时表获取对应的word字段 但是，不管我们使用什么算法，最终我们都会产生大量的计算量，排序过程消耗很大的资源.

随机排序方法

我们简化一下问题，只需要获取一个随机的字段，我们的思路如下

1. 获取表的主键id的最大值，和最小值

2. 然后根据最大值和最小值，算出x=(M-N)*rand() + N;

3. 再获取不小于X的第一行

对应的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

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;

这个算法解决了上一个随机算法1不均匀的问题，并且他在第一步扫描了C行，而在第三步扫描了Y+1行，一共扫描了C+Y+1行,执行的代价要比随机算法高效很多. 现在如果要获取三个随机数，根据随机算法2的思路

1. 获取整张表的总行数C

2. 根据同样的共识获取Y1,Y2,Y3

3. 再执行limit Y,1.获取三个随机数

对应的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；

如果对您有一丝丝帮助，麻烦点个关注，也欢迎转发，谢谢

扫码关注