MySql-Wissenspunkte: Transaktion, Index, Sperrprinzip und Nutzungsanalyse

Dieser Artikel erläutert anhand von Beispielen die Prinzipien und die Verwendung von MySQL-Wissenspunkten wie Transaktionen, Indizes und Sperren. Teilen Sie uns die Einzelheiten zu Ihrer Information mit:

Transaktionen

• Transaktionskonzept

Eine Transaktion ist eine Reihe atomarer SQL-Abfragen oder eine unabhängige Arbeitseinheit. Wenn die Datenbank-Engine eine Reihe von Operationsanweisungen ausführt, werden alle Operationen ausgeführt. Wenn eine davon abstürzt oder aus anderen Gründen nicht ausgeführt werden kann, werden nicht alle Anweisungen ausgeführt. Das heißt, alle Anweisungen in einer Transaktion werden entweder erfolgreich ausgeführt oder alle sind fehlgeschlagen.

• ACID-Transaktionsmerkmale
  • Atomarität

  Eine Transaktion wird als kleinste Arbeitseinheit betrachtet und kann nicht aufgeteilt werden. Alle Vorgänge in der gesamten Transaktion müssen entweder erfolgreich festgeschrieben werden oder alle schlagen fehl und werden zurückgesetzt. Nur ein Teil der Vorgänge kann nicht ausgeführt werden.

  • Konsistenz

  Die Datenbank wird von einem konsistenten Zustand in einen anderen konsistenten Zustand überführt. Ein Zustand, in dem eine Datenbank alle Integritätsbeschränkungen erfüllt.

  • Isolierung

  Im Allgemeinen sind von einer Transaktion vorgenommene Änderungen für andere Transaktionen erst sichtbar, wenn sie endgültig festgeschrieben wurden. Zu diesem Zeitpunkt sollte sichergestellt werden, dass jede Transaktion isoliert ist und sich nicht gegenseitig stören kann.

  • Haltbarkeit

  Sobald eine Transaktion bestätigt wurde, werden alle Änderungen dauerhaft in der Datenbank gespeichert. Selbst wenn das System abstürzt, gehen die geänderten Daten nicht verloren.

• Transaktionsisolationsebene
  • LESEN SIE UNVERBINDLICH

  Änderungen in einer Transaktion sind für andere Transaktionen sichtbar, auch wenn sie nicht festgeschrieben sind. Transaktionen können nicht festgeschriebene Daten lesen, was zu fehlerhaften Lesevorgängen und Nichtwiederholbarkeit führt.

  • LESEN SIE ENGAGIERT

  Die Standardebene der meisten Datenbanken ist READ COMMITTED (MySQL verwendet standardmäßig REPEATABLE READ). Transaktionen auf dieser Ebene lösen Dirty Reads, es können jedoch nicht wiederholbare Lesevorgänge auftreten, da die Abfrageergebnisse unterschiedlich sind, wenn dieselbe Abfrage zweimal ausgeführt wird.

  • WIEDERHOLBARES LESEN

  Diese Ebene löst Dirty Reads und stellt wiederholbare Lesevorgänge sicher. Theoretisch kann die Isolationsebene für wiederholbare Lesevorgänge Phantom-Lesevorgänge jedoch immer noch nicht lösen. Der sogenannte Phantom-Lesevorgang bezieht sich auf die Situation, in der eine bestimmte Transaktion Datensätze innerhalb eines bestimmten Bereichs liest und eine andere Transaktion neue Datensätze innerhalb desselben Bereichs einfügt. Die Speicher-Engines InnoDB und XtraDB lösen das Problem von Phantom-Lesevorgängen durch Multi-Version Concurrency Control (MVVC).

  • SERIALISIERBAR

  Serialisierbarkeit ist die höchste Isolationsstufe. Sie erzwingt die serielle Ausführung von Transaktionen und vermeidet Phantom-Lesevorgänge vollständig. Kurz gesagt: SERIALIZABLE sperrt jeden Zeilenlesevorgang, was zu einer großen Anzahl von Wartezeitüberschreitungen und Sperrkonflikten führt. In der tatsächlichen Entwicklung wird sie selten verwendet.

Index

• Indexkonzept

Ein Index ist eine Datenstruktur, die es Benutzern von Speichermaschinen ermöglicht, Datensätze schnell zu finden. Zum Beispiel

SELECT Benutzername FROM Benutzer WHERE Benutzer-ID = 1;

Wenn Sie der Spalte userId einen Index hinzufügen, verwendet MySQL den Index, um die Zeile für userId zu finden. Das heißt, MySQL durchsucht den Index zuerst nach Wert und gibt dann alle Datenzeilen zurück, die den Wert enthalten.

