Richtiger Einsatz von MySQL-Partitionstabellen

Übersicht über partitionierte MySQL-Tabellen

Wir stoßen häufig auf Tabellen, in denen Hunderte Millionen oder sogar Milliarden Datensätze gespeichert sind. In diesen Tabellen werden große Mengen 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.

1. Bestätigen Sie, ob der MySQL-Server Partitionstabellen unterstützt

Befehl:

Plugins anzeigen; 

2. Eigenschaften von MySQL-Partitionstabellen

Logisch handelt es sich um eine Tabelle, physisch ist sie jedoch in mehreren Dateien gespeichert.

HASH-Partition (HASH)

Eigenschaften der HASH-Partition

• Speichern Sie Datenzeilen in verschiedenen Partitionen der Tabelle entsprechend dem Wert von MOD (Partitionsschlüssel, Anzahl der Partitionen).
• Daten können gleichmäßig auf Partitionen verteilt werden
• Der Schlüsselwert der HASH-Partition muss ein INT-Typwert sein oder kann durch eine Funktion in den INT-Typ konvertiert werden

So erstellen Sie eine HASH-Partitionstabelle

Verwenden Sie das Feld customer_id vom Typ INT als Partitionsschlüssel

Tabelle „customer_login_log“ erstellen (
 `customer_id` int(10) unsigned NOT NULL COMMENT 'Anmelde-Benutzer-ID',
 `login_time` Zeitstempel NICHT NULL STANDARD CURRENT_TIMESTAMP BEI UPDATE CURRENT_TIMESTAMP KOMMENTAR 'Benutzeranmeldezeit',
 `login_ip` int(10) unsigned NOT NULL COMMENT 'Login-IP',
 `login_type` tinyint(4) NOT NULL COMMENT 'Anmeldetyp: 0 erfolglos 1 erfolgreich'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Benutzeranmeldeprotokolltabelle'

PARTITION NACH HASH(Kunden-ID) PARTITIONEN 4;

Verwenden Sie das Nicht-INT-Feld login_time als Partitionsschlüssel (muss zuerst in den INT-Typ konvertiert werden)

Tabelle „customer_login_log“ erstellen (
 `customer_id` int(10) unsigned NOT NULL COMMENT 'Anmelde-Benutzer-ID',
 `login_time` Zeitstempel NICHT NULL STANDARD CURRENT_TIMESTAMP BEI UPDATE CURRENT_TIMESTAMP KOMMENTAR 'Benutzeranmeldezeit',
 `login_ip` int(10) unsigned NOT NULL COMMENT 'Login-IP',
 `login_type` tinyint(4) NOT NULL COMMENT 'Anmeldetyp: 0 erfolglos 1 erfolgreich'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Benutzeranmeldeprotokolltabelle'

PARTITION NACH HASH(UNIX_TIMESTAMP(Anmeldezeit)) PARTITIONEN 4;

Wenn die Tabelle customer_login_log nicht partitioniert ist, wird die Datei auf der physischen Festplatte

customer_login_log.frm # speichert die ursprünglichen Dateninformationen der Tabelle customer_login_log.ibd # Innodb-Datendatei

Wenn Sie eine HASH-Partitionstabelle wie oben erstellen, gibt es fünf Dateien

customer_login_log.frm 
Kunden-Anmeldeprotokoll#P#p0.ibd
Kunden-Anmeldeprotokoll#P#p1.ibd
Kunden-Anmeldeprotokoll#P#p2.ibd
Kunden-Anmeldeprotokoll#P#p3.ibd

Demo

Die Verwendung ist dieselbe wie ohne Partition. Es scheint, als gäbe es nur eine Datenbank, aber tatsächlich gibt es mehrere Partitionsdateien. Wenn wir beispielsweise ein Datenelement einfügen möchten, müssen wir die Partition nicht angeben. MySQL erledigt das automatisch für uns.

Abfrage

Bereichspartitionierung (RANGE)

RANGE-Partitionseigenschaften

• Speichern Sie Datenzeilen in verschiedenen Partitionen der Tabelle basierend auf dem Bereich der Partitionsschlüsselwerte.
• Die Bereiche mehrerer Partitionen müssen zusammenhängend sein, dürfen sich aber nicht überschneiden.
• Standardmäßig wird das Attribut VALUES LESS THAN verwendet, d. h., jede Partition enthält nicht den angegebenen Wert.

So erstellen Sie eine RANGE-Partition

Wenn die P3-Partition nicht definiert ist, wird ein Fehler gemeldet, wenn die eingegebene Kunden-ID größer als 29999 ist. Wenn sie definiert ist, werden alle Daten, die diesen Grenzwert überschreiten, in P3 gespeichert.

Anwendbare Szenarien für die RANGE-Partitionierung

• Der Partitionsschlüssel ist vom Typ „Datum“ oder „Zeit“ (dadurch können die Daten jeder Partitionstabelle ausgeglichener werden. Wenn im obigen Beispiel die Ganzzahl-ID als Partitionsschlüssel verwendet wird und die Anzahl der aktiven Benutzer zwischen 10.000 und 19.999 liegt, ist die Datenmenge in p1 viel größer als in anderen Partitionen, was die Partition bedeutungslos macht. Und wenn die Partition nach dem Typ „Zeit“ partitioniert ist und Sie die Daten in chronologischer Reihenfolge archivieren möchten, müssen Sie nur eine Partition archivieren.)
• Schließen Sie den Partitionsschlüssel in alle Abfragen ein (vermeiden Sie partitionsübergreifende Abfragen).
• Regelmäßiges Bereinigen historischer Daten nach Partitionsbereich

LIST-Partition

Funktionen der LIST-Partition

• Partitionieren Sie nach einer Liste von Partitionsschlüsselwerten
• Wie bei der Bereichspartitionierung können die Listenwerte jeder Partition nicht wiederholt werden
• Jede Datenzeile muss die entsprechende Partitionsliste finden können, sonst schlägt das Einfügen der Daten fehl.

So erstellen Sie eine LIST-Partition

Wenn Sie eine Zeile mit Login-Typ 10 einfügen, wird ein Fehler gemeldet.

3. So partitionieren Sie die Anmeldeprotokolltabelle (customer_login_log)

Geschäftsszenario

• Bei jeder Anmeldung eines Benutzers wird das Protokoll customer_login_log aufgezeichnet.
• Benutzeranmeldeprotokolle werden ein Jahr lang aufbewahrt und können nach einem Jahr gelöscht oder archiviert werden.

Partitionstyp und Partitionsschlüssel der Anmeldeprotokolltabelle

• Verwenden der RANGE-Partitionierung
• Verwenden Sie login_time als Partitionsschlüssel

Partitionierte Benutzeranmeldeprotokolltabelle

Die Daten werden in Partitionen nach Jahren gespeichert, daher wird zur Konvertierung die Funktion YEAR verwendet.

Tabelle „customer_login_log“ erstellen (
 `customer_id` int(10) unsigned NOT NULL COMMENT 'Anmelde-Benutzer-ID',
 `login_time` DATETIME NOT NULL COMMENT 'Benutzeranmeldezeit',
 `login_ip` int(10) unsigned NOT NULL COMMENT 'Login-IP',
 `login_type` tinyint(4) NOT NULL COMMENT 'Anmeldetyp: 0 erfolglos 1 erfolgreich'
) ENGINE=InnoDB 
PARTITION NACH BEREICH (JAHR(Anmeldezeit))(
PARTITION p0 WERTE WENIGER ALS (2017),
PARTITION p1 WERTE WENIGER ALS (2018),
PARTITION p2 WERTE WENIGER ALS (2019)
)

Einfügen und Abfragen von Daten

Abfragen der Partitionsdaten in der angegebenen Tabelle

SELECT Tabellenname, Partitionsname, Partitionsbeschreibung, Tabellenzeilen FROM
information_schema.`PARTITIONS` WHERE Tabellenname = 'Kunden-Anmeldeprotokoll'; 

Fügen Sie zwei weitere 18-Jahres-Protokolle ein und speichern Sie sie in der Tabelle p2

Wie bereits erwähnt, ist es beim Erstellen einer Partitionstabelle am besten, eine MAXVALUE-Partition zu erstellen. Der Grund, warum sie hier nicht erstellt wird, ist die bequemere Datenverwaltung. Wenn wir eine MAXVALUE-Partition erstellen, können wir leicht ein Problem übersehen. Wenn wir 2019 einige Daten einfügen, werden sie automatisch in dieser MAXVALUE-Partition gespeichert, was bei der späteren Datenverwaltung unpraktisch ist. Daher wird die MAXVALUE-Partition nicht erstellt.

Stattdessen fügen wir diese Partition am Ende jedes Jahres über die geplante Aufgabe hinzu. Beispielsweise müssen wir am Ende von 2018 eine Protokollpartition für 2019 in der Protokolltabelle erstellen, da sonst die Protokolle für 2019 nicht eingefügt werden können.

Wir können die folgende Anweisung verwenden

Hinzufügen einer Partition

ALTER TABLE customer_login_log ADD PARTITION (PARTITION p3 WERTE WENIGER ALS (2020))

Partitionen hinzufügen und Daten einfügen

Löschen einer Partition

Wenn wir die Daten von 2016 bis 2017 löschen möchten, müssen wir, da wir die Daten bereits partitioniert haben, nur die Partition p0 mit einer Anweisung löschen.

ALTER TABLE customer_login_log DROP PARTITION p0; 

Es kann festgestellt werden, dass die p0-Partition gelöscht und alle Protokolle im Jahr 2016 bereinigt wurden.

Historische Partitionsdaten archivieren

Möglicherweise besteht ein weiterer Grund zur Archivierung von Daten

MySQL-Version>=5.7, das Archivieren historischer Partitionsdaten ist sehr praktisch, und es wird eine Methode zum Austauschen von Partitionen bereitgestellt

Bedingungen für die Migration von Partitionsdatenarchiven:

• MySQL>=5.7
• Gleiche Struktur
• Die archivierte Datentabelle muss eine nicht partitionierte Tabelle sein
• Nicht temporäre Tabelle; darf keine Fremdschlüsseleinschränkungen haben
• Die Archivierungs-Engine sollte sein: archive

Erstellen einer Tabelle und Austauschen von Partitionen

Tabelle „arch_customer_login_log“ erstellen (
 `customer_id` INT unsigned NOT NULL COMMENT 'Anmelde-Benutzer-ID',
 `login_time` DATETIME NOT NULL COMMENT 'Benutzeranmeldezeit',
 `login_ip` INT unsigned NOT NULL COMMENT 'Login-IP',
 `login_type` TINYINT NOT NULL COMMENT 'Anmeldetyp: 0 erfolglos 1 erfolgreich'
) ENGINE=InnoDB;

ALTER TABLE customer_login_log 
 Tauschen Sie PARTITION p1 mit Tabelle arch_customer_login_log aus; 

Es kann festgestellt werden, dass die Daten von 2017 in der ursprünglichen Tabelle „customer_login_log“ (die Daten in der Partition p1) in die Tabelle „arch_customer_login_log“ übertragen wurden, die Partition p1 jedoch nicht gelöscht wurde, sondern nur die Daten übertragen wurden. Daher müssen wir auch den Befehl DROP ausführen, um die Partition zu löschen und zu verhindern, dass Daten in sie eingefügt werden.

Ändern Sie die Speicher-Engine der archivierten Daten in die Archiv-Engine

Zum Schluss ändern wir die Speicher-Engine der archivierten Daten in die Archiv-Engine. Der Befehl lautet

ALTER TABLE customer_login_log ENGINE=ARCHIV;

Der Vorteil der Verwendung der Archiv-Engine besteht darin, dass sie weniger Speicherplatz beansprucht als Innodb. Allerdings kann die Archiv-Engine nur Abfragevorgänge, keine Schreibvorgänge ausführen.

4. Hauptpunkte bei der Verwendung einer Partitionstabelle

• Wählen Sie Partitionsschlüssel basierend auf Geschäftsszenarien aus, um partitionsübergreifende Abfragen zu vermeiden
• Beim Abfragen einer partitionierten Tabelle ist es am besten, den Partitionsschlüssel in die WHERE-Klausel aufzunehmen.
• Bei einer Tabelle mit einem Primärschlüssel oder eindeutigen Index muss der Primärschlüssel oder eindeutige Index Teil des Partitionsschlüssels sein (aus diesem Grund haben wir bei der obigen Partitionierung die Login-Log-ID (login_id) des Primärschlüssels entfernt, da es sonst nicht möglich wäre, wie oben beschrieben nach Jahr zu partitionieren. Daher ist die Partitionstabelle eigentlich besser für die MyISAM-Engine geeignet).

Unterschiede zwischen MyISAM- und Innodb-Indizes

1. Über automatisches Wachstum

Die Auto-Increment-Spalte der MyISAM-Engine muss ein Index sein. Wenn es sich um einen zusammengesetzten Index handelt, muss die Auto-Increment-Spalte nicht die erste Spalte sein. Sie kann nach dem Sortieren der vorherigen Spalten inkrementiert werden.

Die automatische Wachstumsspalte der InnoDB-Engine muss ein Index sein. Wenn es sich um einen zusammengesetzten Index handelt, muss sie auch die erste Spalte des zusammengesetzten Index sein.

2. Über den Primärschlüssel

MyISAM lässt die Existenz von Tabellen ohne Indizes oder Primärschlüssel zu.

MyISAM-Indizes speichern Zeilenadressen.

Wenn die InnoDB-Engine keinen Primärschlüssel oder einen nicht leeren eindeutigen Index festlegt, wird automatisch ein 6-Byte-Primärschlüssel generiert (für den Benutzer unsichtbar).

Innodb-Daten sind Teil des Primärindex und der zusätzliche Index speichert den Wert des Primärindex.

3. Über die Funktion count()

MyISAM speichert die Gesamtzahl der Zeilen in einer Tabelle. Wenn Sie count(*) from table; auswählen, erhalten Sie den Wert direkt.

Innodb speichert nicht die Gesamtzahl der Zeilen in der Tabelle. Wenn Sie select count(*) from table; verwenden, wird die gesamte Tabelle durchlaufen, was ziemlich zeitaufwändig ist. Nach dem Hinzufügen der Wefre-Bedingung behandeln MyISAM und Innodb dies jedoch auf die gleiche Weise.

4. Volltextindizierung

MyISAM unterstützt Volltextindex vom Typ FULLTEXT

InnoDB unterstützt keine Volltextindizierung vom Typ FULLTEXT, aber InnoDB kann das Sphinx-Plug-In verwenden, um die Volltextindizierung zu unterstützen, und die Wirkung ist besser. (Sphinx ist eine Open-Source-Software, die API-Schnittstellen in mehreren Sprachen bereitstellt und verschiedene MySQL-Abfragen optimieren kann.)

5.Aus Tabelle löschen

Bei Verwendung dieses Befehls erstellt InnoDB die Tabelle nicht neu, sondern löscht die Daten nacheinander. Wenn Sie eine Tabelle mit einer großen Datenmenge auf InnoDB löschen möchten, verwenden Sie diesen Befehl am besten nicht. (Es wird empfohlen, die Tabelle abzuschneiden, der Benutzer muss jedoch die Berechtigung zum Löschen dieser Tabelle haben.)

6. Speicherort des Index

MyISAM-Indizes werden separat als Tabellenname + .MYI-Datei gespeichert.

Innodb-Indizes werden zusammen mit Daten im Tabellenbereich gespeichert.

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.