Wie kann ich gruppenweise maximale Abfragen in MySQL und PostgreSQL optimieren?

Optimierung der gruppenweisen Maximalabfrage

Bei der bereitgestellten Abfrage, die darauf abzielt, Datensätze mit maximalen IDs gruppiert nach option_id abzurufen, treten Leistungsprobleme auf, da alle gescannt werden Reihen. Um dieses Problem zu beheben, sollten Sie die Verwendung eines programmatischen Index in Betracht ziehen, der maximale ID-Werte für jede option_id speichert. Dieser Ansatz ermöglicht das effiziente Abrufen von Maxima, indem nur der Index gescannt wird.

Herkömmliche Ansätze wie die Indizierung (option_id, id) oder (option_id, id DESC) optimieren die Abfrage nicht ausreichend.

MySQL-Optimierung

MySQL 5.5 enthält hierfür eine erweiterte Optimierung Abfrage. Durch die Indizierung (option_id, id) nutzt MySQL den Index für Gruppierungsvorgänge und vermeidet die Notwendigkeit sequenzieller Scans.

Postgres-Optimierung mit Referenztabelle

In Postgres Erstellen Sie eine Referenztabelle, Optionen, mit eindeutigen Options-IDs, die unterschiedlichen Options-IDs in den Datensätzen entsprechen Tabelle.

CREATE TABLE options ( option_id INT PRIMARY KEY, option TEXT UNIQUE NOT NULL );

INSERT INTO options SELECT DISTINCT option_id, 'option' || option_id FROM records;

Verwenden Sie eine korrelierte Unterabfrage, um maximale IDs aus Datensätzen für jede option_id abzurufen:

SELECT option_id, (SELECT MAX(id) FROM records WHERE option_id = o.option_id) AS max_id
FROM options o
ORDER BY 1;

Dieser Ansatz ermöglicht Nur-Index-Scans und minimiert den Zeilenzugriff in Datensätzen. Der ideale Index für diese Abfrage:

CREATE INDEX ON records (option_id, id DESC NULLS LAST);

Postgres-Optimierung mit LATERAL-Join (Postgres 9.3)

Alternativ können Sie in Postgres 9.3 und höher einen LATERAL-Join verwenden Erzielen Sie eine ähnliche Optimierung:

SELECT *
FROM records
LATERAL (
  SELECT MAX(id) OVER (PARTITION BY option_id) AS max_id
  FROM records
) AS m
WHERE records.id = m.max_id;

Dieser Ansatz nutzt auch nur den Index sucht nach effizienter Abfrageausführung.

Das obige ist der detaillierte Inhalt vonWie kann ich gruppenweise maximale Abfragen in MySQL und PostgreSQL optimieren?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!