Lektionen des Moduls (1/4)
Indizes und B-Trees
Wie kann eine Datenbank in Bruchteilen einer Sekunde einen einzelnen Datensatz unter Millionen von Zeilen finden?
Wenn wir SELECT * FROM customers WHERE city = 'Milano' abfragen, muss Postgres standardmäßig einen sequentiellen Scan durchführen: Es liest die gesamte Tabelle Zeile für Zeile physisch von der Festplatte und vergleicht jede Stadt mit „Mailand“. Wenn eine Tabelle Millionen von Datensätzen enthält, beeinträchtigt dieser Prozess die Leistung.
Die wichtigste strukturelle Antwort auf diese Engpässe sind Indizes.
Indizes (B-Bäume)
Indizes funktionieren genau wie der analytische Index am Ende eines großen Buches. Anstatt das ganze Buch zu lesen, um herauszufinden, wo von „Milano“ die Rede ist, schauen wir auf den Rückseiten nach, wo das Wort „Milano“ uns zu [p. 45, 114, 230] weiterleitet.
Datenbanken bauen hinter den Kulissen diese separate, sehr kompakte Datenstruktur (oft ein „B-Baum“) auf, die nach der betreffenden Spalte oder den betreffenden Spalten geordnet ist und direkte Zeiger auf den physischen Speicher der übereinstimmenden Zeilen enthält.
-- Creare un indice semplice
CREATE INDEX idx_customers_city ON customers(city);Dieser einzelne Vorgang löst die Postgres-Engine aus, die alle city-Werte im Hintergrund extrahiert und indiziert. Sobald dies abgeschlossen ist, wird jeder weitere SELECT ... WHERE city = '...' fliegen!
Primärschlüssel und Eindeutigkeit
Wussten Sie, dass die Schlüsselregeln Primärschlüssel (PRIMARY KEY) und UNIQUE heimlich Indizes unter der Haube verwenden? Da die Datenbank sicherstellen muss, dass eine E-Mail-Adresse oder ID beim Einfügen oder Ändern von Zeilen niemals dupliziert wird, erstellt PostgreSQL automatisch einen impliziten B-Tree-Index darüber: Auf diese Weise dauern Eindeutigkeitsprüfungen Mikrosekunden.
Aus diesem Grund sind Verknüpfungen im ID-Feld unglaublich leistungsfähig.
Einen Index löschen
Wenn Sie feststellen, dass sich umfangreiche Abfragen geändert haben und ein Index nie konsultiert wird, ist es ratsam, ihn zu zerstören und wichtigen Speicherplatz zurückzugewinnen:
DROP INDEX idx_customers_city;Du bist dran
In unserem E-Commerce stellen wir durch Dashboards fest, dass wir Kunden sehr oft nach Nationalität filtern (die Länderspalte in Kunden). Erstellen Sie einen neuen Basisindex für die Länderspalte und nennen Sie ihn „idx_customers_country“.
Hinweis anzeigen
Verwenden Sie CREATE INDEX index_name ON table_name(column_name);
Lösung nach 3 Versuchen verfügbar
Erkunden sekundärer eindeutiger Indizes
Ein B-Tree-Index kann auch verwendet werden, um eine UNIQUE-Einschränkung zum Zeitpunkt der Erstellung zu simulieren und Suchvorgänge zu beschleunigen und gleichzeitig die logische Absicht explizit und durchzusetzen. Erstellen Sie einen neuen Index, in dem Sie das Schlüsselwort UNIQUE deklarieren. Definieren Sie es so, dass unerwünschte doppelte Anmeldungen anderer Benutzer blockiert werden, wodurch die Eindeutigkeit der Spalte „E-Mail“ von „Kunden“ erzwungen wird, und nennen Sie es „idx_unique_email_customer“.
Hinweis anzeigen
Schreiben Sie CREATE UNIQUE INDEX index_name ON table(column);
Lösung nach 3 Versuchen verfügbar