SQL Server Index: Steigerung der Datenbankleistung

Als ich anfing, mit SQL Server zu arbeiten, stellte ich fest, dass nicht alle langsamen Abfragen gleich erstellt wurden. Einige Abfragen waren aufgrund eines schlechten Designs langsam, während andere einfach ineffizient waren. Aber eine Entdeckung hat meine Herangehensweise an die Datenbankleistung für immer verändert: Indizes. Stell dir vor, du suchst ein bestimmtes Kapitel in einem dicken Lehrbuch ohne Stichwortverzeichnis; du würdest Hunderte von Seiten durchblättern und dabei wertvolle Zeit verlieren. Dieses Szenario spiegelt das wider, was in Datenbanken ohne richtige Indizierung passiert.

In diesem Artikel erkläre ich, warum SQL Server-Indizes wichtig sind, wie sie funktionieren und wie du sie nutzen kannst, um die Leistung deiner Datenbank zu maximieren. Wenn du deine SQL-Kenntnisse in SQL Server vertiefen möchtest, empfehle ich dir unsere Kurse Einführung in SQL Server und SQL Server für Fortgeschrittene, um T-SQL für die Datenbearbeitung und -analyse zu lernen.

SQL Server-Indizes verstehen

Mit SQL Server-Indizes kannst du Tabellen in Datenbanken effizient abfragen, ohne ganze Datensätze zu durchsuchen. Im Folgenden werden die SQL Server-Indizes und ihre Bedeutung für die Datenbankverwaltung definiert.

Was sind SQL Server-Indizes?

Indizes sind spezielle Datenstrukturen, die es SQL Server ermöglichen, Zeilen effizienter zu finden und abzurufen, was sowohl Zeit als auch Rechenressourcen spart. Indizes organisieren die Daten so, dass sie schneller gefunden werden können. Anstatt die gesamte Tabelle Zeile für Zeile zu durchsuchen (ein Prozess, der als Tabellenscan bekannt ist), kann SQL Server den Index verwenden, um die benötigten Zeilen viel schneller zu finden, als wenn er jeden Eintrag in der Tabelle durchforsten müsste.

Es gibt zwei grundlegende Arten von Indizes in SQL Server:

• Geclusterte Indizes: Ein geclusterter Index bestimmt die physische Reihenfolge der Daten in einer Tabelle. Wenn du einen geclusterten Index für eine Tabelle erstellst, werden die Zeilen auf der Festplatte in der Reihenfolge der indizierten Spalte gespeichert. Dies ist vor allem für Bereichsabfragen von Vorteil, da es einen effizienten sequentiellen Zugriff auf Zeilen ermöglicht.

• Nicht geclusterte Indizes: Nicht geclusterte Indizes haben keinen Einfluss auf die physische Reihenfolge der Daten. Stattdessen erstellen sie eine separate Struktur, die Zeiger auf die eigentlichen Datenzeilen enthält. Nicht geclusterte Indizes sind nützlich, um die Leistung von Abfragen zu verbessern, die auf der Grundlage von Spalten filtern oder sortieren, die nicht im geclusterten Index enthalten sind.

In unserem SQL Server-Tutorial erfährst du alles über die grundlegende bis fortgeschrittene Verwendung von SQL Server im Datenmanagement.

Warum sind Indizes wichtig?

Indizes sind wichtig, um die Abfrageleistung und die Effizienz der Datenbank insgesamt zu verbessern. Hier sind einige wichtige Vorteile der Verwendung von Indizes:

• Schnellere Ausführung von Abfragen: Indizes verkürzen die Zeit, die zum Suchen und Abrufen von Daten benötigt wird. Wenn du zum Beispiel in einer Tabelle mit Millionen von Zeilen nach allen Bestellungen eines bestimmten Kunden suchst, kann SQL Server mit einem Index auf der Spalte "Kunde" diese Zeilen fast sofort auffinden.

• Effiziente Ressourcennutzung: Mit Indizes kann SQL Server Aufgaben mit weniger CPU- und Speicherressourcen erledigen. Diese Effizienz führt zu einer besseren Gesamtleistung des Systems, insbesondere bei hoher Arbeitsbelastung.

