Best Practices-Handbuch für partitionierte MySQL-Tabellen

Vorwort:

Partitionierung ist ein Tabellenentwurfsmuster. Im Allgemeinen besteht die Tabellenpartitionierung darin, eine große Tabelle basierend auf Bedingungen in mehrere kleine Tabellen aufzuteilen. Für die Anwendung ist die partitionierte Tabelle jedoch dasselbe wie die nicht partitionierte Tabelle. Mit anderen Worten: Die Partitionierung ist für die Anwendung transparent und stellt lediglich eine Neuorganisation der Daten durch die Datenbank dar. Dieser Artikel stellt Partitionstabellen in MySQL und ihre Verwendungsszenarien vor. Freunde in Not können ihn zu Rate ziehen. Ich hoffe, er wird Ihnen hilfreich sein.

1. Zweck und Art der Trennwand

Beim Erstellen einer Tabelle in MySQL können Sie die Klausel PARTITION BY verwenden, um die in jeder Partition gespeicherten Daten zu definieren. Beim Ausführen einer Abfrage filtert der Optimierer Partitionen heraus, die basierend auf der Partitionsdefinition nicht die von uns benötigten Daten enthalten, sodass die Abfrage nicht alle Partitionen scannen muss, sondern nur die Partition finden muss, die die erforderlichen Daten enthält.

Ein weiterer Zweck der Partitionierung besteht darin, Daten mit gröberer Granularität in verschiedenen Tabellen zu speichern. Auf diese Weise können zusammengehörige Daten zusammen gespeichert werden. Darüber hinaus ist es praktisch, wenn wir die Daten einer ganzen Partition stapelweise auf einmal löschen möchten.

Im Folgenden werden vier gängige Partitionstypen kurz vorgestellt:

• RANGE-Partitionierung: Die am häufigsten verwendete Methode ist die Zuweisung mehrerer Zeilen zu Partitionen basierend auf Spaltenwerten, die zu einem bestimmten kontinuierlichen Intervall gehören. Am gebräuchlichsten ist es auf der Grundlage des Zeitfelds.
• LIST-Partition: Die LIST-Partition ähnelt der RANGE-Partition. Der Unterschied besteht darin, dass LIST eine Sammlung von Aufzählungswertlisten und RANGE eine Sammlung von kontinuierlichen Intervallwerten ist.
• HASH-Partitionierung: Die Auswahl einer Partition erfolgt anhand des Rückgabewerts eines benutzerdefinierten Ausdrucks, der aus den Spaltenwerten der in die Tabelle einzufügenden Zeilen berechnet wird. Diese Funktion kann jeden in MySQL gültigen Ausdruck enthalten, der einen nicht negativen ganzzahligen Wert erzeugt.
• KEY-Partitionierung: Ähnlich wie HASH-Partitionierung, der Unterschied besteht darin, dass die KEY-Partitionierung nur die Berechnung einer oder mehrerer Spalten unterstützt und der MySQL-Server eine eigene Hash-Funktion bereitstellt. Eine oder mehrere Spalten müssen ganzzahlige Werte enthalten.
  

Unter den oben genannten vier Partitionstypen wird die RANGE-Partitionierung am häufigsten verwendet. Das Merkmal der RANGE-Partitionierung besteht darin, dass die Bereiche mehrerer Partitionen zusammenhängend sein müssen, sich aber nicht überschneiden dürfen. Standardmäßig wird das Attribut VALUES LESS THAN verwendet, d. h. jede Partition enthält nicht den angegebenen Wert.

2. Beispiel für eine Partitionierungsoperation

In diesem Abschnitt wird die RANGE-Partitionierung als Beispiel verwendet, um Vorgänge im Zusammenhang mit Partitionstabellen vorzustellen.

