MySQL-Index für Anfänger

Vorwort

Da die wichtigste Datenstruktur im MySQL-Index der B+-Baum ist, sprechen wir zunächst kurz über das Prinzip des B+-Baums.

B+ Baumprinzip

1. Datenstruktur

B-Baum steht für Balance Tree, also einen ausgeglichenen Baum. Ein ausgeglichener Baum ist ein Suchbaum, bei dem sich alle Blattknoten auf derselben Ebene befinden.

B+ Tree wird basierend auf B Tree und sequentiellen Zugriffszeigern für Blattknoten implementiert. Es verfügt über die Balance von B Tree und verbessert die Leistung von Intervallabfragen durch sequentielle Zugriffszeiger.

In einem B+-Baum sind die Schlüssel in einem Knoten von links nach rechts nicht abnehmend angeordnet. Wenn die links und rechts benachbarten Schlüssel eines Zeigers keyi und keyi+1 sind und nicht null sind, dann sind alle Schlüssel des Knotens, auf den der Zeiger zeigt, größer oder gleich keyi und kleiner oder gleich keyi+1.

2. Bedienung

Wenn Sie einen Suchvorgang durchführen, führen Sie zuerst eine binäre Suche auf dem Stammknoten durch, um einen Zeiger auf einen Schlüssel zu finden, und durchsuchen Sie dann rekursiv den Knoten, auf den der Zeiger zeigt. Bis ein Blattknoten gefunden wird, wird anschließend eine binäre Suche auf dem Blattknoten durchgeführt, um die dem Schlüssel entsprechenden Daten zu finden.

Einfüge- und Löschvorgänge zerstören das Gleichgewicht des ausgeglichenen Baums. Daher muss der Baum nach den Einfüge- und Löschvorgängen geteilt, zusammengeführt, gedreht usw. werden, um das Gleichgewicht aufrechtzuerhalten.

3. Vergleich mit Rot-Schwarz-Baum

Auch ausgeglichene Bäume wie Rot-Schwarz-Bäume können zur Implementierung von Indizes verwendet werden. Dateisysteme und Datenbanksysteme verwenden jedoch im Allgemeinen B+-Bäume als Indexstruktur, und zwar hauptsächlich aus den folgenden zwei Gründen:

1. Weniger Suchanfragen

Die zeitliche Komplexität einer Suchoperation in einem ausgeglichenen Baum ist gleich der Baumhöhe h, die ungefähr O(h)=O(logdN) beträgt, wobei d der Ausgangsgrad jedes Knotens ist.

Der Ausgangsgrad des Rot-Schwarz-Baums beträgt 2, während der Ausgangsgrad des B+-Baums im Allgemeinen sehr groß ist, sodass die Baumhöhe h des Rot-Schwarz-Baums offensichtlich viel größer ist als die des B+-Baums und auch die Anzahl der Suchvorgänge größer ist.

(II) Verwenden der Funktion zum Vorlesen von Datenträgern

Um den Festplatten-E/A-Aufwand zu reduzieren, wird die Festplatte häufig nicht streng bei Bedarf gelesen, sondern jedes Mal im Voraus. Während des Vorlesevorgangs führt die Festplatte ein sequentielles Lesen durch. Beim sequentiellen Lesen ist kein Suchen der Festplatte erforderlich und es ist nur eine kurze Rotationszeit erforderlich, sodass die Geschwindigkeit sehr hoch ist.

Das Betriebssystem unterteilt Speicher und Festplatte im Allgemeinen in Blöcke fester Größe, von denen jeder als Seite bezeichnet wird, und Speicher und Festplatte tauschen Daten in Seiteneinheiten aus. Das Datenbanksystem stellt die Größe eines Indexknotens auf die Größe einer Seite ein, sodass ein Knoten in einem I/O vollständig geladen werden kann. Und durch die Nutzung der Vorlesefunktion können auch benachbarte Knoten vorab geladen werden.

MySQL-Indizes

Indizes werden auf der Ebene der Speicher-Engine und nicht auf der Serverebene implementiert. Daher verfügen unterschiedliche Speicher-Engines über unterschiedliche Indextypen und Implementierungen.

1. B+Baumindex

Ist der Standardindextyp für die meisten MySQL-Speicher-Engines.

Da nicht die gesamte Tabelle gescannt werden muss, sondern nur der Baum durchsucht werden muss, ist die Suchgeschwindigkeit viel höher.

