Vorteile von MySQL-Abdeckungsindizes

Ein allgemeiner Vorschlag besteht darin, Indizes für WHERE-Bedingungen zu erstellen, was jedoch eigentlich einseitig ist. Indizes sollten für alle Abfragen entworfen werden, nicht nur für die WHERE-Bedingungen. Indizes helfen zwar dabei, Datenzeilen effizient zu finden, MySQL kann jedoch auch Indizes verwenden, um Spaltendaten abzurufen, ohne überhaupt eine Datenzeile lesen zu müssen. Schließlich enthalten die Blattknoten eines Index den Wert, der dem Index entspricht. Warum die Zeilen lesen, wenn Sie einfach den Index lesen können, um die gewünschten Daten zu erhalten? Wenn ein Index alle Daten für eine Abfrage enthält, wird er als überdeckender Index bezeichnet.

Das Abdecken von Indizes kann ein sehr mächtiges Werkzeug sein und die Leistung deutlich verbessern. Stellen Sie sich den Fall vor, dass Sie die Daten nicht lesen müssen, sondern nur den Index:

• Indexwerte sind normalerweise viel kleiner als der gesamte Zeilenspeicherplatz, sodass MySQL beim Lesen nur des Indexwerts nur eine kleine Datenmenge lesen kann. Dies ist insbesondere bei Cache-Workloads wichtig, da ein großer Teil der Reaktionszeit für das Kopieren der Daten aufgewendet wird. Dasselbe gilt für Festplatten-E/A, da Indexdaten wesentlich weniger Speicherplatz beanspruchen als Zeilendaten und dadurch mehr E/A-Last und Speichernutzung eingespart werden (dies ist für die MyISAM-Engine von größerer Bedeutung, da MyISAM Indizes packen kann, um den Speicherplatz zu verringern).
• Indizes werden in der Reihenfolge der Indexwerte gespeichert, sodass E/A-Zugriffsbereiche eine geringere E/A-Frequenz verbrauchen als das Abrufen von Zeilendaten von zufälligen Speicherorten auf der Festplatte. Bei einigen Speicher-Engines, wie etwa MyISAM und Percona XtraDB, können Sie mit OPTIMIZE sogar die Tabelle optimieren, um vollständig geordnete Indizes zu erhalten, wodurch einfache Bereichsabfragen einen rein sequentiellen Zugriff verwenden.
• Einige Speicher-Engines, beispielsweise MyISAM, speichern Indizes nur im MySQL-Speicher. Da das Betriebssystem Daten für MyISAM zwischenspeichert, ist für den Zugriff normalerweise ein Systemaufruf erforderlich. Dies kann zu erheblichen Leistungseinbußen führen, insbesondere bei Szenarios mit hohem Cache, bei denen Systemaufrufe die teuerste Möglichkeit für den Datenzugriff darstellen.
• Aufgrund des gruppierten Index von InnoDB ist die Indexabdeckung für InnoDB sehr hilfreich. Der sekundäre Index von InnoDB speichert den Primärschlüsselwert der Zeile in ihren Blattknoten. Daher deckt der Sekundärindex die Abfrage ab und vermeidet die Primärschlüsselabfrage.
  

In allen Fällen ist der Aufwand für eine Abfrage, die nur indizierte Spalten umfasst, wesentlich geringer als für die Suche in den Datenzeilen. Es ist wichtig zu beachten, dass ein gruppierter Index nicht einfach nur ein beliebiger Indextyp ist. Ein gruppierter Index muss die Werte speichern, die den indizierten Datenspalten entsprechen. Hash-, räumliche und Volltextindizes speichern diese Werte nicht, daher kann MySQL zur Abdeckung der Abfrage nur Binärbäume verwenden. Darüber hinaus implementieren verschiedene Speicher-Engines abdeckende Indizes auf unterschiedliche Weise, und nicht alle Speicher-Engines unterstützen abdeckende Indizes (die Memory-Speicher-Engine unterstützt dies beispielsweise derzeit nicht).

Wenn Sie überprüfen, ob der Index in der Abfrage einen überdeckenden Index verwendet, wird bei Verwendung der Explain-Anweisung in der Spalte „Extra“ „Index wird verwendet“ angezeigt. Beispielsweise gibt es einen mehrspaltigen Index für die Tabelle „store_goods“: (shop_id, goods_category_id1). MySQL kann den Index verwenden, wenn die Abfrage nur Daten für diese beiden Spalten zurückgibt:

EXPLAIN SELECT `goods_category_id1`,`shop_id` FROM `store_goods` WHERE 1

Das Abdecken von Indexabfragen kann solche Optimierungen in manchen Fällen ungültig machen. Der MySQL-Abfrageoptimierer ermittelt beim Ausführen der Abfrage, ob der Index die Abfrage abdeckt. Angenommen, der Index deckt die WHERE-Bedingung ab, aber nicht die gesamte Abfrage. Wenn die Auswertung dazu führt, dass kein überdeckender Index verwendet werden soll, werden die Datenzeilen in MySQL 5.5 und früheren Versionen direkt abgerufen, auch wenn die Daten nicht benötigt werden, und anschließend herausgefiltert.

Sehen wir uns an, warum dies passiert und wie die Abfrage umgeschrieben werden kann, um das Problem zu beheben. Zunächst lautet die Abfrage:

EXPLAIN SELECT * FROM Produkte WHERE Schauspieler='SEAN CARREY' AND Titel wie '%APOLLO%'

Das Ergebnis ist, dass zu diesem Zeitpunkt nicht der überdeckende Index, sondern der normale Index verwendet wird, weil:

• Kein Index deckt die Abfragespalten ab, da wir alle Spalten aus der Tabelle lesen und kein Index alle Spalten abdeckt. Theoretisch verfügt MySQL über eine weitere Abkürzung, die verwendet werden kann. Das heißt, die vom Index abgedeckte Spalte wird in der WHERE-Bedingung verwendet. MySQL kann also zuerst diesen Index verwenden, um den entsprechenden Akteur zu finden, dann prüfen, ob sein Titel übereinstimmt, und dann alle Datenzeilen lesen, die die Bedingungen erfüllen.
• Bei früheren Versionen der Speicher-Engine-API (Versionen vor MySQL 5.5) kann MySQL keine LIKE-Operationen in Indizes verwenden und unterstützt nur einfache Vergleichsoperationen (=, IN, >=). MySQL kann präfixübereinstimmende LIKE-Abfragen in Indizes verwenden, da es diese in Vergleichsoperationen umwandeln kann. Das führende Platzhalterzeichen (also das führende % in LIKE) führt jedoch dazu, dass die Speicher-Engine die Übereinstimmungsbedingung nicht auswerten kann. Daher ruft MySQL zum Vergleich die Zeilendaten statt des Indexwerts ab.
  

Dies lässt sich durch die geschickte Kombination von Indizes und das Umschreiben der Abfrage erreichen. Wir können den Index auf (Künstler, Titel, Produkt-ID) erweitern und die Abfrage wie folgt umschreiben:

ERKLÄREN AUSWÄHLEN * 
FROM Produkte
	VERBINDEN (
    Wählen Sie Produkt-ID aus
    FROM Produkte
    WO Schauspieler='SEAN CARREY' UND TITEL WIE '%APOLLO%'
 ) ALS t1 EIN (t1.prod_id=products.prod_id)

Wir nennen dies einen „verzögerten Join“, da dadurch der Zugriff auf die Spalten verzögert wird. In der ersten Phase der Abfrage verwendet MySQL den überdeckenden Index, wenn es übereinstimmende Zeilen in der Unterabfrage findet. Obwohl es nicht in der gesamten Abfrage abgedeckt ist, ist es besser als nichts.

Die Wirksamkeit dieser Optimierung hängt davon ab, wie viele Datenzeilen in der WHERE-Bedingung gefunden werden. Nehmen Sie an, dass die Produkttabelle Millionen von Datenzeilen enthält. Sie können die Leistung dieser beiden Abfragen mit insgesamt 1 Million Datenzeilen vergleichen.

• Fall 1: Es gibt 30.000 Produkte des Schauspielers „SEAN CARREY“, davon tragen 20.000 den Titel „APOLLO“;
• Fall 2: Es gibt 30.000 Produkte des Schauspielers „SEAN CARREY“, davon 40 mit dem Titel „APOLLO“;
• Fall 3: Es gibt 50 Produkte des Schauspielers „SEAN CARREY“, davon tragen 10 den Titel „APOLLO“.
  

Die Vergleichsergebnisse sind in der folgenden Tabelle dargestellt.

Die Ergebnisse werden wie folgt erläutert:

• Im ersten Fall gibt die Abfrage einen sehr großen Ergebnisset zurück, sodass der Optimierungseffekt nicht sichtbar ist. Die meiste Zeit wird mit dem Lesen und Senden der Daten verbracht.
• Im zweiten Fall ergibt die Unterabfragefilterung mit dem überdeckenden Index einen kleinen Ergebnissatz, was zu einer fünffachen Leistungsverbesserung führt. Der Grund für diesen Effekt liegt darin, dass nur 40 Zeilen gelesen werden müssen, verglichen mit einem Datensatz mit 30.000 Zeilen.
• Der dritte Fall zeigt, dass die Unterabfrage fehlschlägt. Der von der abdeckenden Indexfilterung zurückgegebene Ergebnissatz ist zu klein, wodurch der Aufwand für die Unterabfrage höher ist, als wenn die Daten direkt aus der Datentabelle gelesen würden.

In den meisten Speicher-Engines kann ein Index nur den Zugriff auf Spalten abdecken, die Teil des Indexes sind. Tatsächlich nimmt InnoDB jedoch noch eine weitere Optimierung vor. Denken Sie an die sekundären Indizes von InnoDB, die die Primärschlüsselwerte in den Blattknoten speichern. Dies bedeutet, dass die sekundären Indizes von InnoDB tatsächlich über zusätzliche Spalten verfügen, die InnoDB bei der Verwendung von abdeckenden Indizes helfen. Beispielsweise verwendet die Tabelle sakila.actor InnoDB und verfügt über einen Index für last_name, sodass dieser Index Abfragen basierend auf dem Primärschlüssel actor_id abdecken kann – auch wenn diese Spalte nicht Teil des Indexes ist.

EXPLAIN SELECT Schauspieler-ID, Nachname
VON sakila.actor WO Nachname = 'HOPPER'

Oben finden Sie ausführliche Informationen zu den Vorteilen des MySQL-Überdeckungsindex. Weitere Informationen zum MySQL-Überdeckungsindex finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!