MySQL Best Practices: Grundlegende Arten von Partitionstabellen

Übersicht über partitionierte MySQL-Tabellen

Da MySQL immer beliebter wird, werden auch die in MySQL gespeicherten Daten immer größer. In unserer täglichen Arbeit stoßen wir häufig auf Tabellen, in denen Hunderte Millionen oder sogar Milliarden Datensätze gespeichert sind. In diesen Tabellen wird eine große Menge historischer Datensätze gespeichert. Das Bereinigen dieser historischen Daten ist mühsam, da sich alle Daten in einer gemeinsamen Tabelle befinden. Daher können Sie zum Löschen nur eine oder mehrere Löschanweisungen mit Where-Bedingungen aktivieren (normalerweise ist die Where-Bedingung die Zeit). Dies belastet die Datenbank stark. Obwohl wir diese entfernt haben, wurden die zugrunde liegenden Datendateien nicht kleiner. Bei Problemen dieser Art ist die Verwendung der Partitionstabelle die effektivste Lösung. Die gebräuchlichste Partitionierungsmethode ist die Partitionierung nach Zeit. Einer der größten Vorteile der Partitionierung besteht darin, dass historische Daten sehr effizient bereinigt werden können.

Partitionstyp

Derzeit unterstützt MySQL vier Partitionierungstypen: Bereichspartitionierung (RANGE), Listenpartitionierung (LIST), Hash-Partitionierung (HASH) und KEY-Partitionierung. Schauen wir uns die einzelnen Partitionstypen an:

RANGE-Partitionierung

Weist mehrere Zeilen Partitionen basierend auf Spaltenwerten zu, die in ein bestimmtes zusammenhängendes Intervall fallen. Die häufigste basiert auf dem Zeitfeld. Die partitionsbasierte Spalte ist vorzugsweise eine Ganzzahl. Wenn es sich um einen Datumstyp handelt, kann eine Funktion verwendet werden, um sie in eine Ganzzahl umzuwandeln. In diesem Beispiel wird die Funktion to_days verwendet

TABELLE ERSTELLEN mein_Bereich_Datum/Uhrzeit(
 Ich würde INT,
 Einstellungsdatum DATETIME
) 
PARTITION NACH BEREICH (BIS_TAGE(Einstellungsdatum)) (
 PARTITION p1 WERTE KLEINER ALS (TO_DAYS('20171202')),
 PARTITION p2 WERTE KLEINER ALS (TO_DAYS('20171203')),
 PARTITION p3 WERTE KLEINER ALS (TO_DAYS('20171204')),
 PARTITION p4 WERTE KLEINER ALS (TO_DAYS('20171205')),
 PARTITION p5 WERTE KLEINER ALS (TO_DAYS('20171206')),
 PARTITION p6 WERTE KLEINER ALS (TO_DAYS('20171207')),
 PARTITION p7 WERTE KLEINER ALS (TO_DAYS('20171208')),
 PARTITION p8 WERTE KLEINER ALS (TO_DAYS('20171209')),
 PARTITION p9 WERTE KLEINER ALS (TO_DAYS('20171210')),
 PARTITION p10 WERTE KLEINER ALS (TO_DAYS('20171211')),
 PARTITION p11 WERTE KLEINER ALS (MAXIMALER WERT) 
);

p11 ist eine Standardpartition und alle Datensätze, die größer als 20171211 sind, befinden sich in dieser Partition. MAXVALUE ist ein unendlicher Wert. p11 ist eine optionale Partition. Wenn diese Partition beim Definieren der Tabelle nicht angegeben wird, erhalten wir beim Einfügen von Daten, die größer als 20171211 sind, eine Fehlermeldung.

Wenn wir eine Abfrage ausführen, müssen wir das Partitionsfeld einschließen. Damit können Sie die Partitionstrimmfunktion verwenden

mysql> in my_range_datetime einfügen, auswählen * aus Test;                                  
Abfrage OK, 1.000.000 Zeilen betroffen (8,15 Sek.)
Datensätze: 1000000 Duplikate: 0 Warnungen: 0

