我如何才能最好地编写一个查询,从总共600k中随机选择10行?
当前回答
我需要一个查询从一个相当大的表中返回大量随机行。这是我想到的。首先获取最大记录id:
SELECT MAX(id) FROM table_name;
然后将该值代入:
SELECT * FROM table_name WHERE id > FLOOR(RAND() * max) LIMIT n;
Where max is the maximum record id in the table and n is the number of rows you want in your result set. The assumption is that there are no gaps in the record id's although I doubt it would affect the result if there were (haven't tried it though). I also created this stored procedure to be more generic; pass in the table name and number of rows to be returned. I'm running MySQL 5.5.38 on Windows 2008, 32GB, dual 3GHz E5450, and on a table with 17,361,264 rows it's fairly consistent at ~.03 sec / ~11 sec to return 1,000,000 rows. (times are from MySQL Workbench 6.1; you could also use CEIL instead of FLOOR in the 2nd select statement depending on your preference)
DELIMITER $$
USE [schema name] $$
DROP PROCEDURE IF EXISTS `random_rows` $$
CREATE PROCEDURE `random_rows`(IN tab_name VARCHAR(64), IN num_rows INT)
BEGIN
SET @t = CONCAT('SET @max=(SELECT MAX(id) FROM ',tab_name,')');
PREPARE stmt FROM @t;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @t = CONCAT(
'SELECT * FROM ',
tab_name,
' WHERE id>FLOOR(RAND()*@max) LIMIT ',
num_rows);
PREPARE stmt FROM @t;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
$$
then
CALL [schema name].random_rows([table name], n);
其他回答
这里有一个改变游戏规则的方法,可能对许多人有帮助;
我有一个有200k行的表,有连续的id,我需要选择N个随机行,所以我选择根据表中最大的id生成随机值,我创建了这个脚本来找出哪个是最快的操作:
logTime();
query("SELECT COUNT(id) FROM tbl");
logTime();
query("SELECT MAX(id) FROM tbl");
logTime();
query("SELECT id FROM tbl ORDER BY id DESC LIMIT 1");
logTime();
结果如下:
计数:36.8418693542479毫秒 Max: 0.241041183472 ms 订单:0.216960906982毫秒
根据这个结果,order desc是得到最大id的最快操作, 以下是我对这个问题的回答:
SELECT GROUP_CONCAT(n SEPARATOR ',') g FROM (
SELECT FLOOR(RAND() * (
SELECT id FROM tbl ORDER BY id DESC LIMIT 1
)) n FROM tbl LIMIT 10) a
...
SELECT * FROM tbl WHERE id IN ($result);
供您参考:从一个200k表中随机获得10行,我花了1.78 ms(包括php方面的所有操作)
我想这是最好的办法了。
SELECT id, id * RAND( ) AS random_no, first_name, last_name
FROM user
ORDER BY random_no
它是非常简单的单行查询。
SELECT * FROM Table_Name ORDER BY RAND() LIMIT 0,10;
我看了所有的答案,我认为没有人提到这种可能性,我也不知道为什么。
如果你想要最大限度的简单和速度,在一个较小的成本,那么对我来说,它似乎是有意义的存储在DB中的每一行的随机数。只需要创建一个额外的列random_number,并将其默认值设置为RAND()。在此列上创建索引。
然后,当您想检索一行时,在代码(PHP、Perl等)中生成一个随机数,并将其与列进行比较。
SELECT FROM tbl WHERE random_number >= :random LIMIT 1
我想虽然它对于单行来说非常整洁,但是对于像OP要求的十行,你必须分别调用它十次(或者想出一个我立即逃脱的聪明的调整)
如果有一个自动生成的id,我发现一个很好的方法是使用模运算符'%'。例如,如果您需要70,000条随机记录中的10,000条,您可以简化为每7行中需要1行。这可以在这个查询中简化:
SELECT * FROM
table
WHERE
id %
FLOOR(
(SELECT count(1) FROM table)
/ 10000
) = 0;
如果目标行除以total available的结果不是一个整数,那么你将得到比你要求的更多的行,所以你应该添加一个LIMIT子句来帮助你像这样修剪结果集:
SELECT * FROM
table
WHERE
id %
FLOOR(
(SELECT count(1) FROM table)
/ 10000
) = 0
LIMIT 10000;
这确实需要一个完整的扫描,但它比ORDER BY RAND更快,在我看来,比本文中提到的其他选项更容易理解。另外,如果写入数据库的系统批量创建了一组行,你可能不会得到你所期望的随机结果。
推荐文章
- 如何关闭mysql密码验证?
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- MySQL区分大小写查询
- 获得PostgreSQL数据库中当前连接数的正确查询
- 如何在Ruby中生成a和b之间的随机数?
- 在SQL选择语句Order By 1的目的是什么?
- MySQL数据库表中的最大记录数
- 原则-如何打印出真正的sql,而不仅仅是准备好的语句?
- PHP/MySQL插入一行然后获取id
- 我如何循环通过一组记录在SQL Server?
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- java.util.Random真的那么随机吗?我怎么能生成52!(阶乘)可能的序列?