Detaillierte Erläuterung des MySQL InnoDB-Sekundärindex-Sortierbeispiels

Sortierproblem

Ich habe kürzlich auf Geek Time „45 Vorlesungen zur MySQL-Praxis“ gelesen, wodurch mein unzureichendes Verständnis der sekundären InnoDB-Indizes korrigiert wurde. Dies ist eine gute Gelegenheit, den relevanten Inhalt zusammenzufassen.

PS: Alle Tests in diesem Artikel basieren auf MySQL 8.0.13.

Lassen Sie mich zunächst die Frage stellen. Die durch das folgende SQL erstellte Tabelle hat zwei Abfrageanweisungen. Welcher Index wird nicht benötigt?

TABELLE „Geek“ erstellen (
 `a` int(11) NICHT NULL,
 `b` int(11) NICHT NULL,
 `c` int(11) NICHT NULL,
 `d` int(11) NICHT NULL,
 PRIMÄRSCHLÜSSEL (`a`,`b`),
 SCHLÜSSEL `c` (`c`),
 SCHLÜSSEL `ca` (`c`,`a`),
 SCHLÜSSEL `cb` (`c`,`b`)
)ENGINE=InnoDB;

Wählen Sie * aus Geek, wobei c = N, sortiert nach der Grenze 1;
Wählen Sie * aus Geek, wobei c = N, Sortierung nach b, Grenze 1;

Die Antwort des Autors ist, dass die Datenmodelle der Indizes c und ca gleich sind, sodass ca redundant ist. Warum? ?

Wir wissen, dass der Sekundärindex nicht die Position der Zeile speichert, sondern den Wert des Primärschlüssels, und wir wissen auch, dass der Index geordnet ist.

Wenn c dasselbe Datenmodell wie ca hat, müssen die Blattknoten des sekundären Indexes nicht nur nach den Indexspalten, sondern auch nach den zugehörigen Primärschlüsselwerten sortiert werden.

Mein bisheriges Verständnis war, dass Sekundärindizes nur nach den Indexspalten sortiert werden und die Primärschlüsselwerte nicht sortiert werden.

Ich habe den Kolumnisten gefragt und die Antwort erhalten: Der Index c wird wie cab (Sekundärindex) sortiert, um sicherzustellen, dass der Primärschlüssel enthalten und in der richtigen Reihenfolge ist. (PS: Dies ist nicht der Originaltext, ich habe die Antwort erst nach dreimaligem Nachfragen erhalten).

Basierend auf der Idee, zuerst zu fragen, ob und dann, warum, haben wir einige Untersuchungen durchgeführt.

Ja oder nein?

Wenn Sie die InnoDB-Datendatei direkt anzeigen können, können Sie direkt erkennen, ob diese Sortierregel eingehalten wird. Leider handelt es sich um eine Binärdatei und es gibt kein geeignetes Tool, um sie anzuzeigen, also habe ich aufgegeben.

Später fand ich die MySQL-Handler-Anweisung, die Tabellen sowohl von MyISAM- als auch von InnoDB-Engines unterstützt. Die Handler-Anweisung bietet eine Schnittstelle für den direkten Zugriff auf die Tabellenspeicher-Engine.

Die folgende Syntax gibt das Lesen des ersten/vorherigen/nächsten/letzten Datensatzes des angegebenen Index in der angegebenen Tabelle an.

Handler Tabellenname/Tabellenname-Alias ​​liest Indexnamen zuerst/vorherig/nächst/letzter;

Lassen Sie uns dies mithilfe der Handler-Anweisung überprüfen. Erstellen Sie zunächst eine einfache Tabelle und fügen Sie einige Daten ein:

Tabelle t_simple erstellen (
 id int Primärschlüssel,
 v int,
 Schlüssel k_v (v)
)ENGINE=InnoDB STANDARD-CHARSET=utf8mb4;

in t_simple Werte (1, 5) einfügen;
in t_simple Werte (10, 5) einfügen;
in t_simple Werte (4, 5) einfügen;

In den obigen Einfügeanweisungen sind die Werte der Sekundärindexspalten gleich und die Primärschlüssel sind nicht in der richtigen Reihenfolge. Auf diese Weise können Sie während des Durchlaufs sehen, ob sie in der Reihenfolge der Primärschlüssel gespeichert sind.

mysql> Handler t_simple öffnen als ts;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> Handler ts liest k_v weiter;
+----+------+
| Ich würde | v |
+----+------+
| 1 | 5 |
+----+------+
1 Zeile im Satz (0,00 Sek.)

mysql> Handler ts liest k_v weiter;
+----+------+
| Ich würde | v |
+----+------+
| 4 | 5 |
+----+------+
1 Zeile im Satz (0,00 Sek.)

mysql> Handler ts liest k_v weiter;
+----+------+
| Ich würde | v |
+----+------+
| 10 | 5 |
+----+------+
1 Zeile im Satz (0,00 Sek.)

Aus den Ergebnissen können wir ersehen, dass wir die durchlaufenen Sekundärindizes in der Reihenfolge des Primärschlüssels durchlaufen, wenn ihre Werte gleich sind. Grundsätzlich lässt sich feststellen, dass die Sekundärindizes nicht nur nach den Indexspalten, sondern auch nach den Primärschlüsselwerten sortiert sind.

Warum?

Ich habe zuvor noch nie eine Erwähnung eines solchen Mechanismus in MySQL gesehen und die DBAs in meiner früheren Firma und bei meinem vorherigen Unternehmen wussten nichts davon.

Schließlich fand der DBA-Kollege Index Extensions, die folgende Beschreibung enthielten:

InnoDB erweitert jeden Sekundärindex automatisch, indem es die Primärschlüsselspalten anhängt. Betrachten Sie diese Tabellendefinition:

Tabelle erstellen t1 (
 i1 INT NICHT NULL STANDARD 0,
 i2 INT NICHT NULL STANDARD 0,
 d DATUM STANDARD NULL,
 Primärschlüssel (i1, i2),
 INDEX k_d (d)
)ENGINE = InnoDB;

InnoDB erweitert automatisch jeden Sekundärindex, hängt den Primärschlüsselwert an die Indexspalte an und verwendet die erweiterte zusammengesetzte Spalte als Indexspalte des Indexes. Für den k_v-Index der obigen t_simple-Tabelle ist der erweiterte Index die Spalte (v, id).

Der Optimierer entscheidet basierend auf den Primärschlüsselspalten des erweiterten sekundären Indexes, wie und ob dieser Index verwendet wird. Der Optimierer kann erweiterte sekundäre Indizes für Indexzugriffstypen wie „Ref“, „Range“ und „Index_merge“, lose Indexscans, Join- und Sortieroptimierungen sowie Min()/Max()-Optimierungen verwenden.

Sie können show variables like '%optimizer_switch%'; um zu prüfen, ob Indexerweiterungen aktiviert sind. Verwenden Sie SET optimizer_switch = 'use_index_extensions=on/off'; um sie zu aktivieren oder zu deaktivieren. Dies betrifft nur die aktuelle Sitzung.

Nach dem Testen kann der Effekt der Sortierung nach Primärschlüssel beim Zugriff mit dem Handler auch dann noch erzielt werden, wenn die Indexerweiterung der aktuellen Sitzung deaktiviert ist.

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.