MySQL-Datentabellenpartitionierungsstrategie und Vor- und Nachteileanalyse

Warum brauchen wir Partitionen?

Bei riesigen Datentabellen ist zumindest eines sicher: Die Tabelle ist so groß, dass wir nicht bei jeder Abfrage einen vollständigen Tabellenscan durchführen können. Zu diesem Zeitpunkt kann der Index nicht verwendet werden oder die Bedeutung des Index ist gering. Ganz zu schweigen davon, dass die Wartungskosten und der vom Index belegte Speicherplatz sehr hoch sind. Wenn Sie sich auf Indizes verlassen, führt dies zu einer großen Menge fragmentierter Daten mit geringer Dichte, was bei Abfragen Tausende von zufälligen E/A-Zugriffen und Ausfallzeiten zur Folge hat. In diesem Fall werden im Allgemeinen nur 1-2 Indizes verwendet und nicht mehr. Dabei gibt es zwei Möglichkeiten: Die Abfrage muss sequenziell aus dem angegebenen Tabellenteil bzw. dem gewünschten Datenteil suchen und ihr Index muss mit dem Speicher des Servers übereinstimmen.

Es muss wiederholt werden: Wenn der Speicherplatz zu groß ist, funktionieren binäre Baumindizes nicht, es sei denn, der Index deckt die gesamte Abfrage ab. Der Server muss eine ganze Datenzeile in der Datentabelle finden und zufällige E/A-Operationen in einem großen Raumbereich ausführen, was zu einer inakzeptablen Abfrageantwortzeit führt. Auch die Pflege der Indizes (Festplattenspeicherplatz, E/A-Vorgänge) ist kostspielig.

Dieses Problem kann durch eine Partitionierung gelöst werden. Der Schlüssel liegt hier darin, dass die Partitionierung eine primitive Form der Indizierung ist, die nur einen geringen Mehraufwand erfordert und es uns ermöglicht, Ergebnisse aus nahegelegenen Daten abzurufen. In diesem Fall können wir benachbarte Daten sequenziell scannen oder benachbarte Daten zum Abrufen in den Speicher laden. Der Grund für die geringe Auslastung der Partition liegt darin, dass sie keinen Zeiger auf die entsprechende Datenzeile besitzt und nicht aktualisiert werden muss. Bei der Partitionierung handelt es sich weder um eine genaue Aufteilung der Daten in Zeilen, noch handelt es sich dabei um sogenannte Datenstrukturen. Tatsächlich ist die Partitionierung gleichbedeutend mit der Klassifizierung von Daten.

Partitionierungsstrategie

Für große Datentabellen gibt es zwei Strategien zur Partitionierung:

• Es wird kein Index verwendet: Beim Erstellen einer Datentabelle wird kein Index hinzugefügt. Stattdessen werden Partitionen verwendet, um die erforderlichen Datenzeilen zu lokalisieren. Solange Sie die WHERE-Bedingung verwenden, um die Abfrage in kleine Partitionsbereiche aufzuteilen, ist dies ausreichend. Derzeit sind mathematische Methoden erforderlich, um zu berechnen, ob die Abfrageantwortzeit akzeptabel ist. Natürlich wird hier davon ausgegangen, dass die Daten nicht in den Speicher geschrieben werden, sondern alle Daten von der Festplatte gelesen werden. Daher werden die Daten schnell durch andere Abfragen überschrieben und die Verwendung des Caches macht wenig Sinn. Diese Situation kommt häufig bei Datentabellen mit großer Kardinalität vor. Dabei ist zu beachten, dass die Anzahl der Partitionen auf einige Hundert begrenzt werden muss.
• Verwenden Sie Indizes und isolieren Sie Hotzone-Daten: Wenn die meisten Daten außer den Hotzone-Daten nicht verwendet werden, können die Hotzone-Daten separat partitioniert und diese Partition und der Index in den Speicher geladen werden. Derzeit können Sie zur Leistungsoptimierung Indizes verwenden, genau wie beim Bedienen gewöhnlicher Datentabellen.
  

