PostgreSQL-Index
Ein Index ist eine spezielle Tabellenabfrage, die den Datenabruf der Suchmaschine beschleunigt. Einfach gesagt ist ein Index ein Zeiger auf Daten in einer Tabelle. Ein Index in einer Datenbank ist dem Index eines Buches sehr ähnlich.
Nehmen Sie die Indexseiten eines chinesischen Wörterbuchs als Analogie. Wir können die benötigten Schriftzeichen schnell über Verzeichnisse (Indizes) finden, die nach Pinyin, Strichzahl, Radikalen und so weiter sortiert sind.
Indizes beschleunigen SELECT-Abfragen und WHERE-Klauseln, verlangsamen jedoch die Dateneingabe bei UPDATE- und INSERT-Anweisungen. Indizes können erstellt oder gelöscht werden, sie beeinflussen jedoch nicht die Daten.
Die CREATE INDEX-Anweisung wird verwendet, um einen Index zu erstellen. Sie ermöglicht es Ihnen, den Index zu benennen, die Tabelle und die Spalte oder Spalten anzugeben, die indiziert werden sollen, und anzugeben, ob der Index aufsteigend oder absteigend sortiert ist.
Indizes können auch eindeutig sein, ähnlich der UNIQUE-Einschränkung, wodurch doppelte Einträge in einer Spalte oder einer Kombination von Spalten verhindert werden.
CREATE INDEX-Befehl
Die Syntax für CREATE INDEX (Erstellen eines Index) lautet wie folgt:
CREATE INDEX index_name ON table_name;
Index-Typen
Einspaltiger Index
Ein einspaltiger Index ist ein Index, der nur auf Basis einer Spalte einer Tabelle erstellt wird. Die grundlegende Syntax lautet wie folgt:
CREATE INDEX index_name ON table_name (column_name);
Zusammengesetzter Index
Ein zusammengesetzter Index ist ein Index, der auf Basis mehrerer Spalten einer Tabelle erstellt wird. Die grundlegende Syntax lautet wie folgt:
CREATE INDEX index_name ON table_name (column1_name, column2_name);
Ob es sich um einen Einspaltenindex oder einen zusammengesetzten Index handelt, der Index muss auf Spalten liegen, die in den Filterbedingungen der WHERE-Klausel sehr häufig verwendet werden.
Wenn nur eine Spalte verwendet wird, wählen Sie einen Einspaltenindex; wenn mehrere Spalten verwendet werden, verwenden Sie einen zusammengesetzten Index.
Eindeutiger Index
Die Verwendung eines eindeutigen Index dient nicht nur der Leistung, sondern auch der Datenintegrität. Ein eindeutiger Index lässt nicht zu, dass doppelte Werte in die Tabelle eingefügt werden. Die grundlegende Syntax lautet wie folgt:
CREATE UNIQUE INDEX index_name on table_name (column_name);
Partieller Index
Ein partieller Index ist ein Index, der auf einer Teilmenge einer Tabelle aufgebaut ist; die Teilmenge wird durch einen Bedingungsausdruck definiert. Der Index enthält nur Zeilen, die die Bedingung erfüllen. Die grundlegende Syntax lautet wie folgt:
CREATE INDEX index_name ON table_name(column_list) WHERE condition;
Hierbei ist index_name der Name des Index, den Sie erstellen möchten, table_name der Name der Tabelle, die die Spalten enthält, die Sie indizieren möchten, column_list die Liste der Spalten, die Sie indizieren möchten, und condition ein boolescher Ausdruck, der definiert, welche Zeilen in den Index aufgenommen werden.
Impliziter Index
In PostgreSQL ist ein impliziter Index ein Index, der vom Datenbankserver automatisch erstellt wird, wenn ein Objekt erstellt wird. Solche Indizes werden typischerweise automatisch für Primärschlüssel-Constraints und Eindeutigkeits-Constraints erstellt. Wenn Sie beim Erstellen einer Tabelle eine Spalte als Primärschlüssel, als Eindeutigkeits-Constraint oder als Fremdschlüssel deklarieren, erstellt PostgreSQL automatisch einen impliziten Index für diese Spalte. Der Vorteil hiervon ist, dass es die Indexverwaltung vereinfacht und die Datenbankleistung verbessert.
Zum Beispiel: Wenn Sie beim Erstellen einer Tabelle namens "users" eine Spalte namens "userid" als Primärschlüssel deklarieren, erstellt PostgreSQL automatisch einen impliziten Index für die Spalte "userid". Das bedeutet, dass die Datenbank beim Einfügen neuer Datensätze automatisch einen eindeutigen Indexwert für die Spalte "userid" generiert.
Die Erstellung und Verwaltung impliziter Indizes werden automatisch von PostgreSQL übernommen, und Benutzer müssen nicht manuell eingreifen, was die Datenbankverwaltung einfacher und effizienter macht.
Beispiel
Das folgende Beispiel erstellt einen Index für die Spalte SALARY der Tabelle COMPANY:
# CREATE INDEX salary_index ON COMPANY (salary);
Verwenden Sie nun den\d companyBefehl, um alle Indizes der Tabelle COMPANY aufzulisten:
# \d company
Die Ergebnisse sind wie folgt. company_pkey ist ein impliziter Index, der beim Erstellen der Tabelle angelegt wurde:
exampledb=# \d company
Table "public.company"
Column | Type | Collation | Nullable | Default
---------+---------------+-----------+----------+---------
id | integer | | not null |
name | text | | not null |
age | integer | | not null |
address | character(50) | | |
salary | real | | |
Indexes:
"company_pkey" PRIMARY KEY, btree (id)
"salary_index" btree (salary)
Sie können den\diBefehl verwenden, um alle Indizes in der Datenbank aufzulisten:
exampledb=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+-----------------+-------+----------+------------
public | company_pkey | index | postgres | company
public | department_pkey | index | postgres | department
public | salary_index | index | postgres | company
(3 rows)
DROP INDEX (Löschen eines Index)
Ein Index kann mit dem DROP-Befehl von PostgreSQL gelöscht werden.
DROP INDEX index_name;
Sie können die folgende Anweisung verwenden, um den zuvor erstellten Index zu löschen:
# DROP INDEX salary_index;
Nach dem Löschen können Sie sehen, dass salary_index aus der Indexliste entfernt wurde:
exampledb=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+-----------------+-------+----------+------------
public | company_pkey | index | postgres | company
public | department_pkey | index | postgres | department
(2 rows)
Wann sollte die Verwendung von Indizes vermieden werden?
Obwohl der Zweck von Indizes darin besteht, die Datenbankleistung zu verbessern, gibt es mehrere Situationen, in denen die Verwendung von Indizes vermieden werden sollte.
Bei der Verwendung von Indizes sollten die folgenden Richtlinien beachtet werden:
- Indizes sollten nicht für kleine Tabellen verwendet werden.
- Indizes sollten nicht für Tabellen mit häufigen umfangreichen Aktualisierungs- oder Einfügevorgängen verwendet werden.
- Indizes sollten nicht für Spalten verwendet werden, die eine große Anzahl von NULL-Werten enthalten.
- Indizes sollten nicht für Spalten verwendet werden, auf denen häufig Operationen ausgeführt werden.