Detaillierte Erläuterung von neun Hauptindexarten in PostgreSQL

PostgreSQL bietet eine Vielzahl von Indexarten. Jede Indexart basiert auf spezifischen Datenstrukturen und Prinzipien und eignet sich für verschiedene Anwendungsszenarien. Im Folgenden werden diese neun Hauptindexarten detailliert vorgestellt.

1. B - Tree Index

Datenstruktur

Der B - Tree (ausgeglichener Mehrwegsuchbaum) ist eine selbstausgleichende Baumstruktur. Jeder Knoten darin kann mehrere Kindknoten haben (Mehrweg). Normalerweise enthält ein B - Tree-Knoten mehrere Schlüssel-Wert-Paare und Zeiger auf Kindknoten. Beispielsweise kann ein B - Tree-Knoten der Ordnung m höchstens m Kindknoten und mindestens ⌈m/2⌉ Kindknoten haben (mit Ausnahme des Wurzelknotens).

Schematische Darstellung

           +--------------------+
           |  10  |  20  |  30   |
           +--------------------+
          /        |         \
 +---------+    +---------+    +---------+
 |  1 |  5  |    | 11 | 15  |    | 21 | 25  |
 +---------+    +---------+    +---------+

Prinzip

Der B - Tree-Index ist die am häufigsten verwendete Indexart und eignet sich für die meisten Datentypen. Mit seiner ausgeglichenen Baumstruktur kann er Bereichsabfragen, Gleichheitsabfragen und Sortiervorgänge effizient durchführen. Bei der Durchführung einer Abfrage beginnt er am Wurzelknoten und sucht rekursiv nach unten entsprechend der Größenbeziehung der Schlüsselwerte, bis der Zielknoten gefunden wird oder festgestellt wird, dass das Ziel nicht existiert.

Anwendungsszenarien

• Gleichheitsabfrage (=): Wenn Sie einen bestimmten Wert finden müssen, kann der B - Tree den Knoten, der diesen Wert enthält, schnell lokalisieren. Suchen Sie beispielsweise in der Benutzertabelle nach dem Benutzer mit der Benutzer - ID 100.
• Bereichsabfrage (>, <, >=, <=): Für Abfragen, die Werte innerhalb eines bestimmten Bereichs finden müssen, kann der B - Tree seine Ordnungsmäßigkeit nutzen, um die Start- und Endknoten des Bereichs effizient zu lokalisieren, und dann diese Knoten durchlaufen, um die Ergebnisse zu erhalten. Finden Sie beispielsweise Benutzer, deren Alter zwischen 20 und 30 liegt.
• Sortiervorgang (ORDER BY): Da die Schlüsselwerte im B - Tree geordnet gespeichert sind, kann der Index direkt zum Sortieren verwendet werden, wodurch zusätzliche Sortiervorgänge an den Daten vermieden und die Sortiereffizienz verbessert wird.

Erstellungsanweisung

CREATE INDEX idx_btree ON Tabellenname (Spaltenname);

2. Hash Index

Datenstruktur

Der Hash-Index verwendet eine Hash-Tabellenstruktur. Er ordnet die Schlüsselwerte einem Slot in der Hash-Tabelle durch eine Hash-Funktion zu. Die Hash-Tabelle ist normalerweise ein Array, und jeder Slot kann ein oder mehrere Schlüssel-Wert-Paare speichern. Wenn ein Hash-Konflikt auftritt, wird normalerweise die Linked-List-Methode oder die Open-Addressing-Methode verwendet, um ihn zu behandeln.

Schematische Darstellung

+---+---+---+---+
| 0 | 1 | 2 | 3 |
+---+---+---+---+
|   |   | 10->20|
|   |   |       |
+---+---+---+---+

Prinzip

Der Hash-Index konvertiert die Schlüsselwerte durch die Hash-Funktion in einen Hash-Code mit fester Länge und findet dann den entsprechenden Slot gemäß dem Hash-Code. Er eignet sich nur für Gleichheitsabfragen, da die Hash-Funktion die Reihenfolge der Schlüsselwerte nicht garantieren kann, daher unterstützt er keine Bereichsabfragen und Sortiervorgänge.

