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 | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 10 | |
| 3 | DERIVED | r | ALL | NULL | NULL | NULL | NULL | 62228 | Using where |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+------------+--------+---------------+------+---------+------+-------+------------------------------+
Quassnoi https://stackoverflow.com/users/55159/quassnoi写了一个post http://explainextended.com/2009/03/01/selecting-random-rows/关于随机选择行而不执行排序。他的示例随机选择 10 行,但您可以对其进行调整以仅选择一行。
如果你想要它是really快,那么您可以使用不完全统一的近似值,或者有时无法返回行。
您还可以使用存储过程快速选择随机行比尔·卡尔文的帖子 https://stackoverflow.com/questions/211329/quick-selection-of-a-random-row-from-a-large-table-in-mysql/213242#213242:
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 运行得快得多,因为 COUNT(*) 在 InnoDB 中很昂贵,但在 MyISAM 中几乎是即时的。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)