Die Entdeckungsreise von PostgreSQL

Postgres verfügt über mehrere Indextypen und jede neue Version scheint einige neue hinzuzufügen. Jeder Indextyp ist nützlich, aber welcher Typ verwendet werden soll, hängt von (1) der Art der Daten und manchmal (2) den zugrunde liegenden Daten in der Tabelle und (3) der Art der durchgeführten Suche ab. Im folgenden Inhalt stellen wir die Indextypen vor, die Sie in Postgres verwenden können, und zeigen, wann Sie welchen Indextyp verwenden sollten. Bevor wir beginnen, finden Sie hier eine Liste der Indextypen, durch die wir Sie führen:

B-Baum
Generalisierter invertierter Index (GIN)
Generalisierter invertierter Suchbaum (GiST)
Raumpartitioniertes GiST (SP-GiST)
Blockbereichsindex (BRIN)
Hash
Beginnen wir nun mit der Indizierung.

In Postgres sind B-Tree-Indizes die am häufigsten verwendeten Indizes

Wenn Sie einen Abschluss in Informatik haben, ist die B-Tree-Indizierung möglicherweise der erste Index, den Sie lernen. B-Tree-Indizes erstellen einen Baum, der immer sein Gleichgewicht behält. Wenn es anhand des Index nach etwas sucht, durchsucht es den Baum, um den Schlüssel zu finden, und gibt die gesuchten Daten zurück. Die Verwendung eines Index ist erheblich schneller als ein sequenzieller Scan, da nur wenige Seiten gelesen werden müssen (wenn Sie nur wenige Datensätze zurückgeben), im Gegensatz zum sequenziellen Scannen von Tausenden von Datensätzen.

Wenn Sie eine standardmäßige CREATE INDEX-Anweisung ausführen, wird ein B-Tree-Index für Sie erstellt. B-Tree-Indizes sind für die meisten Datentypen wertvoll, z. B. Text, Zahlen und Zeitstempel. Wenn Sie mit der Verwendung von Indizes in Ihrer Datenbank noch nicht vertraut sind und nicht zu viele der erweiterten Funktionen von Postgres in Ihrer Datenbank verwenden, ist die Verwendung eines Standard-B-Tree-Index wahrscheinlich die beste Option.

GIN-Index für mehrwertige Spalten

Der verallgemeinerte invertierte Index, allgemein GIN genannt, eignet sich hauptsächlich für Datentypen, bei denen eine einzelne Spalte mehrere Werte enthält.

Laut Postgres-Dokumentation:

„GIN ist für die Handhabung von Situationen konzipiert, in denen der indizierte Eintrag ein zusammengesetzter Wert ist und die vom Index verarbeitete Abfrage nach einem Wert suchen muss, der im zusammengesetzten Eintrag vorkommt. Dieser Eintrag könnte beispielsweise ein Dokument sein und der Die Abfrage kann nach bestimmten Zeichen suchen, die im Dokument enthalten sind.“

Die häufigsten Datentypen in diesem Bereich sind:

hStore
Array
Reichweite
JSONB
Einer der befriedigendsten Aspekte von GIN-Indizes ist ihre Fähigkeit, in zusammengesetzten Werten gespeicherte Daten zu verstehen. Da ein GIN-Index jedoch spezifische Kenntnisse der Datenstruktur für jeden einzelnen hinzugefügten Typ erfordert, werden nicht alle Datentypen vom GIN-Index unterstützt.

GiST-Index für Zeilen mit überlappenden Werten

Der Generalized Inverted Search Tree (GiST)-Index eignet sich vor allem dann, wenn sich Ihre Daten mit anderen Datenzeilen in derselben Spalte überschneiden. GiST-Indizes lassen sich am besten nutzen, wenn Sie einen Geometriedatentyp deklarieren und wissen möchten, ob zwei Polygone einige Punkte enthalten. In einem Fall kann ein bestimmter Punkt in einer Box enthalten sein, während andere Punkte gleichzeitig nur in einem Polygon existieren. Gängige mit GiST indizierte Datentypen sind:

Geometrietyp
Texttypen, die eine Volltextsuche erfordern
Es gibt viele feste Grenzen für die Größe von GiST-Indizes, andernfalls können GiST-Indizes extrem groß werden. Aus diesem Grund sind GiST-Indizes verlustbehaftet (ungenau).

Laut offizieller Dokumentation:

„GiST-Indizes sind verlustbehaftet, was bedeutet, dass der Index falsche Übereinstimmungen erzeugen kann. Daher ist es notwendig, die echten Tabellenzeilen zu überprüfen, um falsche Übereinstimmungen zu beseitigen (PostgreSQL führt diese Aktion bei Bedarf automatisch aus)“

Das bedeutet nicht, dass Sie ein falsches Ergebnis erhalten, es bedeutet lediglich, dass Postgres einen kleinen zusätzlichen Aufwand leistet, um diese falschen Ergebnisse herauszufiltern, bevor es die Daten an Sie zurücksendet.

Besonderer Tipp: GIN- und GiST-Indizes können häufig für denselben Datentyp verwendet werden. Normalerweise hat man eine gute Leistung, nimmt aber viel Speicherplatz ein und umgekehrt. Wenn es um GIN vs. GiST geht, gibt es keine einheitliche Lösung, die in jeder Situation funktioniert, aber die oben genannten Regeln sollten für die meisten gängigen Situationen gelten.

