首页 >数据库 >mysql教程 >如何从大型 PostgreSQL 表中高效地选择随机行?

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

Barbara Streisand
Barbara Streisand原创
2025-01-21 05:26:13853浏览

How to Efficiently Select Random Rows from Large PostgreSQL Tables?

PostgreSQL随机行选择方法

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

  • 使用random()过滤:

    <code class="language-sql">  select * from table where random() < 0.001;</code>
  • 使用order by random()limit:

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

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

大型表的优化方法

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

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

查询:

<code class="language-sql">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;</code>

工作原理:

  • ID范围估计:

    • 如果不精确知道,则查询表以估计ID列的最小值、最大值和总跨度(最大值 - 最小值)。
  • 随机ID生成:

    • 在估计的ID范围内生成一组不同的随机数。
  • 冗余和重复消除:

    • 对生成的数字进行分组以去除重复项,减少选择缺失行或已选择行的可能性。
  • 表连接和限制:

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

为什么它速度快:

  • 最小的索引使用:

    • 查询仅对ID列执行索引扫描,比全表扫描或排序操作快得多。
  • 优化的随机数生成:

    • 生成的随机数分布在估计的ID范围内,最大限度地减少了缺失或重叠行的可能性。
  • 冗余和重复消除:

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

其他选项:

  • 处理间隙的递归CTE:

    • 对于ID序列中存在间隙的表,请添加一个额外的CTE来处理这些间隙。
  • 函数包装器以供重用:

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

    • 创建一个通用函数,该函数接受任何具有整数列的表作为参数。
  • 物化视图以提高速度:

    • 考虑基于优化查询创建物化视图,以便更快地检索(准)随机选择的行。
  • PostgreSQL 9.5中的TABLE SAMPLE

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

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

声明:
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn