Detaillierte Erläuterung der Einschränkungen und Beschränkungen von MySQL-partitionierten Tabellen

Build deaktivieren

Partitionsausdrücke unterstützen die folgenden Konstrukte nicht:

Gespeicherte Prozeduren, gespeicherte Funktionen, UDFS oder Plug-Ins

Deklarieren von Variablen oder Benutzervariablen

Bitte beachten Sie, dass von Partitionen nicht unterstützte SQL-Funktionen vorhanden sind.

Arithmetische und logische Operatoren

Partitionsausdrücke unterstützen die Rechenoperationen „+“, „-“ und „*“, aber keine DIV- und „/“-Operationen (dieses Problem besteht weiterhin, siehe Fehler Nr. 30188 und Nr. 33182). Das Ergebnis muss jedoch eine Ganzzahl oder NULL sein (außer bei linearen Partitionsschlüsseln, weitere Informationen finden Sie unter Partitionstypen).

Partitionsausdrücke unterstützen keine bitweisen Operationen: |, &, ^, <<, >>, ~.

HANDLER-Anweisung

Partitionierte Tabellen vor MySQL 5.7.1 unterstützen die HANDLER-Anweisung nicht. Diese Einschränkung wurde in späteren Versionen behoben.

Server-SQL-Modus

Wenn Sie eine benutzerdefinierte partitionierte Tabelle verwenden möchten, beachten Sie bitte, dass der SQL-Modus beim Erstellen der partitionierten Tabelle nicht beibehalten wird. Wie im Kapitel „Server-SQL-Modi“ erläutert, können sich die Ergebnisse der meisten MySQL-Funktionen und -Operatoren je nach Server-SQL-Modus ändern. Wenn der SQL-Modus nach dem Erstellen partitionierter Tabellen geändert wird, kann dies daher zu erheblichen Änderungen im Verhalten dieser Tabellen führen, was leicht zu Datenverlust oder -beschädigung führen kann. Aus den oben genannten Gründen wird dringend empfohlen, den SQL-Modus des Servers nach dem Erstellen einer partitionierten Tabelle niemals zu ändern.

Sehen wir uns zur Veranschaulichung der obigen Situation ein Beispiel an:

1. Fehlerbehandlung

mysql> TABELLE ERSTELLEN tn (c1 INT)
  -> PARTITION NACH LISTE(1 DIV c1) (
  -> PARTITION p0 WERTE IN (NULL),
  -> PARTITION p1 WERTE IN (1)
  -> );
  Abfrage OK, 0 Zeilen betroffen (0,05 Sek.)

Standardmäßig ist das Ergebnis einer Division durch 0 in MySQL NULL und kein Fehler:

mysql> AUSWÄHLEN @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 Zeile im Satz (0,00 Sek.)
 
mysql> INSERT INTO tn VALUES (NULL), (0), (1);
Abfrage OK, 3 Zeilen betroffen (0,00 Sek.)
Datensätze: 3 Duplikate: 0 Warnungen: 0

Wenn wir jedoch den SQL-Modus ändern, wird ein Fehler gemeldet:

mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
 
mysql> INSERT INTO tn VALUES (NULL), (0), (1);
FEHLER 1365 (22012): Division durch 0

2. Tabellenhilfsfunktionen

Manchmal kann das Ändern des SQL-Modus dazu führen, dass die partitionierte Tabelle unbrauchbar wird. Beispielsweise funktionieren einige Tabellen nur, wenn der SQL-Modus NO_UNSIGNED_SUBTRACTION ist, wie zum Beispiel:

mysql> AUSWÄHLEN @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 Zeile im Satz (0,00 Sek.)
 
mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
  -> PARTITION BY RANGE(c1 - 10) (
  -> PARTITION p0 WERTE KLEINER ALS (-5),
  -> PARTITION p1 WERTE KLEINER ALS (0),
  -> PARTITION p2 WERTE KLEINER ALS (5),
  -> PARTITION p3 WERTE WENIGER ALS (10),
  -> PARTITION p4 WERTE KLEINER ALS (MAXIMALER WERT)
  -> );
FEHLER 1563 (HY000): Die Partitionskonstante liegt außerhalb des Partitionsfunktionsbereichs.
   
mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
 
mysql> AUSWÄHLEN @@sql_mode;
+-------------------------+
| @@sql_mode |
+-------------------------+
| KEINE_UNSIGNED_SUBTRACTION |
+-------------------------+
1 Zeile im Satz (0,00 Sek.)
 
mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
  -> PARTITION BY RANGE(c1 - 10) (
  -> PARTITION p0 WERTE KLEINER ALS (-5),
  -> PARTITION p1 WERTE KLEINER ALS (0),
  -> PARTITION p2 WERTE KLEINER ALS (5),
  -> PARTITION p3 WERTE WENIGER ALS (10),
  -> PARTITION p4 WERTE KLEINER ALS (MAXIMALER WERT)
  -> );
   
Abfrage OK, 0 Zeilen betroffen (0,05 Sek.)

Wenn Sie den SQL-Modus nach dem Erstellen von tu ändern, können Sie möglicherweise nicht mehr auf die Tabelle zugreifen:

mysql> SET sql_mode=''; Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
 
mysql> SELECT * FROM tu;
FEHLER 1563 (HY000): Die Partitionskonstante liegt außerhalb des Partitionsfunktionsbereichs.
 
mysql> INSERT INTO tu VALUES (20);
FEHLER 1563 (HY000): Die Partitionskonstante liegt außerhalb des Partitionsfunktionsbereichs.

Der SQL-Modus auf der Serverseite wirkt sich auch auf die Replikation partitionierter Tabellen aus. Die Verwendung unterschiedlicher SQL-Modi zwischen der Master- und der Slave-Datenbank kann zu unterschiedlichen Ergebnissen führen, wenn Partitionsausdrücke auf der Master- und der Slave-Datenbank ausgeführt werden (obwohl das Umschalten zwischen Master und Slave in Alibaba ein normaler Vorgang ist). Dies kann auch zu einer unterschiedlichen Datenverteilung zwischen verschiedenen Partitionen während des Master-Slave-Replikationsprozesses führen. Es kann auch dazu führen, dass das Einfügen der Partitionstabelle auf der Master-Datenbank erfolgreich ist, auf der Slave-Datenbank jedoch fehlschlägt. Aufgrund der oben beschriebenen Situation besteht die beste Lösung darin, sicherzustellen, dass der SQL-Modus zwischen dem Primär- und dem Sicherungsserver konsistent ist (darauf muss der DBA bei Betrieb und Wartung achten).

Überlegungen zur Leistung

Im Folgenden sind einige Faktoren aufgeführt, die sich auf die Leistung von Partitionsvorgängen auswirken:

Die Dateisystemoperationen, die bei der Partitionierung oder Neupartitionierung (wie etwa ALTER TABLE ... PARTITION BY ..., REORGANIZE PARTITION oder REMOVE PARTITIONING) ausgeführt werden, hängen von der Implementierung des Dateisystems ab. Dies bedeutet, dass die oben genannten Vorgänge von Faktoren des Betriebssystems beeinflusst werden, z. B.: Typ und Eigenschaften des Dateisystems, Festplattengeschwindigkeit, Auslagerungsspeicher, Dateiverarbeitungseffizienz des Betriebssystems und Optionen und Variablen im Zusammenhang mit Dateihandles auf dem MySQL-Server. Beachten Sie, dass Sie sicherstellen müssen, dass „large_files_support“ aktiviert ist und dass die Einstellung „open_files_limit“ sinnvoll ist. Bei MyISAM-partitionierten Tabellen müssen Sie myisam_max_sort_file_size erhöhen, um die Leistung zu verbessern. Bei InnoDB-Tabellen sind Partitionierungs- oder Neupartitionierungsvorgänge mit aktiviertem innodb_file_per_table schneller.

