Detaillierte Einführung in Partitionstabellen in MySQL

Dieser Artikel bietet Ihnen eine detaillierte Einführung in die Partitionstabelle in MySQL. Ich hoffe, dass er für Freunde hilfreich ist.

Für Benutzer ist die Partitionstabelle eine unabhängige logische Tabelle, die jedoch unten aus mehreren physischen Untertabellen besteht. Der Code, der die Partitionierung implementiert, ist eigentlich eine Kapselung der Handle-Objekte einer Reihe zugrunde liegender Tabellen. Anforderungen für Partitionstabellen werden über die Handle-Objekte in Schnittstellenaufrufe an die Speicher-Engine umgewandelt

Bedeutung

MySQL kann die in jeder Partition gespeicherten Daten definieren, indem es beim Erstellen einer Tabelle die PARTITION BY-Klausel verwendet. Beim Ausführen einer Abfrage filtert der Optimierer basierend auf der Partitionsdefinition diejenigen Partitionen heraus, die nicht über die von uns benötigten Daten verfügen, sodass die Abfrage nicht alle Partitionen scannen muss – es können nur die Partitionen gefunden werden, die die erforderlichen Daten enthalten.

Einer der Hauptzwecke der Partitionierung besteht darin, Daten in verschiedenen Tabellen mit einer gröberen Granularität zu speichern. Auf diese Weise können zusammengehörige Daten gespeichert werden. Darüber hinaus ist dies sehr praktisch, wenn wir die Daten der gesamten Partition auf einmal löschen möchten.

Partitionierung kann in den folgenden Szenarien eine große Rolle spielen:

• Die Tabelle ist zu groß, um alle in den Speicher zu passen, oder nur in die Tabelle. Der letzte Teil enthält Hotspot Daten und der Rest sind historische Daten

• Partitionstabellendaten sind einfacher zu pflegen

• Partitionstabellendaten können auf verschiedene physische Geräte

• Partitionstabellen können verwendet werden, um einige besondere Engpässe zu vermeiden

• Bei Bedarf können unabhängige Partitionen gesichert und wiederhergestellt werden

Auch die Partitionstabelle selbst hat einige

Einschränkungen, folgende Punkte sind besonders wichtig:

• Eine Tabelle kann nur maximal 1024 Partitionen haben

• In MySQL 5.1 muss der Partitionsausdruck eine Ganzzahl oder ein Ausdruck sein, der eine Ganzzahl zurückgibt. In MySQL5.5 können Spalten in einigen Szenarien direkt für die Partitionierung verwendet werden

• Fremdschlüsseleinschränkungen können nicht in partitionierten Tabellen verwendet werden

• Bei Partitionierung Wenn das Feld Primärschlüssel- oder eindeutige Indexspalten enthält, müssen alle Primärschlüsselspalten und eindeutigen Indexspalten enthalten sein

Prinzip der partitionierten Tabelle

Es gibt keinen Unterschied zwischen der Verwaltung jeder zugrunde liegenden Tabelle in der Partition durch die Speicher-Engine und der Verwaltung normaler Tabellen (alle zugrunde liegenden Tabellen müssen dieselbe Speicher-Engine verwenden)

Der Index der Partitionstabelle muss nur hinzugefügt werden ein identischer Index für jede zugrunde liegende Tabelle. Aus Sicht der Speicher-Engine gibt es keinen Unterschied zwischen der zugrunde liegenden Tabelle und einer gewöhnlichen Tabelle, und die Speicher-Engine muss nicht wissen, ob es sich um eine gewöhnliche Tabelle oder einen Teil einer partitionierten Tabelle handelt.

Die Operationen an der Partitionstabelle werden gemäß der folgenden Operationslogik ausgeführt:

SELECT-Abfrage

Beim Abfragen einer Partitionstabelle öffnet und sperrt zunächst die Partitionsschicht alle In der unteren Schichttabelle ermittelt der Optimierer 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

INSERT-Operation

