MySQL 随机查询解决方案

最新推荐文章于 2024-06-19 19:08:59 发布

Disillusion021

最新推荐文章于 2024-06-19 19:08:59 发布

阅读量3.8k

点赞数 5

分类专栏： MySQL 文章标签： java sql mysql

本文链接：https://blog.csdn.net/Disillusion_0/article/details/123896446

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

小数据量随机查询一条或多条(最简单常用)

select * from exam_question order by rand() limit 1;
select * from exam_question order by rand() limit 10;

大数据量主键连续随机查询一条

因为主键连续所以

先查出最大id和最小id
根据最大id和最小id生成一个这之间的随机数
然后再根据这个随机数查询一条记录

SELECT * FROM exam_question AS t1 
JOIN (
	SELECT floor( 
			rand() * (( SELECT max( id ) FROM exam_question ) - ( SELECT min( id ) FROM exam_question ) + 1 ) + ( SELECT min( id ) FROM exam_question )
	) AS id 
) AS t2 
WHERE t1.id >= t2.id 
ORDER BY t1.id 
LIMIT 1;

或者

SELECT * FROM exam_question AS t1 
JOIN (
	SELECT floor( 
			rand() * (( SELECT max( id ) FROM exam_question ) - ( SELECT min( id ) FROM exam_question ) + 1 ) + ( SELECT min( id ) FROM exam_question )
	) AS id 
) AS t2 
WHERE t1.id = t2.id

ps: 这两种写法都只针对主键连续时，概率相等的随机查询一条数据。

第一种写法虽然可以从主键不连续的表里查询出一条数据，但是概率不均匀，会存在某一条或几条数据总是被取到的现象。
第二种写法只能对主键连续的表使用，每一条数据被随机取出的概率相等。

大数据量主键连续随机查询连续的一组数据 (多条连续的记录为一组)

分析，其实是查询一条的变体，只不过变成了随机查询出一条数据后把它后面的几条数据一并取出而已

先计算出随机查询出的这一组数据中第一条数据的id的可能范围，设 n 为要取出的一组有多少条数据，则第一条数据的id的区间为[min(id), max(id) - n + 1]
生成该区间内的一个随机数
根据这个随机数查询出连续的一组数据

-- 假设随机取5条为一组，即n为5
SELECT * FROM exam_question AS t1 
JOIN (
    SELECT floor( 
            rand() * (( SELECT max( id ) - 5 + 1 FROM exam_question ) - ( SELECT min( id ) FROM exam_question ) + 1 ) + ( SELECT min( id ) FROM exam_question )
    ) AS id 
) AS t2 
WHERE t1.id >= t2.id 
ORDER BY t1.id 
LIMIT 5;

ps: 同理只针对主键连续时，每一组数据被取出的概率相等

大数据量主键不连续随机查询一条数据

分析，因为id不连续，所以通常需要将id映射为连续的，再随机取出一个id，然后再根据id查出这一条或多条记录。这里利用行号是连续的特性，通过查出一个随机行来查询(可以理解为将不连续id映射成连续行号列)。

查询出当前表总条数
获取1至总条数之间(含两端)的一个随机数rowid
查询出表中的第rowid行条记录

实现方案一：

-- 这里使用了@rownum变量作为行号列join到原表里
-- 每条记录每次执行where子句时 @rownum 会自增1
-- 为了方便验证把 @rownum 和 rowid(随机行号) 也作为显示列查询了出来
SELECT exam_question.*, @rownum, rowid
FROM exam_question,
	(SELECT @rownum := 0) as r,
	(SELECT floor( rand() * ( SELECT count( id ) FROM exam_question) + 1) AS rowid) as c
where (@rownum := @rownum +1) = c.rowid;

实现方案二：

查询出总条数 select count(*) from exam_question;
生成rowid(随机行号)(用Java或SQL生成)
select * from exam_question where exam_question limit rowid, 1;

ps: 因为个人喜欢在SQL里搞，采用了实现方案一，方案二有兴趣大家可以自行尝试

大数据量主键不连续随机查询多条数据

假设从100万条数据中取出3000条数据
分析，和大数据量主键不连续随机查询一条数据类似，都是要将不连续id映射为连续的。

方案一：可以采取上面的方案，通过join行号列@rownum将不连续id映射为连续行号列。

查询出总条数
生成3000个随机rowid。rowid1，rowid2，rowid3，…， rowid3000。
用select in读取指定的3000条记录

附上SQL(仅供参考)

SELECT @count := count( id ) FROM exam_question;
SELECT @rowid1 := floor( rand() * @count + 1) as rowid1;
SELECT @rowid2 := floor( rand() * @count + 1) as rowid2;
SELECT @rowid3 := floor( rand() * @count + 1) as rowid3;
SELECT @rowid4 := floor( rand() * @count + 1) as rowid4;
SELECT @rowid5 := floor( rand() * @count + 1) as rowid5;
SELECT exam_question.*, @rownum
FROM exam_question, (SELECT @rownum := 0)r
where (@rownum := @rownum + 1) in (@rowid1, @rowid2, @rowid3, @rowid4, @rowid5);

方案二：也可以取出全部id，存入一个数组或临时表等数据结构，然后从中随机取出多个id。

取出100w条记录的id存入数组
在数组中随机取出3000个id
用select in读取指定的3000条记录
ps:
方案二可以提前冗余一个临时表，或数组等数据结构，在每次更新原表时，就将id先存入这个临时表或数组，这样就避免了读取数据时再创建临时表或数组。
并且考虑实际的业务，取某一个数据往往有cache层，如果主键分布不均匀可以把主键放到redis的集合中，从redis集合中随机取就无敌的快了。