Leitfaden für Datenanalysten zur SQL-Indizierung: Langsame Abfragen beheben

Lässt die Antwortzeit Ihrer SQL-Abfragen zu wünschen übrig? Oder wissen Sie vielleicht nicht, ob Ihre Abfragen schneller sein könnten? In diesem Artikel erklären wir Ihnen, wie die SQL-Indizierung helfen kann.

Das Eintauchen in SQL ist wie das Freischalten einer Superkraft. Es geht darum, die Kunst der effektiven Abfrage Ihrer Datenbank zu beherrschen, damit Sie die benötigten Informationen schnell und einfach abrufen können. Aber was passiert, wenn Sie eine Frage stellen, die so komplex ist, dass sich die Antwortzeit von Sekunden auf Minuten verlängert - oder sogar länger? Dann sollten Sie die SQL-Indizierung nutzen.

In diesem Artikel erfahren Sie alles, was Sie über die SQL-Indizierung wissen müssen und warum sie der nächste Schritt bei der Optimierung Ihrer SQL-Abfragen sein sollte. Tauchen wir ein!

SQL und Datenbankabfragen

SQL ist eine Sprache zur Abfrage und Verwaltung von Datenbanken. Sie ermöglicht es Ihnen, Daten zu speichern und anzuzeigen sowie diese Daten zu aktualisieren. Die Verwaltung von Daten ist für die Datenanalyse und Business Intelligence von entscheidender Bedeutung. Wir müssen die zugrunde liegenden Daten verwalten, damit wir sie analysieren und Schlussfolgerungen daraus ziehen können: Welche Produkte verkaufen sich gut? Welche Kunden kaufen viel? Welche Kunden zahlen nicht pünktlich?

Sobald wir die Daten haben, können wir diese Fragen beantworten. Aber zuerst müssen wir "Fragen stellen" oder "Anfragen" zu unseren Daten stellen. Diese Anfragen werden Abfragen genannt - z. B. so etwas wie "Zählen Sie für die Verkäufe des letzten Monats, wie oft jedes Produkt verkauft wurde, und teilen Sie mir die Summe der Verkäufe mit".

Je komplexer unsere Fragen sind, desto komplexer werden natürlich auch unsere Abfragen. Es kann sein, dass wir Informationen aus mehreren Quellen kombinieren (oder JOIN) müssen, damit wir die benötigten Informationen extrahieren können.

Es ist zum Beispiel nützlich zu wissen, dass die Produkt-ID 2123876123 im letzten Quartal am meisten verkauft wurde, aber es ist noch nützlicher zu wissen, dass dieses Produkt ein blaues Sweatshirt ist. Mit zunehmender Komplexität unserer Abfragen wird es für unser Datenbanksystem immer schwieriger und zeitaufwändiger, die Abfrage zu beantworten. Wir müssen unsere SQL-Abfragen beschleunigen, aber wie?

Hier beginnt der Spaß. Es ist eine subtile Kunst zu verstehen, wann eine Abfrage langsam reagiert und wie man ihre Leistung verbessern kann. Wir könnten versuchen, unsere Abfrage zu ändern und sie effizienter zu machen, aber die Optimierung der SQL-Leistung bringt uns nur bedingt weiter. An einem bestimmten Punkt müssen wir eine Abfrageoptimierung durchführen. Dadurch wird es für das Datenbanksystem einfacher, die gesuchten Ergebnisse abzurufen, was die Abfrageleistung verbessert.

An dieser Stelle kommen Indizes ins Spiel.

Was ist ein SQL-Index?

Wie der Index eines Buches bereiten SQL-Indizes das Datenbanksystem auf einen effizienteren Datenabruf vor. Das Erstellen von Indizes ist einfach; wir haben einen umfassenden Kurs über Indizes, in dem Sie alles lernen, was Sie wissen müssen. Dazu gehört, wie sie in der Datenbank erstellt werden, die SQL-Syntax für die Erstellung eines Index und wann ein Index erstellt werden muss. Es gibt über 50 Übungen und einen geschätzten Lernaufwand von 10 Stunden.

