Fragen zum Vorstellungsgespräch zum MySQL-Datenbankindex (grundlegende Programmierkenntnisse)

Einführung

Die Indizierung stellt bei Mysql ein schwieriges Problem dar, ist aber auch eine sehr wichtige Grundfertigkeit für Programmierer. In der normalen Projektentwicklung ist es ein wichtiges Mittel zur SQL Optimierung. Bei Vorstellungsgesprächen ist es ein wichtiger Aspekt, dass die Interviewer die Leistungsoptimierung ihrer Datenbanken bei Bewerbern häufig prüfen. Daher ist es für jeden Programmierer notwendig, die Prinzipien der Indizierung gründlich zu beherrschen und sie auf tatsächliche Datenbankabfragen anwenden zu können. In diesem Artikel wird Mysql Index unter den Gesichtspunkten der Indexprinzipien und Indexdesignprinzipien erläutert. Ich bin davon überzeugt, dass Sie den Alibaba-Interviewer nach der Lektüre dieses Artikels mit Ihrem Verständnis der Mysql -Indexabfragedaten vollständig überzeugen können. Sind Sie bereit? Wir machten uns auf den Weg.

Indexierungsprinzip

Bevor wir mit dem Entwerfen und Optimieren von Indizes beginnen, sollten wir uns zunächst eingehend mit den Prinzipien von Indizes befassen. Denn alle Entwürfe und Optimierungen müssen auf Ihrem fundierten Verständnis der Prinzipien basieren.

Viele wissen, dass bei der Ausführung von SQL Abfragen dieselben Daten auf dieselbe Tabelle treffen. Daten ohne oder mit Index abfragen. Es besteht ein großer Unterschied zwischen den beiden. Warum also gibt es diese Lücke? Einfach ausgedrückt: Wenn die Geschäftsdaten mit einem Wörterbuch verglichen werden, dann ist der Index das Verzeichnis dieses Wörterbuchs. Wenn ich Sie auffordere, ein Wort nachzuschlagen, und Sie das Verzeichnis nicht zum Nachschlagen verwenden, können Sie die Seiten nur einzeln durchblättern. Wenn Sie Pech haben, müssen Sie möglicherweise bis zur letzten Seite blättern, um das gewünschte Wort zu finden. Dies ist der legendäre vollständige Tabellenscan. Wenn wir jedoch das Verzeichnis durchsuchen, können wir schnell die Seite finden, auf der sich das Wort befindet, und dann das entsprechende Wort finden. Die Stärke von Indizes liegt in der Verbesserung der Effizienz von Datenabfragen. OK, jetzt haben wir ein perzeptuelles Verständnis von Indizes. Schauen wir uns das Ganze also genauer an.

Wir alle wissen, dass die Datenstruktur des Index in Mysql B+ -Baum ist (die Vor- und Nachteile von Strukturen wie B Baum und Hash Index werden hier nicht erläutert, da dies nicht der Schwerpunkt dieses Artikels ist). Sehen wir uns daher an, wie B+ -Baum des Index auf der Festplatte Schritt für Schritt wächst.

1. Datenseite

Bei der täglichen Projektentwicklung liegen die meisten unserer Geschäftsdaten in relationalen Daten vor. Anschließend werden die Daten jeder Tabelle in der Datenbank schließlich auf der Festplatte des Servers gespeichert. Ich frage mich, ob Sie jemals darüber nachgedacht haben, wie diese Daten gespeichert werden? Tatsächlich sind die Datenbanktabellen, die wir täglich in der Mysql Datenbank verwenden, logische Tabellen, die für Menschen verständlich sind. Tatsächlich werden sie als Datenseiten auf der Festplatte gespeichert. Datenseiten stellen die grundlegende Einheit der Interaktion zwischen Festplatte und Speicher dar. Die Innodb Speicher-Engine von Mysql interagiert tatsächlich über buffer pool mit den Datenseiten auf der Festplatte, anstatt die Datenseiten auf der Festplatte direkt zu bearbeiten. Der Aufbau der Datenseite ist in der folgenden Abbildung dargestellt:

Gleichzeitig werden benachbarte Datenseiten über eine bidirektional verknüpfte Liste aufeinander referenziert. Wie in der folgenden Abbildung gezeigt, ist der orangerote Teil die Datenseite und das kleine Kästchen in der Mitte kann als spezifische Daten verstanden werden. Die Datenseitengröße der InnoDB Speicher-Engine von Mysql beträgt 16KB . Innodb Speicher-Engine von Mysql lokalisiert eine Datenseite eindeutig über die Seitennummer, sodass jede Datenseite ihre eigene Seitennummer hat. Wie aus der obigen Abbildung ersichtlich, verfügt jede Datenseite Page Header Page Header der die Seitennummer der aktuellen Datenseite, die Seitennummer der nächsten Seite und die Seitennummer der vorherigen Seite speichert.

