Detaillierte Erklärung des MySQL-Covering-Index

Konzept

Wenn der Index alle Daten enthält, die die Abfrageanforderungen erfüllen, wird er als überdeckender Index bezeichnet, was bedeutet, dass keine Rückkehr zur Tabelle erforderlich ist.

Beurteilungskriterien

Wenn Sie „explain“ verwenden, können Sie dies anhand der zusätzlichen Spalte der Ausgabe beurteilen. Bei einer Index-Abfrage wird sie als „using index“ angezeigt. Der MySQL-Abfrageoptimierer ermittelt, ob eine Index-Abfrage vorhanden ist, bevor er die Abfrage ausführt.

Beachten

1. Überdeckende Indizes sind nicht auf jeden Indextyp anwendbar. Der Index muss den Wert der Spalte speichern.

2. Hash- und Volltextindizes speichern keine Werte, daher kann MySQL nur B-TREE verwenden

3. Verschiedene Speicher-Engines implementieren die Abdeckungsindizes unterschiedlich

4. Nicht alle Speicher-Engines unterstützen sie

5. Wenn Sie einen überdeckenden Index verwenden möchten, müssen Sie auf den Wert der SELECT-Liste achten, um die erforderlichen Spalten zu extrahieren. Sie können SELECT * nicht verwenden, da die Indexdatei zu groß wird und die Abfrageleistung abnimmt, wenn Sie alle Felder zusammen indizieren. Sie können dies nicht tun, nur um einen überdeckenden Index zu verwenden.

Wenn ein Index die Werte aller abzufragenden Felder enthält (oder abdeckt), wird er als „abdeckender Index“ bezeichnet. Das heißt, Sie müssen nur den Index scannen, ohne zur Tabelle zurückzukehren.

Vorteile des ausschließlichen Index-Scannens ohne Rückkehr zur Tabelle:

1. Indexeinträge sind normalerweise viel kleiner als die Datenzeilengröße und es muss nur der Index gelesen werden, sodass MySQL den Datenzugriff erheblich reduziert.

2. Da der Index in der Reihenfolge der Spaltenwerte gespeichert wird, erfordern E/A-intensive Bereichssuchen viel weniger E/A als das zufällige Lesen jeder Datenzeile von der Festplatte.

3. Einige Speicher-Engines wie MyISAM speichern nur Indizes im Speicher und verlassen sich beim Zwischenspeichern der Daten auf das Betriebssystem. Für den Zugriff auf die Daten ist daher ein Systemaufruf erforderlich.

4. Der gruppierte Index und der überdeckende Index von Innodb sind besonders nützlich für Innodb-Tabellen. (Der Sekundärindex von InnoDB speichert den Primärschlüsselwert der Zeile im Blattknoten. Wenn also der sekundäre Primärschlüssel die Abfrage abdecken kann, kann die sekundäre Abfrage des Primärschlüsselindex vermieden werden.)

Ein überdeckender Index muss die Werte der Indexspalten speichern, während Hash-Indizes, räumliche Indizes und Volltextindizes die Werte der Indexspalten nicht speichern. Daher kann MySQL nur B-Baum-Indizes als überdeckende Indizes verwenden.

Wenn eine Indexabdeckungsabfrage initiiert wird, werden die verwendeten Indexinformationen in der zusätzlichen Spalte „Explain“ angezeigt.

Fallstricke bei der Abdeckung von Indizes: Der MySQL-Abfrageoptimierer ermittelt vor der Ausführung, ob ein Index vorhanden ist, der die Abfrage abdecken kann. Vorausgesetzt, dass der Index die Felder in der Where-Bedingung abdeckt, aber nicht die Felder, die in der gesamten Abfrage enthalten sind, kehren MySQL 5.5 und frühere Versionen ebenfalls zur Tabelle zurück, um die Datenzeile abzurufen, obwohl diese Zeile nicht benötigt wird und schließlich herausgefiltert wird.

Wie in der Abbildung oben gezeigt, kann die Abdeckungsabfrage aus folgenden Gründen nicht verwendet werden:

1. Kein Index kann diesen Index abdecken. Weil die Abfrage alle Spalten aus der Tabelle auswählt und kein Index vorhanden ist, der alle Spalten abdeckt.