• Verbesserte Sortierung und Gruppierung: Indizes können Operationen zum Sortieren (ORDER BY) und Gruppieren (GROUP BY) beschleunigen. Indem sie die Daten sortiert halten, ermöglichen Indizes dem SQL Server, die Ergebnisse schnell und ohne zusätzlichen Sortieraufwand abzurufen.

Betrachte diesen Anwendungsfall für Indexanwendungen. Stell dir eine große E-Commerce-Plattform vor, auf der die Nutzer/innen häufig nach Produkten mit ihrem Namen suchen. Indem du einen nicht geclusterten Index für die Produktnamensspalte erstellst, kannst du diese Suchvorgänge fast sofort durchführen, was die Benutzerfreundlichkeit verbessert und die Serverlast verringert.

Ich empfehle den Lernpfad SQL Server Fundamentals von DatCamp, um mehr über das Sortieren, Gruppieren und Verbinden von Tabellen für die Datenanalyse zu erfahren.

Arten von SQL Server-Indizes

Es gibt verschiedene Arten von SQL Server-Indizes, jeder mit einzigartigen Eigenschaften und Anwendungsfällen. Lass uns die gängigen Arten von Indizes besprechen.

Geclusterte vs. nicht geclusterte Indizes

Die beiden Haupttypen von Indizes in SQL Server sind geclusterte und nicht geclusterte Indizes.

Geclusterter Index

Ein geclusterter Index bestimmt die physische Reihenfolge der Zeilen in einer Tabelle und sortiert die Tabelle nach der/den Indexspalte(n). Da die Daten physisch nur auf eine Weise geordnet werden können, kann es nur einen geclusterten Index pro Tabelle geben. Ein geclusterter Index ist ideal für Abfragen, die eine Reihe von Werten zurückgeben oder Daten nach der indizierten Spalte sortieren müssen. 

Eine nach OrderDate sortierte Tabelle kann zum Beispiel von einem geclusterten Index auf der Spalte OrderDate profitieren. Abfragen, die alle Bestellungen innerhalb eines Datumsbereichs abrufen, werden viel schneller ausgeführt.

Nicht geclusterter Index

Ein nicht geclusterter Index erstellt eine eigene Struktur, die auf die tatsächlichen Zeilen in der Tabelle verweist. Eine Tabelle kann mehrere nicht geclusterte Indizes haben, die jeweils in einem anderen Abfragemuster verwendet werden. Nicht geclusterte Indizes enthalten einen Verweis (Zeilenlokator) auf die eigentlichen Daten, der eine Zeilen-ID oder ein geclusterter Indexschlüssel sein kann. Diese Art von Index wird auf Spalten angewendet, die häufig in Suchbedingungen oder Joins verwendet werden (z. B. EmailAddress, ProductName).

Ein nicht geclusterter Index auf der Spalte ProductName einer Bestandstabelle ermöglicht zum Beispiel eine schnellere Suche nach bestimmten Produkten, ohne die Struktur der Tabelle zu verändern.

Einzigartige und zusammengesetzte Indizes

SQL Server unterstützt auch eindeutige und zusammengesetzte Indizes, die jeweils einem bestimmten Zweck dienen.

Einzigartige Indizes

Ein eindeutiger Index stellt sicher, dass alle Werte in der/den indizierten Spalte(n) eindeutig sind. Sie stellt die Datenintegrität sicher, indem sie doppelte Werte verhindert, und wird oft auf Spalten mit natürlicher Eindeutigkeit angewendet, wie z. B. E-Mail-Adressen oder Benutzernamen. Ein eindeutiger Index für die Spalte Email in einer Tabelle stellt zum Beispiel sicher, dass keine zwei Benutzer dieselbe E-Mail-Adresse haben können.

Zusammengesetzte Indizes

Ein zusammengesetzter Index erstreckt sich über mehrere Spalten und optimiert Abfragen, die auf der Grundlage einer Kombination dieser Spalten filtern oder sortieren. Die Reihenfolge der Spalten im zusammengesetzten Index ist wichtig, wobei SQL Server die Spalte ganz links zuerst verarbeitet. Zusammengesetzte Indizes unterstützen komplexe Abfragen mit mehreren Bedingungen. Ein zusammengesetzter Index auf LastName und FirstName in einer Kundentabelle beschleunigt zum Beispiel die Suche nach Kunden anhand ihres vollständigen Namens.

