Ausführliche Erläuterung des Prinzips des MySQL Innodb-Index

Einführung

Wenn ich zurückblicke, sagte der Lehrer vor vier Jahren, als ich etwas über MySQL-Indizes lernte, Folgendes, als es um Indizes ging:

Ein Index ist wie das Inhaltsverzeichnis eines Buches. Wenn ein Benutzer über einen Index nach Daten sucht, ist dies vergleichbar mit der Suche eines Benutzers nach einem bestimmten Wissenspunkt in einem Kapitel über ein Verzeichnis. Dies hilft Benutzern, ihre Suchgeschwindigkeit effektiv zu verbessern. Daher kann die Verwendung von Indizes die Gesamtleistung des Datenbanksystems effektiv verbessern.

Nun, das stimmt tatsächlich. Allerdings denken Sie nach der Lektüre dieser Aussage möglicherweise immer noch, dass sie zu abstrakt ist! Aus diesem Grund möchte ich es genauer erklären, daher ist dieser Artikel entstanden!

Es ist zu beachten, dass meine Aussage nur für die Innodb-Engine von MySQL gilt. Die Korrektheit der MySQL-Engine von SQL Server, Oracle und MySQL ist möglicherweise nicht gewährleistet!

InnoDB ist die am häufigsten verwendete Speicher-Engine für MySQL. Das Verständnis des Index der InnoDB-Speicher-Engine ist für die tägliche Arbeit von großem Nutzen. Die Existenz des Index dient dazu, das Abrufen von Datenbankzeilendatensätzen zu beschleunigen.

Was ist ein Index?

Der Index wird als Verzeichnis übersetzt und dient zum schnellen Auffinden der gewünschten Daten. Beispielsweise vergleichen wir eine Datenbank mit einem Buch, und der Index ist das Inhaltsverzeichnis des Buches. Um einen bestimmten interessanten Inhalt im Buch zu finden, blättern wir normalerweise nicht das gesamte Buch durch, um zu bestätigen, wo sich der Inhalt befindet. Stattdessen verwenden wir das Inhaltsverzeichnis, um die Seite zu finden, auf der sich das Inhaltskapitel befindet, und blättern dann direkt zu dieser Seite.

Werfen wir einen Blick auf die Indizes in der Datenbank:

Vollständiger Tabellenscan vs. Indexscan

Am Beispiel eines Wörterbuchs bedeutet ein vollständiger Tabellenscan, dass wir, wenn wir nach einem bestimmten Wort suchen, das Xinhua-Wörterbuch durchlesen und dann das gewünschte Wort finden. Dem vollständigen Tabellenscan entspricht eine Indexsuche, bei der wir den spezifischen Speicherort der gewünschten Daten im Indexteil der Tabelle finden und dann alle gewünschten Daten in der Tabelle finden.

OK, lasst uns ohne weitere Umschweife mit dem Reden beginnen!

Text

Populärwissenschaft des Index

Stellen Sie zunächst die Konzepte des gruppierten Index und des nicht gruppierten Index vor!

In dem von uns normalerweise verwendeten MySQL verwenden wir die folgende Anweisung

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX Indexname
 [VERWENDUNG von Indextyp]
 ON tbl_name (index_col_name, …)
 
Indexspaltenname:
 col_name [(Länge)] [ASC | DESC]

Die erstellten Indizes, wie zusammengesetzte Indizes, Präfixindizes und eindeutige Indizes, sind alle nicht gruppierte Indizes. In einigen Büchern werden sie auch als sekundäre Indizes bezeichnet. Im folgenden Text nennen wir ihn einen nicht gruppierten Index und seine Datenstruktur ist ein B+-Baum.

Nun, es gibt in MySQL keine Anweisung, diesen gruppierten Index separat zu generieren. In Innodb werden die Daten in MySQL in der Reihenfolge des Primärschlüssels gespeichert. Anschließend erstellt der gruppierte Index einen B+-Baum entsprechend dem Primärschlüssel jeder Tabelle, und die Blattknoten speichern die Zeilendaten der gesamten Tabelle. Da die Daten in der Tabelle nur nach einem B+-Baum sortiert werden können, kann eine Tabelle nur einen gruppierten Index haben.

In Innodb ist der Clusterindex standardmäßig der Primärschlüsselindex.

An dieser Stelle sollten mich die klugen Leser fragen

Was ist, wenn meine Tabelle keinen Primärschlüssel hat?

Die Antwort lautet: Wenn kein Primärschlüssel vorhanden ist, erstellen Sie einen gruppierten Index nach den folgenden Regeln:

Wenn kein Primärschlüssel vorhanden ist, wird eine eindeutige und nicht leere Indexspalte als Primärschlüssel verwendet, die zum gruppierten Index dieser Tabelle wird. Wenn kein solcher Index vorhanden ist, definiert InnoDB implizit einen Primärschlüssel als gruppierten Index.