Siehe auch Maximale Anzahl an Partitionen.

MyISAM und Partitionsdateideskriptoren

Für MyISAM-partitionierte Tabellen verwendet MySQL zwei Dateideskriptoren pro Partition für jede geöffnete Tabelle. Dies bedeutet, dass zum Ausführen von Vorgängen an einer partitionierten MyISAM-Tabelle (insbesondere ALTER TABLE-Vorgänge) mehr Dateideskriptoren erforderlich sind als für dieselbe Tabelle ohne Partitionen.

Angenommen, wir möchten eine MyISAM-Tabelle mit 100 Partitionen erstellen. Die Anweisung lautet wie folgt:

Tabelle erstellen t (c1 VARCHAR (50))
PARTITION NACH SCHLÜSSEL (c1) PARTITIONEN 100
Motor = MyIsam;

Kurz gesagt: Obwohl wir in diesem Beispiel die KEY-Partitionierung verwenden, tritt das Problem der Dateideskriptoren in allen Partitionen auf, die die MyISAM-Tabellen-Engine verwenden, unabhängig vom Partitionstyp. Bei partitionierten Tabellen, die andere Speicher-Engines (wie etwa InnoDB) verwenden, besteht dieses Problem jedoch nicht.

Angenommen, Sie möchten t so neu partitionieren, dass es 101 Partitionen hat, verwenden Sie die folgende Anweisung:

ALTER TABLE t PARTITION BY KEY (c1) PARTITIONS 101;

Wenn ALTER TABLE-Anweisungen verarbeitet werden sollen, werden 402 Dateideskriptoren benötigt, d. h. 2 für die ursprünglichen 100 Partitionen + 2 für die 101 neuen Partitionen. Dies liegt daran, dass beim Neuorganisieren der Tabellendaten alle Partitionen (alte und neue) geöffnet werden müssen. Es wird daher empfohlen, beim Ausführen dieser Vorgänge darauf zu achten, dass --open-files-limit größer eingestellt ist.

Tabellensperre

Der Vorgang, Partitionsvorgänge an der Tabelle auszuführen, belegt die Schreibsperre der Tabelle, was sich nicht auf das Lesen auswirkt. Beispielsweise können INSERT- und UPDATE-Vorgänge an diesen Partitionen erst ausgeführt werden, nachdem der Partitionsvorgang abgeschlossen ist.

Speicher-Engine

Partitionsvorgänge wie Abfrage- und Aktualisierungsvorgänge sind mit der MyISAM-Engine normalerweise schneller als mit InnoDB und NDB.

Indizes; Partitionsbereinigung

Wie bei nicht partitionierten Tabellen kann die Abfragegeschwindigkeit bei partitionierten Tabellen durch die geeignete Verwendung von Indizes erheblich verbessert werden. Darüber hinaus kann beim Entwurf partitionierter Tabellen und bei Abfragen dieser Tabellen die Partitionsbereinigung zu einer deutlichen Leistungssteigerung führen.

Vor MySQL 5.7.3 unterstützten partitionierte Tabellen kein Indexbedingungs-Pushdown, spätere Versionen unterstützen es jedoch.

Leistung beim Laden von Daten

In MySQL 5.7 werden beim Laden von Daten Puffer verwendet, um die Leistung zu verbessern. Sie müssen wissen, dass der Puffer zu diesem Zweck 130 KB jeder Partition einnimmt.

Maximale Anzahl an Partitionen

Wenn die Partitionstabelle NDB nicht als Speicher-Engine verwendet, beträgt die maximale Anzahl unterstützter Partitionen (einschließlich Unterpartitionen) 8192.

Die maximale Anzahl von Partitionen für die benutzerdefinierte Partitionierung, wenn NDB als Speicher-Engine verwendet wird, hängt von der MySQL Cluster-Version, den Datenknoten und anderen Faktoren ab.