# Erstellen Sie eine partitionierte Tabellemysql> CREATE TABLE `tr` (
 -> `id` INT, 
 -> `Name` VARCHAR(50), 
 -> `gekauft` DATUM
 -> ) ENGINE=InnoDB STANDARD-CHARSET=utf8
 -> PARTITION NACH BEREICH (JAHR (gekauft)) (
 -> PARTITION p0 WERTE WENIGER ALS (1990),
 -> PARTITION p1 WERTE WENIGER ALS (1995),
 -> PARTITION p2 WERTE WENIGER ALS (2000),
 -> PARTITION p3 WERTE WENIGER ALS (2005),
 -> PARTITION p4 WERTE WENIGER ALS (2010),
 -> PARTITION p5 WERTE WENIGER ALS (2015)
 -> );
Abfrage OK, 0 Zeilen betroffen (0,28 Sek.)

# Datamysql einfügen> INSERT INTO `tr` VALUES
 -> (1, 'Schreibtisch-Organizer', '2003-10-15'),
 -> (2, 'Wecker', '1997-11-05'),
 -> (3, 'Vorsitzender', '10.03.2009'),
 -> (4, 'Bücherregal', '1989-01-10'),
 -> (5, 'Heimtrainer', '2014-05-09'),
 -> (6, 'Sofa', '1987-06-05'),
 -> (7, 'Espressomaschine', '22.11.2011'),
 -> (8, 'Aquarium', '1992-08-04'),
 -> (9, 'Schreibtisch', '2006-09-16'),
 -> (10, 'Lavalampe', '1998-12-25');
Abfrage OK, 10 Zeilen betroffen (0,03 Sek.)
Datensätze: 10 Duplikate: 0 Warnungen: 0

Nach der Erstellung können Sie sehen, dass jede Partition einer IBD-Datei entspricht. Die obige Erstellungsanweisung ist leicht zu verstehen. In dieser Partitionstabelle wird das Jahr im Datum DATE mithilfe der Funktion YEAR extrahiert und in eine Ganzzahl umgewandelt. Jahre vor 1990 werden in Partition p0 gespeichert, Jahre vor 1995 in Partition p1 und so weiter. Beachten Sie, dass jede Partition in der Reihenfolge vom niedrigsten bis zum höchsten definiert ist. Um zu verhindern, dass die eingefügten Daten einen Fehler melden, weil die entsprechende Partition nicht gefunden wird, sollten wir rechtzeitig eine neue Partition erstellen. Im Folgenden werden weitere Vorgänge im Zusammenhang mit der Partitionswartung gezeigt.

# Daten einer Partition anzeigenmysql> SELECT * FROM tr PARTITION (p2);
+------+----------+------------+
| ID | Name | gekauft |
+------+----------+------------+
| 2 | Wecker | 05.11.1997 |
| 10 | Lavalampe | 25.12.1998 |
+------+----------+------------+
2 Zeilen im Satz (0,00 Sek.)

# Partition hinzufügenmysql> alter table tr add partition(
 -> PARTITION p6 WERTE WENIGER ALS (2020)
 -> );
Abfrage OK, 0 Zeilen betroffen (0,06 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

# Partition aufteilenmysql> alter table tr reorganize partition p5 into(
 -> Partition s0 Werte kleiner als(2012),
 -> Partition s1 Werte kleiner als (2015)
 -> );
Abfrage OK, 0 Zeilen betroffen (0,26 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

# Partitionen zusammenführenmysql> alter table tr reorganize partition s0,s1 into ( 
 -> Partition p5 Werte kleiner als (2015) 
 -> );