mysql> Partitionen erklären, wählen Sie * aus my_range_datetime, wobei hiredate >= '20171207124503' und hiredate<='20171210111230'; 
+----+----------+------------------+--------------+------+---------------+-----------+---------+---------+---------+---------+-------------+
| ID | Typ auswählen | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+------------------+--------------+------+---------------+-----------+---------+---------+---------+---------+-------------+
| 1 | SIMPLE | my_range_datetime | p7,p8,p9,p10 | ALLE | NULL | NULL | NULL | NULL | 400061 | Verwenden von „where“ |
+----+----------+------------------+--------------+------+---------------+-----------+---------+---------+---------+---------+-------------+
1 Zeile im Satz (0,03 Sek.)

Beachten Sie den Inhalt der Partitionen im Ausführungsplan. Es werden nur drei Partitionen abgefragt, p7, p8, p9 und p10. Daraus ist ersichtlich, dass mit der Funktion to_days tatsächlich eine Partitionsbereinigung durchgeführt werden kann.

Das Obige basiert auf Datum und Uhrzeit. Was passiert, wenn das obige Problem auftritt, wenn es sich um einen Zeitstempeltyp handelt?

Tatsächlich bietet MySQL ein RANGE-Partitionierungsschema basierend auf der Funktion UNIX_TIMESTAMP. Darüber hinaus kann nur die Funktion UNIX_TIMESTAMP verwendet werden. Wenn andere Funktionen wie to_days verwendet werden, wird der folgende Fehler gemeldet: „ERROR 1486 (HY000): Konstante, zufällige oder zeitzonenabhängige Ausdrücke in der (Unter-)Partitionierungsfunktion sind nicht zulässig.“

Und in der offiziellen Dokumentation heißt es auch: „Alle anderen Ausdrücke, die TIMESTAMP-Werte beinhalten, sind nicht zulässig. (Siehe Fehler Nr. 42849.)“.

Als Nächstes testen wir das RANGE-Partitionierungsschema basierend auf der Funktion UNIX_TIMESTAMP, um zu sehen, ob damit eine Partitionsbereinigung möglich ist.

Partitionierungsschema für TIMESTAMP

Die Anweisung zur Tabellenerstellung lautet wie folgt:

Tabelle erstellen my_range_timestamp (
  Ich würde INT,
  Einstellungsdatum ZEITSTEMPEL
)
PARTITION NACH BEREICH ( UNIX_TIMESTAMP ( Einstellungsdatum ) ) (
  PARTITION p1 WERTE KLEINER ALS ( UNIX_TIMESTAMP('2017-12-02 00:00:00') ),
  PARTITION p2 WERTE KLEINER ALS ( UNIX_TIMESTAMP('2017-12-03 00:00:00') ),
  PARTITION p3 WERTE KLEINER ALS ( UNIX_TIMESTAMP('2017-12-04 00:00:00') ),
  PARTITION p4 WERTE KLEINER ALS ( UNIX_TIMESTAMP('2017-12-05 00:00:00') ),
  PARTITION p5 WERTE KLEINER ALS ( UNIX_TIMESTAMP('2017-12-06 00:00:00') ),
  PARTITION p6 WERTE KLEINER ALS ( UNIX_TIMESTAMP('2017-12-07 00:00:00') ),
  PARTITION p7 WERTE KLEINER ALS ( UNIX_TIMESTAMP('2017-12-08 00:00:00') ),
  PARTITION p8 WERTE KLEINER ALS ( UNIX_TIMESTAMP('2017-12-09 00:00:00') ),
  PARTITION p9 WERTE KLEINER ALS ( UNIX_TIMESTAMP('2017-12-10 00:00:00') ),
  PARTITION p10 WERTE KLEINER ALS (UNIX_TIMESTAMP('2017-12-11 00:00:00') )
);

Daten einfügen und den Ausführungsplan der obigen Abfrage anzeigen

mysql> in my_range_timestamp einfügen, auswählen * aus Test;
Abfrage OK, 1.000.000 Zeilen betroffen (13,25 Sek.)
Datensätze: 1000000 Duplikate: 0 Warnungen: 0

