在MySQL中用SQL语句取随机记录
表总的数据量
select count(*) from obj_track_20130304 ; --4320000条
1.
select * from obj_track_20130304 order by rand() limit 5;
--查询时间12.328s
2.
SELECT * FROM obj_track_20130304 AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM obj_track_20130304)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 5;
--查询时间0.528s
3.
SELECT * FROM obj_track_20130304 WHERE id>=(SELECT FLOOR(RAND()*(SELECT MAX(id) FROM obj_track_20130304))) ORDER BY ID LIMIT 5;
--查询时间0.015s
1,2,3比较,不同的SQL语句查询效率差别很大。
PS:
rand()是产生的随机数;floor()是不大于指定数的最大整数.