目的：获取随机样本记录

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

本章的目的就是要写出一个仅返回随机数据样本的高效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;

总结

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