Detaillierte Erläuterung der Verwendung und der zugrunde liegenden Prinzipien von MySQL-Tabellenpartitionen

Was ist eine Partitionstabelle?

MySQL unterstützt Partitionierung seit Version 5.1. Bei der Partitionierung werden die Daten einer Tabelle in mehrere kleinere und besser handhabbare Teile aufgeteilt, z. B. nach Monat. Logisch gesehen handelt es sich jedoch immer noch um eine Tabelle.

Vor dem Aufkommen der Partitionstabelle wurden alle Daten in einer Datei gespeichert. Wenn die Datenmenge zu groß war, waren bei der Abfrage der Daten zwangsläufig viele IO-Operationen erforderlich. Nach der Verwendung der Partitionstabelle speichert jede Partition unterschiedliche Daten. Dies reduziert nicht nur die io. Außerdem kann dadurch der Datenzugriff beschleunigt werden.

Um die Leistung von MySQL sicherzustellen, empfehlen wir, dass eine einzelne MySQL-Tabelle nicht zu groß sein sollte. Die Empfehlungen lauten: Eine einzelne Tabelle sollte weniger als 2 GB groß sein, die Anzahl der Datensätze sollte weniger als 10 Millionen betragen und es sollten 10 Datenbanken und 100 Tabellen vorhanden sein. Wenn die Anzahl der Datensätze in einer Zeile sehr klein ist, kann die Anzahl der Datensätze größer sein. Andernfalls kann die Verarbeitung langsam werden, wenn die Anzahl der Datensätze Millionen erreicht.

Was also sollten wir tun, wenn das Geschäftsvolumen wächst und die Daten einen Engpass erreichen? Neben der Verwendung einer verteilten Datenbank können wir die Datenbank und die Tabellen auch selbst aufteilen oder die Partitionierungsfunktion von MySQL verwenden, um dies zu erreichen.

Die Partitionstabelle wurde erstellt, um das Konzept „Teile und herrsche“ zu unterstützen. Die Partitionstabelle ist sehr nützlich, aber viele Leute kennen sie immer noch nicht.
Nachdem eine Tabelle als Partitionstabelle festgelegt wurde, wird dem Dateinamen der Datendatei .idb ein #-Zeichen hinzugefügt, um anzuzeigen, dass es sich um eine Partitionstabelle handelt.

Anwendungsszenarien für Partitionstabellen

1. Die Tabelle ist so groß, dass sie nicht im Speicher abgelegt werden kann, oder nur der letzte Teil der Tabelle enthält aktuelle Daten und der Rest sind historische Daten.
2. Die Daten in der Partitionstabelle sind einfacher zu verwalten und große Datenmengen können stapelweise gelöscht werden
3. Optimieren, prüfen, reparieren usw. einer unabhängigen Partition
4. Die Daten der Partitionstabelle können auf verschiedene Geräte verteilt werden, ohne dass mehrere Hardwaregeräte effizient genutzt werden können.
5. Kann unabhängige Partitionen sichern und wiederherstellen;
   

Einschränkungen bei partitionierten Tabellen

1. Eine Tabelle kann maximal 1024 Partitionen haben, in den Versionen 5.7 und höher sind es 8196 Partitionen.
2. In den Anfangstagen von MySQL mussten Partitionierungsausdrücke Ganzzahlen oder Ausdrücke sein, die Ganzzahlen zurückgaben. In MySQL 5.5 können Spalten in einigen Szenarien direkt zur Partitionierung verwendet werden.
3. Partitionierte Tabellen können keine externen Einschränkungen verwenden
4. Am besten ist es, die Partitionsspalte nicht zu ändern
5. Wenn das Partitionierungsfeld einen Primärschlüssel oder eine eindeutige Indexspalte hat, müssen alle Primärschlüsselspalten und eindeutigen Indexspalten einbezogen werden, und zwar etwa so:
   

