大規模な PostgreSQL テーブルからランダムな行を効率的に選択するにはどうすればよいですか?

PostgreSQL のランダム行選択メソッド

従来のランダムな行選択方法は、数百万、さらには数十億のレコードを含む大きなテーブルを扱う場合、非効率的で時間がかかります。一般的なメソッドは次の 2 つです:

• 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 中国語 Web サイトの他の関連記事を参照してください。