MySQL 随机查询表中N条记录

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 效率高出了很多


本文出自 “老鹰工作室” 博客,请务必保留此出处http://colinspace.blog.51cto.com/1043642/1364260

MySQL 随机查询表中N条记录,布布扣,bubuko.com

MySQL 随机查询表中N条记录

上一篇:Mysql Master error :Failed to initialize the master info structure


下一篇:mysql主从同步从库上Slave_IO_Running: Connecting问题