目标
- 随机返回数据库中符合条件的 N 条记录。
可选查询方案
- MYSQL 的 RAND() 函数。
- 自定义查询与排序条件(推荐)
查询测试
环境说明
- 一张有 40 多万条记录的数据库表:jw_jobinfo
- Navicat Premium 12
- 查询记录数:
SELECT count(*) FROM jw_jobinfo;
根据 MYSQL 的 RAND() 函数排序
- SQL 语句中,直接写 ORDER BY RAND( ) :
SELECT * FROM `jw_jobinfo` WHERE id > 1000 ORDER BY RAND( ) LIMIT 5;
- SQL 语句非常简洁,返回结果非常随机,但是查询效率很低。下面是几次查询的平均值,查询耗时接近 5 秒:
自定义查询与排序条件(推荐)
- 查询排序原理:
-
查询出符合条件的记录中 id 的最大值 maxId;
-
查询出符合条件的记录中 id 的最小值 minId;
-
查询排序算法可以表示如下:
select * from 数据表 where 数据表 .id >= minId + 随机数 order by 数据表 .id
- 随机数的值在 0 ~(maxId - minId) 之间
- 这个算法有缺陷(下面会给出说明和改进方案)
-
SQL语句:
SELECT * FROM `jw_jobinfo` AS t1 JOIN ( SELECT ROUND( RAND( ) * ( ( SELECT MAX( id ) FROM `jw_jobinfo` WHERE id > 1000 ) - ( SELECT MIN( id ) FROM `jw_jobinfo` WHERE id > 1000 ) ) + ( SELECT MIN( id ) FROM `jw_jobinfo` WHERE id > 1000 ) ) AS id ) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 5;
-
查询效果,耗时 0.004 秒:
- 算法的缺陷:
- 如上所示,查询的目标是在符合条件的记录中随机返回 n 条,那么,当 minId + 随机数刚好等于 maxId 时,查出的记录会只有一条。
- 为此,可以做以下改进:
-
先查询出根据 id 降序排序的符合条件的 n 条记录,找出其中的最小 id 值。然后用这个最小 id 值代替上面的 maxId。
-
改进后的完整的 SQL 语句 :
SELECT * FROM `jw_jobinfo` AS t1 JOIN ( SELECT ROUND( RAND( ) * ( ( SELECT MIN( id ) FROM (SELECT id FROM `jw_jobinfo` WHERE id > 1000 ORDER BY id desc limit 5) tt ) - ( SELECT MIN( id ) FROM `jw_jobinfo` WHERE id > 1000 ) ) + ( SELECT MIN( id ) FROM `jw_jobinfo` WHERE id > 1000 ) ) AS id ) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 5;
- 即:
修替换 SQL 中的 SELECT MAX( id ) FROM `jw_jobinfo` WHERE id > 1000 为 SELECT MIN( id ) FROM (SELECT id FROM `jw_jobinfo` WHERE id > 1000 ORDER BY id desc limit 5) tt
-
SQL 执行效率:
-
- 算法的缺陷:
-
总结
1、第一种算法-通过 order by rand() 返回随机记录。优点:返回的记录非常随机,缺点:执行时间特别长。
2、第二种算法-随机获取一个合适的Id 值然后进行排序。优点:执行时间端;缺点:返回的记录没有第一种算法那么随机,而是随机的连续 n 条记录。
3、上面两种算法执行时间的比较前提是:
- 数据库表很大 ,一般至少有几十万条记录
- 查询字段很多。
- 查询的字段的数量对第一种算法的影响很大,如果只查询几个字段,用第一种算法也是可以接受的: