Zusammenfassung häufiger Probleme mit MySQL-Indizes

F1: Welche Indizes hat die Datenbank? Was sind die Vor- und Nachteile?

1. B-Baum-Index: der von den meisten Datenbanken verwendete Index (innoDB verwendet B+-Baum). Es kann den Zugriff auf Daten, insbesondere die Suche nach Bereichsdaten, beschleunigen. Der Nachteil besteht darin, dass Sie nur von der äußersten linken Spalte des Index aus suchen können und keine Spalten im Index überspringen können. Wenn eine Spalte in der Abfrage eine Bereichsabfrage verwendet, können alle Spalten auf der rechten Seite nicht mithilfe der Indexoptimierung gesucht werden.

2. Hash-Index: basierend auf Hash-Tabelle implementiert. In MySQL unterstützt nur die Speicher-Engine explizit Hash-Suchen. Hash-Lookups sind sehr schnell, aber Hash-Indizes enthalten nur Hash-Werte und Zeilenzeiger und speichern keine Feldwerte. Daher können die Werte im Index nicht verwendet werden, um das Lesen von Zeilen zu vermeiden oder eine Sortierung durchzuführen. Da Hash-Indizes den gesamten Inhalt der indizierten Spalten zur Berechnung der Hash-Werte verwenden, unterstützen sie keine Suche nach teilweisen Spaltenübereinstimmungen. Hashes unterstützen nur Gleichheitsvergleiche und keine Bereichsabfragen. Sobald viele Hash-Konflikte auftreten, sind die Wartungskosten sehr hoch. InnoDB unterstützt „adaptiven Hash-Index“.

3. Volltextindex: Der Volltextindex ist ein spezieller Indextyp, der nach Schlüsselwörtern im Text sucht, anstatt Indexwerte zu vergleichen. Anfangs konnte es nur auf MyISAM verwendet werden, aber nach 5.6.24 unterstützte innoDB auch die Volltextindizierung. Volltextindexabfragen verwenden Match....against. Es kommt zu keinem Konflikt, wenn gleichzeitig Volltextsuch- und wertbasierte B-Tree-Indizes für dieselbe Spalte erstellt werden.

4. Räumlicher Datenindex (R-Tree-Index). MyISAM unterstützt den R-Tree-Index. Der Vorteil besteht darin, dass keine Präfixabfrage erforderlich ist und Daten aus allen Breitengraden indiziert werden, sodass es zum Speichern geografischer Daten verwendet werden kann. Der Nachteil besteht darin, dass Sie zur Datenverwaltung GIS-bezogene Funktionen von MySQL wie MBRCONTAINS() verwenden müssen. Da GIS in MySQL jedoch nicht perfekt ist, werden die meisten Leute diese Funktion nicht verwenden.

F2: Warum verwenden Sie keine binären Suchbäume oder Rot-Schwarz-Bäume als Datenbankindizes?

Wenn ein Binärbaum große Datenmengen verarbeitet, ist die Baumhöhe zu hoch. Obwohl die Indizierungseffizienz sehr hoch ist und logN erreicht, wird eine große Menge an Festplatten-E/A ausgeführt, was den Aufwand nicht rechtfertigt. Darüber hinaus kann das Löschen oder Einfügen von Daten dazu führen, dass sich die Datenstruktur in eine verknüpfte Liste ändert, was einen verbesserten Ausgleichsalgorithmus erfordert. Beim Einfügen und Löschen von Elementen in einem Rot-Schwarz-Baum ändert sich jedoch die Farbe und die Rotation (Linksrotation, Rechtsrotation) wird häufig durchgeführt, was Zeitverschwendung ist. Wenn die Datenmenge jedoch gering ist, können sie in einen Rot-Schwarz-Baum eingefügt werden. Zu diesem Zeitpunkt ist die zeitliche Komplexität des Rot-Schwarz-Baums geringer als die des B-Baums. Unter Berücksichtigung der oben genannten Punkte hat die Datenbank letztendlich B-Tree als Index gewählt.

F3: Anwendungsszenarien von B-Baum und B+-Baum:

1. B-Bäume werden häufig in Dateisystemen und einigen Datenbankindizes wie MongoDB verwendet.

2.B+-Baum wird hauptsächlich für MySQL-Datenbankindizes verwendet.

F4: Vorteile von B+-Baum gegenüber B-Baum

Jeder Knoten des B-Baums speichert nicht nur Indizes, die auf untergeordnete Knoten verweisen, sondern auch Datenfelder. Daher verfügt ein einzelner Knoten nicht über viele Indizes, die auf untergeordnete Knoten verweisen, der Baum ist hoch und die Anzahl der Festplatten-E/A-Vorgänge ist hoch. Die Höhe des B+-Baums ist niedriger und alle Daten werden in Blattknoten gespeichert, die sich alle auf derselben Ebene befinden. Daher ist die Abfrageleistung stabil und die Bereichssuche bequem.

F5: Fallstricke bei der Verwendung von Indizes beim Sortieren mehrerer Spalten

Eine key_part-Spezifikation kann mit ASC oder DESC enden. Diese Schlüsselwörter sind für zukünftige Erweiterungen zur Angabe der aufsteigenden oder absteigenden Indexwertspeicherung zulässig. Derzeit werden sie zwar geparst, aber ignoriert; Indexwerte werden immer in aufsteigender Reihenfolge gespeichert.

Laut der MySQL-Dokumentation können Sie beim Erstellen eines Indexes asc oder desc hinzufügen, zum Beispiel: add index idx(a asc,b desc) . MySQL ignoriert dies jedoch (was für eine Falle ...). Es scheint, dass desc nach Version 8.0 unterstützt wird.

Welche Auswirkungen wird dies haben?

Angenommen, es gibt die Spalten test1 und test2, beide vom Typ int.

Wir erstellen den Index ``idx1(test1,test2),
Angenommen, wir möchten nach Test1 und Test2 sortieren, zum Beispiel SQL

Erläutern Sie „Select * aus der Tabelle, sortiert nach Test1, Test2, Limit 1“.

Sie können die Indexsortierung verwenden:

• Sortieren nach test1
• Sortieren nach Test1 absteigend
• Sortieren nach Test1,Test2
• Sortieren nach Test1 absteigend, Test2 absteigend
  

Die Sortierung nach Index kann nicht verwendet werden:

• Sortieren nach Test1, Test2, Desc
• Sortieren nach Test1 absteigend, Test2
  

Da der Index keine desc-Funktion unterstützt, wird der mehrspaltige Index in aufsteigender Reihenfolge aller Spalten gespeichert. Wenn Sie also nur eine Spalte, alle Spalten in aufsteigender Reihenfolge oder alle Spalten in absteigender Reihenfolge sortieren, können Sie den Index verwenden. Wenn Sie jedoch für die erste Spalte die aufsteigende Reihenfolge und für die zweite Spalte die absteigende Reihenfolge oder für die erste Spalte die absteigende Reihenfolge und für die zweite Spalte die aufsteigende Reihenfolge verwenden, können Sie den Index nicht verwenden.

Oben finden Sie eine detaillierte Zusammenfassung häufiger Probleme mit MySQL-Indizes. Weitere Informationen zu MySQL-Indizes finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!