MySQL如何随机查询表中的几行记录

2021/8/2 19:08:29

编程Tag： mysql 排序随机 id SQL rand limit 表中几行

本文主要是介绍MySQL如何随机查询表中的几行记录，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

ps: 推荐使用以下的方法三.

方法一：

使用order by rand()，命令比较简单，只需要一行就行，但是执行效率低，扫描表记录行数多。具体指令如下：

mysql> select word from words order by rand() limit 3;

该sql的意思就是随机选取表中的三条记录，只需要这么一句命令行。这里用到的是order by的排序方法，需要临时表，其具体执行流程如下
请添加图片描述 强调：

order by rand使用的是rowid排序方法，只要内存临时表空间够，就把所有字段放入内存临时表，sort_buffer表排完序后只要回内存表就行，不用回原表进行磁盘io；
position字段：当建立的表没有主键时，InnoDB默认会给表添加一个字段来定位数据行，上图就是建立了一个position字段作为rowid，回表时根据position来定位内存临时表中的word字段值；
以上使用的是优先队列排序算法，因为随机取的只有前三行数据，所以没必要使用快速排序或者归并排序对所有数据进行排序。（优先队列排序类似于冒泡排序，只不过只冒一个泡泡，这个泡泡里面有三个排好序的值，每次从队列中取一个值，分别与这三个值比较并替换，所以只能得到排名前三的数据记录）；
以上过程一共从表中扫描了20003行数据。

以下方法二和方法三考虑只取一行随机数据；两种方法效率都比第一种方法高，都需要多行sql语句。

方法二：随机排序法1（效率高，不保证随机）

效率很高，主要根据扫描表记录行数判断，但是精确度很差。

具体过程如下

取得这个表的主键 id 的最大值 M 和最小值 N;
用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;
取不小于 X 的第一个 ID 的行。

对应的sql语句如下

mysql> select max(id),min(id) into @M,@N from t ;  // @表示一个变量，不需要扫描表
set @X= floor((@M-@N+1)*rand() + @N);  // 生成id_max和id_min区间内的某一个随机数（导致概率不一致）
select * from t where id >= @X limit 1;  // 取得id等于该随机数的记录行，使用索引减少扫描行数

使用该方法的话就不需要像方法一一样在内存中先进行数据行的排序了，直接从表中读取数据就行，而且扫描行数特别少。

存在问题：当表中的数据有被删除过时，也就是id_max和id_min之间存在无数据的空行，如下图
请添加图片描述
按照sql语句：

select * from t where id >= @X limit 1;

当计算出来的@X为3-10000时取到的记录行都是word3，而@X取值在（1,10001）之间的每一个整数概率都是一样的，所以几乎取不到word1和word2，也就不能叫随机取值了。

方法三：改进的随机排序法2（效率居中，保证随机）

体过程如下：

取得整个表的行数，并记为 C；
取得 Y = floor(C * rand())。 floor 函数在这里的作用，就是取整数部分；
再用 limit Y,1 取得一行。

对应的sql语句如下（limit后面跟变量要用sql语句拼接）

mysql> select count(*) into @C from t;  // 读取所有非空行的总数
set @Y = floor(@C * rand());  // 随机选择总行数中的某一个整数，只会取到012，且概率一致
set @sql = concat("select * from t limit ", @Y, ",1");  
prepare stmt from @sql;
execute stmt;
DEALLOCATE prepare stmt;

该方法中第一句用count(*)只会读取表中非空行，并且用limit语句会自动跳过空行。一共扫描的行数就是C+Y+1;如下图所示
请添加图片描述