霜天部落 | 专注PHP研发,研究LAMP高性能架构部署与优化

MySQL 如何从表中取出随机数据

随机查询的SQL语句:

SELECT * FROM table_name ORDER BY rand() LIMIT 5;

我试了一下,8千条记录的表,执行一次需要0.08s,慢了些。如果表数据量很大,不建议采用此方式,太慢。

后来请教了google,得到如下代码

SELECT * FROM table_name AS r1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id)
FROM table_name)) AS id) AS r2 WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT 5;

执行效率需要0.02s.可惜的是,只有mysql 4.1.*以上才支持这样的子查询.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

经过研究,发现使用聚合函数可以提供效率。

使用MAX()*RAND()

SELECT * FROM `table`
WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`)))
ORDER BY id LIMIT 1;

或者:

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

这种方法推荐使用。