Detaillierte Erklärung der MySQL InnoDB-Indexerweiterung

Indexerweiterung: InnoDB erweitert automatisch jeden sekundären Index, indem es die Primärschlüsselspalten an diesen Index anhängt. Erstellen Sie die folgende Tabellenstruktur:

mysql> CREATE TABLE t1 (
  -> i1 INT NICHT NULL STANDARD 0,
  -> i2 INT NICHT NULL STANDARD 0,
  -> d DATE DEFAULT NULL,
  -> PRIMÄRSCHLÜSSEL (i1, i2),
  -> INDEX k_d (d)
  ->) ENGINE = InnoDB;

Abfrage OK, 0 Zeilen betroffen (0,14 Sek.)

Tabelle t1 hat einen Primärschlüssel, der in den Spalten (i1, i2) definiert ist. Ein sekundärer Index ist auch in Spalte (d) definiert, aber InnoDB erweitert diesen Index und behandelt ihn als (d,i1,i2).

Der Optimierer berücksichtigt die Primärschlüsselspalten erweiterter sekundärer Indizes bei der Entscheidung, wie und ob der Index verwendet werden soll. Dies kann zu effizienteren Abfrageausführungsplänen und einer besseren Leistung führen.

Der Optimierer kann erweiterte sekundäre Indizes für den Ref-, Range- und Index_Merge-Indexzugriff, für lose Indexscans, für Join- und Sortieroptimierungen und für MIN()/MAX()-Optimierungen verwenden.

Das folgende Beispiel zeigt, ob der Optimierer einen erweiterten Sekundärindex verwendet, um den Ausführungsplan so zu beeinflussen, dass die folgenden Daten in die Tabelle t1 eingefügt werden:

mysql> INSERT INTO t1 VALUES (1, 1, '1998-01-01'), (1, 2, '1999-01-01'), (1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
  ->(1, 5, '2002-01-01'), (2, 1, '1998-01-01'), (2, 2, '1999-01-01'), (2, 3, '2000-01-01'), (2, 4, '2001-01-01'),
  ->(2, 5, '2002-01-01'), (3, 1, '1998-01-01'), (3, 2, '1999-01-01'), (3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
  ->(3, 5, '2002-01-01'), (4, 1, '1998-01-01'), (4, 2, '1999-01-01'), (4, 3, '2000-01-01'), (4, 4, '2001-01-01'),
  ->(4, 5, '2002-01-01'), (5, 1, '1998-01-01'), (5, 2, '1999-01-01'), (5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
  ->(5, 5, '01.01.2002');
Abfrage OK, 25 Zeilen betroffen (0,05 Sek.)
Datensätze: 25 Duplikate: 0 Warnungen: 0

Angenommen, die folgende Abfrage wird ausgeführt:

Setzen Sie optimizer_switch = "use_index_extensions=off";
Erläutern Sie „Select count(*) from t1“, wobei i1=3 und d= „2000-01-01“;

In diesem Fall kann der Optimierer den Primärschlüssel nicht verwenden, da der Primärschlüssel die Spalten (i1, i2) enthält und die Abfrage nicht auf i2 verweist. Stattdessen kann der Optimierer den sekundären Index k_d in Spalte (d) verwenden, und der Ausführungsplan hängt davon ab, ob der erweiterte Index verwendet wird.

Wenn der Optimierer die Indexerweiterung nicht berücksichtigt, behandelt er den Index k_d einfach als (d).

mysql> SET optimizer_switch = "use_index_extensions=off";
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> erkläre „select count(*) from t1“, wobei i1=3 und d= '2000-01-01' \G;
*************************** 1. Reihe ***************************
      ID: 1
 select_type: EINFACH
    Tabelle: t1
  Partitionen: NULL
     Typ: ref
mögliche Schlüssel: PRIMARY,k_d
     Schlüssel: PRIMARY
   Schlüssellänge: 4
     Verweis: const
     Reihen: 5
   gefiltert: 20.00
    Extra: Verwenden von „where“
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

Wenn der Optimierer eine Indexerweiterung in Betracht zieht, betrachtet er k_d als (d, i1, i2). In diesem Fall kann das Indexpräfix ganz links (d, i1) verwendet werden, um einen besseren Ausführungsplan zu generieren.

mysql> SET optimizer_switch = "use_index_extensions=on";
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> erkläre „select count(*) from t1“, wobei i1=3 und d= '2000-01-01' \G;
*************************** 1. Reihe ***************************
      ID: 1
 select_type: EINFACH
    Tabelle: t1
  Partitionen: NULL
     Typ: ref
mögliche Schlüssel: PRIMARY,k_d
     Schlüssel: k_d
   Schlüssellänge: 8
     Verweis: konstant, konstant
     Reihen: 1
   gefiltert: 100,00
    Extra: Index verwenden
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

In beiden Fällen gibt key an, dass der Optimierer den sekundären Index k_d verwendet, aber die EXPLAIN-Ausgabe zeigt diese Verbesserungen durch die Verwendung des erweiterten Index:

.key_len wurde von 4 Bytes auf 8 Bytes geändert, was darauf hinweist, dass die Schlüsselsuche die Spalten d und i1 verwendet, nicht nur d.

Der Wert von .ref ändert sich von const zu const,const, da die Schlüsselsuche zwei Schlüsselspalten statt einer verwendet.

.rows: Von 5 auf 1 verringert, was darauf hinweist, dass InnoDB weniger Zeilen untersucht, um Abfrageergebnisse zu generieren.

Der .Extra-Wert wurde von „Using where; Using index“ in „Using index“ geändert. Dies bedeutet, dass zum Abfragen von Datensätzen nur die Verwendung von Indizes und nicht die Abfrage von Datenzeilendatensätzen erforderlich ist.

Mit „show status“ können Sie den Unterschied zwischen dem Optimierer mit und ohne erweiterte Indizes sehen:

mysql> Tabelle t1 leeren;
Abfrage OK, 0 Zeilen betroffen (0,01 Sek.)

mysql> Flush-Status;
Abfrage OK, 0 Zeilen betroffen (0,03 Sek.)

Die obigen Anweisungen zum Leeren der Tabellentabelle und zum Leeren des Status werden zum Leeren des Tabellencaches und der Statusstatistiken verwendet.

Die Ergebnisse der Statusanzeige bei nicht verwendeter Indexerweiterung lauten wie folgt:

mysql> SET optimizer_switch = "use_index_extensions=off";
Abfrage OK, 0 Zeilen betroffen (0,01 Sek.)

mysql> wähle count(*) von t1, wobei i1=3 und d= '2000-01-01';
+----------+
| Anzahl(*) |
+----------+
| 1 |
+----------+
1 Zeile im Satz (0,00 Sek.)

mysql> Status wie „handler_read%“ anzeigen;
+--------------------------+----------+
| Variablenname | Wert |
+--------------------------+----------+
| Handler_read_first | 0 |
| Handler_Schlüssel lesen | 1 |
| Zuletzt gelesener Handler | 0 |
| Weiter lesen | 5 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+--------------------------+----------+
7 Zeilen im Satz (0,00 Sek.)

Bei Verwendung der Indexerweiterung erzeugt „show status“ die folgenden Ergebnisse, wobei der Wert von handler_read_next von 5 auf 1 sinkt, was darauf hinweist, dass die Verwendung dieses Indexes effizienter ist:

mysql> Tabelle t1 leeren;
Abfrage OK, 0 Zeilen betroffen (0,01 Sek.)

mysql> Flush-Status
  -> ;
Abfrage OK, 0 Zeilen betroffen (0,02 Sek.)

mysql> SET optimizer_switch = "use_index_extensions=on";
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> wähle count(*) von t1, wobei i1=3 und d= '2000-01-01';
+----------+
| Anzahl(*) |
+----------+
| 1 |
+----------+
1 Zeile im Satz (0,00 Sek.)

mysql> Status wie „handler_read%“ anzeigen;
+--------------------------+----------+
| Variablenname | Wert |
+--------------------------+----------+
| Handler_read_first | 0 |
| Handler_Schlüssel lesen | 1 |
| Zuletzt gelesener Handler | 0 |
| Weiter lesen | 1 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+--------------------------+----------+
7 Zeilen im Satz (0,01 Sek.)

Das Flag use_index_extensions der Systemvariable optimizer_switch ermöglicht es dem Optimierer, Primärschlüsselspalten von der Betrachtung auszuschließen, wenn entschieden wird, wie Sekundärindizes für InnoDB-Tabellen verwendet werden. Standardmäßig ist use_index_extensions aktiviert. Um zu überprüfen, ob das Deaktivieren von Indexerweiterungen die Leistung verbessern kann, können Sie die folgende Anweisung ausführen:

mysql> SET optimizer_switch = "use_index_extensions=off";
Abfrage OK, 0 Zeilen betroffen (0,01 Sek.)

Oben finden Sie eine ausführliche Erläuterung der MySQL InnoDB-Indexerweiterung. Weitere Informationen zur MySQL-Indexerweiterung finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!

Das könnte Sie auch interessieren:

Detaillierte Erläuterung des Index und der Speicherstruktur der MySQL InnoDB-Engine
Detaillierte Erläuterung des MySQL InnoDB-Sekundärindex-Sortierbeispiels
Ausführliche Erläuterung des Prinzips des MySQL Innodb-Index
Detaillierte Einführung in den MySQL Innodb Index-Mechanismus
Index und Algorithmus der Mysql Innodb-Speicher-Engine

<<: Detaillierte Erläuterung der Bereitstellungs- und Konfigurationsmethoden des Linux-Zabbix-Agenten

>>: Implementierungsfallcodeanalyse für dynamische und statische Trennung von Nginx

Was sind HTML-Inline-Elemente und Block-Level-Elemente und ihre Unterschiede

Mehrere Möglichkeiten, das Problem des Schwebens zu lösen, das dazu führt, dass die Höhe des übergeordneten Elements in CSS zusammenbricht

Artikel

Mehrere Möglichkeiten zur Implementierung eines zweispaltigen Layouts mit fester Breite links und adaptiver Breite rechts mithilfe von CSS

7 Möglichkeiten, mit CSS ein zweispaltiges Layout...

Detaillierte Erklärung der MySQL InnoDB-Indexerweiterung

Was sind HTML-Inline-Elemente und Block-Level-Elemente und ihre Unterschiede

So importieren Sie Vue-Komponenten automatisch bei Bedarf

Details zur Verwendung von aktiviertem Vue in untergeordneten Komponenten

So verwenden Sie vue-cli, um ein Projekt zu erstellen und es mit webpack zu verpacken

So überwachen Sie MySQL mit Zabbix

So implementieren Sie Nginx Reverse Proxy und Load Balancing (basierend auf Linux)

Optimierung der MySQL 4G-Speicherserverkonfiguration

Allgemeine Linux-Befehle chmod zum Ändern der Dateiberechtigungen 777 und 754

Beispielanalyse der drei Verbindungsmethoden für virtuelle VMware-Maschinen

Mehrere Möglichkeiten, das Problem des Schwebens zu lösen, das dazu führt, dass die Höhe des übergeordneten Elements in CSS zusammenbricht

Artikel empfehlen

Detaillierte Erklärung der Zusammenhänge zwischen Bildformat und Design im Webdesign

Häufige Fragen und Antworten im Vorstellungsgespräch für Stellen als Webdesigner

MYSQL METADATA LOCK (MDL LOCK) Theorie und Sperrtyptest

Implementierung einer zeitgesteuerten MySQL-Datenbanksicherung (vollständige Datenbanksicherung)

Details zur Verwendung der JS-Tag-Syntax

Code zum Aktivieren von IE8 im IE7-Kompatibilitätsmodus

Detaillierte Erläuterung der MySQL Master-Slave-Replikation und der Lese-/Schreibtrennung

Einfache Schritte zum Kapseln von Komponenten in Vue-Projekten

Einführung in die reaktive Funktion toRef-Funktion ref-Funktion in Vue3

Zusammenfassung der Überwachung von Tastaturereignissen durch Vue

Mehrere Möglichkeiten zur Implementierung eines zweispaltigen Layouts mit fester Breite links und adaptiver Breite rechts mithilfe von CSS

Detaillierte Erklärung der Lösung zum Vergessen des Passworts in MySQL 5.7

Detaillierte Erklärung der Verwendung des SS-Befehls in Linux in Kombination mit Zabbix zum Überwachen von Sockets

Mycli ist ein unverzichtbares Tool für MySQL-Befehlszeilen-Enthusiasten

Implementierungsmethode für den React State-Zustand und Lebenszyklus