SQL
Indizes

Einführung

Bemerkungen

Index erstellen

CREATE INDEX ix_cars_employee_id ON Cars (EmployeeId);

Dadurch wird ein Index für die Spalte EmployeeId in der Tabelle Cars erstellt . Dieser Index verbessert die Geschwindigkeit von Abfragen, die den Server auffordern, nach Werten in EmployeeId zu sortieren oder auszuwählen, z. B. die folgenden:

SELECT * FROM Cars WHERE EmployeeId = 1

Der Index kann wie im Folgenden mehr als eine Spalte enthalten.

CREATE INDEX ix_cars_e_c_o_ids ON Cars (EmployeeId, CarId, OwnerId);

In diesem Fall wäre der Index für Abfragen hilfreich, die nach allen enthaltenen Spalten sortieren oder auswählen, wenn der Satz von Bedingungen auf dieselbe Weise angeordnet ist. Das bedeutet, dass beim Abrufen der Daten die Zeilen abgerufen werden können, die mithilfe des Index abgerufen werden sollen, anstatt die vollständige Tabelle zu durchsuchen.

Im folgenden Fall würde beispielsweise der zweite Index verwendet.

SELECT * FROM Cars WHERE EmployeeId = 1 Order by CarId DESC

Wenn sich die Reihenfolge unterscheidet, hat der Index jedoch nicht die gleichen Vorteile wie im Folgenden.

SELECT * FROM Cars WHERE OwnerId = 17 Order by CarId DESC

Der Index ist nicht so hilfreich, da die Datenbank den gesamten Index über alle Werte von EmployeeId und CarID abrufen muss, um herauszufinden, welche Elemente OwnerId = 17 .

(Der Index kann weiterhin verwendet werden; der Abfrageoptimierer kann jedoch feststellen, dass das Abrufen des Indexes und das Filtern nach OwnerId und das Abrufen nur der benötigten Zeilen schneller ist als das Abrufen der vollständigen Tabelle, insbesondere wenn die Tabelle groß ist.)

Gruppierte, eindeutige und sortierte Indizes

Indizes können mehrere Merkmale aufweisen, die entweder beim Erstellen oder durch Ändern vorhandener Indizes festgelegt werden können.

CREATE CLUSTERED INDEX ix_clust_employee_id ON Employees(EmployeeId, Email);  

Die obige SQL-Anweisung erstellt einen neuen Clustered-Index für Mitarbeiter. Clustered-Indizes sind Indizes, die die tatsächliche Struktur der Tabelle bestimmen. Die Tabelle selbst wird nach der Indexstruktur sortiert. Das heißt, es kann höchstens einen Clustered-Index für eine Tabelle geben. Wenn für die Tabelle bereits ein gruppierter Index vorhanden ist, schlägt die obige Anweisung fehl. (Tabellen ohne Clustered-Indizes werden auch als Heap-Speicher bezeichnet.)

CREATE UNIQUE INDEX uq_customers_email ON Customers(Email);

Dadurch wird ein eindeutiger Index für die Spalte E-Mail in der Tabelle Customers erstellt . Dieser Index sowie die Beschleunigung von Abfragen wie ein normaler Index erzwingen auch, dass jede E-Mail-Adresse in dieser Spalte eindeutig ist. Wenn eine Zeile mit einem nicht eindeutigen E - Mail- Wert eingefügt oder aktualisiert wird, schlägt das Einfügen oder Aktualisieren standardmäßig fehl.

CREATE UNIQUE INDEX ix_eid_desc ON Customers(EmployeeID);

Dadurch wird ein Index für Kunden erstellt, der auch eine Tabellenbeschränkung erstellt, wonach die EmployeeID eindeutig sein muss. (Dies schlägt fehl, wenn die Spalte derzeit nicht eindeutig ist - in diesem Fall, wenn Mitarbeiter eine ID gemeinsam nutzen.)

CREATE INDEX ix_eid_desc ON Customers(EmployeeID Desc);

Dadurch wird ein Index erstellt, der in absteigender Reihenfolge sortiert ist. Standardmäßig sind Indizes (zumindest im MSSQL-Server) aufsteigend, dies kann jedoch geändert werden.

Einfügen mit einem eindeutigen Index

UPDATE Customers SET Email = "[email protected]" WHERE id = 1;

Dies schlägt fehl, wenn für die E-Mail- Spalte von Kunden ein eindeutiger Index festgelegt ist. Für diesen Fall kann jedoch ein alternatives Verhalten definiert werden:

UPDATE Customers SET Email = "[email protected]" WHERE id = 1 ON DUPLICATE KEY; 

SAP ASE: Index löschen

Dieser Befehl löscht den Index in der Tabelle. Es funktioniert auf einem SAP ASE Server.

Syntax:

DROP INDEX [table name].[index name]

Beispiel:

DROP INDEX Cars.index_1

Sortierter Index

Wenn Sie einen Index verwenden, der so sortiert ist, wie Sie ihn abrufen würden, führt die SELECT Anweisung beim Abrufen keine zusätzliche Sortierung durch.

CREATE INDEX ix_scoreboard_score ON scoreboard (score DESC);

Wenn Sie die Abfrage ausführen

SELECT * FROM scoreboard ORDER BY score DESC;

Das Datenbanksystem führt keine zusätzliche Sortierung durch, da es in dieser Reihenfolge eine Indexsuche durchführen kann.

