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

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中文網其他相關文章！