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

PostgreSQL随机行选择方法

处理包含数百万甚至数十亿条记录的大型表时，传统的随机行选择方法效率低下且速度缓慢。两种常见方法是：

• 使用random()过滤:

    select * from table where random() < 0.001;

• 使用order by random()和limit:

    select * from table order by random() limit 1000;

然而，由于需要全表扫描或排序，这些方法对于行数较多的表并非最佳选择，会导致性能瓶颈。

大型表的优化方法

对于以下类型的表，请考虑以下优化方法，其速度明显更快：

• 具有少量或中等间隙的数值ID列（已建立索引以加快查找速度）
• 在选择过程中没有或只有最少的写入操作

查询：

WITH params AS (
  SELECT 1 AS min_id, -- 可选：自定义最小ID起始值
       5100000 AS id_span -- 近似ID范围（最大ID - 最小ID + 缓冲）
)
SELECT *
FROM (
  SELECT DISTINCT 1 + trunc(random() * p.id_span)::integer AS id
  FROM params p, generate_series(1, 1100) g
  GROUP BY 1
) r
INNER JOIN big ON r.id = big.id
LIMIT 1000;

工作原理：

• ID范围估计：

  • 如果不精确知道，则查询表以估计ID列的最小值、最大值和总跨度（最大值 - 最小值）。

• 随机ID生成：

  • 在估计的ID范围内生成一组不同的随机数。

• 冗余和重复消除：

  • 对生成的数字进行分组以去除重复项，减少选择缺失行或已选择行的可能性。

• 表连接和限制：

  • 使用ID列（必须建立索引）将随机数与实际表连接。此高效连接检索所选行的相应数据。
  • 最后，应用限制以检索所需的行数。

为什么它速度快：

• 最小的索引使用：

  • 查询仅对ID列执行索引扫描，比全表扫描或排序操作快得多。

• 优化的随机数生成：

  • 生成的随机数分布在估计的ID范围内，最大限度地减少了缺失或重叠行的可能性。

• 冗余和重复消除：

  • 对生成的数字进行分组可确保只选择不同的行，从而减少了消除重复项所需的额外过滤或连接。

其他选项：

• 处理间隙的递归CTE：

  • 对于ID序列中存在间隙的表，请添加一个额外的CTE来处理这些间隙。

• 函数包装器以供重用：

  • 定义一个函数，该函数采用限制和间隙百分比作为参数，允许轻松配置并与不同的表重用。

• 任何表的通用函数：

  • 创建一个通用函数，该函数接受任何具有整数列的表作为参数。

• 物化视图以提高速度：

  • 考虑基于优化查询创建物化视图，以便更快地检索（准）随机选择的行。

• PostgreSQL 9.5中的TABLE SAMPLE：

  • 利用PostgreSQL的“TABLE SAMPLE SYSTEM”功能，实现更快但随机性较低的行采样方法，确保返回精确的行数。但是，请记住，由于聚类效应，样本可能不是完全随机的。

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