2. MySQL kann keinen LIK-Vorgang auf einem Index durchführen. MySQL kann einen LIKE-Vergleich mit einer Übereinstimmung des ganz linken Präfixes im Index durchführen. Wenn die LIKE-Abfrage jedoch mit einem Platzhalter beginnt, kann die Speicher-Engine keinen Vergleichsabgleich durchführen. In diesem Fall kann MySQL nur den Wert der Datenzeile und nicht den Indexwert zum Vergleich extrahieren

Optimiertes SQL: Indizes hinzufügen (Künstler, Titel, Produkt-ID), verzögerte Zuordnung verwenden (verzögerter Zugriff auf Spalten)

Hinweis: In der ersten Phase der Abfrage können Sie einen überdeckenden Index verwenden, um die passende Produkt-ID in der Unterabfrage in der From-Klausel zu finden, und dann die äußere Ebene abfragen, um eine Übereinstimmung zu finden und alle erforderlichen Werte basierend auf dem Produkt-ID-Wert abzurufen.

In 5.5 erlaubt das API-Design MySQL nicht, Filterbedingungen an die Speicher-Engine-Schicht weiterzugeben (es zieht Daten von der Speicher-Engine zur Serverschicht und filtert dann entsprechend den Bedingungen). Nach 5.6 verbessert die ICP-Funktion die Abfrageausführungsmethode.

Wenn MySQL den Index nicht zum Sortieren verwenden kann, verwendet es seinen eigenen Sortieralgorithmus (Quicksort-Algorithmus), um die Daten im Speicher (Sortierpuffer) zu sortieren. Wenn der Speicher nicht ausreicht, teilt es die Daten auf der Festplatte in Blöcke auf, sortiert jeden Datenblock und fügt dann jeden Block zu einem geordneten Ergebnissatz zusammen (tatsächlich externe Sortierung).

Für die Dateisortierung verfügt MySQL über zwei Sortieralgorithmen

1. Zwei Durchgänge

Die Implementierungsmethode besteht darin, zuerst die zu sortierenden Felder und die Zeigerinformationen abzurufen, die die relevanten Zeilendaten direkt lokalisieren können, und sie dann im festgelegten Speicher (festgelegt durch den Parameter sort_buffer_size) zu sortieren. Nachdem die Sortierung abgeschlossen ist, werden die erforderlichen Spalten erneut über die Zeilenzeigerinformationen abgerufen.

Hinweis: Dieser Algorithmus wird vor 4.1 verwendet. Er muss zweimal auf die Daten zugreifen. Insbesondere der zweite Lesevorgang führt zu einer großen Anzahl zufälliger E/A-Vorgänge. Andererseits ist der Speicheraufwand gering

2. Single-Pass-Algorithmus

Dieser Algorithmus nimmt alle benötigten Spalten auf einmal heraus, sortiert sie im Speicher und gibt die Ergebnisse direkt aus. Hinweis: Dieser Algorithmus wird seit MySQL 4.1 verwendet. Dadurch wird die Anzahl der E/A-Vorgänge verringert und die Effizienz gesteigert, es entsteht jedoch auch ein großer Speicheraufwand. Wenn wir die Spalten entfernen, die nicht benötigt werden, wird der für den Sortiervorgang erforderliche Speicher stark verschwendet. In MySQL 4.1 und späteren Versionen können Sie durch Festlegen des Parameters max_length_for_sort_data steuern, ob MySQL den ersten oder den zweiten Sortieralgorithmus wählt. Wenn die Gesamtgröße aller abgerufenen großen Felder größer ist als die Einstellung „max_length_for_sort_data“, verwendet MySQL den ersten Sortieralgorithmus, andernfalls den zweiten. Um die Sortierleistung so weit wie möglich zu verbessern, bevorzugen wir natürlich die Verwendung des zweiten Sortieralgorithmus. Daher ist es sehr wichtig, in der Abfrage nur die erforderlichen Spalten abzurufen.

Wenn sich ORDER BY beim Sortieren einer Join-Operation nur auf die Spalten der ersten Tabelle bezieht, führt MySQL eine Filesort-Operation für die Tabelle aus und führt dann den Join aus. In diesem Fall gibt EXPLAIN „Using filesort“ aus. Andernfalls muss MySQL eine temporäre Tabelle für den Abfrageergebnissatz generieren und eine Filesort-Operation ausführen, nachdem der Join abgeschlossen ist. In diesem Fall gibt EXPLAIN „Using temporary; Using filesort“ aus.

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. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM. Wenn Sie mehr darüber erfahren möchten, schauen Sie sich bitte die folgenden Links an