Mysql 随机查询

目标

• 随机返回数据库中符合条件的 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、上面两种算法执行时间的比较前提是：

• 数据库表很大 ，一般至少有几十万条记录
• 查询字段很多。
  • 查询的字段的数量对第一种算法的影响很大，如果只查询几个字段，用第一种算法也是可以接受的：
  • 

附录

参考博客：https://blog.csdn.net/zwc2xm/article/details/79467829