So erstellen Sie Indizes in PostgreSQL

Indizes sind spezialisierte Suchtabellen, die von Datenbanksuchmaschinen verwendet werden, um die Abfrageergebnisse zu beschleunigen. Ein Index ist ein Verweis auf die Informationen in einer Tabelle. Wenn beispielsweise die Namen in einem Kontaktbuch nicht alphabetisch sortiert sind, müssen Sie jede Zeile durchgehen und jeden Namen durchsuchen, bevor Sie die bestimmte Telefonnummer erreichen, nach der Sie suchen. Ein Index beschleunigt die SELECT-Befehle und WHERE-Angaben und führt die Dateneingabe in den UPDATE- und INSERT-Befehlen durch performing. Unabhängig davon, ob Indizes eingefügt oder gelöscht werden, hat dies keine Auswirkungen auf die in der Tabelle enthaltenen Informationen. Indizes können auf die gleiche Weise speziell sein, wie die UNIQUE-Beschränkung hilft, Replikate in dem Feld oder der Gruppe von Feldern zu vermeiden, für die der Index existiert.

Allgemeine Syntax

Die folgende allgemeine Syntax wird verwendet, um Indizes zu erstellen.

>> CREATE INDEX index_name ON table_name (column_name);

Um mit der Arbeit an Indizes zu beginnen, öffnen Sie den pgAdmin von Postgresql über die Anwendungsleiste. Sie finden die Option 'Server' unten angezeigt. Klicken Sie mit der rechten Maustaste auf diese Option und verbinden Sie sie mit der Datenbank.

Wie Sie sehen, ist die Datenbank 'Test' in der Option 'Datenbanken' aufgeführt. Wenn Sie keine haben, klicken Sie mit der rechten Maustaste auf "Datenbanken", navigieren Sie zur Option "Erstellen" und benennen Sie die Datenbank nach Ihren Wünschen.

Erweitern Sie die Option 'Schemas', und Sie finden dort die Option 'Tabellen' aufgeführt. Wenn Sie keine haben, klicken Sie mit der rechten Maustaste darauf, navigieren Sie zu "Erstellen" und klicken Sie auf die Option "Tabelle", um eine neue Tabelle zu erstellen. Da wir die Tabelle 'emp' bereits erstellt haben, können Sie sie in der Liste sehen.

Versuchen Sie die SELECT-Abfrage im Abfrage-Editor, um die Datensätze der 'emp'-Tabelle abzurufen, wie unten gezeigt.

>> AUSWÄHLEN * VON ÖFFENTLICH.emp ORDER BY „id“ ASC;

Die folgenden Daten werden in der 'emp'-Tabelle sein.

Erstellen Sie einspaltige Indizes

Erweitern Sie die Tabelle 'emp', um verschiedene Kategorien zu finden, z.G., Spalten, Einschränkungen, Indizes usw. Klicken Sie mit der rechten Maustaste auf „Indizes“, navigieren Sie zur Option „Erstellen“ und klicken Sie auf „Index“, um einen neuen Index zu erstellen.

Konstruieren Sie einen Index für die gegebene 'emp'-Tabelle oder die eventuelle Anzeige, indem Sie das Index-Dialogfenster verwenden. Hier gibt es zwei Registerkarten: 'Allgemein' & 'Definition.' Geben Sie auf der Registerkarte 'Allgemein' einen bestimmten Titel für den neuen Index in das Feld 'Name' ein. Wählen Sie den 'Tablespace' aus, unter dem der neue Index gespeichert wird, indem Sie die Dropdown-Liste neben 'Tablespace . verwenden.'  Wie im Bereich "Kommentar" können Sie hier Indexkommentare abgeben. Um diesen Vorgang zu starten, navigieren Sie zum Tab "Definition".

Legen Sie hier die 'Zugriffsmethode' fest, indem Sie den Indextyp auswählen. Um Ihren Index anschließend als "Einzigartig" zu erstellen, sind dort mehrere andere Optionen aufgeführt. Tippen Sie im Bereich "Spalten" auf das Zeichen "+" und fügen Sie die Spaltennamen hinzu, die für die Indizierung verwendet werden sollen. Wie Sie sehen, haben wir die Indizierung nur auf die Spalte "Telefon" angewendet. Wählen Sie zunächst den Abschnitt SQL aus.

