Frage Wie funktioniert die Datenbankindexierung?


Angesichts der Tatsache, dass die Indexierung so wichtig ist, wie Ihr Datensatz an Größe zunimmt, kann jemand erklären, wie die Indexierung auf einer datenbankunabhängigen Ebene funktioniert?

Informationen zu Abfragen zum Indexieren eines Felds finden Sie unter Auschecken Wie indexiere ich eine Datenbankspalte?.


1873
2017-08-04 10:07


Ursprung


Antworten:


Warum wird es benötigt?

Wenn Daten auf plattenbasierten Speichergeräten gespeichert werden, werden sie als Datenblöcke gespeichert. Auf diese Blöcke wird vollständig zugegriffen, wodurch sie die atomare Festplattenzugriffsoperation bilden. Festplattenblöcke sind ähnlich aufgebaut wie verknüpfte Listen. Beide enthalten einen Abschnitt für Daten, einen Zeiger auf den Ort des nächsten Knotens (oder Blocks), und beide müssen nicht zusammenhängend gespeichert werden.

Aufgrund der Tatsache, dass eine Anzahl von Datensätzen nur nach einem Feld sortiert werden kann, können wir angeben, dass die Suche nach einem Feld, das nicht sortiert ist, eine lineare Suche erfordert, die dies erfordert N/2 Blockzugriffe (im Durchschnitt), wo N ist die Anzahl der Blöcke, die die Tabelle umfasst. Wenn dieses Feld ein Nicht-Schlüsselfeld ist (d.h. keine eindeutigen Einträge enthält), muss der gesamte Tablespace durchsucht werden N Zugriffe blockieren.

Während bei einem sortierten Feld eine binäre Suche verwendet werden kann, die log2 N Zugriffe blockieren. Da die Daten bei einem Nicht-Schlüssel-Feld sortiert sind, muss der Rest der Tabelle nicht nach doppelten Werten durchsucht werden, sobald ein höherer Wert gefunden wurde. Somit ist die Leistungssteigerung erheblich.

Was ist Indexierung?

Indizierung ist eine Möglichkeit, eine Anzahl von Datensätzen in mehreren Feldern zu sortieren. Das Erstellen eines Indexes für ein Feld in einer Tabelle erstellt eine andere Datenstruktur, die den Feldwert enthält, und einen Zeiger auf den Datensatz, auf den es sich bezieht. Diese Indexstruktur wird dann sortiert, wodurch Binärsuchen ausgeführt werden können.

Der Nachteil der Indizierung besteht darin, dass diese Indizes zusätzlichen Speicherplatz auf der Festplatte benötigen, da die Indizes mithilfe der MyISAM-Engine in einer Tabelle gespeichert werden. Diese Datei kann schnell die Größenbeschränkungen des zugrunde liegenden Dateisystems erreichen, wenn viele Felder in derselben Tabelle indiziert sind .

Wie funktioniert es?

Lassen Sie uns zuerst ein Beispiel für ein Datenbanktabellenschema skizzieren.

Feldname Datentyp Größe auf Platte
id (Primärschlüssel) Unsigned INT 4 Byte
firstName Char (50) 50 Bytes
Nachname Char (50) 50 Bytes
emailAddress Char (100) 100 Bytes

Hinweis: char wurde anstelle von varchar verwendet, um eine genaue Größe auf dem Datenträgerwert zu ermöglichen. Diese Beispieldatenbank enthält fünf Millionen Zeilen und ist nicht indiziert. Die Leistung mehrerer Abfragen wird nun analysiert. Dies sind eine Abfrage mit dem Ich würde (ein sortiertes Schlüsselfeld) und eines mit dem Vorname (ein unsortiertes Nicht-Schlüssel-Feld).

Beispiel 1 - sortierte vs unsortierte Felder

