看到这篇文章:[url]http://jnote.cn/blog/mysql/mysql-rand-efficiency.html[/url]我做了些测试:mysql> show create table song\G*************************** 1. row *************************** Table: songCreate Table: CREATE TABLE `song` ( `id` int(11) NOT NULL auto_increment COMMENT 'Autoincreament element', `name` mediumtext NOT NULL, `datetime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `rank` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `f_rank` (`rank`)) ENGINE=MyISAM AUTO_INCREMENT=1752001 DEFAULT CHARSET=gbk1 row in set (0.00 sec)mysql> select count(1) from song;+----------+| count(1) |+----------+| 1752000 |+----------+1 row in set (0.00 sec)mysql> select * from song order by rand() limit 10;+---------+----------+---------------------+---------+| id | name | datetime | rank |+---------+----------+---------------------+---------+| 440107 | t440107 | 2007-12-04 14:59:37 | 4335260 || 1237909 | t1237909 | 2007-12-04 15:00:42 | 7867024 || 75076 | t75076 | 2007-12-04 14:59:08 | 1725100 || 1351876 | t1351876 | 2007-12-04 15:00:52 | 6407879 || 915284 | t915284 | 2007-12-04 15:00:15 | 7382198 || 110507 | t110507 | 2007-12-04 14:59:11 | 6505687 || 1256420 | t1256420 | 2007-12-04 15:00:44 | 8501775 || 668347 | t668347 | 2007-12-04 14:59:56 | 4243303 || 547132 | t547132 | 2007-12-04 14:59:46 | 7121637 || 621792 | t621792 | 2007-12-04 14:59:52 | 7006891 |+---------+----------+---------------------+---------+10 rows in set (3.70 sec)SELECT * FROM song AS t1 JOIN ( SELECT ROUND(RAND() * (SELECT MAX(id) FROM song)) AS id) AS t2WHERE t1.id >= t2.idORDER BY t1.id ASC LIMIT 10;mysql> SELECT * FROM song AS t1 JOIN -> ( -> SELECT ROUND(RAND() * (SELECT MAX(id) FROM song)) AS id -> ) AS t2 -> WHERE t1.id >= t2.id -> ORDER BY t1.id ASC LIMIT 10;+---------+----------+---------------------+---------+---------+| id | name | datetime | rank | id |+---------+----------+---------------------+---------+---------+| 1566855 | t1566855 | 2007-12-04 15:01:10 | 7827537 | 1566855 || 1566856 | t1566856 | 2007-12-04 15:01:10 | 846094 | 1566855 || 1566857 | t1566857 | 2007-12-04 15:01:10 | 747858 | 1566855 || 1566858 | t1566858 | 2007-12-04 15:01:10 | 1201006 | 1566855 || 1566859 | t1566859 | 2007-12-04 15:01:10 | 3761459 | 1566855 || 1566860 | t1566860 | 2007-12-04 15:01:10 | 5204274 | 1566855 || 1566861 | t1566861 | 2007-12-04 15:01:10 | 4736994 | 1566855 || 1566862 | t1566862 | 2007-12-04 15:01:10 | 8072147 | 1566855 || 1566863 | t1566863 | 2007-12-04 15:01:10 | 6149754 | 1566855 || 1566864 | t1566864 | 2007-12-04 15:01:10 | 6532326 | 1566855 |+---------+----------+---------------------+---------+---------+10 rows in set (0.02 sec)SELECT * FROM `song` WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `song`)-(SELECT MIN(id) FROM `song`)) + (SELECT MIN(id) FROM `song`))) ORDER BY id LIMIT 10;mysql> SELECT * FROM `song` WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `song`)-(SELECT MIN(id) FROM `song`)) + (SELECT MIN(id) FROM `song`))) ORDER BY id LIMIT 10;+------+-------+---------------------+---------+| id | name | datetime | rank |+------+-------+---------------------+---------+| 2142 | t2142 | 2007-12-04 14:59:03 | 3644248 || 2411 | t2411 | 2007-12-04 14:59:03 | 9358685 || 4401 | t4401 | 2007-12-04 14:59:03 | 7664728 || 4732 | t4732 | 2007-12-04 14:59:03 | 3501462 || 4768 | t4768 | 2007-12-04 14:59:03 | 1688296 || 4820 | t4820 | 2007-12-04 14:59:03 | 346328 || 5075 | t5075 | 2007-12-04 14:59:03 | 3586428 || 5167 | t5167 | 2007-12-04 14:59:03 | 3742607 || 5224 | t5224 | 2007-12-04 14:59:03 | 5285546 || 6244 | t6244 | 2007-12-04 14:59:03 | 3076592 |+------+-------+---------------------+---------+10 rows in set (0.03 sec)SELECT * FROM `song` WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `song`)))) ORDER BY id LIMIT 20;mysql> SELECT * FROM `song` WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `song`)))) ORDER BY id LIMIT 20;+------+-------+---------------------+---------+| id | name | datetime | rank |+------+-------+---------------------+---------+| 1874 | t1874 | 2007-12-04 14:59:03 | 1974147 || 2090 | t2090 | 2007-12-04 14:59:03 | 8433426 || 2653 | t2653 | 2007-12-04 14:59:03 | 2637918 || 3244 | t3244 | 2007-12-04 14:59:03 | 2044034 || 3538 | t3538 | 2007-12-04 14:59:03 | 728785 || 3703 | t3703 | 2007-12-04 14:59:03 | 5099125 || 4941 | t4941 | 2007-12-04 14:59:03 | 7530856 || 5059 | t5059 | 2007-12-04 14:59:03 | 2147224 || 5494 | t5494 | 2007-12-04 14:59:03 | 8994486 || 5550 | t5550 | 2007-12-04 14:59:03 | 1054949 || 5953 | t5953 | 2007-12-04 14:59:03 | 5898393 || 6515 | t6515 | 2007-12-04 14:59:03 | 7227102 || 6818 | t6818 | 2007-12-04 14:59:03 | 4292209 || 6962 | t6962 | 2007-12-04 14:59:03 | 7107078 || 7242 | t7242 | 2007-12-04 14:59:03 | 1639503 || 7449 | t7449 | 2007-12-04 14:59:03 | 547861 || 8109 | t8109 | 2007-12-04 14:59:03 | 2031446 || 8764 | t8764 | 2007-12-04 14:59:03 | 3934261 || 8808 | t8808 | 2007-12-04 14:59:03 | 1433932 || 9283 | t9283 | 2007-12-04 14:59:04 | 1558430 |+------+-------+---------------------+---------+20 rows in set (0.03 sec)
本文转自 david_yeung 51CTO博客,原文链接:http://blog.51cto.com/yueliangdao0608/81274,如需转载请自行联系原作者