Anwendungsszenarien

• Gleichheitsabfrage (=): Wenn Sie einen bestimmten Wert genau abgleichen müssen, kann der Hash-Index den Slot schnell über die Hash-Funktion berechnen und so die Zieldaten lokalisieren. Suchen Sie beispielsweise in der Cache-Tabelle nach dem Wert, der einem bestimmten Cache-Schlüssel entspricht.

Erstellungsanweisung

CREATE INDEX idx_hash ON Tabellenname USING hash (Spaltenname);

3. GiST Index

Datenstruktur

GiST (Generalized Search Tree) ist eine allgemeine Suchbaumstruktur. Seine Knoten können verschiedene Arten von Daten und Operatorklassen speichern. Jeder Knoten enthält normalerweise mehrere Schlüssel-Wert-Paare und Zeiger auf Kindknoten und kann angepasste Aufteilungs- und Zusammenführungsvorgänge gemäß verschiedenen Datentypen und Operatoren durchführen.

Schematische Darstellung

           +--------------------+
           |  Rect1 |  Rect2  |   |
           +--------------------+
          /        |         \
 +---------+    +---------+    +---------+
 | Point1 |    | Point2  |    | Point3  |
 +---------+    +---------+    +---------+

Prinzip

Der GiST-Index ist eine allgemeine Indexstruktur, die mehrere Datentypen und Operatorklassen unterstützt. Er organisiert die Daten in einer Baumstruktur, indem er den Datenraum rekursiv unterteilt, wodurch effiziente Abfragen erreicht werden. Bei der Durchführung einer Abfrage beginnt er gemäß den Abfragebedingungen und Operatorklassen am Wurzelknoten und sucht rekursiv nach unten, bis die Knoten gefunden werden, die die Bedingungen erfüllen.

Anwendungsszenarien

• Volltextsuche: Textdaten können tokenisiert werden, und dann kann der GiST-Index verwendet werden, um diese Token zu speichern und abzufragen. Führen Sie beispielsweise eine Stichwortsuche in der Artikeltabelle durch.
• Geometrische Datenabfrage: Für geometrische Daten wie Punkte, Linien und Ebenen kann der GiST-Index räumliche Abfragen wie Enthaltensein und Überschneidung effizient verarbeiten. Finden Sie beispielsweise alle Points of Interest innerhalb eines bestimmten Bereichs in einer Kartenanwendung.
• Mehrdimensionale Datenabfrage: Er eignet sich zur Verarbeitung mehrdimensionaler Daten wie mehrdimensionaler Arrays und Vektoren. Fragen Sie beispielsweise hochdimensionale Merkmalsvektoren in einer Machine-Learning-Anwendung ab.

Erstellungsanweisung

CREATE INDEX idx_gist ON Tabellenname USING gist (Spaltenname);

4. SP - GiST Index

Datenstruktur

SP - GiST (Space - Partitioned GiST) ist ein raumpartitionierter GiST-Index. Er organisiert die Daten in einer Baumstruktur, indem er den Datenraum partitioniert. Jeder Knoten enthält mehrere Partitionen und Zeiger auf Kindknoten, und die Partitionen können gemäß der Verteilung der Daten dynamisch angepasst werden.

Schematische Darstellung

           +--------------------+
           |  Part1 |  Part2  |   |
           +--------------------+
          /        |         \
 +---------+    +---------+    +---------+
 | SubP1   |    | SubP2   |    | SubP3   |
 +---------+    +---------+    +---------+

Prinzip

Der SP - GiST-Index eignet sich zur Verarbeitung unausgeglichener Datenverteilung. Er partitioniert den Datenraum und verteilt die Daten gleichmäßig auf verschiedene Knoten, wodurch die Abfrageeffizienz verbessert wird. Bei der Durchführung einer Abfrage beginnt er gemäß den Abfragebedingungen und Partitionsinformationen am Wurzelknoten und sucht rekursiv nach unten, bis die Knoten gefunden werden, die die Bedingungen erfüllen.

