So erstellen Sie MySQL-Indizes richtig

Die Indizierung ähnelt dem Erstellen bibliografischer Indizes in Universitätsbibliotheken. Sie kann die Effizienz des Datenabrufs verbessern und die IO-Kosten der Datenbank senken. Die MySQL-Leistung beginnt bei etwa 3 Millionen Datensätzen nachzulassen, obwohl in der offiziellen Dokumentation 5-8 Millionen Datensätze angegeben sind. Daher ist es unbedingt erforderlich, Indizes für große Datenmengen zu erstellen. MySQL bietet „Explain“, das zum Anzeigen detaillierter Informationen zur SQL-Ausführung und zum Optimieren von Indizes verwendet wird.

Was ist ein Index?

Die offizielle Definition eines Indexes von MySQL lautet: Ein Index ist eine Datenstruktur, die MySQL dabei hilft, Daten effizient abzurufen. Wir können es einfach so verstehen: eine Datenstruktur, die schnell eine sortierte Reihenfolge findet. Es gibt zwei Hauptstrukturen des MySQL-Index: B+Tree-Index und Hash-Index. Wenn wir von Indizes sprechen, meinen wir, sofern nicht anders angegeben, normalerweise Indizes, die in einer B-Baumstruktur organisiert sind (B+Baumindex). Der Index ist in der Abbildung dargestellt:

Der äußerste hellblaue Festplattenblock 1 enthält die Daten 17, 35 (dunkelblau) und die Zeiger P1, P2, P3 (gelb). Der Zeiger P1 zeigt auf einen Festplattenblock kleiner als 17, P2 liegt zwischen 17 und 35 und P3 zeigt auf einen Festplattenblock größer als 35. Die realen Daten sind in den Unterblattknoten vorhanden, also in der untersten Ebene 3, 5, 9, 10, 13 usw. Nicht-Blattknoten speichern keine realen Daten, sondern nur Datenelemente, die die Suchrichtung vorgeben, z. B. 17, 35.

Suchvorgang: Um beispielsweise nach Datenelement 28 zu suchen, laden Sie zuerst den Festplattenblock 1 in den Speicher, führen Sie einen E/A-Vorgang durch und verwenden Sie eine binäre Suche, um den P2-Zeiger zu bestimmen. Dann wird festgestellt, dass 28 zwischen 26 und 30 liegt, und Festplattenblock 3 wird über die Adresse des P2-Zeigers in den Speicher geladen, und der zweite E/A-Vorgang erfolgt. Festplattenblock 8 wird auf die gleiche Weise gefunden und der dritte I/O erfolgt.

Tatsächlich können die oberen drei Schichten von B+Tree Millionen von Daten darstellen. Millionen von Daten erfordern nur drei I/O-Operationen statt Millionen von I/O-Operationen, und die Zeitersparnis ist enorm.

Datentabellenindizes können die Effizienz des Datenabrufs verbessern und die E/A-Kosten der Datenbank senken. Indizes können auch die Kosten für das Sortieren von Datenbanken senken. Sortier- und Gruppierungsvorgänge verbrauchen hauptsächlich CPU-Ressourcen und Speicher. Wenn Sie also Indizes bei Sortier- und Gruppierungsvorgängen sinnvoll nutzen können, wird der Verbrauch von CPU-Ressourcen erheblich reduziert. Nachfolgend analysieren wir kurz, wie MySQL-Datenindizes richtig erstellt werden.

Wie kann ermittelt werden, ob ein Index erstellt werden muss?

1. Wenn einige Felder häufig als Abfragebedingungen verwendet werden müssen, müssen Sie Indizes für sie erstellen

Das sollte eigentlich jedem bekannt sein, doch was gilt eigentlich als häufig? Analysieren Sie umfassend alle von Ihnen ausgeführten SQL-Anweisungen. Am besten ist es, sie alle einzeln aufzulisten. Nach der Analyse stellten wir fest, dass einige der Felder in den meisten SQL-Abfrageanweisungen verwendet werden. Daher haben wir gezielt Indizes für sie erstellt.

2. Felder mit geringer Eindeutigkeit sind nicht für die Indizierung geeignet

Welche Felder sind nicht eindeutig genug? Wie beispielsweise Statusfeld und Typfeld. Felder, in denen nur wenige feste Werte gespeichert sind, z. B. Benutzeranmeldestatus, Nachrichtenstatus usw. Hierbei handelt es sich um Besonderheiten beim Index-Scanning. Wenn Sie beispielsweise den Index nach Daten mit den Schlüsselwerten A und B durchsuchen und ein übereinstimmendes Datenstück über A finden, das sich auf Seite X befindet, und dann mit dem Scannen fortfahren und Daten finden, die mit A auf Seite Y übereinstimmen, verwirft die Speicher-Engine die Daten auf Seite X und speichert die Daten auf Seite Y, bis alle Daten gefunden sind, die mit A übereinstimmen. Wenn Sie dann nach Feld B suchen und Daten finden, die mit Feld B auf Seite X übereinstimmen, scannt die Speicher-Engine Seite X erneut, was bedeutet, dass Seite X zweimal oder sogar noch öfter gescannt wird. Analog dazu kann dieselbe Datenseite wiederholt gelesen, verworfen und erneut gelesen werden, was die E/A-Belastung der Speicher-Engine zweifellos erheblich erhöht.

3. Zu häufig aktualisierte Felder sind nicht für die Indexerstellung geeignet

Wenn Sie einen Index für ein Feld erstellen und die Felddaten erneut aktualisieren, aktualisiert die Datenbank ihren Index automatisch. Wenn das Feld daher zu häufig aktualisiert wird, wird der Index ständig aktualisiert, und die Auswirkungen auf die Leistung sind vorstellbar. Für die Indizierung sind Felder besser geeignet, die nach Dutzenden von Suchvorgängen nur einmal aktualisiert werden. Wenn ein Feld im selben Zeitraum mehrmals aktualisiert wird, kann es nicht indiziert werden.

4. Felder, die nicht in der Where-Bedingung vorkommen, sollten nicht indexiert werden

Hierzu gibt es eigentlich nicht viel zu sagen. Es ist sinnlos, einen Index für Felder zu erstellen, die nicht als Abfragebedingungen verwendet werden.

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM. Wenn Sie mehr darüber erfahren möchten, schauen Sie sich bitte die folgenden Links an