Wie kann ich zufällige Zeilen aus einer großen PostgreSQL-Tabelle effizient auswählen?

Das zufällige Auswählen von Zeilen aus großen Datenbanken wie PostgreSQL kann eine leistungsintensive Aufgabe sein. In diesem Artikel werden zwei gängige Methoden zur effizienten Erreichung dieses Ziels untersucht und deren Vor- und Nachteile erörtert.

Methode 1: Nach Zufallswert filtern

<code class="language-sql">select * from table where random() < 0.01;</code>

Diese Methode sortiert die Zeilen zufällig und filtert dann basierend auf einem Schwellenwert. Es erfordert jedoch einen vollständigen Tabellenscan und kann bei großen Datensätzen langsam sein.

Methode 2: Nach Zufallswerten sortieren und die Ergebnisse eingrenzen

<code class="language-sql">select * from table order by random() limit 1000;</code>

Diese Methode sortiert die Zeilen zufällig und wählt die obersten n Zeilen aus. Sie bietet eine bessere Leistung als die erste Methode, weist jedoch eine Einschränkung auf: Sie kann möglicherweise keine zufällige Teilmenge auswählen, wenn die Zeilengruppe zu viele Zeilen enthält.

Optimierungslösungen für große Datenmengen

Für Tabellen mit einer großen Anzahl von Zeilen (z. B. 500 Millionen Zeilen in Ihrem Beispiel) bietet der folgende Ansatz eine optimierte Lösung:

<code class="language-sql">WITH params AS (
   SELECT 1       AS min_id,           -- 最小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>

Diese Abfrage nutzt den Index der ID-Spalte für einen effizienten Abruf. Es generiert eine Reihe von Zufallszahlen innerhalb des ID-Raums, um sicherzustellen, dass die IDs eindeutig sind, und verknüpft die Daten mit der Haupttabelle, um die erforderliche Anzahl von Zeilen auszuwählen.

Andere Überlegungen

Grenzabfrage:
Es ist wichtig, dass die Tabellen-ID-Spalte relativ wenige Lücken aufweist, um zu vermeiden, dass bei der Zufallszahlengenerierung große Puffer erforderlich sind.

Materialisierte Ansicht:
Wenn Sie wiederholt auf zufällige Daten zugreifen müssen, sollten Sie die Erstellung materialisierter Ansichten in Betracht ziehen, um die Leistung zu verbessern.

TABLESAMPLE SYSTEM für PostgreSQL 9.5:
Diese in PostgreSQL 9.5 eingeführte Optimierungstechnik ermöglicht ein schnelles Sampling eines bestimmten Prozentsatzes von Zeilen.

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