Detaillierte Erläuterung der zugrunde liegenden Implementierung des absteigenden Index, einer neuen Funktion von MySQL 8

Was ist ein absteigender Index?

Sie kennen sich vielleicht mit Indizes aus, aber nicht mit absteigenden Indizes. Tatsächlich sind absteigende Indizes eine Teilmenge von Indizes.

Normalerweise verwenden wir die folgende Anweisung, um einen Index zu erstellen:

Erstelle den Index idx_t1_bcd auf t1(b,c,d);

Das obige SQL bedeutet, einen gemeinsamen Index für die drei Felder b, c und d in der Tabelle t1 zu erstellen.

Was jedoch nicht jeder weiß, ist, dass das obige SQL tatsächlich dem folgenden SQL entspricht:

Erstelle den Index idx_t1_bcd auf t1(b asc,c asc,d asc);

asc bedeutet aufsteigende Reihenfolge. Der mit dieser Syntax erstellte Index wird als aufsteigender Index bezeichnet. Das heißt, wenn wir normalerweise Indizes erstellen, erstellen wir aufsteigende Indizes.

Sie denken vielleicht, dass Sie beim Erstellen eines Indexes „asc“ für das Feld festlegen können. Können Sie also auch „desc“ festlegen?

Natürlich sind auch beispielsweise folgende drei Aussagen möglich:

Erstelle den Index idx_t1_bcd auf t1(b desc, c desc, d desc);
Erstelle den Index idx_t1_bcd auf t1(b asc, c desc, d desc);
Erstelle den Index idx_t1_bcd auf t1(b asc,c asc,d desc);

Diese Syntax wird auch in MySQL unterstützt. Der mit dieser Syntax erstellte Index wird als absteigender Index bezeichnet. Das Hauptproblem besteht darin, dass er vor MySQL 8.0 nur auf Syntaxebene unterstützt wurde und es auf der untersten Ebene keine echte Unterstützung gab.

Wir verwenden Mysql7 und Mysql8, um die folgenden Beispiele zu veranschaulichen:

Erstellen Sie in Mysql7 bzw. Mysql8 eine Tabelle mit fünf Feldern: a, b, c, d und e:

Tabelle t1 erstellen (
ein int-Primärschlüssel,
b int,
c int,
d int,
e varchar(20)
) Motor = InnoDB;

Erstellen Sie dann separat einen absteigenden Index:


Erstelle den Index idx_t1_bcd auf t1(b desc, c desc, d desc);

Nach der erfolgreichen Erstellung verwenden wir das folgende SQL, um die Indexinformationen anzuzeigen:


Index von t1 anzeigen;

In MySQL 7 erhalten Sie das Ergebnis:

In MySQL 8 erhalten Sie das Ergebnis:

Uns interessieren nur die drei Zeilen mit dem Schlüsselnamen idx_t1_bcd. Wenn Sie genau hinschauen, werden Sie feststellen, dass die Ergebnisse des Felds „Sortierung“ in diesen beiden Ergebnissen unterschiedlich sind:

• In MySQL 7 ist das Ergebnis des Sortierfelds A, A, A, was bedeutet, dass die Sortiermethode der drei Felder b, c und d aufsteigend ist.
• In MySQL 8 ist das Ergebnis des Sortierfelds D, D, D, was bedeutet, dass die Sortiermethode der drei Felder b, c und d desc ist.

Als wir den Index erstellt haben, haben wir jedoch auf Syntaxebene klar angegeben, dass die Sortiermethode für die drei Felder b, c und d desc ist. Dies zeigt, dass in MySQL 7 absteigende Indizes nur auf Syntaxebene unterstützt werden und es auf der untersten Ebene keine echte Unterstützung gibt und der Index auf aufsteigende Reihenfolge festgelegt ist. In MySQL 8 werden absteigende Indizes tatsächlich von unten nach oben unterstützt .

Bisher sollte jeder ein allgemeines Verständnis von aufsteigenden und absteigenden Indizes haben, aber sie verstehen sie nicht wirklich, weil nicht jeder weiß, wie aufsteigende und absteigende Indizes auf der untersten Ebene implementiert werden.

