Indizes- Das ist das Erste, was Sie bei Ihrer Arbeit gut verstehen müssen SQL Server, aber seltsamerweise werden grundlegende Fragen in Foren nicht sehr oft gestellt und erhalten nicht viele Antworten.
Rob Sheldon beantwortet diese Fragen, die in Fachkreisen für Verwirrung über Indizes sorgen SQL Server: Bei manchen ist es uns einfach peinlich, sie zu fragen, aber bevor wir andere fragen, überlegen wir es uns erst einmal.
Verwendete Terminologie:
| Index | Index |
| Haufen | ein Bündel |
| Tisch | Tisch |
| Sicht | Leistung |
| B-Baum | ausgeglichener Baum |
| Clustered-Index | Clustered-Index |
| Nicht gruppierter Index | Nicht gruppierter Index |
| Composite-Index | Composite-Index |
| abdeckender Index | abdeckender Index |
| Primärschlüsseleinschränkung | Primärschlüsseleinschränkung |
| einzigartige Beschränkung | Einschränkung der Eindeutigkeit von Werten |
| Abfrage | Anfrage |
| Abfrage-Engine | Abfrage-Subsystem |
| Datenbank | Datenbank |
| Datenbank-Engine | Speichersubsystem |
| Füllfaktor | Indexfüllfaktor |
| Ersatz-Primärschlüssel | Ersatz-Primärschlüssel |
| Abfrageoptimierer | Abfrageoptimierer |
| Indexselektivität | Indexselektivität |
| gefilterter Index | Filterbarer Index |
| Ausführungsplan | Ausführungsplan |
Grundlagen von Indizes in SQL Server.
Eine der wichtigsten Möglichkeiten, eine hohe Produktivität zu erreichen SQL Server ist die Verwendung von Indizes. Ein Index beschleunigt den Abfrageprozess, indem er einen schnellen Zugriff auf Datenzeilen in einer Tabelle ermöglicht, ähnlich wie ein Index in einem Buch Ihnen dabei hilft, die benötigten Informationen schnell zu finden. In diesem Artikel gebe ich einen kurzen Überblick über Indizes in SQL Server und erläutern, wie sie in der Datenbank organisiert sind und wie sie dazu beitragen, Datenbankabfragen zu beschleunigen.
Indizes werden für Tabellen- und Ansichtsspalten erstellt. Indizes bieten eine Möglichkeit, Daten basierend auf den Werten in diesen Spalten schnell zu durchsuchen. Wenn Sie beispielsweise einen Index für einen Primärschlüssel erstellen und dann mithilfe der Primärschlüsselwerte nach einer Datenzeile suchen, dann SQL Server sucht zuerst den Indexwert und verwendet dann den Index, um schnell die gesamte Datenzeile zu finden. Ohne Index wird ein vollständiger Scan für alle Zeilen in der Tabelle durchgeführt, was erhebliche Auswirkungen auf die Leistung haben kann.
Sie können für die meisten Spalten einer Tabelle oder Ansicht einen Index erstellen. Die Ausnahme bilden hauptsächlich Spalten mit Datentypen zum Speichern großer Objekte ( LOB), solche wie Bild, Text oder varchar(max). Sie können auch Indizes für Spalten erstellen, die zum Speichern von Daten im Format dienen XML, aber diese Indizes sind etwas anders strukturiert als die Standardindizes und ihre Betrachtung würde den Rahmen dieses Artikels sprengen. Außerdem wird in dem Artikel nicht darauf eingegangen Columnstore Indizes. Stattdessen konzentriere ich mich auf die Indizes, die in Datenbanken am häufigsten verwendet werden SQL Server.
Ein Index besteht aus einer Reihe von Seiten, Indexknoten, die in einer Baumstruktur organisiert sind – ausgeglichener Baum. Diese Struktur ist hierarchischer Natur und beginnt mit einem Wurzelknoten oben in der Hierarchie und Blattknoten, den Blättern, unten, wie in der Abbildung dargestellt:
Wenn Sie eine indizierte Spalte abfragen, beginnt die Abfrage-Engine oben am Stammknoten und arbeitet sich nach unten durch die Zwischenknoten, wobei jede Zwischenschicht detailliertere Informationen zu den Daten enthält. Die Abfrage-Engine bewegt sich weiter durch die Indexknoten, bis sie mit den Indexblättern die unterste Ebene erreicht. Wenn Sie beispielsweise in einer indizierten Spalte nach dem Wert 123 suchen, ermittelt die Abfrage-Engine zunächst die Seite auf der ersten Zwischenebene auf der Stammebene. In diesem Fall zeigt die erste Seite auf einen Wert zwischen 1 und 100 und die zweite auf einen Wert zwischen 101 und 200, sodass die Abfrage-Engine auf die zweite Seite dieser Zwischenebene zugreift. Als nächstes werden Sie sehen, dass Sie zur dritten Seite der nächsten Zwischenstufe wechseln sollten. Von hier aus liest das Abfragesubsystem den Wert des Index selbst auf einer niedrigeren Ebene. Indexblätter können je nach Indextyp entweder die Tabellendaten selbst oder einfach einen Zeiger auf Zeilen mit Daten in der Tabelle enthalten: gruppiert oder nicht gruppiert.
Clustered-Index
Ein Clustered-Index speichert die tatsächlichen Datenzeilen in den Blättern des Index. Zurück zum vorherigen Beispiel bedeutet dies, dass die mit dem Schlüsselwert 123 verknüpfte Datenzeile im Index selbst gespeichert wird. Ein wichtiges Merkmal eines Clustered-Index besteht darin, dass alle Werte in einer bestimmten Reihenfolge sortiert werden, entweder aufsteigend oder absteigend. Daher kann eine Tabelle oder Ansicht nur einen Clustered-Index haben. Darüber hinaus ist zu beachten, dass Daten in einer Tabelle nur dann in sortierter Form gespeichert werden, wenn für diese Tabelle ein Clustered-Index erstellt wurde.
Eine Tabelle, die keinen Clustered-Index hat, wird als Heap bezeichnet.
Nicht gruppierter Index
Im Gegensatz zu einem Clustered-Index enthalten die Blätter eines Nonclustered-Index nur die Spalten ( Schlüssel), anhand derer dieser Index bestimmt wird, und enthält außerdem einen Zeiger auf Zeilen mit echten Daten in der Tabelle. Dies bedeutet, dass das Unterabfragesystem einen zusätzlichen Vorgang erfordert, um die erforderlichen Daten zu finden und abzurufen. Der Inhalt des Datenzeigers hängt davon ab, wie die Daten gespeichert werden: Clustertabelle oder Heap. Wenn ein Zeiger auf eine gruppierte Tabelle zeigt, zeigt er auf einen gruppierten Index, der zum Auffinden der tatsächlichen Daten verwendet werden kann. Wenn sich ein Zeiger auf einen Heap bezieht, zeigt er auf eine bestimmte Datenzeilenkennung. Nicht gruppierte Indizes können nicht wie gruppierte Indizes sortiert werden, Sie können jedoch mehr als einen nicht gruppierten Index für eine Tabelle oder Ansicht erstellen, bis zu 999. Dies bedeutet nicht, dass Sie so viele Indizes wie möglich erstellen sollten. Indizes können die Systemleistung entweder verbessern oder verschlechtern. Zusätzlich zur Möglichkeit, mehrere nicht gruppierte Indizes zu erstellen, können Sie auch zusätzliche Spalten einschließen ( enthaltene Spalte) in seinen Index: Die Blätter des Index speichern nicht nur den Wert der indizierten Spalten selbst, sondern auch die Werte dieser nicht indizierten zusätzlichen Spalten. Mit diesem Ansatz können Sie einige der dem Index auferlegten Einschränkungen umgehen. Sie können beispielsweise eine nicht indizierbare Spalte einschließen oder die Indexlängenbeschränkung (in den meisten Fällen 900 Byte) umgehen.
Arten von Indizes
Er ist nicht nur ein gruppierter oder nicht gruppierter Index, sondern kann auch als zusammengesetzter Index, eindeutiger Index oder abdeckender Index konfiguriert werden.
Composite-Index
Ein solcher Index kann mehr als eine Spalte enthalten. Sie können bis zu 16 Spalten in einen Index aufnehmen, ihre Gesamtlänge ist jedoch auf 900 Byte begrenzt. Sowohl gruppierte als auch nicht gruppierte Indizes können zusammengesetzt sein.
Eindeutiger Index
Dieser Index stellt sicher, dass jeder Wert in der indizierten Spalte eindeutig ist. Wenn der Index zusammengesetzt ist, gilt die Eindeutigkeit für alle Spalten im Index, jedoch nicht für jede einzelne Spalte. Wenn Sie beispielsweise einen eindeutigen Index für die Spalten erstellen NAME Und NACHNAME, dann muss der vollständige Name eindeutig sein, Duplikate im Vor- oder Nachnamen sind jedoch möglich.
Ein eindeutiger Index wird automatisch erstellt, wenn Sie eine Spalteneinschränkung definieren: Primärschlüssel oder Eindeutigkeitswert-Einschränkung:
- Primärschlüssel
Wenn Sie dann eine Primärschlüsseleinschränkung für eine oder mehrere Spalten definieren SQL Server erstellt automatisch einen eindeutigen Clustered-Index, wenn zuvor noch kein Clustered-Index erstellt wurde (in diesem Fall wird ein eindeutiger Nicht-Clustered-Index für den Primärschlüssel erstellt). - Einzigartigkeit der Werte
Wenn Sie dann eine Einschränkung für die Eindeutigkeit von Werten definieren SQL Server erstellt automatisch einen eindeutigen, nicht gruppierten Index. Sie können angeben, dass ein eindeutiger Clustered-Index erstellt wird, wenn noch kein Clustered-Index für die Tabelle erstellt wurde
Abdeckindex
Ein solcher Index ermöglicht es einer bestimmten Abfrage, sofort alle erforderlichen Daten aus den Blättern des Index abzurufen, ohne zusätzlich auf die Datensätze der Tabelle selbst zugreifen zu müssen.
Entwerfen von Indizes
So nützlich Indizes auch sein können, sie müssen sorgfältig entworfen werden. Da Indizes viel Speicherplatz beanspruchen können, sollten Sie nicht mehr Indizes als nötig erstellen. Darüber hinaus werden Indizes automatisch aktualisiert, wenn die Datenzeile selbst aktualisiert wird, was zu zusätzlichem Ressourcenaufwand und Leistungseinbußen führen kann. Beim Entwerfen von Indizes müssen verschiedene Überlegungen zur Datenbank und zu Abfragen berücksichtigt werden.
Datenbank
Wie bereits erwähnt, können Indizes die Systemleistung verbessern, weil Sie bieten der Abfrage-Engine eine schnelle Möglichkeit, Daten zu finden. Sie sollten jedoch auch berücksichtigen, wie oft Sie Daten einfügen, aktualisieren oder löschen möchten. Wenn Sie Daten ändern, müssen auch die Indizes geändert werden, um die entsprechenden Aktionen für die Daten widerzuspiegeln, was die Systemleistung erheblich beeinträchtigen kann. Berücksichtigen Sie bei der Planung Ihrer Indexierungsstrategie die folgenden Richtlinien:
- Verwenden Sie für Tabellen, die häufig aktualisiert werden, so wenige Indizes wie möglich.
- Wenn die Tabelle eine große Datenmenge enthält, die Änderungen jedoch geringfügig sind, verwenden Sie so viele Indizes wie nötig, um die Leistung Ihrer Abfragen zu verbessern. Denken Sie jedoch sorgfältig darüber nach, bevor Sie Indizes für kleine Tabellen verwenden, denn ... Es ist möglich, dass die Verwendung einer Indexsuche länger dauert als das einfache Durchsuchen aller Zeilen.
- Versuchen Sie bei Clustered-Indizes, die Felder so kurz wie möglich zu halten. Der beste Ansatz besteht darin, einen Clustered-Index für Spalten zu verwenden, die eindeutige Werte haben und NULL nicht zulassen. Aus diesem Grund wird ein Primärschlüssel häufig als Clustered-Index verwendet.
- Die Eindeutigkeit der Werte in einer Spalte beeinflusst die Leistung des Index. Im Allgemeinen gilt: Je mehr Duplikate eine Spalte enthält, desto schlechter ist die Leistung des Index. Andererseits ist die Performance des Index umso besser, je mehr eindeutige Werte vorhanden sind. Verwenden Sie nach Möglichkeit einen eindeutigen Index.
- Berücksichtigen Sie bei einem zusammengesetzten Index die Reihenfolge der Spalten im Index. Spalten, die in Ausdrücken verwendet werden WO(z.B, WHERE FirstName = 'Charlie') muss im Index an erster Stelle stehen. Nachfolgende Spalten sollten nach der Eindeutigkeit ihrer Werte aufgelistet werden (Spalten mit der höchsten Anzahl eindeutiger Werte stehen an erster Stelle).
- Sie können auch einen Index für berechnete Spalten angeben, wenn diese bestimmte Anforderungen erfüllen. Beispielsweise müssen Ausdrücke, die zum Erhalten des Werts einer Spalte verwendet werden, deterministisch sein (immer das gleiche Ergebnis für einen bestimmten Satz von Eingabeparametern zurückgeben).
Datenbankabfragen
Eine weitere Überlegung beim Entwerfen von Indizes ist, welche Abfragen für die Datenbank ausgeführt werden. Wie bereits erwähnt, müssen Sie berücksichtigen, wie oft sich die Daten ändern. Darüber hinaus sollten die folgenden Grundsätze angewendet werden:
- Versuchen Sie, so viele Zeilen wie möglich in einer Abfrage einzufügen oder zu ändern, anstatt dies in mehreren Einzelabfragen zu tun.
- Erstellen Sie einen nicht gruppierten Index für Spalten, die häufig als Suchbegriffe in Ihren Abfragen verwendet werden. WO und Verbindungen in VERBINDEN.
- Erwägen Sie die Indizierung von Spalten, die in Zeilensuchabfragen verwendet werden, um exakte Wertübereinstimmungen zu erzielen.
Warum kann eine Tabelle nicht zwei Clustered-Indizes haben?
Möchten Sie eine kurze Antwort? Ein Clustered-Index ist eine Tabelle. Wenn Sie einen Clustered-Index für eine Tabelle erstellen, sortiert die Speicher-Engine alle Zeilen in der Tabelle entsprechend der Indexdefinition in aufsteigender oder absteigender Reihenfolge. Ein Clustered-Index ist keine separate Einheit wie andere Indizes, sondern ein Mechanismus zum Sortieren von Daten in einer Tabelle und zum Ermöglichen des schnellen Zugriffs auf Datenzeilen.
Stellen Sie sich vor, Sie hätten eine Tabelle mit der Historie der Verkaufstransaktionen. Die Verkaufstabelle enthält Informationen wie Bestell-ID, Produktposition in der Bestellung, Produktnummer, Produktmenge, Bestellnummer und -datum usw. Sie erstellen einen Clustered-Index für Spalten Auftragsnummer Und Leitungs-ID, aufsteigend sortiert, wie im Folgenden dargestellt T-SQL Code:
Wenn Sie dieses Skript ausführen, werden alle Zeilen in der Tabelle zuerst physisch nach der OrderID-Spalte und dann nach LineID sortiert, die Daten selbst bleiben jedoch in einem einzigen logischen Block, der Tabelle. Aus diesem Grund können Sie nicht zwei Clustered-Indizes erstellen. Es kann nur eine Tabelle mit einem Datenwert geben und diese Tabelle kann nur einmal in einer bestimmten Reihenfolge sortiert werden.
Wenn eine gruppierte Tabelle viele Vorteile bietet, warum dann einen Heap verwenden?
Sie haben Recht. Clustered-Tabellen sind großartig und die meisten Ihrer Abfragen werden bei Tabellen mit einem Clustered-Index besser funktionieren. In manchen Fällen möchten Sie die Tische jedoch möglicherweise in ihrem natürlichen, makellosen Zustand belassen, d. h. in Form eines Heaps und erstellen Sie nur nicht gruppierte Indizes, um Ihre Abfragen am Laufen zu halten.
Wie Sie sich erinnern, speichert der Heap Daten in zufälliger Reihenfolge. Normalerweise fügt das Speichersubsystem Daten in der Reihenfolge zu einer Tabelle hinzu, in der sie eingefügt werden, aber das Speichersubsystem verschiebt Zeilen auch gerne, um die Speicherung effizienter zu gestalten. Dadurch haben Sie keine Möglichkeit vorherzusagen, in welcher Reihenfolge die Daten gespeichert werden.
Wenn die Abfrage-Engine Daten ohne die Vorteile eines nicht gruppierten Indexes finden muss, führt sie einen vollständigen Scan der Tabelle durch, um die benötigten Zeilen zu finden. Bei sehr kleinen Tabellen ist dies normalerweise kein Problem, aber mit zunehmender Größe des Heaps nimmt die Leistung schnell ab. Natürlich kann ein nicht gruppierter Index hilfreich sein, indem er einen Zeiger auf die Datei, Seite und Zeile verwendet, in der die erforderlichen Daten gespeichert sind – dies ist normalerweise eine viel bessere Alternative zu einem Tabellenscan. Dennoch ist es schwierig, die Vorteile eines Clustered-Index zu vergleichen, wenn man die Abfrageleistung berücksichtigt.
Allerdings kann der Heap in bestimmten Situationen dazu beitragen, die Leistung zu verbessern. Stellen Sie sich eine Tabelle mit vielen Einfügungen, aber wenigen Aktualisierungen oder Löschungen vor. Beispielsweise wird eine Tabelle, in der ein Protokoll gespeichert wird, hauptsächlich zum Einfügen von Werten verwendet, bis sie archiviert wird. Auf dem Heap kommt es nicht zu Paging und Datenfragmentierung wie bei einem Clustered-Index, da die Zeilen einfach am Ende des Heaps hinzugefügt werden. Eine zu starke Aufteilung der Seiten kann erhebliche Auswirkungen auf die Leistung haben, und zwar nicht im positiven Sinne. Im Allgemeinen ermöglicht Ihnen der Heap das relativ problemlose Einfügen von Daten und Sie müssen sich nicht mit dem Speicher- und Wartungsaufwand befassen, der bei einem Clustered-Index anfällt.
Die mangelnde Aktualisierung und Löschung von Daten sollte jedoch nicht als einziger Grund angesehen werden. Auch die Art und Weise der Datenerhebung ist ein wichtiger Faktor. Sie sollten beispielsweise keinen Heap verwenden, wenn Sie häufig Datenbereiche abfragen oder die abgefragten Daten häufig sortiert oder gruppiert werden müssen.
Dies bedeutet lediglich, dass Sie die Verwendung des Heaps nur dann in Betracht ziehen sollten, wenn Sie mit sehr kleinen Tabellen arbeiten oder sich Ihre gesamte Interaktion mit der Tabelle auf das Einfügen von Daten beschränkt und Ihre Abfragen äußerst einfach sind (und Sie nicht gruppierte Indizes verwenden). Trotzdem). Ansonsten bleiben Sie bei einem gut gestalteten Clustered-Index, etwa einem, der auf einem einfachen aufsteigenden Schlüsselfeld definiert ist, etwa einer weit verbreiteten Spalte mit IDENTITÄT.
Wie ändere ich den standardmäßigen Indexfüllfaktor?
Das Ändern des standardmäßigen Indexfüllfaktors ist eine Sache. Zu verstehen, wie das Standardverhältnis funktioniert, ist eine andere Sache. Aber gehen Sie zunächst ein paar Schritte zurück. Der Indexfüllfaktor bestimmt den Platz auf einer Seite, um den Index auf der untersten Ebene (Blattebene) zu speichern, bevor mit dem Füllen einer neuen Seite begonnen wird. Wenn der Koeffizient beispielsweise auf 90 eingestellt ist, nimmt der Index bei steigendem Index 90 % der Seite ein und wechselt dann zur nächsten Seite.
Standardmäßig ist der Indexfüllfaktorwert in SQL Server ist 0, was dasselbe wie 100 ist. Daher erben alle neuen Indizes automatisch diese Einstellung, es sei denn, Sie geben in Ihrem Code ausdrücklich einen Wert an, der vom Systemstandardwert abweicht, oder ändern das Standardverhalten. Sie können verwenden SQL Server Management Studio um den Standardwert anzupassen oder eine gespeicherte Systemprozedur auszuführen sp_configure. Zum Beispiel der folgende Satz T-SQL Befehle setzen den Koeffizientenwert auf 90 (Sie müssen zuerst in den erweiterten Einstellungsmodus wechseln):
EXEC sp_configure "erweiterte Optionen anzeigen ", 1; GO RECONFIGURE; GO EXEC sp_configure " Füllfaktor", 90; GO NEU KONFIGURIEREN; GO
Nachdem Sie den Wert des Indexfüllfaktors geändert haben, müssen Sie den Dienst neu starten SQL Server. Sie können den eingestellten Wert jetzt überprüfen, indem Sie sp_configure ohne das angegebene zweite Argument ausführen:
EXEC sp_configure „Füllfaktor“ GO
Dieser Befehl sollte einen Wert von 90 zurückgeben. Daher verwenden alle neu erstellten Indizes diesen Wert. Sie können dies testen, indem Sie einen Index erstellen und den Füllfaktorwert abfragen:
VERWENDEN Sie AdventureWorks2012; -- Ihre Datenbank GO CREATE NONCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName); GO SELECT fill_factor FROM sys .indexes WHERE object_id = object_id("Person.Person" ) AND name ="ix_people_lastname" ;In diesem Beispiel haben wir einen nicht gruppierten Index für eine Tabelle erstellt Person in der Datenbank AdventureWorks2012. Nachdem wir den Index erstellt haben, können wir den Füllfaktorwert aus den Systemtabellen sys.indexes abrufen. Die Abfrage sollte 90 zurückgeben.
Stellen wir uns jedoch vor, dass wir den Index gelöscht und erneut erstellt haben, jetzt aber einen bestimmten Füllfaktorwert angegeben haben:
Dieses Mal haben wir Anweisungen hinzugefügt MIT und Option Füllfaktor für unsere Indexerstellung INDEX ERSTELLEN und den Wert 80 angegeben. Operator WÄHLEN gibt nun den entsprechenden Wert zurück.
Bisher war alles ziemlich einfach. Wenn Sie in diesem gesamten Prozess einen Index erstellen, der einen Standardkoeffizientenwert verwendet, können Sie sich wirklich verbrennen, vorausgesetzt, Sie kennen diesen Wert. Beispielsweise bastelt jemand an den Servereinstellungen herum und ist so stur, dass er den Indexfüllfaktor auf 20 setzt. In der Zwischenzeit erstellen Sie weiterhin Indizes, vorausgesetzt, der Standardwert ist 0. Leider haben Sie keine Möglichkeit, die Füllung herauszufinden Faktor bis, solange Sie keinen Index erstellen und dann den Wert überprüfen, wie wir es in unseren Beispielen getan haben. Andernfalls müssen Sie auf den Moment warten, in dem die Abfrageleistung so stark abnimmt, dass Sie anfangen, etwas zu vermuten.
Ein weiteres Problem, das Sie beachten sollten, ist die Neuerstellung von Indizes. Wie beim Erstellen eines Index können Sie den Indexfüllfaktorwert angeben, wenn Sie ihn neu erstellen. Im Gegensatz zum Befehl „create index“ verwendet Rebuild jedoch nicht die Standardeinstellungen des Servers, auch wenn es den Anschein hat. Dies gilt umso mehr, wenn Sie den Wert des Indexfüllfaktors nicht speziell angeben SQL Server wird den Wert des Koeffizienten verwenden, mit dem dieser Index vor seiner Umstrukturierung existierte. Zum Beispiel der folgende Vorgang INDEX ÄNDERN baut den gerade erstellten Index neu auf:
Wenn wir den Füllfaktorwert überprüfen, erhalten wir einen Wert von 80, da wir diesen Wert bei der letzten Indexerstellung angegeben haben. Der Standardwert wird ignoriert.
Wie Sie sehen, ist das Ändern des Indexfüllfaktorwerts nicht so schwierig. Es ist viel schwieriger, den aktuellen Wert zu kennen und zu verstehen, wann er angewendet wird. Wenn Sie den Koeffizienten beim Erstellen und Neuerstellen von Indizes immer konkret angeben, kennen Sie immer das konkrete Ergebnis. Es sei denn, Sie müssen sich Sorgen machen, dass nicht jemand anderes die Servereinstellungen erneut verfälscht, was zur Folge hat, dass alle Indizes mit einem lächerlich niedrigen Indexfüllfaktor neu erstellt werden.
Ist es möglich, einen Clustered-Index für eine Spalte zu erstellen, die Duplikate enthält?
Ja und nein. Ja, Sie können einen Clustered-Index für eine Schlüsselspalte erstellen, die doppelte Werte enthält. Nein, der Wert einer Schlüsselspalte darf nicht in einem nicht eindeutigen Zustand bleiben. Lassen Sie mich erklären. Wenn Sie einen nicht eindeutigen Clustered-Index für eine Spalte erstellen, fügt die Speicher-Engine dem doppelten Wert einen Eindeutiger hinzu, um die Eindeutigkeit sicherzustellen und somit jede Zeile in der Cluster-Tabelle identifizieren zu können.
Beispielsweise könnten Sie beschließen, einen gruppierten Index für eine Spalte mit Kundendaten zu erstellen Familienname, Nachname den Nachnamen behalten. Die Spalte enthält die Werte Franklin, Hancock, Washington und Smith. Anschließend fügt man noch einmal die Werte Adams, Hancock, Smith und Smith ein. Da der Wert der Schlüsselspalte jedoch eindeutig sein muss, ändert die Speicher-Engine den Wert der Duplikate so, dass sie etwa so aussehen: Adams, Franklin, Hancock, Hancock1234, Washington, Smith, Smith4567 und Smith5678.
Auf den ersten Blick scheint dieser Ansatz in Ordnung zu sein, aber ein ganzzahliger Wert erhöht die Größe des Schlüssels, was bei einer großen Anzahl von Duplikaten zum Problem werden kann und diese Werte zur Grundlage eines nicht gruppierten Index oder eines Fremdindex werden Schlüsselreferenz. Aus diesen Gründen sollten Sie nach Möglichkeit immer versuchen, eindeutige Clustered-Indizes zu erstellen. Wenn dies nicht möglich ist, versuchen Sie zumindest, Spalten mit einem sehr hohen eindeutigen Wertinhalt zu verwenden.
Wie wird die Tabelle gespeichert, wenn kein Clustered-Index erstellt wurde?
SQL Server unterstützt zwei Arten von Tabellen: gruppierte Tabellen mit einem gruppierten Index und Heap-Tabellen oder nur Heaps. Im Gegensatz zu Cluster-Tabellen werden die Daten auf dem Heap in keiner Weise sortiert. Im Wesentlichen handelt es sich hierbei um einen Datenhaufen. Wenn Sie einer solchen Tabelle eine Zeile hinzufügen, hängt die Speicher-Engine diese einfach am Ende der Seite an. Wenn die Seite mit Daten gefüllt ist, werden sie einer neuen Seite hinzugefügt. In den meisten Fällen möchten Sie einen Clustered-Index für eine Tabelle erstellen, um die Sortierbarkeit und Abfragegeschwindigkeit zu nutzen (stellen Sie sich vor, Sie suchen nach einer Telefonnummer in einem unsortierten Adressbuch). Wenn Sie jedoch keinen Clustered-Index erstellen möchten, können Sie dennoch einen Nonclustered-Index auf dem Heap erstellen. In diesem Fall verfügt jede Indexzeile über einen Zeiger auf eine Heapzeile. Der Index umfasst die Datei-ID, die Seitennummer und die Datenzeilennummer.
Welche Beziehung besteht zwischen Werteindeutigkeitsbeschränkungen und einem Primärschlüssel mit Tabellenindizes?
Ein Primärschlüssel und eine eindeutige Einschränkung stellen sicher, dass die Werte in einer Spalte eindeutig sind. Sie können für eine Tabelle nur einen Primärschlüssel erstellen und dieser darf keine Werte enthalten NULL. Sie können mehrere Einschränkungen für die Eindeutigkeit eines Werts für eine Tabelle erstellen, und jede davon kann einen einzelnen Datensatz haben NULL.
Wenn Sie einen Primärschlüssel erstellen, erstellt die Speicher-Engine auch einen eindeutigen Clustered-Index, sofern noch kein Clustered-Index erstellt wurde. Sie können das Standardverhalten jedoch überschreiben und es wird ein nicht gruppierter Index erstellt. Wenn beim Erstellen des Primärschlüssels ein gruppierter Index vorhanden ist, wird ein eindeutiger nicht gruppierter Index erstellt.
Wenn Sie eine eindeutige Einschränkung erstellen, erstellt die Speicher-Engine einen eindeutigen, nicht gruppierten Index. Sie können jedoch die Erstellung eines eindeutigen Clustered-Index angeben, sofern noch keiner erstellt wurde.
Im Allgemeinen sind eine Eindeutigkeitswertbeschränkung und ein Eindeutigkeitsindex dasselbe.
Warum werden gruppierte und nicht gruppierte Indizes in SQL Server als B-Tree bezeichnet?
Grundlegende Indizes in SQL Server, gruppiert oder nicht gruppiert, werden über Gruppen von Seiten verteilt, die als Indexknoten bezeichnet werden. Diese Seiten sind in einer bestimmten Hierarchie mit einer Baumstruktur organisiert, die als ausgeglichener Baum bezeichnet wird. Auf der obersten Ebene befindet sich der Wurzelknoten, auf der unteren die Blattknoten, mit Zwischenknoten zwischen der oberen und der unteren Ebene, wie in der Abbildung dargestellt:
Der Stammknoten stellt den Haupteinstiegspunkt für Abfragen dar, die versuchen, Daten über den Index abzurufen. Ausgehend von diesem Knoten initiiert die Abfrage-Engine eine Navigation entlang der hierarchischen Struktur zum entsprechenden Blattknoten, der die Daten enthält.
Stellen Sie sich zum Beispiel vor, dass eine Anfrage zur Auswahl von Zeilen mit einem Schlüsselwert von 82 eingegangen ist. Das Abfragesubsystem beginnt mit der Arbeit am Wurzelknoten, der auf einen geeigneten Zwischenknoten verweist, in unserem Fall 1-100. Vom Zwischenknoten 1-100 erfolgt ein Übergang zum Knoten 51-100 und von dort zum Endknoten 76-100. Wenn es sich um einen Clustered-Index handelt, enthält das Knotenblatt die Daten der Zeile, die dem Schlüssel gleich 82 zugeordnet ist. Wenn es sich um einen Nicht-Clustered-Index handelt, enthält das Indexblatt einen Zeiger auf die Clustered-Tabelle oder eine bestimmte Zeile in der Haufen.
Wie kann ein Index die Abfrageleistung überhaupt verbessern, wenn Sie alle diese Indexknoten durchlaufen müssen?
Erstens verbessern Indizes nicht immer die Leistung. Zu viele falsch erstellte Indizes verwandeln das System in einen Sumpf und beeinträchtigen die Abfrageleistung. Genauer gesagt lässt sich sagen, dass Indizes bei sorgfältiger Anwendung erhebliche Leistungssteigerungen bewirken können.
Stellen Sie sich ein riesiges Buch zum Thema Leistungsoptimierung vor SQL Server(Papierversion, keine elektronische Version). Stellen Sie sich vor, Sie möchten Informationen zur Konfiguration von Resource Governor finden. Sie können mit dem Finger Seite für Seite durch das gesamte Buch ziehen oder das Inhaltsverzeichnis öffnen und die genaue Seitenzahl mit den gesuchten Informationen herausfinden (vorausgesetzt, das Buch ist korrekt indexiert und die Inhalte verfügen über die richtigen Indexe). Dies spart Ihnen sicherlich viel Zeit, auch wenn Sie zunächst auf eine völlig andere Struktur (den Index) zugreifen müssen, um die benötigten Informationen aus der Primärstruktur (dem Buch) zu erhalten.
Wie ein Buchindex, ein Index in SQL Server ermöglicht Ihnen die Durchführung präziser Abfragen der benötigten Daten, anstatt alle in einer Tabelle enthaltenen Daten vollständig zu scannen. Bei kleinen Tabellen ist ein vollständiger Scan in der Regel kein Problem, große Tabellen nehmen jedoch viele Datenseiten in Anspruch, was zu einer erheblichen Ausführungszeit der Abfrage führen kann, sofern kein Index vorhanden ist, der es der Abfrage-Engine ermöglicht, sofort den richtigen Speicherort der Daten zu ermitteln. Stellen Sie sich vor, Sie verirren sich ohne Karte an einer mehrstöckigen Straßenkreuzung vor einer großen Metropole und Sie werden auf die Idee kommen.
Wenn Indizes so großartig sind, warum erstellen Sie dann nicht einfach einen für jede Spalte?
Keine gute Tat sollte ungestraft bleiben. Zumindest ist das bei Indizes der Fall. Natürlich funktionieren Indizes hervorragend, solange Sie Operator-Abrufabfragen ausführen WÄHLEN, aber sobald häufige Anrufe bei den Betreibern beginnen EINFÜGEN, AKTUALISIEREN Und LÖSCHEN, sodass sich die Landschaft sehr schnell ändert.
Wenn Sie eine Datenanfrage durch den Betreiber initiieren WÄHLEN, findet die Abfrage-Engine den Index, durchläuft seine Baumstruktur und entdeckt die gesuchten Daten. Was könnte einfacher sein? Aber die Dinge ändern sich, wenn Sie eine Änderungserklärung initiieren wie AKTUALISIEREN. Ja, für den ersten Teil der Anweisung kann die Abfrage-Engine erneut den Index verwenden, um die geänderte Zeile zu finden – das sind gute Nachrichten. Und wenn es eine einfache Datenänderung in einer Zeile gibt, die sich nicht auf Änderungen in Schlüsselspalten auswirkt, ist der Änderungsprozess völlig schmerzlos. Was aber, wenn die Änderung dazu führt, dass die Seiten, die die Daten enthalten, aufgeteilt werden oder der Wert einer Schlüsselspalte geändert wird, wodurch diese auf einen anderen Indexknoten verschoben wird? Dies führt dazu, dass der Index möglicherweise eine Neuorganisation benötigt, die sich auf alle zugehörigen Indizes und Vorgänge auswirkt , was zu einem weit verbreiteten Rückgang der Produktivität führte.
Ähnliche Vorgänge laufen beim Anruf eines Operators ab LÖSCHEN. Ein Index kann dabei helfen, die zu löschenden Daten zu finden, aber das Löschen der Daten selbst kann zu einer Neuordnung der Seite führen. Bezüglich des Betreibers EINFÜGEN, der Hauptfeind aller Indizes: Sie beginnen, eine große Datenmenge hinzuzufügen, was zu Änderungen in den Indizes und deren Neuorganisation führt und alle darunter leiden.
Berücksichtigen Sie daher die Arten von Abfragen an Ihre Datenbank, wenn Sie darüber nachdenken, welche Art von Indizes und wie viele Sie erstellen möchten. Mehr bedeutet nicht besser. Berücksichtigen Sie vor dem Hinzufügen eines neuen Index zu einer Tabelle nicht nur die Kosten für die zugrunde liegenden Abfragen, sondern auch den verbrauchten Speicherplatz sowie die Kosten für die Aufrechterhaltung von Funktionen und Indizes, was zu einem Dominoeffekt auf andere Vorgänge führen kann. Ihre Indexentwurfsstrategie ist einer der wichtigsten Aspekte Ihrer Implementierung und sollte viele Überlegungen umfassen, von der Größe des Index über die Anzahl der eindeutigen Werte bis hin zur Art der Abfragen, die der Index unterstützt.
Ist es notwendig, einen Clustered-Index für eine Spalte mit einem Primärschlüssel zu erstellen?
Sie können einen Clustered-Index für jede Spalte erstellen, die die erforderlichen Bedingungen erfüllt. Es ist wahr, dass ein Clustered-Index und eine Primärschlüsseleinschränkung füreinander geschaffen sind und eine himmlische Übereinstimmung darstellen. Verstehen Sie also, dass beim Erstellen eines Primärschlüssels automatisch ein Clustered-Index erstellt wird, wenn noch keiner vorhanden ist zuvor erstellt. Sie können jedoch entscheiden, dass ein Clustered-Index anderswo eine bessere Leistung erbringen würde, und oft wird Ihre Entscheidung gerechtfertigt sein.
Der Hauptzweck eines Clustered-Index besteht darin, alle Zeilen in Ihrer Tabelle basierend auf der Schlüsselspalte zu sortieren, die beim Definieren des Index angegeben wurde. Dies ermöglicht eine schnelle Suche und einen einfachen Zugriff auf Tabellendaten.
Der Primärschlüssel einer Tabelle kann eine gute Wahl sein, da er jede Zeile in Tabellen eindeutig identifiziert, ohne dass zusätzliche Daten hinzugefügt werden müssen. In einigen Fällen ist ein Ersatz-Primärschlüssel die beste Wahl, der nicht nur eindeutig, sondern auch klein ist und dessen Werte sequentiell ansteigen, wodurch nicht gruppierte Indizes, die auf diesem Wert basieren, effizienter werden. Auch dem Abfrageoptimierer gefällt diese Kombination aus einem Clustered-Index und einem Primärschlüssel, da das Zusammenführen von Tabellen schneller ist als das Zusammenführen auf andere Weise, bei der kein Primärschlüssel und der zugehörige Clustered-Index verwendet werden. Wie ich schon sagte, es ist eine himmlische Verbindung.
Abschließend ist jedoch zu beachten, dass bei der Erstellung eines Clustered-Index mehrere Aspekte zu berücksichtigen sind: Wie viele Nicht-Cluster-Indizes werden darauf basieren, wie oft ändert sich der Wert der Schlüsselindexspalte und wie groß. Wenn sich die Werte in den Spalten eines Clustered-Index ändern oder der Index nicht wie erwartet funktioniert, können alle anderen Indizes in der Tabelle betroffen sein. Ein Clustered-Index sollte auf der beständigsten Spalte basieren, deren Werte in einer bestimmten Reihenfolge zunehmen, sich aber nicht zufällig ändern. Der Index muss Abfragen für die Daten der Tabelle unterstützen, auf die am häufigsten zugegriffen wird, damit die Abfragen die Tatsache voll ausnutzen, dass die Daten an den Stammknoten, den Blättern des Index, sortiert und zugänglich sind. Wenn der Primärschlüssel zu diesem Szenario passt, verwenden Sie ihn. Wenn nicht, wählen Sie einen anderen Spaltensatz.
Was passiert, wenn Sie eine Ansicht indizieren, ist es dann immer noch eine Ansicht?
Eine Ansicht ist eine virtuelle Tabelle, die Daten aus einer oder mehreren Tabellen generiert. Im Wesentlichen handelt es sich um eine benannte Abfrage, die Daten aus den zugrunde liegenden Tabellen abruft, wenn Sie diese Ansicht abfragen. Sie können die Abfrageleistung verbessern, indem Sie in dieser Ansicht einen gruppierten Index und nicht gruppierte Indizes erstellen, ähnlich wie Sie Indizes für eine Tabelle erstellen. Der größte Nachteil besteht jedoch darin, dass Sie zuerst einen gruppierten Index erstellen und dann einen nicht gruppierten Index erstellen können.
Wenn eine indizierte Ansicht (materialisierte Ansicht) erstellt wird, bleibt die Ansichtsdefinition selbst eine separate Einheit. Dies ist schließlich nur ein fest codierter Operator WÄHLEN, in der Datenbank gespeichert. Aber der Index ist eine ganz andere Geschichte. Wenn Sie einen Clustered- oder Nonclustered-Index für einen Anbieter erstellen, werden die Daten wie bei einem regulären Index physisch auf der Festplatte gespeichert. Wenn sich Daten in zugrunde liegenden Tabellen ändern, ändert sich außerdem automatisch der Index der Ansicht (das bedeutet, dass Sie die Indizierung von Ansichten für Tabellen, die sich häufig ändern, möglicherweise vermeiden sollten). In jedem Fall bleibt die Ansicht eine Ansicht – ein Blick auf die Tabellen, aber eine, die im Moment ausgeführt wird, mit entsprechenden Indizes.
Bevor Sie einen Index für eine Ansicht erstellen können, muss diese mehrere Einschränkungen erfüllen. Beispielsweise kann eine Ansicht nur auf Basistabellen verweisen, nicht jedoch auf andere Ansichten, und diese Tabellen müssen sich in derselben Datenbank befinden. Es gibt tatsächlich noch viele andere Einschränkungen, also schauen Sie unbedingt in der Dokumentation nach SQL Server für all die schmutzigen Details.
Warum einen abdeckenden Index anstelle eines zusammengesetzten Index verwenden?
Stellen wir zunächst sicher, dass wir den Unterschied zwischen den beiden verstehen. Ein zusammengesetzter Index ist einfach ein regulärer Index, der mehr als eine Spalte enthält. Es können mehrere Schlüsselspalten verwendet werden, um sicherzustellen, dass jede Zeile in einer Tabelle eindeutig ist. Möglicherweise verfügen Sie auch über mehrere Spalten, um sicherzustellen, dass der Primärschlüssel eindeutig ist, oder Sie versuchen möglicherweise, die Ausführung häufig aufgerufener Abfragen für mehrere Spalten zu optimieren. Im Allgemeinen gilt jedoch: Je mehr Schlüsselspalten ein Index enthält, desto weniger effizient ist der Index, was bedeutet, dass zusammengesetzte Indizes mit Bedacht eingesetzt werden sollten.
Wie bereits erwähnt, kann eine Abfrage von großem Nutzen sein, wenn sich alle erforderlichen Daten genau wie der Index selbst sofort auf den Indexblättern befinden. Dies ist für einen Clustered-Index kein Problem, da Alle Daten sind bereits vorhanden (weshalb es so wichtig ist, bei der Erstellung eines Clustered-Index sorgfältig darüber nachzudenken). Ein nicht gruppierter Index für Blätter enthält jedoch nur Schlüsselspalten. Um auf alle anderen Daten zuzugreifen, erfordert der Abfrageoptimierer zusätzliche Schritte, die einen erheblichen Mehraufwand bei der Ausführung Ihrer Abfragen verursachen können.
Hier hilft der Deckungsindex. Wenn Sie einen nicht gruppierten Index definieren, können Sie zusätzliche Spalten zu Ihren Schlüsselspalten angeben. Angenommen, Ihre Anwendung fragt häufig Spaltendaten ab Auftragsnummer Und Auftragsdatum in der Tabelle Verkäufe:
Sie können für beide Spalten einen zusammengesetzten, nicht gruppierten Index erstellen, aber die OrderDate-Spalte erhöht nur den Aufwand für die Indexpflege, ohne als besonders nützliche Schlüsselspalte zu dienen. Die beste Lösung wäre, einen abdeckenden Index für die Schlüsselspalte zu erstellen Auftragsnummer und zusätzlich enthaltene Spalte Auftragsdatum:
CREATE NONCLUSTERED INDEX ix_orderid ON dbo.Sales(OrderID) INCLUDE (OrderDate);Dadurch werden die Nachteile der Indizierung redundanter Spalten vermieden und gleichzeitig die Vorteile der Speicherung von Daten in Blättern beim Ausführen von Abfragen beibehalten. Die enthaltene Spalte ist nicht Teil des Schlüssels, aber die Daten werden auf dem Blattknoten, dem Indexblatt, gespeichert. Dadurch kann die Abfrageleistung ohne zusätzlichen Aufwand verbessert werden. Darüber hinaus unterliegen die im abdeckenden Index enthaltenen Spalten weniger Einschränkungen als die Schlüsselspalten des Index.
Spielt die Anzahl der Duplikate in einer Schlüsselspalte eine Rolle?
Wenn Sie einen Index erstellen, müssen Sie versuchen, die Anzahl der Duplikate in Ihren Schlüsselspalten zu reduzieren. Oder genauer: Versuchen Sie, die Wiederholungsrate so gering wie möglich zu halten.
Wenn Sie mit einem zusammengesetzten Index arbeiten, gilt die Duplizierung für alle Schlüsselspalten als Ganzes. Eine einzelne Spalte kann viele doppelte Werte enthalten, es sollte jedoch nur minimale Wiederholungen in allen Indexspalten geben. Sie erstellen beispielsweise einen zusammengesetzten nicht gruppierten Index für Spalten Vorname Und Familienname, Nachname Sie können viele John Doe-Werte und viele Doe-Werte haben, aber Sie möchten so wenige John Doe-Werte wie möglich haben oder vorzugsweise nur einen John Doe-Wert.
Das Eindeutigkeitsverhältnis der Werte einer Schlüsselspalte wird als Indexselektivität bezeichnet. Je mehr eindeutige Werte vorhanden sind, desto höher ist die Selektivität: Ein eindeutiger Index weist die größtmögliche Selektivität auf. Die Abfrage-Engine mag Spalten mit hohen Selektivitätswerten sehr, insbesondere wenn diese Spalten in den WHERE-Klauseln Ihrer am häufigsten ausgeführten Abfragen enthalten sind. Je selektiver der Index ist, desto schneller kann die Abfrage-Engine die Größe des resultierenden Datensatzes reduzieren. Der Nachteil besteht natürlich darin, dass Spalten mit relativ wenigen eindeutigen Werten selten gute Kandidaten für die Indizierung sind.
Ist es möglich, einen nicht gruppierten Index nur für eine bestimmte Teilmenge der Daten einer Schlüsselspalte zu erstellen?
Standardmäßig enthält ein nicht gruppierter Index eine Zeile für jede Zeile in der Tabelle. Das Gleiche gilt natürlich auch für einen Clustered-Index, vorausgesetzt, dass es sich bei einem solchen Index um eine Tabelle handelt. Aber wenn es um einen nicht gruppierten Index geht, ist die Eins-zu-Eins-Beziehung ein wichtiges Konzept, denn beginnend mit der Version SQL Server 2008 haben Sie die Möglichkeit, einen filterbaren Index zu erstellen, der die darin enthaltenen Zeilen einschränkt. Ein gefilterter Index kann die Abfrageleistung verbessern, weil... Es ist kleiner und enthält gefilterte, genauere Statistiken als alle tabellarischen Statistiken – dies führt zur Erstellung verbesserter Ausführungspläne. Ein gefilterter Index erfordert außerdem weniger Speicherplatz und geringere Wartungskosten. Der Index wird nur aktualisiert, wenn sich die Daten ändern, die dem Filter entsprechen.
Darüber hinaus lässt sich ganz einfach ein filterbarer Index erstellen. Im Betreiber INDEX ERSTELLEN Sie müssen nur angeben, in WO Filterzustand. Sie können beispielsweise alle Zeilen, die NULL enthalten, aus dem Index herausfiltern, wie im Code gezeigt:
Tatsächlich können wir alle Daten herausfiltern, die bei kritischen Abfragen nicht wichtig sind. Aber seien Sie vorsichtig, denn... SQL Server Es gibt verschiedene Einschränkungen für filterbare Indizes, z. B. die Unmöglichkeit, einen filterbaren Index für eine Ansicht zu erstellen. Lesen Sie daher die Dokumentation sorgfältig durch.
Es kann auch sein, dass Sie durch die Erstellung einer indizierten Ansicht ähnliche Ergebnisse erzielen können. Ein gefilterter Index bietet jedoch mehrere Vorteile, beispielsweise die Möglichkeit, die Wartungskosten zu senken und die Qualität Ihrer Ausführungspläne zu verbessern. Gefilterte Indizes können auch online neu erstellt werden. Versuchen Sie dies mit einer indizierten Ansicht.
6. Indizes und Leistungsoptimierung
Indizes in Datenbanken: Zweck, Auswirkungen auf die Leistung, Prinzipien der Indexerstellung
6.1 Wozu dienen Indizes?
Indizes sind spezielle Strukturen in Datenbanken, mit denen Sie die Suche und Sortierung nach einem bestimmten Feld oder einer Reihe von Feldern in einer Tabelle beschleunigen können. Außerdem werden sie verwendet, um die Eindeutigkeit von Daten sicherzustellen. Am einfachsten lassen sich Indizes mit Indizes in Büchern vergleichen. Wenn es keinen Index gibt, müssen wir das gesamte Buch durchsuchen, um die richtige Stelle zu finden, aber mit einem Index kann die gleiche Aktion viel schneller ausgeführt werden.
Normalerweise gilt: Je mehr Indizes vorhanden sind, desto besser ist die Leistung von Datenbankabfragen. Wenn jedoch die Anzahl der Indizes übermäßig ansteigt, nimmt die Leistung von Datenänderungsvorgängen (Einfügen/Ändern/Löschen) ab und die Größe der Datenbank nimmt zu, sodass das Hinzufügen von Indizes mit Vorsicht erfolgen sollte.
Einige allgemeine Grundsätze im Zusammenhang mit der Erstellung von Indizes:
· Für Spalten, die in Joins verwendet werden, die häufig für Such- und Sortiervorgänge verwendet werden, müssen Indizes erstellt werden. Bitte beachten Sie, dass Indizes immer automatisch für Spalten erstellt werden, die einer Primärschlüsselbeschränkung unterliegen. Am häufigsten werden sie für Spalten mit einem Fremdschlüssel erstellt (in Access automatisch);
· Für Spalten, die einer Eindeutigkeitsbeschränkung unterliegen, muss automatisch ein Index erstellt werden.
· Am besten erstellen Sie Indizes für die Felder, in denen es eine Mindestanzahl sich wiederholender Werte gibt und die Daten gleichmäßig verteilt sind. Oracle verfügt über spezielle Bit-Indizes für Spalten mit einer großen Anzahl doppelter Werte. SQL Server und Access bieten diesen Indextyp nicht;
· wenn die Suche ständig (gleichzeitig) für einen bestimmten Satz von Spalten durchgeführt wird, kann es in diesem Fall sinnvoll sein, einen zusammengesetzten Index zu erstellen (nur in SQL Server) – einen Index für eine Gruppe von Spalten;
· Bei Änderungen an Tabellen werden die dieser Tabelle überlagerten Indizes automatisch geändert. Dadurch kann der Index stark fragmentiert sein, was sich auf die Leistung auswirkt. Sie sollten den Grad der Indexfragmentierung regelmäßig überprüfen und sie defragmentieren. Beim Laden großer Datenmengen ist es manchmal sinnvoll, zunächst alle Indizes zu löschen und nach Abschluss des Vorgangs erneut zu erstellen;
· Indizes können nicht nur für Tabellen, sondern auch für Ansichten erstellt werden (nur in SQL Server). Vorteile – die Möglichkeit, Felder nicht zum Zeitpunkt der Anfrage zu berechnen, sondern zum Zeitpunkt des Erscheinens neuer Werte in den Tabellen.
In diesem Artikel werden Indizes und ihre Rolle bei der Optimierung der Abfrageausführungszeit erläutert. Im ersten Teil des Artikels werden die verschiedenen Formen von Indizes und deren Speicherung erläutert. Als Nächstes untersuchen wir die drei wichtigsten Transact-SQL-Anweisungen, die für die Arbeit mit Indizes verwendet werden: CREATE INDEX, ALTER INDEX und DROP INDEX. Anschließend wird die Fragmentierung der Indizes ihrer Auswirkungen auf die Systemleistung betrachtet. Anschließend werden einige allgemeine Richtlinien zum Erstellen von Indizes bereitgestellt und mehrere spezielle Indextypen beschrieben.
allgemeine Informationen
Datenbanksysteme verwenden in der Regel Indizes, um einen schnellen Zugriff auf relationale Daten zu ermöglichen. Ein Index ist eine separate physische Datenstruktur, die einen schnellen Zugriff auf eine oder mehrere Datenzeilen ermöglicht. Daher ist die ordnungsgemäße Optimierung von Indizes ein wichtiger Aspekt zur Verbesserung der Abfrageleistung.
Ein Datenbankindex ähnelt in vielerlei Hinsicht einem Index (alphabetischer Index) eines Buches. Wenn wir schnell ein Thema in einem Buch finden müssen, schauen wir zunächst im Index, auf welchen Seiten des Buches dieses Thema behandelt wird, und öffnen dann sofort die gewünschte Seite. Ebenso greift die Datenbank-Engine bei der Suche nach einer bestimmten Zeile in einer Tabelle auf den Index zu, um deren physischen Speicherort zu ermitteln.
Es gibt jedoch zwei wesentliche Unterschiede zwischen einem Buchindex und einem Datenbankindex:
Der Leser des Buches hat die Möglichkeit, im Einzelfall selbst zu entscheiden, ob er den Index nutzt oder nicht. Diese Möglichkeit hat der Datenbanknutzer nicht und diese Entscheidung wird ihm von einer aufgerufenen Systemkomponente abgenommen Abfrageoptimierer. (Der Benutzer kann die Verwendung von Indizes durch Indexhinweise manipulieren, die Verwendung dieser Hinweise wird jedoch nur in einer begrenzten Anzahl von Sonderfällen empfohlen.)
Der Index für eine bestimmte Arbeitsmappe wird zusammen mit der Arbeitsmappe erstellt und anschließend nicht mehr geändert. Das bedeutet, dass der Index für ein bestimmtes Thema immer auf dieselbe Seitenzahl verweist. Im Gegensatz dazu kann sich der Datenbankindex immer dann ändern, wenn sich die entsprechenden Daten ändern.
Wenn eine Tabelle keinen geeigneten Index hat, verwendet das System eine Tabellenscanmethode, um Zeilen abzurufen. Ausdruck Tabellenscan bedeutet, dass das System nacheinander jede Zeile der Tabelle abruft und untersucht (von der ersten bis zur letzten) und die Zeile in die Ergebnismenge einfügt, wenn die Suchbedingung in der WHERE-Klausel dafür erfüllt ist. Somit werden alle Zeilen entsprechend ihrer physischen Position im Speicher abgerufen. Diese Methode ist weniger effizient als der Zugriff über Indizes, wie unten erläutert.
Indizes werden in zusätzlichen Datenbankstrukturen namens gespeichert Indexseiten. Für jede indizierte Zeile gibt es Indexeintrag, die auf der Indexseite gespeichert wird. Jedes Indexelement besteht aus einem Indexschlüssel und einem Index. Aus diesem Grund ist das Indexelement deutlich kürzer als die Tabellenzeile, auf die es verweist. Aus diesem Grund ist die Anzahl der Indexelemente auf jeder Indexseite viel größer als die Anzahl der Zeilen auf der Datenseite.
Diese Eigenschaft von Indizes ist sehr wichtig, da die Anzahl der zum Durchlaufen der Indexseiten erforderlichen E/A-Vorgänge deutlich geringer ist als die Anzahl der zum Durchlaufen der entsprechenden Datenseiten erforderlichen E/A-Vorgänge. Mit anderen Worten: Das Scannen einer Tabelle würde wahrscheinlich viel mehr E/A-Vorgänge erfordern als das Scannen des Tabellenindex.
Datenbank-Engine-Indizes werden mithilfe einer B+-Baumdatenstruktur erstellt. Ein B+-Baum hat eine Baumstruktur, in der alle untersten Knoten gleich viele Ebenen vom oberen (Wurzelknoten) des Baums entfernt sind. Diese Eigenschaft bleibt auch dann erhalten, wenn Daten zur indizierten Spalte hinzugefügt oder daraus entfernt werden.
Die folgende Abbildung zeigt die B+-Baumstruktur für die Employee-Tabelle und den direkten Zugriff auf die Zeile in dieser Tabelle mit dem Wert 25348 für die Id-Spalte. (Wir gehen davon aus, dass die Employee-Tabelle durch die Id-Spalte indiziert wird.) In dieser Abbildung können Sie auch sehen, dass ein B+-Baum aus einem Wurzelknoten, Baumknoten und null oder mehr Zwischenknoten besteht:
Sie können diesen Baum wie folgt nach dem Wert 25348 durchsuchen. Beginnend an der Wurzel des Baums wird nach dem kleinsten Schlüsselwert gesucht, der größer oder gleich dem erforderlichen Wert ist. Somit beträgt dieser Wert im Wurzelknoten 29346, sodass ein Übergang zum Zwischenknoten erfolgt, der diesem Wert zugeordnet ist. In diesem Knoten erfüllt der Wert 28559 die angegebenen Anforderungen, wodurch ein Übergang zu dem diesem Wert zugeordneten Baumknoten erfolgt. Dieser Knoten enthält den gewünschten Wert 25348. Nachdem wir den erforderlichen Index ermittelt haben, können wir seine Zeile mithilfe der entsprechenden Zeiger aus der Datentabelle extrahieren. (Ein alternativer äquivalenter Ansatz wäre die Suche nach einem Wert, der kleiner oder gleich dem Index ist.)
Aufgrund ihrer offensichtlichen Vorteile ist die indizierte Suche normalerweise die bevorzugte Methode zum Durchsuchen von Tabellen mit einer großen Anzahl von Zeilen. Mit der indizierten Suche können wir jede Zeile in einer Tabelle in sehr kurzer Zeit und mit nur wenigen E/A-Vorgängen finden. Und die sequentielle Suche (d. h. das Durchsuchen einer Tabelle von der ersten bis zur letzten Zeile) nimmt umso mehr Zeit in Anspruch, je weiter die gewünschte Zeile entfernt ist.
In den folgenden Abschnitten betrachten wir die beiden vorhandenen Indextypen, Clustered und Nonclustered, und erfahren, wie man Indizes erstellt.
Clustered-Indizes
Clustered-Index bestimmt die physische Reihenfolge der Daten in einer Tabelle. Mit der Datenbank-Engine können Sie nur einen Clustered-Index für eine Tabelle erstellen, weil Die Zeilen einer Tabelle können nicht auf mehrere Arten physisch angeordnet werden. Eine Suche mithilfe eines Clustered-Index wird vom Wurzelknoten eines B+-Baums nach Knoten im Baum durchgeführt, die in einer doppelt verknüpften Liste namens „ Seitenkette.
Eine wichtige Eigenschaft eines Clustered-Index besteht darin, dass seine Baumknoten Seiten mit Daten enthalten. (Alle anderen Ebenen von Clustered-Index-Knoten enthalten Indexseiten.) Eine Tabelle, für die ein Clustered-Index (entweder explizit oder implizit) definiert ist, wird als Clustered-Tabelle bezeichnet. Die B+-Baumstruktur eines Clustered-Index ist in der folgenden Abbildung dargestellt:
Ein Clustered-Index wird standardmäßig für jede Tabelle erstellt, die über einen durch eine Primärschlüsseleinschränkung definierten Primärschlüssel verfügt. Darüber hinaus ist jeder Clustered-Index standardmäßig eindeutig, d. h. In einer Spalte, für die ein gruppierter Index definiert ist, kann jeder Datenwert nur einmal vorkommen. Wenn ein Clustered-Index für eine Spalte erstellt wird, die doppelte Werte enthält, erzwingt das Datenbanksystem Eindeutigkeit, indem es an Zeilen, die doppelte Werte enthalten, einen Vier-Byte-Bezeichner anhängt.
Clustered-Indizes ermöglichen einen sehr schnellen Datenzugriff, wenn eine Abfrage einen Wertebereich durchsucht.
Nicht gruppierte Indizes
Die Struktur eines nicht gruppierten Index ist genau die gleiche wie die eines gruppierten Index, weist jedoch zwei wichtige Unterschiede auf:
Ein nicht gruppierter Index ändert die physische Reihenfolge der Tabellenzeilen nicht.
Nicht gruppierte Indexknotenseiten bestehen aus Indexschlüsseln und Lesezeichen.
Wenn Sie einen oder mehrere nicht gruppierte Indizes für eine Tabelle definieren, wird die physische Reihenfolge der Tabellenzeilen nicht geändert. Für jeden nicht gruppierten Index erstellt das Datenbankmodul eine zusätzliche Indexstruktur, die in Indexseiten gespeichert wird. Die B+-Baumstruktur eines nicht gruppierten Index ist in der folgenden Abbildung dargestellt:
Ein Lesezeichen in einem nicht gruppierten Index gibt an, wo sich die Zeile befindet, die dem Indexschlüssel entspricht. Die Indexschlüssel-Lesezeichenkomponente kann von zwei Typen sein, abhängig davon, ob es sich bei der Tabelle um eine Clustertabelle oder einen Heap handelt. (In der SQL Server-Terminologie ist ein Heap eine Tabelle ohne Clustered-Index.) Wenn ein Clustered-Index vorhanden ist, wird auf der Registerkarte „Nonclustered Index“ der B+-Baum des Clustered-Index der Tabelle angezeigt. Wenn die Tabelle keinen Clustered-Index hat, ist das Lesezeichen identisch Zeilenbezeichner (RID – Zeilenbezeichner), bestehend aus drei Teilen: der Adresse der Datei, in der die Tabelle gespeichert ist, der Adresse des physischen Blocks (Seite), in dem die Zeile gespeichert ist, und dem Offset der Zeile auf der Seite.
Wie bereits erwähnt, kann die Suche nach Daten mit einem nicht gruppierten Index je nach Tabellentyp auf zwei verschiedene Arten erfolgen:
Heap – Durchläuft die Suchstruktur eines nicht gruppierten Indexes, woraufhin die Zeile mithilfe der Zeilenkennung abgerufen wird;
Clustered-Tabelle – ein Suchdurchlauf einer nicht gruppierten Indexstruktur, gefolgt von einem Durchlauf des entsprechenden Clustered-Index.
In beiden Fällen ist die Menge an E/A-Vorgängen ziemlich groß. Daher sollten Sie einen nicht gruppierten Index mit Vorsicht entwerfen und ihn nur verwenden, wenn Sie sicher sind, dass seine Verwendung die Leistung erheblich verbessern wird.
Transact-SQL-Sprache und -Indizes
Nachdem wir nun mit der physischen Struktur von Indizes vertraut sind, schauen wir uns in diesem Abschnitt an, wie man Indizes erstellt, ändert und löscht sowie wie man Ierhält und Indexinformationen bearbeitet. All dies wird uns auf die anschließende Diskussion über die Verwendung von Indizes zur Verbesserung der Systemleistung vorbereiten.
Indizes erstellen
Mithilfe der Anweisung wird ein Index für eine Tabelle erstellt INDEX ERSTELLEN. Diese Anweisung hat die folgende Syntax:
CREATE INDEX index_name ON table_name (column1 ,...) [ INCLUDE (column_name [ ,... ]) ] [[, ] PAD_INDEX = (ON | OFF)] [[, ] DROP_EXISTING = (ON | OFF)] [[ , ] SORT_IN_TEMPDB = (ON | OFF)] [[, ] IGNORE_DUP_KEY = (ON | OFF)] [[, ] ALLOW_ROW_LOCKS = (ON | OFF)] [[, ] ALLOW_PAGE_LOCKS = (ON | OFF)] [[, ] STATISTICS_NORECOMPUTE = (ON | OFF)] [[, ] ONLINE = (ON | OFF)]] Syntaxkonventionen
Der Parameter index_name gibt den Namen des zu erstellenden Index an. Ein Index kann für eine oder mehrere Spalten einer einzelnen Tabelle erstellt werden, die durch den Parameter table_name identifiziert werden. Die Spalte, für die der Index erstellt wird, wird durch den Parameter „column1“ angegeben. Das numerische Suffix dieses Parameters gibt an, dass der Index für mehrere Spalten der Tabelle erstellt werden kann. Die Datenbank-Engine unterstützt auch das Erstellen von Indizes für Ansichten.
Sie können jede Tabellenspalte indizieren. Dies bedeutet, dass auch Spalten mit den Datentypwerten VARBINARY(max), BIGINT und SQL_VARIANT indiziert werden können.
Der Index kann einfach oder zusammengesetzt sein. Ein einfacher Index wird für eine einzelne Spalte erstellt, während ein zusammengesetzter Index für mehrere Spalten erstellt wird. Ein zusammengesetzter Index unterliegt bestimmten Einschränkungen hinsichtlich seiner Größe und Anzahl der Spalten. Ein Index kann maximal 900 Bytes und maximal 16 Spalten haben.
EINZIGARTIGER Parameter Gibt an, dass die indizierte Spalte nur einwertige (d. h. sich nicht wiederholende) Werte enthalten kann. In einem einwertigen zusammengesetzten Index muss die Eindeutigkeit die Kombination der Werte aller Spalten jeder Zeile sein. Wenn das Schlüsselwort UNIQUE nicht angegeben ist, sind doppelte Werte in der/den indizierten Spalte(n) zulässig.
CLUSTERED-Parameter gibt einen Clustered-Index an und NONCLUSTERED-Parameter(Standard) gibt an, dass der Index die Reihenfolge der Zeilen in der Tabelle nicht ändert. Das Datenbankmodul erlaubt maximal 249 nicht gruppierte Indizes für eine Tabelle.
Die Datenbank-Engine wurde erweitert, um Indizes mit absteigender Reihenfolge der Spaltenwerte zu unterstützen. Der Parameter ASC nach dem Spaltennamen gibt an, dass der Index mit aufsteigender Reihenfolge der Spaltenwerte erstellt wird, und der Parameter DESC gibt die absteigende Reihenfolge der Indexspaltenwerte an. Dies bietet eine größere Flexibilität bei der Verwendung des Index. Bei absteigender Reihenfolge sollten Sie zusammengesetzte Indizes für Spalten erstellen, deren Werte in entgegengesetzter Richtung geordnet sind.
INCLUDE-Parameter Ermöglicht Ihnen die Angabe von Nichtschlüsselspalten, die den Knotenseiten eines nicht gruppierten Index hinzugefügt werden. Spaltennamen in der INCLUDE-Liste dürfen nicht wiederholt werden und eine Spalte kann nicht sowohl als Schlüsselspalte als auch als Nichtschlüsselspalte verwendet werden.
Um den Nutzen des INCLUDE-Parameters wirklich zu verstehen, müssen Sie verstehen, was er ist abdeckender Index. Wenn alle Abfragespalten in den Index einbezogen werden, können Sie erhebliche Leistungsverbesserungen erzielen, weil Der Abfrageoptimierer kann alle Spaltenwerte auf Indexseiten finden, ohne auf die Daten in der Tabelle zuzugreifen. Diese Fähigkeit wird als abdeckender Index oder abdeckende Abfrage bezeichnet. Daher können Sie durch die Aufnahme zusätzlicher Nicht-Schlüsselspalten in die Seiten des nicht gruppierten Indexknotens eine größere Abdeckung von Abfragen erhalten und deren Leistung erheblich verbessern.
FILLFACTOR-Parameter Gibt den Prozentsatz jeder Indexseite an, die zum Zeitpunkt der Indexerstellung gefüllt sein soll. Der Wert des Parameters FILLFACTOR kann im Bereich von 1 bis 100 eingestellt werden. Mit dem Wert n=100 wird jede Indexseite zu 100 % gefüllt, d. h. Sowohl eine vorhandene Knotenseite als auch eine Nicht-Knotenseite verfügen nicht über freien Platz zum Einfügen neuer Zeilen. Daher wird empfohlen, diesen Wert nur für statische Tabellen zu verwenden. (Der Standardwert n=0 bedeutet, dass die Indexknotenseiten voll sind und jede der Zwischenseiten freien Speicherplatz für einen Eintrag enthält.)
Wenn der Parameter FILLFACTOR auf Werte zwischen 1 und 99 gesetzt ist, enthalten die Knotenseiten der erstellten Indexstruktur freien Speicherplatz. Je größer der Wert von n ist, desto weniger freier Speicherplatz ist auf den Indexknotenseiten vorhanden. Bei n=60 verfügt beispielsweise jede Indexknotenseite über 40 % freien Speicherplatz für zukünftige Indexzeileneinfügungen. (Indexzeilen werden mit einer INSERT- oder UPDATE-Anweisung eingefügt.) Daher wäre ein Wert von n=60 für Tabellen sinnvoll, deren Daten sich relativ häufig ändern. Bei FILLFACTOR-Werten zwischen 1 und 99 enthalten die Zwischenindexseiten freien Platz für jeweils einen Eintrag.
Sobald ein Index erstellt wurde, wird der FILLFACTOR-Wert während der Verwendung nicht unterstützt. Mit anderen Worten: Beim Festlegen des Prozentsatzes für freien Speicherplatz wird nur die Menge des mit den verfügbaren Daten reservierten Speicherplatzes angezeigt. Um den Parameter FILLFACTOR auf seinen ursprünglichen Wert zurückzusetzen, verwenden Sie die ALTER INDEX-Anweisung.
PAD_INDEX-Parameter steht in engem Zusammenhang mit dem Parameter FILLFACTOR. Der Parameter FILLFACTOR gibt grundsätzlich die Menge des freien Speicherplatzes als Prozentsatz der gesamten Seitengröße der Indexknoten an. Und der Parameter PAD_INDEX gibt an, dass der Wert des Parameters FILLFACTOR sowohl für Indexseiten als auch für Datenseiten im Index gilt.
DROP_EXISTING-Parameter Ermöglicht Ihnen, die Leistung beim Reproduzieren eines Clustered-Index für eine Tabelle zu verbessern, die auch über einen Nicht-Clustered-Index verfügt. Weitere Informationen finden Sie im Abschnitt „Neuaufbau des Index“ weiter unten.
SORT_IN_TEMPDB-Parameter Wird verwendet, um Daten aus Zwischensortiervorgängen, die beim Erstellen eines Indexes verwendet werden, in der Tempdb-Systemdatenbank zu platzieren. Dies kann die Leistung verbessern, wenn sich tempdb auf einem anderen Datenträger befindet als die Daten.
Parameter IGNORE_DUP_KEY Ermöglicht dem System, den Versuch zu ignorieren, doppelte Werte in indizierte Spalten einzufügen. Diese Option sollte nur verwendet werden, um den Abbruch einer lang laufenden Transaktion zu vermeiden, wenn eine INSERT-Anweisung doppelte Daten in eine indizierte Spalte einfügt. Wenn diese Option aktiviert ist und eine INSERT-Anweisung versucht, Zeilen in eine Tabelle einzufügen, die die Eindeutigkeit des Index verletzen, gibt das Datenbanksystem einfach eine Warnung aus, anstatt die gesamte Anweisung zum Absturz zu bringen. In diesem Fall fügt die Datenbank-Engine keine Zeilen mit doppelten Schlüsselwerten ein, sondern ignoriert sie einfach und fügt die richtigen Zeilen hinzu. Wenn dieser Parameter nicht gesetzt ist, wird die Ausführung der gesamten Anweisung abnormal beendet.
Wann Parameter ALLOW_ROW_LOCKS aktiviert (auf „Ein“ gesetzt) wendet das System eine Zeilensperre an. Ebenso, wenn aktiviert Parameter ALLOW_PAGE_LOCKS, wendet das System beim gleichzeitigen Zugriff eine Seitensperre an. STATISTICS_NORECOMPUTE-Parameter Bestimmt den Status der automatischen Neuberechnung der Statistiken für den angegebenen Index.
Aktiviert ONLINE-Parameter ermöglicht Ihnen, einen Index im Dialogmodus zu erstellen, neu zu erstellen und zu löschen. Mit dieser Option können Sie gleichzeitig die Daten der Haupttabelle oder des Clustered-Index und aller zugehörigen Indizes ändern, während Sie den Index ändern. Während beispielsweise ein Clustered-Index neu erstellt wird, können Sie seine Daten weiterhin aktualisieren und Abfragen für diese Daten ausführen.
Parameter EIN erstellt den angegebenen Index entweder für die Standarddateigruppe (Standardwert) oder die angegebene Dateigruppe (Dateigruppenwert).
Das folgende Beispiel zeigt, wie man einen nicht gruppierten Index für die Id-Spalte der Employee-Tabelle erstellt:
USE SampleDb; CREATE INDEX ix_empid ON Employee(Id);
Das Erstellen eines einwertigen zusammengesetzten Index wird im folgenden Beispiel gezeigt:
USE SampleDb; EINZIGARTIGEN INDEX ERSTELLEN ix_empid_prnu ON Works_on (EmpId, ProjectNumber) WITH FILLFACTOR= 80;
In diesem Beispiel müssen die Werte in jeder Spalte einstellig sein. Wenn ein Index erstellt wird, werden 80 % des Platzes auf jeder Indexknotenseite gefüllt.
Sie können keinen eindeutigen Index für eine Spalte erstellen, wenn die Spalte doppelte Werte enthält. Ein solcher Index kann nur erstellt werden, wenn jeder Wert (auch NULL-Werte) genau einmal in der Spalte vorkommt. Darüber hinaus wird jeder Versuch, einen vorhandenen Datenwert in eine Spalte einzufügen oder zu ändern, die in einem vorhandenen eindeutigen Index enthalten ist, vom System abgelehnt, wenn der Wert dupliziert wird.
Informationen zur Indexfragmentierung abrufen
Während der Lebensdauer eines Indexes kann dieser fragmentiert werden, was die Speicherung von Daten auf Indexseiten ineffizient macht. Es gibt zwei Arten der Indexfragmentierung: interne Fragmentierung und externe Fragmentierung. Die interne Fragmentierung bestimmt die auf jeder Seite gespeicherte Datenmenge, während die externe Fragmentierung auftritt, wenn die Seiten nicht in der logischen Reihenfolge sind.
Um Informationen zur internen Indexfragmentierung zu erhalten, wird eine dynamische DMV-Verwaltungsansicht aufgerufen sys.dm_db_index_physical_stats. Dieser DMV gibt Informationen über das Volumen und die Fragmentierung der Daten und Indizes der angegebenen Seite zurück. Für jede Seite wird eine Zeile für jede Ebene des B+-Baums zurückgegeben. Mit diesem DMV können Sie Informationen über den Grad der Zeilenfragmentierung in Datenseiten erhalten, auf deren Grundlage Sie entscheiden können, ob die Daten neu organisiert werden sollen.
Die Verwendung der Ansicht sys.dm_db_index_physical_stats wird im folgenden Beispiel gezeigt. (Bevor Sie das Batch-Beispiel ausführen, müssen Sie alle vorhandenen Indizes in der Works_on-Tabelle löschen. Um Indizes zu löschen, verwenden Sie die DROP INDEX-Anweisung, die später gezeigt wird.)
USE SampleDb; DECLARE @dbId INT; DECLARE @tabId INT; DECLARE @indId INT; SET @dbId = DB_ID("SampleDb"); SET @tabId = OBJECT_ID("Mitarbeiter"); SELECT avg_fragmentation_in_percent, avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats (@dbId, @tabId, NULL, NULL, NULL);
Wie Sie dem Beispiel entnehmen können, verfügt die Ansicht sys.dm_db_index_physical_stats über fünf Parameter. Die ersten drei Parameter definieren die IDs der aktuellen Datenbank, Tabelle und des Index. Der vierte Parameter gibt die Partitions-ID an und der letzte Parameter gibt die Scanebene an, die zum Erhalten statistischer Informationen verwendet wird. (Der Standardwert für einen bestimmten Parameter kann mit dem NULL-Wert angegeben werden.)
Die wichtigsten Spalten in dieser Ansicht sind die Spalten avg_fragmentation_in_percent und avg_page_space_used_in_percent. Der erste gibt den durchschnittlichen Grad der Fragmentierung in Prozent an und der zweite bestimmt die Menge des belegten Speicherplatzes in Prozent.
Indexinformationen bearbeiten
Sobald Sie mit den Indexfragmentierungsinformationen, wie im vorherigen Abschnitt beschrieben, vertraut sind, können Sie diese und andere Indexinformationen mit den folgenden Systemtools bearbeiten:
Verzeichnisansichten sys.indexes;
Katalogansichten sys.index_columns;
Systemprozedur sp_helpindex;
Objekteigenschaften-Eigenschaftsfunktionen;
SQL Server Management Studio-Verwaltungsumgebung;
Dynamische DMV-Verwaltungsansicht sys.dm_db_index_usage_stats;
Dynamische DMV-Verwaltungsansicht sys.dm_db_missing_index_details.
Katalogansicht sys.indexes enthält eine Zeile für jeden Index und eine Zeile für jede Tabelle ohne Clustered-Index. Die wichtigsten Spalten dieser Katalogansicht sind die Spalten object_id, name und index_id. Die Spalte „object_id“ enthält den Namen des Datenbankobjekts, das den Index besitzt, und die Spalten „name“ und „index_id“ enthalten den Namen bzw. die ID dieses Index.
Katalogansicht sys.index_columns enthält eine Zeile für jede Spalte, die Teil des Index oder Heaps ist. Diese Informationen können in Verbindung mit Informationen verwendet werden, die über die Katalogansicht sys.indexes abgerufen werden, um zusätzliche Informationen zu den Eigenschaften des angegebenen Indexes zu erhalten.
Systemverfahren sp_helpindex gibt Informationen zu Tabellenindizes sowie statistische Informationen zu Spalten zurück. Diese Prozedur hat die folgende Syntax:
sp_helpindex [@db_object = ] „Name“
Hier stellt die Variable @db_object den Tabellennamen dar.
In Bezug auf Indizes, Objekteigenschaftenfunktion hat zwei Eigenschaften: IsIndexed und IsIndexable. Die erste Eigenschaft liefert Informationen darüber, ob die Tabelle oder Ansicht über einen Index verfügt, und die zweite Eigenschaft gibt an, ob die Tabelle oder Ansicht indizierbar ist.
Um vorhandene Indexinformationen mit SQL Server Management Studio zu bearbeiten, wählen Sie die gewünschte Datenbank im Ordner „Datenbanken“ aus, erweitern Sie den Knoten „Tabellen“ und erweitern Sie in diesem Knoten die gewünschte Tabelle und ihren Ordner „Indizes“. Im Ordner „Indizes“ der Tabelle wird eine Liste aller vorhandenen Indizes für diese Tabelle angezeigt. Durch Doppelklicken auf einen Index wird das Dialogfeld „Indexeigenschaften“ mit den Eigenschaften dieses Index geöffnet. (Sie können mit Management Studio auch einen neuen Index erstellen oder einen vorhandenen löschen.)
Leistung sys.dm_db_index_usage_stats Gibt eine Anzahl der verschiedenen Arten von Indexoperationen und den Zeitpunkt zurück, zu dem jede Art von Operation das letzte Mal ausgeführt wurde. Jeder einzelne Such-, Such- oder Aktualisierungsvorgang für einen angegebenen Index in einer einzelnen Abfrage wird als Verwendung des Index betrachtet und erhöht den entsprechenden Zähler in diesem DMV um eins. Auf diese Weise erhalten Sie allgemeine Informationen darüber, wie oft ein Index verwendet wird, sodass Sie anhand dieser Informationen ermitteln können, welche Indizes häufiger und welche weniger verwendet werden.
Leistung sys.dm_db_missing_index_details Gibt detaillierte Informationen zu Tabellenspalten zurück, für die keine Indizes vorhanden sind. Die wichtigsten Spalten dieser DMV sind die Spalten index_handle und object_id. Der Wert in der ersten Spalte identifiziert den spezifischen fehlenden Index und der Wert in der zweiten Spalte identifiziert die Tabelle, in der der Index fehlt.
Indizes ändern
Die Datenbank-Engine ist eines der wenigen Datenbanksysteme, das die Anweisung unterstützt INDEX ÄNDERN. Mit dieser Anweisung können Indexwartungsvorgänge durchgeführt werden. Die Syntax der ALTER INDEX-Anweisung ist der Syntax der CREATE INDEX-Anweisung sehr ähnlich. Mit anderen Worten: Mit dieser Anweisung können Sie die Werte der Parameter ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY und STATISTICS_NORECOMPUTE ändern, die zuvor in der CREATE INDEX-Anweisung beschrieben wurden.
Zusätzlich zu den oben genannten Optionen unterstützt die ALTER INDEX-Anweisung drei weitere Optionen:
REBUILD-Parameter, wird verwendet, um den Index neu zu erstellen;
REORGANIZE-Parameter, wird zum Reorganisieren von Indexknotenseiten verwendet;
DISABLE-Parameter, wird verwendet, um den Index zu deaktivieren. Diese drei Optionen werden in den folgenden Unterabschnitten erläutert.
Index neu erstellen
Jede Änderung von Daten mithilfe von INSERT-, UPDATE- oder DELETE-Anweisungen kann zu einer Datenfragmentierung führen. Wenn diese Daten indiziert sind, ist auch eine Indexfragmentierung möglich, wobei die Indexinformationen über verschiedene physische Seiten verteilt sind. Aufgrund der Fragmentierung der Indexdaten ist die Datenbank-Engine möglicherweise gezwungen, zusätzliche Datenlesevorgänge durchzuführen, was die Gesamtsystemleistung verringert. In diesem Fall müssen Sie alle fragmentierten Indizes NEU AUFBAUEN.
Dies kann auf zwei Arten erfolgen:
über den REBUILD-Parameter der ALTER INDEX-Anweisung;
über den Parameter DROP_EXISTING der CREATE INDEX-Anweisung.
Der REBUILD-Parameter wird zum Neuerstellen von Indizes verwendet. Wenn Sie für diesen Parameter ALL anstelle des Indexnamens angeben, werden alle Indizes der Tabelle neu erstellt. (Da Indizes dynamisch neu erstellt werden können, müssen Sie sie nicht löschen und neu erstellen.)
Die Option DROP_EXISTING der CREATE INDEX-Anweisung kann die Leistung verbessern, wenn ein gruppierter Index für eine Tabelle neu erstellt wird, die auch nicht gruppierte Indizes enthält. Es gibt an, dass ein vorhandener gruppierter oder nicht gruppierter Index gelöscht und der angegebene Index neu erstellt werden soll. Wie bereits erwähnt, enthält jeder nicht gruppierte Index einer gruppierten Tabelle in seinen Baumknoten die entsprechenden Werte des gruppierten Index der Tabelle. Aus diesem Grund müssen Sie alle nicht gruppierten Indizes neu erstellen, wenn Sie einen Clustered-Index für eine Tabelle löschen. Durch die Verwendung des Parameters DROP_EXISTING wird vermieden, dass nicht gruppierte Indizes erneut erstellt werden müssen.
Die Option DROP_EXISTING ist leistungsfähiger als die Option REBUILD, da sie flexibler ist und mehrere Optionen bietet, z. B. das Ändern der Spalten, aus denen der Index besteht, und das Ändern eines nicht gruppierten Index in einen gruppierten Index.
Indexknotenseiten neu organisieren
Der REORGANIZE-Parameter der ALTER INDEX-Anweisung organisiert die Seiten der Knoten im angegebenen Index neu, sodass die physische Reihenfolge der Seiten mit ihrer logischen Reihenfolge von links nach rechts übereinstimmt. Dadurch wird ein gewisses Maß an Indexfragmentierung beseitigt und die Indexleistung verbessert.
Index deaktivieren
Die Option DISABLE deaktiviert den angegebenen Index. Ein deaktivierter Index kann erst verwendet werden, wenn er wieder aktiviert wird. Beachten Sie, dass sich ein deaktivierter Index nicht ändert, wenn Änderungen an den zugehörigen Daten vorgenommen werden. Aus diesem Grund muss ein deaktivierter Index zur Wiederverwendung vollständig neu erstellt werden. Um einen deaktivierten Index zu aktivieren, verwenden Sie die Option REBUILD der ALTER TABLE-Anweisung.
Wenn ein Clustered-Index für eine Tabelle deaktiviert ist, kann auf die Daten der Tabelle nicht zugegriffen werden, da alle Datenseiten der Tabelle mit dem Clustered-Index in ihren Baumknoten gespeichert sind.
Indizes entfernen und umbenennen
Um Indizes in der aktuellen Datenbank zu entfernen, verwenden Sie DROP INDEX-Anweisung. Beachten Sie, dass das Löschen eines Clustered-Index für eine Tabelle ein sehr ressourcenintensiver Vorgang sein kann, weil Alle nicht gruppierten Indizes müssen neu erstellt werden. (Alle nicht gruppierten Indizes verwenden den Indexschlüssel des gruppierten Index als Zeiger auf ihren Knotenseiten.) Die Verwendung der DROP INDEX-Anweisung zum Löschen eines Index wird im folgenden Beispiel veranschaulicht:
USE SampleDb; DROP INDEX ix_empid ON Mitarbeiter;
Die DROP INDEX-Anweisung verfügt über eine zusätzliche MOVE TO-Parameter, dessen Bedeutung mit dem ON-Parameter der CREATE INDEX-Anweisung identisch ist. Mit anderen Worten: Mit diesem Parameter können Sie angeben, wohin die Datenzeilen auf den Clustered-Index-Knotenseiten verschoben werden sollen. Die Daten werden als Heap an einen neuen Speicherort verschoben. Sie können entweder eine Standarddateigruppe oder eine benannte Dateigruppe für den neuen Datenspeicherort angeben.
Die DROP INDEX-Anweisung kann nicht zum Löschen von Indizes verwendet werden, die implizit vom System für Integritätseinschränkungen erstellt werden, wie z. B. PRIMARY KEY- und UNIQUE-Indizes. Um solche Indizes zu entfernen, müssen Sie die entsprechende Einschränkung entfernen.
Indizes können mit der Systemprozedur sp_rename umbenannt werden.
Indizes können in Management Studio auch mithilfe von Datenbankdiagrammen oder dem Objekt-Explorer erstellt, geändert und gelöscht werden. Am einfachsten ist es jedoch, den Indexes-Ordner der gewünschten Tabelle zu verwenden. Die Verwaltung von Indizes in Management Studio ähnelt der Verwaltung von Tabellen in Management Studio.
Obwohl die Datenbank-Engine die Anzahl der Indizes praktisch nicht begrenzt, gibt es mehrere Gründe, warum Sie die Anzahl begrenzen sollten. Erstens nimmt jeder Index eine bestimmte Menge an Speicherplatz ein, sodass die Möglichkeit besteht, dass die Gesamtzahl der Datenbankindexseiten die Anzahl der Datenseiten in der Datenbank übersteigt. Zweitens bietet das Einfügen und Löschen von Daten im Gegensatz zu den Vorteilen der Verwendung eines Index zum Abrufen von Daten keinen solchen Vorteil, da der Index verwaltet werden muss. Je mehr Indizes eine Tabelle hat, desto mehr Aufwand ist für deren Neuorganisation erforderlich. Generell ist es ratsam, Indizes für häufige Abfragen auszuwählen und dann deren Verwendung zu bewerten.
In diesem Abschnitt finden Sie einige Richtlinien zum Erstellen und Verwenden von Indizes. Bei den folgenden Empfehlungen handelt es sich lediglich um allgemeine Regeln. Letztendlich hängt ihre Wirksamkeit davon ab, wie die Datenbank in der Praxis genutzt wird und welche Art von Abfragen am häufigsten ausgeführt werden. Das Indizieren einer Spalte, die nie verwendet wird, bringt keinen Nutzen.
Indizes und WHERE-Klauselbedingungen
Wenn die WHERE-Klausel einer SELECT-Anweisung eine Suchbedingung mit einer Spalte enthält, sollte ein Index für diese Spalte erstellt werden. Dies empfiehlt sich insbesondere unter Bedingungen hoher Selektivität. Unter Selektivität einer Bedingung verstehen wir das Verhältnis der Anzahl der Zeilen, die die Bedingung erfüllen, zur Gesamtzahl der Zeilen in der Tabelle. Eine hohe Selektivität entspricht einem niedrigeren Wert dieses Verhältnisses. Die Suchverarbeitung mithilfe einer indizierten Spalte ist am erfolgreichsten, wenn die Bedingungsselektivität weniger als 5 % beträgt.
Eine Spalte sollte nicht indiziert werden, wenn der Selektivitätsgrad der Bedingung konstant bei 80 % oder mehr liegt. In diesem Fall erfordern die Indexseiten zusätzliche E/A-Vorgänge, wodurch die Zeitersparnis durch die Verwendung von Indizes verringert wird. In diesem Fall ist es schneller, eine Suche durch Scannen der Tabelle durchzuführen, was normalerweise vom Abfrageoptimierer ausgewählt wird, wodurch der Index unbrauchbar wird.
Wenn die Suchbedingung einer häufig verwendeten Abfrage AND-Operatoren enthält, erstellen Sie am besten einen zusammengesetzten Index für alle Tabellenspalten, die in der WHERE-Klausel der SELECT-Anweisung angegeben sind. Die Erstellung eines solchen zusammengesetzten Index wird im folgenden Beispiel gezeigt:
In diesem Beispiel wird ein zusammengesetzter Index für alle Spalten der WHERE-Klausel erstellt. In dieser Abfrage werden zwei Bedingungen durch eine UND-Verknüpfung miteinander verknüpft. Daher sollten Sie in diesen Bedingungen einen zusammengesetzten nicht gruppierten Index für beide Spalten erstellen.
Indizes und der Join-Operator
Für einen Join-Vorgang wird empfohlen, für jede zu verbindende Spalte einen Index zu erstellen. Die verknüpften Spalten stellen häufig den Primärschlüssel einer Tabelle und den entsprechenden Fremdschlüssel einer anderen Tabelle dar. Wenn Sie PRIMARY KEY- und FOREIGN KEY-Integritätseinschränkungen für die entsprechenden Join-Spalten angeben, sollten Sie aus diesem Grund nur einen nicht gruppierten Index für die Fremdschlüsselspalte erstellen Das System erstellt implizit einen Clustered-Index für die Primärschlüsselspalte.
Das folgende Beispiel zeigt, wie Sie die Indizes erstellen, die verwendet würden, wenn Sie eine Abfrage mit einer Join-Operation und einem zusätzlichen Filter hätten:
Abdeckindex
Wie bereits erwähnt, kann die Einbeziehung aller Abfragespalten in einen Index die Abfrageleistung erheblich verbessern. Die Erstellung eines solchen Index, Covering genannt, wird im folgenden Beispiel gezeigt:
VERWENDEN Sie AdventureWorks2012; GO DROP INDEX Person.Address.IX_Address_StateProvinceID; GO CREATE INDEX ix_address_zip ON Person.Address(PostalCode) INCLUDE(City, StateProvinceID); GO SELECT City, StateProvinceID FROM Person.Address WHERE PostalCode = 84407;
In diesem Beispiel wird zunächst der Index IX_Address_StateProvinceID aus der Adresstabelle entfernt. Anschließend wird ein neuer Index erstellt, der zusätzlich zur Spalte „PostalCode“ zwei weitere Spalten enthält. Schließlich zeigt die SELECT-Anweisung am Ende des Beispiels die vom Index abgedeckte Abfrage. Für diese Abfrage muss das System die Datenseiten nicht nach Daten durchsuchen, da der Abfrageoptimierer alle Spaltenwerte auf den nicht gruppierten Indexknotenseiten finden kann.
Abdeckende Indizes werden empfohlen, da Indexseiten in der Regel viel mehr Einträge enthalten als die entsprechenden Datenseiten. Um diese Methode verwenden zu können, müssen die gefilterten Spalten außerdem die ersten Schlüsselspalten im Index sein.
Indizes für berechnete Spalten
Mit der Datenbank-Engine können Sie die folgenden speziellen Indextypen erstellen:
indizierte Ansichten;
filterbare Indizes;
Indizes für berechnete Spalten;
partitionierte Indizes;
Spaltenpersistenzindizes;
XML-Indizes;
Volltextindizes.
In diesem Abschnitt werden berechnete Spalten und die zugehörigen Indizes erläutert.
Berechnete Spalte ist eine Tabellenspalte, in der die Ergebnisse von Tabellendatenberechnungen gespeichert werden. Eine solche Spalte kann virtuell oder dauerhaft sein. Diese beiden Spaltentypen werden in den folgenden Unterabschnitten erläutert.
Virtuell berechnete Spalten
Eine berechnete Spalte, die keinen entsprechenden Clustered-Index hat, ist eine logische Spalte, d. h. Es wird nicht physisch auf der Festplatte gespeichert. Daher wird es bei jedem Zugriff auf die Zeile ausgewertet. Die Verwendung virtueller berechneter Spalten wird im folgenden Beispiel gezeigt:
USE SampleDb; CREATE TABLE Orders (OrderId INT NOT NULL, Price MONEY NOT NULL, Quantity INT NOT NULL, OrderDate DATETIME NOT NULL, Total AS Price * Quantity, ShippedDate AS DATEADD (DAY, 7, orderdate));
Die Tabelle „Bestellungen“ in diesem Beispiel verfügt über zwei virtuell berechnete Spalten: „Gesamt“ und „Versanddatum“. Die Gesamtspalte wird mithilfe von zwei weiteren Spalten berechnet: Preis und Menge, und die Spalte „Versanddatum“ wird mithilfe der Funktion DATEADD und der Spalte „Bestelldatum“ berechnet.
Konstant berechnete Spalten
Mit der Datenbank-Engine können Sie Indizes für deterministisch berechnete Spalten erstellen, wobei die zugrunde liegenden Spalten über präzise Datentypen verfügen. (Eine berechnete Spalte wird als deterministisch bezeichnet, wenn sie immer dieselben Werte für dieselben Tabellendaten zurückgibt.)
Eine indizierte berechnete Spalte kann nur erstellt werden, wenn die folgenden Parameter der SET-Anweisung auf ON gesetzt sind (diese Parameter stellen sicher, dass die Spalte deterministisch ist):
QUOTED_IDENTIFIER
CONCAT_NULL_YIELDS_NULL
Darüber hinaus muss der Parameter NUMERIC_ROUNDABORT auf „off“ gesetzt sein.
Wenn Sie einen Clustered-Index für eine berechnete Spalte erstellen, sind die Werte der Spalte physisch in den entsprechenden Tabellenzeilen vorhanden, da die Knotenseiten des Clustered-Index die Datenzeilen enthalten. Das folgende Beispiel erstellt einen gruppierten Index für eine berechnete Spaltensumme aus der Tabelle „Bestellungen“:
USE SampleDb; CREATE CLUSTERED INDEX ix1 ON Orders (Total);
Nach Ausführung der CREATE INDEX-Anweisung ist die berechnete Gesamtspalte physisch in der Tabelle vorhanden. Dies bedeutet, dass alle Aktualisierungen der zugrunde liegenden Spalten einer berechneten Spalte zu einer Aktualisierung führen.
Die Spalte kann auf andere Weise mit konstant gemacht werden PERSISTED-Parameter. Mit dieser Option können Sie das physische Vorhandensein einer berechneten Spalte angeben, ohne einen entsprechenden Clustered-Index zu erstellen. Diese Funktion ist erforderlich, um physisch berechnete Spalten zu erstellen, die auf Spalten mit einem ungefähren Datentyp (Float oder Real) erstellt werden. (Wie bereits erwähnt, kann ein Index nur für eine berechnete Spalte erstellt werden, wenn die zugrunde liegenden Spalten genau den Datentyp haben.)
In diesem Artikel für Anfänger werde ich untersuchen, wie Sie die erforderlichen Indizes ermitteln, um die Geschwindigkeit der Ausführung von SQL-Abfragen zu erhöhen.
Tatsächlich sind mit Indizes viele Feinheiten verbunden, die die Leistung sowohl in die eine als auch in die entgegengesetzte Richtung erheblich beeinflussen können. Im Internet finden Sie viele Artikel dazu. Umfangreiche Artikel, in denen die Unterschiede in der Adressierung, der Speicherhaltung und vielen anderen Dingen erläutert werden.
Das sind natürlich wirklich nützliche Dinge, aber ihnen fehlt oft eine kleine Nuance – die Datenmengen, bei denen all diese Funktionen wirklich einen spürbaren Effekt haben. Und diese Zahl wird normalerweise in Hunderttausenden Datensätzen gemessen. Mit einfachen Worten: Wenn Ihre Tabellen etwa 1.000 bis 30.000 Datensätze enthalten und es sich um eine Website (oder eine ähnliche Ressource) und nicht um eine Art Zwischendatenspeicher für geladene Systeme handelt, ist dies in den meisten Fällen wichtiger Erstellen Sie einfach die richtigen Indizes. Hierbei gilt es zu beachten, dass man nicht unbedingt technisch versiert sein muss. Mit einfacher Logik können viele nützliche Indizes erstellt werden.
Notiz: Dies setzt voraus, dass die Abfragen selbst mehr oder weniger optimal aufgebaut sind, es beispielsweise keine zusätzlichen Felder in select usw. gibt.
Index für ganzzahlige Bezeichnerfelder.
Wenn Sie ein Feld mit einem ganzzahligen Bezeichner haben (es spielt keine Rolle, ob es sich um den Bezeichner der Tabelle selbst oder um einen Bezeichner handelt, der auf eine Zeile in einer anderen Tabelle verweist), erstellen Sie einen separaten Index dafür.
Der Punkt ist dieser. Wenn das Feld ein Identifikator von Datensätzen der Tabelle selbst ist, dann sprechen wir von einem Primärschlüssel (es ist auch ein Index). Ein solcher Index bietet viele Vorteile, da Websites am häufigsten mit Identifikatoren arbeiten. Wenn es sich um einen Zeilenbezeichner aus einer Verzeichnistabelle handelt, ist auch ein Index erforderlich. Denn wenn Sie gefilterte Daten benötigen, sind diese Verzeichnisse ohne Indizes nicht von großem Nutzen (naja, vielleicht nur die Größe der Datenbank).
Wenn im ersten Fall alles ganz einfach und klar ist, werde ich für den zweiten Fall (mit einem Nachschlagewerk) ein einfaches Beispiel geben.
Nehmen wir an, es gibt zwei Tabellen: Artikel (Artikel-ID, Name, Text) und Kommentare (Kommentar-ID, Artikel-ID, Text). Die erste Tabelle enthält 200 Datensätze (Artikel), die zweite Tabelle enthält 2000 Datensätze (10 Kommentare für jeden Artikel). Wenn jeder Benutzer einen Artikel öffnet, wird dementsprechend die folgende Abfrage ausgeführt:
Wenn die SQL-Abfrage ohne Index für das Feld „article_id“ ausgeführt wird, wird jedes Mal die gesamte Tabelle mit Kommentaren (alle 2000 Datensätze) vollständig gescannt. Wenn ein Index für das Feld „article_id“ hinzugefügt wird, muss die Datenbank nicht mehr als 20 Datensätze untersuchen (um genau zu sein, im schlimmsten Fall etwa 18). Die Rechnung ist hier einfach. Im schlimmsten Fall erfolgt eine Indexsuche ungefähr mit der Geschwindigkeit des binären Logarithmus aus der Anzahl der Datensätze + der Anzahl der Datensätze mit demselben Indexfeldwert. In diesem Fall hat jeder Artikel 10 Datensätze (ihre Werte werden wiederholt) + log2 von 200 (da es nur 200 Artikel gibt = 2000 / 10) = 10 + 8 (aufgerundet) = 18.
Natürlich führt jeder dieser Indizes zusätzlich zum benötigten Speicherplatz auch zu einem zusätzlichen Datenbank-Overhead für Einfügungen, Aktualisierungen und Löschungen. Schließlich müssen neben der Änderung der Daten der Tabelle selbst auch deren Indizes neu erstellt werden. Aber wie ich bereits sagte, ist das für das Volumen normaler Websites kein großes Problem. Und selbst wenn Sie einen Index für eine Tabelle erstellen, die Sie in Ihren SQL-Abfragen nicht verwenden, verursacht dies keine nennenswerten Probleme. Darüber hinaus ist es immer möglich, dass dieser Index durch die Installation eines zusätzlichen Moduls oder das Hinzufügen von Abfragen selbst sehr nützlich sein kann.
Notiz: Bedenken Sie jedoch, dass dies speziell für ganzzahlige Indizes gilt und nicht für die Option „Lassen Sie mich Indizes für alle möglichen Felder erstellen“.
Einfache und zusammengesetzte Indizes für die häufigsten Abfragen.
Viele Datenbanken verfügen über einen Ergebniscache für Abfragen. Versuchen Sie, dieselbe Anfrage zweimal hintereinander auszuführen – im ersten Fall dauert die Ausführung der Anfrage lange, im zweiten Mal schnell. Beim ersten Mal werden die Daten berechnet, beim zweiten Mal werden die Daten aus dem Cache bereitgestellt. Dies hilft jedoch nicht viel, wenn kein Cache für Abfragen erstellt wird (z. B. wenn der Filter mithilfe integrierter Datenbankfunktionen berechnete Bedingungen enthält) oder wenn Abfragen zwar vom gleichen Typ sind, aber mit unterschiedlichen verwendet werden Parameter und in den Fällen, in denen viele Anfragen vorliegen und die Daten daher für einen sehr kurzen Zeitraum im Cache gespeichert werden.
Daher kann es sinnvoll sein, in regelmäßigen Abständen zusätzlich reguläre und zusammengesetzte Indizes für häufig ausgeführte Abfragen zu erstellen. Schauen wir uns zwei Beispiele an.
Einfacher Index.
Nehmen wir an, Sie haben eine Tabelle – Produkte (Produkt – ID, Code, Name, Text). Und es kommt vor, dass Website-Benutzer Produkte häufig anhand ihrer alphanumerischen Codes (Artikel - Codefeld) suchen. Dementsprechend sieht die Anfrage etwa so aus:
In dieser Situation ist es sinnvoll, einen separaten Index für das Feld „Code“ zu erstellen, da die Datenbank damit nicht alle Datensätze der Tabelle vollständig durchsuchen muss. Bitte beachten Sie jedoch, dass Datenbanken möglicherweise Einschränkungen hinsichtlich Feldtypen und -größen haben. Daher sollten Sie zunächst prüfen, ob es möglich ist, für solche Felder einen Index zu erstellen.
Composite-Index.
Bevor ich ein Beispiel mit einem zusammengesetzten Index gebe, möchte ich einen wichtigen Punkt ein wenig klarstellen – die Reihenfolge der Felder im Index ist wichtig. Denn die Suche erfolgt zunächst nach dem ersten Feld und dann nach dem darauffolgenden (und so weiter). Wenn Sie also nur den spezifischen Wert des letzten Felds kennen, ist ein solcher Index nicht geeignet, da es ohne Kenntnis des spezifischen Werts des ersten Felds unmöglich ist, zu bestimmen, welcher Satz von Datensätzen überprüft werden muss warum die Datenbank alle Datensätze in der Tabelle scannen muss. In einfachen Worten ist Index (Spalte_1, Spalte_2) nicht gleich Index (Spalte_2, Spalte_1).
Nehmen wir nun die folgende Situation an. Es gibt drei Tabellen: Benutzer (Benutzer-ID, Name), Kategorie (Katze – ID, Name) und Artikel (Artikel – ID, Katzen-ID, Benutzer-ID, Name, Text). Und so etwas haben Sie auf der Website getan: Am Ende des Artikels wird eine vollständige Liste der Artikel desselben Benutzers aus einer bestimmten Kategorie angezeigt. Gleichzeitig erwiesen sich die Nutzer als so produktiv, dass sie viele Artikel schrieben, wenn auch in unterschiedlichen Kategorien (z. B. kleine Geschichten, Kurznotizen usw.). In diesem Fall sieht die Anfrage so aus:
Wenn Sie Indizes für Bezeichnerfelder erstellt haben, hilft Ihnen das, aber nicht viel. Erstens gibt es zwei gleichwahrscheinliche Indizes. Eine für Kategorien und die zweite für Benutzer. Welches besser sein wird, ist im Allgemeinen unbekannt. Dies hilft möglicherweise auch nicht viel, da Benutzer möglicherweise 1.000 Artikel und Kategorien 1.000 Artikel haben können. Zweitens müssen die Datensätze für einen bestimmten Benutzer (oder eine bestimmte Kategorie) auch dann reduziert werden, wenn sie immer noch mit dem zweiten Feld gescannt werden, d. h. mit einem vollständigen Scan (wenn auch für eine geringere Menge an Datensätzen). Wenn Benutzer beispielsweise 1.000 Datensätze haben, müssen Sie für alle 1.000 Datensätze prüfen, ob sie zur Kategorie gehören oder nicht.
Bei einer großen Anzahl von Datensätzen und häufigen Aufrufen ist dies eine sehr teure SQL-Abfrage. Daher lohnt es sich in diesem Fall, einen zusammengesetzten Index zu erstellen, zum Beispiel (user_id, cat_id). In diesem Fall wird nach der Suche nach Benutzer die nachfolgende Suche nach Kategorie schneller sein, da es auch einen Index für das Ergebnis gibt Aufzeichnungen. Dementsprechend werden statt 1000 Datensätzen deutlich weniger geprüft (die Berechnung der Prüfungen erfolgt auf die gleiche Weise wie bei einem regulären Index – Logarithmus + Anzahl der Datensätze).
Wie können Sie in solchen Situationen die Reihenfolge der Felder bestimmen? Hier ist alles ganz einfach und ähnelt dem, was ich im Artikel zum Filtern beschrieben habe (siehe Link am Anfang). Ich möchte Sie daran erinnern, dass der Punkt darin besteht, dass mit jedem angewendeten Filter die Anzahl der Datensätze so gering wie möglich wird. Daher ist es sinnvoll, die durchschnittliche Anzahl der Datensätze pro Feldwert in der Tabelle zu überprüfen. Und das Feld mit dieser Nummer weniger sollte zuerst kommen. Für eine bestimmte SQL-Abfrage lohnt es sich beispielsweise, Folgendes zu überprüfen:
Berechnen Sie die durchschnittliche Anzahl der Datensätze für Benutzer. Wählen Sie – Durchschnittliche Anzahl der Datensätze avg(data.count) als avg from – Gruppieren Sie alle Datensätze nach Kennung (wählen Sie count(*) als „Anzahl“ aus dem Artikel aus – Gruppieren Sie nach Benutzern, gruppieren Sie nach user_id) als Daten; -- Berechnen Sie die durchschnittliche Anzahl von Datensätzen für Kategorien. Wählen Sie -- Durchschnittliche Anzahl von Datensätzen avg(data.count) als avg von -- Gruppieren Sie alle Datensätze nach Kennung (wählen Sie count(*) als "Anzahl" aus dem Artikel aus -- Gruppieren Sie nach Kategorie gruppieren nach cat_id) als Daten;
Wenn dementsprechend die durchschnittliche Anzahl der Benutzer geringer ist, sollte dieses Feld an erster Stelle stehen, da nach der ersten Suche nur wenige Datensätze überprüft werden müssen. Andernfalls muss die Kategorie-ID an erster Stelle stehen.
Es lohnt sich jedoch zu verstehen, dass es in einer solchen Situation auch sinnvoll ist, zu überprüfen, ob die Datensätze mehr oder weniger gleichmäßig verteilt sind. Schließlich kann es sein, dass ein Benutzer 2000 Artikel geschrieben hat und der Rest nur 100. In einer solchen Situation kann ein Filter nach Kategorie vorzuziehen sein, da die meisten Leser die Artikel dieses bestimmten Benutzers sehen. Daher lohnt es sich manchmal, nur die Gruppierung nach Bezeichnern zu berechnen (ohne den Durchschnitt zu berechnen) und die Ergebnisse schnell anzuzeigen.
Wenn Sie einen Index für drei oder mehr Felder erstellen müssen, sollten Sie dasselbe tun und nur die Anzahl der Felder erhöhen, für die die Gruppierung nach Kennung erfolgt. In einfachen Worten: Überprüfen Sie zuerst das erste Feld und ermitteln Sie die kleinste Zahl. Geben Sie dann anstelle von „Gruppierung nach Spalte_1“ verschiedene Optionen mit den verbleibenden Feldern in der Form „Gruppierung nach Spalte_1, Spalte_2“ und dann „Gruppierung nach Spalte_1, Spalte_3“ an. und so weiter. In diesem Fall wählt jeder diejenigen Kombinationen, bei denen die durchschnittliche Anzahl der Datensätze immer kleiner wird.
Und Indizes, das hier spezielle Nachschlagetabellen, mit dem eine Datenbanksuchmaschine den Datenabruf beschleunigen kann. Einfach ausgedrückt ist ein Index ein Zeiger auf Daten in einer Tabelle. Der Index in der Datenbank ist dem Index am Ende des Buches sehr ähnlich.
Wenn Sie beispielsweise Links zu allen Seiten eines Buches zu einem bestimmten Thema wünschen, konsultieren Sie zunächst den Index, der alle Themen in alphabetischer Reihenfolge auflistet und dann auf eine oder mehrere bestimmte Seitenzahlen verweist.
Der Index trägt dazu bei, Abfragen und Sätze zu beschleunigen, verlangsamt jedoch die Dateneingabe bei Anweisungen AKTUALISIEREN Und EINFÜGEN. Indizes können ohne Auswirkungen auf die Daten erstellt oder gelöscht werden.
Das Erstellen eines Index erfordert eine Anweisung INDEX ERSTELLEN, mit der Sie einen Index benennen können, um die Tabelle und die zu indizierenden Spalten anzugeben und um anzugeben, ob der Index in aufsteigender oder absteigender Reihenfolge vorliegt.
Indizes können mit dieser Einschränkung auch eindeutig sein EINZIGARTIG, sodass der Index doppelte Einträge in einer Spalte oder Spaltenkombination verhindert, auf die sich ein Index befindet.
CREATE INDEX-Befehl
Grundlegende Syntax INDEX ERSTELLEN wie folgt:
CREATE INDEX index_name ON table_name;
Einspaltige Indizes
Ein einspaltiger Index wird nur für eine Spalte in der Tabelle erstellt. Die grundlegende Syntax ist wie folgt.
CREATE INDEX index_name ON table_name(column_name);
Einzigartige Indizes
Eindeutige Indizes werden nicht nur für den Betrieb verwendet, sondern auch zur Gewährleistung der Datenintegrität. Ein eindeutiger Index lässt nicht zu, dass doppelte Werte in die Tabelle eingefügt werden. Die grundlegende Syntax ist wie folgt.
CREATE UNIQUE INDEX index_name für table_name(column_name);
Zusammengesetzte Indizes
Ein zusammengesetzter Index ist ein Index für zwei oder mehr Spalten einer Tabelle. Seine grundlegende Syntax ist wie folgt.
CREATE INDEX Indexname für Tabellenname (Spalte1, Spalte2);
Unabhängig davon, ob Sie einen Index für eine einzelne Spalte oder einen zusammengesetzten Index erstellen, berücksichtigen Sie die Spalte(n), die Sie möglicherweise sehr häufig in einer WHERE-Abfrage als Filterbedingung verwenden.
Wenn nur eine Spalte verwendet wird, muss ein Index für eine Spalte ausgewählt werden. Wenn es zwei oder mehr Spalten gibt, die häufig als Filter in der WHERE-Klausel verwendet werden, wäre ein zusammengesetzter Index die bessere Wahl.
Implizite Indizes
Implizite Indizes sind Indizes, die beim Erstellen eines Objekts automatisch auf dem Datenbankserver erstellt werden. Indizes werden automatisch für den Primärschlüssel und die eindeutige Einschränkung erstellt.
DROP INDEX-Befehl
Der Index kann mit einem SQL-Befehl gelöscht werden FALLEN. Sie sollten beim Löschen eines Index vorsichtig sein, da die Leistung entweder langsamer oder besser sein kann.
Die grundlegende Syntax sieht so aus:
DROP INDEX Indexname;
Sie können sich das INDEX-Einschränkungsbeispiel ansehen, um einige echte Beispiele für Indizes zu sehen.
Wann sollten Sie Indizes vermeiden?
Obwohl Indizes dazu gedacht sind, die Datenbankleistung zu verbessern, gibt es Zeiten, in denen sie vermieden werden sollten.
Die folgenden Anweisungen geben an, wann die Indexnutzung überdacht werden sollte.
- Indizes sollten nicht für kleine Tabellen verwendet werden.
- Tabellen, die häufig große Aktualisierungs- oder Einfügevorgänge durchführen.
- Indizes sollten nicht für Spalten verwendet werden, die eine große Anzahl von Nullwerten enthalten.
- Spalten, die häufig manipuliert werden, sollten nicht indiziert werden.
