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

从PostgreSQL等大型数据库中随机选择行，可能是一个性能密集型任务。本文探讨两种高效实现此目标的常用方法，并讨论其优缺点。

方法一：按随机值过滤

<code class="language-sql">select * from table where random() < 0.01;</code>

此方法随机对行进行排序，然后根据阈值进行过滤。但是，它需要进行全表扫描，对于大型数据集来说可能很慢。

方法二：按随机值排序并限制结果

<code class="language-sql">select * from table order by random() limit 1000;</code>

此方法随机对行进行排序，并选择前n行。它的性能优于第一种方法，但它有一个局限性：当行组中存在过多行时，它可能无法选择随机子集。

大型数据集的优化方案

对于具有大量行的表（例如您示例中的5亿行），以下方法提供了一种优化的解决方案：

<code class="language-sql">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   big USING (id)
LIMIT  1000;                          -- 去除多余项</code>

此查询利用ID列上的索引进行高效检索。它在ID空间内生成一系列随机数，确保ID唯一，并将数据与主表连接以选择所需数量的行。

其他考虑因素

边界查询：
表ID列的空隙相对较少至关重要，以避免在随机数生成中需要大型缓冲区。

物化视图：
如果需要重复调用随机数据，考虑创建物化视图可以提高性能。

PostgreSQL 9.5 的 TABLESAMPLE SYSTEM：
PostgreSQL 9.5 中引入的这种优化技术允许快速采样指定百分比的行。

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