目的:获取随机样本记录

从一组数据中随机取几条。这样的需求太太平常了。我们通常把所有的数据读到程序中,由程序再取出随机的样例数据集。其实我们可以直接通过数据库查询拿出这样随机的样例数据。

本章的目的就是要写出一个仅返回随机数据样本的高效SQL。

反模式:随机排序

获取随机记录最常见的SQL语句,就是对查询结果随机排序,然后取第一行。

1
select * from bug order by rand() limit 1;

这样的方式很方便,也很容易理解。但是它的弱点很明显,它意味着整个排序过程无法使用索引,不得不由数据库“手动的”重新排序,也就是全表遍历,它的性能会随着数据量的增长变得越来越慢。

如何识别反模式:当出现以下情况时,可能是反模式

  1. 在SQL中,返回一个随机行速度非常慢;
  2. 要获取所有的记录然后随机一个。要如何增加程序可使用的内存大小?
  3. 有些列出现的频率比别的列要高一些,这个随机算法不是很随机。

合理使用反模式

这个反模式的特征很明显,它不能使用索引,当数据量大的时候很慢,所以在数据量小的时候,你使用它完全没有问题。

解决方案:没有具体的顺序…

随机选择是需要全表遍历并且耗时的进行手动排序的一个典型案例。你预期想办法优化一个不可能被优化的查询,还不如考虑一下别的实现方案。

从1到最大值之间随机选择

一种避免对所有数据排序的方法,就是在1到最大主键之间随机选择一个。

1
2
3
select b1.* from bug b1
join (select ceil(rand() * (select MAX(bug_id) from bug)) as rand_id) as b2
on b1.bug_id = b2.rand_id

这个方案要求主键是连续的,如果主键在中间断了,有可能取到空值。

选择下一个最大值

这个方案实在前一个方案上进行修改,解决主键不连续的问题。这个查询会返回它随机找到第一个有效值

1
2
3
4
5
select b1.* from bug b1
join (select ceil(rand() * (select max(bug_id) from bug)) as bug_id) as b2
where b1.bug_id >= b2.bug_id
order by b1.bug.id
limit 1;

这个方案虽然解决了主键有缝隙的情况,但是当主键缝隙比较大时,它的下一条被选中的概率也会随之增加。

获取所有键值,随机取一个

你可以将所有主键读到内存中,然后从内存中随机选一个,再然后根据主键查询对应的记录。这个方案虽然避免了全表遍历,但是它有以下缺点

  1. 你会获取到一个过长列表,有可能超出程序内存极限,导致整个系统瘫痪
  2. 你必须查询两次,如果查询太复杂或太耗时。这也会成为问题所在

专有的解决方案

每个数据库都可能正对这个需求提供独有的解决方案,如SQL Server 2005 增加一个TABLE-SAMPLE的子句

1
select * from bug TABLESAMPLE(1, ROWS);

Oracle提供了一个类似的SAMPLE,比如返回表中1%的记录

1
select * from (select * from bug SAMPLE(1) order by dbms_random.value) where ROWNUM = 1;

总结

有些查询是无法优化的,换种方法试试看。