Detaillierte Erläuterung des MySQL-Index (Zusammenfassung)

In der obigen „Analyse des MySQL-Ausführungsprozesses“ haben wir hauptsächlich den Ausführungsprozess von SQL-Anweisungen auf der Serverebene vorgestellt

Lassen Sie uns ihn analysieren Werfen wir noch einmal einen Blick auf die Ausführungsschritte spezifischer Anweisungen auf der Engine-Ebene. Lassen Sie uns zunächst den Index

Index verstehen Die Entstehung des Index dient eigentlich dazu, die Effizienz der Datenabfrage zu verbessern, ist wie das Inhaltsverzeichnis eines Buches

Datenstruktur

Gemeinsame Datenstrukturen umfassen geordnete Hash-Tabellen Arrays und Suchbäume

Eine Hash-Tabelle ist eine Struktur, die Daten mithilfe eines Schlüsselwerts speichert. Solange wir den zu findenden Wert eingeben, der der Schlüssel ist, können wir den entsprechenden Wert finden Wert. Die Idee des Hashings ist sehr einfach. Fügen Sie den Wert in das Array ein, verwenden Sie eine Hash-Funktion, um den Schlüssel in eine Position umzuwandeln, und fügen Sie dann den Wert an der entsprechenden Position des Arrays ein.

Zwangsläufig mehrere Schlüsselwerte passieren Die Konvertierung der Hash-Funktion führt zum gleichen Wert. Eine Möglichkeit, mit dieser Situation umzugehen, besteht darin, eine verknüpfte Liste

Hash-Tabelle zu erstellen. Diese Struktur eignet sich für Szenarien, in denen es nur gleichwertige Abfragen gibt Geordnetes Array in Die Leistung sowohl in äquivalenten Abfrage- als auch in Bereichsabfrageszenarien ist sehr gut

Wenn Sie nur die Abfrageeffizienz betrachten, sind geordnete Arrays sehr gut. Es wird jedoch problematisch, wenn Sie einen Datensatz in der Mitte einfügen müssen. Die Kosten sind zu hoch.

Der geordnete Array-Index ist nur geeignet für statische Speicher-Engines

Die Merkmale des binären Suchbaums sind: Der linke Sohn jedes Knotens ist kleiner als der übergeordnete Knoten, und der übergeordnete Knoten ist kleiner als der rechte Sohn

Um die Abfragekomplexität O(log(N)) beizubehalten, müssen Sie natürlich dafür sorgen, dass der Baum ein ausgeglichener Binärbaum bleibt. Um diese Garantie zu gewährleisten, beträgt die zeitliche Komplexität der Aktualisierung ebenfalls O(log(N))

Binärbäume sind die effizienteste Suche, aber tatsächlich verwenden die meisten Datenbankspeicher keine Binärbäume. Der Grund dafür ist, dass der Index nicht nur im Speicher vorhanden ist, sondern auch auf die Festplatte geschrieben wird

Damit eine Abfrage so wenig Festplatte wie möglich liest, muss der Abfrageprozess auf möglichst wenige Datenblöcke zugreifen. Dann sollten wir keine binären Bäume verwenden, sondern „N-äre“ Bäume. Hier hängt das „N“ im „N-ary“-Baum von der Größe des Datenblocks ab

N-ary-Baum wird aufgrund seiner Leistungsvorteile beim Lesen und Schreiben sowie bei der Anpassung häufig in Datenbanken verwendet Festplattenzugriffsmuster. Die Engine ist defekt

InnoDBs Indexmodell

In InnoDB werden Tabellen in Form von Indizes basierend auf der Primärschlüsselreihenfolge in diesem Speicher gespeichert Die Methode wird als Index-organisierte Tabelle bezeichnet. InnoDB verwendet das B+-Baum-Indexmodell, sodass die Daten im B+-Baum gespeichert werden

Jeder Index entspricht einem B+-Baum in InnoDB Entsprechend dem Inhalt des Blattknotens, dem Indextyp ist unterteilt in Die Blattknoten von Primärschlüsselindizes und Nicht-Primärschlüsselindizes

speichern die gesamte Datenzeile. In InnoDB wird der Primärschlüsselindex auch Clustered-Index genannt

Der Inhalt der Blattknoten des Nicht-Primärschlüsselindex ist der Wert des Primärschlüssels. In InnoDB werden Nicht-Primärschlüsselindizes auch Sekundärindizes genannt

Abfragen, die auf Nicht-Primärschlüsselindizes basieren, erfordern das Scannen eines weiteren Indexbaums (Tabellenrückgabe). Daher sollten wir versuchen, die Primärschlüsselabfrage

Indexpflege

B+-Baum zu verwenden, um die Indexordnung beim Einfügen neuer Werte aufrechtzuerhalten

Wenn der neu eingefügte ID-Wert kleiner als der ursprüngliche ist, ist es relativ mühsam, die nachfolgenden Daten logisch zu verschieben, um Platz dafür zu schaffenUnd noch schlimmer, wenn die Daten Wenn die Seite voll ist, müssen Sie gemäß dem B+-Baumalgorithmus eine neue Datenseite beantragen und dann einige Daten dorthin verschieben. Dieser Vorgang wird als Seitenteilung bezeichnet. In diesem Fall leidet natürlich die Leistung.

Neben der Leistung wirken sich Seitenaufteilungsvorgänge auch auf die Datenseitennutzung aus. Die ursprünglich auf einer Seite platzierten Daten werden nun auf zwei Seiten aufgeteilt und die Gesamtplatznutzung wird um etwa 50 % reduziert.

Natürlich wird es Spaltungen und Fusionen geben. Wenn zwei benachbarte Seiten aufgrund gelöschter Daten eine geringe Auslastung aufweisen, werden die Datenseiten zusammengeführt. Der Zusammenführungsprozess kann als der umgekehrte Prozess des Aufteilungsprozesses betrachtet werden

