MySQL-Index-Pushdown in fünf Minuten verstehen

Wenn Sie im Rahmen eines Vorstellungsgesprächs Begriffe wie „MySQL 5.6“ und „Indexoptimierung“ hören, dürfte Ihnen sofort klar sein, dass es bei dieser Frage um „Index-Pushdown“ geht.

Was ist Index-Pushdown?

Index Condition Pushdown (ICP) ist eine neue Funktion von MySQL 5.6. Sie kann die Anzahl der Tabellenabfragen reduzieren und die Abfrageeffizienz verbessern.

Das Prinzip der Index-Pushdown-Optimierung

Werfen wir zunächst einen kurzen Blick auf die allgemeine Architektur von MySQL:

Die MySQL-Serviceschicht ist für die SQL-Syntaxanalyse, die Generierung von Ausführungsplänen usw. und den Aufruf der Speicher-Engine-Schicht zum Speichern und Abrufen von Daten verantwortlich.

Das Pushdown des Index-Pushs bedeutet tatsächlich, dass einige der Aufgaben, die in der Verantwortung der oberen Schicht (Serviceschicht) liegen, zur Verarbeitung an die untere Schicht (Engine-Schicht) übergeben werden.

Schauen wir uns die MySQL-Abfrage ohne Verwendung von ICP genauer an:

• Die Speicher-Engine liest den Indexdatensatz;
• Suchen und lesen Sie den vollständigen Zeilendatensatz basierend auf dem Primärschlüsselwert im Index.
• Die Speicher-Engine übergibt den Datensatz an die Serverebene, um zu prüfen, ob der Datensatz die WHERE-Bedingung erfüllt.

Bei Verwendung von ICP läuft die Abfrage wie folgt ab:

• Die Speicher-Engine liest den Indexdatensatz (nicht den vollständigen Zeilendatensatz).
• Bestimmen Sie, ob die WHERE-Bedingung anhand der Spalten im Index überprüft werden kann. Wenn die Bedingung nicht erfüllt ist, verarbeiten Sie die nächste Zeile der Indexdatensätze.
• Wenn die Bedingungen erfüllt sind, verwenden Sie den Primärschlüssel im Index, um den vollständigen Zeilendatensatz zu finden und zu lesen (dies wird als Tabellenrückgabe bezeichnet).
• Die Speicher-Engine übergibt den Datensatz an die Serverebene, die prüft, ob der Datensatz die restlichen WHERE-Bedingungen erfüllt.

Spezifische Praxis des Index-Pushdowns

Die Theorie ist ziemlich abstrakt, also lasst uns sie in die Praxis umsetzen.

Verwenden Sie eine Benutzertabelle „tuser“ und erstellen Sie einen gemeinsamen Index (Name, Alter) in der Tabelle.

Wenn jetzt eine Anforderung besteht: Rufen Sie alle Benutzer in der Tabelle ab, deren Vorname Zhang ist und die 10 Jahre alt sind. Die SQL-Anweisung wird dann folgendermaßen geschrieben:

Wählen Sie * aus dem Benutzer, wobei der Name beispielsweise „张%“ und das Alter 10 ist.

Wenn Sie das Prinzip der ganz links stehenden Übereinstimmung von Indizes verstehen, wissen Sie, dass diese Anweisung nur beim Durchsuchen des Indexbaums verwendet werden kann und die ID des ersten Datensatzes, der die Bedingungen erfüllt, 1 ist.

Was sind also die nächsten Schritte?

Kein ICP verwendet

Vor MySQL 5.6 findet die Speicher-Engine die Primärschlüssel-ID (1, 4) des name likelike '張%' über den gemeinsamen Index, durchsucht die Tabelle nacheinander, entfernt den gruppierten Index, um den vollständigen Zeilendatensatz zu finden, und die Serverebene age=10進行篩選.

Schauen wir uns das schematische Diagramm an:

Es ist ersichtlich, dass die Tabelle zweimal zurückgegeben werden muss, wodurch das andere Feldalter unseres gemeinsamen Indexes verschwendet wird.

Verwenden von ICP

Ab MySQL 5.6 findet die Speicher-Engine name likelike '張% ' basierend auf dem kombinierten Index (Name, Alter). Da der kombinierte Index die Spalte „age“ enthält, filtert die Speicher-Engine den kombinierten Index direkt nach age=10 . Durchsuchen Sie die Tabelle nacheinander nach den gefilterten Daten.

Schauen wir uns das schematische Diagramm an:

Sie können sehen, dass die Tabelle nur einmal zurückgegeben wurde.

Darüber hinaus können wir uns auch den Ausführungsplan ansehen und in Extra Using index condition sehen, was bedeutet, dass Index-Pushdown verwendet wird.

+----+----------+-------+------------+-------+---------------+---------------+-----------+-----------+----------+----------+------+----------+----------+----------+-----------+---------------------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+-------+------------+-------+---------------+---------------+-----------+-----------+----------+----------+------+----------+----------+----------+-----------+---------------------------+
| 1 | SIMPLE | tuser | NULL | Bereich | na_index | na_index | 102 | NULL | 2 | 25.00 | Indexbedingung wird verwendet |
+----+----------+-------+------------+-------+---------------+---------------+-----------+-----------+----------+----------+------+----------+----------+----------+-----------+---------------------------+

Bedingungen für die Verwendung von Index-Pushdowns

• Kann nur mit den Zugriffsmethoden range , ref , eq_ref und ref_or_null verwendet werden;
• Kann nur für InnoDB und MyISAM -Speicher-Engines und ihre partitionierten Tabellen verwendet werden;
• Bei der Speicher-Engine InnoDB gilt der Index-Pushdown nur für sekundäre Indizes (auch Hilfsindizes genannt).

Der Zweck des Index-Pushdowns besteht darin, die Anzahl der Tabellenrückgaben zu verringern, d. h. die Anzahl der E/A-Vorgänge zu reduzieren. Beim gruppierten Index von InnoDB sind die Daten und der Index zusammen und es gibt keine Tabellenrückgabe.

• Bedingungen, dass Referenzunterabfragen nicht nach unten verschoben werden können;
• Bedingungen, die auf gespeicherte Funktionen verweisen, können nicht weitergegeben werden, da die Speicher-Engine gespeicherte Funktionen nicht aufrufen kann.

Zugehörige Systemparameter

Das Pushdown der Indexbedingung ist standardmäßig aktiviert und Sie können den Systemparameter optimizer_switch verwenden, um zu steuern, ob es aktiviert ist.

Zeigen Sie den Standardstatus an:

mysql> wähle @@optimizer_switch\G;
*************************** 1. Reihe ***************************
@@optimizer_switch: index_merge=ein,index_merge_union=ein,index_merge_sort_union=ein,index_merge_intersection=ein,engine_condition_pushdown=ein,index_condition_pushdown=ein,mrr=ein,mrr_cost_based=ein,block_nested_loop=ein,batched_key_access=aus,materialization=ein,semijoin=ein,loosescan=ein,firstmatch=ein,duplicateweedout=ein,subquery_materialization_cost_based=ein,use_index_extensions=ein,condition_fanout_filter=ein,derived_merge=ein
1 Zeile im Satz (0,00 Sek.)

Zustand umschalten:

setze optimizer_switch="index_condition_pushdown=off";
setze optimizer_switch="index_condition_pushdown=on";

Zusammenfassen

Dieser Artikel endet hier. Ich hoffe, er kann Ihnen helfen. Ich hoffe auch, dass Sie mehr Inhalten auf 123WORDPRESS.COM mehr Aufmerksamkeit schenken können!