Aufsteigender Index als Basiswert für die Implementierung

Wir wissen, dass Indizes verwendet werden, um die Abfragegeschwindigkeit zu verbessern, aber warum können Indizes die Abfragegeschwindigkeit verbessern?

Gegeben sei eine Zahlenfolge wie [1,3,7,9,2,5,4,6,8], die eine ungeordnete Folge oder ein Array ist. Wenn Sie die Abfragegeschwindigkeit dieser Folge erhöhen möchten, was werden Sie zuerst tun? Ich glaube, die meisten Leute können zunächst an das Sortieren denken, indem sie diese ungeordnete Sequenz in aufsteigender Reihenfolge sortieren, um beispielsweise [1,2,3,4,5,6,7,8,9] zu erhalten. Nachdem wir diese geordnete Sequenz haben, können wir Algorithmen wie die binäre Suche verwenden, um die Abfragegeschwindigkeit dieser Sequenz zu verbessern.

Was ich Ihnen anhand dieses Beispiels sagen möchte : Wenn Sie die Abfragegeschwindigkeit eines Datensatzes erhöhen möchten, können Sie die Daten zuerst sortieren.

Dasselbe gilt also auch für die in der Mysql-Tabelle gespeicherten Daten. Wenn wir die Abfragegeschwindigkeit dieser Tabelle erhöhen möchten, können wir zunächst die Daten in dieser Tabelle sortieren. Dann enthält eine Datenzeile in der Tabelle viele Felder. Diese Datenzeilen möchten wir nun sortieren. Welche Felder sollen wir verwenden, um die Reihenfolge zu bestimmen? Dies ist der Index. Die Spalte, die Sie beim Erstellen des Index angeben, wird zum Sortieren der Datenzeilen in der Tabelle verwendet.

Beispielsweise verwenden wir immer noch die oben erstellte t1-Tabelle und fügen 8 Datensätze in die t1-Tabelle ein:

in t1-Werte einfügen (4,3,1,1,'d');
in t1-Werte einfügen (1,1,1,1, „a“);
in t1-Werte einfügen (8,8,8,8, „h“);
in t1-Werte einfügen (2,2,2,2,'b');
in t1-Werte einfügen (5,2,3,5, 'e');
in t1-Werte einfügen (3,3,2,2, „c“);
in t1-Werte einfügen (7,4,5,5, „g“);
in t1-Werte einfügen (6,6,4,4,'f');

Anschließend müssen die Daten in der Datei gespeichert werden, sodass das Format zum Speichern der Daten in der Datei ungefähr wie folgt lautet und die Reihenfolge mit der Einfügereihenfolge übereinstimmt:

4311d
1111a
8888h
2222b
5235e
Nr. 3322c
7455 g
6644f

Beachten Sie, dass t1 die Innodb-Speicher-Engine ist und das Feld a der Primärschlüssel ist. Daher sortiert die Innodb-Speicher-Engine beim Verarbeiten der eingefügten Daten nach dem Primärschlüssel. Das heißt, das Format zum Speichern dieser Daten in der oben genannten Datei ist ungenau. Da der Artikel nicht zu lang werden soll, werde ich nicht näher darauf eingehen. Interessierte Studenten können dem offiziellen Bericht folgen: 1:25. Ich werde einen Artikel schreiben, in dem ich speziell die spezifische Implementierung von Indizes in Innodb erkläre, einschließlich der Generierung von B+-Bäumen.

Wenn wir nach Daten basierend auf der obigen Speichermethode suchen, z. B. nach der Datensatzzeile mit a=3, müssen wir mit der ersten Datensatzzeile beginnen, also müssen wir 6 Mal suchen. Wenn wir die obigen Daten nach der Größe des Felds a sortieren:

1111a
2222b
Nr. 3322c
4311d
5235e
6644f
7455 g
8888h