Einen Index löschen oder ihn deaktivieren und neu erstellen

DROP INDEX ix_cars_employee_id ON Cars;  

Wir können den Befehl DROP , um unseren Index zu löschen. In diesem Beispiel werden wir DROP den Index ix_cars_employee_id auf dem Tisch Autos genannt.

Dadurch wird der Index vollständig gelöscht. Wenn der Index in einem Cluster zusammengefasst ist, wird das Clustering entfernt. Es kann nicht neu erstellt werden, ohne den Index neu zu erstellen, was langsam und rechenintensiv sein kann. Alternativ kann der Index deaktiviert werden:

ALTER INDEX ix_cars_employee_id ON Cars DISABLE; 

Dadurch kann die Tabelle die Struktur zusammen mit den Metadaten zum Index beibehalten.

Kritisch bleibt dabei die Indexstatistik erhalten, so dass die Änderung leicht ausgewertet werden kann. Bei Bedarf kann der Index später erneut erstellt werden, anstatt ihn vollständig neu zu erstellen.

ALTER INDEX ix_cars_employee_id ON Cars REBUILD;

Eindeutiger Index, der NULL erlaubt

CREATE UNIQUE INDEX idx_license_id 
   ON Person(DrivingLicenseID) WHERE DrivingLicenseID IS NOT NULL
GO

Dieses Schema ermöglicht eine 0..1-Beziehung - Personen können keinen oder einen Führerschein haben und jeder Führerschein kann nur einer Person gehören

Index neu erstellen

Im Laufe der Zeit können B-Tree-Indizes aufgrund der Aktualisierung / Löschung / Einfügung von Daten fragmentiert werden. In der SQLServer-Terminologie können wir interne (Indexseite, die halb leer ist) und extern (logische Seitenreihenfolge entspricht nicht der physischen Reihenfolge) haben. Die Neuerstellung des Index ist dem Löschen und Neuerstellen des Index sehr ähnlich.

Wir können einen Index mit neu erstellen

ALTER INDEX index_name REBUILD; 

Standardmäßig ist der Index für die Neuerstellung ein Offline-Vorgang, durch den die Tabelle gesperrt und DML dagegen verhindert wird. In vielen RDBMS ist jedoch eine Online-Wiederherstellung möglich. Einige DB-Anbieter bieten auch Alternativen zur REORGANIZE von Indizes an, wie z. B. REORGANIZE (SQLServer) oder COALESCE / SHRINK SPACE (Oracle).

Clustered-Index

Bei Verwendung eines Clustered-Index werden die Zeilen der Tabelle nach der Spalte sortiert, auf die der Clustered-Index angewendet wird. Daher kann es nur einen Clusterindex für die Tabelle geben, da Sie die Tabelle nicht nach zwei verschiedenen Spalten sortieren können.

Im Allgemeinen ist es am besten, einen Clustered-Index zu verwenden, wenn Lesevorgänge für große Datentabellen ausgeführt werden. Der Vorteil des Clustered-Index liegt beim Schreiben in eine Tabelle, und Daten müssen neu organisiert (sortiert) werden.

Ein Beispiel zum Erstellen eines gruppierten Index für eine Tabelle Employees in der Spalte Employee_Surname:

CREATE CLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);  

Nicht gruppierter Index

Nicht gruppierte Indizes werden getrennt von der Tabelle gespeichert. Jeder Index in dieser Struktur enthält einen Zeiger auf die Zeile in der Tabelle, die er darstellt.

Diese Zeiger werden Zeilenlokatoren genannt. Die Struktur des Zeilenlokators hängt davon ab, ob die Datenseiten in einem Heap oder in einer Clustertabelle gespeichert sind. Bei einem Heap ist ein Zeilenlokator ein Zeiger auf die Zeile. Bei einer gruppierten Tabelle ist der Zeilenlokator der gruppierte Indexschlüssel.

Ein Beispiel zum Erstellen eines nicht gruppierten Index für die Tabelle Employees und die Spalte Employee_Surname:

CREATE NONCLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);  

Es können mehrere nicht gruppierte Indizes für die Tabelle vorhanden sein. Die Leseoperationen sind bei nicht gruppierten Indizes im Allgemeinen langsamer als bei gruppierten Indizes, da Sie zuerst zum Index und dann zur Tabelle gehen müssen. Es gibt jedoch keine Einschränkungen bei Schreibvorgängen.

Teilweiser oder gefilterter Index

SQL Server und SQLite ermöglichen das Erstellen von Indizes, die nicht nur eine Teilmenge von Spalten enthalten, sondern auch eine Teilmenge von Zeilen.

Betrachten Sie eine konstant wachsende Anzahl von Bestellungen, wobei order_state_id gleich beendete (2) ist, und eine stabile Anzahl von Bestellungen, wobei order_state_id equal gestartet ist (1).

Wenn Ihr Unternehmen Fragen wie diese verwendet:

SELECT id, comment
  FROM orders
 WHERE order_state_id =  1
   AND product_id = @some_value;

Mit der teilweisen Indizierung können Sie den Index einschränken, einschließlich nur der noch nicht abgeschlossenen Aufträge:

CREATE INDEX Started_Orders
          ON orders(product_id)
       WHERE order_state_id = 1;

Dieser Index ist kleiner als ein ungefilterter Index, wodurch Platz gespart und die Aktualisierungskosten des Index reduziert werden.