Beispielanalyse der MySQL-Indexabdeckung

Dieser Artikel beschreibt die MySQL-Indexabdeckung anhand von Beispielen. Teilen Sie uns die Einzelheiten zu Ihrer Information mit:

Indexabdeckung

Wenn die abgefragte Spalte zufällig Teil des Indexes ist, muss die Abfrage nur für die Indexdatei ausgeführt werden und es ist nicht erforderlich, zur Suche nach den Daten auf der Festplatte zurückzukehren. Diese Art der Abfrage ist sehr schnell und wird als „Index-Covering“ bezeichnet.

Angenommen, es gibt eine t15-Tabelle, in der ein gemeinsamer Index erstellt wird: cp (cat_id, price)

Wenn wir die folgende SQL-Anweisung verwenden, erfolgt eine Indexabdeckung. Wenn Sie das nicht glauben, können wir es uns ansehen. Das Extra hier zeigt Using index, was bedeutet, dass diese SQL-Anweisung nur Indexabdeckung verwendet.

Wählen Sie den Preis aus t15, wobei cat_id = 1 ist;

Sehen wir uns eine Frage an. Erstellen Sie eine T11-Tabelle mit einem Index für die E-Mail-Spalte.

Angenommen, wir verwenden die folgende Abfrage:

Wählen Sie die E-Mail aus T11, wobei right(email,4)='.com' ist.

Abfrageanalyse:

Schauen wir uns zunächst Extra an. Hier steht Using index, was bedeutet, dass hier Indexabdeckung verwendet wird. Der Grund, warum possible_keys NULL ist, liegt darin, dass die Funktion in MySQL verwendet wird, sodass der E-Mail-Index während der Abfrage nicht verwendet wird, aber der Schlüssel ist email, was bedeutet, dass der Index zum Sortieren verwendet wird. Wenn Sie mir nicht glauben, drucke ich die Daten aus und schaue nach.

Die Daten hier sind sortiert. Die Originaldaten sehen so aus.

Indexabdeckungsproblem

Erstellen Sie Tabelle A (
id varchar(64) Primärschlüssel,
ver int,
…
)

Die Tabelle enthält mehrere sehr lange Felder varbinary(3000) mit gemeinsamen Indizes für id und ver, insgesamt 10.000 Datensätze

Warum ist select id from A order by id so langsam?
Und select id from A order by id,ver ist sehr schnell

Frage: id und (id,ver) haben beide Indizes, und select id sollte den Effekt der „Indexabdeckung“ erzeugen. Warum ist ersteres langsam und letzteres schnell?

Idee: Der Unterschied zwischen InnoDB-Clusterindex und MyISAM-Index. Betrachten Sie die Indexabdeckung aus diesen beiden Perspektiven

(1) Unter der Annahme, dass diese Tabelle einen MyISAM-Index verwendet, muss keine der beiden SQL-Anweisungen eine Zeile zurückgehen, um nach Daten zu suchen. Ihre Geschwindigkeiten sollten also ähnlich sein.

(2) Angenommen, diese Tabelle verwendet den InnoDB-Index, dann verwendet die SQL-Anweisung „select id from A order by id“ den Primärschlüsselindex. Dies liegt daran, dass jeder Primärschlüssel von InnoDB die Daten jeder Zeile enthält und es in dieser Abfrage mehrere besonders große Felder gibt, sodass die Suche nach der ID relativ langsam ist. Die SQL-Anweisung „select id from A order by id,ver“ verwendet den gemeinsamen Index „id,ver“. In der InnoDB-Speicher-Engine speichert der Sekundärindex die Anwendung des Primärschlüsselindex, sodass der Sekundärindex nicht die Daten der Zeile enthält. In diesem Fall ist die Suche nach der ID im Index (id,ver) schneller. Wenn der entsprechende Knotenbaum gefunden wurde, muss nur erneut nach der Position des Primärschlüsselindex gesucht werden, um die Daten der Zeile zu erhalten. Das ist schneller.

schließen:

(1) Wenn die Tabelle die MyISAM-Engine verwendet, gibt es keinen signifikanten Unterschied in der Geschwindigkeit zwischen den beiden Anweisungen.
(2) Aufgrund des Clustered-Index muss sich der ID-Index der InnoDB-Tabelle über mehrere Blöcke auf der Festplatte erstrecken, was zu einer langsamen Geschwindigkeit führt.
(3) Auch bei der InnoDB-Engine gibt es keinen signifikanten Unterschied in der Geschwindigkeit der beiden Anweisungen, wenn keine langen Varbinay-Spalten vorhanden sind.

Tabelle t12, Speicher-Engine MyISAM, hat Primärschlüsselindex und (id,ver)-Index sowie mehrere große Felder mit variabler Länge. Testinferenz 1

Tabelle t13 mit der Speicher-Engine InnoDB verfügt über einen Primärschlüsselindex und einen (id,ver)-Index sowie mehrere große Felder mit variabler Länge.

Tabelle t14, Speichermodul ist InnoDB, hat Primärschlüsselindex und (id,ver)-Index, keine großen Felder mit variabler Länge Folgerung 3

Jede Tabelle von t12, t13 und t14 enthält 10.000 Daten, und dann wird ein Test durchgeführt. Die Testergebnisse sind wie folgt und unsere Schlussfolgerung ist korrekt.

Leser, die an weiteren MySQL-bezogenen Inhalten interessiert sind, können sich die folgenden Themen auf dieser Site ansehen: „Zusammenfassung der Kenntnisse im Bereich MySQL-Indexoperationen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-Allgemeinfunktionen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-Protokolloperationen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-Transaktionsoperationen“, „Zusammenfassung der Kenntnisse im Bereich MySQL-gespeicherte Prozeduren“ und „Zusammenfassung der Kenntnisse im Zusammenhang mit MySQL-Datenbanksperren“.

Ich hoffe, dass dieser Artikel für jedermann beim Entwurf einer MySQL-Datenbank hilfreich ist.