我如何才能最好地编写一个查询,从总共600k中随机选择10行?
当前回答
我认为这是一个简单但更快的方法,我在现场服务器上测试了它,与上面的几个答案相比,它更快。
SELECT * FROM `table_name` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table_name` ) ORDER BY id LIMIT 30;
//对一个130行的表花费0.0014秒
SELECT * FROM `table_name` WHERE 1 ORDER BY RAND() LIMIT 30
//对130行的表花费0.0042秒
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 30
//对130行的表花费0.0040秒
其他回答
以下内容应该是快速的,公正的,独立于id列的。但是,它不能保证返回的行数与请求的行数匹配。
SELECT *
FROM t
WHERE RAND() < (SELECT 10 / COUNT(*) FROM t)
解释:假设你想要100行中的10行,那么每一行都有1/10的概率被选中,这可以通过WHERE RAND() < 0.1来实现。这种方法不能保证有10行;但是如果查询运行了足够多的次数,那么每次执行的平均行数将在10左右,并且表中的每一行都将被均匀地选择。
我是这样做的:
select *
from table_with_600k_rows
where rand() < 10/600000
limit 10
我喜欢它,因为它不需要其他表,写起来很简单,执行起来非常快。
我使用了Riedsio发布的http://jan.kneschke.de/projects/mysql/order-by-rand/(我使用了返回一个或多个随机值的存储过程的情况):
DROP TEMPORARY TABLE IF EXISTS rands;
CREATE TEMPORARY TABLE rands ( rand_id INT );
loop_me: LOOP
IF cnt < 1 THEN
LEAVE loop_me;
END IF;
INSERT INTO rands
SELECT r1.id
FROM random AS r1 JOIN
(SELECT (RAND() *
(SELECT MAX(id)
FROM random)) AS id)
AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1;
SET cnt = cnt - 1;
END LOOP loop_me;
在这篇文章中,他通过维护一个表(使用触发器等)解决了id中的间隙导致不那么随机的结果的问题。参见文章); 我通过向表中添加另一列来解决这个问题,用连续的数字填充,从1开始(编辑:此列添加到运行时由子查询创建的临时表中,不影响永久表):
DROP TEMPORARY TABLE IF EXISTS rands;
CREATE TEMPORARY TABLE rands ( rand_id INT );
loop_me: LOOP
IF cnt < 1 THEN
LEAVE loop_me;
END IF;
SET @no_gaps_id := 0;
INSERT INTO rands
SELECT r1.id
FROM (SELECT id, @no_gaps_id := @no_gaps_id + 1 AS no_gaps_id FROM random) AS r1 JOIN
(SELECT (RAND() *
(SELECT COUNT(*)
FROM random)) AS id)
AS r2
WHERE r1.no_gaps_id >= r2.id
ORDER BY r1.no_gaps_id ASC
LIMIT 1;
SET cnt = cnt - 1;
END LOOP loop_me;
在文章中,我可以看到他花了很大的精力来优化代码;我不知道我的改变是否/有多大影响性能,但对我来说非常好。
这非常快,而且是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反模式》一书中发现了这个破解方法。
这里有一个改变游戏规则的方法,可能对许多人有帮助;
我有一个有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方面的所有操作)
推荐文章
- 在SQL Server 2008 R2中重命名数据库时出错
- 将数据复制到另一个表中
- 将表从一个数据库复制到另一个数据库的最简单方法?
- 如何在SQL中选择表的最后一条记录?
- SQL在Oracle中连接多行列值的查询
- 在单个查询中计算空值和非空值
- 在存储过程中使用“SET XACT_ABORT ON”有什么好处?
- 如何通过查询在MySQL中获得数据库结构?
- SQL to LINQ工具
- 如何从一个查询插入多行使用雄辩/流利
- 如何连接列在Postgres选择?
- MySQL删除表中的所有行,并将ID重置为零
- 在准备语句中使用“like”通配符
- MySQL中的表名是否区分大小写?
- 库未加载:libmysqlclient.16。在OS X 10.6上使用mysql2 gem运行'rails server'时出现dylib错误