PS: Erinnern Sie sich noch an den Unterschied zwischen dem automatisch inkrementierten Primärschlüssel und der UUID als Primärschlüssel? Da der Primärschlüssel einen gruppierten Index verwendet, müssen, wenn der Primärschlüssel eine automatisch inkrementierende ID ist, die entsprechenden Daten auch nebeneinander auf der Festplatte gespeichert werden, und die Schreibleistung ist relativ hoch. Wenn es die Form einer UUID hat, führen häufige Einfügungen dazu, dass InnoDB häufig Festplattenblöcke verschiebt und die Schreibleistung relativ niedrig ist.

Einführung in die Indizierungsprinzipien

Nehmen wir zunächst eine Tabelle mit einem Primärschlüssel, wie unten gezeigt, pId ist der Primärschlüssel

Zeichnen Sie das Strukturdiagramm der Tabelle wie folgt

Wie in der obigen Abbildung gezeigt, ist es in zwei Teile unterteilt, der obere Teil ist der durch den Primärschlüssel gebildete B + -Baum und der untere Teil sind die tatsächlichen Daten auf der Festplatte! Wenn wir dann die folgende Anweisung ausführen

Wählen Sie * aus der Tabelle, in der pId = "11" ist.

Der Ausführungsprozess ist dann wie folgt

Wie in der obigen Abbildung gezeigt, können die tatsächlichen Daten nach drei Suchvorgängen gefunden werden, wenn man von der Wurzel ausgeht. Wenn Sie keinen Index verwenden, müssen Sie die Festplatte Zeile für Zeile durchsuchen, bis Sie den Datenspeicherort finden. Die Verwendung eines Indexes ist offensichtlich schneller. Beim Schreiben von Daten muss jedoch die Struktur dieses B+-Baums beibehalten werden, sodass die Schreibleistung abnimmt!

OK, führen wir einen nicht gruppierten Index ein! Wir führen die folgende Anweisung aus

Erstellen Sie den Index Indexname für Tabelle (Name).

Das Strukturdiagramm sieht wie folgt aus

Bitte beachten Sie, dass basierend auf Ihrem Indexfeld ein neuer B+-Baum generiert wird. Daher erhöhen wir jedes Mal, wenn wir einen Index hinzufügen, die Größe der Tabelle und belegen Speicherplatz auf der Festplatte. Achten Sie jedoch auf die Blattknoten. Die Blattknoten von nicht gruppierten Indizes sind keine echten Daten. Ihre Blattknoten sind immer noch Indexknoten, die den Wert des Indexfelds und den entsprechenden Primärschlüsselindex (gruppierter Index) speichern.

Wenn wir die folgende Anweisung ausführen

Wählen Sie * aus der Tabelle, in der Name = "lisi" ist

Das Strukturdiagramm sieht wie folgt aus

Anhand der roten Linie in der obigen Abbildung ist ersichtlich, dass die Suche beim nicht gruppierten Indexbaum beginnt und dann der gruppierte Index gefunden wird. Finden Sie gemäß dem gruppierten Index die vollständigen Daten im B+-Baum des gruppierten Indexes!

Das

Warum nicht den gruppierten Indexbaum abfragen?

Denken Sie daran, dass unser nicht gruppierter Indexbaum den Wert des Indexfelds speichert. Wenn wir zu diesem Zeitpunkt die folgende Anweisung ausführen

wähle den Namen aus der Tabelle, in der Name='lisi' ist

Das Strukturdiagramm sieht wie folgt aus

Wie die rote Linie in der Abbildung oben zeigt, wird der gruppierte Indexbaum nicht abgefragt, wenn der gewünschte Wert im nicht gruppierten Indexbaum gefunden wird. Denken Sie an das Indizierungsproblem, das der Blogger in „Die richtige Haltung von Select“ erwähnt hat:

Bei der Ausführung von „select col from table where col = ?“ und einem Index auf col ist die Effizienz um ein Vielfaches höher als bei der Ausführung von „select * from table where col = ?!“

Nachdem Sie sich das Bild oben angesehen haben, sollten Sie diesen Satz besser verstehen.

Also führen wir jetzt die folgende Anweisung aus. Was wird passieren?

Erstellen Sie den Index „index_birthday“ für die Tabelle (Geburtstag).

Das Strukturdiagramm sieht wie folgt aus

Sie sehen, durch das Hinzufügen eines weiteren Index wird ein weiterer nicht gruppierter Indexbaum generiert. Aus diesem Grund heißt es in vielen Artikeln, dass Indizes nicht beliebig hinzugefügt werden können. Denn es gibt genauso viele nicht gruppierte Indexbäume wie Indizes! Wenn Sie einen Einfügevorgang durchführen, müssen Sie die Änderungen dieser Bäume gleichzeitig beibehalten! Wenn Sie also zu viele Indizes haben, verschlechtert sich die Einfügeleistung!

Zusammenfassen

Allerdings sollte jedem das Prinzip der Indizierung klar sein! Die Details sind vielleicht nicht genau genug, aber ich denke, es reicht aus, damit ein F&E-Mitarbeiter das versteht. Schließlich sind wir keine professionellen Datenbankadministratoren.
Ich hoffe, dass jeder etwas daraus lernen kann!