mysql 随机查询N条记录
method 1 :
SELECT * FROM tablename ORDER BY RAND() LIMIT N;
method 2 :
SELECT * FROM tablename AS T1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM tablename)) AS id)AS T2 WHERE T1.id >= T2.id ORDER BY T1.id ASC LIMIT N;
method 2 相比 method 1 效率要高些，
TESTING CASE :
不同数量级的表的查询对比
[root@TKE1 scripts]# mysql -e "select count(*) from tkglobal.user;"
+----------+
| count(*) |
+----------+
| 6555 |
+----------+
[root@TKE1 scripts]# mysql -e "select count(*) from tkglobal.dynamiccatalogue;"
+----------+
| count(*) |
+----------+
| 38756021 |
+----------+
利用linux time tool to test cost time
[root@TKE1 scripts]# time mysql -e "SELECT id,name FROM tkglobal.user ORDER BY RAND() LIMIT 1; "
+-------+--------+
| id | name |
+-------+--------+
| 49303 | 2871 |
+-------+--------+
real 0m0.011s
user 0m0.001s
sys 0m0.004s
[root@TKE1 scripts]# time mysql -e "SELECT xid,SiteID FROM tkglobal.dynamiccatalogue ORDER BY RAND() LIMIT 1; "
+---------+--------+
| xid | SiteID |
+---------+--------+
| dcmid | 13 |
+---------+--------+
real 0m51.931s
user 0m0.001s
sys 0m0.005s
[root@TKE1 scripts]#
[root@TKE1 scripts]# time mysql -e "SELECT id,name FROM tkglobal.user AS T1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM tkglobal.user)) AS id)AS T2 WHERE T1.id >= T2.id ORDER BY T1.id ASC LIMIT 1; "
ERROR 1052 (23000) at line 1: Column 'id' in field list is ambiguous
real 0m0.006s
user 0m0.002s
sys 0m0.003s
[root@TKE1 scripts]# time mysql -e "SELECT xid,SiteID FROM tkglobal.dynamiccatalogue AS T1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM tkglobal.dynamiccatalogue)) AS id)AS T2 WHERE T1.id >= T2.id ORDER BY T1.id ASC LIMIT 1; "
ERROR 1247 (42S22) at line 1: Reference 'id' not supported (forward reference in item list)
real 0m0.005s
user 0m0.002s
sys 0m0.002s
[root@TKE1 scripts]#
发现：
在表中数据很大的时候，method 2 相比method1 效率高出了很多
本文出自 “ColinSpace” 博客，请务必保留此出处http://colinspace.blog.51cto.com/1043642/1364260