Erweiterte MySQL-Funktionen - detaillierte Erläuterung des Konzepts und des Mechanismus der Datentabellenpartitionierung

Die MySQL-Partitionierung wird durch Umschließen der Datentabelle implementiert. Dies bedeutet, dass der Index tatsächlich auf Grundlage jeder einzelnen Partition und nicht der gesamten Tabelle definiert wird. Diese Funktion unterscheidet sich von Oracle, wo Indizes und Tabellen flexibler und komplexer partitioniert werden können.​

Die MySQL-Partitionierung bestimmt die Partition, zu der eine Datenzeile gehört, indem die Bedingungen der PATITION BY-Klausel definiert werden. Bei der Ausführung einer Abfrage unterscheidet der Abfrageoptimierer zwischen Partitionen, d. h. die Abfrage überprüft nicht alle Partitionen, sondern nur die Partitionen, die die angeforderten Daten enthalten.​

Der Hauptzweck der Partitionierung besteht darin, die Datentabelle grob zu indizieren und zu clustern. Dadurch muss möglicherweise nicht mehr auf eine große Anzahl von Datentabellen zugegriffen werden, und zusammengehörende Datenzeilen müssen nicht mehr nah beieinander gespeichert werden. Die Vorteile der Partitionierung sind erheblich, insbesondere in den folgenden Szenarien:

• Wenn die Datentabelle zu groß ist, um in den Speicher zu passen, oder wenn eine Datentabelle viele historische Daten und Hot-Zone-Zeilen enthält.
• Partitionierte Daten sind einfacher zu verwalten als unpartitionierte Daten. So lassen sich beispielsweise alte Daten durch das Löschen der gesamten Partition problemlos beseitigen, und auch einzelne Partitionen lassen sich einfach optimieren, prüfen und reparieren.
• Durch die Partitionierung können Daten physisch auf dem Speicher verteilt werden, wodurch der Server mehrere Festplatten effizienter nutzen kann.
• Durch Partitionierung können Sie Engpässe bei bestimmten Arbeitslasten vermeiden.
• Zur Datensicherung können einzelne Partitionen einzeln gesichert bzw. wiederhergestellt werden, was bei großen Datenmengen sehr vorteilhaft ist.
  

Die Implementierungsdetails der MySQL-Partitionierung sind sehr komplex und schwer zu verstehen. Wir müssen uns nur auf die Leistung konzentrieren. Wenn Sie mehr erfahren möchten, können Sie den Abschnitt zur Partitionierung im MySQL-Handbuch lesen. Mit der Partitionierung gehen auch andere Probleme und Einschränkungen einher:

• Die Befehle zum Erstellen und Ändern von Tabellen sind komplexer.
• Jede Tabelle kann maximal 1024 Partitionen haben.
• In MySQL 5.1 muss der Partitionierungsausdruck eine Ganzzahl sein oder eine Ganzzahl zurückgeben; in MySQL 5.5 und höher können in einigen Fällen Spalten zur Partitionierung verwendet werden.
• Jeder Primärschlüssel oder eindeutige Index muss alle Spalten im Partitionierungsausdruck enthalten.
• Fremdschlüsseleinschränkungen können nicht verwendet werden.
  

Partitionierungsmechanismus

Wie bereits erwähnt, verfügt die Partitionstabelle tatsächlich über mehrere versteckte physische Speichertabellen, die durch Handle-Objekte dargestellt werden. Wir können nicht direkt auf die Partitionen zugreifen. Normalerweise wird jede Partition von einer Speicher-Engine verwaltet (daher ist für alle Partitionen dieselbe Speicher-Engine erforderlich) und der Index in der Datentabelle ist tatsächlich ein Index der verborgenen physischen Speichertabelle. Aus Sicht der Speicher-Engine sind Partitionen auch Datentabellen. Die Speicher-Engine weiß nicht wirklich, ob die Datentabelle unabhängig ist oder eine Partition einer größeren Datentabelle. Die Operationen an der Partitionstabelle werden durch die folgenden logischen Operationen implementiert:

SELECT Abfrage

Beim Abfragen einer partitionierten Tabelle öffnet und sperrt die Partitionierungsebene alle versteckten Partitionen, der Abfrageoptimierer bestimmt, welche versteckten Partitionen ignoriert werden können, und dann ruft die Partitionierungsebene die Speicher-Engine auf, die die Partitionen über die Handle-API verwaltet, um die Abfrageergebnisse zu erhalten.

INSERT-Operation

Wenn eine Datenzeile eingefügt wird, öffnet und sperrt die Partitionsebene alle Partitionen, bestimmt dann, welche Partition die aktuelle Datenzeile speichert, und speichert die Datenzeile in der entsprechenden Partition.

DELETE-Vorgang

