如何从大型 PostgreSQL 表中高效地选择随机行？

从大型PostgreSQL表中选择随机行

处理大型数据集时，选择随机行可能是一项计算密集型任务。本文探讨了从包含大约 5 亿行的表中检索随机行的各种方法，并讨论了它们的性能和准确性。

方法一：使用 RANDOM() 和 LIMIT

第一种方法包括使用 RANDOM() 函数生成随机数，然后使用 LIMIT 子句过滤结果以获取所需数量的行。

SELECT * FROM table WHERE RANDOM() < 0.000002 LIMIT 1000;

这种方法的优点是易于实现，但对于大型表来说效率可能很低。由于使用了 LIMIT 子句，数据库必须扫描表的全部行才能挑选随机行并丢弃其余行。

方法二：使用 ORDER BY RANDOM() 和 LIMIT

另一种方法是首先按 RANDOM() 函数对行进行排序，然后使用 LIMIT 子句获取随机行。

SELECT * FROM table ORDER BY RANDOM() LIMIT 1000;

这种方法与第一种方法类似，但排序保证更有效地选择随机行。它减少了所需的扫描次数，使其成为大型表的更好选择。但是，对于行数极多的表，它仍然不是最佳选择。

高效方法：使用数字 ID 列和索引

对于具有数字 ID 列且间隙较少的表，可以使用更有效的方法。这涉及在 ID 范围内生成随机数并将其用于与表连接。

WITH params AS (
   SELECT 1 AS min_id,              -- 最小 ID <= 当前最小 ID
        5100000 AS id_span             -- 四舍五入。(max_id - min_id + buffer)
    )
SELECT *
FROM  (
   SELECT p.min_id + trunc(random() * p.id_span)::integer AS id
   FROM   params p, generate_series(1, 1100) g  -- 1000 + buffer
   GROUP  BY 1                        -- 去除重复项
) r
JOIN   table USING (id)
LIMIT  1000;

这种方法利用索引访问来显著减少所需的扫描次数。它非常适合具有大量行且 ID 列中间隙较少的表。

考虑因素和建议

选择随机行的最佳方法取决于具体的表特征和性能要求。对于小型表，RANDOM() 或 ORDER BY RANDOM() 方法可能就足够了。但是，对于具有数字 ID 列且间隙较少的大型表，建议使用上述优化方法以获得最佳性能。

需要注意的是，由于计算机中伪随机数生成的性质，这些方法都不能保证真正的随机性。但是，它们提供了一种从大型表中以合理的效率和准确性获取随机行样本的实用方法。

以上是如何从大型 PostgreSQL 表中高效地选择随机行？的详细内容。更多信息请关注PHP中文网其他相关文章！