Ein Artikel zum Verständnis von MySQL Index Pushdown (ICP)

1. Einleitung

ICP (Index Condition Pushdown) ist eine in MySQL 5.6 eingeführte Abfrageoptimierungsstrategie. Sie verschiebt die ursprünglich von der Serverebene durchgeführte Indexbedingungsprüfung auf die Speichermodulebene, um die Anzahl der Tabellenrückgaben und Speichermodulzugriffe zu verringern und so die Abfrageeffizienz zu verbessern.

2. Grundsatz

Um zu verstehen, wie ICP funktioniert, müssen wir zunächst verstehen, wie MySQL-Abfragen ohne ICP ausgeführt werden:

• 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 der Verwendung von ICP läuft die Abfrage wie folgt ab:

• Lesen Sie Indexdatensätze (keine vollständigen Zeilendatensätze).
• 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.
  

Praxis

Erstellen Sie zuerst eine Tabelle und fügen Sie Datensätze ein

CREATE TABLE-Benutzer (
id int(11) NOT NULL AUTO_INCREMENT COMMENT "Primärschlüssel",
name varchar(32) KOMMENTAR "Name",
Stadt varchar(32) KOMMENTAR "Stadt",
Alter int(11) KOMMENTAR "Alter",
Primärschlüssel (ID),
Schlüssel idx_name_city(Name, Stadt)
)engine=InnoDB Standardzeichensatz=utf8;

in Benutzer einfügen (Name, Stadt, Alter) Werte („ZhaoDa“, „Beijing“, 20), („QianEr“, „ShangHai“, 21), („SunSan“, „GuanZhou“, 22), („LiSi“, „ShenZhen“, 24), („ZhouWu“, „NingBo“, 25), („WuLiu“, „HangZhou“, 26), („ZhengQi“, „NanNing“, 27), („WangBa“, „YinChuan“, 28), („LiSi“, „TianJin“, 29), („ZhangSan“, „NanJing“, 30), („CuiShi“, „ZhengZhou“, 65), („LiSi“, „KunMing“, 29), („LiSi“, „ZhengZhou“, 30);

Überprüfen Sie die Tabellendatensätze

mysql> wähle * vom Benutzer aus;
+----+----------+--------------+------+
| ID | Name | Stadt | Alter |
+----+----------+--------------+------+
| 1 | ZhaoDa | Peking | 20 |
| 2 | QianEr | Shanghai | 21 |
| 3 | SunSan | GuanZhou | 22 |
| 4 | LiSi | ShenZhen | 24 |
| 5 | ZhouWu | NingBo | 25 |
| 6 | WuLiu | HangZhou | 26 |
| 7 | ZhengQi | NanNing | 27 |
| 8 | WangBa | YinChuan | 28 |
| 9 | LiSi | TianJin | 29 |
| 10 | ZhangSan | NanJing | 30 |
| 11 | CuiShi | ZhengZhou | 65 |
| 12 | LiSi | KunMing | 29 |
| 13 | LiSi | ZhengZhou | 30 |
+----+----------+--------------+------+
13 Zeilen im Satz (0,00 Sek.)

Beachten Sie, dass in dieser Tabelle ein gemeinsamer Index (Name, Stadt) erstellt wird. Angenommen, wir möchten die folgende Anweisung abfragen:

Wählen Sie * vom Benutzer, wobei Name="LiSi" und Stadt wie "%Z%" und Alter > 25 ist;

3.1 Kein Index-Pushdown verwenden

Ohne Index-Pushdown kann gemäß dem Prinzip „Linksübereinstimmung“ des gemeinsamen Index nur die Spalte „Name“ den Index verwenden. Die Spalte „Ort“ kann den Index nicht verwenden, da es sich um eine Fuzzy-Übereinstimmung handelt. Der Ausführungsprozess zu diesem Zeitpunkt ist wie folgt:

1. Die Speicher-Engine findet den Datensatz mit dem Namenswert LiSi basierend auf dem gemeinsamen Index (Name, Stadt), insgesamt 4 Datensätze.
2. Anschließend wird die Tabelle basierend auf den ID-Werten in diesen vier Datensätzen nacheinander gescannt, um vollständige Zeilendatensätze aus dem gruppierten Index abzurufen und diese Datensätze an die Serverebene zurückzugeben.
3. Die Serverschicht empfängt diese Datensätze und filtert sie gemäß den Bedingungen Name="LiSi" und Stadt wie "%Z%" und Alter > 25 und lässt schließlich den Datensatz ("LiSi", "ZhengZhou", 30) übrig.
   

Lass uns ein Bild zeichnen:

Indexbedingungs-Pushdown wird nicht verwendet

3.2 Index-Pushdown verwenden

Bei Verwendung von Index-Pushdown läuft die Ausführung wie folgt ab:

• Die Speicher-Engine findet 4 Datensätze mit dem Namen „LiSi“ basierend auf dem gemeinsamen Index (Name, Stadt).
• Da der gemeinsame Index die Stadtspalte enthält, filtert die Speicher-Engine den gemeinsamen Index direkt nach Stadt, beispielsweise „%Z%“. Nach dem Filtern bleiben 2 Datensätze übrig;
• Basierend auf den ID-Werten der gefilterten Datensätze wird die Tabelle einzeln gescannt, vollständige Zeilendatensätze werden aus dem gruppierten Index abgerufen und diese Datensätze werden an die Serverebene zurückgegeben.
• Die Serverebene filtert die Zeilen erneut basierend auf der anderen Bedingung der WHERE-Anweisung (Alter > 25) und lässt schließlich nur den Datensatz („LiSi“, „ZhengZhou“, 30) übrig.
  

Lass uns ein Bild zeichnen:

Verwenden des Indexbedingungs-Pushdowns

Darüber hinaus können Sie auch aus dem Ausführungsplan ersehen, dass Index-Pushdown verwendet wird (Die Bedingung „Index verwenden“ wird in Extra angezeigt).

mysql> erklären Sie „select * from user where name="LiSi" und Stadt wie "%Z%" und Alter > 25;
+----+----------+----------+---------+------+---------------+---------------+-----------+---------+---------+-------+----------+----------+------------------------------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------+---------+------+---------------+---------------+-----------+---------+---------+-------+----------+----------+------------------------------------+
| 1 | SIMPLE | Benutzer | NULL | ref | idx_name_city | idx_name_city | 99 | const | 4 | 7,69 | Indexbedingung verwenden; Where verwenden |
+----+----------+----------+---------+------+---------------+---------------+-----------+---------+---------+-------+----------+----------+------------------------------------+
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

IV. Nutzungsbedingungen

• Kann nur für die Zugriffsmethoden „range“, „ref“, „eq_ref“, „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).
  

Tipp: 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. Für den gruppierten Index von InnoDB wurden die vollständigen Zeilendatensätze in den Cache geladen, sodass ein Index-Pushdown bedeutungslos ist.

• 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.
  

5. Verwandte 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

Dies ist das Ende dieses Artikels über MySQL Index Pushdown (ICP). Weitere Informationen zu MySQL Index Pushdown (ICP) finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!