Angesichts unserer Beispieldatenbank von r = 5,000,000 Aufzeichnungen einer festen Größe, die eine Rekordlänge von R = 204 Bytes und sie werden in einer Tabelle mit der MyISAM-Engine gespeichert, die die Standardblockgröße verwendet B = 1,024Bytes. Der Blockierungsfaktor der Tabelle wäre bfr = (B/R) = 1024/204 = 5 Datensätze pro Plattenblock. Die Gesamtzahl der Blöcke, die benötigt werden, um die Tabelle zu halten, ist N = (r/bfr) = 5000000/5 = 1,000,000 Blöcke.

Eine lineare Suche auf dem ID-Feld würde einen Durchschnitt von benötigen N/2 = 500,000 Blockieren Sie Zugriffe, um einen Wert zu finden, da das ID-Feld ein Schlüsselfeld ist. Aber da das ID-Feld auch sortiert ist, kann eine binäre Suche durchgeführt werden, die einen Durchschnitt von log2 1000000 = 19.93 = 20 Zugriffe blockieren. Sofort können wir sehen, dass dies eine drastische Verbesserung ist.

Jetzt die Vorname Feld ist weder sortiert noch ein Schlüsselfeld, so dass eine binäre Suche unmöglich ist, noch sind die Werte eindeutig und daher wird die Tabelle bis zum Ende nach einer genauen Suche erfordern N = 1,000,000 Zugriffe blockieren. In dieser Situation zielt die Indexierung darauf ab, zu korrigieren.

Wenn ein Indexdatensatz nur das indizierte Feld und einen Zeiger auf den ursprünglichen Datensatz enthält, liegt es nahe, dass er kleiner ist als der Datensatz mit mehreren Feldern, auf den er zeigt. Daher benötigt der Index selbst weniger Festplattenblöcke als die ursprüngliche Tabelle, wodurch weniger Blockzugriffe zum Durchlaufen benötigt werden. Das Schema für einen Index auf dem Vorname Feld ist unten umrissen;

Feldname Datentyp Größe auf Platte
firstName Char (50) 50 Bytes
(Datensatzzeiger) Spezielle 4 Bytes

Hinweis: Zeiger in MySQL sind 2, 3, 4 oder 5 Bytes lang, abhängig von der Größe der Tabelle.

Beispiel 2  - Indizierung

Angesichts unserer Beispieldatenbank von r = 5,000,000 Datensätze mit einer Indexaufzeichnungslänge von R = 54 Bytes und die Standardblockgröße verwenden B = 1,024 Bytes. Der Blockierungsfaktor des Index wäre bfr = (B/R) = 1024/54 = 18 Datensätze pro Plattenblock. Die Gesamtzahl der zum Halten des Index erforderlichen Blöcke beträgt N = (r/bfr) = 5000000/18 = 277,778 Blöcke.

Jetzt eine Suche mit dem Vorname Feld kann den Index verwenden, um die Leistung zu erhöhen. Dies ermöglicht eine binäre Suche des Index mit einem Durchschnitt von log2 277778 = 18.08 = 19 Zugriffe blockieren. Um die Adresse des aktuellen Datensatzes zu finden, der einen weiteren Blockzugriff zum Lesen benötigt, wird der Gesamtwert zu 19 + 1 = 20 Blockzugriffe, weit entfernt von den 1.000.000 Blockzugriffen, die benötigt werden, um einen zu finden Vorname Übereinstimmung in der nicht indizierten Tabelle.

Wann sollte es verwendet werden?

Da das Erstellen eines Indexes zusätzlichen Speicherplatz erfordert (277.778 zusätzliche Blöcke aus dem obigen Beispiel, eine Erhöhung von ~ 28%) und zu viele Indizes zu Problemen führen können, die sich aus den Größenbeschränkungen des Dateisystems ergeben, muss sorgfältig überlegt werden Felder zum Indexieren.

