Verschiedene Arten von MySQL-Indizes

Was ist ein Index?

Ein Index ist eine Datenstruktur, die von einer Datenbankspeicher-Engine zum schnellen Auffinden bestimmter Daten verwendet wird.

Wir können das Xinhua-Wörterbuch als Analogie verwenden: Wenn die ausführliche Erklärung jedes Zeichens im Xinhua-Wörterbuch der Datensatz der Tabelle in der Datenbank ist, dann ist das nach Radikalen oder Pinyin sortierte Verzeichnis der Index, der es uns ermöglicht, schnell den Speicherort der ausführlichen Erklärung eines bestimmten Zeichens zu finden.

In MySQL verwendet die Speicher-Engine eine ähnliche Methode. Zuerst wird der entsprechende Wert im Index gesucht und dann anhand des übereinstimmenden Indexwerts der Speicherort des Datensatzes in der entsprechenden Tabelle ermittelt.

Warum werden in Interviews Indizes abgefragt?

Der Grund, warum in Interviews häufig nach Indizes gefragt wird, besteht darin, dass Indizes der Schlüssel zu einer guten Datenbankleistung und das effektivste Mittel zur Abfrageoptimierung sind. Indizes können die Abfrageleistung problemlos um mehrere Größenordnungen verbessern.

Schlechte Indizes wirken sich jedoch auch auf die Abfrageleistung aus. Mit zunehmender Datenmenge in der Tabelle nimmt auch der Einfluss des Index auf die Leistung zu. Bei kleinen Datenmengen und geringer Komplexität sind die Auswirkungen fehlerhafter Indizes auf die Leistung möglicherweise nicht offensichtlich. Wenn die Datenmenge jedoch allmählich zunimmt, sinkt die Leistung stark.

Arten von Indizes

Kommen wir nach der vorherigen Einführung zum Punkt und lernen die von MySQL unterstützten Indextypen sowie deren Prinzipien und Verwendung kennen.

Verschiedene Indextypen können in verschiedenen Szenarien eine bessere Leistung bieten. In MySQL werden Indizes auf der Ebene der Speicher-Engine und nicht auf der Serverebene implementiert. Wie wir alle wissen, unterstützt MySQL mehrere Arten von Speicher-Engines. Daher ist die Implementierung von Indizes in verschiedenen Speicher-Engines nicht gleich, und nicht alle Indextypen werden von allen Speicher-Engines unterstützt. Selbst wenn mehrere Speicher-Engines denselben Indextyp unterstützen, kann die zugrunde liegende Implementierung unterschiedlich sein.

B-Baum-Index

B-Tree-Indizes werden von den meisten MySQL-Speicher-Engines unterstützt. Wenn wir von Indizes sprechen und der Typ nicht ausdrücklich angegeben ist, beziehen wir uns wahrscheinlich auf B-Tree-Indizes. Wir verwenden den Begriff B-Baum, weil MySQL dieses Schlüsselwort beim Erstellen einer Tabelle und anderen Anweisungen verwendet.

Verschiedene Speicher-Engines können jedoch in der untersten Schicht unterschiedliche Datenstrukturen und Algorithmen verwenden. Beispielsweise verwendet die InnoDB-Speicher-Engine intern die B+Tree-Struktur, während die NDB-Cluster-Speicher-Engine intern die T-Tree-Struktur verwendet. Verschiedene Speicher-Engines verwenden B-Tree-Indizes auf unterschiedliche Weise, und auch die Leistung kann unterschiedlich sein. Beispielsweise speichert der InnoDB-Index das ursprüngliche Datenformat, während die MyISAM-Speicher-Engine Präfixkomprimierungstechnologie verwendet, um den Index zu verkleinern. Die Zeilen des InnoDB-Index speichern die Primärschlüsselreferenzen der Datenzeilen, während die Zeilen des MyISAM-Speicher-Engine-Index die physischen Speicherorte der Datenzeilen speichern.

Das Prinzip des B-Tree-Index

B-Tree-Indizes können den Zugriff auf Daten beschleunigen, da die erforderlichen Daten ohne vollständigen Tabellenscan schnell abgerufen werden können. Wie also macht das der B-Tree-Index? Schauen wir uns anhand eines einfachen Beispiels an, wie der B-Tree-Index von InnoDB funktioniert:

TABELLE `om_address` erstellen (
 `province_name` varchar(255) NICHT NULL KOMMENTAR 'Provinzname',
 `city_name` varchar(255) NICHT NULL KOMMENTAR 'Stadt',
 `district_name` varchar(255) NICHT NULL KOMMENTAR 'Bezirk',
 `detailed_address` varchar(255) NULL DEFAULT NULL COMMENT 'Detaillierte Adresse',
 INDEX `index_province_city_district`(`Provinzname`, `Stadtname`, `Bezirksname`) MIT BTREE
)ENGINE = InnoDB;

Diese Tabelle enthält vier Felder, die Provinz, Stadt, Bezirk und detaillierte Adresse darstellen, sowie einen B-Baum-Index, der die drei Felder Provinz, Stadt und Bezirk enthält. Denn alle Werte des Index werden der Reihe nach gespeichert, das heißt, der linke Teilbaum des Knotens ist kleiner als der aktuelle Knoten und der rechte Teilbaum des Knotens ist größer als der aktuelle Knoten. Beginnen Sie dann beim Abfragen von Daten mit der Suche beim Stammknoten des Index und durchsuchen Sie den Teilbaum entsprechend dem Indexwert des aktuellen Knotens, bis der entsprechende Indexwert gefunden wird oder er überhaupt nicht gefunden wird.