Neben der Suche kann es auch zum Sortieren und Gruppieren verwendet werden.

Sie können mehrere Spalten als Indexspalten angeben und mehrere Indexspalten bilden zusammen den Schlüssel.

Gilt für die Suche nach vollständigen Schlüsselwerten, Schlüsselwertbereichen und Schlüsselpräfixen. Die Schlüsselpräfixsuche ist dabei nur auf die Suche nach dem ganz linken Präfix anwendbar. Erfolgt die Suche nicht in der Reihenfolge der Indexspalten, kann der Index nicht verwendet werden.

Der B+Tree-Index von InnoDB ist in einen Primärindex und einen Hilfsindex unterteilt. Das Datenfeld des Blattknotens des Primärindexes zeichnet den kompletten Datensatz auf. Diese Indizierungsmethode wird als gruppierter Index bezeichnet. Da es unmöglich ist, Datenzeilen an zwei verschiedenen Orten zu speichern, kann eine Tabelle nur einen gruppierten Index haben.

Das Datenfeld des Blattknotens des Hilfsindex zeichnet den Wert des Primärschlüssels auf. Wenn Sie den Hilfsindex für die Suche verwenden, müssen Sie daher zuerst den Primärschlüsselwert finden und dann im Primärindex suchen.

2. Hash-Index

Hash-Indizes können in O(1)-Zeit durchsucht werden, verlieren jedoch ihre Ordnungsmäßigkeit: Sie können nicht zum Sortieren und Gruppieren verwendet werden; sie unterstützen nur exakte Suchen und können nicht für Teilsuchen oder Bereichssuchen verwendet werden. Die InnoDB-Speicher-Engine verfügt über eine spezielle Funktion namens „adaptiver Hash-Index“. Wenn ein Indexwert sehr häufig verwendet wird, wird über dem B+Tree-Index ein Hash-Index erstellt. Dadurch erhält der B+Tree-Index einige der Vorteile des Hash-Index, z. B. eine schnelle Hash-Suche.

3. Volltextindizierung

Die MyISAM-Speicher-Engine unterstützt die Volltextindizierung, die zum Suchen von Schlüsselwörtern im Text verwendet wird, anstatt sie direkt auf Gleichheit zu vergleichen.

Die Suchbedingung verwendet MATCH AGAINST anstelle des normalen WHERE.

Der Volltextindex wird mithilfe eines invertierten Index implementiert, der die Zuordnung von Schlüsselwörtern zu den Dokumenten aufzeichnet, in denen sie sich befinden.

Die Speicher-Engine InnoDB unterstützt seit MySQL Version 5.6.4 auch die Volltextindizierung.

4. Geodatenindex

Die MyISAM-Speicher-Engine unterstützt räumliche Datenindizes (R-Tree) und kann zur Speicherung geografischer Daten verwendet werden. Räumliche Datenindizes indizieren Daten aus allen Dimensionen und können jede Dimension effektiv für kombinierte Abfragen verwenden. Zur Datenpflege müssen GIS-bezogene Funktionen verwendet werden.

Indexoptimierung

1. Unabhängige Spalten

Bei der Ausführung einer Abfrage darf die Indexspalte nicht Teil eines Ausdrucks oder eines Funktionsparameters sein, da der Index sonst nicht verwendet werden kann. Beispielsweise kann die folgende Abfrage den Index der Spalte „actor_id“ nicht verwenden:

WÄHLEN SIE actor_id AUS sakila.actor, WO actor_id + 1 = 5;

2. Mehrspaltiger Index

Wenn Sie mehrere Spalten als Bedingungen für eine Abfrage verwenden müssen, bietet die Verwendung eines mehrspaltigen Indexes eine bessere Leistung als die Verwendung mehrerer einspaltiger Indizes. Beispielsweise ist es in der folgenden Anweisung am besten, actor_id und film_id als mehrspaltige Indizes festzulegen.

WÄHLEN SIE Film-ID, Schauspieler-ID AUS sakila.film_actor, WO Schauspieler-ID = 1 UND Film-ID = 1;

3. Reihenfolge der Indexspalten

Setzen Sie die selektivsten Indexspalten an den Anfang.

Unter der Selektivität eines Index versteht man das Verhältnis der eindeutigen Indexwerte zur Gesamtzahl der Datensätze. Der Maximalwert ist 1. In diesem Fall verfügt jeder Datensatz über einen entsprechenden eindeutigen Index. Je höher die Selektivität, desto effizienter die Abfrage.