SQL Server-Indizes erstellen und verwalten

Wir verwenden das SQL Server Management Studio (SSMS), um einen Index in SQL Server zu erstellen. Bevor ich dir das näher erkläre, solltest du dich in unserem Blog über die Unterschiede zwischen den SQL Server-, PostgreSQL- und MySQL-Dialekten informieren. Ich werde die Schritte zur Erstellung und Verwaltung von SQL Server-Indizes erläutern.

Wie man einen SQL Server-Index erstellt

Im Folgenden findest du eine Schritt-für-Schritt-Anleitung zum Erstellen von Indizes sowohl mit SQL Server Management Studio (SSMS) als auch mit SQL-Code.

Erstellen eines Indexes mit SSMS

1. Öffne den Object Explorer: Starte SSMS und verbinde dich mit deiner SQL Server-Instanz. Erweitere die Datenbank, die die Tabelle enthält, für die du einen Index erstellen möchtest.

Öffne den Objekt-Explorer und navigiere in der Tabelle, um den Index zu erstellen. Bild vom Autor.

2. Finde die Tabelle: Navigiere zum Ordner "Tabellen", suche deine Tabelle und erweitere sie.

Suche die Tabelle, um den Index zu erstellen und erweitere sie. Bild vom Autor.

3. Öffne den Ordner Indizes: Klicke mit der rechten Maustaste auf den Ordner " Indizes" und wähle " Neuer Index".

Öffne den Ordner Index und klicke mit der rechten Maustaste, um einen neuen Index zu erstellen. Bild vom Autor.

4. Wähle den Index-Typ: Wähle die Art des Indexes aus, den du erstellen möchtest (z.B. Clustered Columnstore Index oder Non-Clustered Index).
5. Definiere Indexeigenschaften: Gib den Indexnamen an und füge dann Spalten zu dem Index hinzu, indem du auf Hinzufügen klickst und die gewünschten Spalten auswählst. Konfiguriere zusätzliche Optionen, wie z.B. Eindeutigkeit und Sortierreihenfolge.

Wähle den Indextyp und definiere die Eigenschaften. Bild vom Autor.

6. Erstelle den Index: Klicke auf OK, um den Index zu erstellen.

Erstelle den Index, indem du auf ok klickst. Bild vom Autor.

Einen Index mit SQL-Code erstellen

Du kannst Indizes auch direkt über T-SQL-Befehle erstellen. Im Folgenden findest du Beispiele für die Erstellung verschiedener Arten von Indizes mithilfe von Abfragen:

-- Create a non-clustered index on the Product column
CREATE NONCLUSTERED INDEX IX_Sales_Product
ON Sales (Product);

-- Create a unique index on the combination of CustomerName and OrderDate
CREATE UNIQUE NONCLUSTERED INDEX IX_Sales_CustomerName_OrderDate
ON Sales (CustomerName, OrderDate);

-- Create a clustered index on the OrderDate column
CREATE CLUSTERED INDEX IX_Sales_OrderDate
ON Sales (OrderDate);

Wie man einen SQL Server-Index löscht

Du kannst einen Index mit der einfachen Methode löschen. Sei vorsichtig, denn das Entfernen eines Index kann die Abfrageleistung beeinträchtigen.

SQL Server-Index mit SSMS löschen

1. Navigiere im Objekt-Explorer zum Ordner Indizes unter der Tabelle.
2. Klicke mit der rechten Maustaste auf den Index, den du löschen möchtest, und wähle Löschen.

Navigiere zu dem Index, den du löschen möchtest. Bild vom Autor.

3. Bestätige die Löschung im Dialogfeld.

Bestätige die Löschung im Dialogfeld und klicke auf ok. Bild vom Autor.

Einen Index mit T-SQL fallen lassen

Mit den folgenden Befehlen kannst du bestehende Indizes aus einer Tabelle in SQL Server löschen.

