So verwenden Sie MySQL zur Abdeckung von Index- und Tabellenrückgabe

Zwei Hauptkategorien von Indizes

Verwendete Speicher-Engine: MySQL 5.7 InnoDB

Gruppierter Index

• * Wenn die Tabelle einen Primärschlüssel hat, ist der Primärschlüssel der gruppierte Index
• * Wenn die Tabelle keinen Primärschlüssel hat, wird standardmäßig die erste NOT NULL- und eindeutige (UNIQUE) Spalte als gruppierter Index verwendet
• * Wenn keine der oben genannten Angaben gemacht wird, wird standardmäßig eine versteckte Zeilen-ID als gruppierter Index erstellt.

Die Blattknoten des gruppierten Index von InnoDB speichern Zeilendatensätze (tatsächlich eine Seitenstruktur, eine Seite enthält mehrere Datenzeilen). InnoDB muss mindestens einen gruppierten Index haben.

Es ist ersichtlich, dass die Verwendung einer Clustered-Index-Abfrage sehr schnell ist, da der Zeilendatensatz direkt lokalisiert werden kann.

Normaler Index

Gewöhnliche Indizes werden auch als Sekundärindizes bezeichnet. Dabei handelt es sich um andere Indizes als gruppierte Indizes, d. h. nicht gruppierte Indizes.

Die gewöhnlichen Index-Blattknoten von InnoDB speichern den Wert des Primärschlüssels (gruppierter Index), während der gewöhnliche Index von MyISAM Datensatzzeiger speichert.

Beispiel

Erstellen einer Tabelle

mysql> Tabelle erstellen Benutzer(
  -> id int(10) auto_increment,
  -> Name varchar(30),
  -> Alter tinyint(4),
  -> Primärschlüssel (ID),
  -> Index idx_age (Alter)
  ->)Engine=InnoDB-Zeichensatz=utf8mb4;

Das ID-Feld ist ein gruppierter Index und das Altersfeld ist ein normaler Index (sekundärer Index).

Daten füllen

in Benutzer (Name, Alter) Werte einfügen ('Name', 30);
in Benutzer (Name, Alter) Werte einfügen ('Name', 20);
in Benutzer (Name, Alter) Werte einfügen ('Name', 40);
in Benutzer (Name, Alter) Werte einfügen ('Name', 10);

mysql> wähle * vom Benutzer aus;
+----+--------+------+
| ID | Name | Alter |
+----+--------+------+
| 1 | Zhang San | 30 |
| 2 | Li Si | 20 |
| 3 | Wang Wu | 40 |
| 4 | Liu Ba | 10 |
+----+--------+------+

Index-Speicherstruktur

id ist der Primärschlüssel, es handelt sich also um einen gruppierten Index, und seine Blattknoten speichern die Daten der entsprechenden Zeilendatensätze.

ClusteredIndex

Alter ist ein gemeinsamer Index (Sekundärindex), ein nicht gruppierter Index, und seine Blattknoten speichern die Werte des gruppierten Indexes.

Normaler Index (Sekundärindex)

Wenn die Abfragebedingung der Primärschlüssel (Clusterindex) ist, müssen Sie den B+-Baum nur einmal scannen, um die Zeilendatensatzdaten zu finden, die über den Clusterindex gesucht werden sollen.

Beispiel: select * from user where id = 1;

Clustered-Index-Suchprozess

Wenn die Abfragebedingung ein gemeinsamer Index (nicht gruppierter Index) ist, muss der B+-Baum zweimal gescannt werden. Der erste Scan lokalisiert den Wert des gruppierten Indexes über den gemeinsamen Index, und dann lokalisiert der zweite Scan die zu findenden Zeilendatensatzdaten über den Wert des gruppierten Indexes.
Beispiel: select * from user where age = 30;

1. Suchen Sie zunächst den Primärschlüsselwert id=1 über den gemeinsamen Index age=30

2. Suchen Sie dann die Zeilendatensatzdaten über den Clustered-Index id=1

Der erste Schritt des normalen Indexsuchprozesses