mysql> Partitionen erklären, wählen Sie * aus my_range_timestamp, wobei hiredate >= '20171207124503' und hiredate<='20171210111230';
+----+----------+------------------+--------------+------+---------------+-----------+---------+---------+---------+---------+-------------+
| ID | Typ auswählen | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+----------+------------------+--------------+------+---------------+-----------+---------+---------+---------+---------+-------------+
| 1 | SIMPLE | my_range_timestamp | p7,p8,p9,p10 | ALLE | NULL | NULL | NULL | NULL | 400448 | Verwenden von „where“ |
+----+----------+------------------+--------------+------+---------------+-----------+---------+---------+---------+---------+-------------+
1 Zeile im Satz (0,00 Sek.)

Darüber hinaus ist auch eine Partitionsbeschneidung möglich.

Wenn Sie vor Version 5.7 für Spalten der Typen DATA und DATETIME eine Partitionsbereinigung implementieren wollten, konnten Sie nur die Funktionen YEAR() und TO_DAYS() verwenden. In Version 5.7 wurde die Funktion TO_SECONDS() hinzugefügt.

LIST-Partition

LIST-Partition

Die LIST-Partitionierung ähnelt der RANGE-Partitionierung. Der Unterschied besteht darin, dass LIST eine Sammlung von Aufzählungswertlisten und RANGE eine Sammlung kontinuierlicher Intervallwerte ist. Die Syntax beider ist sehr ähnlich. Es wird außerdem empfohlen, dass die LIST-Partitionsspalte eine Spalte ungleich Null ist, da sonst das Einfügen eines Nullwerts fehlschlägt, wenn die Aufzählungsliste keinen Nullwert enthält. Dies unterscheidet sich von anderen Partitionen. RANGE-Partitionen speichern es als minimalen Partitionswert, und HASH\KEY-Partitionen konvertieren es zur Speicherung in 0. Der Hauptgrund ist, dass LIST-Partitionen nur Ganzzahlen unterstützen und nicht ganzzahlige Felder durch Funktionen in Ganzzahlen konvertiert werden müssen.

Tabelle t_list erstellen( 
  ein int(11), 
  b int(11) 
  )(Partition nach Liste (b) 
  partitionieren Sie p0-Werte in (1,3,5,7,9), 
  Partitionieren Sie die p1-Werte in (2,4,6,8,0) 
  );

Hash-Partitionierung

In unserer tatsächlichen Arbeit begegnen uns häufig Tabellen wie Mitgliedertabellen. Es gibt kein offensichtliches Merkmalsfeld für die Partitionierung. Die Tabellendaten sind jedoch sehr umfangreich. Um diesen Datentyp zu partitionieren, bietet MySQL eine Hash-Partitionierung. Basierend auf der angegebenen Anzahl von Partitionen werden die Daten verschiedenen Partitionen zugewiesen. Die HASH-Partitionierung kann HASH nur auf Ganzzahlen anwenden. Nicht ganzzahlige Felder können nur durch Ausdrücke in Ganzzahlen umgewandelt werden. Der Ausdruck kann jede gültige Funktion oder jeder gültige Ausdruck in MySQL sein. Für nicht ganzzahlige HASH-Werte wird beim Einfügen der Daten in die Tabelle ein zusätzlicher Schritt zur Berechnung des Ausdrucks durchgeführt. Daher wird die Verwendung komplexer Ausdrücke nicht empfohlen, da dies die Leistung beeinträchtigt.

Die grundlegenden Anweisungen für die Hash-Partitionstabelle lauten wie folgt:

CREATE TABLE mein_Mitglied (
  id INT NICHT NULL,
  fname VARCHAR(30),
  lname VARCHAR(30),
  erstellt DATUM NICHT NULL STANDARD '1970-01-01',
  getrenntes DATUM NICHT NULL DEFAULT '9999-12-31',
  job_code INT,
  Store-ID INT
)
PARTITION NACH HASH(id)
PARTITIONEN 4;

Beachten:

1. Für die HASH-Partitionierung ist die Angabe der PARTITIONS-Klausel nicht erforderlich. Wenn im obigen Text beispielsweise PARTITIONS 4 angegeben ist, ist die Standardanzahl der Partitionen 1.
2. Das Schreiben von PARTITIONEN ohne Angabe der Anzahl der Partitionen ist nicht zulässig.
3. Wie bei der RANGE-Partitionierung und der LIST-Partitionierung muss der Ausdruck in der Klausel PARTITION BY HASH (Ausdruck) einen ganzzahligen Wert zurückgeben.
4. Die zugrunde liegende Implementierung der HASH-Partitionierung basiert tatsächlich auf der MOD-Funktion. Beispielsweise für die folgende Tabelle
   

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4; Wenn Sie einen Datensatz mit col3 als „2017-09-15“ einfügen möchten, wird die Partitionsauswahl durch die folgenden Werte bestimmt:

MOD(JAHR('2017-09-01'),4) = MOD(2017,4) = 1

LINEARE HASH-Partitionierung

Die LINEARE HASH-Partitionierung ist eine spezielle Art der HASH-Partitionierung. Im Gegensatz zur HASH-Partitionierung, die auf der MOD-Funktion basiert, basiert die LINEARE HASH-Partitionierung auf einem anderen Algorithmus.

Das Format ist wie folgt:

Tabelle erstellen meine_Mitglieder (
  id INT NICHT NULL,
  fname VARCHAR(30),
  lname VARCHAR(30),
  eingestellt DATUM NICHT NULL STANDARD '1970-01-01',
  getrenntes DATUM NICHT NULL DEFAULT '9999-12-31',
  job_code INT,
  Store-ID INT
)
PARTITION NACH LINEAR HASH(id)
PARTITIONEN 4;

Hinweis: Der Vorteil besteht darin, dass in Szenarien mit großen Datenmengen, z. B. auf TB-Ebene, das Hinzufügen, Löschen, Zusammenführen und Aufteilen von Partitionen schneller geht. Der Nachteil besteht darin, dass im Vergleich zu HASH-Partitionen eine ungleichmäßige Datenverteilung wahrscheinlicher ist.

KEY-Partition

Die KEY-Partitionierung ähnelt der HASH-Partitionierung, weist jedoch folgende Unterschiede auf:

1. Die KEY-Partitionierung lässt mehrere Spalten zu, während die HASH-Partitionierung nur eine Spalte zulässt.
2. Wenn ein Primärschlüssel oder eindeutiger Schlüssel vorhanden ist, kann die Partitionsspalte im Schlüssel nicht angegeben werden. Der Standardwert ist der Primärschlüssel oder eindeutige Schlüssel. Wenn nicht, muss die Spalte explizit angegeben werden.
3. Das KEY-Partitionsobjekt muss eine Spalte sein und darf kein auf einer Spalte basierender Ausdruck sein.
4. Die Algorithmen für KEY-Partitionierung und HASH-Partitionierung sind unterschiedlich. Bei PARTITION BY HASH (expr) ist der MOD-Wert der von expr zurückgegebene Wert, während er bei PARTITION BY KEY (column_list) auf dem MD5-Wert der Spalte basiert.
   

Das Format ist wie folgt:

Tabelle erstellen k1 (
  id INT NICHT NULL PRIMÄRSCHLÜSSEL,  
  Name VARCHAR(20)
)
PARTITION NACH KEY()
PARTITIONEN 2;

Wenn kein Primärschlüssel oder eindeutiger Schlüssel vorhanden ist, lautet das Format wie folgt:

Tabelle erstellen tm1 (
  s1 CHAR(32)
)
PARTITION NACH SCHLÜSSEL(S1)
PARTITIONEN 10;

Zusammenfassen:

Wenn bei der MySQL-Partitionierung ein Primärschlüssel oder ein eindeutiger Schlüssel vorhanden ist, muss die Partitionierungsspalte darin enthalten sein.

Bei nativen RANGE-Partitionen, LIST-Partitionen und HASH-Partitionen kann das Partitionsobjekt nur einen ganzzahligen Wert zurückgeben.

Das Partitionsfeld darf nicht NULL sein. Wie lässt sich sonst der Partitionsbereich bestimmen? Versuchen Sie daher, NOT NULL zu verwenden.

Dies ist das Ende dieses Artikels über bewährte MySQL-Methoden und grundlegende Typen partitionierter Tabellen. Weitere Informationen zu grundlegenden Typen partitionierter MySQL-Tabellen finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!