-- Drop a non-clustered index
DROP INDEX IX_Sales_Product ON Sales;

-- Drop a clustered index
DROP INDEX IX_Sales_OrderDate ON Sales;

Wie man einen SQL Server-Index reorganisiert

Bei der Reorganisation eines Index werden die Blattseiten des Index defragmentiert, wodurch die Leistung verbessert wird, ohne den Index vollständig neu aufzubauen.

Einen SQL Server-Index mit SSMS reorganisieren

1. Klicke mit der rechten Maustaste auf den Index und gehe dann auf Alles neu organisieren.

Klicke mit der rechten Maustaste auf Indizes und dann auf Alle reorganisieren. Bild vom Autor.

2. Wähle den/die zu reorganisierenden Index(e) aus und bestätige.

Wähle die zu reorganisierenden Indizes aus und bestätige dann. Bild vom Autor.

Einen Index mit T-SQL reorganisieren

Du kannst auch die folgenden Abfragen verwenden, um Indizes in SQL Server zu reorganisieren.

-- Reorganize a single index
ALTER INDEX IX_Sales_Product
ON Sales
REORGANIZE;

-- Reorganize all indexes on the table
ALTER INDEX ALL
ON Sales
REORGANIZE;

Wie man einen SQL Server-Index neu aufbaut

Wenn du einen Index neu erstellst, wird er von Grund auf neu erstellt, wodurch die Fragmentierung beseitigt und der Speicherplatz optimiert wird.

Wiederaufbau eines Index mit SSMS

1. Klicke mit der rechten Maustaste auf den Index und gehe dann auf Alle neu erstellen.

Klicke mit der rechten Maustaste auf den Index und gehe dann auf Alle neu erstellen. Bild vom Autor

2. Wähle den/die neu zu erstellenden Index(e) und bestätige.

Wähle die neu zu erstellenden Indizes aus und bestätige. Bild vom Autor

Wiederaufbau eines Index mit T-SQL

Die folgende Abfrage baut die Indizes der Tabelle neu auf.

-- Rebuild a single index
ALTER INDEX IX_Sales_Product
ON Sales
REBUILD;

-- Rebuild all indexes on the table
ALTER INDEX ALL
ON Sales
REBUILD;

Best Practices für die Indexverwaltung

Die Pflege und Optimierung von SQL Server-Indizes ist wichtig, um eine optimale Datenbankleistung zu gewährleisten. Im Folgenden findest du Tipps für ein effektives Indexmanagement.

• Führe regelmäßige Indexpflege durch: Reorganisiere deinen Index regelmäßig oder baue ihn neu auf, um die Fragmentierung zu verringern, die mit der Zeit die Leistung beeinträchtigen kann. Verwende Wartungsskripte oder Tools wie SQL Server Maintenance Plans, um diese Aufgaben zu automatisieren.

• Analysiere die Indexnutzung: Verwende Dynamic Management Views (DMVs) wie sys.dm_db_index_usage_stats, um ungenutzte oder nicht ausgelastete Indizes zu identifizieren. Entferne Indizes, die keinen wesentlichen Leistungsvorteil bieten, um unnötigen Overhead zu vermeiden.

• Vermeide eine Überindizierung: Zu viele Indizes können Schreibvorgänge verlangsamen (INSERT, UPDATE, DELETE), weil alle relevanten Indizes aktualisiert werden müssen. Konzentriere dich auf die Indizierung von Spalten, die häufig in den SELECT Abfragen, Joins oder Filterbedingungen verwendet werden.

• Überwache den Fragmentierungsgrad: Verwende sys.dm_db_index_physical_stats, um stark fragmentierte Indizes zu identifizieren. Reorganisiere die Indizes bei einer Fragmentierung unter 30% und baue sie bei einer Fragmentierung über 30% neu auf.

• Nutze gefilterte Indizes: Verwende gefilterte Indizes, um Abfragen zu optimieren, die auf einer Teilmenge von Daten beruhen, z. B. auf aktiven Nutzern oder bestimmten Datumsbereichen.

Du solltest auch die folgenden Praktiken beachten, um häufige Fallstricke bei der Verwaltung deiner Indizes zu vermeiden:

• Überindizierung: Vermeide es, für jede Abfrage einen Index zu erstellen; dies hat Auswirkungen auf die Schreibvorgänge und die Speicherung.

• Vernachlässigung der Wartung: Wenn du die Indizes nicht neu erstellst oder reorganisierst, führt das zu einer stärkeren Fragmentierung.

• Ignorieren der Indizierung für Schreibvorgänge: Während Indizes das Lesen beschleunigen, können sie das Schreiben erheblich verlangsamen.

• Keine Prüfung von zusammengesetzten Indizes: Eine falsche Spaltenreihenfolge in zusammengesetzten Indizes kann sie unwirksam machen.

Im Lernpfad "SQL Server für Datenbankadministratoren " des DataCamps erfährst du mehr über Transaktionen und Fehlerbehandlung in SQL Server, um die Abfrageleistung zu verbessern.

Leistungsüberlegungen

Indizes sind nützlich, um die Leistung der Datenbank zu optimieren, können aber auch mit einigen Kompromissen verbunden sein. Um ein gut funktionierendes Datenbanksystem zu erhalten, ist es wichtig, ihre Auswirkungen zu verstehen und sie effektiv zu verwalten.

Auswirkungen von Indizes auf die Abfrageleistung

Indizes haben einen großen Einfluss auf die Ausführungspläne und die Leistung von Abfragen, da sie die Zeit, die zum Abrufen von Daten benötigt wird, reduzieren. Sie ermöglichen es SQL Server, Zeilen schnell zu finden, so dass weniger kostspielige vollständige Scans der Tabelle erforderlich sind. Ausführungspläne zeigen oft Operationen wie Index Seek anstelle von Table Scan, wenn effektive Indizes vorhanden sind.

Zu den Instrumenten zur Messung der Indexleistung gehören die folgenden:

• sys.dm_db_index_usage_stats verfolgt, wie Indizes von Abfragen verwendet werden.

• sys.dm_db_index_operational_stats liefert Betriebskennzahlen wie Lese- und Schreibvorgänge.

• Der Abfragespeicher analysiert die Abfrageleistung und identifiziert Ineffizienzen, die durch fehlende oder ungenutzte Indizes verursacht werden.

Die folgende Abfrage ruft zum Beispiel Statistiken darüber ab, wie Indizes in benutzerdefinierten Tabellen verwendet werden, einschließlich Details zu Suchvorgängen, Scans, Lookups und Updates, um ihre Effektivität und Leistung zu bewerten.

-- Retrieve index usage statistics for user tables
SELECT 
    OBJECT_NAME(IXS.OBJECT_ID) AS TableName, 
    I.name AS IndexName,
    IXS.user_seeks AS Seeks,
    IXS.user_scans AS Scans,
    IXS.user_lookups AS Lookups,
    IXS.user_updates AS Updates
-- DMV providing index usage stats
FROM 
    sys.dm_db_index_usage_stats IXS         
JOIN 
    sys.indexes I ON I.OBJECT_ID = IXS.OBJECT_ID 
                 AND I.index_id = IXS.index_id
WHERE 
    OBJECTPROPERTY(IXS.OBJECT_ID, 'IsUserTable') = 1;

Gleichgewicht zwischen Indexnutzung und Speicherung

Indizes verbessern zwar die Leistung der Datenbank, aber sie verursachen auch Speicherkosten, da sie vor allem bei großen Datenmengen Speicherplatz verbrauchen. Es ist wichtig zu wissen, wie man diese Faktoren bei der Implementierung von Indizes ausbalanciert. Mehr Indizes können Schreibvorgänge verlangsamen, besonders in Umgebungen mit vielen Transaktionen. Ziehe die folgenden Strategien in Betracht, um die Indexnutzung auszugleichen.

• Verzeichne nur, was wichtig ist: Konzentriere dich auf Indizierungsspalten, die häufig in den Klauseln WHERE, JOIN und ORDER BY verwendet werden. Vermeide es, selten abgefragte Spalten zu indizieren.