Abfrage OK, 0 Zeilen betroffen (0,12 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

# Daten einer Partition löschen mysql> alter table tr truncate partition p0;
Abfrage OK, 0 Zeilen betroffen (0,11 Sek.)

# Partition löschen mysql> alter table tr drop partition p1;
Abfrage OK, 0 Zeilen betroffen (0,06 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

# Austauschpartition # Erstellen Sie zunächst eine Austauschtabelle mit der gleichen Struktur wie die Partitionstabelle mysql> CREATE TABLE `tr_archive` (
 -> `id` INT, 
 -> `Name` VARCHAR(50), 
 -> `gekauft` DATUM
 ->) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Abfrage OK, 0 Zeilen betroffen (0,28 Sek.)
# Führen Sie „Partition austauschen mysql> alter table tr“ aus, und tauschen Sie PARTITION p2 mit Tabelle tr_archive aus.
Abfrage OK, 0 Zeilen betroffen (0,13 Sek.)

3. Partitionsüberlegungen und anwendbare Szenarien

Tatsächlich gibt es bei der Verwendung von Partitionstabellen viele Einschränkungen und Vorsichtsmaßnahmen. Unter Bezugnahme auf die offizielle Dokumentation werden einige Punkte wie folgt kurz zusammengefasst:

• Das Partitionsfeld muss vom Typ „Integer“ oder ein Ausdruck sein, der sich in einen Integer auflösen lässt.
• Es wird empfohlen, das Partitionsfeld auf NOT NULL zu setzen. Wenn das Partitionsfeld einer Datenzeile null ist, wird die Datenzeile bei der RANGE-Partitionierung in die kleinste Partition aufgeteilt.
• Wenn bei der MySQL-Partitionierung ein Primärschlüssel oder ein eindeutiger Schlüssel vorhanden ist, muss die Partitionierungsspalte darin enthalten sein.
• Innodb-partitionierte Tabellen unterstützen keine Fremdschlüssel.
• Das Ändern der SQL_Mode-Einstellung kann die Leistung partitionierter Tabellen beeinträchtigen.
• Partitionierte Tabellen haben keinen Einfluss auf Auto-Increment-Spalten.

Aus der obigen Einführung können wir ersehen, dass Partitionstabellen für einige Protokollierungstabellen geeignet sind. Kennzeichnend für diesen Tabellentyp sind die große Datenmenge, die Unterscheidung zwischen kalten und heißen Daten sowie die Datenarchivierung nach der Zeitdimension. Dieser Tabellentyp eignet sich besser für die Verwendung von Partitionstabellen, da Partitionstabellen separate Partitionen verwalten können, was für die Datenarchivierung praktischer ist.

4. Warum Partitionstabellen nicht häufig verwendet werden

In unserer Projektentwicklung werden Partitionstabellen selten verwendet. Hier sind einige Gründe:

• Die Auswahl der Partitionsfelder ist eingeschränkt.
• Wenn die Abfrage den Partitionsschlüssel nicht verwendet, werden möglicherweise alle Partitionen gescannt, ohne dass die Effizienz verbessert wird.
• Wenn die Daten ungleichmäßig verteilt sind und die Partitionsgrößen stark variieren, kann die Leistungsverbesserung begrenzt sein.
• Es ist ziemlich kompliziert, eine normale Tabelle in eine partitionierte Tabelle umzuwandeln.
• Die Partitionen müssen kontinuierlich gepflegt werden. Beispielsweise muss vor Juni eine neue Partition für Juni hinzugefügt werden.
• Erhöhte Lernkosten und unbekannte Risiken.
  

Zusammenfassen:

In diesem Artikel wird die MySQL-Partitionierung ausführlich vorgestellt. Wenn Sie partitionierte Tabellen verwenden möchten, empfiehlt es sich, im Voraus zu planen, partitionierte Tabellen während der Initialisierung zu erstellen und Wartungspläne zu erstellen. Bei richtiger Verwendung ist dies recht praktisch, insbesondere für Tabellen, die eine Archivierung historischer Daten erfordern. Die Verwendung partitionierter Tabellen macht die Archivierung bequemer. Natürlich gibt es noch viel mehr Inhalt zur Partitionstabelle. Interessierte Studierende können in der offiziellen Dokumentation nachsehen, die eine große Anzahl von Beispielen enthält.

siehe:

https://dev.mysql.com/doc/refman/5.7/en/partitioning.html

https://www.jb51.net/article/187690.htm

Dies ist das Ende dieses Artikels zum Best-Practice-Leitfaden für MySQL-Partitionstabellen. Weitere relevante Praktiken für MySQL-Partitionstabellen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!