也来谈一下随即查询数据的效率问题

也来谈一下随即查询数据的效率问题
看到这篇文章：
http://jnote.cn/blog/mysql/mysql-rand-efficiency.html

我做了些测试：

mysql> show create table song\G
*************************** 1. row ***************************
       Table: song
Create 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_TIME
STAMP,
  `rank` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `f_rank` (`rank`)
) ENGINE=MyISAM AUTO_INCREMENT=1752001 DEFAULT CHARSET=gbk
1 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 t2
WHERE t1.id >= t2.id
ORDER 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`))) ORD
ER 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)