Partitionsgefahren

Die beiden Partitionierungsstrategien basieren auf zwei wichtigen Annahmen: Der Suchbereich kann durch Filtern von Partitionen während der Abfrage eingeschränkt werden und die Kosten der Partitionen selbst sind nicht hoch. Diese beiden Annahmen sind jedoch möglicherweise nicht immer gültig. Hier sind einige Probleme, auf die Sie stoßen können:

• NULL-Werte können dazu führen, dass die Partitionsfilterung fehlschlägt: Wenn die Partitionsfunktion NULL sein kann, sind die Ergebnisse der Partitionierungsarbeit sehr merkwürdig. Es wird davon ausgegangen, dass die erste Partition speziell ist. Angenommen, PARTITION BY RANGE YEAR(order_date) wird verwendet. Wenn die Spalte order_date NULL oder ein ungültiges Datum ist, wird es in der ersten Partition gespeichert. Angenommen, Sie schreiben eine Abfrage mit der folgenden Abfragebedingung: WHERE order_date BETWEEN '2021-01-01' AND '2021-01-31'. MySQL überprüft tatsächlich zwei Partitionen, eine für YEAR, eine Funktion, die NULL zurückgeben kann, wenn sie ungültige Eingaben erhält, und eine für qualifizierte Werte, die NULL sein können (in der ersten Partition gespeichert). Dies ist auch für andere Funktionen möglich, beispielsweise TO_DAYS. Dies kann zu Problemen führen, wenn die erste Partition groß ist, insbesondere wenn die erste Strategie ohne Indizes verwendet wird. Der Effekt der Datensuche aus zwei Partitionen statt aus einer ist völlig unerwartet. Um dies zu vermeiden, sollte eine „falsche“ erste Partition erstellt werden, zum Beispiel PARTITION p_nulls VALUES LESS THAN (0). Wenn in der Datentabelle keine ungültigen Daten gespeichert sind, ist die erste Partition leer. Auch wenn sie gescannt wird, hat dies kaum Auswirkungen auf die Leistung, da sie leer ist oder nur sehr wenige Daten enthält. In MySQL 5.5 und höher muss diese Situation nicht behandelt werden, wenn Spalten direkt zur Partitionierung verwendet werden, sie muss jedoch behandelt werden, wenn Funktionen verwendet werden.
• Index entspricht nicht der Partition: Wenn ein Index definiert ist, der nicht der Partitionsbedingung entspricht, kann die Abfrage die Partition möglicherweise nicht filtern. Angenommen, für Feld A ist ein Index definiert, aber Feld B wird zur Partitionierung verwendet. Da jede Partition über einen eigenen Index verfügt, durchlaufen Abfragen dieses Index den Indexbaum aller Partitionen. Wenn alle Nicht-Blattknoten des Indexbaums im Speicher abgelegt sind, ist die Abfrage zwar schneller, allerdings lässt sich das Scannen des gesamten Indexes nicht vermeiden. Um diese Situation zu vermeiden, sollten Sie versuchen, die Verwendung nicht partitionierter Indexspalten zu vermeiden, es sei denn, die WHERE-Bedingung selbst kann die Partition angeben. Dies lässt sich scheinbar leicht vermeiden, ist aber tatsächlich überraschend. Angenommen, eine partitionierte Tabelle wird in einer Join-Abfrage mit einer zweiten Tabelle verwendet und der in der Join-Abfrage verwendete Index ist nicht der Partitionsindex. Dann greift jede Zeile der Union-Abfrage auf die Partition der zweiten Tabelle zu und durchsucht diese.
• Die Entscheidung, welche Partition verwendet werden soll, kann aufwändig sein: Die Partitionierung wird auf unterschiedliche Weise implementiert, sodass die tatsächliche Leistung nicht immer konsistent ist. Dies gilt insbesondere, wenn Fragen auftauchen wie „Zu welcher Partition gehört diese Datenzeile?“ oder „Wie finde ich die Datenzeile, die den Abfragebedingungen entspricht?“. Bei so vielen Partitionen ist es schwierig, solche Fragen zu beantworten. Die lineare Suche ist nicht immer effizient und wird daher mit zunehmender Anzahl der Partitionen teurer. Die schlimmste Form ist das zeilenweise Einfügen. Jedes Mal, wenn eine Datenzeile in eine partitionierte Datentabelle eingefügt wird, muss der Server einmal einen Scan durchführen, um herauszufinden, welche Partition zum Speichern der neuen Datenzeile verwendet werden soll. Dieses Problem lässt sich durch eine Begrenzung der Partitionsanzahl verringern. Tatsächlich wird im Allgemeinen empfohlen, die Anzahl von 100 Partitionen nicht zu überschreiten. Für andere Partitionstypen, wie etwa Schlüssel- und Hash-Partitionen, gibt es diese Einschränkung natürlich nicht.
• Auch das Öffnen und Sperren von Partitionen kann kostspielig sein: Ein Nebeneffekt partitionierter Tabellen besteht darin, dass für Abfragen das Öffnen und Sperren jeder einzelnen Partition erforderlich ist. Dieser Vorgang wird vor dem Filtern der Partitionen durchgeführt. Dieser Aufwand ist unabhängig vom Partitionstyp und wirkt sich auf alle Operationsanweisungen aus. Dieser Effekt ist insbesondere bei Abfragen mit geringen Datenmengen, beispielsweise bei der Abfrage nur einer Datenzeile, deutlich spürbar. Dieser Fehler kann durch die Ausführung von Stapelverarbeitungsvorgängen anstelle von Einzelvorgängen verringert werden, z. B. durch das gleichzeitige Einfügen mehrerer Zeilen oder LOAD DATA INFILE, das gleichzeitige Löschen von Daten nach Bereichen usw. Natürlich ist es auch effektiv, die Anzahl der Partitionen zu begrenzen.
• Wartungsvorgänge können kostspielig sein: Einige Partitionswartungen sind schnell erledigt, beispielsweise das Erstellen oder Löschen von Partitionen. Andere Vorgänge, wie etwa das Anpassen von Partitionen, ähneln ein wenig ALTER-Vorgängen bei Tabellen: Sie erfordern das Ausführen einer Schleife und das Kopieren von Datenzeilen. Wenn Sie beispielsweise die Größe einer Partition ändern, wird eine temporäre Partition erstellt, Daten werden auf die neue Partition verschoben und anschließend die alte Partition gelöscht.
  