Verwendung des B-Tree-Index

Entsprechend den Eigenschaften des B-Tree-Index kann dieser für die vollständige Werteübereinstimmung, die Wertebereichsübereinstimmung und die ganz linke Präfixübereinstimmung verwendet werden.

• Unter vollständiger Wertübereinstimmung versteht man die Übereinstimmung aller Felder im Index, z. B. die Abfrage von Daten für den Bezirk Nangang, Stadt Harbin, Provinz Heilongjiang.
• Bei der Wertebereichsübereinstimmung wird ein bestimmter Bereich von Feldern im Index abgeglichen, wobei die vorherigen Felder jedoch vollständig übereinstimmen müssen. Beispielsweise stimmt das erste Feld, province_name, vollständig mit dem Provinznamen überein, und das zweite Feld, city_name, stimmt mit dem Bereich der Städtenamen überein.
• Die ganz links stehende Präfixübereinstimmung bezieht sich auf die Übereinstimmung mit einem bestimmten Anfangsteil des Felds im Index, muss aber die vollständige Übereinstimmung mit dem vorherigen Feld erfüllen. Beispiel: Das erste Feld „province_name“ ist der Name der Provinz Innere Mongolei und das zweite Feld „city_name“ ist der Name der Stadt, die mit „呼“ beginnt.
  

Hash-Index

Hash-Indizes werden basierend auf Hash-Tabellen implementiert und dienen dazu, die vom Index gezeigten Daten genau abzugleichen. Die Speicher-Engine berechnet einen Hash-Code für alle Indexfelder jeder Datenzeile. Der Hash-Code ist ein relativ kleiner Wert, und die für unterschiedliche Daten berechneten Hash-Codes sind im Allgemeinen unterschiedlich. Der Hash-Index speichert den Hash-Code und einen Zeiger auf die Datenzeile.

In MySQL unterstützt nur die Memory-Speicher-Engine Hash-Indizes. Dies ist auch der Standardindextyp für die Memory-Speicher-Engine. Darüber hinaus werden in der InnoDB-Speicher-Engine auch Hash-Indizes verwendet, sogenannte adaptive Hash-Indizes. Wenn bestimmte Indizes sehr häufig verwendet werden, erstellt die InnoDB-Speicher-Engine einen Hash-Index basierend auf dem B-Tree-Index im Speicher, sodass der B-Tree-Index auch den Vorteil einer schnellen Hash-Suche bietet.

Da der Hash-Index nur den Hash-Wert der entsprechenden Daten speichern muss, ist die Indexstruktur sehr kompakt, nimmt wenig Platz ein und die Abfragegeschwindigkeit ist auch sehr schnell. Hash-Indizes unterstützen jedoch nur Abfragen mit vollständiger Wertgleichheit und können keinen Indexfeldbereichsabgleich oder teilweisen Indexfeldabgleich unterstützen.

Geodatenindex

Der räumliche Datenindex (R-Tree) wird hauptsächlich zum Speichern geografischer Daten verwendet. Er indiziert Daten aus allen Dimensionen und kann während der Abfrage jede Dimension effektiv für kombinierte Abfragen verwenden. Derzeit unterstützt die MyISAM-Speicher-Engine die Indizierung räumlicher Daten, zur Datenpflege müssen jedoch die GIS-bezogenen Funktionen von MySQL verwendet werden.

In MySQL können räumliche Indizes nur für räumliche Datentypen wie GEOMETRY, POINT, LINESTRING usw. erstellt werden.

Volltextindex

Bei der Volltextindizierung werden die Werte im Index nicht direkt verglichen wie bei den zuvor eingeführten Indizes, sondern direkt die Schlüsselwörter im gesuchten Text. Dies ähnelt dem, was eine Suchmaschine tut, und ist kein einfaches Abgleichen der Where-Bedingung.

Sie können im selben Feld gleichzeitig einen Volltextindex und einen B-Tree-Index erstellen, ohne dass es zu Konflikten kommt. Volltextindizes sind auf Match- und Against-Operationen anwendbar, nicht auf gewöhnliche Where-Bedingungsoperationen. In MySQL können Volltextindizes nur für Felder vom Typ CHAR, VARCHAR oder TEXT erstellt werden.

Zusammenfassen

Ein Index ist eine Datenstruktur, die von der Datenbank-Speicher-Engine verwendet wird, um bestimmte Daten schnell zu finden. Dazu gehören B-Tree-Index, Hash-Index, räumlicher Datenindex und Volltextindex. Der B-Tree-Index wird am häufigsten verwendet. Die InnoDB-Speicher-Engine verwendet intern die B+Tree-Struktur. Der Hash-Index basiert auf einer Hash-Tabelle und wird verwendet, um die vom Index angezeigten Daten genau abzugleichen. Der räumliche Datenindex indiziert Daten aus allen Dimensionen, und jede Dimension kann während der Abfrage effektiv für kombinierte Abfragen verwendet werden. Der Volltextindex vergleicht Schlüsselwörter im gesuchten Text direkt, ähnlich wie eine Suchmaschine.

Oben finden Sie eine ausführliche Einführung in die verschiedenen Arten von MySQL-Indizes. Weitere Informationen zu MySQL-Indextypen finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!