Datenbankindex
A Datenbankindex ist ein Datenstruktur Dies verbessert die Geschwindigkeit der Datenabrufvorgänge auf a Datenbanktabelle Auf Kosten zusätzlicher Schreibvorgänge und Speicherplatz zur Aufrechterhaltung der Indexdatenstruktur. Indizes werden verwendet, um Daten schnell zu lokalisieren, ohne jede Zeile in einer Datenbanktabelle durchsuchen zu müssen, wenn auf eine Datenbanktabelle zugegriffen wird. Indizes können mit einem oder mehreren erstellt werden Spalten einer DatenbanktabelleBereitstellung der Grundlage für schnelles Zufall Lookups und effizienter Zugang bestellter Datensätze.
Ein Index ist eine Kopie ausgewählter Datenspalten aus einer Tabelle, die so konzipiert ist, dass eine sehr effiziente Suche ermöglicht wird. Ein Index enthält normalerweise einen "Schlüssel" oder direkten Link zu der ursprünglichen Datenreihe, aus der sie kopiert wurde, damit die vollständige Zeile effizient abgerufen werden kann. Einige Datenbanken erweitern die Indexierungsleistung, indem Entwickler Indizes für Spaltenwerte erstellen können, die durch Funktionen transformiert wurden oder Ausdrücke. Zum Beispiel könnte ein Index erstellt werden obere (last_name)
, was nur die oberen Case-Versionen der speichern würde Nachname
Feld im Index. Eine andere Option, die manchmal unterstützt wird, ist die Verwendung von Teilindizes, wobei Indexeinträge nur für jene Datensätze erstellt werden, die einen bedingten Ausdruck erfüllen. Ein weiterer Aspekt der Flexibilität besteht darin, die Indexierung eins zu ermöglichen Benutzerdefinierte Funktionensowie Ausdrücke aus einer Reihe von integrierten Funktionen.
Verwendungszweck
Unterstützung für die schnelle Suche
Die meisten Datenbank Die Software umfasst die Indizierungstechnologie, die ermöglicht Sublineare Zeit Sieh nach oben die Leistung zu verbessern, als Lineare Suche ist für große Datenbanken ineffizient.
Angenommen, eine Datenbank enthält N -Datenelemente und man muss basierend auf dem Wert eines der Felder abgerufen werden. Eine einfache Implementierung ruft und untersucht jeden Element gemäß dem Test. Wenn es nur einen passenden Element gibt, kann dies aufhören, wenn es dieses einzelne Element findet. Wenn jedoch mehrere Übereinstimmungen vorhanden sind, muss es alles testen. Dies bedeutet, dass die Anzahl der Operationen in dem durchschnittlichen Fall ist O(Noch lineare Zeit. Da Datenbanken viele Objekte enthalten können und dass die Suche in einer häufigen Operation ist, ist es häufig wünschenswert, die Leistung zu verbessern.
Ein Index ist jede Datenstruktur, die die Leistung der Suche verbessert. Für diesen Zweck werden viele verschiedene Datenstrukturen verwendet. Es gibt komplexe Design-Kompromisse, die die Nachschlagleistung, die Indexgröße und die Index-Update-Leistung beinhalten. Viele Indexdesigns zeigen logarithmisch (O(log (n))) Suchleistung und in einigen Anwendungen ist es möglich, flach zu erreichen (O(1)) Leistung.
Überwachung der Datenbankbeschränkungen
Indizes werden an die Polizei gewöhnt Datenbankbeschränkungenwie einzigartig, Ausschluss, PRIMÄRSCHLÜSSEL und UNBEKANNTER SCHLÜSSEL. Ein Index kann als eindeutig deklariert werden, was eine implizite Einschränkung in der zugrunde liegenden Tabelle erzeugt. Datenbanksysteme erstellen normalerweise implizit einen Index für eine Reihe von Spalten, die für den Primärschlüssel deklariert wurden, und einige können einen bereits bestehenden Index verwenden, um diese Einschränkung zu überwachen. Viele Datenbanksysteme erfordern, dass sowohl die Referenzierung als auch die referenzierten Sätze von Spalten in einer fremden Schlüsselbeschränkung indiziert werden, wodurch die Leistung von Einfügen, Aktualisierungen und Löschungen an den an der Einschränkung beteiligten Tabellen verbessert wird.
Einige Datenbanksysteme unterstützen eine Ausschlussbeschränkung, die sicherstellt, dass für einen neu eingefügten oder aktualisierten Datensatz ein bestimmtes Prädikat für keinen anderen Datensatz gilt. Dies kann verwendet werden, um eine eindeutige Einschränkung (mit Gleichheitsprädikat) oder komplexere Einschränkungen zu implementieren, z. B. sicherzustellen, dass keine überlappenden Zeitbereiche oder keine sich überschneidenden Geometrieobjekte in der Tabelle gespeichert werden. Ein Index, der eine schnelle Suche nach Aufzeichnungen unterstützt, die das Prädikat erfüllen, ist erforderlich, um eine solche Einschränkung zu überwachen.[1]
Indexarchitektur- und Indizierungsmethoden
Nicht geklüpfter
Die Daten sind in willkürlicher Reihenfolge vorhanden, aber die logische Bestellung wird durch den Index angegeben. Die Datenzeilen können unabhängig vom Wert der indizierten Spalte oder des indexierten Ausdrucks in der gesamten Tabelle verbreitet werden. Der nicht klusterierte Indexbaum enthält die Indexschlüssel in sortierter Reihenfolge, wobei die Blattpegel des Index den Zeiger auf den Datensatz enthält (Seite und die Zeilennummer in der Datenseite in Seite organisierten Motoren; Zeilenversatz in Datei-organisierten Motoren ).
In einem nicht klusterierten Index,
- Die physische Reihenfolge der Zeilen entspricht nicht der Indexreihenfolge.
- Die indizierten Spalten sind typischerweise nicht primäre Schlüsselspalten, die im Join verwendet werden, wobei und bestellen Sie nach Klauseln.
In einer Datenbanktabelle kann es mehr als einen nicht geklüsterten Index geben.
Zusammengeklustert
Das Clustering verändert den Datenblock in eine bestimmte Reihenfolge, um mit dem Index übereinzustimmen, was dazu führt, dass die Zeilendaten in der Reihenfolge gespeichert werden. Daher kann in einer bestimmten Datenbanktabelle nur ein Cluster -Index erstellt werden. Clustered Indizes können die Gesamtgeschwindigkeit des Abrufs erheblich erhöhen, aber normalerweise nur, wenn die Daten nacheinander in derselben oder umgekehrten Reihenfolge des Cluster -Index oder wenn ein Elementbereich ausgewählt wird.
Da die physischen Datensätze in dieser Sortierreihenfolge auf der Festplatte angezeigt werden, erfolgt das nächste Zeilenelement in der Reihenfolge unmittelbar vor oder nach dem letzten, und es sind weniger Datenblock -Lesevorgänge erforderlich. Das primäre Merkmal eines Cluster -Index ist daher die Reihenfolge der physikalischen Datenzeilen gemäß den auf sie hingewiesenen Indexblöcken. Einige Datenbanken trennen die Daten und Indexblöcke in separate Dateien, andere setzen zwei vollständig unterschiedliche Datenblöcke in dieselben physischen Dateien ein.
Cluster
Wenn mehrere Datenbanken und mehrere Tabellen verbunden sind, wird sie als a genannt Cluster (Nicht zu verwechseln mit dem zuvor beschriebenen Cluster -Index). Die Datensätze für die Tabellen, die den Wert eines Cluster -Schlüssels teilen, müssen im selben oder in der Nähe von Datenblöcken zusammen gespeichert werden. Dies kann die Verknüpfungen dieser Tabellen auf dem Cluster -Schlüssel verbessern, da die Matching -Rekorde zusammen gespeichert werden und weniger E/A erforderlich ist, um sie zu lokalisieren.[2] Die Clusterkonfiguration definiert das Datenlayout in den Tabellen, die Teile des Clusters sind. Ein Cluster kann mit a gekennzeichnet werden B-Baum Index oder a Hash-tabelle. Der Datenblock, in dem der Tabellendatensatz gespeichert wird, wird durch den Wert des Cluster -Schlüssels definiert.
Säulenreihenfolge
Die Reihenfolge, in der die Indexdefinition die Spalten definiert, ist wichtig. Es ist möglich, eine Reihe von Zeilenkennungen nur mit der ersten indizierten Spalte abzurufen. Es ist jedoch nicht möglich oder effizient (in den meisten Datenbanken), den Satz von Zeilenkennungen nur mit der zweiten oder größeren indizierten Spalte abzurufen.
Zum Beispiel kann in einem von City zuerst organisierten Telefonbuch, dann mit dem Nachnamen und dann nach dem Vornamen in einer bestimmten Stadt die Liste aller Telefonnummern problemlos extrahieren. Es wäre jedoch sehr mühsam, alle Telefonnummern für einen bestimmten Nachnamen zu finden. Man müsste nach Abschnitt jeder Stadt nach den Einträgen mit diesem Nachnamen suchen. Einige Datenbanken können dies tun, andere werden den Index einfach nicht verwenden.
Im Telefonbuchbeispiel mit a Composite-Index auf den Spalten erstellt (Stadt, last_name, First_name
) Wenn wir suchen, indem wir genaue Werte für alle drei Felder angeben, ist die Suchzeit minimal - aber wenn wir die Werte für die Werte bereitstellen Stadt
und Vorname
Nur die Suche verwendet nur die Stadt
Feld zum Abrufen aller übereinstimmenden Datensätze. Dann überprüft ein sequentielles Suchen mit der Übereinstimmung mit Vorname
. Um die Leistung zu verbessern, muss man sicherstellen, dass der Index in der Reihenfolge der Suchspalten erstellt wird.
Anwendungen und Einschränkungen
Indizes sind für viele Anwendungen nützlich, verfügen jedoch über einige Einschränkungen. Folgendes berücksichtigen Sql Aussage: Wählen Sie First_Name aus Personen aus, wobei last_name = 'Smith';
. Um diese Anweisung ohne Index zu verarbeiten, muss die Datenbanksoftware in jeder Zeile in der Tabelle die Spalte last_name betrachten (dies wird als a bezeichnet Voller Tischscan). Mit einem Index folgt die Datenbank einfach der Indexdatenstruktur (normalerweise a B-Baum) bis der Smith -Eintrag gefunden wurde; Dies ist viel weniger rechnerisch teuer als ein vollständiger Tischscan.
Betrachten Sie diese SQL -Anweisung: Wählen Sie Email_address von Kunden, wobei E -Mail_Address wie '%@wikipedia.org';
. Diese Abfrage würde eine E -Mail -Adresse für jeden Kunden ergeben, dessen E -Mail -Adresse mit "@wikipedia.org" endet. Auch wenn die Spalte von E -Mail_Address indiziert wurde, muss die Datenbank einen vollständigen Indexscan ausführen. Dies liegt daran, dass der Index mit der Annahme erstellt wird, dass Wörter von links nach rechts gehen. Mit einer Wildcard Zu Beginn der Suchzeit kann die Datenbanksoftware die zugrunde liegende Indexdatenstruktur nicht verwenden (mit anderen Worten, wo Klausel ist nicht Sargierbar). Dieses Problem kann durch Hinzufügen eines anderen in erstellten Index gelöst werden Reverse (E -Mail_address)
und eine SQL -Abfrage wie diese: Wählen Sie E -Mail_Address von Kunden, wobei reverse (E -Mail_address) wie Reverse ('%@wikipedia.org');
. Dadurch wird die Wildkarten im rechtsten Teil der Abfrage (jetzt goidepikiw@%) gesetzt, den der Index auf Reverse (E-Mail_Address) erfüllen kann.
Wenn die Wildcard -Zeichen auf beiden Seiten des Suchworts als verwendet werden %Wikipedia.org%Der in diesem Feld verfügbare Index wird nicht verwendet. Eher wird nur eine sequentielle Suche durchgeführt, die o (n) Zeit in Anspruch nimmt.
Arten von Indizes
Bitmap -Index
Ein Bitmap -Index ist eine spezielle Indexierung, die den Großteil seiner Daten als speichert Bit -Arrays (Bitmaps) und beantwortet die meisten Abfragen durch Ausführung bitime logische Operationen auf diesen Bitmaps. Die am häufigsten verwendeten Indizes wie z. B+ Bäume, sind am effizientesten, wenn die Werte, die sie indexieren, nicht eine kleine Anzahl von Male wiederholen oder wiederholen. Im Gegensatz dazu ist der Bitmap -Index für Fälle ausgelegt, in denen die Werte einer Variablen sehr häufig wiederholt werden. Zum Beispiel enthält das Geschlechtsfeld in einer Kundendatenbank normalerweise höchstens drei unterschiedliche Werte: männlich, weiblich oder unbekannt (nicht aufgezeichnet). Für solche Variablen kann der Bitmap -Index einen signifikanten Leistungsvorteil gegenüber den häufig verwendeten Bäumen haben.
Dichter Index
Ein dichter Index in Datenbanken ist ein Datei mit Tastenpaaren und Zeiger für jeden Aufzeichnung in der Datendatei. Jeder Schlüssel in dieser Datei ist einem bestimmten Zeiger zugeordnet ein Rekord In der sortierten Datendatei. In Cluster -Indizes mit doppelten Schlüsselpunkten ist die dichten Indexpunkte zum ersten Rekord mit diesem Schlüssel.[3]
Spärmer Index
Ein spärlicher Index in Datenbanken ist eine Datei mit Tastenpaaren und Zeigern für jeden Block in der Datendatei. Jeder Schlüssel in dieser Datei ist einem bestimmten Zeiger zugeordnet zum Block In der sortierten Datendatei. In Cluster -Indizes mit doppelten Schlüssel ist die spärlichen Indexpunkte zum niedrigsten Suchschlüssel in jedem Block.
Reverse Index
Ein Reverse-Key-Index kehrt den Schlüsselwert um, bevor er ihn in den Index eingibt. Z. B. wird der Wert 24538 im Index zu 83542. Umkehrung des Schlüsselwerts ist besonders nützlich für die Indizierung von Daten wie Sequenznummern, bei denen die neuen Schlüsselwerte monotonisch zunehmen.
Primärindex
Der Primärindex enthält die Schlüsselfelder der Tabelle und einen Zeiger auf die Nicht-Key-Felder der Tabelle. Der Primärindex wird automatisch erstellt, wenn die Tabelle in der Datenbank erstellt wird.
Sekundärindex
Es wird verwendet, um Felder zu indexieren, die weder Bestellfelder noch Schlüsselfelder sind (es gibt keine Zusicherung, dass die Datei auf dem Feld des Schlüsselfeldes oder im Primärschlüsselfeld organisiert ist). Ein Indexeintrag für jedes Tupel in der Datendatei (dichter Index) enthält den Wert des indizierten Attributs und Zeigers auf den Block oder den Datensatz.
Hash -Index
Indeximplementierungen
Indizes können mit einer Vielzahl von Datenstrukturen implementiert werden. Zu den beliebten Indizes gehören ausgeglichene Bäume, B+ Bäume und Hashes.[4]
Im Microsoft SQL Server, das Blattknoten des Clustered-Index entspricht den tatsächlichen Daten, nicht nur ein Zeiger auf Daten, die sich an anderer Stelle befinden, wie dies bei einem nicht klusterten Index der Fall ist.[5] Jede Beziehung kann einen einzelnen Clustered -Index und viele nicht ungewöhnliche Indizes haben.[6]
Index -Parallelitätskontrolle
Ein Index wird in der Regel gleichzeitig durch mehrere Transaktionen und Prozesse zugegriffen und somit benötigt Parallelitätskontrolle. Während im Prinzip Indizes die gemeinsamen Datenbank -Concurrency -Kontrollmethoden verwenden können, existieren spezialisierte Parallelitätskontrollmethoden für Indizes, die in Verbindung mit den gemeinsamen Methoden für einen erheblichen Leistungsgewinn angewendet werden.
Abdeckungsindex
In den meisten Fällen wird ein Index verwendet, um die Datensätze, aus denen die erforderlichen Daten gelesen werden, schnell zu finden. Mit anderen Worten, der Index wird nur verwendet, um Datensätze in der Tabelle zu lokalisieren und keine Daten zurückzugeben.
Ein Abdeckindex ist ein Sonderfall, in dem der Index selbst die erforderlichen Datenfelder enthält und die erforderlichen Daten beantworten kann.
Betrachten Sie die folgende Tabelle (andere Felder weggelassen):
ICH WÜRDE | Name | Andere Felder |
---|---|---|
12 | Stecker | ... |
13 | Lampe | ... |
14 | Sicherung | ... |
Um den Namen für ID 13 zu finden, ist ein Index auf (ID) nützlich, aber der Datensatz muss weiterhin gelesen werden, um den Namen zu erhalten. Ein Index für (ID, Name) enthält jedoch das erforderliche Datenfeld und beseitigt die Notwendigkeit, den Datensatz nachzuschlagen.
Die Abdeckung von Indizes ist jeweils für eine bestimmte Tabelle. Abfragen, die sich über mehrere Tabellen hinweg verbinden/ zugreifen, können möglicherweise in Betracht ziehen, Indizes für mehr als eine dieser Tabellen abzudecken.[7]
Ein Abdeckindex kann das Abrufen von Daten dramatisch beschleunigen, kann jedoch aufgrund der zusätzlichen Schlüssel, die die Dateninsertion und -aktualisierung verlangsamen, groß sein. Um diese Indexgröße zu reduzieren, ermöglichen einige Systeme, einschließlich Nicht-Key-Felder im Index. Nicht-Key-Felder sind nicht selbst Teil der Indexreihenfolge, sondern nur auf Blattebene, was einen Deckindex mit weniger Gesamtindexgröße ermöglicht.
Standardisierung
Kein Standard definiert, wie Indizes erstellt werden, da der ISO -SQL -Standard keine physikalischen Aspekte abdeckt. Indizes sind unter anderem einer der physischen Teile der Datenbankkonzeption wie Speicher (Tablespace oder Dateigruppen). RDBMS -Anbieter bieten eine Create -Indexsyntax mit einigen spezifischen Optionen, die von den Funktionen ihrer Software abhängen.
Siehe auch
Verweise
- ^ PostgreSQL 9.1.2 Dokumentation: Tabelle erstellen
- ^ Überblick über Cluster Oracle® -Datenbankkonzepte 10G Release 1 (10.1)
- ^ Datenbanksysteme: Das vollständige Buch. Hector Garcia-Molina, Jeffrey D. Ullman, Jennifer D. Widom
- ^ Gavin Powell (2006). Kapitel 8: Erstellen schneller Datenbankmodelle. Datenbankdesign beginnen. Wrox Publishing. ISBN 978-0-7645-7490-0.
- ^ "Clustered Indexstrukturen". SQL Server 2005 Bücher online (September 2007).
- ^ Daren Bieniek; Randy Dess; Mike Hotek; Javier Loria; Adam Machanic; Antonio Soto; Adolfo Wiernik (Januar 2006). "Kapitel 4: Indizes erstellen". SQL Server 2005 Implementierung und Verwaltung. Microsoft Press.
- ^ Abdeckung von Indizes für die Abfrageoptimierung