MySQL hilft Ihnen, Index-Pushdown in Sekunden zu verstehen

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

1. Das Prinzip der Index-Push-Down-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索引下推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.
  

2. 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 des Index 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?

1. ICP nicht verwenden

Vor MySQL 5.6 findet die Speicher-Engine die Primärschlüssel-ID (1, 4) von name like „张%“ über den gemeinsamen Index, durchsucht die Tabelle nacheinander, entfernt den gruppierten Index, um den vollständigen Zeilendatensatz zu finden, und server filtert die Daten entsprechend age=10 .

Schauen wir uns das schematische Diagramm an:

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

2. Verwenden Sie ICP

Nach MySQL 5.6 findet die Speicher-Engine name like '張% “ basierend auf dem kombinierten Index ( name , age ). 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 |
+----+----------+-------+------------+-------+---------------+---------------+-----------+-----------+----------+----------+------+----------+----------+----------+-----------+---------------------------+

3. Nutzungsbedingungen für 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數據和索引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.
  

Relevante 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";

Dies ist das Ende dieses Artikels zum Verständnis von MySQL Index Pushdown in wenigen Minuten. Weitere relevante MySQL Index Pushdown-Inhalte finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!