MySQL 获取随机条数据

场景

有一个需要从数据库随机获取指定数量的数据的需求，然而这个问题却是意外的挺麻烦。

假设有一个数据表

create table topic (
  id      int primary key not null
  comment '编号',
  content varchar(20)     not null
  comment '内容'
)
  comment '主题表';

这里的 topic 表有两个关键性的特点

• 主键可以进行比较（int）
• 主键整体存在趋势（自增/自减）

解决方案 1：直接使用 order by rand()

直接使用 order by rand() 就可以获取到随机的数据了，而且能够获取到全部的数据（顺序仍然是随机的）。

1. 按照 rand() 产生的结果

   这一步相当于为每条数据加上一列由 rand() 函数产生的数据，然后对这一列进行排序

2. 限制查询条数

select *
from topic
order by rand()
limit 50000;

但缺点很明显，速度是个问题，因为 rand() 的数据没有索引，所以会造成排序速度极慢。

在 10w 条数据中随机获取 5w 条数据，花费时常 6 s 378 ms，这个时间真的太长了点。

其实 order by rand() 看起来很奇怪，实际上等效于：

select *
from (
       select
         topic.*,
         rand() as order_column
       from topic
     ) as temp
order by order_column
limit 50000;

解决方案 2：使用 where subquery 取中间的随机值

因为 order by rand() 没有索引导致的排序太耗时，我们可以尝试绕过这个问题。

下面的这种解决方案便是如此

1. 取最小值和最大值之间的随机值
2. 判断 id 是否大于（或者小于）这个随机值
3. 限制查询条数

select *
from topic
where id >= ((select max(id)
              from topic)
             - (select min(id)
                from topic))
            * rand()
            + (select min(id)
               from topic)
limit 50000;

这种方法查询速度虽然极快（150 ms），但却会受到数据分布密度的影响。如果数据不是平均的，那么查询到的总数据条数就会受限。

那么，下面来说该方法的缺陷

• 获取到的数据受分布密度影响

  例如数据分布呈以下情况

  1,100002,100003,100004...199999,200000

  那么使用上述代码就只能获取到很少一部分数据（大约在 2.5w 条左右）。然而如果将符号稍微下改一下，将 >= 修改为 <=，那么能够获取到的平均数量将大大增加（7.5w 条左右）。

  select *
  from topic
  # 注意：这里的符号修改了
  where id >= ((select max(id)
                from topic)
              - (select min(id)
                  from topic))
              * rand()
              + (select min(id)
                from topic)
  limit 50000;

• 每一条数据获取到的概率不是完全相同的
  虽然获取到的全部数据是随机的，但每一个的概率却并不相同。例如在 <= 时会出现永远都为第一条的现象，究其原因就是因为 第一条 的概率实在是太大了，因为查询数据表时数据的检索规则是从第一条开始的呢！即便修改成 >=，所得到的第一条数据也普遍偏小。
  使用 >= 的结果

  • 数据越是在前面，那么获取到的概率就越低
  • 但即便是很低概率，在最前面总有机会，所以第一条一般偏小
  • 数据密度前面偏大时，获取到的数量会非常小

密度越是趋于平均，获取到的最大随机数据条数的平均值愈接近 1/2，否则则会愈加偏离（不一定偏大还是偏小）。

解决方案 3：使用临时表 temporary table

解决方案 2 着眼于避免使用没有索引的 rand() 进行排序，但这里思考另一个解决方案，使用加了索引之后的 rand() 进行排序。创建临时表，仅包含主键 id 和需要进行排序的索引列 randomId，然后排序完成过后获取到乱序数据。

drop temporary table if exists temp_topic;
create temporary table temp_topic (
  id       bigint primary key not null,
  randomId double             not null,
  index (randomId)
)
  as
    select
      id,
      rand() as randomId
    from topic;
select t.*
from topic t
  join (
         select id
         from (
                select id
                from temp_topic
                order by randomId
              ) as temp
         limit 50000
       ) as temp
    on t.id = temp.id;

这种方法的查询速度不算很快（878 ms，相比于第二种），而且仍然是与数据量呈正相关的（因为要复制数据）。但和第一种，也是真正的随机获取。

解决方案 4：使用 join order by rand()

吾辈在 StackOverflow 上面看到了一个 最优解 by 2016，一切表现的都很好，速度不算慢（261 ms），也可以获取到全部数据，也是真正的随机获取。

select g.*
from
  topic g
  join
  (select id
   from
     topic
   where
     rand() < (select ((50000 / count(0)) * 10)
               from
                 topic)
   order by rand()
   limit 50000) as z on z.id = g.id;

这里的 where rand() 是想要在获取的数据与总数据量差距过大（10 倍以上）时过滤数据，提高排序效率。

吾辈这里不清楚上面发生了什么，感觉 rand() 只要在 join 里面之后的排序就会变得很快！
事实上，只要使用 join 内连接之后，就算是 order by rand() 的效率也很高，甚至在获取少量数据时比上面使用 where rand 过滤数据后再排序的的速度还要快

SELECT t.*
FROM topic t
  JOIN
  (SELECT id
   FROM `topic`
   ORDER BY RAND()
   LIMIT 50000) AS z ON z.id = t.id;

注：在获取的数据量很大时，使用上面那种 where 过滤一次的效率上会更有优势一点！

总结

这里有一篇不错的英文文章对随机获取数据进行了分析：http://jan.kneschke.de/projects/mysql/order-by-rand/，也有人在 StackOverflow 上进行了讨论 https://stackoverflow.com/questions/1823306/

不同点	order by rand()	where subquery	temporary table	join order by rand()
可以随机获取全部	可以	几乎不可能	可以	可以
速度	慢	极快	较快	极快
需要可比较的主键类型	否	是	否	否
受数据分布密度影响	否	是	否	否
速度受表数据复杂度影响	很大	极小	较小	极小

那么，看完上面的不同点对比，我们也可以得出它们的使用场景了

• 强烈推荐首选 join order by rand() 作为随机获取数据的解决方案
• 唯一不推荐的就是 order by rand()，这是新手才会写出来 sql。当然，如果你的数据量很小（1000 条以下）时，直接使用 order by rand() 以现代机器的性能 sql 也不会很慢呢

注：如果仅仅只是需要打乱数据顺序的话还是更推荐将数据读取到内存中在进行操作更好！