Wenn Sie eine Einführung in die theoretische Seite von Indizes wünschen, haben wir auch weitere Artikel für Sie über SQL-Indizierungsgrundlagen und Was ist ein Datenbankindex?

Für unsere Zwecke werden wir uns nicht mit den Details befassen, wie die Datenbank einen Index aufbaut oder mit der zugrunde liegenden B-Baum-Struktur. Stattdessen werden wir uns darauf konzentrieren, wie die Datenbank einen Index verwendet. Es genügt zu sagen, dass ein B-Baum-Index (Balanced Tree) es uns ermöglicht, auf jede Zeile in der Datenbank in der gleichen Zeitspanne zuzugreifen.

Die Datenbankindizierung beschleunigt den Datenabruf. Denken Sie an unsere Analogie: Es ist einfacher, die Seite zu finden, die sich auf Abraham Lincoln bezieht, wenn Sie im Index des Buches nach "Lincoln, Abraham" suchen. (Vergleichen Sie das mit der Suche auf jeder einzelnen Seite nach einer Erwähnung von Mr. Lincoln, und Sie werden die Idee verstehen.) Mit einem Index würde die Suche nach einer bestimmten Person im Buch genauso viel Zeit in Anspruch nehmen.

Stellen Sie sich andererseits vor, das Buch hätte keinen Index und Sie müssten jede Seite durchsuchen, um einen Namen zu finden. Das Auffinden eines bestimmten Verweises wird eine unbekannte, variable Zeitspanne in Anspruch nehmen - Personen, die auf den ersten Seiten erscheinen, werden schneller gefunden als Personen, die am Ende erscheinen (es sei denn, wir scannen von hinten nach vorne).

Die SQL-Indizierung funktioniert auf die gleiche Weise. Ein Index wird auf eine Spalte angewendet, die es der Datenbank erleichtert, Informationen für eine Suche zurückzugeben.

Sollten wir Indizes für jede Spalte in einer Tabelle erstellen? Würde dies die Datenbank nicht beschleunigen? Nein. Die Indizierung zu vieler Spalten wirkt sich negativ auf die Datenbankleistung aus und macht das Hinzufügen und Aktualisieren von Zeilen sehr langsam. Die beste Praxis ist es, nur Spalten zu indizieren, die häufig zum Ordnen oder Sortieren von Daten verwendet werden.

Datenbankindizierung Beispiel 1

Nehmen wir an, dass Sie Informationen über Personen speichern müssen: Vorname, Nachname, Straßenname, Hausnummer, Postleitzahl, Ort, Land, Telefonnummer und Geburtsdatum.

Nehmen wir nun an, dass Sie Millionen von Datensätzen in dieser Datenbank gespeichert haben. Würden Sie die Datenbank wahrscheinlich anhand des Nachnamens durchsuchen (d. h. abfragen)? Ja, wahrscheinlich. Würden Sie die Datenbank wahrscheinlich nur anhand des Vornamens abfragen? Nein, wahrscheinlich nicht. Wahrscheinlicher ist, dass Sie eine Abfrage anhand des Vornamens und des Nachnamens durchführen würden. Aber vielleicht möchten Sie sehen, wann ein bestimmter Vorname im Datensatz am beliebtesten war. In diesem Fall würden Sie fragen: "In welchem Jahr wurden die meisten Personen mit diesem Vornamen geboren?"

Jeder dieser Fälle würde einen anderen Ansatz für die Indizierung erfordern. Wenn wir nur nach dem Nachnamen suchen, würden wir einen Index für die Spalte last_name erstellen. Bei einer Suche nach dem Vornamen und last würden wir sowohl die Spalte first_name als auch die Spalte last_name indizieren. Im dritten Fall würden wir einen Index nur für die Spalte first_name erstellen.

Kurz gesagt, es gibt verschiedene SQL-Indexierungstechniken. Durch die Anwendung dieser verschiedenen Techniken können wir die SQL-Leistung optimieren. Die Erstellung von Indizes hängt davon ab, wie Sie die Daten verwenden werden - oder anders ausgedrückt, wie Sie die Daten abfragen werden.

Datenbank-Indizierung Beispiel #2