Anwendungsszenarien

• Mehrdimensionale Datenabfrage: Für mehrdimensionale Daten wie mehrdimensionale Arrays und Vektoren kann der SP - GiST-Index die Daten gemäß der Verteilung der Daten partitionieren, wodurch die Abfrageeffizienz verbessert wird. Fragen Sie beispielsweise mehrdimensionale geografische Daten in einem geografischen Informationssystem ab.
• Sparse-Data-Abfrage: Wenn es eine große Menge an Sparse Data im Datensatz gibt, kann der SP - GiST-Index diese Daten effektiv verarbeiten und die Leistungsprobleme traditioneller Indizes bei der Verarbeitung von Sparse Data vermeiden.

Erstellungsanweisung

CREATE INDEX idx_spgist ON Tabellenname USING spgist (Spaltenname);

5. GIN Index

Datenstruktur

GIN (Generalized Inverted Index) ist ein invertierter Index. Er zeichnet die Vorkommenspositionen jedes Schlüsselwertes in einer Liste auf. Insbesondere verwaltet er eine Zuordnung von Schlüsselwerten zu einer Liste von Dokument - IDs, wobei die Dokument - IDs die Datensätze darstellen, die diesen Schlüsselwert enthalten.

Schematische Darstellung

+------+-----------------+
| Key  | Document IDs    |
+------+-----------------+
|  A   |  1, 3, 5        |
|  B   |  2, 4           |
+------+-----------------+

Prinzip

Der GIN-Index eignet sich für mehrwertige Spalten und die Volltextsuche. Er erreicht effiziente Abfragen, indem er die Vorkommenspositionen jedes Schlüsselwertes aufzeichnet. Bei der Durchführung einer Abfrage findet er gemäß den Abfragebedingungen den entsprechenden Schlüsselwert und erhält dann die Liste der Dokument - IDs, die diesen Schlüsselwert enthalten, wodurch die Zieldaten lokalisiert werden.

Anwendungsszenarien

• Array-Abfrage: Wenn eine bestimmte Spalte in der Tabelle vom Typ Array ist, kann der GIN-Index Arrays, die bestimmte Elemente enthalten, effizient abfragen. Finden Sie beispielsweise Produkte, die bestimmte Tags enthalten, in der Produkttabelle.
• JSON-Datenabfrage: Für JSON-Daten kann der GIN-Index die Schlüssel-Wert-Paare in JSON indizieren, wodurch effiziente JSON-Datenabfragen erreicht werden. Finden Sie beispielsweise Benutzer mit bestimmten Attributen in der Benutzertabelle.
• Volltextsuche: Der GIN-Index kann Textdaten tokenisieren und dann die Vorkommenspositionen jedes Tokens aufzeichnen, wodurch eine effiziente Volltextsuche erreicht wird. Führen Sie beispielsweise eine Stichwortsuche in der Nachrichtentabelle durch.

Erstellungsanweisung

CREATE INDEX idx_gin ON Tabellenname USING gin (Spaltenname);

6. BRIN Index

Datenstruktur

BRIN (Block Range INdex) ist ein Blockbereichsindex. Er reduziert die Größe des Index, indem er die Minimal- und Maximalwerte jedes Datenblocks speichert. Die Indexdatei besteht aus einer Reihe von Blockbereichseinträgen, und jeder Eintrag enthält die Startblocknummer, die Endblocknummer, den Minimalwert und den Maximalwert eines Datenblocks.

Schematische Darstellung

+--------------------+
| Block Range | Min | Max |
+--------------------+
|  0 - 10     |  1  | 10  |
| 11 - 20     | 11  | 20  |
+--------------------+

Prinzip

