聘我网

 新概念招聘3.0

MySQL中如何高效实现随机取行？

vote up0vote downstar 
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 |
+----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+
1 row in set (0.00 sec)

上面的显然太低效了。

 

1 个答复

vote up0vote downcheck

随机取一行可以这样：

SET @r := (SELECT ROUND(RAND() * (SELECT COUNT(*) FROM mytable)));
SET @sql := CONCAT('SELECT * FROM mytable LIMIT ', @r, ', 1');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

如果要一般情况下的随机取N行，则需要用到变量，并且复杂度为O(n):

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

这里有更详细的解释。

链接

您的回答

不是您要找的问题? 浏览其他含有标签 的问题或者 自己问个.