Benachbarte Daten verweisen durch Zeiger aufeinander. Die Zeiger markieren die Seitenzahl der Datenseite. Jede Datenseite speichert ein zusammenhängendes Datensegment. Der Datensatzkopf in jeder Datenzeile speichert den Adressoffset der nächsten Zeile mit realen Daten. Einfach ausgedrückt kann man das so verstehen, dass ein Zeiger auf die Adresse der nächsten Datenzeile zeigt. Daher gibt es innerhalb der Datenseite tatsächlich eine unidirektional verknüpfte Liste der Datenzeilen. Diese einseitig verknüpfte Liste bezieht sich auf die Primärschlüssel id und ist von klein nach groß geordnet.

Aus der obigen Datenseitenstruktur können wir erkennen, dass bei jeder Dateneinfügung der Bereich User Records größer wird und der entsprechende Bereich User Record kleiner wird. Wenn der User Record erschöpft ist, wird eine Seitenteilung durchgeführt, um eine neue Datenseite zu erstellen. Es ist hier zu beachten, dass wir, wenn wir den automatisch inkrementierten Primärschlüssel in Mysql verwenden, sicherstellen können, dass die Datenzeilen in der Reihenfolge der aufsteigenden id angeordnet sind. Wenn der Primärschlüssel jedoch von uns selbst festgelegt wird und nicht automatisch inkrementiert wird, ist es möglich, dass der Primärschlüsselwert der später eingefügten Daten kleiner ist als der Primärschlüsselwert der vorherigen Daten. In diesem Fall wird Mysql die Seite beim Aufteilen entsprechend der Primärschlüsselgröße neu anordnen. Ich frage mich, falls Sie hier Fragen haben: Warum müssen wir sie nach der Größe des Primärschlüssels anordnen? Tatsächlich hängt es mit nachfolgenden Datenabfragen zusammen. Das Anordnen der Daten auf der Datenseite in der Reihenfolge des Primärschlüssels ist die Grundlage für den normalen Betrieb des Index. Der allgemeine Ablauf ist in der folgenden Abbildung dargestellt:

2. Seitenverzeichnis

Jede Datenseite hat ihr eigenes Seitenverzeichnis. Page Directory in der obigen Seitenstruktur wird tatsächlich zum Auffinden von Datenzeilen verwendet. Die Daten auf der Datenseite werden tatsächlich nach Gruppen zugewiesen. Unterschiedliche Slots im Seitenverzeichnis entsprechen tatsächlich unterschiedlichen Gruppen auf der Datenseite. Suchen Sie beim Abfragen von Daten den entsprechenden Slot über id , ermitteln Sie dann anhand des entsprechenden Slots die entsprechende Datenzeilengruppe auf der Datenseite und durchlaufen Sie die Daten in der Datenzeilengruppe, bis die entsprechenden Daten gefunden werden.

3. Indexprinzipanalyse

(1) Grundlagen der Indizierung

Mit den Grundkenntnissen zu Datenseiten in den beiden obigen Abschnitten wird es einfacher sein, das Indexprinzip zu verstehen. Wenn kein Index vorhanden ist, werden alle Datenabfragen durch einen vollständigen Tabellenscan durchgeführt. Durchlaufen Sie jede Datenzeile auf der Abfragedatenseite und anschließend alle Datenseiten, bis ein Datenelement gefunden wird, das die Bedingungen erfüllt. Daher ist die Abfrageeffizienz sehr gering. Wie können wir also die Effizienz der Datenabfrage verbessern? Ist es möglich, ein Primärschlüsselverzeichnis wie ein Wörterbuchverzeichnis zu haben, um die Datenseitennummer zu finden? Die Antwort ist ja, und genau das macht Mysql . Mysql verwendet das Primärschlüsselverzeichnis, das eigentlich der legendäre Primärschlüsselindex ist, um Datenabfragen zu optimieren. Das Primärschlüsselverzeichnis enthält zwei wichtige Elemente: eines ist der kleinste Primärschlüssel auf der Datenseite und das andere ist die Seitennummer der aktuellen Datenseite. Auf diese Weise können Daten über dieses Primärschlüsselverzeichnis abgefragt werden.