Beim Schreiben eines Datensatzes Die Partitionsschicht: Öffnen und sperren Sie zuerst alle zugrunde liegenden Tabellen, bestimmen Sie dann, welche Partition den Datensatz empfängt, und schreiben Sie den Datensatz dann in die entsprechende zugrunde liegende Tabelle.

LÖSCHEN-Vorgang

Beim Löschen eines Datensatzes wird die Partition gelöscht Die Ebene öffnet und sperrt zunächst alle zugrunde liegenden Tabellen, bestimmt dann die den Daten entsprechende Partition und führt schließlich einen Löschvorgang für die entsprechende zugrunde liegende Tabelle aus

UPDATE-Vorgang

Wenn ein Datensatz aktualisiert wird, Zuerst wird die Partitionsschicht geöffnet und alle zugrunde liegenden Tabellen gesperrt. MySQL bestimmt zunächst, in welcher Partition der Datensatz aktualisiert werden muss, entnimmt dann die Daten und aktualisiert sie, bestimmt dann, in welche Partition die aktualisierten Daten abgelegt werden sollen, und schreibt schließlich die zugrunde liegende Tabelle und aktualisiert die Originaldaten.

Diese Vorgänge unterstützen das Filtern.

Obwohl jeder Vorgang „zuerst alle zugrunde liegenden Tabellen öffnet und sperrt“, bedeutet dies

nicht, dass die Partitionstabelle während der Verarbeitung die gesamte Tabelle sperrt . Wenn die Speicher-Engine selbst Sperren auf Zeilenebene implementieren kann, wird die entsprechende Tabellensperre auf Partitionsebene freigegeben. Dieser Sperr- und Entsperrvorgang ähnelt Abfragen bei normalem InnoDB.

Arten von Partitionstabellen

MySQL unterstützt eine Vielzahl von Partitionstabellen. Am häufigsten sehen wir die Partitionierung basierend auf Bereichen. Jeder Partitionsspeicher liegt in einem bestimmten Bereich . Aufzeichnungen. Der Partitionsausdruck kann eine Spalte oder ein Ausdruck sein, der Spalten enthält.

Zum Beispiel speichert die folgende Tabelle die jährlichen Umsätze in verschiedenen Partitionen:

CREATE TABLE sales(
    order_date DATETIME NOT NULL,
    ....
)ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date))(
    PARTITION p_2010 VALUES LESS THAN (2010),
    PARTITION p_2011 VALUES LESS THAN (2011),
    PARTITION p_2012 VALUES LESS THAN (2012),
    PARTITION p_catchall VALUES LESS THAN MAXVALUE;
)

PARTITION In der Partitionsklausel können verschiedene Funktionen verwendet werden. Es gibt jedoch eine Anforderung: Der vom

-Ausdruck zurückgegebene Wert muss eine bestimmte Ganzzahl sein und darf keine Konstante sein.

MySQL unterstützt auch Schlüsselwert-, Hash- und Listenpartitionierung usw.

So verwenden Sie partitionierte Tabellen

Wenn wir Datensätze für einen bestimmten Zeitraum aus einer sehr großen Tabelle abfragen möchten, wie sollten wir diese Tabelle abfragen und wie können wir das? effizienter machen?

Da die Datenmenge sehr groß ist, können wir sicherlich nicht bei jeder Abfrage die gesamte Tabelle scannen. In Anbetracht des Speicherplatzverbrauchs und der Wartung von Indizes möchten wir keine Indizes verwenden. Selbst wenn Sie Indizes verwenden, werden Sie feststellen, dass die Daten nicht in der gewünschten Weise aggregiert werden, was zu einer starken Fragmentierung führt und schließlich dazu führt, dass eine Abfrage Tausende von zufälligen I/Os generiert. Tatsächlich

Wenn die Datenmenge extrem groß ist, kann der B-Tree-Index nicht mehr funktionieren.

So können wir einige grobkörnigere, aber kostengünstigere Methoden zum Abrufen von Daten wählen, z. B. die Indizierung nur eines kleinen Teils der entsprechenden Metadaten für eine große Datenmenge.