Wenn wir nach dem Sortieren immer noch nach der Zeile mit a=3 suchen, müssen wir nur dreimal suchen. Ein weiterer Vorteil besteht darin, dass wir, wenn wir jetzt die Datenzeile a = 3,5 finden müssen, bei Verwendung der Speichermethode vor dem Sortieren alle 8 Datenzeilen abfragen müssen, um schließlich zu bestätigen, dass die Datenzeile a = 3,5 nicht vorhanden ist. Wenn wir die Speichermethode nach dem Sortieren verwenden, müssen wir nur 4 Mal prüfen, denn wenn Sie die Datensatzzeile 4311d finden, werden Sie feststellen, dass 4> 3,5 ist, was bedeutet, dass die Datensatzzeile a = 3,5 nicht vorhanden ist.

Und wenn wir jetzt einen Index für t1 erstellen, genau wie wir oben den Index erstellt haben, wenn wir das folgende SQL schreiben:

Erstelle den Index idx_t1_bcd auf t1(b,c,d);

Diese SQL-Anweisung gibt an, dass ein Index für t1 erstellt werden soll. Die Indexfelder sind b, c und d und sind in aufsteigender Reihenfolge. Daher werden die Originaldaten tatsächlich nach den drei Feldern b, c und d sortiert. Nach der Sortierung sieht das Ergebnis ungefähr so ​​aus:

1111a
2222b
5235e
4311d
Nr. 3322c
7455 g
6644f
8888h

Sie können genauer hinschauen. Die obigen Datensätze sind nach den drei Feldern b, c und d sortiert. Beispielsweise sind die Werte der drei Felder b, c und d in 1111a 111 und die Werte der drei Felder b, c und d in 2222b sind 222. 111 ist kleiner als 222, daher wird die entsprechende Zeile an erster Stelle eingestuft.

Welche Vorteile bietet diese Art der Datensortierung? Tatsächlich sind die Vorteile denen der Sortierung nach Feld a ähnlich. Wenn Sie beispielsweise nach Daten suchen möchten, bei denen b=4, c=4 und d=4 ist, kann die Suche schneller sein. Tatsächlich ist dies das Prinzip der Indizierung: Wir erstellen einen Index für eine Tabelle, was bedeutet, dass wir die Daten in der Tabelle sortieren, und die sortierten Daten können die Suchgeschwindigkeit verbessern.

Ein weiterer zu beachtender Punkt ist, dass es viele Möglichkeiten zum Sortieren gibt oder Sie einige Datenstrukturen verwenden können, z. B. Binärbäume, Rot-Schwarz-Bäume und B+-Bäume. Diese Datenstrukturen sortieren tatsächlich Daten, aber die Sortierformen sind unterschiedlich. Jede Datenstruktur hat ihre eigenen Merkmale, und jeder sollte wissen, dass die in MySQL am häufigsten verwendete der B+-Baum ist.

Ich glaube, dass jeder nach der Lektüre dieses Artikels ein neues Verständnis von Indizes haben sollte. Der einzige Unterschied besteht darin, dass die oben angeführten Beispiele alle in aufsteigender Reihenfolge sind und die sortierten Daten nicht nur die Abfragegeschwindigkeit verbessern können, sondern auch für die Sortierung nützlich sind. Wenn wir beispielsweise t1 jetzt nach b asc, c asc, d asc sortieren möchten, bedeutet dies, dass die Daten in der t1-Tabelle im Voraus nach b, c, d sortiert wurden, wenn für diese Sortierung ein aufsteigender Index von b, c, d in der t1-Tabelle festgelegt wurde. Daher kann die Anweisung „order by“ die sortierten Daten direkt verwenden, ohne sie erneut mit Filesort sortieren zu müssen.

Und wenn unsere Reihenfolge „order by b desc, c desc, d desc“ lautet, können wir auch den aufsteigenden Index von b, c, d verwenden, denn wenn es „order by b asc, c asc, d asc“ lautet, können wir von oben nach unten durchlaufen, und wenn es „order by b desc, c desc, d desc“ lautet, können wir von unten nach oben durchlaufen.