Beispielsweise weist in den unten angezeigten Ergebnissen die Spalte „customer_id“ eine höhere Selektivität auf als die Spalte „staff_id“. Daher ist es am besten, die Spalte „customer_id“ an den Anfang des mehrspaltigen Indexes zu setzen.

Wählen Sie COUNT (DISTINCT staff_id) / COUNT (*) AS staff_id_selectivity,
COUNT(DISTINCT Kunden-ID)/COUNT(*) AS Kunden-ID-Selektivität,
ZÄHLEN(*)
VON Zahlung;

staff_id_selectivity: 0,0001
Kunden-ID-Selektivität: 0,0373
 ANZAHL(*): 16049

4. Präfixindex

Für Spalten der Typen BLOB, TEXT und VARCHAR müssen Sie einen Präfixindex verwenden, um nur die Anfangszeichen zu indizieren.

Die Auswahl der Präfixlänge muss basierend auf der Indexselektivität bestimmt werden.

5. Abdeckungsindex

Der Index enthält die Werte aller Felder, die abgefragt werden müssen.

Es bietet folgende Vorteile:

• Indizes sind normalerweise viel kleiner als die Größe von Datenzeilen, und das Lesen nur des Indexes kann die Menge der Datenzugriffe erheblich reduzieren.
• Einige Speicher-Engines (wie etwa MyISAM) speichern nur Indizes im Speicher zwischen und verlassen sich beim Zwischenspeichern der Daten auf das Betriebssystem. Daher ist der Zugriff auf den Index allein ohne die Verwendung von Systemaufrufen (die normalerweise zeitaufwändig sind) möglich.
• Wenn der sekundäre Index die Abfrage abdecken kann, ist für die InnoDB-Engine kein Zugriff auf den primären Index erforderlich.

6. Prinzip des ganz linken Präfixes

Wie der Name schon sagt, ist der äußerste Linke zuerst da und jeder aufeinanderfolgende Index kann beginnend vom äußersten Linken abgeglichen werden.

Das Wesentliche des gemeinsamen Indexes:

Wenn Sie einen gemeinsamen Index (a,b,c) erstellen, entspricht dies dem Erstellen eines einspaltigen Index (a). Wenn Sie möchten, dass der gemeinsame Index (a,b) und der gemeinsame Index (a,b,c) wirksam werden, können Sie nur die drei Kombinationen von a und a,b und a,b,c verwenden.

Vorteile von Indizes

• Dadurch wird die Anzahl der Datenzeilen, die der Server scannen muss, erheblich reduziert.
• Hilft dem Server, Sortieren und Gruppieren zu vermeiden, und vermeidet die Erstellung temporärer Tabellen (B+Tree-Indizes sind geordnet und können für ORDER BY- und GROUP BY-Operationen verwendet werden. Temporäre Tabellen werden hauptsächlich während des Sortierens und Gruppierens erstellt. Da Sortieren und Gruppieren nicht erforderlich sind, müssen keine temporären Tabellen erstellt werden).
• Konvertieren Sie zufällige E/A in sequentielle E/A (B+Tree-Index ist geordnet und speichert benachbarte Daten zusammen).

Bedingungen für die Verwendung von Indizes

• Bei sehr kleinen Tabellen ist ein einfacher vollständiger Tabellenscan in den meisten Fällen effizienter als die Indizierung.
• Für mittelgroße bis große Tabellen sind Indizes sehr effektiv;
• Bei sehr großen Tabellen steigen jedoch die Kosten für die Erstellung und Pflege der Indizes entsprechend. In diesem Fall ist eine Technologie erforderlich, die einen abzufragenden Datensatz direkt unterscheidet, anstatt jeweils einen Datensatz abzugleichen. Beispielsweise kann eine Partitionierungstechnologie verwendet werden.

Zusammenfassung

Der Index ist eine sehr wichtige Funktion in MySQL. Wenn Sie den Index bei der täglichen Entwicklung sinnvoll einsetzen können, können Sie die Ausführungsleistung von SQL-Anweisungen erheblich verbessern. Daher ist es sehr wichtig, die dahinter stehenden Prinzipien zu verstehen.

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, er wird für jedermanns Studium hilfreich sein. Ich hoffe auch, dass jeder 123WORDPRESS.COM unterstützen wird.