Das ist genau die Aufgabe der Partitionierung. Das Verständnis der Partitionierung kann als die ursprüngliche Form eines Index betrachtet werden. Da Partitionen keine zusätzlichen Datenstrukturen benötigen, um die Daten in jeder Partition aufzuzeichnen – Partitionen müssen den Speicherort jedes Datenelements nicht genau lokalisieren, sodass keine zusätzlichen Datenstrukturen erforderlich sind – sind die Kosten daher sehr niedrig . Es ist nur ein einfacher Ausdruck erforderlich, um auszudrücken, welche Daten in jeder Partition gespeichert sind.

Um die Skalierbarkeit großer Datenmengen sicherzustellen, gibt es im Allgemeinen zwei Strategien:

1. Scannen Sie die Daten vollständig ohne Index : Solange die WHERE-Bedingung genutzt werden kann, um die benötigten Daten auf wenige Partitionen zu beschränken, ist die Effizienz sehr hoch. Bei dieser Strategie wird davon ausgegangen, dass die Daten nicht vollständig im Speicher abgelegt werden müssen und dass sich alle erforderlichen Daten auf der Festplatte befinden. Da der Speicher relativ klein ist, werden die Daten schnell aus dem Speicher verdrängt, sodass der Cache keine Rolle spielt. Diese Strategie eignet sich, wenn auf normale Weise auf große Datenmengen zugegriffen wird.

2. Indexdaten und separate Hotspots: Wenn die Daten offensichtliche „Hotspots“ aufweisen und außer diesem Teil der Daten nur selten auf andere Daten zugegriffen wird Sie können diesen Teil der Hotspot-Daten in einer separaten Partition ablegen, sodass die Daten in dieser Partition im Speicher zwischengespeichert werden können. Solche Abfragen können nur auf eine kleine partitionierte Tabelle zugreifen, Indizes verwenden und auch den Cache effektiv nutzen.

Unter welchen Umständen treten Probleme auf?

Die beiden oben vorgestellten Partitionierungsstrategien basieren auf zwei sehr wichtigen Annahmen: Abfragen können gefiltert werden. Löschen von a Viele zusätzliche Partitionen und Partitionen selbst verursachen keine großen zusätzlichen Kosten.

Es stellt sich heraus, dass diese beiden Annahmen in bestimmten Szenarien problematisch sein können:

• Partitionsspalten und Indexspalten stimmen nicht überein: Wenn definiert Die Indexspalte und die Partitionsspalte stimmen nicht überein, was dazu führt, dass die Abfrage die Partitionsfilterung nicht durchführen kann.

• Die Wahl einer Partition kann teuer sein: Verschiedene Arten von Partitionen werden unterschiedlich implementiert, daher variiert ihre Leistung. Insbesondere bei der Bereichspartitionierung kann der Aufwand für die Abfrage, zu welchen Partitionen eine qualifizierte Zeile gehört, sehr hoch sein, da der Server die Liste aller Partitionsdefinitionen durchsuchen muss, um die richtige Antwort zu finden.

• Die Kosten für das Öffnen und Sperren aller zugrunde liegenden Tabellen können hoch sein: Wenn eine Abfrage auf eine partitionierte Tabelle zugreift, muss MySQL alle zugrunde liegenden Tabellen öffnen und sperren ist ein weiterer Overhead von partitionierten Tabellen.

• Die Kosten für die Wartung von Partitionen können hoch sein: Einige Partitionswartungsvorgänge können sehr schnell sein, z. B. das Hinzufügen oder Löschen von Partitionen. Einige Vorgänge, beispielsweise das Reorganisieren von Partitionen oder ähnliche ALTER-Anweisungen, können sehr kostspielig sein, da für solche Vorgänge das Kopieren von Daten erforderlich ist.

Das obige ist der detaillierte Inhalt vonDetaillierte Einführung in Partitionstabellen in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!