-- Partitionen erstellen, die alle Primärschlüssel enthalten müssen create table user_11(
  id bigint(20) ungleich null ,
  Name varchar(20) ,
  Alter int(3),
	PRIMÄRSCHLÜSSEL (`id`,`alter`)
)
-- Partition nach Bereichsspalten (ID, Alter) erstellen (
  Werte der Partition p00 sind kleiner als (6,30) – Werte kleiner als 6 befinden sich in der Partition P0; Werte der Partition p11 sind kleiner als (11,40) – Werte kleiner als 11 befinden sich in der Partition p1; Werte der Partition p22 sind kleiner als (16,50) – Werte kleiner als 16 befinden sich in der Partition p2; Werte der Partition p33 sind kleiner als (9999,9999) – Werte größer als 21 befinden sich in der Partition p3, oder verwenden Sie einen größeren Wert);

-- Partitionen erstellen, die alle eindeutigen Schlüssel enthalten müssen create table user_22(
  id bigint(20) ungleich null,
  Name varchar(20) ,
  Alter int(3) ungleich null ,
	eindeutiger Schlüssel only_one_1(Alter,ID)
)
-- Partition nach Bereichsspalten (ID, Alter) erstellen (
  Werte der Partition p000 sind kleiner als (6,30) – Werte kleiner als 6 befinden sich in der Partition P0; Werte der Partition p111 sind kleiner als (11,40) – Werte kleiner als 11 befinden sich in der Partition p1; Werte der Partition p222 sind kleiner als (16,50) – Werte kleiner als 16 befinden sich in der Partition p2; Werte der Partition p333 sind kleiner als (9999,9999) – Werte größer als 21 befinden sich in der Partition p3, oder verwenden Sie einen größeren Wert);

Partitionstyp

1. Bereichspartitionierung
2. Partition auflisten
3. Spaltenpartitionierung
4. Hash-Partition
5. Schlüsselpartitionierung
6. Unterpartitionen
   

Verwendung der Partitionstabelle

1. Bereichspartitionierung

Im folgenden Beispiel wird das Alter partitioniert.

Tabelle „Mitarbeiter“ erstellen (
id bigint(20) ungleich null,
Alter int(3) ungleich null,
Name varchar(20)
)
-- Partition nach Bereich (Alter) erstellen (
  Werte der Partition p0 kleiner als (6) – Werte kleiner als 6 befinden sich in der Partition P0; Werte der Partition p1 kleiner als (11) – Werte kleiner als 11 befinden sich in der Partition p1; Werte der Partition p2 kleiner als (16) – Werte kleiner als 16 befinden sich in der Partition p2; Werte der Partition p3 kleiner als (21) – Werte kleiner als 21 befinden sich in der Partition p3);

Nach der Erstellung können Sie die Partitionsdatei im Datenordner sehen

[root@VM_0_5_centos test]# pwd
/var/lib/mysql/test
[root@VM_0_5_centos test]# ll
Gesamtverbrauch 8741504
-rw-rw---- 1 mysql mysql 61 31. Oktober 2018 db.opt
-rw-rw---- 1 mysql mysql 8614 1. August 21:30 Mitarbeiter.frm
-rw-rw---- 1 mysql mysql 32 1. August 21:30 Mitarbeiter.par
-rw-rw---- 1 mysql mysql 98304 1. August 21:30 Mitarbeiter#P#p0.ibd
-rw-rw---- 1 mysql mysql 98304 1. August 21:30 Mitarbeiter#P#p1.ibd
-rw-rw---- 1 mysql mysql 98304 1. August 21:30 Mitarbeiter#P#p2.ibd
-rw-rw---- 1 mysql mysql 98304 1. August 21:30 Mitarbeiter#P#p3.ibd

Da in das Altersfeld nur Zahlen kleiner als 21 eingegeben werden können, wird ein Fehler gemeldet, wenn Sie die Zahl 21 eingeben.