Wenn Sie eine sehr große Zahl an Partitionen erstellen (weniger als die maximale Zahl an Partitionen) und beim Öffnen der Datei Fehler auftreten wie „Fehler ... vom Speichermodul: Keine Ressourcen mehr“, müssen Sie möglicherweise „open_files_limit“ erhöhen. Allerdings hängt open_files_limit tatsächlich vom Betriebssystem ab und es ist möglicherweise nicht empfehlenswert, es auf allen Plattformen anzupassen. Es gibt jedoch auch Situationen, in denen die Verwendung riesiger Partitionen oder von Hunderten von Partitionen nicht empfehlenswert ist. Die Verwendung von immer mehr Partitionen führt daher nicht unbedingt zu guten Ergebnissen.

Unterstützt keinen Abfragecache

Partitionierte Tabellen unterstützen keinen Abfragecache und Abfragen an partitionierte Tabellen vermeiden automatisch den Abfragecache. Das heißt, der Abfragecache funktioniert nicht in der Abfrageanweisung der Partitionstabelle.

Ein Schlüsselcache pro Partition

In MySQL 5.7 können Sie den Schlüsselcache von MyISAM-partitionierten Tabellen über CACHE INDEX und LOAD INDEX INTO CACHE verwenden. Ein Schlüsselcache kann für eine, mehrere oder alle Partitionen definiert werden, sodass die Indizes einer, mehrerer oder aller Partitionen vorab in den Schlüsselcache geladen werden können.

Fremdschlüssel für partitionierte InnoDB-Tabellen werden nicht unterstützt.

Partitionierte Tabellen, die die InnoDB-Engine verwenden, unterstützen keine Fremdschlüssel. Die folgenden zwei Sondersituationen werden erläutert:

Sie können in InnoDB-Tabellen keine benutzerdefinierte Partitionierung mit Fremdschlüsseln verwenden. Wenn eine InnoDB-Tabelle bereits Fremdschlüssel verwendet, kann sie nicht partitioniert werden.

Eine InnoDB-Tabelle kann keinen Fremdschlüssel enthalten, der mit einer benutzerdefinierten partitionierten Tabelle verknüpft ist. Eine InnoDB-Tabelle, die benutzerdefinierte Partitionen verwendet, kann keine Spalten enthalten, die mit Fremdschlüsseln verknüpft sind.

Der Umfang der gerade aufgeführten Einschränkungen umfasst alle Tabellen, die die Speicher-Engine InnoDB verwenden. CREATE TABLE- und ALTER TABLE-Anweisungen, die diese Einschränkungen verletzen, sind nicht zulässig.

ALTER TABLE ... ORDER BY

Die Ausführung von ALTER TABLE ... ORDER BY auf einer partitionierten Tabelle führt dazu, dass die Zeilen jeder Partition sortiert werden.

Effizienz der REPLACE-Anweisung beim Ändern des Primärschlüssels

In manchen Fällen ist es notwendig, den Primärschlüssel einer Tabelle zu ändern. Wenn Ihre Anwendung REPLACE-Anweisungen verwendet, können die Ergebnisse dieser Anweisungen erheblich verändert werden.

Volltextindex

Partitionierte Tabellen unterstützen keine Volltextindizierung oder -suche, selbst wenn die Speicher-Engine für die partitionierte Tabelle InnoDB oder MyISAM ist.

Räumliche Spalte

Partitionierte Tabellen unterstützen keine räumlichen Spalten wie Punkte oder Geometrie.

Temporäre Tabellen

Temporäre Tabellen können nicht partitioniert werden (Fehler Nr. 17497).

Protokolltabelle

Die Protokolltabelle kann nicht partitioniert werden. Wenn Sie die Ausführung der Anweisung ALTER TABLE ... PARTITION BY ... erzwingen, wird ein Fehler gemeldet.