Was wäre also, wenn die Sortierung nach b aufsteigend, c absteigend, d absteigend wäre? Stimmt es, dass diese Order-By-Anweisung den aufsteigenden Index von b, c und d nicht nutzen kann?

Zu diesem Zeitpunkt ist ein absteigender Index erforderlich.

Absteigender Index zugrunde liegende Implementierung

Wir haben viel Zeit damit verbracht, das Implementierungsprinzip des aufsteigenden Index vorzustellen. Zusammenfassend geht es darum, die Daten in der Tabelle in aufsteigender Reihenfolge entsprechend der Größe des angegebenen Felds zu sortieren.

Was ist aufsteigende Reihenfolge? Nach dem Größenvergleich der Daten steht der kleinere oben und der größere unten, bzw. wenn es sich um einen B+-Baum handelt, steht der kleinere links und der größere rechts. Und absteigende Reihenfolge bedeutet, dass die größeren oben und die kleineren unten sind, oder wenn es ein B+-Baum ist, sind die größeren links und die kleineren rechts.

Also für die obigen Originaldaten:

4311d
1111a
8888h
2222b
5235e
Nr. 3322c
7455 g
6644f

Wenn wir diese Daten nach einer absteigenden Reihenfolge sortieren, lautet das Ergebnis:

8888h
7455 g
6644f
5235e
4311d
Nr. 3322c
2222b
1111a

Es ist ganz einfach, oder? Wenn wir die Daten nach b desc, c desc, d desc sortieren, erhalten wir:

8888h
6644f
7455 g
Nr. 3322c
4311d
5235e
2222b
1111a

Es ist auch sehr einfach. Was ist, wenn wir die Daten nach b desc, c asc, d desc sortieren möchten? Ist das nicht ein bisschen verwirrend?

Tatsächlich ist es nicht schwierig. Beim Sortieren wird eigentlich die Größe der Daten verglichen. Lassen Sie uns die folgenden drei Datenzeilen verwenden, um dies zu simulieren:

Nr. 3322c
7455 g
4311d

Sortieren Sie zunächst nach b desc, c desc, d desc. Die Ergebnisse sind wie folgt:

7455 g
Nr. 3322c
4311d

Sortieren Sie nach b desc, c asc, d desc. Die Ergebnisse sind wie folgt:

7455 g
4311d
Nr. 3322c

Vielleicht haben einige von Ihnen bereits verstanden, dass b desc tatsächlich bedeutet, dass die Daten im Feld b mit den größeren Daten oben und die Daten im Feld c mit den kleineren Daten unten stehen. Wenn die Daten gleich sind, wird Feld c verglichen und Feld c in aufsteigender Reihenfolge sortiert, d. h. die Daten im Feld c mit den kleineren Daten stehen unten und die Daten im Feld c mit den größeren Daten stehen oben. So haben wir das obige Ergebnis erhalten.

Dies ist der absteigende Index.

Zusammenfassen

Tatsächlich sind aufsteigender und absteigender Index nur unterschiedliche Sortiermethoden. Nachdem der absteigende Index in MySQL 8 implementiert wurde, sind wir bei der Erstellung von Indizes flexibler. Wir können geeignete Indizes gemäß den vom Unternehmen geforderten Sortierregeln erstellen, was Ihre Abfrage beschleunigen kann.

Natürlich erklärt dieser Artikel nur das Prinzip. Jeder muss wissen, dass der B+-Baum, der zum Sortieren in MySQL verwendet wird, nicht die sehr einfache Methode ist, die ich oben als Beispiel angegeben habe. Aber auch wenn der B+-Baum verwendet wird, ist das Prinzip dasselbe, man vergleicht nur die Größe der Daten.

Ein weiterer Punkt ist, dass derzeit nur die Innodb-Speicher-Engine absteigende Indizes unterstützt .

Dies ist das Ende dieses Artikels über die detaillierte Implementierung des zugrunde liegenden absteigenden Indexes der neuen Funktion von MySQL 8. Weitere relevante Inhalte zum absteigenden Index von MySQL 8 finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder durchsuchen Sie die folgenden verwandten Artikel weiter. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!