• Verwende gefilterte Indizes: Erstelle Indizes für Teilmengen von Daten, um Platz zu sparen und bestimmte Abfragen zu optimieren.

• Komprimierung der Hebelwirkung: Aktiviere die Datenkomprimierung, um den Speicherplatzbedarf von Indizes zu reduzieren.

• Archiviere alte Daten: Verschiebe Daten, auf die selten zugegriffen wird, in eine Archivtabelle und reduziere die Indizierung der Haupttabelle.

Die oben genannten Strategien sorgen für eine optimale Abfrageleistung und halten gleichzeitig die Speicher- und Wartungskosten in Schach.

Fehlerbehebung bei Indexproblemen

Wenn sie nicht ordnungsgemäß gewartet werden, können Indizes mit der Zeit abbauen und die Effizienz der Datenbank verringern. Lasst uns besprechen, wie wir diese Probleme erkennen und lösen können.

• Fragmentierung: Mit der Zeit werden die Indizes fragmentiert, was zu einer ineffizienten Datenabfrage führt. Zu den Anzeichen für eine Fragmentierung gehören längere Ausführungszeiten für Abfragen und hohe Festplatten-E/A. Um dieses Problem zu lösen, verwende sys.dm_db_index_physical_stats, um die Fragmentierung zu erkennen. Reorganisieren Sie dann die Indizes für Fragmentierungsgrade unter 30% und bauen Sie die Indizes für Fragmentierungsgrade über 30% neu auf

• Veraltete Statistiken: Veraltete Statistiken führen zu suboptimalen Abfrageausführungsplänen. Daher kann es trotz geeigneter Indizes zu langsamen Abfragen kommen. Um dieses Problem zu lösen, aktualisieren Sie die Statistiken für kritische Indizes manuell oder aktivieren Sie AUTO_UPDATE_STATISTICS.

• Ungenutzte oder überflüssige Indizes: Einige Indizes bleiben ungenutzt, verbrauchen Speicherplatz und verlangsamen Schreibvorgänge. Die Symptome dieses Problems sind hohe Indexpflegekosten und ein geringer Abfragenutzen. Um dieses Problem zu lösen, identifiziere ungenutzte Indizes mit sys.dm_db_index_usage_stats und lasse sie bei Bedarf fallen.

• Fehlende Indizes: Das Fehlen geeigneter Indizes kann dazu führen, dass Tabellen gescannt werden, was die Abfragen verlangsamt. In solchen Szenarien zeigen die Abfrageausführungspläne Table Scan anstelle von Index Seek. Um dieses Problem zu lösen, verwende den DMV Missing Index sys.dm_db_missing_index_details, um Möglichkeiten zu identifizieren und die empfohlenen Indizes auf der Grundlage von Abfragemustern zu erstellen.

• Überschneidende Indizes: Mehrere Indizes mit ähnlichen Spaltenstrukturen verschwenden Ressourcen, was zu einer erhöhten Speichernutzung und redundanten Wartungsarbeiten führt. Konsolidiere überlappende Indizes immer in einen einzigen, gut gestalteten Index.

Probiere unseren Lernpfad für SQL Server-Entwickler/innen aus, um mehr über die Behebung von Indexproblemen in SQL Server zu erfahren und die Effizienz der Datenbank zu verbessern.

Fazit

Die Beherrschung von SQL Server-Indizes ist wichtig, wenn du die Datenbankleistung durch das Schreiben effizienter Abfragen verbessern willst. Wenn du mit Indizes arbeitest, solltest du auch die verschiedenen Arten von SQL Server-Indizes kennen, um deine Indizes für eine optimale Datenbankleistung zu verwalten.

Ich empfehle unseren Lernpfad zum Associate Data Analyst in SQL, um ein kompetenter Datenanalyst zu werden. In unserem Kurs Reporting in SQL lernst du außerdem, wie du komplexe Berichte und Dashboards für eine effektive Datenpräsentation erstellst. Wenn du dich auf ein Vorstellungsgespräch vorbereitest, bei dem du deine SQL Server-Kenntnisse unter Beweis stellen musst, empfehle ich dir unseren Blog Top 30 SQL Server Interview Questions, um zu üben und das Gespräch zu bestehen.