Zusammenfassung der Wissenspunkte zu MySQL-Index, Sperre und Transaktion

Dieser Artikel fasst die Wissenspunkte zu MySql-Indizes, -Sperren und -Transaktionen zusammen. Teilen Sie uns die Einzelheiten zu Ihrer Information mit:

1. Inhaltsverzeichnis

Ein Index ist vergleichbar mit dem Inhaltsverzeichnis eines Buches und Sie können anhand einer Seitenzahl im Inhaltsverzeichnis sofort den entsprechenden Datensatz finden.

Vorteile der Indizierung:

1. Natürliche Sortierung.
2. Schnelle Suche.

Nachteile der Indizierung:

1. Nimmt Platz ein.
2. Verringern Sie die Geschwindigkeit, mit der Sie die Tabelle aktualisieren.

Hinweis : Bei kleinen Tabellen ist es schneller, einen vollständigen Tabellenscan durchzuführen, bei mittleren und großen Tabellen werden Indizes verwendet. Sehr große Tabellenindizes sind grundsätzlich wirkungslos.

In Bezug auf die Implementierung werden Indizes in zwei Typen unterteilt: Cluster-Indizes und Hilfsindizes (auch Sekundärindizes oder Nicht-Cluster-Indizes genannt).

Funktional gibt es sechs Typen: Normalindex, eindeutiger Index, Primärschlüsselindex, zusammengesetzter Index, Fremdschlüsselindex und Volltextindex.

Lassen Sie uns die 6 Indextypen im Detail besprechen:

1. Normaler Index: Der grundlegendste Index ohne jegliche Einschränkungen.
2. Eindeutiger Index: Ähnlich wie ein normaler Index, jedoch mit einer Eindeutigkeitsbeschränkung.
3. Primärschlüsselindex: Ein spezieller eindeutiger Index, der keine Nullwerte zulässt.
4. Zusammengesetzter Index: Mehrere Spalten werden zu einem Index zusammengefasst, der mehrere Spalten umfassen kann.
5. Fremdschlüsselindex: Nur Tabellen vom Typ InnoDB können Fremdschlüsselindizes verwenden, um Datenkonsistenz und -integrität sicherzustellen und kaskadierende Operationen zu implementieren.
6. Volltextindex: Der mit MySQL gelieferte Volltextindex kann nur für InnoDB und MyISAM verwendet werden und kann nur Volltextsuchen auf Englisch durchführen. Im Allgemeinen wird eine Volltextindex-Engine (ES, Solr) verwendet.

Hinweis : Der Primärschlüssel ist der eindeutige Index, aber der eindeutige Index ist nicht unbedingt der Primärschlüssel. Der eindeutige Index kann leer sein, aber es kann nur einen leeren Wert geben, und der Primärschlüssel kann nicht leer sein.

Darüber hinaus clustert InnoDB Daten nach Primärschlüssel. Wenn kein Primärschlüssel und kein Clustered-Index definiert sind, wählt MySql stattdessen einen eindeutigen, nicht leeren Index aus. Wenn kein solcher Index vorhanden ist, wird implizit ein 6-Byte-Primärschlüssel als Clustered-Index definiert, der von Benutzern weder angezeigt noch aufgerufen werden kann.

Um es einfach auszudrücken:

1. Wenn Sie einen Primärschlüssel festlegen, wird automatisch ein eindeutiger Index generiert. Wenn zuvor kein gruppierter Index vorhanden ist, ist der Primärschlüssel der gruppierte Index.
2. Wenn kein Primärschlüssel festgelegt ist, wird ein nicht leerer eindeutiger Index als gruppierter Index ausgewählt. Wenn keiner vorhanden ist, wird ein impliziter 6-Byte-Index generiert.

MySql speichert Daten in Seiten und die Standardseitengröße beträgt 16 KB. Bei einer Abfrage wird nicht nur ein bestimmtes Datenelement geladen, sondern alle Seiten, auf denen sich die Daten befinden, werden in den Seitencache geladen. Dies ähnelt tatsächlich dem Prinzip des nahen Zugriffs des Betriebssystems.

Der Index von MySql verwendet eine B+-Baumstruktur. Bevor wir über den B+-Baum sprechen, wollen wir zuerst über den B-Baum sprechen. Der B-Baum ist ein mehrseitiger, ausgeglichener Suchbaum. Im Vergleich zu einem gewöhnlichen Binärbaum ist er nicht extrem unausgewogen und auch mehrseitig.

Die Besonderheit von B-Tree besteht darin, dass Daten auch in untergeordneten Knoten gespeichert werden, die keine Seiten sind.

Wie Sie auf dem Bild sehen können:

Diese Funktion bedeutet, dass nicht seitenbezogene untergeordnete Knoten keine große Anzahl von Indizes speichern können.

