如何從大型 PostgreSQL 表中有效地選擇隨機行？

從PostgreSQL等大型資料庫中隨機選擇行，可能是效能密集型任務。本文探討兩種高效實現此目標的常用方法，並討論其優缺點。

方法一：以隨機值過濾

select * from table where random() < 0.01;

此方法隨機對行進行排序，然後根據閾值進行過濾。但是，它需要進行全表掃描，對於大型資料集來說可能很慢。

方法二：依隨機值排序並限制結果

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

此方法隨機對行進行排序，並選擇前n行。它的性能優於第一種方法，但它有一個限制：當行組中存在過多行時，它可能無法選擇隨機子集。

大型資料集的最佳化方案

對於具有大量行的表（例如您範例中的5億行），以下方法提供了一個最佳化的解決方案：

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;                          -- 去除多余项

此查詢利用ID列上的索引進行高效率檢索。它在ID空間內產生一系列隨機數，確保ID唯一，並將資料與主表連接以選擇所需數量的行。

其他考慮因素

邊界查詢：
表ID列的空隙相對較少至關重要，以避免在隨機數產生中需要大型緩衝區。

物化視圖：
如果需要重複調用隨機數據，考慮創建物化視圖可以提高效能。

PostgreSQL 9.5 的 TABLESAMPLE SYSTEM：
PostgreSQL 9.5 中引入的這種最佳化技術允許快速取樣指定百分比的行。

以上是如何從大型 PostgreSQL 表中有效地選擇隨機行？的詳細內容。更多資訊請關注PHP中文網其他相關文章！