Stellen Sie sich vor, wir haben eine person Tabelle mit vier Spalten: ssn (Sozialversicherungsnummer, die einer nationalen ID-Nummer ähnelt), first_name, last_name und zip_code (Postleitzahl). Wenn wir Millionen von Datensätzen haben, dauert die Ausführung einer SQL-Abfrage zum Auffinden einer Zeile auf der Grundlage des Nachnamens fast eine Minute - sogar für eine so einfache Tabelle.

Können Sie sich vorstellen, dass Sie bei jeder Abfrage eine Minute warten müssen, bis die Anwendung die benötigten Informationen abruft? In diesem Beispiel dauerte es 46 Sekunden, um 40 Millionen Datensätze zu durchsuchen. Es gab keinen Index für die Spalte last_name, so dass das Datenbanksystem jeden Datensatz in der Tabelle lesen musste, um zu prüfen, welche Datensätze mit einem bestimmten Nachnamen übereinstimmen. Wir müssen diese SQL-Abfrage dringend beschleunigen.

In dieser Situation erstellen wir einen Index für die Spalte last_name. Dadurch wird die SQL-Abfrage um drei Größenordnungen (ca. 3.000 Mal schneller) auf 15 Millisekunden beschleunigt: ein enormer Gewinn an SQL-Abfrageleistung.

Um einen Index in einer Datenbank zu erstellen, müssen wir Folgendes definieren:

1. Der Name des Indexes.
2. Welche Spalte(n) den Index erhalten soll(en).
3. Der Name der Tabelle, die diese Spalten enthält.

Denken Sie daran, dass Sie nicht für jede Spalte oder jede Kombination von Spalten einen Index erstellen sollten. Wenn wir das tun, riskieren wir, die Datenbank zu zerstören und eine Anwendung zu erstellen, die nicht mehr reagiert, wenn Benutzer Datensätze hinzufügen oder aktualisieren.

Schauen wir uns an, wie das gemacht wird.

Erstellen eines SQL-Index

Die Syntax zur Erstellung eines Indexes ist einfach. Um einen einfachen Index für eine Spalte der Tabelle zu erstellen, verwenden wir die folgende Anweisung. In diesem Fall wird der Index auf die Spalte zip_code unserer person Tabelle:

CREATE INDEX index_zip
ON person (zip_code);

Es ist nicht kompliziert. Denken Sie jedoch daran, dass jeder Index aktualisiert werden muss, wenn neue Zeilen hinzugefügt und bestehende Zeilen geändert oder gelöscht werden. Diese Aktualisierungen nehmen Zeit in Anspruch; wenn Sie Indizes übermäßig nutzen, können Indizes dazu führen, dass Ihre Datenbank und ihre Anwendung langsamer oder sogar zu langsam für die Benutzer werden.

Sie können bestehende Indizes auch ändern oder löschen (entfernen), aber das ist nicht Gegenstand dieses Artikels.

Es gibt verschiedene Arten von Indizes:

• Eindeutige Indizes gewährleisten die Datenintegrität, indem sie festlegen, dass keine zwei Zeilen in der Tabelle denselben Wert für den eindeutigen Index haben können.
• Primärindizes sind ein spezieller Typ von Unique-Index, aber es kann nur einen pro Tabelle geben. Der Primärindex wird erstellt, wenn die Tabelle erstellt wird. Wie der Name schon sagt, wird er anhand des Primärschlüssels der Tabelle erstellt.
• Sekundäre Indizes sind zusätzliche Indizes, die bei Bedarf erstellt werden (mit CREATE INDEX) und gelöscht werden können. Sekundäre Indizes können doppelte, nicht eindeutige Werte haben (d. h. es kann mehr als eine Zeile mit demselben Wert geben).
• Zusammengesetzte Indizes (mehrspaltige Indizes) sind Indizes, die mehrere Spalten umfassen.

Indizes sind wichtig, aber eine übermäßige Nutzung ist ebenso schädlich wie eine unzureichende oder fehlende Nutzung. Eine gut umgesetzte Indexierungsstrategie ist der Schlüssel. Sie können unnötige Indizes vermeiden, wenn Sie wissen, wie die Daten in Ihren Tabellen verwaltet werden. Achten Sie jedoch darauf, dass Sie wichtige Indizes einbeziehen, die darauf basieren, wie auf Ihre Daten zugegriffen wird und wie sie verwendet werden.

