Wie wählt man effizient zufällige Zeilen aus einer großen PostgreSQL-Tabelle aus?

Wählen Sie zufällige Zeilen aus einer großen PostgreSQL-Tabelle aus

Bei der Arbeit mit großen Datensätzen kann die Auswahl zufälliger Zeilen eine rechenintensive Aufgabe sein. In diesem Artikel werden verschiedene Methoden zum Abrufen zufälliger Zeilen aus einer Tabelle mit etwa 500 Millionen Zeilen untersucht und deren Leistung und Genauigkeit erläutert.

Methode 1: Verwenden Sie RANDOM() und LIMIT

Die erste Methode besteht darin, die Funktion RANDOM() zu verwenden, um Zufallszahlen zu generieren, und dann die LIMIT-Klausel zu verwenden, um die Ergebnisse zu filtern, um die erforderliche Anzahl von Zeilen zu erhalten.

<code class="language-sql">SELECT * FROM table WHERE RANDOM() < 0.000002 LIMIT 1000;</code>

Dieser Ansatz hat den Vorteil, dass er einfach zu implementieren ist, kann jedoch bei großen Tabellen ineffizient sein. Aufgrund der LIMIT-Klausel muss die Datenbank alle Zeilen der Tabelle scannen, um zufällige Zeilen auszuwählen und den Rest zu verwerfen.

Methode 2: Verwenden Sie ORDER BY RANDOM() und LIMIT

Ein anderer Ansatz besteht darin, die Zeilen zunächst mit der Funktion RANDOM() zu sortieren und dann die LIMIT-Klausel zu verwenden, um zufällige Zeilen zu erhalten.

<code class="language-sql">SELECT * FROM table ORDER BY RANDOM() LIMIT 1000;</code>

Diese Methode ähnelt der ersten Methode, aber die Sortierung garantiert eine effizientere Auswahl zufälliger Zeilen. Dadurch wird die Anzahl der erforderlichen Scans reduziert, was es zu einer besseren Wahl für große Tabellen macht. Allerdings ist es immer noch nicht die beste Wahl für Tabellen mit extrem vielen Zeilen.

Effizienter Ansatz: Verwenden Sie numerische ID-Spalten und Indizes

Für Tabellen mit numerischen ID-Spalten und weniger Lücken kann ein effizienterer Ansatz verwendet werden. Dabei werden Zufallszahlen innerhalb eines ID-Bereichs generiert und zur Verknüpfung mit der Tabelle verwendet.

<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   table USING (id)
LIMIT  1000;</code>

Dieser Ansatz nutzt den Indexzugriff, um die Anzahl der erforderlichen Scans erheblich zu reduzieren. Es ist ideal für Tabellen mit einer großen Anzahl von Zeilen und wenigen Lücken in der ID-Spalte.

Überlegungen und Empfehlungen

Die beste Methode zur Auswahl zufälliger Zeilen hängt von den spezifischen Tabelleneigenschaften und Leistungsanforderungen ab. Für kleine Tabellen können die Methoden RANDOM() oder ORDER BY RANDOM() ausreichend sein. Für große Tabellen mit numerischen ID-Spalten und wenigen Lücken wird jedoch empfohlen, die oben genannte Optimierungsmethode zu verwenden, um die beste Leistung zu erzielen.

Es ist zu beachten, dass aufgrund der Natur der Erzeugung von Pseudozufallszahlen in Computern keine dieser Methoden echte Zufälligkeit garantieren kann. Sie bieten jedoch eine praktische Möglichkeit, mit angemessener Effizienz und Genauigkeit eine Zufallsstichprobe von Zeilen aus einer großen Tabelle zu erhalten.

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