Da Indizes nur verwendet werden, um die Suche nach einem übereinstimmenden Feld in den Datensätzen zu beschleunigen, liegt es nahe, dass Indexfelder, die nur für die Ausgabe verwendet werden, einfach eine Verschwendung von Speicherplatz und Verarbeitungszeit bei einer Einfüge- oder Löschoperation sind sollte vermieden werden. Auch angesichts der Natur einer binären Suche ist die Kardinalität oder Eindeutigkeit der Daten wichtig. Die Indizierung auf einem Feld mit einer Kardinalität von 2 würde die Daten in zwei Hälften teilen, während eine Kardinalität von 1.000 ungefähr 1000 Datensätze zurückgeben würde. Bei einer so geringen Kardinalität wird die Effektivität auf eine lineare Sortierung reduziert, und der Abfrageoptimierer wird die Verwendung des Index vermeiden, wenn die Kardinalität weniger als 30% der Datensatznummer beträgt, was den Index effektiv zu einer Platzverschwendung macht.


2848
2017-08-04 10:41



Das erste Mal, als ich das las, war es sehr hilfreich für mich. Vielen Dank.

Seitdem habe ich einige Einblicke in die Nachteile der Indexerstellung erhalten: wenn Sie in eine Tabelle schreiben (UPDATE oder INSERT) Mit einem Index haben Sie tatsächlich zwei Schreiboperationen im Dateisystem. Eine für die Tabellendaten und eine weitere für die Indexdaten (und deren Umsortierung (und - falls gruppiert - das Umsortieren der Tabellendaten). Wenn sich Tabelle und Index auf derselben Festplatte befinden, kostet das mehr Zeit. Somit würde eine Tabelle ohne Index (ein Heap) schnellere Schreiboperationen ermöglichen. (Wenn Sie zwei Indizes hätten, würden Sie mit drei Schreiboperationen enden und so weiter)

Das Definieren von zwei verschiedenen Orten auf zwei verschiedenen Festplatten für Indexdaten und Tabellendaten kann jedoch das Problem erhöhter Zeitkosten verringern / eliminieren. Dies erfordert die Definition zusätzlicher Dateigruppen mit entsprechenden Dateien auf den gewünschten Festplatten und die Definition der gewünschten Tabelle / Indexposition.

Ein weiteres Problem bei Indizes ist ihre Fragmentierung im Laufe der Zeit, wenn Daten eingefügt werden. REORGANIZE hilft, müssen Sie Routinen schreiben, um es getan zu haben.

In bestimmten Szenarien ist ein Heap hilfreicher als eine Tabelle mit Indizes.

z. B .: - Wenn Sie viele konkurrierende Schreibvorgänge haben, aber nur eine Nacht außerhalb der Geschäftszeiten lesen, um zu berichten.

Außerdem ist eine Unterscheidung zwischen gruppierten und nicht gruppierten Indizes ziemlich wichtig.

Half mir:- Was bedeuten Clustered und Non Clustered Index eigentlich?


175
2018-04-30 14:31



Ein Index ist nur eine Datenstruktur, die das Suchen nach einer bestimmten Spalte in einer Datenbank beschleunigt. Diese Struktur ist normalerweise eine B-Baum- oder eine Hash-Tabelle, aber sie kann jede andere logische Struktur haben.

Für weitere Informationen empfehle ich: Wie funktionieren Datenbankindizes? Und wie helfen Indizes?


130
2018-02-20 14:40



Nun möchten wir sagen, dass wir eine Abfrage ausführen möchten, um alle Details von Mitarbeitern mit dem Namen "Abc" zu finden?

SELECT * FROM Employee 
WHERE Employee_Name = 'Abc'

Was würde ohne einen Index passieren?

Datenbanksoftware müsste buchstäblich jede einzelne Zeile in der Employee-Tabelle prüfen, um zu sehen, ob der Employee_Name für diese Zeile 'Abc' ist. Und weil wir jede Zeile mit dem Namen 'Abc' darin haben wollen, können wir nicht einfach aufhören zu suchen, wenn wir nur eine Zeile mit dem Namen 'Abc' finden, weil es andere Zeilen mit dem Namen geben könnte ABC. Also muss jede Zeile bis zur letzten Zeile durchsucht werden - was bedeutet, dass Tausende von Zeilen in diesem Szenario von der Datenbank untersucht werden müssen, um die Zeilen mit dem Namen 'Abc' zu finden. Dies ist, was man a nennt vollständiger Tabellenscan

Wie ein Datenbankindex die Leistung verbessern kann

Der einzige Vorteil eines Index besteht darin, Suchanfragen zu beschleunigen, indem die Anzahl der Datensätze / Zeilen in einer Tabelle, die untersucht werden müssen, wesentlich verringert wird. Ein Index ist eine Datenstruktur (am häufigsten eine B-Struktur), die die Werte für eine bestimmte Spalte in einer Tabelle speichert.

Wie funktioniert der B-Bäume-Index?

Der Grund, warum B-Bäume die beliebteste Datenstruktur für Indizes sind, liegt in der Tatsache begründet, dass sie zeiteffizient sind - da Nachschlagen, Löschungen und Einfügungen alle in logarithmischer Zeit durchgeführt werden können. Ein weiterer Hauptgrund, warum B-Bäume häufiger verwendet werden, ist, dass die Daten, die innerhalb des B-Baums gespeichert sind, sortiert werden können. Das RDBMS bestimmt normalerweise, welche Datenstruktur tatsächlich für einen Index verwendet wird. In einigen Szenarios mit bestimmten RDBMS können Sie jedoch angeben, welche Datenstruktur Ihre Datenbank verwenden soll, wenn Sie den Index selbst erstellen.

Wie funktioniert ein Hash-Tabellenindex?

Der Grund dafür, dass Hash-Indizes verwendet werden, liegt darin, dass Hash-Tabellen äußerst effizient sind, wenn es darum geht, nur nach Werten zu suchen. So können Abfragen, die Gleichheit mit einer Zeichenfolge vergleichen, Werte sehr schnell abrufen, wenn sie einen Hash-Index verwenden.

Zum Beispiel könnte die Abfrage, die wir bereits besprochen haben, von einem Hash-Index profitieren, der in der Spalte Employee_Name erstellt wurde. Ein Hash-Index funktioniert so, dass der Spaltenwert der Schlüssel in der Hash-Tabelle ist und der tatsächliche Wert, der diesem Schlüssel zugeordnet wird, nur ein Zeiger auf die Zeilendaten in der Tabelle ist. Da eine Hash-Tabelle im Grunde genommen ein assoziatives Array ist, würde ein typischer Eintrag etwa wie "Abc => 0x28939" aussehen, wobei 0x28939 ein Verweis auf die Tabellenzeile ist, in der Abc im Speicher gespeichert ist. Einen Wert wie "Abc" in einem Hashtabellenindex nachzuschlagen und einen Verweis auf die Zeile im Speicher zurückzuholen, ist offensichtlich viel schneller als die Tabelle zu durchsuchen, um alle Zeilen mit dem Wert "Abc" in der Spalte Mitarbeitername zu finden.

Die Nachteile eines Hash-Index

Hash-Tabellen sind keine sortierten Datenstrukturen, und es gibt viele Arten von Abfragen, bei denen Hash-Indizes nicht einmal helfen können. Angenommen, Sie möchten alle Mitarbeiter herausfinden, die weniger als 40 Jahre alt sind. Wie können Sie das mit einem Hashtabellenindex machen? Nun, das ist nicht möglich, weil eine Hash-Tabelle nur dazu dient, Schlüsselwertpaare nachzuschlagen - was bedeutet, dass Abfragen auf Gleichheit geprüft werden

Was genau ist in einem Datenbankindex? Jetzt wissen Sie, dass ein Datenbankindex für eine Spalte in einer Tabelle erstellt wird und dass der Index die Werte in dieser bestimmten Spalte speichert. Es ist jedoch wichtig zu verstehen, dass ein Datenbankindex die Werte nicht in den anderen Spalten derselben Tabelle speichert. Wenn Sie beispielsweise einen Index für die Spalte "Employee_Name" erstellen, bedeutet dies, dass die Spaltenwerte Employee_Age und Employee_Address nicht ebenfalls im Index gespeichert werden. Wenn wir nur alle anderen Spalten im Index speichern würden, dann wäre das genau so, als würde man eine weitere Kopie der gesamten Tabelle erstellen - was viel Platz beanspruchen würde und sehr ineffizient wäre.

Wie weiß eine Datenbank, wann ein Index verwendet werden soll? Wenn eine Abfrage wie "SELECT * FROM Employee WHERE Employee_Name = 'Abc'" ausgeführt wird, prüft die Datenbank, ob für die abgefragten Spalten ein Index vorhanden ist. Angenommen, in der Spalte Employee_Name wird ein Index erstellt, muss die Datenbank entscheiden, ob es sinnvoll ist, den Index zu verwenden, um die gesuchten Werte zu finden - da es in einigen Szenarios tatsächlich weniger effizient ist, den Datenbankindex zu verwenden und effizienter, nur um die gesamte Tabelle zu scannen.

Was kostet ein Datenbankindex?

Es braucht Platz - und je größer Ihr Tisch, desto größer Ihr Index. Ein weiterer Leistungseinbruch bei Indizes ist die Tatsache, dass beim Hinzufügen, Löschen oder Aktualisieren von Zeilen in der entsprechenden Tabelle dieselben Operationen für den Index ausgeführt werden müssen. Denken Sie daran, dass ein Index bis zu den Minuten die gleichen Daten enthalten muss wie die Tabellenspalte (n), die der Index abdeckt.

Als allgemeine Regel sollte ein Index nur für eine Tabelle erstellt werden, wenn die Daten in der indizierten Spalte häufig abgefragt werden.

Siehe auch

  1. Welche Spalten ergeben im Allgemeinen gute Indizes?
  2. Wie funktionieren Datenbankindizes?

93
2017-08-13 18:36



Klassisches Beispiel "Index in Büchern"

Betrachten Sie ein "Buch" von 1000 Seiten, geteilt durch 100 Abschnitte, jeder Abschnitt mit X-Seiten.

Einfach, nicht wahr?

Jetzt, ohne eine Indexseite, um einen bestimmten Abschnitt zu finden, der mit dem Buchstaben "S" beginnt, haben Sie keine andere Möglichkeit, als das gesamte Buch zu durchsuchen. d.h. 1000 Seiten

Aber mit einer Indexseite am Anfang bist du da. Und mehr, um einen bestimmten Abschnitt zu lesen, der wichtig ist, müssen Sie immer und immer wieder über die Indexseite schauen. Nachdem Sie den passenden Index gefunden haben, können Sie effizient zum Abschnitt springen, indem Sie andere Abschnitte überspringen.

Aber dann, zusätzlich zu 1000 Seiten, benötigen Sie weitere ~ 10 Seiten, um die Indexseite anzuzeigen, also insgesamt 1010 Seiten.

Daher ist der Index ein separater Abschnitt, der Werte der indizierten Spalte + Zeiger in der sortierten Reihenfolge für effiziente Suchvorgänge in der indizierten Reihe speichert.

Die Dinge sind einfach in der Schule, oder? : P


82
2018-04-23 14:43



Einfache Beschreibung !!!!!!!!!!

Der Index ist nichts anderes als eine Datenstruktur, die die Werte für eine bestimmte Spalte in einer Tabelle speichert. Ein Index wird für eine Spalte einer Tabelle erstellt.

Beispiel: Wir haben eine Datenbanktabelle namens Benutzer mit drei Spalten - Name, Alter und Adresse. Angenommen, die Benutzertabelle hat Tausende von Zeilen.

Nehmen wir an, wir möchten eine Abfrage ausführen, um alle Details von Benutzern mit dem Namen "John" zu finden. Wenn wir die folgende Abfrage ausführen.

SELECT * FROM User 
WHERE Name = 'John'

Die Datenbanksoftware müsste buchstäblich jede einzelne Zeile in der User-Tabelle prüfen, um zu sehen, ob der Name für diese Zeile 'John' ist. Das wird lange dauern.
Dies ist der Punkt, an dem uns der Index hilft, "der Index wird verwendet, um Suchanfragen zu beschleunigen, indem die Anzahl der Datensätze / Zeilen in einer Tabelle, die untersucht werden muss, wesentlich verringert wird".
Wie erstelle ich einen Index?

CREATE INDEX name_index
ON User (Name)

Ein Index besteht aus Spaltenwerten (zB: John) aus einer Tabelle, und diese Werte werden in einer Datenstruktur gespeichert.
Jetzt wird die Datenbank den Index verwenden, um Mitarbeiter mit dem Namen John zu finden, da der Index vermutlich alphabetisch nach dem Benutzernamen sortiert sein wird. Und weil es sortiert ist, bedeutet es, dass die Suche nach einem Namen viel schneller ist, da alle Namen, die mit einem "J" beginnen, im Index direkt nebeneinander stehen!


46
2017-08-02 01:30



Nur ein kurzer Vorschlag. Da die Indizierung zusätzliche Schreib- und Speicherplatz kostet, sollten Sie Tabellen ohne Indizes verwenden, wenn Ihre Anwendung mehr Einfüge- / Aktualisierungsvorgänge erfordert. Wenn jedoch mehr Datenabrufvorgänge erforderlich sind, sollten Sie nach indizierten Daten suchen Tabelle.


21
2018-01-14 06:44



Stellen Sie sich einfach den Datenbankindex als Index eines Buches vor.  Wenn Sie ein Buch über Hunde haben und Informationen über Deutsche Schäferhunde finden möchten, können Sie natürlich alle Seiten des Buches durchblättern und finden, wonach Sie suchen, aber das ist natürlich zeitaufwendig und nicht sehr schnell. Eine weitere Möglichkeit ist, dass Sie einfach zum Index-Bereich des Buchs gehen und dann nach dem suchen, wonach Sie suchen, indem Sie den Namen der gesuchten Entität (in diesem Beispiel Deutsche Schäferhunde) verwenden und auch die Seitennummer ansehen Finden Sie schnell, wonach Sie suchen. In Datenbank wird die Seitenzahl als ein Zeiger bezeichnet, der die Datenbank an die Adresse auf der Festplatte verweist, auf der sich die Entität befindet. Mit der gleichen deutschen Schäferhund-Analogie könnten wir so etwas haben ("Deutscher Schäferhund", 0x77129), wobei 0x77129 die Adresse auf der Festplatte ist, auf der die Reihendaten für German Shepherd gespeichert sind.

Kurz gesagt, ein Index ist eine Datenstruktur, die die Werte für eine bestimmte Spalte in einer Tabelle speichert, um die Abfragesuche zu beschleunigen.


16
2017-12-21 17:16



Der SQL-Index hat etwas mit der Beschleunigung der Suche in der SQL-Datenbank zu tun. Mit Index kann der Programmierer Daten schnell aus der Datenbank abrufen. Angenommen, Sie sind ein Student oder ein Buchleser. Ihr Buch enthält 50.000 Seiten. Am ersten Tag lesen Sie ein Thema "ABC" am nächsten Tag möchten Sie ein anderes Thema "xyz" lesen. Sie werden nie manuell Seite für Seite gehen. Was Sie in dieser Situation tun werden, ist, den Buchindex zu verwenden, um nach einem bestimmten Thema zu suchen und dann direkt zu Ihrem Thema zu springen. Der Index hat Ihnen viel Zeit beim Suchen des Themas gespart. Gleich im SQL-Index erlaubt Index, Millionen von Datensätzen sehr schnell von der Datenbank zu suchen.


10
2018-02-15 10:17



Ein Datenbankindex ist eine Datenstruktur, die die Geschwindigkeit von Datenabrufoperationen in einer Datenbanktabelle auf Kosten von zusätzlichen Schreibvorgängen und Speicherplatz zur Aufrechterhaltung der Indexdatenstruktur verbessert. Indizes werden verwendet, um Daten schnell zu finden, ohne bei jedem Zugriff auf eine Datenbanktabelle jede Zeile in einer Datenbanktabelle durchsuchen zu müssen. Indizes können mithilfe einer oder mehrerer Spalten einer Datenbanktabelle erstellt werden, die die Grundlage für schnelle zufällige Suchvorgänge und effizienten Zugriff auf geordnete Datensätze bietet.


0
2017-07-09 05:33