Wenn Sie beispielsweise die Daten mit der Primärschlüssel id=5 abfragen möchten, suchen Sie zuerst im Primärschlüsselverzeichnis. Zu diesem Zeitpunkt wird festgestellt, dass die Primärschlüssel- id=5 größer als die Primärschlüssel id=1 , aber kleiner als id=8 ist. Daher kann festgestellt werden, dass sich die Daten tatsächlich auf der Datenseite mit der Seitennummer 1 befinden.

Natürlich gibt es in Mysql in Wirklichkeit viele Datenseiten, also auch viele entsprechende Primärschlüsselindizes. In diesem Fall müssen Sie die Datenseite durch binäre Suche lokalisieren und dann die entsprechenden Daten finden.

(2) Indexseite

Heutzutage entwickeln sich verschiedene Internetunternehmen rasant und das entsprechende Geschäftsvolumen ist ebenfalls riesig. Daher ist auch die Datenmenge in der Datenbank sehr groß. Es ist üblich, dass eine Tabelle Millionen oder Zehnmillionen von Daten enthält. Gemäß dem obigen Primärschlüsselverzeichnis müssen eine große Anzahl von Primärschlüsseln und Datenseitennummern gespeichert werden. Selbst wenn eine binäre Suche durchgeführt wird, ist die Effizienz der Datenabfrage relativ gering.

Mysql speichert Indexanweisungen tatsächlich auf Indexseiten. Wenn die Datenmenge groß ist, gibt es mehr entsprechende Indizes, sodass spezielle Indexseiten zum Speichern von Indexdaten verwendet werden. Darüber hinaus fragt die obere Ebene dieser Indexseiten die Indexseiten weiterhin ab und lokalisiert sie über den Primärschlüssel und die Indexseitennummer, sodass wir die folgende Struktur erhalten. id Identnummer handelt es sich hierbei um die jeweils kleinste id .

Wenn die Datenmenge auf der Indexseite zunimmt, wird auch die Indexseite aufgeteilt. Auf diese Weise bilden die Indexseiten unterschiedliche Ebenen, und die drei Seitendaten der Indexseitenebene, der Indexseite und der Datenseite bilden das, was wir als B+ -Baum bezeichnen. Die folgende Abbildung zeigt die B+ -Baumstruktur des Index, die eine viel effizientere Durchführung von Datenabfragen ermöglicht als ein vollständiger Tabellenscan. Nur Blattknoten von B+ speichern Daten. Die folgende Abbildung ist ein Primärschlüsselindex, auch Clusterindex genannt. Tatsächlich ist die Grundidee „Teile und herrsche“ erkennbar. Die Datenmenge ist riesig, oder? Also werde ich die Daten auf viele Datenseiten aufteilen. Es gibt viele Datenseiten, oder? Also werde ich die Datenseiten über Indexseiten organisieren. Es gibt viele Indexseiten, oder? Also werde ich sie über Indexseiten indizieren.

Werfen wir einen Blick auf den Datenabfrageprozess im B+ -Baum. Wenn Sie beispielsweise Daten mit der ID 3 abfragen müssen, legen Sie auf der Indexseite fest, dass Sie zur Indexseite 3 gehen sollen. Dann bestimmen wir auf Indexseite 3 weiter, id=1 zu Indexseite 1 gehen soll, und auf der Indexseite bestimmen wir, dass es die Datenseite mit der Seitennummer 1 sein soll. Wir durchlaufen diese Datenseite und finden schließlich die entsprechenden Daten.

Der obige B+ -Baum, der aus Indexseiten und Datenseiten besteht, ist ein gruppierter Index. Natürlich können wir auch normale Indizes über andere Felder erstellen. Die Blattknoten gewöhnlicher Indizes speichern die entsprechende Primärschlüssel id anstelle spezifischer Daten. Der Index weist das Problem des Tabellen-Backtrackings auf, d. h. nach der Abfrage der entsprechenden id müssen die spezifischen Daten im gruppierten Index basierend auf id weiter abgefragt werden. Nur durch solche Vorgänge können alle Daten von select * abgefragt werden. Natürlich können wir eine solche Abfrageverschwendung vermeiden, indem wir abdeckende Indizes verwenden.

Zusammenfassen

Dieser Artikel verwendet Schritt-für-Schritt-Diagramme, um das Indizierungsprinzip von Mysql InnoDB zu erklären und die entsprechende B+ -Baumindexstruktur aufzubauen. Der konkrete Ablauf der Datenabfrage wird erläutert. Ich glaube, dass jeder ein tieferes Verständnis von Indizes hat. Später werde ich aus praktischer Sicht analysieren, wie Indizes entworfen werden und wie mit Indexfehlern umgegangen wird.