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

PostgreSQL-Methode zur zufälligen Zeilenauswahl

Herkömmliche Methoden zur zufälligen Zeilenauswahl sind ineffizient und langsam, wenn es um große Tabellen mit Millionen oder sogar Milliarden von Datensätzen geht. Zwei gängige Methoden sind:

• Verwenden Sie random(), um zu filtern:

    select * from table where random() < 0.001;

• Verwenden Sie order by random() und limit:

    select * from table order by random() limit 1000;

Da jedoch ein vollständiger Tabellenscan oder eine vollständige Tabellensortierung erforderlich ist, sind diese Methoden nicht die beste Wahl für Tabellen mit einer großen Anzahl von Zeilen und führen zu Leistungsengpässen.

Optimierungsmethoden für große Tabellen

Für die folgenden Tabellentypen sollten Sie die folgende Optimierungsmethode in Betracht ziehen, die deutlich schneller ist:

• Numerische ID-Spalten mit kleinen oder mittleren Lücken (indiziert für schnellere Suchvorgänge)
• Keine oder nur minimale Schreibvorgänge während der Auswahl

Abfrage:

WITH params AS (
  SELECT 1 AS min_id, -- 可选：自定义最小ID起始值
       5100000 AS id_span -- 近似ID范围（最大ID - 最小ID + 缓冲）
)
SELECT *
FROM (
  SELECT DISTINCT 1 + trunc(random() * p.id_span)::integer AS id
  FROM params p, generate_series(1, 1100) g
  GROUP BY 1
) r
INNER JOIN big ON r.id = big.id
LIMIT 1000;

So funktioniert es:

• ID-Bereichsschätzung:

  • Wenn Sie nicht genau wissen, fragen Sie die Tabelle ab, um die minimale, maximale und Gesamtspanne (max. - min.) der ID-Spalte abzuschätzen.

• Zufällige ID-Generierung:

  • Generieren Sie einen anderen Satz Zufallszahlen innerhalb des geschätzten ID-Bereichs.

• Redundanz und Duplikatbeseitigung:

  • Gruppieren Sie die generierten Zahlen, um Duplikate zu entfernen und so die Möglichkeit zu verringern, fehlende oder bereits ausgewählte Zeilen auszuwählen.

• Tabellenverknüpfungen und Einschränkungen:

  • Verknüpfen Sie die Zufallszahlen mit der tatsächlichen Tabelle mithilfe der ID-Spalte (muss indiziert sein). Dieser effiziente Join ruft die entsprechenden Daten für die ausgewählte Zeile ab.
  • Zuletzt wenden Sie ein Limit an, um die erforderliche Anzahl an Zeilen abzurufen.

Warum es schnell ist:

• Minimale Indexnutzung:

  • Die Abfrage führt nur einen Indexscan für die ID-Spalte durch, was viel schneller ist als ein vollständiger Tabellenscan oder Sortiervorgang.

• Optimierte Zufallszahlengenerierung:

  • Die generierten Zufallszahlen werden über den geschätzten ID-Bereich verteilt, wodurch die Möglichkeit fehlender oder überlappender Zeilen minimiert wird.

• Redundanz und Duplikatbeseitigung:

  • Durch die Gruppierung der generierten Zahlen wird sichergestellt, dass nur unterschiedliche Zeilen ausgewählt werden, wodurch die Notwendigkeit einer zusätzlichen Filterung oder Verknüpfung zur Eliminierung von Duplikaten verringert wird.

Weitere Optionen:

• Rekursiver CTE zur Behandlung von Lücken:

  • Fügen Sie für Tabellen mit Lücken in der ID-Sequenz einen zusätzlichen CTE hinzu, um diese Lücken zu schließen.

• Funktions-Wrapper zur Wiederverwendung:

  • Definieren Sie eine Funktion, die Grenzwert und Lückenprozentsatz als Parameter verwendet, was eine einfache Konfiguration und Wiederverwendung mit verschiedenen Tabellen ermöglicht.

• Universelle Funktionen für jeden Tisch:

  • Erstellen Sie eine generische Funktion, die jede Tabelle mit ganzzahligen Spalten als Parameter akzeptiert.

• Ansichten materialisieren, um die Geschwindigkeit zu erhöhen:

  • Erwägen Sie die Erstellung einer materialisierten Ansicht basierend auf einer optimierten Abfrage, um (quasi) zufällig ausgewählte Zeilen schneller abzurufen.

• TABLE SAMPLE in PostgreSQL 9.5:

  • Nutzen Sie die „TABLE SAMPLE SYSTEM“-Funktion von PostgreSQL, um eine schnellere, aber weniger zufällige Zeilenstichprobenmethode zu implementieren und sicherzustellen, dass eine genaue Anzahl von Zeilen zurückgegeben wird. Beachten Sie jedoch, dass die Stichprobe aufgrund von Clustereffekten möglicherweise nicht völlig zufällig ist.

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