Der BRIN-Index eignet sich für sehr große Tabellen. Durch das Speichern der Minimal- und Maximalwerte jedes Blockbereichs kann er bei der Durchführung einer Abfrage schnell feststellen, ob ein Datenblock Daten enthalten kann, die die Abfragebedingungen erfüllen, wodurch die Anzahl der Datenblöcke, die gescannt werden müssen, reduziert wird. Da er jedoch nur die Randinformationen des Blockbereichs aufzeichnet, ist die Abfrageleistung relativ gering.

Anwendungsszenarien

• Sehr große Tabellen: Wenn die Tabelle eine große Datenmenge enthält, kann die Verwendung des BRIN-Index den Speicherplatz des Index erheblich reduzieren und die Wartungseffizienz des Index verbessern. Speichern Sie beispielsweise eine große Anzahl von Protokolldatensätzen in der Protokolltabelle.
• Daten, die in der Reihenfolge eingefügt wurden: Wenn die Daten in der Reihenfolge eingefügt werden, haben die Daten in benachbarten Datenblöcken eine bestimmte Reihenfolge, und der BRIN-Index kann eine bessere Rolle spielen. Beispielsweise können in Zeitreihendaten Daten, die in chronologischer Reihenfolge eingefügt wurden, mithilfe des BRIN-Index effizient abgefragt werden.

Erstellungsanweisung

CREATE INDEX idx_brin ON Tabellenname USING brin (Spaltenname);

7. Bitmap Index

Datenstruktur

Der Bitmap-Index verwendet eine Bitmap-Struktur. Für jeden unterschiedlichen Schlüsselwert verwaltet er eine Bitmap, und jedes Bit der Bitmap entspricht einem Datensatz. Wenn ein Bit 1 ist, bedeutet dies, dass der entsprechende Datensatz diesen Schlüsselwert enthält; wenn es 0 ist, bedeutet dies, dass er ihn nicht enthält.

Schematische Darstellung

+------+--------------------+
| Key  | Bitmap              |
+------+--------------------+
|  A   |  1 0 1 0 1          |
|  B   |  0 1 0 1 0          |
+------+--------------------+

Prinzip

Der Bitmap-Index eignet sich für Spalten mit geringer Kardinalität (d. h. es gibt nur wenige unterschiedliche Werte in der Spalte). Er realisiert effiziente kombinierte Abfragen mehrerer Bedingungen durch Bitoperationen der Bitmap. Bei der Durchführung einer Abfrage findet er gemäß den Abfragebedingungen die entsprechende Bitmap, führt dann Bitoperationen durch, um die Bitmap der Datensätze zu erhalten, die die Bedingungen erfüllen, und lokalisiert schließlich die Zieldaten gemäß der Bitmap.

Anwendungsszenarien

• Spalten mit geringer Kardinalität: Wenn es nur wenige unterschiedliche Werte in der Spalte gibt, kann die Verwendung des Bitmap-Index den Speicherplatz des Index erheblich reduzieren und die Abfrageeffizienz verbessern. In der Spalte "Geschlecht" gibt es beispielsweise nur zwei Werte: "männlich" und "weiblich".
• Kombinierte Abfragen mehrerer Bedingungen: Der Bitmap-Index ist sehr effektiv für kombinierte Abfragen mehrerer Bedingungen, da er die Bitmaps mehrerer Bedingungen schnell durch Bitoperationen zusammenführen kann, um das endgültige Abfrageergebnis zu erhalten. Finden Sie beispielsweise in der Benutzertabelle Benutzer, die weiblich und zwischen 20 und 30 Jahre alt sind.

Erstellungsanweisung

-- PostgreSQL selbst hat keine direkte Syntax zum Erstellen eines Bitmap-Index, aber ein ähnlicher Effekt kann durch die Kombination von B - Tree-Indizes erzielt werden
CREATE INDEX idx_bitmap ON Tabellenname (Spaltenname);

8. Partial Index

Datenstruktur

Die Datenstruktur des Partial-Index ist die gleiche wie die eines gemeinsamen Index, mit der Ausnahme, dass er nur einen Index für einen Teil der Daten in der Tabelle erstellt. Er enthält nur die Daten, die die Bedingung erfüllen, indem er einen bedingten Ausdruck hinzufügt.

