Bagaimana untuk Memilih Baris Rawak dengan Cekap dalam PostgreSQL?

Kaedah pemilihan baris rawak PostgreSQL yang cekap

Untuk memilih baris rawak dalam PostgreSQL, kaedah terbaik bergantung pada saiz jadual, indeks yang tersedia dan tahap rawak yang diperlukan.

Untuk jadual yang sangat besar dengan 500 juta baris dan lajur ID berangka (cth. id):

• Kaedah terpantas:

  • Gunakan fungsi CTE dan random() untuk menjana ID rawak dalam ruang ID.
  • Sertai ID yang dijana dengan jadual menggunakan lajur id.
  • Tapis pendua dan alih keluar ID berlebihan.

<code class="language-sql">WITH params AS (
   SELECT 1       AS min_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;                          -- 去除多余项</code>

• Kaedah penambahbaikan:

  • Gunakan CTE rekursif (random_pick) untuk menghapuskan sebarang jurang dalam ruang ID.
  • Gabungkan hasil rekursif untuk menghapuskan pendua.
  • Gunakan luaranLIMIT untuk memenuhi kekangan.

<code class="language-sql">WITH RECURSIVE random_pick AS (
   SELECT *
   FROM  (
      SELECT 1 + trunc(random() * 5100000)::int AS id
      FROM   generate_series(1, 1030)  -- 1000 + 百分之几 - 根据需要调整
      LIMIT  1030                      -- 查询规划器提示
      ) r
   JOIN   big b USING (id)             -- 消除缺失

   UNION                               -- 消除重复项
   SELECT b.*
   FROM  (
      SELECT 1 + trunc(random() * 5100000)::int AS id
      FROM   random_pick r             -- 加上百分之三 - 根据需要调整
      LIMIT  999                       -- 小于1000，查询规划器提示
      ) r
   JOIN   big b USING (id)             -- 消除缺失
   )
TABLE  random_pick
LIMIT  1000;  -- 实际限制</code>

• Fungsi am:

  • Balut pertanyaan di atas ke dalam fungsi supaya ia boleh digunakan semula untuk mana-mana jadual dengan lajur integer unik.

<code class="language-sql">CREATE OR REPLACE FUNCTION f_random_sample(_tbl_type anyelement
                                         , _id text = 'id'
                                         , _limit int = 1000
                                         , _gaps real = 1.03)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql VOLATILE ROWS 1000 AS
$func$
DECLARE
   _tbl text := pg_typeof(_tbl_type)::text;
   _estimate int := (...);
BEGIN
   RETURN QUERY EXECUTE format(
   $$
   WITH RECURSIVE random_pick AS (
      SELECT ...
      FROM  ...
     ...
   )
   TABLE  random_pick
   LIMIT  ;
   $$
 , _tbl, _id
   )
   USING (...);
END
$func$;</code>

Untuk senario yang tidak memerlukan rawak yang tepat atau panggilan berulang:

• Paparan material:

  • Buat paparan terwujud untuk menyimpan kira-kira baris yang dipilih secara rawak.
  • Segarkan semula pandangan yang menjadi kenyataan secara berkala.

• TABLESAMPLE SYSTEM (n):

  • Diperkenalkan dalam PostgreSQL 9.5, TABLESAMPLE SYSTEM (n) menyediakan kaedah persampelan rawak yang cepat dan tidak tepat.
  Parameter
  • n mewakili peratusan jadual untuk dijadikan sampel.

<code class="language-sql">SELECT * FROM big TABLESAMPLE SYSTEM ((1000 * 100) / 5100000.0);</code>

Nota lain:

• Untuk prestasi terbaik, gunakan indeks pada lajur ID.
• Fungsi random() dalam PostgreSQL tidak selamat dari segi kriptografi.
• Pendekatan yang dicadangkan memberikan tahap rawak yang tinggi untuk kebanyakan kes penggunaan praktikal.

Atas ialah kandungan terperinci Bagaimana untuk Memilih Baris Rawak dengan Cekap dalam PostgreSQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!