SQL Server Index Design Guide
Ein Index ist ein On-Disk-Struktur mit einer Tabelle oder Sicht zugeordnet, die den Abruf von Zeilen aus der Tabelle oder Sicht Geschwindigkeiten. Ein Index enthält Schlüssel aufgebaut aus einer oder mehreren Spalten in der Tabelle oder Sicht. Diese Schlüssel werden in einer Struktur (B-Baum) gespeichert, auf dem SQL Server ermöglicht die Zeile oder Zeilen mit den Schlüsselwerten schnell und effizient zugeordnet zu finden.
Nicht immer Indexnutzung mit guter Leistung und ein guten Leistung mit effizienter Index Verwendung zu. Bei der Verwendung würde einen Index immer produzieren half die beste Leistung, die Aufgabe des Abfrageoptimierer einfach sein. In der Realität kann eine falsche Index Wahl verursacht weniger als optimale Leistung zu erzielen. Daher ist die Aufgabe der Abfrageoptimierer einen Index oder eine Kombination von Indizes zu wählen, nur dann, wenn sie die Leistung zu verbessern, und indizierten Abruf zu vermeiden, wenn sie die Leistung beeinträchtigen werden.
Index Design-Aufgaben
Die Folge Aufgaben bilden unsere empfohlene Strategie für die Gestaltung von Indizes:
Verstehen Sie die Eigenschaften der am häufigsten verwendeten Abfragen. Zum Beispiel weiß, dass eine häufig verwendete Abfrage verbindet zwei oder mehr Tabellen finden Sie die beste Art des Indizes ermitteln zu verwenden.
Bestimmen Sie, welche Indexoptionen kann die Leistung verbessern, wenn der Index erstellt wird oder aufrechterhalten. Zum Beispiel würde profitieren von der Online-Index-Option einen Clustered-Index auf einem vorhandenen großen Tisch zu schaffen. Die ONLINE-Option ermöglicht die gleichzeitige Aktivität auf den zugrunde liegenden Daten fortzusetzen, während der Index erstellt oder neu erstellt wird. Weitere Informationen finden Sie unter Festlegen Indexoptionen.
Bestimmen Sie den optimalen Speicherort für den Index. Einen nicht gruppierten Index kann in derselben Dateigruppe wie die darunterliegende Tabelle, oder auf einem anderen Dateigruppe gespeichert werden. Der Speicherort von Indizes kann die Abfrageleistung verbessern, indem Sie Disk-I / O-Leistung zu erhöhen. Um zum Beispiel einen nicht gruppierten Index für eine Dateigruppe zu speichern, die auf einer anderen Platte ist als die Tabelle Dateigruppe der Leistung verbessern kann, da mehrere Festplatten können gleichzeitig gelesen werden.
Alternativ gruppierten und nicht gruppierten Indizes kann ein Partitionsschema für mehrere Dateigruppen verwenden. Partitionierung macht große Tabellen oder Indizes besser handhabbar, indem wir Ihnen den Zugriff oder verwalten Teilmengen von Daten schnell und effizient, während die Integrität der gesamten Sammlung zu halten. Weitere Informationen finden Sie partitionierten Tabellen und Indizes. Wenn Sie Partitionierungs prüfen, ob der Index ausgerichtet sein sollte, das heißt, im Wesentlichen auf die gleiche Weise wie die Tabelle partitioniert oder unabhängig verteilt.
Kann in Bereichsabfragen verwendet werden.
Wenn der gruppierte Index nicht mit der einzigartigen Eigenschaft erstellt, fügt der Datenbankmodul automatisch eine 4-Byte-uniqueifier Spalte der Tabelle. Wenn es erforderlich ist, fügt die Database Engine automatisch einen uniqueifier Wert auf eine Zeile jede Taste einzigartig zu machen. Diese Spalte und ihre Werte werden intern verwendet, und nicht von den Benutzern gesehen oder auf sie zugegriffen werden.
Clustered Index Architektur
In SQL Server-Indizes werden als B-Bäume organisiert. Jede Seite in einem Index B-Baum ist ein Indexknoten bezeichnet. Der obere Knoten des B-Baums ist der Wurzelknoten bezeichnet wird. Die unteren Knoten im Index sind die Blattknoten genannt. Irgendwelche Indexstufen zwischen der Wurzel und dem Blattknoten werden als Zwischenstufen kollektiv bekannt. In einem Clustered-Index enthalten die Blattknoten die Datenseiten der zugrunde liegenden Tabelle. Die Wurzel und Zwischenebene Knoten enthalten Indexseiten Indexzeilen halten. Jeder Index Zeile enthält einen Schlüsselwert und einen Zeiger auf entweder einer Zwischenniveau-Seite in dem B-Baum oder eine Datenzeile in der Blattebene des Index. Die Seiten in jeder Ebene des Index sind in einer doppelt verketteten Liste verbunden.
Clustered-Indizes hat eine Zeile in sys.partitions. mit index_id = 1 für jede Partition durch den Index verwendet. Standardmäßig hat ein Clustered-Index eine einzelne Partition. Wenn ein gruppierter Index mehrere Partitionen aufweist, hat jede Trennwand eine B-Baumstruktur, die die Daten für diese bestimmte Partition enthält. wenn ein Clustered-Index vier Partitionen hat zum Beispiel gibt es vier B-Baumstrukturen; eine in jeder Partition.
In Abhängigkeit von den Datentypen in dem Clustered-Index, jede Cluster-Indexstruktur eine oder mehr Zuordnungseinheiten in dem Sie hat zu speichern und die Daten für eine bestimmte Partition zu verwalten. Zumindest wird jeder gruppierten Index eine IN_ROW_DATA Zuordnungseinheit pro Partition. Der Clustered-Index wird auch eine LOB_DATA Zuordnungseinheit pro Partition, wenn es große Objekt (LOB) Spalten enthält. Es wird auch eine ROW_OVERFLOW_DATA Zuordnungseinheit pro Partition, wenn es Spalten mit variabler Länge enthält, die die 8060-Byte-Zeilengröße überschreiten.
Die Seiten in der Datenkette und die Reihen in ihnen werden auf dem Wert des gruppierten Indexschlüssels bestellt. Alle Einsätze sind an dem Punkt, wo der Schlüsselwert in der eingefügten Zeile paßt in der Ordnungsfolge unter dem vorhandenen Zeilen vorgenommen.
Diese Abbildung zeigt die Struktur eines gruppierten Index in einer einzigen Partition.
Abfrage Überlegungen
Bevor Sie Clustered-Indizes erstellen, verstehen, wie Ihre Daten zugegriffen werden. Betrachten Sie einen Clustered-Index für Abfragen verwenden, die folgendes tun:
Gibt einen Wertebereich von mit Hilfe von Operatoren wie BETWEEN,>,> =, <, and <=.
Nachdem die Reihe mit dem ersten Wert unter Verwendung des gruppierten Index, mit nachfolgenden Zeilen indizieren Werte gefunden wird garantiert physikalisch benachbart sein. Wenn beispielsweise eine Abfrage Datensätze zwischen einer Reihe von Auftragsnummern abruft, ein gruppierter Index für die Spalte Salesordernumber schnell die Zeile finden können, dass die Ausgangskundenauftragsnummer enthält, und Abrufen dann alle aufeinanderfolgenden Zeilen in der Tabelle bis zum letzten Kundenauftrag Zahl erreicht ist.
Rück große Ergebnismengen.
JOIN-Klauseln; Typischerweise sind diese Fremdschlüsselspalten.
Verwenden ORDER BY oder GROUP BY-Klauseln.
Spalte Überlegungen
Im Allgemeinen sollten Sie die gruppierten Indexschlüssel mit möglichst wenigen Spalten wie möglich definieren. Betrachten Sie Spalten, die eine oder mehrere der folgenden Eigenschaften haben:
Sind einzigartig oder enthalten viele unterschiedliche Werte
Zugegriffen wird sequentiell
Definiert als IDENTITY.
Häufig verwendet, um die Daten aus einer Tabelle abgefragt, zu sortieren.
Es kann eine gute Idee zu clustern, das heißt physisch Art, die Tabelle auf dieser Spalte die Kosten einen Sortiervorganges die Spalt jedes Mal speichern abgefragt.
Clustered-Indizes sind keine gute Wahl für die folgenden Attribute:
Spalten, die häufigen Änderungen unterziehen
Dies bewirkt, dass in der ganzen Reihe bewegen, weil der Datenbank-Engine, den Datenwert einer Zeile in der physischen Ordnung halten müssen. Dies ist ein wichtiger Gesichtspunkt bei hochvolumigen Transaktionsverarbeitungssystemen, in denen Daten typischerweise flüchtig sind.
Mit mehrspaltigen eindeutigem Indizes garantiert der Index, dass jede Kombination von Werten in dem Index Schlüssel eindeutig ist. Zum Beispiel wird, wenn ein eindeutiger Index auf einer Kombination aus Namen VZ erstellt. Vorname. und Middle Spalten, keine zwei Zeilen in der Tabelle könnten die gleiche Kombination von Werten für diese Spalten.
Sowohl gruppierten und nicht gruppierten Indizes können eindeutig sein. Vorausgesetzt, dass die Daten in der Spalte ist einzigartig, können Sie sowohl einen eindeutigen gruppierten Index und mehrere eindeutige nicht gruppierte Indizes auf der gleichen Tabelle erstellen.
Die Vorteile von eindeutigen Indizes gehören die folgenden:
Die Datenintegrität der definierten Spalten gewährleistet ist.
Weitere Informationen hilfreich für den Abfrageoptimierer vorgesehen.
Überlegungen
Wenn die Daten eindeutig ist und Sie wollen Eindeutigkeit erzwungen, einen eindeutigen Index anstelle eines nicht eindeutigen Index für die gleiche Kombination von Spalten zu schaffen liefert zusätzliche Informationen für den Abfrageoptimierer, die eine effizientere Ausführungspläne produzieren kann. Erstellen eines eindeutigen Index (vorzugsweise durch eine eindeutige constraint Schaffung) wird in diesem Fall empfohlen.
Ein einzigartiger nicht gruppiert Index kann enthält nonkey Spalten enthält. Weitere Informationen finden Sie unter Index mit Eingeschlossene Spalten.
Weitere Informationen darüber, wie gefilterte Indizes zu erstellen und wie der gefilterten Index Prädikat Ausdruck zu definieren, siehe gefilterte Indizes erstellen.
Gefiltert Indizes für heterogene Daten
Wenn eine Tabelle heterogene Datenzeilen enthält, können Sie einen gefilterten Index für eine oder mehr Kategorien von Daten erstellen.
Zum Beispiel aufgeführten Produkte in der Production.Product Tabelle sind jeweils mit einem ProductSubcategoryID zugeordnet. die wiederum im Zusammenhang mit den Produktkategorie Fahrrädern, Komponenten, Bekleidung oder Zubehör. Diese Kategorien sind heterogen, weil ihre Spaltenwerte in der Tabelle Production.Product nicht eng korreliert. Zum Beispiel werden die Spalten Farbe. Meldebestand. Listenpreis. Gewicht. Klasse. und Stil hat einzigartige Eigenschaften für jede Produktkategorie. Nehmen wir an, dass es häufig Anfragen für Zubehör, die Unterkategorien zwischen 27 und 36 inklusive haben. Sie können die Leistung von Abfragen für Zubehör verbessern, indem sie einen gefilterten Index für die Unterkategorien erstellen, wie im folgenden Beispiel gezeigt.
Die gefilterten Index FIProductAccessories umfasst die folgende Abfrage, da die Abfrage
Ergebnisse sind in dem Index und der Abfrage-Plan enthält eine Basistabellennachschlag nicht enthalten. Zum Beispiel kann die Abfrage Prädikatenausdruck ProductSubcategoryID = 33 ist eine Teilmenge des gefilterten Index Prädikats ProductSubcategoryID> = 27 und ProductSubcategoryID <= 36. the ProductSubcategoryID and ListPrice columns in the query predicate are both key columns in the index, and name is stored in the leaf level of the index as an included column.
Schlüsselspalten
Es ist eine bewährte Methode, eine kleine Anzahl von Schlüsseln oder eingeschlossenen Spalten in einer gefilterten Indexdefinition zu schließen, und nur die Spalten zu übernehmen, die notwendig sind für die Abfrageoptimierer den gefilterten Index für den Abfrageausführungsplan zu wählen. Der Abfrageoptimierer kann einen gefilterten Index für die Abfrage wählen, unabhängig davon, ob es funktioniert oder deckt nicht die Abfrage. Allerdings ist die Abfrage-Optimierer eher einen gefilterten Index wählen, ob er die Abfrage abdeckt.
In einigen Fällen umfasst ein gefilterter Index die Abfrage, ohne die Spalten in dem gefilterten Indexausdruck als Schlüssel oder eingeschlossene Spalten in der gefilterten Indexdefinition inklusive. Die folgenden Richtlinien erklären, wenn eine Spalte im gefilterten Indexausdruck ein Schlüssel oder enthalten Spalte in der gefilterten Indexdefinition sein sollte. Die Beispiele beziehen sich auf die gefilterten Index, FIBillOfMaterialsWithEndDate, die zuvor erstellt wurde.
Eine Spalte in dem gefilterten Indexausdruck muss nicht ein Schlüssel oder enthielt Spalte in der gefilterten Indexdefinition sein, wenn der gefilterten Indexausdruck auf die Abfrage Prädikat äquivalent ist, und die Abfrage gibt nicht die Spalte in dem gefilterten Indexausdruck mit den Abfrageergebnissen . Zum Beispiel deckt FIBillOfMaterialsWithEndDate die folgende Abfrage, da die Abfrage Prädikat äquivalent zu dem Filterausdruck ist, und EndDate ist nicht mit den Abfrageergebnissen zurückgegeben. FIBillOfMaterialsWithEndDate braucht nicht EndDate als Schlüsselspalte oder eingeschlossene Spalte in der gefilterten Indexdefinition.
Eine Spalte in dem gefilterten Indexausdruck sollte eine Taste oder Spalte enthielt im gefilterten Indexdefinition sein, wenn die Spalte in dem Abfrageergebnis ist. Zum Beispiel hat FIBillOfMaterialsWithEndDate die folgende Abfrage nicht abdecken, weil es die EndDate-Spalte in den Abfrageergebnissen zurückgegeben. Daher kann ein Schlüssel oder Spalte enthalten im gefilterten Indexdefinition sein EndDate sollte.