mysql> Mitarbeiter einfügen (ID, Name, Alter) Werte (1, „yexindong“, 21);
FEHLER 1526 (HY000): Tabelle hat keine Partition für Wert 21

Um dieses Problem zu lösen, können Sie beim Erstellen einer Tabelle Folgendes tun: Verwenden Sie maxvalue für den Maximalwert. Es wird gesagt, dass der Wert von maxvalue 28 Neunen beträgt, also 999999999999999999999999999999

Tabelle „Mitarbeiter“ erstellen (
id bigint(20) ungleich null,
Alter int(3) ungleich null,
Name varchar(20)
)
-- Partition nach Bereich (Alter) erstellen (
  Werte der Partition p0 sind kleiner als (6) – Werte kleiner als 6 befinden sich in der Partition P0; Werte der Partition p1 sind kleiner als (11) – Werte kleiner als 11 befinden sich in der Partition p1; Werte der Partition p2 sind kleiner als (16) – Werte kleiner als 16 befinden sich in der Partition p2; Werte der Partition p3 sind kleiner als der Maximalwert – Werte größer als 16 befinden sich in der Partition p3 oder verwenden Sie einen größeren Wert);

Zeitbereichspartitionierung

CREATE TABLE Mitarbeiter (
    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 BEREICH ( JAHR(getrennt) ) (
    PARTITION p0 WERTE WENIGER ALS (1991) -- Daten vor 1991 befinden sich in Partition P0; PARTITION p1 WERTE WENIGER ALS (1996) -- Daten vor 1996 befinden sich in Partition P1; PARTITION p2 WERTE WENIGER ALS (2001) -- Daten vor 2001 befinden sich in Partition P2; PARTITION p3 WERTE WENIGER ALS MAXIMALER WERT -- Daten nach 2001 befinden sich in Partition P3);

CREATE TABLE-Mitglieder (
    Vorname VARCHAR(25) NOT NULL,
    Nachname VARCHAR(25) NOT NULL,
    Benutzername VARCHAR(16) NOT NULL,
    E-Mail VARCHAR(35),
    beigetreten DATUM NICHT NULL
)
PARTITION BY RANGE COLUMNS (verbunden) (
    PARTITION p0 WERTE KLEINER ALS ('1960-01-01'),
    PARTITION p1 WERTE KLEINER ALS ('1970-01-01'),
    PARTITION p2 WERTE KLEINER ALS ('1980-01-01'),
    PARTITION p3 WERTE KLEINER ALS ('1990-01-01'),
    PARTITION p4 WERTE KLEINER ALS MAXIMALER WERT
);

2. Listenpartition (Listenpartition)

Der größte Unterschied zwischen Listenpartitionierung und Bereichspartitionierung besteht darin, dass die Listenpartitionierung gleich ist, während die Bereichspartitionierung innerhalb eines bestimmten Bereichs liegt.

CREATE TABLE Mitarbeiter (
    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 BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17), -- Die Werte von 3,5,6,9,17 werden in die Partition pNorth eingefügt. PARTITION pEast VALUES IN (1,2,10,11,19,20), -- Die Werte von 1,2,10,11,19,20 werden in die Partition pEast eingefügt. PARTITION pWest VALUES IN (4,12,13,14,18), -- Die Werte von 4,12,13,14,18 werden in die Partition pWest eingefügt. PARTITION pCentral VALUES IN (7,8,15,16) -- Die Werte von 7,8,15,16 werden in die Partition pCentral eingefügt);

3. Spaltenpartitionierung

Spaltenpartitionierung ist eine Variante der Bereichspartitionierung und Listenpartitionierung. Das heißt, Spaltenpartitionierung wird durch Bereichspartitionierung und Listenpartitionierung gekapselt. Der einzige Unterschied besteht darin, dass Spaltenpartitionierung keine Datentypbeschränkungen hat. Mit anderen Worten: Bereichspartitionierung und Listenpartitionierung sind Spaltenpartitionierung.

