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

從大型PostgreSQL表中選擇隨機行

處理大型資料集時，選擇隨機行可能是一項計算密集型任務。本文探討了從包含約 5 億行的表中檢索隨機行的各種方法，並討論了它們的性能和準確性。

方法一：使用 RANDOM() 和 LIMIT

第一種方法包括使用 RANDOM() 函數產生隨機數，然後使用 LIMIT 子句過濾結果以取得所需數量的行。

<code class="language-sql">SELECT * FROM table WHERE RANDOM() < 0.000002 LIMIT 1000;</code>

這種方法的優點是易於實現，但對於大型表來說效率可能很低。由於使用了 LIMIT 子句，資料庫必須掃描表格的全部行才能挑選隨機行並丟棄其餘行。

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

另一種方法是先按 RANDOM() 函數對行進行排序，然後使用 LIMIT 子句取得隨機行。

<code class="language-sql">SELECT * FROM table ORDER BY RANDOM() LIMIT 1000;</code>

這種方法與第一種方法類似，但排序保證更有效地選擇隨機行。它減少了所需的掃描次數，使其成為大型表的更好選擇。但是，對於行數極多的表，它仍然不是最佳選擇。

高效方法：使用數字 ID 欄位和索引

對於具有數字 ID 列且間隙較少的表，可以使用更有效的方法。這涉及在 ID 範圍內產生隨機數字並將其用於與表連接。

<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   table USING (id)
LIMIT  1000;</code>

這種方法利用索引存取來顯著減少所需的掃描次數。它非常適合具有大量行且 ID 列中間隙較少的表。

考慮因素與建議

選擇隨機行的最佳方法取決於特定的表特徵和性能要求。對於小型表，RANDOM() 或 ORDER BY RANDOM() 方法可能就足夠了。但是，對於具有數字 ID 列且間隙較少的大型表，建議使用上述最佳化方法以獲得最佳效能。

要注意的是，由於電腦中偽隨機數產生的性質，這些方法都不能保證真正的隨機性。但是，它們提供了一種從大型表中以合理的效率和準確性獲取隨機行樣本的實用方法。

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