Schematische Darstellung

Originaltabelle:
+----+-------+
| ID | Value |
+----+-------+
| 1  |  A    |
| 2  |  B    |
| 3  |  A    |
+----+-------+

Partial Index (Value = 'A'):
+----+-------+
| ID | Value |
+----+-------+
| 1  |  A    |
| 3  |  A    |
+----+-------+

Prinzip

Der Partial-Index reduziert die Größe und die Wartungskosten des Index, indem er nur einen Index für einen Teil der Daten erstellt, und verbessert die Effizienz des Index. Bei der Durchführung einer Abfrage werden nur die Daten indiziert, die die Bedingungen erfüllen, wodurch unnötige Indexscans reduziert werden.

Anwendungsszenarien

• Nur einen Teil der Daten indizieren: Wenn Sie nur einen Teil der Daten in der Tabelle indizieren müssen, können Sie den Partial-Index verwenden. Indizieren Sie beispielsweise in der Benutzertabelle nur aktive Benutzer.
• Indexeffizienz verbessern: Durch die Reduzierung der Datenmenge im Index kann der Partial-Index die Abfrage- und Wartungseffizienz des Index verbessern. Indizieren Sie beispielsweise in der Protokolltabelle nur die Protokolle des letzten Monats.

Erstellungsanweisung

CREATE INDEX idx_partial ON Tabellenname (Spaltenname) WHERE Bedingung;

9. Unique Index

Datenstruktur

Die Datenstruktur des Unique-Index ist die gleiche wie die eines gemeinsamen Index und verwendet normalerweise einen B - Tree oder eine andere geeignete Indexstruktur. Sein Merkmal ist es, sicherzustellen, dass alle Werte in der Indexspalte eindeutig sind.

Schematische Darstellung

+----+-------+
| ID | Value |
+----+-------+
| 1  |  A    |
| 2  |  B    |
| 3  |  C    |
+----+-------+

Prinzip

Der Unique-Index prüft, ob die Werte in der Indexspalte eindeutig sind, wenn Daten eingefügt oder aktualisiert werden, um sicherzustellen, dass alle Werte in der Indexspalte eindeutig sind. Wenn die Eindeutigkeitsbeschränkung verletzt wird, wird der Vorgang abgelehnt.

Anwendungsszenarien

• Primärschlüsselbeschränkung: Der Unique-Index kann verwendet werden, um die Primärschlüsselbeschränkung zu implementieren, um sicherzustellen, dass jede Zeile in der Tabelle eine eindeutige Kennung hat. Verwenden Sie beispielsweise die Benutzer - ID als Primärschlüssel in der Benutzertabelle.
• Eindeutigkeitsbeschränkung: Wenn Sie sicherstellen müssen, dass die Werte in einer bestimmten Spalte eindeutig sind, es sich aber nicht um den Primärschlüssel handelt, können Sie den Unique-Index verwenden. Verwenden Sie beispielsweise die E-Mail-Adresse als Eindeutigkeitsbeschränkung in der E-Mail-Tabelle.

Erstellungsanweisung

CREATE UNIQUE INDEX idx_unique ON Tabellenname (Spaltenname);

Leapcell: Das Beste vom Serverlosen Webhosting

Schließlich empfehle ich eine Plattform, die sich am besten für die Bereitstellung von Webservices eignet: Leapcell

🚀 Mit Ihrer Lieblingssprache bauen

Entwickeln Sie mühelos in JavaScript, Python, Go oder Rust.

🌍 Unbegrenzte Projekte kostenlos bereitstellen

Zahlen Sie nur für das, was Sie nutzen – keine Anfragen, keine Gebühren.

⚡ Pay-as-You-Go, keine versteckten Kosten

Keine Leerlaufgebühren, nur nahtlose Skalierbarkeit.

📖 Erkunden Sie unsere Dokumentation

🔹 Folgen Sie uns auf Twitter: @LeapcellHQ