我如何才能最好地编写一个查询,从总共600k中随机选择10行?
当前回答
您可以轻松地使用带限制的随机偏移量
PREPARE stm from 'select * from table limit 10 offset ?';
SET @total = (select count(*) from table);
SET @_offset = FLOOR(RAND() * @total);
EXECUTE stm using @_offset;
您还可以像这样应用where子句
PREPARE stm from 'select * from table where available=true limit 10 offset ?';
SET @total = (select count(*) from table where available=true);
SET @_offset = FLOOR(RAND() * @total);
EXECUTE stm using @_offset;
在600,000行(700MB)表查询执行上的测试花费了大约0.016秒的硬盘驱动器时间。
EDIT:偏移量可能取接近表末尾的值,这将导致select语句返回更少的行(或者可能只有一行),为了避免这种情况,我们可以在声明偏移量后再次检查,如下所示
SET @rows_count = 10;
PREPARE stm from "select * from table where available=true limit ? offset ?";
SET @total = (select count(*) from table where available=true);
SET @_offset = FLOOR(RAND() * @total);
SET @_offset = (SELECT IF(@total-@_offset<@rows_count,@_offset-@rows_count,@_offset));
SET @_offset = (SELECT IF(@_offset<0,0,@_offset));
EXECUTE stm using @rows_count,@_offset;
其他回答
如果你想要一个随机记录(不管id之间是否有空隙):
PREPARE stmt FROM 'SELECT * FROM `table_name` LIMIT 1 OFFSET ?';
SET @count = (SELECT
FLOOR(RAND() * COUNT(*))
FROM `table_name`);
EXECUTE stmt USING @count;
来源:https://www.warpconduit.net/2011/03/23/selecting-a-random-record-using-mysql-benchmark-results/评论- 1266
这非常快,而且是100%随机的,即使你有间隙。
将SELECT Count(*)中可用的行数计算为rows FROM TABLE 选择10个不同的随机数a_1,a_2,…,a_10在0到x之间 SELECT * FROM TABLE LIMIT 1 offset a_i for i=1,…,10
我在Bill Karwin的《SQL反模式》一书中发现了这个破解方法。
我知道这不是你想要的,但我将给你的答案是我在一个小网站的生产中使用的。
根据您访问随机值的次数,不值得使用MySQL,因为您将无法缓存答案。我们在那里有一个按钮来访问一个随机页面,用户可以每分钟点击几次,如果他愿意的话。这将导致MySQL的大量使用,至少对我来说,MySQL是优化的最大问题。
我会采用另一种方法,你可以把答案存储在缓存中。调用MySQL:
SELECT min(id) as min, max(id) as max FROM your_table
使用min和max Id,您可以在服务器中计算一个随机数。在python中:
random.randint(min, max)
然后,用你的随机数,你可以在你的表中得到一个随机Id:
SELECT *
FROM your_table
WHERE id >= %s
ORDER BY id ASC
LIMIT 1
在这种方法中,对数据库执行两次调用,但是可以缓存它们,并且在很长一段时间内不访问数据库,从而提高性能。注意,如果表中有洞,这不是随机的。有超过一行很容易,因为你可以使用python创建Id,并为每行执行一个请求,但由于它们是缓存的,这是可以的。
如果你的表中有太多的洞,你可以尝试同样的方法,但是现在是记录的总数:
SELECT COUNT(*) as total FROM your_table
然后在python中你可以这样写:
random.randint(0, total)
为了获取一个随机结果,你可以使用如下所示的LIMIT:
SELECT *
FROM your_table
ORDER BY id ASC
LIMIT %s, 1
注意它会在X个随机行之后得到1个值。即使您的表中有洞,它也将是完全随机的,但它将为您的数据库带来更多的开销。
如果你只有一个读请求
将@redsio的答案与一个临时表结合起来(600K并不是很多):
DROP TEMPORARY TABLE IF EXISTS tmp_randorder;
CREATE TABLE tmp_randorder (id int(11) not null auto_increment primary key, data_id int(11));
INSERT INTO tmp_randorder (data_id) select id from datatable;
然后用一个@redsios的版本回答:
SELECT dt.*
FROM
(SELECT (RAND() *
(SELECT MAX(id)
FROM tmp_randorder)) AS id)
AS rnd
INNER JOIN tmp_randorder rndo on rndo.id between rnd.id - 10 and rnd.id + 10
INNER JOIN datatable AS dt on dt.id = rndo.data_id
ORDER BY abs(rndo.id - rnd.id)
LIMIT 1;
如果表比较大,可以先筛选第一部分:
INSERT INTO tmp_randorder (data_id) select id from datatable where rand() < 0.01;
如果你有很多读请求
Version: You could keep the table tmp_randorder persistent, call it datatable_idlist. Recreate that table in certain intervals (day, hour), since it also will get holes. If your table gets really big, you could also refill holes select l.data_id as whole from datatable_idlist l left join datatable dt on dt.id = l.data_id where dt.id is null; Version: Give your Dataset a random_sortorder column either directly in datatable or in a persistent extra table datatable_sortorder. Index that column. Generate a Random-Value in your Application (I'll call it $rand). select l.* from datatable l order by abs(random_sortorder - $rand) desc limit 1;
这个解决方案用最高和最低的random_sortorder来区分“边缘行”,所以在间隔中重新排列它们(一天一次)。
一个伟大的职位处理几个情况,从简单,到差距,到不均匀与差距。
http://jan.kneschke.de/projects/mysql/order-by-rand/
对于大多数一般情况,你可以这样做:
SELECT name
FROM random AS r1 JOIN
(SELECT CEIL(RAND() *
(SELECT MAX(id)
FROM random)) AS id)
AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1
这假设id的分布是相等的,并且id列表中可能存在间隙。有关更高级的示例,请参阅本文
推荐文章
- 如何关闭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!(阶乘)可能的序列?