Was ist der Unterschied zwischen MySQL-Tabellenrückabfrage und Indexabdeckung?

Zurück zur Tabellenabfrage

InnoDB-Indizes sind in zwei Kategorien unterteilt, eine ist ein Clustered-Index (Clustered Index) und die andere ist ein Nicht-Clustered-Index (Sekundärindex)

#🎜 🎜# Clustered-Index: Der Blattknoten speichert die gesamte Datenzeile. Der Index ist die gleiche wie die Datenzeile die logische (Index-)Reihenfolge der Schlüsselwerte. Eine Tabelle kann nur einen Clustered-Index enthalten. Denn der Index (Verzeichnis) kann nur auf eine Art sortiert werden.

Nicht gruppierter Index (gewöhnlicher Index, nicht gruppierter Index, sekundärer Index): Der Btree-Blattknoten des nicht gruppierten Index speichert den PK (Primärschlüssel) der aktuellen Datenzeile. Beispielsweise speichert MYISAM den Index zunächst über key_buffer im Speicher. Wenn auf Daten zugegriffen werden muss (auf Daten wird über den Index zugegriffen), wird der Index direkt im Speicher durchsucht und dann die entsprechenden Daten auf der Festplatte über den Index gefunden . Aus diesem Grund trifft der Index nicht auf den Schlüsselpuffer. Der Grund für die langsame Geschwindigkeit.

Warum speichern die Blattknoten von Nicht-Primärschlüssel-Indexstrukturen Primärschlüsselwerte?

Reduziert den Wartungsaufwand für den Sekundärindex, wenn eine Zeilenverschiebung oder eine Aufteilung der Datenseite erfolgt (wenn die Daten aktualisiert werden müssen, muss der Sekundärindex nicht geändert werden, sondern nur der Cluster). Der Index muss geändert werden. Eine Tabelle kann nur einen Clustered-Index haben, und die anderen sind Sekundärindizes. Auf diese Weise müssen Sie nur den Clustered-Index ändern und den Sekundärindex nicht neu erstellen.

Bei Verwendung von nicht gruppierten Indizes Um bestimmte Daten zu erhalten, müssen wir zu diesem Zeitpunkt über den Primärschlüssel zum gruppierten Index zurückkehren und die Daten abfragen. Rufen Sie dann die Tabellenabfrage zurück. Der Indexbaum wurde zweimal gescannt. Der Wirkungsgrad ist also relativ gering.

Indexabdeckung

Indexabdeckung ist eine Lösung für Tabellenrückgabeabfragen. Wie der Name schon sagt, werden alle Spalten der Abfrage durch die verwendeten Indexspalten abgedeckt (es kann sich um einen einspaltigen Index oder einen gemeinsamen Index handeln, normalerweise einen gemeinsamen Index). Für einen einspaltigen Index ist es schwierig, alle Spalten abzudecken der Abfrage).

Da der Index bereits den Wert des abzufragenden Felds enthält, reicht es aus, den Feldwert bei der Abfrage direkt im Index zurückzugeben. Es ist nicht erforderlich, die Tabelle abzufragen, wodurch die Notwendigkeit vermieden wird für den Primärschlüsselindex verbessert auch die Effizienz der Abfrage.

id ist ein Clustered-Index, Name ist ein Nicht-Clustered-Index:

select name, age from t where name = 'lcc';

Sie müssen die Tabellenabfrage zurückgeben

Indexabdeckung: # 🎜🎜##🎜 🎜#Fragen Sie nur das Namensfeld in SQL ab. Auf diese Weise deckt der Namensindex alle Abfragespalten ab.

select name  from t where name = 'lcc';

Ändern Sie den Index des Namens in einen gemeinsamen Index (Name, Alter) und führen Sie dann „select name, age from t where name = ‚lcc‘“ aus. Dies gilt auch für alle Abfragespalten.

Da Covering-Indizes Indexspaltenwerte speichern müssen, während Hash-Indizes, räumliche Indizes und Volltextindizes keine Indexspaltenwerte speichern, können nur Daten, die B-Tree-Indizes verwenden, als Covering-Indizes verwendet werden.

Wenn Sie eine Indexabdeckungsabfrage durchführen, können Sie die Informationen zur Verwendung des Index in der Spalte „Extra“ von „explain“ (Ausführungsplan) sehen.