• Indizierung
  • B-Baum-Index

  Verwenden Sie zum Speichern von Daten die B-Tree-Datenstruktur. Die meisten MySQL-Engines unterstützen diesen Index. B-Tree-Indizes können den Datenzugriff beschleunigen, da B-Tree-Indexspalten sequenziell gespeichert werden, was schnelle Bereichssuchen ermöglicht.

  • Hash-Index

  Der Hash-Index wird als einfache Hash-Tabelle implementiert. Nur Abfragen, die genau mit allen Spalten des Index übereinstimmen, sind gültig. Für jede Datenzeile berechnet die Speicher-Engine einen Hashcode für alle Indexspalten, und der Hashcodewert ist kleiner. Ein Hash-Index speichert alle Hash-Codes im Index und speichert einen Zeiger auf jede Datenzeile in der Hash-Tabelle. In MySQL unterstützt nur die Speicher-Engine explizit Hash-Indizes.

• Indextyp
  • Normaler Index

  Hauptaufgaben Beschleunigen Sie den Zugriff auf Daten

  • Eindeutiger Index

  Gewöhnliche Indizes erlauben die Wiederholung von Daten. Wenn Sie sicher sind, dass die Daten in einer Spalte nicht wiederholt werden, können Sie einen eindeutigen Index erstellen. Ein eindeutiger Index hat zwei Vorteile: Der Index ist effizienter: Wenn beim Einfügen neuer Daten diese wiederholt werden, lehnt MySQL deren Einfügen ab.

  • Primärschlüsselindex

  Der Primärschlüssel selbst wird standardmäßig indiziert.

  • Volltextindex

  Ein normaler Index für ein Textfeld kann nur die Suche nach der ersten Zeichenfolge im Feld beschleunigen. Wenn das Feld einen großen Text enthält, der aus mehreren oder mehr Wörtern besteht, funktioniert ein normaler Index nicht. In diesem Fall ist ein Volltextindex besser geeignet.

  Abfrageeffizienz: eindeutiger Index > automatisch inkrementierter Primärschlüssel > Primärschlüssel

  Einfügen: Primärschlüssel > automatisch inkrementierter Primärschlüssel > eindeutiger Index

Sperren

Hier diskutieren wir hauptsächlich Downlink-Level-Locks

• Tabellenebene

Die MyISAM-Engine kann so verstanden werden, dass sie die gesamte Tabelle sperrt. Sie kann gleichzeitig gelesen, aber nicht gleichzeitig geschrieben werden. Während der Sperrzeit können andere Prozesse nicht in die Tabelle schreiben. Handelt es sich um eine Schreibsperre, dürfen andere Prozesse nicht lesen.

• Zeilenebene

Bei der INNODB-Engine ist eine einzelne Datensatzzeile gesperrt und kann gleichzeitig gelesen, aber nicht gleichzeitig geschrieben werden. Sperren auf Zeilenebene weisen einen hohen Overhead und eine langsame Sperrung auf. Es können Deadlocks auftreten. Die Sperrgranularität ist am geringsten, die Wahrscheinlichkeit von Sperrkonflikten am geringsten und die Parallelität am höchsten.

• InnoDB-Sperrzeilen

Da InnoDB standardmäßig eine Zeilensperre verwendet, führt MySQL eine Zeilensperre nur aus, wenn der Primärschlüssel explizit angegeben ist. Andernfalls führt MySQL eine Tabellensperre aus.

Beispiel 1: (Geben Sie den Primärschlüssel explizit an, und es gibt einen solchen Datensatz, Zeilensperre)

Wählen Sie * aus Produkten, wobei id = "3" für Update ist.
Wählen Sie * aus Produkten, wobei ID = "3" und Typ = 1 für Update ist.

Beispiel 2: (Geben Sie den Primärschlüssel explizit an. Wenn kein solcher Datensatz gefunden wird, wird keine Sperre ausgeführt.)

Wählen Sie * aus Produkten, wobei id = '-1' für Update ist.

Beispiel 3: (kein Primärschlüssel, Tabellensperre)

SELECT * FROM Produkte WHERE Name='Maus' FOR UPDATE;

Beispiel 4: (Unklarer Primärschlüssel, Tabellensperre)

Wählen Sie * aus Produkten, wobei ID<>'3' für Update ist.

Beispiel 5: (Primärschlüssel ist unklar, Tabellensperre)

Wählen Sie * aus Produkten, wobei die ID wie „3“ für das Update lautet.

Hinweis 1: FOR UPDATE ist nur auf InnoDB anwendbar und muss in einem Transaktionsblock (BEGIN/COMMIT) ausgeführt werden, um wirksam zu werden.
Hinweis 2: Um den Sperrstatus zu testen, können Sie den Befehlsmodus von MySQL verwenden und zum Test zwei Fenster öffnen.

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.