Der B+-Baum optimiert den B-Baum für diesen Zweck. Wie in der folgenden Abbildung dargestellt:

Wir können sehen, dass der B+-Baum alle Daten in Blattknoten speichert und nicht untergeordnete Knoten nur Indizes und Zeiger speichern.

Wir gehen davon aus, dass ein nicht seitenbezogener untergeordneter Knoten 16 KB groß ist, jeder Index, d. h. der Primärschlüssel, Bigint, also 8b, ist und der Zeiger 8b ist. Dann kann jede Seite etwa 1000 Indizes speichern (16 KB/8 B + 8 B).

Wie viele Indizes kann ein 3-Schicht-B+-Baum speichern? Wie unten dargestellt:

Möglichkeit zur Speicherung von ca. 1 Milliarde Indizes. Normalerweise beträgt die Höhe eines B+-Baums 2-4 Ebenen. Da der Stammknoten bei Ausführung von MySQL im Speicher resident ist, erfordert jede Suche nur etwa 2-3 IOs. Man kann sagen, dass das Design des B+-Baums auf den Eigenschaften mechanischer Festplatten basiert.

Wenn wir den Aufbau des Index kennen, können wir einige weitere Informationen erfahren:

1. Der Primärschlüssel von MySql darf nicht zu groß sein. Wenn Sie UUID verwenden, werden die Nicht-Blattknoten des B+-Baums verschwendet.
2. Der Primärschlüssel von MySql ist vorzugsweise automatisch inkrementell. Wenn UUID verwendet wird, wird der B+-Baum bei jeder Einfügung angepasst, was zu Seitenaufteilungen führt und die Leistung erheblich beeinträchtigt.

Wenn also Sharding im Projekt verwendet wird, benötigen wir normalerweise einen Primärschlüssel für das Sharding. Was also sollen wir tun? In Bezug auf die Implementierung können wir den automatisch inkrementierten Primärschlüssel beibehalten und den logischen Primärschlüssel als eindeutigen Index verwenden.

2. Verriegelungsmechanismus

Wenn es um MySQL-Sperren geht, tauchen verschiedene Konzepte auf. Tatsächlich haben Sperren mehrere Dimensionen. Lassen Sie es uns erklären.

1. Typdimension

• Gemeinsame Sperre (Lesesperre/S-Sperre)
• Exklusive Sperre (Schreibsperre/X-Sperre)

Typenaufschlüsselung:

• Absicht gemeinsame Sperre

• Absichtliche Exklusivität (gegenseitiger Ausschluss) der Sperre

• Pessimistische Sperre (Sperre verwenden, z. B. für Aktualisierung)
• Optimistisches Sperren (unter Verwendung des Versionsnummernfelds, ähnlich dem CAS-Mechanismus, d. h. der Benutzer steuert es selbst. Nachteil: Bei sehr hoher Parallelität gibt es viele nutzlose Wiederholungsversuche)

2. Sperrgranularität (Granularitätsdimension)

• Tabellensperre
• Seitensperre (MySQL BerkeleyDB Engine)
• Zeilensperren (InnoDB)

3. Sperralgorithmus (Algorithmusdimension)

• Datensatzsperre (einzeiliger Datensatz)
• Lückensperre (sperrt einen Bereich, schließt aber den gesperrten Datensatz nicht ein)
• Next-Key Lock (Datensatzsperre + Lückensperre, sperrt einen Bereich und sperrt den Datensatz selbst. MySql verwendet diese Sperre, um Phantom-Lesevorgänge zu verhindern)

4. Ist der Lesevorgang standardmäßig gesperrt?

• Standardmäßig stellt der MVCC-Mechanismus („consistent non-locking read“) die Richtigkeit der Isolierung auf RR-Ebene sicher und ist nicht gesperrt.

Sie können die Sperre manuell wählen: Wählen Sie xxxx zum Aktualisieren (exklusive Sperre); wählen Sie die xxxx-Sperre im Freigabemodus (gemeinsame Sperre), was als „konsistentes Sperrlesen“ bezeichnet wird.

Nach der Verwendung der Sperre können Phantom-Lesevorgänge auf RR-Ebene vermieden werden. Natürlich kann das standardmäßige MVCC-Lesen auch Phantom-Lesevorgänge vermeiden.

Da RR Phantom-Lesevorgänge verhindern kann, was ist der Nutzen von SERIALIZABLE?

Verhindern Sie verlorene Updates. Beispielsweise die folgende Abbildung:

Zu diesem Zeitpunkt müssen wir die Ebene SERIALIZABLE zum seriellen Lesen verwenden.

Schließlich besteht das Implementierungsprinzip der Zeilensperre darin, den Clusterindex zu sperren. Wenn Sie den Index bei der Abfrage nicht richtig treffen, gibt der MySQL-Optimierer die Zeilensperre auf und verwendet die Tabellensperre.