SP-GiST-Index für größere Datenmengen

Der räumlich partitionierte GiST-Index (SP-GiST) übernimmt den räumlich partitionierten Baum von Purdue Research. SP-GiST-Indizes werden häufig verwendet, wenn Ihre Daten einen natürlichen Clusterfaktor aufweisen und kein ausgeglichener Baum sind. Telefonnummern sind ein gutes Beispiel (zumindest US-Telefonnummern). Sie haben das folgende Format:

3-stellige Vorwahl
3-stellige Vorwahlnummer (bezogen auf alte Telefonzentralen)
4-stellige Zeilennummer
Dies bedeutet, dass es einen natürlichen Clusterfaktor bei den ersten drei Ziffern des ersten Satzes gibt, gefolgt vom zweiten Satz aus drei Ziffern, und dann sind die Zahlen gleichmäßig verteilt. Allerdings gibt es in einigen Vorwahlen von Telefonnummern einen höheren Sättigungszustand als in anderen. Das Ergebnis kann ein sehr unausgeglichener Baum sein. Da es im Vorfeld einen natürlichen Aggregationsfaktor gibt und die Daten nicht gleichmäßig verteilt sind, könnten Daten wie Telefonnummern ein guter Fall für SP-GiST sein.

BRIN-Index für größere Datenmengen

Block Range Indexes (BRIN) konzentrieren sich auf bestimmte Situationen wie SP-GiST. Sie werden am besten verwendet, wenn die Daten eine natürliche Reihenfolge haben und die Datengröße oft groß ist. Wenn Sie eine Milliarde Datensätze in chronologischer Reihenfolge haben, kann BRIN nützlich sein. Wenn Sie einen großen Datensatz abfragen, der natürlich gruppiert ist, beispielsweise mehrere Postleitzahlen, kann BRIN Ihnen dabei helfen, sicherzustellen, dass ähnliche Postleitzahlen an nahe beieinander liegenden Orten auf der Festplatte gespeichert werden.

Wenn Sie eine sehr große Datenbank haben, die nach Datum oder Postleitzahl sortiert ist, können Sie mit dem BRIN-Index einige unnötige Daten sehr schnell überspringen oder ausschließen. Darüber hinaus sind BRIN-Indizes im Vergleich zur Gesamtdatengröße relativ klein, sodass BRIN-Indizes bei einem großen Datensatz eine bessere Leistung erzielen können.

Hash-Index, endlich keine Angst mehr vor einem Absturz

Hash-Indizes gibt es in Postgres schon seit Jahren. Bis zur Veröffentlichung von Postgres 10 gab es jedoch einen großen Vorbehalt hinsichtlich ihrer Verwendung: Sie waren nicht WAL-protokolliert. Das heißt, wenn Ihr Server abstürzt und Sie z. B. mit wal-g kein Failover auf einen Standby-Server oder eine Wiederherstellung aus einem Archiv durchführen können, verlieren Sie diesen Index, bis Sie ihn neu erstellen. Mit der Veröffentlichung von Postgres 10 sind sie jetzt WAL-protokolliert, sodass Sie darüber nachdenken können, sie wieder zu verwenden, aber die eigentliche Frage ist: Sollten Sie das tun?

Hash-Indizes bieten manchmal schnellere Suchvorgänge als B-Tree-Indizes und lassen sich auch schnell erstellen. Das größte Problem besteht darin, dass sie nur auf „Gleichheits“-Vergleichsoperationen beschränkt sind, sodass Sie sie nur für die Suche nach exakten Übereinstimmungen verwenden können. Dadurch sind Hash-Indizes deutlich weniger flexibel als häufig verwendete B-Tree-Indizes, und Sie sollten sie nicht als Ersatz, sondern als Index für Sonderfälle betrachten.

Welches sollten Sie verwenden? Wir haben gerade viel vorgestellt. Wenn Sie ein wenig Angst haben, ist das normal. Wenn Sie das bereits wissen, erstellt CREATE INDEX immer einen Index für Sie mithilfe eines B-Baums, und die gute Nachricht ist, dass Postgres für die meisten Datenbanken eine sehr gute oder sehr gute Leistung erbringt. :) Wenn Sie erwägen, weitere Postgres-Funktionen zu verwenden, finden Sie hier eine Cheat-Liste, wenn Sie andere Postgres-Indextypen verwenden:

B-Tree – geeignet für die meisten Datentypen und Abfragen

GIN – für JSONB/hstore/arrays
GiST – für Volltextsuche und geometrische Datentypen
SP-GiST – geeignet für große Datensätze mit natürlichen Aggregationsfaktoren, aber ungleichmäßiger Verteilung
BRIN – geeignet für wirklich große Datensätze mit sequentieller Anordnung
Hash – gut für Gleichheitsoperationen, aber normalerweise ist immer noch ein B-Tree-Index alles, was Sie brauchen.
Wenn Sie Fragen oder Feedback zu diesem Artikel haben, können Sie sich gerne unserem Slack-Kanal anschließen.

Das obige ist der detaillierte Inhalt vonDie Entdeckungsreise von PostgreSQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!