Die Registerkarte SQL zeigt den SQL-Befehl, der durch Ihre Eingaben im gesamten Index-Dialog erstellt wurde. Klicken Sie auf die Schaltfläche 'Speichern', um den Index zu erstellen.

Gehen Sie erneut zur Option 'Tabellen' und navigieren Sie zur Tabelle 'emp'. Aktualisieren Sie die Option 'Indizes', und Sie finden den neu erstellten Index 'index_on_phone' darin aufgelistet.

Jetzt führen wir den EXPLAIN SELECT-Befehl aus, um die Ergebnisse für die Indizes mit der WHERE-Klausel zu überprüfen. Dies führt zu der folgenden Ausgabe, die lautet: 'Seq Scan on emp.' Sie fragen sich vielleicht, warum dies passiert ist, während Sie Indizes verwenden.

Grund: Der Postgres-Planer kann aus verschiedenen Gründen entscheiden, keinen Index zu haben. Der Stratege trifft meistens die besten Entscheidungen, auch wenn die Gründe nicht immer klar sind. Es ist in Ordnung, wenn in einigen Abfragen eine Indexsuche verwendet wird, aber nicht in allen. Die von beiden Tabellen zurückgegebenen Einträge können je nach den von der Abfrage zurückgegebenen festen Werten variieren. Da dies der Fall ist, ist ein Sequenzscan fast immer schneller als ein Indexscan, was darauf hindeutet, dass der Abfrageplaner möglicherweise Recht hatte, als er feststellte, dass die Kosten für die Ausführung der Abfrage auf diese Weise reduziert werden.

Erstellen Sie mehrere Spaltenindizes

Um mehrspaltige Indizes zu erstellen, öffnen Sie die Befehlszeilen-Shell und betrachten Sie die folgende Tabelle 'student', um mit der Arbeit an Indizes mit mehreren Spalten zu beginnen.

>> AUSWÄHLEN * VON Schüler;

Schreiben Sie die folgende CREATE INDEX-Abfrage hinein. Diese Abfrage erstellt einen Index namens 'new_index' in den Spalten 'sname' und 'age' der Tabelle 'student'.

>> INDEX ERSTELLEN new_index ON Schüler (Name, Alter);

Jetzt werden wir die Eigenschaften und Attribute des neu erstellten 'new_index'-Index mit dem '\d'-Befehl auflisten. Wie Sie im Bild sehen können, handelt es sich um einen btree-artigen Index, der auf die Spalten 'Name' und 'Alter' angewendet wurde.

>> \d neuer_index;

EINZIGARTIGEN Index erstellen

Um einen eindeutigen Index zu erstellen, nehmen Sie die folgende 'emp'-Tabelle an.

>> AUSWÄHLEN * VON emp;

Führen Sie die CREATE UNIQUE INDEX-Abfrage in der Shell aus, gefolgt vom Indexnamen 'empind' in der 'name'-Spalte der 'emp'-Tabelle. In der Ausgabe sehen Sie, dass der eindeutige Index nicht auf eine Spalte mit doppelten 'Namen'-Werten angewendet werden kann.

>> CREATE unique INDEX empind ON emp (Name);

Achten Sie darauf, den eindeutigen Index nur auf Spalten anzuwenden, die keine Duplikate enthalten. Für die Tabelle 'emp' können Sie davon ausgehen, dass nur die Spalte 'id' eindeutige Werte enthält. Also werden wir einen eindeutigen Index darauf anwenden.

>> ERSTELLE eindeutigen INDEX empind ON emp (id);

Im Folgenden sind die Attribute des eindeutigen Index aufgeführt.

>> \d empid;

Drop-Index

Die DROP-Anweisung wird verwendet, um einen Index aus einer Tabelle zu entfernen.

>> DROP INDEX empind;

Fazit

Obwohl Indizes dazu gedacht sind, die Effizienz von Datenbanken zu verbessern, ist es in einigen Fällen nicht möglich, einen Index zu verwenden. Bei der Verwendung eines Index sind folgende Regeln zu beachten:

• Indizes sollten bei kleinen Tabellen nicht abgelöst werden.
• Tabellen mit vielen umfangreichen Batch-Upgrade-/Aktualisierungs- oder Hinzufügungs-/Einfügevorgängen.
• Bei Spalten mit einem erheblichen Prozentsatz von NULL-Werten können Indizes nicht durcheinander sein-
• Verkauf.
• Bei regelmäßig manipulierten Spalten sollte eine Indexierung vermieden werden.