3. Transaktionen

Transaktionen sind ein ewiges Thema in Datenbanken, ACID: Atomarität, Konsistenz, Isolation und Persistenz.

Von den vier Eigenschaften ist Konsistenz die wichtigste. Konsistenz wird durch Atomarität, Isolation und Persistenz gewährleistet.

• Die Atomizität wird durch das Undo-Protokoll gewährleistet. Undo Log speichert die Aufzeichnungen vor jeder Änderung, so dass sie im Fehlerfall rückgängig gemacht werden können.
• Die Isolierung wird durch MVCC und Lock gewährleistet. Ich werde später darauf eingehen.
• Die Haltbarkeit wird durch Redo Log garantiert. Jedes Mal, bevor die Daten tatsächlich geändert werden, wird der Datensatz in das Redo-Protokoll geschrieben. Nur wenn das Redo-Protokoll erfolgreich geschrieben wurde, wird es tatsächlich in den B+-Baum geschrieben. Wenn vor der Übermittlung ein Stromausfall auftritt, kann der Datensatz über das Redo-Protokoll wiederhergestellt werden.

Dann reden wir über Isolation.

Isolationsstufe:

1. Nicht festgeschriebenes Lesen (RU)
2. Lesen bestätigt (RC)
3. Wiederholbares Lesen (RR)
4. serialisierbar

Jede Ebene löst unterschiedliche Probleme, normalerweise drei Probleme: Dirty Reads, nicht wiederholbare Reads und Phantom Reads. Ein klassisches Bild:

Hier ist ein Punkt, der zu Phantom-Lesevorgängen zu beachten ist. In der Datenbankspezifikation verursacht die RR-Ebene Phantom-Lesevorgänge. Aufgrund der MySQL-Optimierung verursacht die RR-Ebene von MySQL jedoch keine Phantom-Lesevorgänge: Bei Verwendung der Standardauswahl verwendet MySQL den MVCC-Mechanismus, um sicherzustellen, dass keine Phantom-Lesevorgänge stattfinden; Sie können auch Sperren verwenden. Bei Verwendung von Sperren, z. B. für Updates (X-Sperre) oder Sperren im Freigabemodus (S-Sperre), verwendet MySQL die Next-Key-Sperre, um sicherzustellen, dass keine Phantom-Lesevorgänge stattfinden. Ersteres wird als Snapshot-Lesen bezeichnet, letzteres als aktuelles Lesen.

Prinzipanalyse:

• Der Grund, warum RU-Dirty-Read auftritt: Das RU-Prinzip besteht darin, den Zeilendatensatz jeder Aktualisierungsanweisung zu sperren, anstatt die gesamte Transaktion zu sperren, sodass ein Dirty-Read auftritt. RC und RR sperren die gesamte Transaktion.
• Der Grund, warum RC nicht wiederholt lesen kann: RC generiert bei jeder Ausführung einer SQL-Anweisung eine neue Lese-Ansicht und was jedes Mal gelesen wird, ist anders. Die RR-Transaktion verwendet von Anfang bis Ende dieselbe Lese-Ansicht.
• Der Grund, warum RR keine Phantom-Lesevorgänge hat, ist wie oben erwähnt.

Was ist also der Unterschied zwischen RR und Serializble? A: Updates verloren. Der Schlossteil dieses Artikels wurde erwähnt.

MVCC-Einführung: Der vollständige Name lautet „Multi-Version Concurrency Control“.

Jeder gruppierte Index von innoDB verfügt über 4 versteckte Felder, nämlich den Primärschlüssel (RowID), die Transaktions-ID der letzten Änderung (MVCC-Kern), den Zeiger des Undo-Protokolls (Isolationskern) und die Index-Löschmarkierung (beim Löschen wird es nicht sofort gelöscht, sondern markiert und dann asynchron gelöscht);

Im Wesentlichen wird MVCC mithilfe der verknüpften Undo-Log-Liste implementiert.

MVCC-Implementierungsmethode: Die Transaktion ändert die Originaldaten in einer exklusiven Sperre, speichert die Daten vor der Änderung im Undo-Protokoll und verknüpft die Daten mit dem Rollback-Zeiger. Wenn die Änderung erfolgreich ist, wird nichts unternommen. Wenn die Änderung fehlschlägt, werden die Daten im Undo-Protokoll wiederhergestellt.

Noch etwas: Normalerweise denken wir, dass MVCC dem optimistischen Sperren ähnelt, also Versionsnummern verwendet, aber tatsächlich ist InnoDB nicht auf diese Weise implementiert. Unsere Nutzung von MySql wird hierdurch selbstverständlich nicht beeinträchtigt.

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.