Wie oben erwähnt ist Partitionierung keine perfekte Lösung. Die aktuelle Version von MySQL hat einige weitere Einschränkungen:

• Alle Partitionen müssen dieselbe Speicher-Engine verwenden.
• Es gibt bestimmte Einschränkungen hinsichtlich der Funktionen oder Ausdrücke, die als Partitionsfunktionen verwendet werden können.
• Einige Speicher-Engines unterstützen keine Partitionierung.
• Für MYISAM-Datentabellen kann LOAD INDEX INTO CACHE nicht verwendet werden.
• Für MYISAM-Datentabellen erfordern partitionierte Tabellen mehr offene Dateideskriptoren, was bedeutet, dass ein einzelner Cache-Eintrag in der Datentabelle mehreren Dateideskriptoren entsprechen kann. Daher begrenzt die Grundkonfiguration den Cache der Datentabelle, um eine Überschreitung der Vorverarbeitungsmenge des Serverbetriebssystems zu vermeiden. Partitionierte Tabellen können diese Grenze tatsächlich überschreiten.

Natürlich wird die Unterstützung für die Partitionierung mit der Aktualisierung und Iteration der MySQL-Versionen immer besser und viele Partitionierungsprobleme wurden behoben.

Oben finden Sie detaillierte Informationen zur Partitionierungsstrategie für MySQL-Datentabellen sowie zu deren Vor- und Nachteilen. Weitere Informationen zur Partitionierungsstrategie für MySQL-Datentabellen sowie zu deren Vor- und Nachteilen finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!