提问人：Abhishek Anand 提问时间：11/11/2023 更新时间：11/11/2023 访问量：102
从 Order By 子句中选择 Random n rows out of Order By 子句
Select Random n rows out of Order By clause
问：
我一遍又一遍地遇到这个问题，并寻求是否有更好（更优化）的方法来实现这一目标。
要求：从表 XYZ 中，从按 updated_at 排序的前 X 行中随机选择 n 行（n 可以是一行或任意数字）
所以，简而言之，我不是在寻找随机行，而是在最后使用的 20 行中随机寻找 5 行。
原因：我们进行多个批量操作，如果我们只选择最早的更新值，它最终将在多个线程中执行所有操作。因此，需要一定程度的随机化。
当前方式 ：目前我们这样做如下，
SELECT * FROM TABLE ORDER BY updated_at ASC LIMIT 20
然后在APP中，我们使用随机化来随机选择其中的5个。
这是低效的，因为我们实际上扔掉了 db 给我们的 15 个结果。
我们在这里谈论的是将近一百万行的表，因此解决方案需要具有可扩展性。
答：
1赞
Erwin Brandstetter
11/11/2023
#1
使用子查询：
SELECT *
FROM (
SELECT * FROM tbl
ORDER BY updated_at
LIMIT 20
) sub
ORDER BY random()
LIMIT 5;
Postgres 有函数 random（），MySQL 有 rand（）。
显然，需要索引才能快速。(updated_at)
如果您确实想锁定选定的行，请考虑（仅限 Postgres）：
要优化泛型随机选择的性能，请执行以下操作：
评论
0赞
Abhishek Anand
11/11/2023
很有意思。绝对应该在 Mysql 和 Postgres 版本上工作，而不会对数据库产生太大影响。已经有复杂的索引了。关于如何使用 Laravel Eloquent 为 Model 完成的任何想法。;)
0赞
Erwin Brandstetter
11/11/2023
我不能评论 Lavarel Eloquent，不使用它。您不需要“复杂”索引，只需要最基本的 B 树索引，或者至少需要一个作为前导索引列的索引。(updated_at)updated_at
0赞
Abhishek Anand
11/11/2023
不用担心。那么，让我开始一个新问题。为了让事情清楚。这应该可以完成工作。将标记为答案。:)
1赞
Adi
11/11/2023
@AbhishekAnand 所以这就是我在你的数据库本身上做这件事的意思，也不要发布另一个问题，因为它可能已经存在了，在这里你$randomRows = YourModel::orderByDesc('updated_at') ->limit(20) ->inRandomOrder() ->limit(5) ->get();
0赞
Stefan Hummert
11/11/2023
#2
我不确定你想做什么，但也许它与这里的这个答案有关：如何在SQL中随机选择行？
如果要对这些行进行更新，则应使用 SELECT * FROM TABLE MYTABLE WHERE ORDERSTATUS='NOTFILLED' FOR UPDATE LIMIT 5;
如果选择使用 FOR UPDATE，则执行相同查询的其他并行进程不会获得第一个查询锁定的行。 请参阅此处： 何时使用 SELECT ...如需更新？
在更新并提交或回滚事务时释放它们。
评论
0赞
starball
11/12/2023
@user16217248这个链接是怎么回事？
0赞
Zegarek
11/11/2023
#3
频繁 top-n 的补充 ：从版本 11 开始，您可以在索引中包含一些额外的数据。因此，如果隐藏在你的下面没有太多的列，而且它们不是太重，你可以用已经快速的索引扫描换取更快的仅索引扫描，（可能）堆提取为零 - 所有内容都只从索引中读取，而不必跳到实际的表： db<>fiddle 上的演示 select*
create table "TABLE" (id bigserial, txt text, updated_at timestamp);
insert into "TABLE"(txt,updated_at)
select random()::text, n
from generate_series(now()-'1 week'::interval,
now(),
'1 second')_(n)
limit 3e5;
create index simple_idx on "TABLE" (updated_at);
explain analyze verbose
SELECT * FROM "TABLE" ORDER BY updated_at ASC LIMIT 20;
| 查询计划 |
|---|
| 限制（成本=0.42..1.61行=20，宽度=48）（实际时间=0.044..0.050行=20循环=1） |
| 输出：id、txt、updated_at |
| -> 使用公共simple_idx进行索引扫描。TABLE“（成本=0.42..17800.42行=300000，宽度=48）（实际时间=0.043..0.047行=20个循环=1） |
| 输出：id、txt、updated_at |
| 规划时间：1.011 ms |
| 执行时间：0.069 ms |
drop index simple_idx;
create index inclusive_idx on "TABLE" (updated_at) include (txt);
vacuum analyze "TABLE";
explain analyze verbose
SELECT txt,updated_at FROM "TABLE" ORDER BY updated_at ASC LIMIT 20;
| 查询计划 |
|---|
| 限制（成本=0.42..1.21行=20，宽度=27）（实际时间=0.038..0.043行=20循环=1） |
| 输出： txt， updated_at |
| -> 仅索引 使用公共inclusive_idx扫描。TABLE“（成本=0.42..11768.42行=300000，宽度=27）（实际时间=0.037..0.040行=20个循环=1） |
| 输出： txt， updated_at |
| 堆提取：0 |
| 规划时间：0.098 ms |
| 执行时间：0.058 ms |
我简化了上面的内容，只显示了对核心查询的改进，但正如小提琴中所示，包装器从中受益是一样的。order by random() limit n
评论
0赞
Abhishek Anand
11/11/2023
这似乎等同于许多数据库提供的 Index + store 子句。这实际上是一件好事，但该表有近 35 列，对于我们的特定用例来说开销太大了。
0赞
Zegarek
11/11/2023
@AbhishekAnand 如果几乎意味着 3 低于 35，它们都适合 - 索引限制为 32 列。即使某些值很长，PostgreSQL 也会在 TOASTing 之前进行就地压缩，时间很长，也不会立即阻止此选项。并不是说它是即插即用的，但对我来说，在我的 10M 测试中，50 倍的加速听起来确实足够好，值得研究。我不确定你所说的索引+商店是什么。在TSQL中有一个等价物，在Oracle中，你只能在IOT（）中。其余的只需将所有列添加到索引列表中即可。textjsonincludeincludecluster
评论