Mysql 随机查询


目标

  • 随机返回数据库中符合条件的 N 条记录。

可选查询方案


  • MYSQL 的 RAND() 函数。
  • 自定义查询与排序条件（推荐）

查询测试

环境说明


  • 一张有 40 多万条记录的数据库表：jw_jobinfo
  • Navicat Premium 12
  • 查询记录数：

    • ​​SELECT count(*) FROM jw_jobinfo;​​
    • Mysql 随机查询_随机查询


根据 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 秒：


  • Mysql 随机查询_sql_02
  • 算法的缺陷：


    • 如上所示，查询的目标是在符合条件的记录中随机返回 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 执行效率：
Mysql 随机查询_sql_03










总结

1、第一种算法-通过 order by rand() 返回随机记录。优点：返回的记录非常随机，缺点：执行时间特别长。

2、第二种算法-随机获取一个合适的Id 值然后进行排序。优点：执行时间端；缺点：返回的记录没有第一种算法那么随机，而是随机的连续 n 条记录。

3、上面两种算法执行时间的比较前提是：


  • 数据库表很大 ，一般至少有几十万条记录
  • 查询字段很多。

    • 查询的字段的数量对第一种算法的影响很大，如果只查询几个字段，用第一种算法也是可以接受的：
    • Mysql 随机查询_随机查询_04


附录