Der zweite Schritt des normalen Indexsuchprozesses

Zurück zur Tabellenabfrage

Suchen Sie zuerst den Clusterindexwert über den Wert des normalen Index und dann die Zeilendatensatzdaten über den Wert des Clusterindex. Es ist erforderlich, den Index B + -Baum zweimal zu scannen, und seine Leistung ist geringer als beim einmaligen Scannen des Indexbaums.

Indexabdeckung

Alle von SQL benötigten Spaltendaten können in einem Indexbaum abgerufen werden, ohne zur Tabelle zurückkehren zu müssen, was schneller ist.

Beispiel: select id,age from user where age = 10;

So implementieren Sie einen abdeckenden Index

Eine gängige Methode besteht darin, die abgefragten Felder in einen gemeinsamen Index einzubauen.

1. Beispiel: select id,age from user where age = 10;

Erläuterung der Analyse: Da das Alter ein allgemeiner Index ist, wird der Altersindex verwendet und die entsprechenden Ergebnisse können durch einmaliges Scannen des B + -Baums abgefragt werden, wodurch ein abdeckender Index erhalten wird.

2. Implementierung: select id,age,name from user where age = 10;

Erläuterung der Analyse: Alter ist ein gemeinsamer Index, aber die Spalte Name befindet sich nicht im Indexbaum. Daher ist es nach der Abfrage der Werte von ID und Alter über den Altersindex erforderlich, zur Tabelle zurückzukehren und den Wert von Name abzufragen. Der NULL-Wert in der Spalte „Extra“ zeigt an, dass eine Tabellenabfrage durchgeführt wurde.

Um eine Indexabdeckung zu erreichen, ist es notwendig, einen zusammengesetzten Index idx_age_name(age,name) zu erstellen.

Löschen Sie den Index idx_age für den Benutzer.
Erstellen Sie den Index idx_age_name für den Benutzer („Alter“, „Name“).

Erläuterung der Analyse: Zu diesem Zeitpunkt sind die Felder Alter und Name der zusammengesetzte Index idx_age_name. Die Werte der abgefragten Felder id, Alter und Name befinden sich nur im Indexbaum. Es ist nur erforderlich, den zusammengesetzten Index B+-Baum einmal zu scannen. Auf diese Weise wird die Indexabdeckung erreicht. Zu diesem Zeitpunkt wird für das zusätzliche Feld Index verwendet, was bedeutet, dass die Indexabdeckung verwendet wird.

Welche Szenarien eignen sich für die Verwendung von Index-Covering zur SQL-Optimierung?

Optimieren Sie die Zählabfrage der gesamten Tabelle

mysql> Tabelle erstellen Benutzer(
  -> id int(10) auto_increment,
  -> Name varchar(30),
  -> Alter tinyint(4),
  -> Primärschlüssel (ID),
  ->)Engine=InnoDB-Zeichensatz=utf8mb4;

Beispiel: Wählen Sie Anzahl(Alter) vom Benutzer aus;

Verwenden der Indexabdeckungsoptimierung: Erstellen eines Index für das Altersfeld

Erstelle den Index idx_age für Benutzer(Alter);

Optimierung der Spaltenabfrage-Rücktabelle

Das im vorherigen Artikel verwendete Beispiel zur Beschreibung der Verwendung der Indexabdeckung ist

Beispiel: select id,age,name from user where age = 10;

Indexabdeckung verwenden: Erstellen Sie einen zusammengesetzten Index idx_age_name(age,name)

Paginierungsabfrage

Beispiel: select id,age,name from user order by age limit 100,2;

Da das Namensfeld kein Index ist, ist für Paging-Abfragen eine Back-Table-Abfrage erforderlich. In diesem Fall verwendet Extra Filesort und die Abfrageleistung ist schlecht.

Indexabdeckung verwenden: Erstellen Sie einen zusammengesetzten Index idx_age_name(age,name)

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, er wird für jedermanns Studium hilfreich sein. Ich hoffe auch, dass jeder 123WORDPRESS.COM unterstützen wird.