Wie kann ich zufällige Zeilen in PostgreSQL effizient auswählen?

Effiziente zufällige Zeilenauswahlmethode für PostgreSQL

PostgreSQL bietet eine Vielzahl von Methoden zur effizienten Auswahl zufälliger Zeilen.

Methode 1: Random() und Limit-Klausel verwenden

Diese Methode verwendet die Funktion random() und die Klausel LIMIT:

SELECT *
FROM table
ORDER BY random()
LIMIT 1000;

Bei großen Tabellen kann diese Methode jedoch langsamer sein, da sie einen vollständigen Tabellenscan erfordert.

Methode 2: Indexbasierte Methode

Diese Methode verwendet den Primärschlüsselindex, um die Abfrage zu optimieren:

WITH params AS (
   SELECT 1       AS min_id,          -- 最小ID (大于等于当前最小ID)
        , 5100000 AS id_span          -- 四舍五入 (max_id - min_id + 缓冲)
)
SELECT *
FROM  (
   SELECT p.min_id + trunc(random() * p.id_span)::integer AS id
   FROM   params p
        , generate_series(1, 1100) g  -- 1000 + 缓冲
   GROUP  BY 1                        -- 去除重复项
) r
JOIN   table USING (id)
LIMIT  1000;                          -- 去除多余项

Diese Methode ist schneller als Methode eins, da sie einen Indexscan anstelle eines vollständigen Tabellenscans verwendet.

Methode 3: Rekursiven CTE verwenden

Diese Methode verwendet einen rekursiven gemeinsamen Tabellenausdruck (CTE), um fehlende Werte in der ID-Spalte zu verarbeiten:

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   table b USING (id)             -- 去除缺失值

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

Methode 4: TABLESAMPLE SYSTEM (n) verwenden

PostgreSQL 9.5 führte die TABLESAMPLE SYSTEM (n)-Syntax ein, wobei n ein Prozentsatz zwischen 0 und 100 ist:

SELECT *
FROM big
TABLESAMPLE SYSTEM ((1000 * 100) / 5100000.0);

Diese Methode ist schnell, liefert aber aufgrund von Clustering-Effekten möglicherweise keine wirklich zufälligen Stichproben.

Vergleich und Vorschläge

Wenn in der Tabelle nur wenige Werte für die ID-Spalte fehlen und der Primärschlüsselindex vorhanden ist, ist Methode zwei (indexbasierte Methode) die beste Wahl, da sie die beste Geschwindigkeit bietet und Genauigkeit Sex.

Für Tabellen mit vielen fehlenden Werten ziehen Sie bitte Methode 3 (rekursiver CTE) in Betracht, mit der fehlende Werte effektiv behandelt werden können.

Methode eins (random() und limit) hat eine geringere Leistung und sollte bei kleineren Tabellen verwendet werden.

Methode 4(TABLESAMPLE SYSTEM) ist schnell, aber nicht so genau wie andere Methoden. Es kann verwendet werden, um schnelle Schätzungen für große Tabellen vorzunehmen.

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