MysqL> EXPLAIN SELECT * FROM urls ORDER BY RAND() LIMIT 1;
+----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
| 1 | SIMPLE | urls | ALL | NULL | NULL | NULL | NULL | 62228 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
以上不符合效率,我该如何正确地做到？
UPDATE
似乎使用答案中提到的解决方案仍无济于事：
MysqL> explain SELECT *
-> FROM (
-> SELECT @cnt := COUNT(*) + 1,-> @lim := 10
-> FROM urls
-> ) vars
-> STRAIGHT_JOIN
-> (
-> SELECT r.*,-> @lim := @lim - 1
-> FROM urls r
-> WHERE (@cnt := @cnt - 1)
-> AND RAND(20090301) < @lim / @cnt
-> ) i;
+----+-------------+------------+--------+---------------+------+---------+------+-------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+------+---------+------+-------+------------------------------+
| 1 | PRIMARY | 最佳答案
Quassnoi编写了一个关于随机选择行而不执行排序的post.他的示例随机选择10行,但您可以调整它以仅选择一行.
如果你想要它真的很快,那么你可以使用一个不完全统一的近似值,或者有时候无法返回一行.
您还可以使用存储过程从Bill Karwin’s post快速选择随机行：
SET @r := (SELECT ROUND(RAND() * (SELECT COUNT(*) FROM mytable)));
SET @sql := CONCAT('SELECT * FROM mytable LIMIT ',@r,',1');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
请注意,这将在MyISAM中比InnoDB运行得快得多,因为在InnoDB中COUNT(*)是昂贵的,但在MyISAM中几乎是即时的.