Beim Löschen einer Datenzeile öffnet und sperrt die Partitionierungsebene alle Partitionen, prüft, welche Partition die Datenzeile enthält, und sendet dann die Löschanforderung an diese Partition.

UPDATE-Vorgang

Beim Ändern einer Datenzeile öffnet und sperrt die Partitionierungsebene alle Partitionen, prüft, welche Partition die Datenzeile enthält, ruft die Datenzeile zur Änderung ab und bestimmt dann, welche Partition die neue Datenzeile enthalten soll, sendet die Einfügeanforderung an die Partition und die Löschanforderung an die alte Partition.
Einige der oben genannten Vorgänge unterstützen die Partitionsfilterung (d. h. das Ignorieren irrelevanter Partitionen). Wenn Sie beispielsweise eine Zeile löschen, muss der Server zuerst die Datenzeile finden. Wenn in der WHERE-Bedingung eine passende Partitionierungsausdrucksbedingung angegeben ist, kann der Server Partitionen ignorieren, die die Zeile nicht enthalten. Dasselbe gilt für UPDATE-Operationen und das Gleiche gilt für INSERT-Operationen. Der Server sucht nur nach einer einzufügenden Partition, nicht nach allen.

Obwohl die Partitionsebene alle Partitionen öffnet und sperrt, bedeutet das nicht, dass die Partitionen gesperrt bleiben. Speicher-Engines wie InnoDB unterstützen Sperren auf Zeilenebene und entsperren Partitionen nur auf Partitionsebene. Der Sperr- und Entsperrvorgang ähnelt dem Sperrvorgang gewöhnlicher InnoDB-Datentabellen.

Arten von Partitionen

MySQL unterstützt mehrere Arten der Partitionierung. Die am häufigsten verwendete Art ist die Bereichspartitionierung, also die Partitionierung nach verschiedenen Wertebereichen oder Ausdrücken bestimmter Spalten. Die folgende Anweisung unterteilt beispielsweise die Verkaufsdaten nach Jahr in verschiedene Partitionen:

CREATE TABLE Verkäufe (
  Bestelldatum DATETIME NICHT NULL
  --Andere Spaltendefinitionen) ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) (
  PARTITION p_2018 WERTE WENIGER ALS (2018),
  PARTITION p_2019 WERTE WENIGER ALS (2019),
  PARTITION p_2020 WERTE WENIGER ALS (2020),
  PARTITION p_other WERTE KLEINER ALS MAXIMALER WERT);

In der Teilungsklausel können unterschiedliche Funktionen verwendet werden. Die Hauptanforderung besteht darin, dass eine nicht-konstante, deterministische Ganzzahl zurückgegeben werden muss. Im obigen Beispiel wird die Funktion YEAR verwendet, es können jedoch auch andere Funktionen verwendet werden, z. B. TO_DAYS(). Die Verwendung von Zeitintervallen zur Partitionierung ist ein gängiger Ansatz für datumsbasierte Daten.​

MySQL unterstützt auch Methoden zur Schlüssel-, Hash- und Listenpartitionierung und einige unterstützen auch die Unterpartitionierung (die in der Praxis selten verwendet wird). In MySQL 5.5 und höher können Sie den Partitionstyp RANGE COLUMNS verwenden, um direkt nach datumsbasierten Spalten zu partitionieren, ohne Funktionen zum Konvertieren von Datumsangaben in Ganzzahlen zu verwenden. Zu den weiteren gängigen Partitionierungstechniken gehören:

• Verwenden Sie Schlüssel zur Partitionierung, um InnoDB-Mutex-Konflikte zu reduzieren.
• Mit der Modulo-Berechnungsmethode können Sie die Bereichspartitionen durchlaufen. Wenn Sie beispielsweise nur die Daten der letzten Tage aufbewahren müssen, können Sie partitionieren, indem Sie für das Datum oder für den Wochentag Modulo 7 verwenden.
• Angenommen, die Datentabelle verfügt über keinen automatisch inkrementierenden Primärschlüssel, Sie möchten aber auch die zusammengefassten Hot-Zone-Daten partitionieren. Da der Zeitstempel nicht im Primärschlüssel enthalten ist, kann keine Zeitstempelpartitionierung verwendet werden. Derzeit können Sie HASH (id DIV 1000000) verwenden, wodurch alle 1.000.000 Datenzeilen partitioniert werden. Dadurch können wir den gewünschten Effekt erzielen, ohne den Primärschlüssel zu ändern. Dies hat auch zusätzliche Auswirkungen. Das heißt, wir müssen keine Partitionskonstanten erstellen, um neue Daten zu speichern.
  

Oben finden Sie ausführliche Informationen zum Konzept und Mechanismus der erweiterten MySQL-Funktion - Datentabellenpartitionierung. Weitere Informationen zur erweiterten MySQL-Funktion Datentabellenpartitionierung finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!