在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()是不大于指定数的最大整数.

 

 

posted on 2013-03-29 16:07  淘果果  阅读(290)  评论(0编辑  收藏  举报

导航