Datentyp des Partitionsschlüssels

Der Partitionsschlüssel muss eine Ganzzahl oder ein Ausdruck sein, der als Ganzzahl ausgewertet wird. Sie können keine Ausdrücke verwenden, die zu einem ENUM-Typ führen. Weil Ausdrücke dieses Typs NULL sein können.

Es gibt zwei Ausnahmen:

Wenn Sie die LINER-Partitionierung verwenden, können Sie andere Datentypen als TEXT oder BLOBS als Partitionierungsschlüssel verwenden, da die internen Hash-Funktionen von MySQL aus diesen Spalten den richtigen Datentyp generieren. Beispielsweise sind die folgenden Create-Anweisungen zulässig:

Tabelle erstellen tkc (c1 Zeichen)
PARTITION NACH SCHLÜSSEL(c1)
PARTITIONEN 4;

Tabelle erstellen tke
  ( c1 ENUM('rot', 'orange', 'gelb', 'grün', 'blau', 'indigo', 'violett') )
PARTITION NACH LINEAREM SCHLÜSSEL (c1)
PARTITIONEN 6;

Bei der Partitionierung nach RANGE-, LIST-, DATE- oder DATETIME-Spalten kann eine Zeichenfolge verwendet werden. Beispielsweise sind die folgenden Create-Anweisungen zulässig:

TABELLE ERSTELLEN rc (c1 INT, c2 DATUM)
PARTITION NACH BEREICHSSPALTEN(c2) (
  PARTITION p0 WERTE KLEINER ALS('1990-01-01'),
  PARTITION p1 WERTE KLEINER ALS('1995-01-01'),
  PARTITION p2 WERTE KLEINER ALS('2000-01-01'),
  PARTITION p3 WERTE KLEINER ALS('2005-01-01'),
  PARTITION p4 WERTE KLEINER ALS (MAXIMALER WERT)
);

Tabelle erstellen lc (c1 INT, c2 CHAR(1))
PARTITION NACH LIST COLUMNS(c2) (
  PARTITION p0 WERTE IN('a', 'd', 'g', 'j', 'm', 'p', 's', 'v', 'y'),
  PARTITION p1 WERTE IN('b', 'e', ​​​​'h', 'k', 'n', 'q', 't', 'w', 'z'),
  PARTITION p2 WERTE IN('c', 'f', 'i', 'l', 'o', 'r', 'u', 'x', NULL)
);

Keine der oben genannten Ausnahmen gilt für BLOB- oder TEXT-Spaltentypen.

Unterabfragen

Auch wenn die Unterabfrage ganzzahlige oder NULL-Werte vermeidet, kann der Partitionsschlüssel nicht weiter abgefragt werden.

Das Problem mit der Unterpartitionierung

Unterpartitionen müssen HASH- oder KEY-Partitionen verwenden. Nur RANGE- und LIST-Partitionen unterstützen Unterpartitionen; HASH und KEY unterstützen keine Unterpartitionen.

SUBPARTITION BY KEY erfordert die explizite Angabe der Unterpartitionsspalten. Im Gegensatz zu PARTITION BY KEY, das weggelassen werden kann (in diesem Fall wird standardmäßig der Primärschlüssel der Tabelle verwendet). Wenn Sie die Tabelle beispielsweise folgendermaßen erstellen:

TABELLE ERSTELLEN ts (
  id INT NICHT NULL AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(30)
);

Sie können mit der folgenden Anweisung auch eine partitionierte Tabelle (partitioniert nach KEY) mit denselben Spalten erstellen:

TABELLE ERSTELLEN ts (
  id INT NICHT NULL AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(30)
)
PARTITION NACH KEY()
PARTITIONEN 4;

Die vorherige Anweisung ist eigentlich identisch mit der folgenden Anweisung:

TABELLE ERSTELLEN ts (
  id INT NICHT NULL AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(30)
)
PARTITION NACH SCHLÜSSEL(id)
PARTITIONEN 4;
 

