MySQL 中随机抽样：order by rand limit 的替代方案

最近由于需要大概研究了一下MYSQL的随机抽取实现方法。举个例子，要从tablename表中随机提取一条记录，大家一般的写法就是：SELECT * FROM tablename ORDER BY RAND() LIMIT 1。

  MYSQL的官方手册，里面针对RAND()的提示大概意思就是，在ORDER BY从句里面不能使用RAND()函数，因为这样会导致数据列被多次扫描。但是在MYSQL 3.23版本中，仍然可以通过ORDER BY RAND()来实现随机。

  但是真正测试一下才发现这样效率非常低。一个15万余条的库，查询5条数据，居然要8秒以上。查看官方手册，也说rand()放在ORDER BY 子句中会被执行多次，自然效率及很低。

You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.

  搜索Google，网上基本上都是查询max(id) * rand()来随机获取数据。
  SELECT *

FROM table AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM table)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id ASC LIMIT 5;

 SELECT *

FROM table AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM table)-(SELECT MIN(id) FROM table))+(SELECT MIN(id) FROM table)) AS id) AS t2
WHERE t1.id >= t2.id
ORDER BY t1.id LIMIT 1;

采用JOIN的语法比直接在WHERE中使用函数效率还要高很多。