Bewährte Praktiken der SQL-Indizierung

Kommen wir nun zu den besten Praktiken für die Indexierung.

1. Indizieren Sienicht jede Tabelle. Kleine Tabellen benötigen keine Indizes, da ein Tabellenscan effizienter ist als das Durchsuchen des Indexes und das anschließende Abrufen der Daten aus der Tabelle.
2. Indizieren Sienicht jede Spalte. Ich hoffe, dass dies aus den vorherigen Beispielen ersichtlich ist. Die Indizierung jeder Spalte verursacht zusätzlichen Aufwand, um diese Indizes auf dem neuesten Stand zu halten, und verlangsamt andere Datenbankoperationen. Indizieren Sie Spalten, nach denen Sie filtern (d.h. die Sie häufig in WHERE-Klauseln verwenden).
3. Indizieren Siekeine großen Spalten. Ein großes Feld innerhalb Ihrer Tabelle führt zu einem großen Index.
4. Indizieren Sie Fremdschlüssel. Dies verbessert die Leistung von JOIN
5. Verwenden Sie mehrspaltige Indizes nur, wenn es angebracht ist. Mehrspaltige Indizes sind großartig. (Erinnern Sie sich an unser Beispiel, in dem wir sowohl den Vornamen als auch den Nachnamen indiziert haben, damit wir diese Kombination effizient abfragen können). Zusammengesetzte Indizes sind jedoch eine größere Herausforderung, da Sie die Reihenfolge der Spalten im Index berücksichtigen müssen. Sollen wir einen Index auf den Vornamen und den Nachnamen oder auf den Nachnamen und den Vornamen erstellen? Dies sind zwei verschiedene Indizes. Welcher wird effizienter arbeiten? Die Antwort hängt von den SQL-Abfragen ab. Im Allgemeinen ist ein einspaltiger Index ausreichend (und spart Zeit).
6. Verwenden Sie Indizes zum Vorsortieren von Daten. Das wiederholte Sortieren von Daten kann vermieden werden, wenn ein Index mit der Sortierreihenfolge (d.h. aufsteigend oder absteigend) hinzugefügt wird.
7. Stellen Siesicher, dass die Dinge wie erwartet funktionieren. Verwenden Sie EXPLAIN PLAN , um zu überprüfen, ob Ihre Indizes hilfreich sind.

Da wir nun eine Strategie für die Erstellung von Indizes haben, sollten wir uns überlegen, wie wir sie pflegen und abstimmen. Sie müssen Ihre Indizes überwachen: Prüfen Sie die Indexstatistiken, um die Nutzung der Indizes zu verstehen, und prüfen Sie den Abfrageplan, um zu analysieren, wie Ihre SQL-Abfragen die Indizes nutzen.

Bei der SQL-Indexwartung werden Indizes reorganisiert oder neu aufgebaut. Die Reorganisation eines Indexes ist weniger intensiv als ein vollständiger Neuaufbau. Sowohl die Reorganisation als auch der Neuaufbau eines Index sind ressourcenintensiv und können die Leistung verbessern, müssen es aber nicht. Analysieren Sie also Ihre Indizes, bevor Sie sie pflegen. Gehen Sie nicht davon aus, dass die Beibehaltung eines Indexes die Leistung einer SQL-Abfrage verbessern wird.

Wie geht es weiter mit der SQL-Indizierung?

Nachdem wir nun besprochen haben, was Indizes sind und wie man sie am besten erstellt und pflegt, sollten Sie sich im nächsten Schritt näher mit Indizes beschäftigen. Unser Kurs Indizes verstehen erklärt die Funktionsweise von Indizes und zeigt Ihnen, wie und wann Sie einen Index erstellen. Die praktischen Übungen helfen Ihnen, schnell zu verstehen, was Sie brauchen, um Ihre Abfragen schnell und effizient zu gestalten. Viel Spaß beim Lernen!