Wie wählt man zufällige Zeilen in PostgreSQL effizient aus?

Effiziente PostgreSQL-Methode zur zufälligen Zeilenauswahl

Um zufällige Zeilen in PostgreSQL auszuwählen, hängt die beste Methode von der Größe der Tabelle, den verfügbaren Indizes und dem erforderlichen Grad der Zufälligkeit ab.

Für eine sehr große Tabelle mit 500 Millionen Zeilen und einer numerischen ID-Spalte (z. B. id):

• Schnellste Methode:

  • Verwenden Sie CTE- und random()-Funktionen, um zufällige IDs innerhalb des ID-Raums zu generieren.
  • Verknüpfen Sie die generierte ID mithilfe der ID-Spalte mit der Tabelle.
  • Duplikate herausfiltern und überflüssige IDs entfernen.

<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>

• Verbesserungsmethode:

  • Verwenden Sie rekursiven CTE (random_pick), um Lücken im ID-Bereich zu schließen.
  • Führen Sie rekursive Ergebnisse zusammen, um Duplikate zu vermeiden.
  • Extern anwendenLIMIT, um Einschränkungen zu erfüllen.

<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>

• Allgemeine Funktionen:

  • Verpacken Sie die oben genannten Abfragen in eine Funktion, damit sie für jede Tabelle mit eindeutigen Ganzzahlspalten wiederverwendet werden können.

<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>

Für Szenarien, die keine präzise Zufälligkeit oder wiederholte Aufrufe erfordern:

• Materialisierte Ansicht:

  • Erstellen Sie eine materialisierte Ansicht, um ungefähr zufällig ausgewählte Zeilen zu speichern.
  • Aktualisieren Sie materialisierte Ansichten regelmäßig.

• TABLESAMPLE SYSTEM (n):

  • Eingeführt in PostgreSQL 9.5, TABLESAMPLE SYSTEM (n) bietet eine schnelle und ungenaue Zufallsstichprobenmethode.
  Der Parameter
  • n stellt den Prozentsatz der Tabellen dar, die abgetastet werden sollen.

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

Weitere Hinweise:

• Um die beste Leistung zu erzielen, verwenden Sie einen Index für die ID-Spalte.
• Die random()-Funktionen in PostgreSQL sind nicht kryptografisch sicher.
• Der vorgeschlagene Ansatz bietet ein hohes Maß an Zufälligkeit für die meisten praktischen Anwendungsfälle.

Das obige ist der detaillierte Inhalt vonWie wählt man zufällige Zeilen in PostgreSQL effizient aus?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!