Wenn Sie jedoch versuchen, eine unterpartitionierte Tabelle zu erstellen und dabei die Standardspalte als Unterpartitionsspalte verwenden, schlägt die folgende Anweisung fehl und muss wie unten gezeigt angegeben werden, damit die Anweisung erfolgreich ist: (Fehler bekannt als Fehler Nr. 51470).

mysql> TABELLE ERSTELLEN ts (
  -> id INT NICHT NULL AUTO_INCREMENT PRIMARY KEY,
  -> Name VARCHAR(30)
  -> )
  -> PARTITION NACH BEREICH(id)
  -> UNTERPARTITION NACH SCHLÜSSEL()
  -> UNTERPARTITIONEN 4
  -> (
  -> PARTITION p0 WERTE KLEINER ALS (100),
  -> PARTITION p1 WERTE KLEINER ALS (MAXIMALER WERT)
  -> );
   
FEHLER 1064 (42000): Sie haben einen Fehler in Ihrer SQL-Syntax. Lesen Sie im Handbuch nach,
entspricht Ihrer MySQL-Serverversion für die richtige zu verwendende Syntax in der Nähe von ')
 
mysql> TABELLE ERSTELLEN ts (
  -> id INT NICHT NULL AUTO_INCREMENT PRIMARY KEY,
  -> Name VARCHAR(30)
  -> )
  -> PARTITION NACH BEREICH(id)
  -> UNTERPARTITION NACH SCHLÜSSEL(id)
  -> UNTERPARTITIONEN 4
  -> (
  -> PARTITION p0 WERTE KLEINER ALS (100),
  -> PARTITION p1 WERTE KLEINER ALS (MAXIMALER WERT)
  -> );
   
Abfrage OK, 0 Zeilen betroffen (0,07 Sek.)

Datenwörterbuch- und Indexwörterbuchoptionen

Für das Datenwörterbuch und das Indexwörterbuch einer partitionierten Tabelle gelten die folgenden Einschränkungen:

Datenwörterbuch und Indexwörterbuch auf Tabellenebene werden ignoriert (Fehler #32091)

Auf Windows-Systemen unterstützen MyISAM-partitionierte Tabellen keine Datenwörterbuch- und Indexwörterbuchoptionen für einzelne Partitionen oder Unterpartitionen. Es unterstützt jedoch Datenwörterbücher für unabhängige Partitionen oder Unterpartitionen von partitionierten InnoDB-Tabellen.

Reparieren und Wiederherstellen der Partitionstabelle

Partitionierte Tabellen unterstützen die Anweisungen CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE und REPAIR TABLE.

Alternativ können Sie ALTER TABLE ... REBUILD PARTITION verwenden, um eine oder mehrere Partitionen einer partitionierten Tabelle neu zu erstellen. Sie können auch ALTER TABLE ... REORGANIZE PARTITION verwenden, um Partitionen neu zu erstellen.

Ab MySQL 5.7.2 unterstützen Unterpartitionen die Operationen ANALYZE, CHECK, OPTIMIZE, REPAIR und TRUNCATE. Die REBUILD-Syntax wurde in Versionen vor MySQL 5.7.5 eingeführt, funktioniert aber nicht (siehe Fehler Nr. 19075411, Fehler Nr. 73130).

Die Vorgänge mysqlcheck, myisamchk und myisampack werden für partitionierte Tabellen nicht unterstützt.

Exportoptionen

In MySQL-Versionen vor 5.7.4 wird die Exportoption der FLUSH TABLES-Anweisung für InnoDB-partitionierte Tabellen nicht unterstützt (Bug #16943907).

Verweise

https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations.html

https://www.slideshare.net/datacharmer/mysql-partitions-tutorial/34-Partition_pruning_unpartitioned_tableexplain_partitions

https://www.percona.com/blog/2010/12/11/mysql-partitioning-can-save-you-or-kill-you/