Der Einfügungsdatenmodus des automatisch zunehmenden Primärschlüssels entspricht dem zuvor erwähnten inkrementellen Einfügungsszenario. Jedes Mal, wenn ein neuer Datensatz eingefügt wird, handelt es sich um einen Anhängevorgang. Dabei werden weder andere Datensätze verschoben, noch wird die Teilung der Blattknoten ausgelöst.

Wenn Felder mit Geschäftslogik als Primärschlüssel verwendet werden, ist es oft nicht einfach, eine geordnete Einfügung sicherzustellen, sodass die Kosten für das Schreiben von Daten relativ hoch sind

Je kleiner der Primärschlüssel ist Länge, die Blattknoten gewöhnlicher Indizes Je kleiner sie sind, desto kleiner ist der von gewöhnlichen Indizes belegte Platz

Aus Sicht der Leistung und des Speicherplatzes ist die automatische Inkrementierung des Primärschlüssels daher häufig eine Vernünftigere Auswahl

Gibt es noch welche? Ist das Szenario für die direkte Verwendung von Geschäftsfeldern als Primärschlüssel geeignet? Einige Geschäftsszenarioanforderungen lauten wie folgt:

1 Es gibt nur einen Index

2.

Dies ist ein typisches KV-Szenario

Covering Index

Wenn die ausgeführte Anweisung ID aus t auswählt, müssen Sie nur den Wert von ID überprüfen. Der Wert von ID befindet sich bereits im k-Indexbaum, sodass die Abfrageergebnisse direkt bereitgestellt werden können, ohne zur Tabelle zurückkehren zu müssen . Mit anderen Worten: In dieser Abfrage hat der Index k unsere Abfrageanforderungen „abgedeckt“. Wir nennen ihn einen abdeckenden Index

, da der abdeckende Index die Anzahl der Baumsuchen reduzieren und die Abfrageleistung erheblich verbessern kann Daher ist die Verwendung des Covering-Index eine gängige Methode zur Leistungsoptimierung.

Index-Pushdown

Wenn das Prinzip des Präfixes ganz links erfüllt ist, kann das Präfix ganz links verwendet werden Eintrag im Index. An dieser Stelle möchten Sie vielleicht fragen: Was passiert mit den Teilen, die nicht mit dem Präfix ganz links übereinstimmen?

Die in MySQL 5.6 eingeführte Index-Pushdown-Optimierung kann während des Indexdurchlaufprozesses zuerst die im Index enthaltenen Felder beurteilen, Datensätze, die die Bedingungen nicht erfüllen, direkt herausfiltern und die Anzahl der Tabellenrückgaben reduzieren

Prinzip des Präfixes ganz links

Nicht nur die gesamte Definition des Index, solange das Präfix ganz links erfüllt ist, kann der Index verwendet werden, um den Abruf zu beschleunigen

Wie ordne ich beim Erstellen eines gemeinsamen Index die Reihenfolge der Felder im Index an?

Unser Bewertungskriterium ist hier die Wiederverwendbarkeit des Index. Da das Präfix ganz links unterstützt werden kann und bereits ein gemeinsamer Index von (a, b) vorhanden ist, besteht im Allgemeinen keine Notwendigkeit, einen separaten Index für a zu erstellen. Daher besteht das erste Prinzip darin, dass, wenn durch Anpassen der Reihenfolge ein Index weniger beibehalten werden kann, diese Reihenfolge oft diejenige ist, die priorisiert werden muss

Präfixindex

Am häufigsten verwenden Das Prinzip des linken Präfixes ermöglicht es Ihnen, einen Teil einer Zeichenfolge als Index zu definieren. Wenn die Anweisung, mit der Sie den Index erstellen, die Präfixlänge nicht angibt, enthält der Index standardmäßig die gesamte Zeichenfolge

. Dies hat jedoch den Nachteil, dass die Anzahl der zusätzlichen Datensatzscans erhöht werden kann Index ist derselbe Bedarf weiterer Vergleich

Verwenden Sie den Präfixindex und definieren Sie die Länge. Sie können Platz sparen, ohne zu viel zusätzliche Abfragekosten hinzuzufügen

Sie können den statistischen Index verwenden Wie Es gibt viele verschiedene Werte, um zu bestimmen, wie lange das Präfix verwendet werden soll, wodurch die Anzahl der Scans reduziert wird

Der Einfluss des Präfixindex auf den Abdeckungsindex

Verwendung Präfixindex Der Covering-Index optimiert nicht nur die Abfrageleistung, dies ist auch ein Faktor, den Sie berücksichtigen müssen, wenn Sie entscheiden, ob Sie einen Präfixindex

Reverse Storage und Hash Storage

Für Felder wie Postfächer kann die Verwendung von Präfixindizes gut funktionieren. Was sollten wir jedoch tun, wenn wir auf eine Situation stoßen, in der die Präfixunterscheidung nicht gut genug ist?

Die erste Möglichkeit besteht darin, die Speicherung in umgekehrter Reihenfolge zu verwenden. Wenn Sie die ID-Nummer speichern, speichern Sie sie verkehrt herum

Die zweite Möglichkeit besteht darin, das Hash-Feld zu verwenden. Sie können ein weiteres Ganzzahlfeld in der Tabelle erstellen, um den Bestätigungscode des Personalausweises zu speichern und einen Index für dieses Feld zu erstellen

Empfohlenes kostenloses Lernvideo-Tutorial:

MySQL-Video-Tutorial

Das obige ist der detaillierte Inhalt vonDetaillierte Erläuterung des MySQL-Index (Zusammenfassung). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!