Vorteile der Indexabdeckung

Die Indexeinträge sind normalerweise viel kleiner als die Größe der Datenzeile , weil der abdeckende Index nur Der Index muss gelesen werden, was den Datenzugriff erheblich reduziert.

• Der Index wird in der Reihenfolge der Spaltenwerte gespeichert, und E/A-intensive Bereichssuchen sind viel kleiner als die E/A für das zufällige Lesen jeder Datenzeile von der Festplatte.

• Einige Speicher-Engines wie MyISAM speichern nur Indizes im Speicher, und die Daten müssen vom Betriebssystem zwischengespeichert werden. Daher ist für den Zugriff auf die Daten ein Systemaufruf erforderlich Covering-Index wird Dies wird vermieden.

• Für Datenbanktabellen unter der InnoDB-Engine sind Covering-Indizes aufgrund des Clustered-Index von InnoDB sehr praktisch. Da der Sekundärindex von InnoDB den Primärschlüsselwert der Zeile im Blattknoten speichert, wird eine Sekundärabfrage des Primärschlüsselindex vermieden, wenn der Sekundärindex die Abfrage abdecken kann.

• Welche Szenarien eignen sich für die Verwendung der Indexabdeckung zur Optimierung von SQL? muss nicht abgefragt werden;

Optimierung der vollständigen Tabellenanzahl; 🎜#

• Unter welchen Umständen sollten Sie keinen Index erstellen?

Tabellen oder Felder, die häufig hinzugefügt, gelöscht oder geändert werden (z. B Benutzersaldo)
Für Felder, die nicht in der Wo-Bedingung verwendet werden, wird kein Index erstellt#🎜🎜 #

• Diejenigen mit schlechter Filterbarkeit sind nicht für die Indizierung geeignet (z. B. Geschlecht). Beim Indexdurchlauf können zunächst die im Index enthaltenen Felder beurteilt und Datensätze, die die Bedingungen nicht erfüllen, direkt herausgefiltert werden, um die Anzahl der Tabellenrückgaben zu verringern.
#🎜🎜 #Erstellen Sie einen gemeinsamen Index:

KEY `username` (`name`,`age`) )

Ausführung:

select * from user2 where name like 'j%' and age=99;

Die obige Abfrage-SQL entspricht dem Prinzip des am weitesten links stehenden Präfixes des Index, daher wird der Benutzernamenindex

#🎜 verwendet 🎜#Der Der Ausführungsablauf des oben genannten SQL in 5.5 ist wie folgt:

• Zuerst ruft die Serverschicht von MySQL die Speicher-Engine auf, um den ersten Benutzernamen zu erhalten, der mit j beginnt.

• Nachdem die Speicher-Engine den ersten Datensatz von username=‘j’ gefunden hat, wird die Primärschlüssel-ID zu diesem Zeitpunkt im Blattknoten von B+Tree gespeichert , durch Rückkehr Suchen Sie bei Tabellenoperationen die vollständigen Daten des Datensatzes im Primärschlüsselindex und geben Sie sie an die Serverschicht zurück.

• Nachdem die Serverschicht die Daten erhalten hat, bestimmt sie, ob das Alter des Datensatzes 99 beträgt. Wenn Alter = 99, wird der Datensatz an den Client zurückgegeben. Wenn Alter! = 99, wird der Datensatz verworfen.

Der Ausführungsablauf des oben genannten SQL in 5.6 ist wie folgt:

• Die Serverschicht von MySQL ruft zunächst die Speicher-Engine auf, um den ersten Benutzernamen zu finden, der mit j beginnt.

• Nachdem die Speicher-Engine den Datensatz gefunden hat, hat sie es nicht eilig, zur Tabelle zurückzukehren, sondern ermittelt weiterhin, ob das Alter dieses Datensatzes 99 beträgt. Wenn Alter = 99, kehrt er zur Tabelle zurück. Wenn das Alter nicht 99 Jahre beträgt, wird die Tabelle nicht zurückgegeben. Fahren Sie direkt mit dem Lesen des nächsten Datensatzes fort.

Das obige ist der detaillierte Inhalt vonWas ist der Unterschied zwischen MySQL-Tabellenrückabfrage und Indexabdeckung?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!