4. Hash-Partition

Bei der Hash-Partitionierung muss kein Bereich oder keine Liste angegeben werden, sondern der einzufügende Wert wird dynamisch zugewiesen, um zu bestimmen, in welche Partition eingefügt werden soll. Das Prinzip ist dem von hashMap sehr ähnlich. Der Unterschied besteht darin, dass hashMap eine Störungsfunktion verwendet, um das Hash-Kollisionsproblem zu lösen, während die Hash-Partitionierung von MySQL das Ergebnis direkt durch eine Modulo-Operation erhält und den Wert dann an der angegebenen Position in die Partition einfügt.

-- Partitionierung gemeinsamer Felder CREATE TABLE employees (
    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 HASH(store_id)
PARTITIONEN 5;--Erstellen Sie 5 Partitionen, 0, 1, 2, 3, 4

- Erstellen Sie eine Partition vom Zeittyp CREATE TABLE employees (
    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 HASH( JAHR(eingestellt) )
PARTITIONEN 4; -- Erstellen Sie vier Partitionen: 0, 1, 2 und 3

5. Geheime Schlüsselpartition (Schlüsselpartition)

Schlüsselpartitionierung wird weniger genutzt

-- Partition nach Primärschlüssel CREATE TABLE k1 (
    id INT NICHT NULL PRIMÄRSCHLÜSSEL,
    Name VARCHAR(20)
)
PARTITION NACH KEY()
PARTITIONS 2; -- Erstellen Sie zwei Partitionen mit den Namen P0 und P1. Dies ist eine Variante der Hash-Partitionierung. Die Speichermethode ist dieselbe wie bei der Hash-Partitionierung. -- Partition nach eindeutigem Schlüssel CREATE TABLE k1 (
    id INT NICHT NULL,
    Name VARCHAR(20),
    EINDEUTIGER SCHLÜSSEL (ID)
)
PARTITION NACH KEY()
PARTITIONEN 3;-- Erstellen Sie drei Partitionen, nämlich p0, p1, p2

-- Geben Sie das Primärschlüsselfeld für die Partitionierung an CREATE TABLE tm1 (
    s1 CHAR(32) PRIMÄRSCHLÜSSEL
)
PARTITION NACH SCHLÜSSEL(S1)
PARTITIONEN 10; -- 10 Partitionen erstellen

6. Unterpartitionen

Unterpartitionierung kann wie folgt verstanden werden: Partitionierung auf der Grundlage der Partitionierung. Wenn beispielsweise eine Tabelle in drei Partitionen unterteilt ist und jede Partition drei Unterpartitionen hat, gibt es insgesamt 3 * 3 = 9 Partitionen.

 -- Die Tabelle enthält 3 Partitionen und jede Partition hat 2 Unterpartitionen, also insgesamt 6 Partitionen. CREATE TABLE ts (id INT, bought DATE)
    PARTITION NACH BEREICH (JAHR (gekauft))
    UNTERPARTITION NACH HASH(TO_DAYS(gekauft))
    UNTERPARTITIONEN 2 (
        PARTITION p0 WERTE WENIGER ALS (1990),
        PARTITION p1 WERTE WENIGER ALS (2000),
        PARTITION p2 WERTE KLEINER ALS MAXIMALER WERT
    );

Wenn Sie die MySQL-Datendatei aufrufen, sehen Sie, dass 6 Dateien vorhanden sind. Wie der Name schon sagt, werden 6 Partitionen generiert.

-rw-rw---- 1 mysql mysql 98304 2. August 22:37 ts#P#p0#SP#p0sp0.ibd
-rw-rw---- 1 mysql mysql 98304 2. August 22:37 ts#P#p0#SP#p0sp1.ibd
-rw-rw---- 1 mysql mysql 98304 2. August 22:37 ts#P#p1#SP#p1sp0.ibd
-rw-rw---- 1 mysql mysql 98304 2. August 22:37 ts#P#p1#SP#p1sp1.ibd
-rw-rw---- 1 mysql mysql 98304 2. August 22:37 ts#P#p2#SP#p2sp0.ibd
-rw-rw---- 1 mysql mysql 98304 2. August 22:37 ts#P#p2#SP#p2sp1.ibd

Hinzufügen einer Partition

- Listenpartition hinzufügen, Tabellentitel ändern, Partition hinzufügen (Partition p7-Werte in („CEO“));

Partitionstabellenprinzip

Die Partitionstabelle wird durch mehrere miteinander verbundene zugrunde liegende Tabellen implementiert, die ebenfalls durch Handle-Objekte identifiziert werden. Wir können auf jede Partition direkt zugreifen. Die Speicher-Engine verwaltet die zugrunde liegenden Tabellen der Partitionen auf die gleiche Weise wie normale Tabellen (alle zugrunde liegenden Tabellen müssen dieselbe Speicher-Engine verwenden). Der Index der partitionierten Tabelle fügt einfach jeder zugrunde liegenden Tabelle einen identischen Index hinzu. Aus Sicht der Speicher-Engine unterscheidet sich die zugrunde liegende Tabelle nicht von einer normalen Tabelle, und die Speicher-Engine muss nicht wissen, ob es sich um eine normale Tabelle oder um einen Teil einer partitionierten Tabelle handelt.

Die Operation der Partitionstabelle erfolgt nach folgender Operationslogik:

Abfrage auswählen

Beim Abfragen einer partitionierten Tabelle öffnet und sperrt die Partitionsebene zunächst alle darunter liegenden Tabellen. Der Optimierer ermittelt zunächst, ob einige Partitionen gefiltert werden können, und ruft dann die entsprechende Speicher-Engine-Schnittstelle auf, um auf die Daten jeder Partition zuzugreifen.

Einfügevorgang

Beim Schreiben eines Datensatzes öffnet und sperrt die Partitionsebene zuerst alle darunter liegenden Tabellen, bestimmt dann, welche Partition den Datensatz akzeptiert, und schreibt den Datensatz dann in die entsprechende darunter liegende Tabelle.

Löschvorgang

Beim Löschen eines Datensatzes öffnet und sperrt die Partitionsebene zuerst alle zugrunde liegenden Tabellen, bestimmt dann die den Daten entsprechende Partition und löscht schließlich die entsprechende zugrunde liegende Tabelle.

Aktualisierungsvorgang

Beim Aktualisieren eines Datensatzes öffnet und sperrt die Partitionsebene zunächst alle zugrunde liegenden Tabellen. MySQL ermittelt zunächst, in welcher Partition sich der zu aktualisierende Datensatz befindet, ruft dann die Daten ab und aktualisiert sie und ermittelt anschließend, in welcher Partition sich die aktualisierten Daten befinden sollen. Schließlich schreibt es in die zugrunde liegende Tabelle und löscht die zugrunde liegende Tabelle, in der sich die Quelldaten befinden.

Einige Operationen unterstützen das Filtern. Wenn Sie beispielsweise einen Datensatz löschen, muss MySQL diesen Datensatz zuerst finden. Wenn die Where-Bedingung zufällig mit dem Partitionsausdruck übereinstimmt, können alle Partitionen herausgefiltert werden, die diesen Datensatz nicht enthalten. Dies ist auch bei Updates wirksam. Wenn es sich um einen Einfügevorgang handelt, wird nur eine Partition getroffen und andere Partitionen werden herausgefiltert. MySQL ermittelt zunächst, zu welcher Partition der Datensatz gehört, und schreibt den Datensatz dann in die entsprechende Partitionstabelle, ohne andere Partitionen bearbeiten zu müssen.

Obwohl bei jeder Operation „zuerst alle zugrunde liegenden Tabellen geöffnet und gesperrt werden“, bedeutet dies nicht, dass die Partitionstabelle während der Verarbeitung die gesamte Tabelle sperrt. Wenn die Speicher-Engine selbst Sperren auf Zeilenebene implementieren kann, wie z. B. InnoDB, wird die entsprechende Tabellensperre auf Partitionsebene freigegeben.

So verwenden Sie die Partitionstabelle

1. Das Protokollsystem kann partitioniert werden. Im Allgemeinen ist die Anzahl der Protokolle relativ groß und sie werden nach Jahr oder Monat partitioniert. Im Allgemeinen ist es notwendig, die historischen Aufzeichnungen eines bestimmten Zeitraums im Protokollsystem abzufragen. Da die Datenmenge riesig ist, kann kein vollständiger Tabellenscan verwendet werden. Ein vollständiger Tabellenscan verursacht eine große Menge an zufälligen IOs. Wenn die Datenmenge zu groß ist, funktioniert der Index nicht. Zu diesem Zeitpunkt sollte eine Partitionierung in Betracht gezogen werden, um das Problem zu lösen.
2. Eine Partitionierung ist nicht nur bei großen Datenmengen erforderlich. Eine Partitionierung kann auch bei kleinen Datenmengen verwendet werden. In welchen Szenarien ist die Datenmenge klein? Die Antwort ist, dass Partitionierung verwendet werden kann, wenn die Daten, die Sie jedes Mal abfragen, ein Batch sind. Beispielsweise werden Wörterbücher, Geschäftswörterbücher und Benutzertypwörterbücher im Allgemeinen in derselben Tabelle gespeichert. Und bei jeder Abfrage fragen Sie nicht nur ein Geschäft oder einen Benutzertyp ab, sondern das gesamte Geschäft oder den gesamten Benutzertyp. Dies ist ein Batch, und Partitionierung kann auch verwendet werden, um dies zu erreichen.
3. Nach der Verwendung von Partitionen benötigen Sie keine Indizes mehr, da Partitionen im Allgemeinen für Bereichsabfragen verwendet werden und Bereichsabfragen keine Indizes verwenden müssen. Die Daten wurden auf verschiedene Partitionen verteilt.
4. Wenn Sie Indizes verwenden möchten, können Sie dies tun, müssen aber Hot Data von Cold Data trennen. Hot Data sind Daten, die häufig abgefragt werden. Fügen Sie Hot Data-Tabellen Indizes hinzu, um den Zugriff zu beschleunigen.
   

Vorsichtsmaßnahmen

1. Nullwerte machen die Partitionsfilterung ungültig. Partitionen erfordern Spaltennamen und Sie müssen sicherstellen, dass die Spaltennamen keine Nullwerte enthalten.
2. Wenn die Partitionsspalte und die Indexspalte nicht dieselbe Spalte sind, kann die Abfrage keine Partitionsfilterung durchführen. Wenn beispielsweise sowohl Ihre ID- als auch Ihre Altersfelder indiziert sind, ist es am besten, diese beiden Spalten beim Partitionieren als Partitionsspalten festzulegen: PARTITION BY RANGE COLUMNS(id,age)
3. Das Hinzufügen, Löschen und Ändern partitionierter Tabellen ist sehr aufwändig. Jedes Mal, wenn eine Tabelle hinzugefügt, gelöscht oder geändert wird, werden alle zugrunde liegenden Tabellen geöffnet und gesperrt. Solange eine Tabelle gesperrt ist, können keine anderen Vorgänge ausgeführt werden.
4. Bei der Wartung von Partitionen können die Kosten sehr hoch sein. Insbesondere wenn Sie die Partition ändern müssen, sind die Kosten am höchsten.
   

Zusammenfassen

Dies ist das Ende dieses Artikels über die Verwendung und die zugrunde liegenden Prinzipien von MySQL-Tabellenpartitionen. Weitere Informationen zu den zugrunde liegenden Prinzipien von MySQL-Tabellenpartitionen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!